Bug #47012 archive tables are not upgradeable, and server crashes on any access
Submitted: 31 Aug 2009 11:05 Modified: 12 Mar 2010 17:35
Reporter: Shane Bester (Platinum Quality Contributor) Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: Archive storage engine Severity:S1 (Critical)
Version:5.1.37, 5.1.39 OS:Any
Assigned to: Anurag Shekhar CPU Architecture:Any
Tags: regression, valgrind
Triage: Triaged: D1 (Critical)

[31 Aug 2009 11:05] Shane Bester
Description:
archive tables created in 5.0.x are not readable by 5.1.x.  'show create table' even crashes the server, and check table reports corrupt, repair table reports ok.

there is memory corruption when accessing these 5.0.x archive tables under 5.1.39.

mysqld.exe!CheckBytes()[dbgheap.c:1654]
mysqld.exe!_free_dbg_nolock()[dbgheap.c:1314]
mysqld.exe!_free_dbg()[dbgheap.c:1220]
mysqld.exe!free()[dbgheap.c:1178]
mysqld.exe!my_no_flags_free()[my_malloc.c:59]
mysqld.exe!ha_archive::free_share()[ha_archive.cc:411]
mysqld.exe!ha_archive::open()[ha_archive.cc:498]
mysqld.exe!handler::ha_open()[handler.cc:2059]
mysqld.exe!open_table_from_share()[table.cc:1881]
mysqld.exe!open_unireg_entry()[sql_base.cc:3911]
mysqld.exe!open_table()[sql_base.cc:2907]
mysqld.exe!open_tables()[sql_base.cc:4572]
mysqld.exe!open_normal_and_derived_tables()[sql_base.cc:5028]
mysqld.exe!mysqld_show_create()[sql_show.cc:598]
mysqld.exe!mysql_execute_command()[sql_parse.cc:2919]
mysqld.exe!mysql_parse()[sql_parse.cc:5931]
mysqld.exe!dispatch_command()[sql_parse.cc:1213]
mysqld.exe!do_command()[sql_parse.cc:854]
mysqld.exe!handle_one_connection()[sql_connect.cc:1127]
mysqld.exe!pthread_start()[my_winthread.c:87]
mysqld.exe!_callthreadstart()[thread.c:293]
mysqld.exe!_threadstart()[thread.c:277]

How to repeat:
in 5.0.86 create a table:

drop table if exists t1;
create table t1(a varchar(255),b int)engine=archive;
insert into t1 values (repeat('a',255),10);
insert into t1 values (repeat('b',255),1);
insert into t1 select t1.a,t1.b from t1,t1 a,t1 b,t1 c,t1 d;

Now shutdown and copy the t1.* files to 5.1.39.
Startup 5.1.39 and run:

show create table t1;
check table t1 extended;
repair table t1 extended;

run 5.1.39 under valgrind if needed, to catch a memory corruption if crash doesn't happen, also try full debug build
[31 Aug 2009 11:08] Shane Bester
full valgrind output

Attachment: bug47012_5.1.39_valgrind_output.txt (text/plain), 27.50 KiB.

[31 Aug 2009 15:44] Shane Bester
note: running mysql_upgrade does not fix the problem either, since it just tells all archive tables are corrupt and cannot be repaired.  the only choice users have is to mysqldump archive tables from 5.0 and import them into 5.1..
[12 Sep 2009 12:29] Shane Bester
come to think of it, i now see crashes without even upgrading a server.
just normal replication from 5.1.38->5.1.38, the slave crashes after a while:

