MySQL Bugs: #47495: main.innodb-zip fails on Ubuntu9

Bug #47495	main.innodb-zip fails on Ubuntu9
Submitted:	21 Sep 22:07	Modified:	24 Sep 8:31
Reporter:	Aleksandr Kuzminsky
Status:	Verified
Category:	Server: InnoDB Plugin	Severity:	S3 (Non-critical)
Version:	5.1.39	OS:	Other (Ubunty lenny x64_86)
Assigned to:	Marko Mäkelä	Target Version:
Tags:	main.innodb-zip
Triage:	Needs Triage: D2 (Serious) / R6 (Needs Assessment) / E6 (Needs Assessment)

Description:
The test main.innodb-zip fails on Ubuntu lenny with 5.1.37 + InnoDB plugin 1.0.4

MySQL Version 5.1.37
Checking supported features...
 - skipping ndbcluster, mysqld not compiled with ndbcluster
 - skipping SSL, mysqld not compiled with SSL
Collecting tests...
vardir: /var/lib/buildbot/xtradb/Ubuntu_x86_64/mysql-5.1.37/mysql-test/var
Checking leftover processes...
Removing old var directory...
Creating var directory
'/var/lib/buildbot/xtradb/Ubuntu_x86_64/mysql-5.1.37/mysql-test/var'...
Installing system database...
Using server port 53573
worker[1] Using MTR_BUILD_THREAD 300, with reserved ports 13000..13009

==============================================================================

TEST                                      RESULT   TIME (ms)
------------------------------------------------------------

main.innodb-zip                          [ fail ]
        Test ended at 2009-09-21 13:05:12

CURRENT_TEST: main.innodb-zip
mysqltest: At line 108: query 'CREATE TABLE t1(c TEXT, PRIMARY KEY (c(439)))
ENGINE=InnoDB ROW_FORMAT=COMPRESSED KEY_BLOCK_SIZE=1 CHARSET=ASCII' failed: 1118: 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

The result from queries just before the failure was:
< snip >
drop table t1,t2;
SET SESSION innodb_strict_mode = off;
CREATE TABLE t1(
c TEXT NOT NULL, d TEXT NOT NULL,
PRIMARY KEY (c(767),d(767)))
ENGINE=InnoDB ROW_FORMAT=COMPRESSED KEY_BLOCK_SIZE=1 CHARSET=ASCII;
ERROR 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
CREATE TABLE t1(
c TEXT NOT NULL, d TEXT NOT NULL,
PRIMARY KEY (c(767),d(767)))
ENGINE=InnoDB ROW_FORMAT=COMPRESSED KEY_BLOCK_SIZE=2 CHARSET=ASCII;
ERROR 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
CREATE TABLE t1(
:mysql-test-run: *** ERROR: there were failing test cases
c TEXT NOT NULL, d TEXT NOT NULL,
PRIMARY KEY (c(767),d(767)))
ENGINE=InnoDB ROW_FORMAT=COMPRESSED KEY_BLOCK_SIZE=4 CHARSET=ASCII;
drop table t1;
CREATE TABLE t1(c TEXT, PRIMARY KEY (c(440)))
ENGINE=InnoDB ROW_FORMAT=COMPRESSED KEY_BLOCK_SIZE=1 CHARSET=ASCII;
ERROR 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

More results from queries before failure can be found in
/var/lib/buildbot/xtradb/Ubuntu_x86_64/mysql-5.1.37/mysql-test/var/log/innodb-zip.log

Warnings from just before the error:
Error 1118 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

 - saving
'/var/lib/buildbot/xtradb/Ubuntu_x86_64/mysql-5.1.37/mysql-test/var/log/main.innodb-zip/'
to
'/var/lib/buildbot/xtradb/Ubuntu_x86_64/mysql-5.1.37/mysql-test/var/log/main.innodb-zip/'
------------------------------------------------------------
The servers were restarted 0 times
Spent 0.000 of 1 seconds executing testcases

Failed 1/1 tests, 0.00% were successful.

Failing test(s): main.innodb-zip

