How to fix "ERROR 1118 (42000): Row size too large (> 8126)" with MariaDB 5.5?

Question

I am uploading a CSV file with 314 columns to a MariaDB 5.5 server. Currently my code looks like this:

CREATE TABLE table_name
   (column_1 TEXT,
    column_2 TEXT,
    ...
    column_314 TEXT)
ROW_FORMAT=DYNAMIC;

LOAD DATA LOCAL INFILE filepath  
INTO TABLE table_name 
FIELDS TERMINATED BY ',' 
ENCLOSED BY '"' 
LINES TERMINATED BY '\n' 
IGNORE 1 ROWS;

This results in the following error:

ERROR 1118 (42000): Row size too large (> 8126). Changing some columns to TEXT or BLOB
may help. In current row format, BLOB prefix of 0 bytes is stored inline.

I am using the Barracuda InnoDB file format with file per table enabled:

innodb_file_format=Barracuda
innodb_file_per_table=1

I believe the problem is the innodb page size, which is set to 16k. Since I am on MariaDB 5.5 and not 10.1+ I don't believe there is a way to increase the page size either. The database is managed by an organization and cannot currently be upgraded to a newer version.

Because MariaDB and MySQL both use the same database structure in this case (innoDB), the answers for MySQL are also almost certainly the same methods for MariaDB. Please see [this answer](https://stackoverflow.com/questions/25163614/error-1118-42000-row-size-too-large) — Martin, Apr 02 '19 at 21:14
Possible duplicate of [ERROR 1118 (42000) Row size too large](https://stackoverflow.com/questions/25163614/error-1118-42000-row-size-too-large) — Martin, Apr 02 '19 at 21:15
please share confirmation that you've read and tried these solutions and state If the answers (t)here do **not** answer your query — Martin, Apr 02 '19 at 21:17

Rick James · Accepted Answer · 2020-06-24T21:19:34.070

314 columns is "too many". But, since it is rather messy to split it up the CSV file, here are some thoughts on what might help you get it loaded with that many columns:

Be sure to use minimal-sized integers: TINYINT UNSIGNED is only 1 byte and allows numbers 0..255. See the manual for other sizes.
Be sure to use minimal-sized VARCHARs: Don't simply say VARCHAR(255) for everything. If you under-shoot, you will get truncation, so it may take a couple of passes to figure out the best sizes.
Use CHARACTER SET latin1 for any columns that you know to be just English, or even Western European. Else use utf8 or utf8mb4 where necessary.
Avoid TEXT and BLOB unless necessary.

Show us a sample of the data; we may have further tips.

(You could raise the 16KB limit to 64KB, but that would change the limit for all table, forever. Hardly anyone does such. I don't know the perils, if any.)

How to fix "ERROR 1118 (42000): Row size too large (> 8126)" with MariaDB 5.5?

1 Answers1