MySQL Bugs: #76793: Different row size limitations in Anaconda and Barracuda file formats

Bug #76793	Different row size limitations in Anaconda and Barracuda file formats
Submitted:	22 Apr 2015 17:18	Modified:	2 Mar 2016 21:39
Reporter:	Hartmut Holzgraefe	Email Updates:
Status:	Verified	Impact on me:	None
Category:	MySQL Server: InnoDB storage engine	Severity:	S3 (Non-critical)
Version:	5.6.20, 5.6.24	OS:	Any
Assigned to:		CPU Architecture:	Any

Description:
When creating tables with Antelope format I can ... while with Barracuda formats I run into errors with the same table definition.

Take e.g.:

CREATE TABLE `a10` (
  `id` int(11) NOT NULL,
  `v1` varchar(10) DEFAULT NULL,
  `v2` varchar(10) DEFAULT NULL,
[...]
  `v727` varchar(10) DEFAULT NULL,
  `v728` varchar(10) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 ROW_FORMAT=DYNAMIC;

This table can be created in all row formats. But when using the
Barracuda formats DYNAMIC or COMPRESSED I can't do

  ALTER TABLE a10 ADD v729 varchar(100);

as this will result in

  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

while it works fine without warnings when using the older Antelope formats
(or not specifying a ROW_FORMAT at all)

Note that it says "is 8126" here.

Now I've tried this with all varchar sizes from 1 to 1000,
and I found an interesting thing between tests with VARCHAR(328)
and VARCHAR(329).

With 328 I can get up to

  CREATE TABLE `a328` (
    `id` int(11) NOT NULL,
    `v1` varchar(328) DEFAULT NULL,
  [...]
    `v197` varchar(328) DEFAULT NULL,
     PRIMARY KEY (`id`)
  ) ENGINE=InnoDB DEFAULT CHARSET=latin1 ROW_FORMAT=DYNAMIC

and when trying to add another VARCHAR(328) I'm getting the same 
error again:

  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

If I try this with 329 though I can only have 197 VARCHARs, too,
but when trying to add another one now I get:

  > alter table a329 add column v198 varchar(329);
  ERROR 1118 (42000): Row size too large. The maximum row size for the used table type, not counting BLOBs, is 65535. This includes storage overhead, check the manual. You have to change some columns to TEXT or BLOBs

Note that it now says "maximum row size ... is 65535", and not 
"is 8126" anymore. Which is the actual limit I'd expect to run
into with DYNAMIC Barracuda rows ...

When trying the 328 and 329 cases with Antelope formats I run
into the 65535 version of the error message in both cases.

This leads to the paradox situation that some tables are valid
with Antelope that can't be converted to Barracuda formats.

How to repeat:
Configuration:

  innodb_file_per_table=1;
  innodb_row_format=Barracuda;

Going to attach a test file soon

Suggested fix:
Make sure that table definitions that work with Antelope format 
don't suddenly fail with Barracuda (the case with the VARCHAR(10) 
test table).

And give consistent error messages with Barracuda format in cases 
where both formats fail to add another column.

test SQL script

Attachment: test.sql (application/sql, text), 0 bytes.

Hello Hartmut,

Thank you for the bug report.
Observed with 5.6.24, and with conf

innodb_file_per_table=1
innodb_file_format=Barracuda

-- with varchar(328) + v1..v198
-- 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.

but with Antelope(but innodb_file_format_max changed to Barracuda), got warning instead of error:
.
Query OK, 0 rows affected, 1 warning (0.02 sec)

mysql> show warnings;
+---------+------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Level   | Code | Message                                                                                                                                                                                             |
+---------+------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Warning |  139 | 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. |
+---------+------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

## With Antelope/Barracuda file formats
-- with varchar(329) + v1..v198
-- ERROR 1118 (42000): Row size too large. The maximum row size for the used table type, not counting BLOBs, is 65535. This includes storage overhead, check the manual. You have to change some columns to TEXT or BLOBs

Thanks,
Umesh

I sometimes got warnings with Antelope, sometimes (on other installations) things succeeded without warnings ... I didn't really figure out a pattern for that yet ...

Antelope doesn't support ROW_FORMAT=DYNAMIC, so it is falling back to ROW_FORMAT=COMPACT internally.

I'm curious what happens with barracuda file format with ROW_FORMAT=compact

Also synopsis should be "different row size" not "different table size".

row size, not table size -> fixed

This also happens between 5.6 using Antelope and 5.7 using Barracuda, and a complex query attempts to create a materialized temporary table for a FROM clause subquery.  That is particularly troublesome - the same query/subquery works fine in one version/storage method, and fails with the other.