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] Umesh Shastry
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 16:19] Daniel Silva
Same problem here with 5.7.20 in windows.
Tried several workarounds, but none work.
[4 Jan 16:21] Daniel Silva
test case

Attachment: innbodb_error.sql (application/octet-stream, text), 9.14 KiB.