Bug #39585 innodb and myisam corruption after binary upgrade from <=5.0.46 to >=5.0.48
Submitted: 22 Sep 2008 12:16 Modified: 12 Apr 2010 21:46
Reporter: Shane Bester (Platinum Quality Contributor) Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: Charsets Severity:S3 (Non-critical)
Version:>=5.0.48 OS:Any
Assigned to: Ramil Kalimullin CPU Architecture:Any

[22 Sep 2008 12:16] Shane Bester 
Description:
when upgrading from one minor version of mysqld to another one, e.g. 5.0.40 to 5.0.66a and mysqldump/reload wasn't done, there are cases when corruptions appear after upgrade. 

InnoDB: You should dump + drop + reimport the table to fix the
InnoDB: corruption. If the crash happens at the database startup, see
InnoDB: http://dev.mysql.com/doc/refman/5.0/en/forcing-recovery.html about
InnoDB: forcing recovery. Then dump + drop + reimport.
080922 14:07:56InnoDB: Assertion failure in thread 4868 in file .\btr\btr0btr.c line 624
InnoDB: Failing assertion: btr_node_ptr_get_child_page_no(node_ptr, offsets) == buf_frame_get_page_no(page)
InnoDB: We intentionally generate a memory trap.
InnoDB: Submit a detailed bug report to http://bugs.mysql.com.

mysqld-nt.exe!btr_page_get_father_for_rec
mysqld-nt.exe!btr_validate_level
mysqld-nt.exe!btr_validate_tree
mysqld-nt.exe!row_check_table_for_mysql
mysqld-nt.exe!ha_innobase::check
mysqld-nt.exe!handler::ha_check
mysqld-nt.exe!mysql_admin_table
mysqld-nt.exe!mysql_check_table
mysqld-nt.exe!mysql_execute_command
mysqld-nt.exe!mysql_parse
mysqld-nt.exe!dispatch_command
mysqld-nt.exe!do_command
mysqld-nt.exe!handle_one_connection
mysqld-nt.exe!pthread_start
mysqld-nt.exe!_callthreadstart

How to repeat:
import the attached testcase into 5.0.40.
upgrade to 5.0.66a and run "check table t1 extended"
[22 Sep 2008 12:18] MySQL Verification Team 
import into 5.0.40, then shutdown and run 5.0.66a on datafiles. check table crashes

Attachment: bug39585.sql (text/x-sql), 351.36 KiB.
[22 Sep 2008 12:27] MySQL Verification Team 
5.0.48 -> 5.0.66a seems to work fine
[22 Sep 2008 12:31] MySQL Verification Team 
5.0.42->5.0.60 crashes
5.0.46->5.0.48 crashes.
[22 Sep 2008 12:50] MySQL Verification Team 
not necessarily an innodb bug.  myisam table also becomes corrupted.  So i think this was a result of some character set sorting bugfixes.

mysql> check table t1 extended;
+---------+-------+----------+------------------------------------+
| Table   | Op    | Msg_type | Msg_text                           |
+---------+-------+----------+------------------------------------+
| test.t1 | check | error    | Key in wrong position at page 9216 |
| test.t1 | check | error    | Corrupt                            |
+---------+-------+----------+------------------------------------+
2 rows in set (0.02 sec)

Can mysql_upgrade or 'check table .. for upgrade' be made to detect these old incompatible data files?
[23 Sep 2008 9:45] MySQL Verification Team 
some suggestions how we could address the risk of this problem:

