MySQL Bugs: #4416: Crash with report of index corruption

Bug #4416	Crash with report of index corruption
Submitted:	6 Jul 2004 10:20	Modified:	9 Jul 2004 10:28
Reporter:	Bill McCaffrey	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: InnoDB storage engine	Severity:	S1 (Critical)
Version:	4.1.3	OS:	Linux (Linux SLES 8)
Assigned to:		CPU Architecture:	Any

Description:
I upgrading servers from 4.1.1 standard (gcc) to 4.1.3 standard (icc). After about 1 1/2 hours of operation database crashed and would not restart. I reverted back to 4.1.1 and database came up fine and is running.

How to repeat:
Not sure, there were many queries running against a large table (15 million rows, 9 GB of data)

Error log from crash

Attachment: neopets_my4_dump.txt (text/plain), 136.39 KiB.

Not enough information was provided for us to be able
to handle this bug. Please re-read the instructions at
http://bugs.mysql.com/how-to-report.php

If you can provide more information, feel free to add it
to this bug and change the status back to 'Open'.

Thank you for your interest in MySQL.

Additional info:

How did you upgrade the servers? Did you input data from textual SQL dumps, or did you just start the new mysqld executable on the old tables? (Both should work.) What was printed to the error log? Did the 4.1.3 server leave any innodb.status.<pid> files in the datadir?

inodb dump

Attachment: innodb_status.txt (text/plain), 35.32 KiB.

The server was upgraded by switching the binaries and restarting the server with the existing datafiles.
Attached are two files, one is the error log and the other is an innodb dump file.

The errors are most probably caused by faulty memory chips. Have you tried to run memtest86 (http://www.memtest86.com/) on the system?

Hi!

Did you create the table with MySQL-4.1.0 or MySQL-4.1.1? Then InnoDB thinks that the charset is non-latin1, because in those versions InnoDB was not aware that the MySQL default charset name had changed to latin1_swedish_ci.

If InnoDB thinks that the charset is non-latin1, then InnoDB lets MySQL to do the sorting order comparisons, and that order was changed in MySQL-4.1.2!

http://dev.mysql.com/doc/mysql/en/InnoDB_news-4.1.3.html

"
Important: Starting from MySQL 4.1.3, InnoDB uses the same character set comparison functions as MySQL for non-latin1_swedish_ci character strings that are not BINARY. This changes the sorting order of space and ASCII(0) (= a zero byte) in those character sets. For latin1_swedish_ci character strings and BINARY strings, InnoDB uses its own pad-spaces-at-end comparison method, which stays unchanged. If you have an InnoDB table created with MySQL 4.1.2 or earlier, with an index on a non-latin1 character set (in the case of 4.1.0 and 4.1.1 with any character set) CHAR/VARCHAR/or TEXT column that is not BINARY but may contain the character ASCII(0), then you should do ALTER TABLE or OPTIMIZE table on it to regenerate the index, after upgrading to MySQL 4.1.3 or later.
"

When looking at the printouts in the .err file, I see that you have stored the string

len 17; hex 64615f5f7365637265745f5f73616e7461; asc da__secret__santa

as well as

len 18; hex 64615f5f7365637265745f5f73616e74610a; asc da__secret__santa

to the database. They differ only in the character 0x05 at the end (maybe the newline char). Now, if the MySQL sorting order comparison works differently 4.1.3 when comparing these strings, it could cause the errors reported in the .err log.

What to do:

1) I have to test how MySQL-4.1.1 and 4.1.3 compare 0x05 and other chars <= 0x20 (space).

2) If the sorting order really is the problem, then you have to rebuild the whole table. I apologize for this, since the MySQL tradition is that no data conversion is needed in an upgrade.

Best regards,

Heikki

Hi!

I was able to repeat the problem. The sorting order of 'abc' and 'abc0x05' indeed changed between 4.1.1 and 4.1.3.

You have to rebuild the table, and I have to correct the release note of 4.1.3: any character < 0x20 will cause problems in non-latin1 non-BINARY character sets, and the table has to be rebuilt.

Thank you for the bug report,

Heikki

heikki@hundin:~/mysql-4.1/client> ./mysql test
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1 to server version: 4.1.1-alpha-debug-log

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> create table t1 (a varchar (10) not null primary key) type = innodb;
Query OK, 0 rows affected (0.16 sec)

mysql> insert into t1 values (CAST(0x5061756c AS CHAR));
Query OK, 1 row affected (0.02 sec)

mysql> insert into t1 values (CAST(0x5061756c05 AS CHAR));
Query OK, 1 row affected (0.00 sec)

mysql>
mysql> select * from t1;
+-------+
| a     |
+-------+
| Paul  |
| Paul |
+-------+
2 rows in set (0.02 sec)

mysql> exit
Bye
heikki@hundin:~/mysql-4.1/client> ./mysqladmin shutdown
heikki@hundin:~/mysql-4.1/client> ./mysql test
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1 to server version: 4.1.4-beta-debug-log

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> select * from t1;
+-------+
| a     |
+-------+
| Paul  |
| Paul |
+-------+
2 rows in set (0.06 sec)

mysql> check table t1;
+---------+-------+----------+----------+
| Table   | Op    | Msg_type | Msg_text |
+---------+-------+----------+----------+
| test.t1 | check | error    | Corrupt  |
+---------+-------+----------+----------+
1 row in set (0.35 sec)

mysql> select length(a) from t1;
+-----------+
| length(a) |
+-----------+
|         4 |
|         5 |
+-----------+
2 rows in set (0.01 sec)

mysql> drop table t1;
Query OK, 0 rows affected (0.01 sec)

mysql> create table t1 (a varchar (10) not null primary key) type = innodb;
Query OK, 0 rows affected, 1 warning (0.01 sec)

mysql> insert into t1 values (CAST(0x5061756c AS CHAR));
Query OK, 1 row affected (0.01 sec)

mysql> insert into t1 values (CAST(0x5061756c05 AS CHAR));
Query OK, 1 row affected (0.00 sec)

mysql> select length(a) from t1;
+-----------+
| length(a) |
+-----------+
|         5 |
|         4 |
+-----------+
2 rows in set (0.00 sec)

mysql>