Bug #39585 innodb and myisam corruption after binary upgrade from <=5.0.46 to >=5.0.48
Submitted: 22 Sep 2008 14:16 Modified: 17 Oct 2008 20:09
Reporter: Shane Bester
Status: Closed
Category:Server: Charsets Severity:S3 (Non-critical)
Version:>=5.0.48 OS:Any
Assigned to: Ramil Kalimullin Target Version:5.1.29,5.0+
Triage: Triaged: D1 (Critical)

[22 Sep 2008 14: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 14:18] Shane Bester 
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 14:27] Shane Bester 
5.0.48 -> 5.0.66a seems to work fine
[22 Sep 2008 14:31] Shane Bester 
5.0.42->5.0.60 crashes
5.0.46->5.0.48 crashes.
[22 Sep 2008 14:50] Shane Bester 
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 11:45] Shane Bester 
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 14:10] Ramil Kalimullin 
The real cause is a fix for bug #27562: "ascii.xml invalid?".
[7 Oct 2008 13: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 14: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 14: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 14: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 16: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 15: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 19: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 20: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 18: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 20:09] Paul DuBois 
Noted in 6.0.8 changelog.
[28 Oct 2008 22: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 23: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 10: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 7:25] Shane Bester 
was this fixed in the 5.0 branch?
[5 Nov 2008 9: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 8:08] Shane Bester 
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.