ERROR 1118 (42000): Row size too large. The maximum row size for the used table type, not counting B

[Sergiy Kushnir]

Hi All,

Durin the migration from regular MariaDB into the ColumnStore we found that one of our Hibernate-generated SELECT query ran into the following error ERROR 1118 (42000): Row size too large. The maximum row size for the used table type, not counting BLOBs, is 65535. This includes storage overhead, check the manual. You have to change some columns to TEXT or BLOBs.

As far as I know this error related to the INSERT/UPDATE/CREATE operations and format of storing the data into the database files, but, we received it during the execution of the SELECT statement.

I have tried to play with different configuration parameters but without any luck. Standard solutions with changing ROW_FORMAT, innodb_file_format=Barracuda also didn't help.

I have tried to reproduce the parameters from the regular MariaDB with the same innodb_version, where it works without problems, but also useless.

It looks like that ColumnStore materializes the SELECT statement into the TEMP table on some stage or something like that...

MariaDB [test]> SHOW VARIABLES LIKE "%version%" \g;
+-------------------------+---------------------------------+
| Variable_name           | Value                           |
+-------------------------+---------------------------------+
| innodb_version          | 5.6.35-80.0                     |
| protocol_version        | 10                              |
| slave_type_conversions  |                                 |
| version                 | 10.1.23-MariaDB                 |
| version_comment         | Columnstore 1.0.9-1             |
| version_compile_machine | x86_64                          |
| version_compile_os      | Linux                           |
| version_malloc_library  | system                          |
| version_ssl_library     | OpenSSL 1.0.1e-fips 11 Feb 2013 |
+-------------------------+---------------------------------+

CREATE TABLE `t1` (

  `c1` varchar(32765) NOT NULL,

  `c2` varchar(32766) NOT NULL

) ENGINE=InnoDB DEFAULT CHARSET=latin1;

insert into t1 select '123','123';

MariaDB [test]> select c1,c2 from t1;

+-----+-----+

| c1  | c2  |

+-----+-----+

| 123 | 123 |

| 123 | 123 |

+-----+-----+

2 rows in set (0.01 sec)

MariaDB [test]> select c1,c2,c1 from t1;

ERROR 1118 (42000): Row size too large. The maximum row size for the used table type, not counting BLOBs, is 65535. This includes storage overhead, check the manual. You have to change some columns to TEXT or BLOBs

And from other side on regular MariaDB with the same innodb_version:

MariaDB [test]> SHOW VARIABLES LIKE "%version%";
+-------------------------+---------------------------------+
| Variable_name           | Value                           |
+-------------------------+---------------------------------+
| innodb_version          | 5.6.35-80.0                     |
| protocol_version        | 10                              |
| slave_type_conversions  |                                 |
| version                 | 10.1.22-MariaDB                 |
| version_comment         | mariadb.org binary distribution |
| version_compile_machine | x64                             |
| version_compile_os      | Win64                           |
| version_malloc_library  | system                          |
| version_ssl_library     | YaSSL 2.4.2                     |
+-------------------------+---------------------------------+
9 rows in set (0.00 sec)

MariaDB [test]> CREATE TABLE `t1` (

    ->   `c1` varchar(32765) NOT NULL,

    ->   `c2` varchar(32766) NOT NULL

    -> ) ENGINE=InnoDB DEFAULT CHARSET=latin1;

Query OK, 0 rows affected (0.11 sec)

MariaDB [test]> insert into t1 select '123','123';

Query OK, 1 row affected (0.00 sec)

Records: 1  Duplicates: 0  Warnings: 0

MariaDB [test]>  select c1,c2,c1 from t1;

+-----+-----+-----+

| c1  | c2  | c1  |

+-----+-----+-----+

| 123 | 123 | 123 |

+-----+-----+-----+

1 row in set (0.00 sec)

[Sergiy Kushnir]

Another way to reproduce the same:

select * from (select cast(123 AS CHAR(21845)) as f,cast(123 AS CHAR(21845)) as g,cast(123 AS CHAR(21845)) as h) m1;

ERROR 1118 (42000): Row size too large. The maximum row size for the used table type, not counting BLOBs, is 65535. This includes storage overhead, check the manual. You have to change some columns to TEXT or BLOBs

perfectly works on 10.1.22-MariaDB

[Andrew Hutchings]

Hi Sergiy,

I've responded on your ticket about this:

https://jira.mariadb.org/browse/MCOL-725

A summary for everyone else:

This is a known problem due to our current ORDER BY workaround inside
our modified MariaDB Server. We will resolve this in 1.1 in MCOL-713.

Basically we had to modify the point at which CHAR turns into TEXT
internally because we can't do ORDER BY on TEXT. This will come in
MCOL-643 at which point MCOL-713 is a 1 LoC fix.

I hope this helps.

Kind Regards
Andrew

On 23/05/17 15:50, Sergiy Kushnir wrote:
> Another way to reproduce the same:
>
> |
> select*from(selectcast(123AS CHAR(21845))asf,cast(123AS
> CHAR(21845))asg,cast(123AS CHAR(21845))ash)m1;
> ERROR 1118(42000):Rowsize too large.Themaximum row size forthe used
> table type,notcounting BLOBs,is65535.Thisincludes storage overhead,check

> the manual.Youhave to change some columns to TEXT orBLOBs
> |
>
> perfectly works on 10.1.22-MariaDB
>
> --
> You received this message because you are subscribed to the Google
> Groups "MariaDB ColumnStore" group.
> To unsubscribe from this group and stop receiving emails from it, send
> an email to mariadb-columns...@googlegroups.com
> <mailto:mariadb-columns...@googlegroups.com>.
> To post to this group, send email to
> mariadb-c...@googlegroups.com
> <mailto:mariadb-c...@googlegroups.com>.
> To view this discussion on the web visit
> https://groups.google.com/d/msgid/mariadb-columnstore/45ebcc5f-7a03-4f23-8cbf-300784452dbe%40googlegroups.com
> <https://groups.google.com/d/msgid/mariadb-columnstore/45ebcc5f-7a03-4f23-8cbf-300784452dbe%40googlegroups.com?utm_medium=email&utm_source=footer>.
> For more options, visit https://groups.google.com/d/optout.

--
Andrew Hutchings (LinuxJedi)
Senior Software Engineer, MariaDB