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:
None 
Category:MySQL Server: InnoDB storage engine Severity:S2 (Serious)
Version:5.7.10, 5.7.13 OS:Mac OS X
Assigned to: CPU Architecture:Any
Tags: ROW_FORMAT=DYNAMIC

[12 Jan 2016 22:06] Ryan Masse
Description:
We're trying to import data from a legacy system into a table.  This data has really wide rows and there's no way of changing the schema.

Now that the default row format has been changed to DYNAMIC we're bumping into row size errors.

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.

Now yes who the heck would ever create a table with 200+ columns but.. it happens :)

I've created an example and for convenience made every column a blob.  You'll see that I can't create a table with any more then 196 blobs in it.

It's my understanding that each column would have a 20byte pointer on the current page and the rest be stored off page.

My math: 200 columns X 20 bytes is well below the total row size limit of 8126

How to repeat:
Relevant config parameters:

sql_mode=NO_ENGINE_SUBSTITUTION
default-storage-engine          = InnoDB
innodb_file_format              = Barracuda
innodb-file-per-table           = 1
innodb-default-row-format       = DYNAMIC
innodb-log-file-size            = 512M

mysql> show variables like '%version%';
+-------------------------+------------------------------+
| Variable_name           | Value                        |
+-------------------------+------------------------------+
| innodb_version          | 5.7.10                       |
| protocol_version        | 10                           |
| slave_type_conversions  |                              |
| tls_version             | TLSv1,TLSv1.1                |
| version                 | 5.7.10                       |
| version_comment         | MySQL Community Server (GPL) |
| version_compile_machine | x86_64                       |
| version_compile_os      | osx10.9                      |
+-------------------------+------------------------------+
8 rows in set (0.00 sec)