mysqld-debug.exe!_free_dbg_nolock()[dbgheap.c:1279]
mysqld-debug.exe!_free_dbg()[dbgheap.c:1220]
mysqld-debug.exe!free()[dbgheap.c:1179]
mysqld-debug.exe!my_no_flags_free()[my_malloc.c:60]
mysqld-debug.exe!ha_archive::free_share()[ha_archive.cc:413]
mysqld-debug.exe!ha_archive::close()[ha_archive.cc:561]
mysqld-debug.exe!closefrm()[table.cc:1962]
mysqld-debug.exe!intern_close_table()[sql_base.cc:781]
mysqld-debug.exe!free_cache_entry()[sql_base.cc:803]
mysqld-debug.exe!my_hash_delete()[hash.c:552]
mysqld-debug.exe!remove_table_from_cache()[sql_base.cc:8492]
mysqld-debug.exe!lock_table_name()[lock.cc:1055]
mysqld-debug.exe!lock_table_names()[lock.cc:1137]
mysqld-debug.exe!lock_table_names_exclusively()[lock.cc:1175]
mysqld-debug.exe!mysql_rm_table_part2()[sql_table.cc:1845]
mysqld-debug.exe!mysql_rm_table()[sql_table.cc:1749]
mysqld-debug.exe!mysql_execute_command()[sql_parse.cc:3352]
mysqld-debug.exe!mysql_parse()[sql_parse.cc:5935]
mysqld-debug.exe!dispatch_command()[sql_parse.cc:1215]
mysqld-debug.exe!do_command()[sql_parse.cc:854]
mysqld-debug.exe!handle_one_connection()[sql_connect.cc:1127]
mysqld-debug.exe!pthread_start()[my_winthread.c:85]
mysqld-debug.exe!_callthreadstart()[thread.c:295]
mysqld-debug.exe!_threadstart()[thread.c:277]
kernel32.dll!BaseThreadStart()
[29 Sep 2009 10:42] James Day
Here's a stack dump from a crash while running mysql_upgrade from '5.0.58-enterprise-gpl-log' to '5.1.34sp1-enterprise-gpl-advanced-log'  

/usr/sbin/mysqld(my_print_stacktrace+0x2e)[0x83b0ce]
/usr/sbin/mysqld(handle_segfault+0x322)[0x5b8ae2]
/lib64/libpthread.so.0[0x2ac44df470b0]
/lib64/libc.so.6(memcpy+0x32)[0x2ac44e56e182]
/usr/sbin/mysqld(_ZN10Field_blob4packEPhPKhjb+0xa3)[0x598e33]
/usr/sbin/mysqld(_ZN10ha_archive8pack_rowEPh+0xd6)[0x711c06]
/usr/sbin/mysqld(_ZN10ha_archive14real_write_rowEPhP11azio_stream+0x1e)[0x711cbe]
/usr/sbin/mysqld(_ZN10ha_archive8optimizeEP3THDP15st_ha_check_opt+0x12a)[0x711e4a]
/usr/sbin/mysqld(_ZN10ha_archive6repairEP3THDP15st_ha_check_opt+0x14)[0x7102a4]
/usr/sbin/mysqld(_ZN10ha_archive16check_and_repairEP3THD+0x34)[0x7103d4]
/usr/sbin/mysqld(_ZN7handler19ha_check_and_repairEP3THD+0x5c)[0x69123c]
/usr/sbin/mysqld[0x6039dc]
/usr/sbin/mysqld(_Z10open_tableP3THDP10TABLE_LISTP11st_mem_rootPbj+0x61b)[0x604f3b]
/usr/sbin/mysqld(_Z11open_tablesP3THDPP10TABLE_LISTPjj+0x48a)[0x605bda]
/usr/sbin/mysqld(_Z30open_normal_and_derived_tablesP3THDP10TABLE_LISTj+0x1e)[0x605dde]
/usr/sbin/mysqld(_Z18mysqld_list_fieldsP3THDP10TABLE_LISTPKc+0x22)[0x6b2f42]
/usr/sbin/mysqld(_Z16dispatch_command19enum_server_commandP3THDPcj+0x787)[0x5cb617]
/usr/sbin/mysqld(_Z10do_commandP3THD+0xe4)[0x5cc3c4]
/usr/sbin/mysqld(handle_one_connection+0x6e9)[0x5bf809]
/lib64/libpthread.so.0[0x2ac44df40143]
/lib64/libc.so.6(__clone+0x6d)[0x2ac44e5b852d]
Trying to get some variables.
Some pointers may be invalid and cause the dump to abort...
thd->query at 0xf65f478 = 
thd->thread_id=5
thd->killed=NOT_KILLED
[9 Oct 2009 5:25] Shane Bester
i'm busy writing an archive engine stress tester, so that it may be run in addition to the simple testcases on this bug report. it will do far more extensive testing
[9 Oct 2009 6:48] Shane Bester
see top of file for host, user, threads. this testcase will cause crash/hang/4GB memory allocations, and valgrind errors