The log files in var/log may give you some hint of what went wrong.

If you want to report this error, please read first the documentation
at http://dev.mysql.com/doc/mysql/en/mysql-test-suite.html

How to repeat:
1. compile with ./configure --with-plugins=innobase && make all
2. Run ./mtr innodb-zip

Thank you for the report.

But version 5.1.37 is a bit old. Please upgrade to current version 5.1.39, try with it
and inform us if problem still exists.

Thanks for feedback!
I'm trying on 5.1.39 with embedded innodb and innodb_plugin.
The result is pretty the same:

Logging: ./mtr  innodb-zip
MySQL Version 5.1.39
Checking supported features...
 - skipping ndbcluster, mysqld not compiled with ndbcluster
 - skipping SSL, mysqld not compiled with SSL
Collecting tests...
vardir: /var/lib/buildbot/tmp/mysql-5.1.39/mysql-test/var
Checking leftover processes...
Removing old var directory...
Creating var directory '/var/lib/buildbot/tmp/mysql-5.1.39/mysql-test/var'...
Installing system database...
Using server port 36251

==============================================================================

TEST                                      RESULT   TIME (ms)
------------------------------------------------------------

worker[1] Using MTR_BUILD_THREAD 300, with reserved ports 13000..13009
innodb.innodb-zip                        [ skipped ]  Test requires: 'true'
innodb.innodb-zip 'InnoDB plugin'        [ fail ]
        Test ended at 2009-09-23 03:21:53

CURRENT_TEST: innodb.innodb-zip
mysqltest: At line 109: query 'CREATE TABLE t1(c TEXT, PRIMARY KEY (c(439)))
ENGINE=InnoDB ROW_FORMAT=COMPRESSED KEY_BLOCK_SIZE=1 CHARSET=ASCII' failed: 1118: Row
size too large. The maximum row size for the used tab
le type, not counting BLOBs, is 8126. You have to change some columns to TEXT or BLOBs

The result from queries just before the failure was:
< snip >
drop table t1,t2;
SET SESSION innodb_strict_mode = off;
CREATE TABLE t1(
c TEXT NOT NULL, d TEXT NOT NULL,
PRIMARY KEY (c(767),d(767)))
ENGINE=InnoDB ROW_FORMAT=COMPRESSED KEY_BLOCK_SIZE=1 CHARSET=ASCII;
ERROR 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
CREATE TABLE t1(
c TEXT NOT NULL, d TEXT NOT NULL,
PRIMARY KEY (c(767),d(767)))
ENGINE=InnoDB ROW_FORMAT=COMPRESSED KEY_BLOCK_SIZE=2 CHARSET=ASCII;
ERROR 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
CREATE TABLE t1(
c TEXT NOT NULL, d TEXT NOT NULL,
PRIMARY KEY (c(767),d(767)))
ENGINE=InnoDB ROW_FORMAT=COMPRESSED KEY_BLOCK_SIZE=4 CHARSET=ASCII;
drop table t1;
CREATE TABLE t1(c TEXT, PRIMARY KEY (c(440)))
ENGINE=InnoDB ROW_FORMAT=COMPRESSED KEY_BLOCK_SIZE=1 CHARSET=ASCII;
ERROR 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

More results from queries before failure can be found in
/var/lib/buildbot/tmp/mysql-5.1.39/mysql-test/var/log/innodb-zip.log

Warnings from just before the error:
Error 1118 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

 - saving '/var/lib/buildbot/tmp/mysql-5.1.39/mysql-test/var/log/innodb.innodb-zip-InnoDB
plugin/' to
'/var/lib/buildbot/tmp/mysql-5.1.39/mysql-test/var/log/innodb.innodb-zip-InnoDB plugin/'
------------------------------------------------------------
The servers were restarted 1 times
Spent 0.000 of 4 seconds executing testcases

Failed 1/1 tests, 0.00% were successful.

Failing test(s): innodb.innodb-zip

The log files in var/log may give you some hint of what went wrong.

If you want to report this error, please read first the documentation
at http://dev.mysql.com/doc/mysql/en/mysql-test-suite.html