o) versioning .frm files
o) including huge *INCOMPATIBLE* warning in the change history notes
o) check table for upgrade should always check for situations that could lead to problems like this, so that mysql_upgrade will rebuild a table.
[30 Sep 2008 12:10] Ramil Kalimullin 
The real cause is a fix for bug #27562: "ascii.xml invalid?".
[7 Oct 2008 11: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/55574

2689 Georgi Kodinov	2008-10-07
      Bug #39585: innodb and myisam corruption after binary upgrade from <=5.0.46 to >=5.0.48
      
      There was a change in the binary format introduced in 5.0.48 by the ascii collation definition 
      update fix for bug 27562.
      This was undetected by CHECK TABLE.
      Fixed by checking if there are fields with the ascii collation in tables created with 
      versions before 5.0.48.
[7 Oct 2008 12:29] Alexander Barkov 
More collation changes made in 6.0:

Bug#25420 Tilde = 'Y' in Chinese
Problems: wrong sort orders for the following characters:
- TILDE and GRAVE ACCENT in big5_chinese_ci
- LATIN SMALL LETTER J in cp866_general_ci
- TILDE in gb2312_chinese_ci
- TILDE in gbk_chinese_ci
[7 Oct 2008 12:32] Alexander Barkov 
More collation changes in 6.0:

WL#3664 - strnxfrm() changes for prefix keys and NOPAD
changed sort order for latin2_czech_cs
[7 Oct 2008 12:42] Ramil Kalimullin 
Known changes:

ver. 5.0.48: 
============
bug #29461
  latin7_general_ci
  latin7_general_cs
  latin7_estonian_cs
  latin2_hungarian_ci
  koi8u_general_ci
  cp1251_ukrainian_ci
  cp1250_general_ci
bug #29499, bug #27562
  ascii_general_ci

ver. 5.1.24-rc:
===============
bug #27877
  utf8_general_ci
  ucs2_general_ci

ver. 6.0.1:
===========
WL#3664
  latin2_czech_cs

ver. 6.0.6:
===========
bug #25420
  big5_chinese_ci
  cp866_general_ci
  gb2312_chinese_ci
  gbk_chinese_ci
[7 Oct 2008 14:34] 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/55598

2760 Ramil Kalimullin	2008-10-07
      Fix for bug#39585: innodb and myisam corruption after binary 
      upgrade from <=5.0.46 to >=5.0.48
      
      Problem: 'check table .. for upgrade' doesn't detect 
      incompatible collation changes made in 5.0.48.
      
      Fix: check for incompatible collation changes.
[8 Oct 2008 13:53] 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/55771

2765 Ramil Kalimullin	2008-10-08
      Fix for bug#39585: innodb and myisam corruption after binary 
      upgrade from <=5.0.46 to >=5.0.48
      
      Problem: 'check table .. for upgrade' doesn't detect 
      incompatible collation changes made in 5.0.48.
      
      Fix: check for incompatible collation changes.
[9 Oct 2008 17:31] Bugs System 
Pushed into 5.1.30  (revid:ramil@mysql.com-20081008135257-ekvn8gs483wl1z27) (version source revid:ramil@mysql.com-20081008135257-ekvn8gs483wl1z27) (pib:4)
[9 Oct 2008 18:25] Paul DuBois 
Noted in 5.1.29 (not 5.1.30) changelog.

CHECK TABLE ... FOR UPGRADE did not check for incompatible collation 
changes made in MySQL 5.0.48.

Setting report to NDI pending push to 6.0.x.
[17 Oct 2008 16:42] Bugs System 
Pushed into 6.0.8-alpha  (revid:ramil@mysql.com-20081008135257-ekvn8gs483wl1z27) (version source revid:ramil@mysql.com-20081008135520-olnw953qny9nyxx6) (pib:5)
[17 Oct 2008 18:09] Paul DuBois 
Noted in 6.0.8 changelog.
[28 Oct 2008 21:02] Bugs System 
Pushed into 5.1.29-ndb-6.2.17  (revid:ramil@mysql.com-20081008135257-ekvn8gs483wl1z27) (version source revid:tomas.ulin@sun.com-20081028140209-u4emkk1xphi5tkfb) (pib:5)
[28 Oct 2008 22:21] Bugs System 
Pushed into 5.1.29-ndb-6.3.19  (revid:ramil@mysql.com-20081008135257-ekvn8gs483wl1z27) (version source revid:tomas.ulin@sun.com-20081028194045-0353yg8cvd2c7dd1) (pib:5)
[1 Nov 2008 9:46] Bugs System 
Pushed into 5.1.29-ndb-6.4.0  (revid:ramil@mysql.com-20081008135257-ekvn8gs483wl1z27) (version source revid:jonas@mysql.com-20081101082305-qx5a1bj0z7i8ueys) (pib:5)
[5 Nov 2008 6:25] MySQL Verification Team 
was this fixed in the 5.0 branch?
[5 Nov 2008 8:13] Georgi Kodinov 
Shane,

The fix was to fine-tune the 5.1 upgrade code. There was nothing to fix in 5.0
[18 Jul 2009 6:08] MySQL Verification Team 
actually there was something to fix in 5.0.  if you upgraded from older version of 5.0 to latest 5.0, the check table for upgrade must detect this too.
[8 Mar 2010 20:12] James Day 
Paul or someone else in docs, please note in the 5.0 manual section on upgrading the limitation that if upgrading from versions of MySQL before 5.0.48 to 5.0.48 or later there may be index corruption that is not detected or fixed by the upgrade tools and must be done by hand. Server crashing if you don't do a rebuild by hand is quite unpleasant as an upgrade experience. REPAIR TABLE for MyISAM or ALTER TABLE tablename engine=InnoDB for InnoDB should be sufficient, for other engines may take dump and reload, haven't checked. Or OPTIMIZE TABLE for both, which is what Support tends to tell people to do unless we know of a case where it won't work.

I'm suggesting documenting the existence of this bug in 5.0's upgrade tool because I don't expect it to be fixed now and because we still encounter people doing upgrades across these version numbers in 5.0.
[12 Apr 2010 21:46] Paul DuBois 
This issue is mentioned on the page that lists problems with changes to charsets or collations (of which the ascii collation problem is one):

http://dev.mysql.com/doc/refman/5.0/en/checking-table-incompatibilities.html

"MySQL 5.0.48, 5.1.23 (Bug#27562)

Affects indexes that use the ascii_general_ci collation for columns that contain any of these characters: '`' GRAVE ACCENT, '[' LEFT SQUARE BRACKET, '\' REVERSE SOLIDUS, ']' RIGHT SQUARE BRACKET, '~' TILDE

Affected tables can be detected by CHECK TABLE ... FOR UPGRADE as of MySQL 5.1.29, 5.4.0 (see Bug#39585)."