Attachment: bug47012.c (text/x-csrc), 146.67 KiB.

[9 Oct 2009 6:50] Shane Bester
the bug47012.c testcase should run for at least an hour against a server running under valgrind and yield no warnings or errors before we may consider ARCHIVE engine to be usable again
[16 Oct 2009 11:23] Anurag Shekhar
archive engine in mysql 5.1 uses azlib (modified version of gzlib). azlib and v
gzlib being incompatible the magic number in azlib is changed. When azlib encounters gzlib magic number (table created in 5.0) it fails to open the file 
and in debug version it causes a crash (due to DEBUG_ASSERT).

I feel the upgrade script should update the file with correct magic number and other information, such as header info, in the archive file so that azlib will 
able to handle the file. When attempt is made to open older file, server 
should return error with message advising user to run upgrade script.
[16 Oct 2009 11:58] Shane Bester
so you're saying that 'check table for upgrade' should work on these 5.0 archive tables and that 'repair table' should fix them ?
[16 Oct 2009 12:07] Anurag Shekhar
I think thats only way to get 5.0 archive table working with 5.1
unless we want some kind of auto upgrade (performed while opening
the file), as 5.1 archive engine needs the new header info missing
in 5.0 tables.
[23 Oct 2009 11:57] Bugs System
A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/87934

3192 Anurag Shekhar	2009-10-23
      Bug #47012 archive tables are not upgradeable, and server crashes 
                 on any access
      
      Archive engine for 5.1 (and latter) version uses a modified 
      version of zlib (azlib). These two version are incompatible
      so a proper upgrade is needed before tables created in 5.0 
      can be used reliable.
      
      This upgrade can be performed using repair. But due to lack 
      of test and close proximity with GA dates this patch addressed
      only the crashing issue. Any attempt to repair will be  blocked.
      
      Eventually repair can be allowed to run through (which will also
      cause an upgrade from older version to newer) but only after a 
      thorough testing.
     @ mysql-test/r/archive.result
        Updated result file for test case for bug#47012
     @ mysql-test/std_data/ar1.ARM
        part of table (ar1) created in mysql 5.0
     @ mysql-test/std_data/ar1.ARZ
        part of table (ar1) created in mysql 5.0
     @ mysql-test/std_data/ar1.frm
        part of table (ar1) created in mysql 5.0
     @ mysql-test/t/archive.test
        Added test case for 47012
     @ storage/archive/azio.c
        Fixed a minor issues (minor version over writing version in 
        stream structure)
        Removed assertion when an older version is found instead the 
        table is marked as corrupt which block all operation except 
        for repair.
     @ storage/archive/ha_archive.cc
        Blocked open for repair for older version archive file.
        An error is return indicating the table needs to upgraded.
[28 Oct 2009 15:19] Paul Dubois
I added a note about this problem at http://dev.mysql.com/doc/refman/5.1/en/checking-table-incompatibilities.html.
[2 Nov 2009 6:46] Bugs System
A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/88880

3173 Anurag Shekhar	2009-11-02
      Bug #47012 archive tables are not upgradeable, and server crashes 
               on any access
      
      Archive engine for 5.1 (and latter) version uses a modified 
      version of zlib (azlib). These two version are incompatible
      so a proper upgrade is needed before tables created in 5.0 
      can be used reliable.
      
      This upgrade can be performed using repair. But due to lack 
      of test and close proximity with GA dates this patch addressed
      only the crashing issue. Any attempt to repair will be  blocked.
      
      Eventually repair can be allowed to run through (which will also
      cause an upgrade from older version to newer) but only after a 
      thorough testing.
     @ mysql-test/r/archive.result
        Updated result file for test case for bug#47012
     @ mysql-test/std_data/ar1.ARM
        part of table (ar1) created in mysql 5.0
     @ mysql-test/std_data/ar1.ARZ
        part of table (ar1) created in mysql 5.0
     @ mysql-test/std_data/ar1.frm
        part of table (ar1) created in mysql 5.0
     @ mysql-test/t/archive.test
        Added test case for 47012
     @ storage/archive/azio.c
        Fixed a minor issues (minor version over writing version in 
        stream structure)
        Removed assertion when an older version is found. Instead
        setting the currect version (2) in s->version
        If an unknown version is found marked it as currupt.
     @ storage/archive/ha_archive.cc
        Detecting the archive version in getShare and marking
        it as need to upgrade.
        Blocking open if the archive needs an upgrade. This
        can be allwoed in case of open for repair to upgrade
        the archive but needs to tested.
