MySQL Bugs: #53986: Column size limit seems to be unlimited at InnoDB Plugin 1.0.8 (DEGRADE?)

Bug #53986	Column size limit seems to be unlimited at InnoDB Plugin 1.0.8 (DEGRADE?)
Submitted:	26 May 2010 9:12	Modified:	27 May 2010 22:46
Reporter:	Yasufumi Kinoshita	Email Updates:
Status:	Duplicate	Impact on me:	None
Category:	MySQL Server: InnoDB Plugin storage engine	Severity:	S3 (Non-critical)
Version:	5.1.47 + 1.0.8	OS:	Any
Assigned to:	Assigned Account	CPU Architecture:	Any

Description:
plugin-1.0.8 cannot pass the following part of the innodb_misc1.test script because of succeeding the invalid create table command.

#
# Bug #21101 (Prints wrong error message if max row size is too large)
#
--error 1118
CREATE TABLE t1 (
        c01 CHAR(255), c02 CHAR(255), c03 CHAR(255), c04 CHAR(255),
        c05 CHAR(255), c06 CHAR(255), c07 CHAR(255), c08 CHAR(255),
        c09 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)
        ) ENGINE = InnoDB;

How to repeat:
<innodb-1.0.7 or before>

mysql> CREATE TABLE t1 (
    ->         c01 CHAR(255), c02 CHAR(255), c03 CHAR(255), c04 CHAR(255),
    ->         c05 CHAR(255), c06 CHAR(255), c07 CHAR(255), c08 CHAR(255),
    ->         c09 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)
    ->         ) ENGINE = InnoDB;
ERROR 1118 (42000): Row size too large. The maximum row size for the used table type, not counting BLOBs, is 8126. You have to change some columns to TEXT or BLOBs

(It is correct response)

<innodb-1.0.8>

mysql> CREATE TABLE t1 (
    ->         c01 CHAR(255), c02 CHAR(255), c03 CHAR(255), c04 CHAR(255),
    ->         c05 CHAR(255), c06 CHAR(255), c07 CHAR(255), c08 CHAR(255),
    ->         c09 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)
    ->         ) ENGINE = InnoDB;
Query OK, 0 rows affected (0.10 sec)

(It should not be succeeded)

Suggested fix:
I didn't find the causer change at 5.1.47 yet for now.

the fix for bug #50495 perhaps ?

Thank you for the bug report.

C:\DBS>c:\dbs\5.1\bin\mysql -uroot --port=3306 --prompt="mysql 5.1 >"
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 3
Server version: 5.1.48-Win X64-log Source distribution

Copyright (c) 2000, 2010, Oracle and/or its affiliates. All rights reserved.
This software comes with ABSOLUTELY NO WARRANTY. This is free software,
and you are welcome to modify and redistribute it under the GPL v2 license

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql 5.1 >use test
Database changed
mysql 5.1 >show variables like "%version%";
+-------------------------+---------------------+
| Variable_name           | Value               |
+-------------------------+---------------------+
| innodb_version          | 1.0.8               |
| protocol_version        | 10                  |
| version                 | 5.1.48-Win X64-log  |
| version_comment         | Source distribution |
| version_compile_machine | unknown             |
| version_compile_os      | Win64               |
+-------------------------+---------------------+
6 rows in set (0.00 sec)

mysql 5.1 >CREATE TABLE t1 (
    ->         c01 CHAR(255), c02 CHAR(255), c03 CHAR(255), c04 CHAR(255),
    ->         c05 CHAR(255), c06 CHAR(255), c07 CHAR(255), c08 CHAR(255),
    ->         c09 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)
    ->         ) ENGINE = InnoDB;
Query OK, 0 rows affected (0.17 sec)

mysql 5.1 >

Thank you.

Shane,
is it not bug?

OK. I understand.

+2010-04-20     The InnoDB Team
+
+       * dict/dict0crea.c, handler/ha_innodb.cc, include/trx0trx.h:
+       Fix Bug#50495 'Row size too large' for plugin, but works for
+       built-in InnoDB
+       Only check the record size at index creation time when
+       innodb_strict_mode is set or when ROW_FORMAT is DYNAMIC or COMPRESSED.

This is basically the same problem described in bug#50495 showing in the test suite. Once the problem will be fixed there the test should work as expected. As such - setting as a duplicate of bug#50495 as there is nothing specific to fix here.

My understanding of this is that it is not a duplicate of bug 50495 but a new issue caused by the fix for bug 50495.  The issue here is that the test case is bad for the plugin.  I fixed it locally by adding the --innodb_strict_mode option to innodb_misc1-master.opt which causes the innodb plugin to emit the expected error.

In the MySQL 5.1 tree, we have two separate suites for InnoDB tests: mysql-test/suite/innodb_plugin and mysql-test/suite/innodb. It sounds like this InnoDB test should be moved (and duplicated) to these suites, so that both the built-in InnoDB and the InnoDB Plugin will be tested. Or we could add

SET GLOBAL innodb_strict_mode=ON;

to the beginning of the test and allow the statement to fail in the built-in InnoDB.

The tests from innodb_misc1.test are already included in mysql-test/suite/innodb_plugin/innodb.test.