MariaDB Error importing sql database: Row size too large (> 8126)

1121 · Dec 4, 2018

eva2000 said: ↑

oh i noticed there's no row/data in that table ?? is it meant to not have any rows when imported ? as other possibility is working = not importing data properly ?
Click to expand...

It's a clean database with no data yet, correct, so I assume that's why it shows 0 rows.
I just tried to backup and import the database with the previous Centminmod my.cnf set to
innodb_log_file_size = 2G and I am back to the same ERROR 1118 (42000). Used commands from docs here:
backup
Code (Text):
mysqldump -u mysqlusername -p mysqldatabasename > /path/to/mysqldatabasename_backup_date.sql
restore
Code (Text):
mysql -u mysqlusername -p mysqldatabasename < /path/to/mysqldatabasename_backup_date.sql

eva2000 · Dec 4, 2018

what if you change the sql_mode to one used by non-centminmod my.cnf at MariaDB - Error importing sql database

1121 · Dec 4, 2018

Added as follows:

Code (Text):

sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES

right under

Code (Text):

socket=/var/lib/mysql/mysql.sock

OUTPUT

Code (Text):

mysqladmin var | egrep 'default_storage_engine|sql_mode' | tr -s ' '
| default_storage_engine | InnoDB |
| sql_mode | STRICT_TRANS_TABLES,NO_ENGINE_SUBSTITUTION |

still the same error on import.

eva2000 · Dec 4, 2018

can you share the mysql database's entire table structure via this command to dump only the database structure with no data
Code (Text):
mysqldump --no-data databasename > databasename-schema-only.sql
then post the contents of databasename-schema-only.sql to pastebin.com or gist.github.com and share the link...

it is still a limit for innodb though not sure why the other my.cnf works MySQL :: MySQL Restrictions and Limitations :: 8.4 Limits on Table Column Count and Row Size
InnoDB restricts row size (for data stored locally within the database page) to slightly less than half a database page. For example, the maximum row size is slightly less than 8KB for the default 16KB InnoDB page size, which is defined by the innodb_page_size configuration option.

The statement to create table t4 fails because the defined columns exceed the row size limit for a 16KB InnoDB page.
Code (Text):
mysql> SET SESSION innodb_strict_mode=1; 
mysql> CREATE TABLE t4 (
       c1 CHAR(255),c2 CHAR(255),c3 CHAR(255),
       c4 CHAR(255),c5 CHAR(255),c6 CHAR(255),
       c7 CHAR(255),c8 CHAR(255),c9 CHAR(255),
       c10 CHAR(255),c11 CHAR(255),c12 CHAR(255),
       c13 CHAR(255),c14 CHAR(255),c15 CHAR(255),
       c16 CHAR(255),c17 CHAR(255),c18 CHAR(255),
       c19 CHAR(255),c20 CHAR(255),c21 CHAR(255),
       c22 CHAR(255),c23 CHAR(255),c24 CHAR(255),
       c25 CHAR(255),c26 CHAR(255),c27 CHAR(255),
       c28 CHAR(255),c29 CHAR(255),c30 CHAR(255),
       c31 CHAR(255),c32 CHAR(255),c33 CHAR(255)
       ) ENGINE=InnoDB ROW_FORMAT=COMPACT DEFAULT CHARSET latin1;
ERROR 1118 (42000): Row size too large (> 8126). Changing some columns to TEXT or BLOB or using 
ROW_FORMAT=DYNAMIC or ROW_FORMAT=COMPRESSED may help. In current row format, BLOB prefix of 768 
bytes is stored inline.
Click to expand...

eva2000 · Dec 4, 2018

Oh I think I found the the option that is causing problems in Centmin Mod 123.09beta01 MariaDB 10 default /etc/my.cnf it's related to
Code (Text):
innodb_default_row_format = dynamic
from MySQL :: MySQL Restrictions and Limitations :: 8.4 Limits on Table Column Count and Row Size

Note
innodb_strict_mode is enabled in the following example to ensure that InnoDB returns an error if the defined columns exceed the InnoDB row size limit. When innodb_strict_mode is disabled (the default), creating a table that uses REDUNDANT or COMPACT row format succeeds with a warning if the InnoDB row size limit is exceeded.

DYNAMIC and COMPRESSED row formats are more restrictive in this regard. Creating a table that uses DYNAMIC or COMRESSED row format fails with an error if the InnoDB row size limit is exceeded, regardless of the innodb_strict_mode setting.
Click to expand...

So difference is warning and succeed (innodb_default_row_format compact) versus error and fail (innodb_default_row_format dynamic). Both instances though still mean the table(s) have hit InnoDB column limits - so should be something FunnelFlux needs to fix.

Centmin Mod changed the defaults from compact to dynamic for innodb_default_row_format to workaround fix large prefix issues MariaDB - Index column size too large. The maximum column size is 767 bytes. see specifically here.

By default, the index key prefix length limit is 767 bytes. See Section 13.1.13, “CREATE INDEX Syntax”. For example, you might hit this limit with a column prefix index of more than 255 characters on a TEXT or VARCHAR column, assuming a utf8mb3 character set and the maximum of 3 bytes for each character. When the innodb_large_prefix configuration option is enabled, the index key prefix length limit is raised to 3072 bytes for InnoDB tables that use DYNAMIC or COMPRESSED row format.

Attempting to use an index key prefix length that exceeds the limit returns an error. To avoid such errors in replication configurations, avoid enabling innodb_large_prefix on the master if it cannot also be enabled on slaves.

The limits that apply to index key prefixes also apply to full-column index keys.
Click to expand...

But if you database is not optimally designed as in case of FunnelFlux database table, then it has run up against InnoDB's column limit so you'd be in catch 22 potentially running up against index key prefix length limit or run into innodb column limit. Ideal solution is FunnelFlux restructuring their database table to adhere to InnoDB column limits.

But for your situation, you can for now change /etc/my.cnf

from
Code (Text):
innodb_default_row_format = dynamic
to
Code (Text):
innodb_default_row_format = compact
then restart mariadb mysql
Code (Text):
mysqlrestart

1121 · Dec 5, 2018

Thanks, George. This has not removed the error.
I have spent almost a week trying to get it working and now it is finally working. At this point if it works, it works, and maybe later I can do line by line add/remove to see which settings break it.
I put back the original centminmod my.cnf and Funnelflux seems to be working ok. Maybe when Funnelflux updates are issued I can temporarily switch back to the funnelflux my.cnf, but in the meantime I want to make sure the my.cnf is as optimized as possible with centminmod version. Thanks so much for your time and effort troubleshooting with me!

Log in / Register

Forums

Discover Centmin Mod today

MariaDB Error importing sql database: Row size too large (> 8126)

1121 New Member

eva2000 Administrator Staff Member

1121 New Member

eva2000 Administrator Staff Member

eva2000 Administrator Staff Member

1121 New Member

.

Log in / Register

Useful Searches

Discover Centmin Mod today

MariaDB Error importing sql database: Row size too large (> 8126)

1121 New Member

eva2000 Administrator Staff Member

1121 New Member

eva2000 Administrator Staff Member

eva2000 Administrator Staff Member

1121 New Member

.