CREATE TABLE `test`.`import1` (
`d_alarmformsigned` TEXT,
`d_statements_obtained` TEXT,
`e_sector_officer_reports_require` TEXT,
`d_tatical_worksheet_submitted_by` TEXT,
`e_mar_submitted` TEXT,
`d_unit_i_d` TEXT,
`e_officer` TEXT,
`e_driver` TEXT,
`e_ff1` TEXT,
`e_ff2` TEXT,
`e_ff3` TEXT,
`d_ff4` TEXT,
`d_police_on_scene` TEXT,
`d_badge` TEXT,
`e_ambulance_on_scene` TEXT,
`e_was_inspector_on_scene` TEXT,
`e_inspector` TEXT,
`e_smoke_alarms_required_in_dwell` TEXT,
`e_of_smoke_alarm_s_not_working` TEXT,
`e_smoke_alarm_compliant` TEXT,
`e_report_details_2_main` TEXT,
`d_owner` TEXT,
`d_incident` TEXT,
`e_auditors_name` TEXT,
`d_time_call_received` TEXT,
`d_time_first_unit_on_scene` TEXT,
`e_1st_unit_response` TEXT,
`day` TEXT,
`e_call_priority` TEXT,
`d_map_address` TEXT,
`e_group` TEXT,
`d_respondingfrom` TEXT,
`d_cpt_id` TEXT,
`e_grid` TEXT,
`e_nature_of_call` TEXT,
`d_street` TEXT,
`d_enroute` TEXT,
`d_cleared` TEXT,
`d_firstunit` TEXT,
`e_firstunitdistrict` TEXT,
`e_otherunitcomingfrom` TEXT,
`d_secondunitarriving` TEXT,
`d_secondunitarrtime` TEXT,
`e_workingfire` TEXT,
`d_property_loss2` TEXT,
`e_smoke_alarms_out` TEXT,
`d_publiced` TEXT,
`d_flyers` TEXT,
`d_ofchildren` TEXT,
`d_fireprograms` TEXT,
`d_delete_reviewed_by_auditor_dc` TEXT,
`d_amb_occur_no` TEXT,
`e_auditor_notes_to_report_writer` TEXT,
`d_amb_id` TEXT,
`d_pubedage` TEXT,
`e_dispatcher_reaction_time` TEXT,
`e_ff_reaction_time` TEXT,
`2ndunitarrivingafter1st` TEXT,
`d_total_call_tim_e_duration` TEXT,
`e_auditor_followup` TEXT,
`test` TEXT,
`e_notifya_chief` TEXT,
`e_hazmat` TEXT,
`d_hydro` TEXT,
`d_gas` TEXT,
`d_emailme` TEXT,
`d_other_natureof_call` TEXT,
`e_waiversigned` TEXT,
`d_related_report` TEXT,
`report_station` TEXT,
`report_district_chief` TEXT,
`d_address_cross` TEXT,
`e_nature_med` TEXT,
`e_nature_assist` TEXT,
`e_nature_chem` TEXT,
`e_nature_detect` TEXT,
`e_nature_elect` TEXT,
`e_nature_fire` TEXT,
`e_nature_alarm` TEXT,
`e_nature_odour` TEXT,
`e_nature_rescue` TEXT,
`e_nature_smoke` TEXT,
`e_nature_report` TEXT,
`d_location` TEXT,
`e_inscover` TEXT,
`d_tempadmin` TEXT,
`e_ems_prompt` TEXT,
`e_smoke_alarm_prompt` TEXT,
`e_station` TEXT,
`d_owneraddress` TEXT,
`d_railwaycar_id` TEXT,
`d_railroadname` TEXT,
`d_car_name` TEXT,
`e_multizorb` TEXT,
`e_additive_usage` TEXT,
`d_spill_material` TEXT,
`d_spillsource` TEXT,
`e_spill_details` TEXT,
`d_amount_of_material` TEXT,
`d_business_name` TEXT,
`e_company_contact_information` TEXT,
`d_marby` TEXT,
`d_inspectorreview` TEXT,
`e_whosdistrict` TEXT,
`e_batteries` TEXT,
`e_captemail_lookup` TEXT,
`login` TEXT,
`e_captain_email` TEXT,
`e_captains_note_to_a_c` TEXT,
`d_nature_of_call_categories` TEXT,
`d_reviewedbyac` TEXT,
`d_injuries` TEXT,
`e_acaddressed` TEXT,
`d_first_on_scene` TEXT,
`d_methodreceived` TEXT,
`d_call_cancelled_by_ambulance` TEXT,
`d_time_cancelled` TEXT,
`e_your_chief` TEXT,
`e_report_details_1_size_up` TEXT,
`e_report_details_3_closing` TEXT,
`e_naturereportnotes` TEXT,
`d_property_loss` TEXT,
`d_province_state2` TEXT,
`e_witnessstatement` TEXT,
`e_smokereason` TEXT,
`d_area_of_origin` TEXT,
`d_administrator_flag` TEXT,
`e_sector_reports_required` TEXT,
`d_sector_officer_reports_outstan` TEXT,
`d_ownerdob` TEXT,
`d_tenantdob` TEXT,
`e_monitoring_device_prompt` TEXT,
`e_4gasunitnumber` TEXT,
`e_tenant_contactinfo_fire` TEXT,
`d_tenant_dobstructure_detail` TEXT,
`d_tenant_contact_noin_structured` TEXT,
`e_water_usage` TEXT,
`incident_official` TEXT,
`d_hold_incident` TEXT,
`idate` TEXT,
`d_ffreactiontimefield` TEXT,
`e_related_reports_required` TEXT,
`d_ac_audit_notes` TEXT,
`e_administrative_auditor` TEXT,
`d_send_copy_to_ac_mailbox` TEXT,
`d_call_cancelled` TEXT,
`d_3_tenant_driver_information` TEXT,
`d_3_t_d_insurance` TEXT,
`d_1tdmore` TEXT,
`d_2tdmore` TEXT,
`e_4gasreading` TEXT,
`e_hydrantused` TEXT,
`e_hydrantusedaddress` TEXT,
`new_date_format` TEXT,
`e_crew` TEXT,
`d_call_received_to_on_scene_resp` TEXT,
`e_naturefiresub` TEXT,
`d_third_unit_arriving` TEXT,
`d_third_unit_arrival_time` TEXT,
`e_2nd_unit_response` TEXT,
`e_3rd_unit_response_time` TEXT,
`qu_nect_archive_reference` TEXT,
`d_smoke_alarm_form_received` TEXT,
`e_wuc` TEXT,
`e_related_run` TEXT,
`run_incident` TEXT,
`e_run_zone` TEXT,
`run_call_type` TEXT,
`run_location` TEXT,
`run_station` TEXT,
`run_comments` TEXT,
`run_benchmarks` TEXT,
`run_call_dispatched` TEXT,
`run_1st_unit_on_scene_time` TEXT,
`d_run_primary_key` TEXT,
`d_of_sector_officer_reports` TEXT,
`run_1st_unit_en_route` TEXT,
`run_1st_unit_on_scene` TEXT,
`run_2nd_unit_on_scene` TEXT,
`run_2nd_unit_on_scene_time` TEXT,
`run_3rd_unit_on_scene` TEXT,
`run_3rd_unit_on_scene_time` TEXT,
`run_unit_enroute_time` TEXT,
`e_inspectoremaillink` TEXT,
`run_time_call_received` TEXT,
`run_incident_date` TEXT,
`run_incident_time` TEXT,
`related_employee` TEXT,
`employee_allofficersemail` TEXT,
`employee_all_staff` TEXT,
`search_old_records` TEXT,
`e_related_run_notifications` TEXT,
`e_related_run_notifications2` TEXT,
`e_ff_reaction_time_reports` TEXT,
`e_greater_than_5_tonnes` TEXT,
`e_co_alarm` TEXT,
`file_attachment` TEXT) ROW_FORMAT=DYNAMIC;

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.
[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.