[10 Nov 2009 12:30] Bugs System
A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/89944

3173 Anurag Shekhar	2009-11-10
      Bug #47012 archive tables are not upgradeable, and server crashes 
             on any access
      
      Archive engine for 5.1 (and latter) version uses a modified 
      version of zlib (azlib). These two version are incompatible
      so a proper upgrade is needed before tables created in 5.0 
      can be used reliable.
      
      This upgrade can be performed using repair. But due to lack 
      of test and close proximity with GA dates this patch addressed
      only the crashing issue. Any attempt to repair will be  blocked.
      
      Eventually repair can be allowed to run through (which will also
      cause an upgrade from older version to newer) but only after a 
     @ mysql-test/r/archive.result
        Updated result file for test case for bug#47012
     @ mysql-test/std_data/bug47012.ARM
        part of archive table (t1) created in mysql 5.0
     @ mysql-test/std_data/bug47012.ARZ
        part of archive table (t1) created in mysql 5.0
     @ mysql-test/std_data/bug47012.frm
        part of archive table (t1) created in mysql 5.0
     @ mysql-test/t/archive.test
        Added test case for 47012
     @ storage/archive/azio.c
        Fixed a minor issues (minor version over writing version in 
        stream structure)
        Removed assertion when an older version is found. Instead
        setting the currect version (2) in s->version
        If an unknown version is found marked it as currupt.
     @ storage/archive/ha_archive.cc
        
        Detecting the archive version in getShare and marking
        it as need to upgrade.
        Blocking open if the archive needs an upgrade. This
        can be allwoed in case of open for repair to upgrade
        the archive but needs to tested.
[11 Nov 2009 8:04] Bugs System
A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/90054

3196 Anurag Shekhar	2009-11-11
      Bug #47012 archive tables are not upgradeable, and server crashes 
             on any access
      
      Archive engine for 5.1 (and latter) version uses a modified 
      version of zlib (azlib). These two version are incompatible
      so a proper upgrade is needed before tables created in 5.0 
      can be used reliable.
      
      This upgrade can be performed using repair. But due to lack 
      of test its risky to allow upgrade for now. This patch addresses
      only the crashing issue. Any attempt to repair will be blocked.
      
      Eventually repair can be allowed to run through (which will also
      cause an upgrade from older version to newer) but only after a 
      thorough testing.
     @ mysql-test/r/archive.result
        Updated result file for test case for bug#47012
     @ mysql-test/std_data/bug47012.ARM
        part of archive table (t1) created in mysql 5.0
     @ mysql-test/std_data/bug47012.ARZ
        part of archive table (t1) created in mysql 5.0
     @ mysql-test/std_data/bug47012.frm
        part of archive table (t1) created in mysql 5.0
     @ mysql-test/t/archive.test
        Added test case for bug#47012.
     @ storage/archive/azio.c
        Fixed a minor issues (minor version overwriting version in 
        stream structure)
        Removed assertion when an older version is found. Instead
        setting the correct version (2) in s->version
        If an unknown version is found marked it as corrupt.
     @ storage/archive/ha_archive.cc
        Detecting the archive version in getShare and marking
        it as need to upgrade.
        Blocking open if the archive needs an upgrade. This
        can be allowed in case of open for repair to upgrade
        the archive but needs to tested.
[11 Nov 2009 9:50] Anurag Shekhar
Allowing upgrade of 5.0 archive tables to 5.1+ will be fixed in bug#48633.
Until bug#48633 is resolved, repair (which will also upgrade the tables) on 5.0 tables will be blocked.
[13 Nov 2009 7:45] Bugs System
A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/90306

3679 Anurag Shekhar	2009-11-13 [merge]
      merging fix for bug#47012.
[17 Nov 2009 12:15] Bugs System
A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/90649

