Bug #79941 | Row size too large new ROW_FORMAT=DYNAMIC | ||
---|---|---|---|
Submitted: | 12 Jan 2016 22:06 | Modified: | 11 Jul 2016 13:43 |
Reporter: | Ryan Masse | Email Updates: | |
Status: | Verified | Impact on me: | |
Category: | MySQL Server: InnoDB storage engine | Severity: | S2 (Serious) |
Version: | 5.7.10, 5.7.13 | OS: | MacOS |
Assigned to: | CPU Architecture: | Any | |
Tags: | ROW_FORMAT=DYNAMIC |
[12 Jan 2016 22:06]
Ryan Masse
[11 Jul 2016 13:43]
MySQL Verification Team
Thank you for the report and test case. Observed this with 5.7.13.
[21 Nov 2016 13:05]
Veronica Rossini
Do you tried to turn off the innodb_strict_mode? SET GLOBAL innodb_strict_mode = 0; and then try to import again. innodb_strict_mode is ON using MySQL >= 5.7.7, before was OFF. This mod may be not a solution, but just a workaround.
[10 Jan 2017 14:28]
Harald Weinreich
I have the same problem with MySQL 5.7.13. Adding: innodb_strict_mode = 0 does not solve the problem, the error "Row size too large (> 8126)" still occurs. The setting: innodb_file_per_table = 1 innodb_file_format = Barracuda still worked fine in mysql 5.6 but does not seem to have any effect in MySQL 5.7.13
[10 Jan 2017 14:56]
Harald Weinreich
I found a workaround for the problem! Adding: internal_tmp_disk_storage_engine = MyISAM innodb_file_per_table = ON innodb_file_format = Barracuda to the MySQL configuration file works. It seems that InnoDB has problems with the compressed format and Barracuda, but I'm no expert enough to understand the problem. I found the answer here: http://bugs.mysql.com/bug.php?id=77398 I can only agree that since Oracle's goal is to get rid of MyISAM, the "workaround" for this problem is probably not a good long term answer...
[10 Jan 2017 15:05]
Veronica Rossini
You have to choose the same innodb_file_format of the previous database, e.g. Antelope.
[10 Jan 2017 15:09]
Harald Weinreich
No, that does not work, then the error occurs again. Only Barracuda supports the COMPRESSED and DYNAMIC row formats as required. Anyway, as I wrote above, the setting works fine for mysql 5.7.16
[7 Jun 2017 7:16]
Artyom Konovalenko
I've hit the same bug. This is another test: CREATE TABLE _solid_primary_key ( `id` bigint(20) NOT NULL, `format` varchar(255) NOT NULL, `date` datetime NOT NULL, PRIMARY KEY (`format`,`id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8 ROW_FORMAT=COMPRESSED KEY_BLOCK_SIZE=1; Now try to alter this way: ALTER TABLE _solid_primary_key ADD column prefix INT(5) NOT NULL DEFAULT 7, DROP PRIMARY KEY, ADD PRIMARY KEY (prefix, format, id), ADD INDEX old_PK(format, id); ERROR 1118 (42000): Row size too large. The maximum row size for the used table type, not counting BLOBs, is 8126. This includes storage overhead, check the manual. You have to change some columns to TEXT or BLOBs There is no way to exceed 8126 bytes on new table.
[11 Sep 2017 15:42]
Robin Tong
Ryan, I know this bug was posted a long time ago now but where did you get to with this issue? I'm too in the same scenario with 200+ blob columns and finding that 196 seems to be the limit. Currently running on dev machine with macOS 10.12.6 and MySQL 5.7.13, all other MySQL settings appear to be that same as you reported. The maths here just don't add up according to the documentation, please can someones head any light on the underlying issue?
[21 Oct 2017 19:30]
Klaas Van Parys
I've also been struggling with this issue for hours, but I've found a solution that works for me. I'm switching laptops at work and I'm importing all my databases to the latest MySQL version. I set the in my config file: innodb_page_size = 32K MySQL 5.7.6 allows larger page sizes for InnoDB than 16K: https://dev.mysql.com/doc/refman/5.7/en/innodb-parameters.html#sysvar_innodb_page_size The reason I tried this was because of what I found in this blog: http://mysqlserverteam.com/externally-stored-fields-in-innodb/ ""In MySQL 5.6, the default row format of a table is COMPACT, so that’s what our t1 table is using. The default page size is 16K, so that’s also what we’re using. The maximum record size that can be stored in a 16K page using the COMPACT row format is 8126 bytes.""
[4 Jan 2018 16:19]
Daniel Silva
Same problem here with 5.7.20 in windows. Tried several workarounds, but none work.
[4 Jan 2018 16:21]
Daniel Silva
test case
Attachment: innbodb_error.sql (application/octet-stream, text), 9.14 KiB.
[4 May 2019 13:50]
Christian Roser
I see this sometimes on slaves of our hosting databases: mysql ((none))> show slave status\G *************************** 1. row *************************** Slave_IO_State: Waiting for master to send event Master_Host: masterhost Master_User: repl Master_Port: 3306 Connect_Retry: 60 Master_Log_File: mysql-bin.000146 Read_Master_Log_Pos: 269044252 Relay_Log_File: relay-bin.000391 Relay_Log_Pos: 669180677 Relay_Master_Log_File: mysql-bin.000145 Slave_IO_Running: Yes Slave_SQL_Running: No Replicate_Do_DB: Replicate_Ignore_DB: Replicate_Do_Table: Replicate_Ignore_Table: Replicate_Wild_Do_Table: Replicate_Wild_Ignore_Table: Last_Errno: 1118 Last_Error: Error '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.' on query. Default database: 'dbs59059'. Query: 'CREATE TABLE wp_commerce_manager_products ( id int unsigned auto_increment, category_id int unsigned NOT NULL, name text NOT NULL, price text NOT NULL, normal_price text NOT NULL, reference text NOT NULL, description text NOT NULL, keywords text NOT NULL, url text NOT NULL, thumbnail_url text NOT NULL, downloadable text NOT NULL, download_url text NOT NULL, instructions text NOT NULL, date datetime NOT NULL, date_utc datetime NOT NULL, custom_fields text NOT NULL, available_quantity text NOT NULL, sales_count int unsigned NOT NULL, refunds_count int unsigned NOT NULL, purchase_button_url text NOT NULL, purchase_button_text text NOT NULL, purchase_link_text text NOT NULL, order_confirmation_url text NOT NULL, orders_initial_status text NOT NULL, test_enabled text NOT NULL, client_account_re Skip_Counter: 0 Exec_Master_Log_Pos: 669180504 Relay_Log_Space: 1342828848 Until_Condition: None Until_Log_File: Until_Log_Pos: 0 Master_SSL_Allowed: No Master_SSL_CA_File: Master_SSL_CA_Path: Master_SSL_Cert: Master_SSL_Cipher: Master_SSL_Key: Seconds_Behind_Master: NULL Master_SSL_Verify_Server_Cert: No Last_IO_Errno: 0 Last_IO_Error: Last_SQL_Errno: 1118 Last_SQL_Error: Error '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.' on query. Default database: 'dbs59059'. Query: 'CREATE TABLE wp_commerce_manager_products ( id int unsigned auto_increment, category_id int unsigned NOT NULL, name text NOT NULL, price text NOT NULL, normal_price text NOT NULL, reference text NOT NULL, description text NOT NULL, keywords text NOT NULL, url text NOT NULL, thumbnail_url text NOT NULL, downloadable text NOT NULL, download_url text NOT NULL, instructions text NOT NULL, date datetime NOT NULL, date_utc datetime NOT NULL, custom_fields text NOT NULL, available_quantity text NOT NULL, sales_count int unsigned NOT NULL, refunds_count int unsigned NOT NULL, purchase_button_url text NOT NULL, purchase_button_text text NOT NULL, purchase_link_text text NOT NULL, order_confirmation_url text NOT NULL, orders_initial_status text NOT NULL, test_enabled text NOT NULL, client_account_re Replicate_Ignore_Server_Ids: Master_Server_Id: 183829010 Master_UUID: ac5f3836-58a8-11e9-8f97-001a4a05001a Master_Info_File: /var/lib/mysql/master.info SQL_Delay: 0 SQL_Remaining_Delay: NULL Slave_SQL_Running_State: Master_Retry_Count: 86400 Master_Bind: Last_IO_Error_Timestamp: Last_SQL_Error_Timestamp: 190504 11:26:45 Master_SSL_Crl: Master_SSL_Crlpath: Retrieved_Gtid_Set: ac5f3836-58a8-11e9-8f97-001a4a05001a:17522813-37672774 Executed_Gtid_Set: 33e75de8-e2ab-11e8-a11a-001a4a15001a:1-21660209, 3f72beae-e36c-11e8-907b-001a4a05001a:1-11409514, ac5f3836-58a8-11e9-8f97-001a4a05001a:1-37428658 Auto_Position: 1 Replicate_Rewrite_DB: Channel_Name: Master_TLS_Version: For whatever reason the table has been created on the master, but on the slave it cannot be created. When I dump the database and use the dump to restore it on another database on the same host it doesn't work either.
[2 May 2023 12:40]
MySQL Verification Team
Bug #110876 marked as duplicate of this one