mysql-test-run: *** ERROR: there were failing test cases

Description:
The test main.innodb-zip fails on Ubuntu lenny with 5.1.37 + InnoDB plugin 1.0.4

This test passes with InnoDB plugin on recent 5.1.40 from bzr on Mac OS X.

I'd like to emphasize the bug is ubuntu(debian?) specific.
The test passes on Centos 4 and 5.

Verified as described.

$ ./mysql-test-run innodb-zip
Logging: ./mysql-test-run  innodb-zip
090924  8:28:07 [Note] Plugin 'FEDERATED' is disabled.
090924  8:28:07 [Note] Plugin 'ndbcluster' is disabled.
090924  8:28:07 [Warning] Forcing shutdown of 2 plugins
MySQL Version 5.1.40
Checking supported features...
 - using ndbcluster when necessary, mysqld supports it
 - SSL connections supported
 - binaries are debug compiled
Collecting tests...
vardir: /home/miracee/mysql51bzr/mysql-test/var
Checking leftover processes...
Removing old var directory...
Creating var directory '/home/miracee/mysql51bzr/mysql-test/var'...
Installing system database...
Using server port 45087
worker[1] Using MTR_BUILD_THREAD 300, with reserved ports 13000..13009

==============================================================================

TEST                                      RESULT   TIME (ms)
------------------------------------------------------------

innodb.innodb-zip                        [ skipped ]  Test requires: 'true'
innodb.innodb-zip 'InnoDB plugin'        [ fail ]
        Test ended at 2009-09-24 08:28:15

CURRENT_TEST: innodb.innodb-zip
mysqltest: At line 109: query 'CREATE TABLE t1(c TEXT, PRIMARY KEY (c(439)))
ENGINE=InnoDB ROW_FORMAT=COMPRESSED KEY_BLOCK_SIZE=1 CHARSET=ASCII' failed: 1118: 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

The result from queries just before the failure was:
< snip >
drop table t1,t2;
SET SESSION innodb_strict_mode = off;
CREATE TABLE t1(
c TEXT NOT NULL, d TEXT NOT NULL,
PRIMARY KEY (c(767),d(767)))
ENGINE=InnoDB ROW_FORMAT=COMPRESSED KEY_BLOCK_SIZE=1 CHARSET=ASCII;
ERROR 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
CREATE TABLE t1(
c TEXT NOT NULL, d TEXT NOT NULL,
PRIMARY KEY (c(767),d(767)))
ENGINE=InnoDB ROW_FORMAT=COMPRESSED KEY_BLOCK_SIZE=2 CHARSET=ASCII;
ERROR 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
CREATE TABLE t1(
c TEXT NOT NULL, d TEXT NOT NULL,
PRIMARY KEY (c(767),d(767)))
ENGINE=InnoDB ROW_FORMAT=COMPRESSED KEY_BLOCK_SIZE=4 CHARSET=ASCII;
drop table t1;
CREATE TABLE t1(c TEXT, PRIMARY KEY (c(440)))
ENGINE=InnoDB ROW_FORMAT=COMPRESSED KEY_BLOCK_SIZE=1 CHARSET=ASCII;
ERROR 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

More results from queries before failure can be found in
/home/miracee/mysql51bzr/mysql-test/var/log/innodb-zip.log

Warnings from just before the error:
Error 1118 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

 - saving '/home/miracee/mysql51bzr/mysql-test/var/log/innodb.innodb-zip-InnoDB plugin/'
to '/home/miracee/mysql51bzr/mysql-test/var/log/innodb.innodb-zip-InnoDB plugin/'
------------------------------------------------------------
The servers were restarted 1 times
Spent 0.000 of 8 seconds executing testcases

Failed 1/1 tests, 0.00% were successful.

Failing test(s): innodb.innodb-zip

The log files in var/log may give you some hint of what went wrong.

If you want to report this error, please read first the documentation
at http://dev.mysql.com/doc/mysql/en/mysql-test-suite.html

mysql-test-run: *** ERROR: there were failing test cases