3201 Anurag Shekhar	2009-11-17
      fix for pb2 failure after bug#47012 push.
[2 Dec 2009 8:06] Bugs System
Pushed into 5.1.42 (revid:joro@sun.com-20091202080033-mndu4sxwx19lz2zs) (version source revid:davi.arnaut@sun.com-20091125130912-d7hrln14ef7y5d7i) (merge vers: 5.1.42) (pib:13)
[16 Dec 2009 8:39] Bugs System
Pushed into 6.0.14-alpha (revid:alik@sun.com-20091216083311-xorsasf5kopjxshf) (version source revid:alik@sun.com-20091214191830-wznm8245ku8xo702) (merge vers: 6.0.14-alpha) (pib:14)
[16 Dec 2009 8:45] Bugs System
Pushed into 5.5.0-beta (revid:alik@sun.com-20091216082430-s0gtzibcgkv4pqul) (version source revid:alexey.kopytov@sun.com-20091124083136-iqm136jm31sfdwg3) (merge vers: 5.5.0-beta) (pib:14)
[16 Dec 2009 8:52] Bugs System
Pushed into mysql-next-mr (revid:alik@sun.com-20091216083231-rp8ecpnvkkbhtb27) (version source revid:alik@sun.com-20091212203859-fx4rx5uab47wwuzd) (merge vers: 5.6.0-beta) (pib:14)
[17 Dec 2009 10:53] Tony Bedford
Entries have been added to the 5.1.42, 5.5.1, 5.6.0, 6.0.14:

After a binary upgrade to MySQL 5.1 from a MySQL 5.0 installation that contains ARCHIVE tables, accessing those tables caused the server to crash, even if you had run mysql_upgrade or CHECK TABLE ... FOR UPGRADE.

To work around this problem, use mysqldump to dump all ARCHIVE tables before upgrading, and reload them into MySQL 5.1 after upgrading. The same problem occurs for binary downgrades from MySQL 5.1 to 5.0.
[17 Dec 2009 10:54] Tony Bedford
Entries have been added to the 5.1.42, 5.5.1, 5.6.0, 6.0.14 changelogs:

After a binary upgrade to MySQL 5.1 from a MySQL 5.0 installation that contains ARCHIVE tables, accessing those tables caused the server to crash, even if you had run mysql_upgrade or CHECK TABLE ... FOR UPGRADE.

To work around this problem, use mysqldump to dump all ARCHIVE tables before upgrading, and reload them into MySQL 5.1 after upgrading. The same problem occurs for binary downgrades from MySQL 5.1 to 5.0.
[2 Jan 2010 7:22] dilip kumar
Is this bug being fixed in the 5.1.42 version after upgrading from 5.0 series for archive tables
[2 Jan 2010 7:26] dilip kumar
Is this bug being fixed in the 5.1.42 version after upgrading from 5.0 series for archive tables?.

I tried in my development server check table & then the repair table it worked but not sure this might effect the performance issue so havent tried for the production database. Still waiting for the fix without an dump and reload of the tables because my databases sizes are more then 1.5 TB.
[8 Mar 2010 0:14] Paul Dubois
5.6.0 changelog entry unneeded.
[12 Mar 2010 14:12] Bugs System
Pushed into 5.1.44-ndb-7.0.14 (revid:jonas@mysql.com-20100312135944-t0z8s1da2orvl66x) (version source revid:jonas@mysql.com-20100312115609-woou0te4a6s4ae9y) (merge vers: 5.1.44-ndb-7.0.14) (pib:16)
[12 Mar 2010 14:28] Bugs System
Pushed into 5.1.44-ndb-6.2.19 (revid:jonas@mysql.com-20100312134846-tuqhd9w3tv4xgl3d) (version source revid:jonas@mysql.com-20100312060623-mx6407w2vx76h3by) (merge vers: 5.1.44-ndb-6.2.19) (pib:16)
[12 Mar 2010 14:44] Bugs System
Pushed into 5.1.44-ndb-6.3.33 (revid:jonas@mysql.com-20100312135724-xcw8vw2lu3mijrhn) (version source revid:jonas@mysql.com-20100312103652-snkltsd197l7q2yg) (merge vers: 5.1.44-ndb-6.3.33) (pib:16)