Row size too large. The maximum row size for the used table type, not counting BLOBs, is 65535 - MariaDB Migration

During migration to MariaDB you can get the “Row size too large” error for some of your tables, for example:

  CREATE TABLE cust (
     name VARCHAR(4000),
     loc     VARCHAR(4000),
     descr  VARCHAR(4000),
     notes  VARCHAR(4000),
     ref      VARCHAR(500)
  );
  # SQL Error (1118): 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

The interesting part is that the table above has 5 columns with the total size of 16,500. How is that possible that row size 65,535 was exceeded?

The size of VARCHAR columns is defined in characters, while the row size limit 65,535 is in bytes in MariaDB, and depending on the character set, a single character may require multiple bytes to store its value.

For example, MariaDB UTF8MB4 character set requires from 1 to 4 bytes to store a single character. I had this character set as the default character set, so my table with 16,500 characters could not be created as it may require 16,500*4= 66,000 bytes for row.

For some reason MariaDB checks this limit during table creation not a row insertion.

How to Resolve this Error

In my example it will be enough just to change the data type of the last column from VARCHAR(500) to TEXT and the table can be successfully created:

  CREATE TABLE cust (
     name VARCHAR(4000),
     loc     VARCHAR(4000),
     descr  VARCHAR(4000),
     notes  VARCHAR(4000),
     ref      TEXT
  );
  # OK

You can change the data type for other VARCHAR columns as well. MariaDB's row size limit is not applied for TEXT/BLOB columns.

How to Resolve this Error in SQLines Data Tool

There is the option -mysql_varchar_to_text_size=4000 that can be set in the command line or sqldata.cfg configuration file that defines the size of VARCHAR columns when they are mapped to TEXT data type. This option is applied for MariaDB and MySQL.

Also in SQLines Data tool, you can use the global data type mapping to change VARCHAR(4000) conversion to TEXT:

in the sqlines_dtmap.txt. For more details, see -dtmapf configuration option.