does this test succeeds with 1.0.3? Does it succeed on another Linux? I think it should
fail consistently.

Apparently, the definition of the zlib function compressBound() has been slightly changed
recently. Using the Debian packaged zlib 1.2.3.3, the test passes when the maximum
on-page user record length is reduced by one byte:

Index: mysql-test/innodb-zip.test
===================================================================
--- mysql-test/innodb-zip.test	(revision 5921)
+++ mysql-test/innodb-zip.test	(revision 5922)
@@ -105,7 +105,7 @@
 --error ER_TOO_BIG_ROWSIZE
 CREATE TABLE t1(c TEXT, PRIMARY KEY (c(440)))
 ENGINE=InnoDB ROW_FORMAT=COMPRESSED KEY_BLOCK_SIZE=1 CHARSET=ASCII;
-CREATE TABLE t1(c TEXT, PRIMARY KEY (c(439)))
+CREATE TABLE t1(c TEXT, PRIMARY KEY (c(438)))
 ENGINE=InnoDB ROW_FORMAT=COMPRESSED KEY_BLOCK_SIZE=1 CHARSET=ASCII;
 INSERT INTO t1 VALUES(REPEAT('A',512)),(REPEAT('B',512));
 DROP TABLE t1;

Is this the case? This problem was originally reported at
http://forums.innodb.com/read.php?3,643,643 on the InnoDB forum. I am quoting my post
there: “The code assumes that compressBound() be an accurate upper bound. If it is too
low, modifications may occasionally fail. It is not a good idea to make KEY_BLOCK_SIZE
too low anyway. If the table is read-only, you are probably better off with compressed
MyISAM tables. If the table is updated, a ‘too large’ compressed page size will make
InnoDB run faster, because the page does not have to be recompressed after every
operation.”

InnoDB tablespace compression inherently depends on the zlib implementation. We have to
find a balance between file compatibility (compressBound() or compression format not
changing) and maintainability (using the upstream zlib). If we froze the zlib version in
MySQL/InnoDB instead of relying on dynamic linking, we could perhaps guarantee that
compressBound() will not change, but we would also have to take the burden of dealing
with zlib updates and security fixes ourselves.

Marko,

what is the workaround here?

The work-around is to specify a bigger KEY_BLOCK_SIZE. Choosing a too small KEY_BLOCK_SIZE
will badly hurt performance, because every update or insert will lead to an attempt to
compress the page (potentially, splitting and compressing multiple pages).

Marko, do you expect this limit to apply and limit the number of columns even where
row_format=compressed and strict mode are not in use?

We have an example where for a non-compressed table the plugin rejects the length while
the built in version doesn't. Built in accepts larger unless the limit is actually
exceeded when the data is stored in the row, plugin rejects when it's possible to go over
the limit.

I'm wondering whether this is deliberately stricter enforcement or unintended?

Looks like this issue was fixed in InnoDB 1.0.5, I'm unable to reproduce it with MySQL
5.1.41.

James, at first it was deliberately stricter enforcement. The innodb_strict_mode was added
later during the development, so that InnoDB Plugin would ignore some cases and allow old
SQL dumps to be imported. Do you happen to have the CREATE TABLE statement?

Norbert, the underlying issue has NOT been fixed. What I did was that I adjusted the
maximum row length by one byte in innodb-zip.test, so that it will pass with both old and
new definitions of zlib compressBound().

I think that we should address this bug in the documentation.  It is a bad idea to
configure the compressed page size too small, anyway.

In theory, we could bundle a frozen version of zlib, but that would create lots of extra
work (checking updates and porting at least security fixes) for little gain.

This situation is analogous to using external character set collation libraries: whenever
a collation is changed, any index whose key order is affected by the change will appear
corrupted. (I remember several InnoDB corruption reports that were caused by collation
changes.)

Only in the case of zlib, the ramifications are softer, provided that zlib decompression
will never be changed in a way that would break old files. The effect of this bug is that
InnoDB Plugin will merely deny certain DDL or DML operations when the compressed page size
is unreasonably small.