Bug #47495 main.innodb-zip fails on Ubuntu9
Submitted: 21 Sep 2009 20:07 Modified: 12 Apr 2013 12:17
Reporter: Aleksandr Kuzminsky Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: Packaging Severity:S3 (Non-critical)
Version:5.1.39 OS:Other (Ubunty lenny x64_86)
Assigned to:
Tags: main.innodb-zip
Triage: Triaged: D3 (Medium) / R2 (Low) / E2 (Low)

[21 Sep 2009 20:07] Aleksandr Kuzminsky
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
[22 Sep 2009 6:31] Sveta Smirnova
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.
[23 Sep 2009 10:24] Aleksandr Kuzminsky
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
[23 Sep 2009 10:25] Aleksandr Kuzminsky
Description:
The test main.innodb-zip fails on Ubuntu lenny with 5.1.37 + InnoDB plugin 1.0.4
[23 Sep 2009 17:10] Valerii Kravchuk
This test passes with InnoDB plugin on recent 5.1.40 from bzr on Mac OS X.
[23 Sep 2009 18:22] Aleksandr Kuzminsky
I'd like to emphasize the bug is ubuntu(debian?) specific.
The test passes on Centos 4 and 5.
[24 Sep 2009 6:31] Susanne Ebrecht
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
[25 Sep 2009 14:04] Mikhail Izioumtchenko
does this test succeeds with 1.0.3? Does it succeed on another Linux? I think it should fail consistently.
[28 Sep 2009 6:03] Marko Mäkelä
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.
[29 Sep 2009 11:10] Susanne Ebrecht
Marko,

what is the workaround here?
[29 Sep 2009 12:24] Marko Mäkelä
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).
[5 Nov 2009 20:58] James Day
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?
[19 Nov 2009 9:20] Norbert Tretkowski
Looks like this issue was fixed in InnoDB 1.0.5, I'm unable to reproduce it with MySQL 5.1.41.
[19 Nov 2009 9:44] Marko Mäkelä
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.
[2 Dec 2009 8:39] Marko Mäkelä
One more comment. Apparently, there have been three revisions to zlib in 2006, but none of them seem to be available from http://zlib.net or http://gzip.org/zlib, the upstream sites:

1.2.3.3 (2 October 2006)
1.2.3.2 (3 September 2006)
1.2.3.1 (16 August 2006)

The definition of compressBound() was already changed in version 1.2.3.1:

- Fix compressBound(), was low for some pathological cases [Fearnley]

 uLong ZEXPORT compressBound (sourceLen)
     uLong sourceLen;
 {
-    return sourceLen + (sourceLen >> 12) + (sourceLen >> 14) + 11;
+    return sourceLen + (sourceLen >> 12) + (sourceLen >> 14) +
+           (sourceLen >> 25) + 13;
 }

Apparently, that is Matthew Fearnley. I could not find anything beyond that. In InnoDB Plugin, sourceLen >> 25 will always be 0, and this function will return 2 more than previously. The sourceLen in InnoDB Plugin should never exceed UNIV_PAGE_SIZE, or 16384.

I suggest that the bundled zlib in MySQL be upgraded to 1.2.3.1 or later. Otherwise, an InnoDB assertion failure might occur, albeit at a very small probability.

I wonder where the zlib_1.2.3.3.dfsg.orig.tar.gz in Debian was obtained. Other GNU/Linux distributors seem to be copying Debian here.
[9 Dec 2009 13:55] Marko Mäkelä
The test suite failure was fixed in the InnoDB Plugin version 1.0.5, making it tolerate both definitions of compressBound(): the buggy one in 1.2.3 and earlier and the corrected one in 1.2.3.1 and later.

I am assigning this bug away to the MySQL packaging team. I believe that the bundled zlib in MySQL should be updated to the current version, or at least 1.2.3.1. The currently bundled 1.2.3 contains a buggy definition of compressBound(), which might result in run-time assertion failures.
[14 Dec 2009 3:14] Yasufumi Kinoshita
Aleksandr,

Is it mainly from your building options?

They are result of ldd at my Linux box

http://www.percona.com/mysql/xtradb/5.1.39-8/deb/lenny/x86_64/mysql-server-5.1_5.1.39-xtra...
/usr/sbin/mysqld
./mysqld: /lib64/libz.so.1: no version information available (required by ./mysqld)
./mysqld: /lib64/libc.so.6: version `GLIBC_2.7' not found (required by ./mysqld)
        libpthread.so.0 => /lib64/libpthread.so.0 (0x00002b575dac5000)
        libz.so.1 => /lib64/libz.so.1 (0x00002b575dce0000)
        ~~~~~~~~~
        libwrap.so.0 => /lib64/libwrap.so.0 (0x00002b575def5000)
        libdl.so.2 => /lib64/libdl.so.2 (0x00002b575e100000)
        libcrypt.so.1 => /lib64/libcrypt.so.1 (0x00002b575e304000)
        libnsl.so.1 => /lib64/libnsl.so.1 (0x00002b575e53d000)
        libm.so.6 => /lib64/libm.so.6 (0x00002b575e755000)
        libc.so.6 => /lib64/libc.so.6 (0x00002b575e9a8000)
        /lib64/ld-linux-x86-64.so.2 (0x00002b575d8a8000)

http://www.percona.com/mysql/xtradb/5.1.39-8/binary/percona-xtradb-1.0.4-8-5.1.39.x86_64.t...
plugin/ha_innodb.so
        libpthread.so.0 => /lib64/libpthread.so.0 (0x00002b337273d000)
        libcrypt.so.1 => /lib64/libcrypt.so.1 (0x00002b3372958000)
        libnsl.so.1 => /lib64/libnsl.so.1 (0x00002b3372b91000)
        libm.so.6 => /lib64/libm.so.6 (0x00002b3372da9000)
        libgcc_s.so.1 => /lib64/libgcc_s.so.1 (0x00002b3372ffc000)
        libc.so.6 => /lib64/libc.so.6 (0x00002b337320a000)
        /lib64/ld-linux-x86-64.so.2 (0x0000555555554000)

http://www.percona.com/mysql/xtradb/5.1.39-8/RPM/rhel5/MySQL-server-percona-5.1.39-8.rhel5...
/usr/sbin/mysqld
        libpthread.so.0 => /lib64/libpthread.so.0 (0x00002b3aba862000)
        libdl.so.2 => /lib64/libdl.so.2 (0x00002b3abaa7d000)
        libcrypt.so.1 => /lib64/libcrypt.so.1 (0x00002b3abac81000)
        libnsl.so.1 => /lib64/libnsl.so.1 (0x00002b3abaebb000)
        libm.so.6 => /lib64/libm.so.6 (0x00002b3abb0d2000)
        libc.so.6 => /lib64/libc.so.6 (0x00002b3abb325000)
        /lib64/ld-linux-x86-64.so.2 (0x00002b3aba645000)

Why don't you use bundled libz only for Debian?
Did you use "--with-zlib-dir=bundled" for all configure option?
[24 Dec 2010 7:45] Marko Mäkelä
I just learned that the zlib packages are available from http://zlib.net/fossils/. As of this writing, the most recent release is zlib-1.2.4.5.tar.gz (2010-04-18).
[12 Apr 2013 12:17] Yngve Svendsen
The issue was worked around in InnoDB plugin 1.0.5. We will also evaluate whether or not to also move to a newer zlib.
[12 Apr 2013 13:48] Paul Dubois
Changes were made for test suite. No changelog entry needed.