Bug #35711 missing record in large InnoDB table after database restore
Submitted: 31 Mar 2008 16:42 Modified: 24 Apr 2008 15:23
Reporter: Giuseppe Maxia Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: Backup Severity:S2 (Serious)
Version:6.0.5 OS:Other (Linux, Mac OS X)
Assigned to: Sveta Smirnova CPU Architecture:Any
Tags: missing, online backup, restore

[31 Mar 2008 16:42] Giuseppe Maxia 
Description:
ChangeSet@1.2610, 2008-03-28 11:53:03+01:00

Using a sample employees database, containing 4 million records, after a backup database and the corresponding restore, there is one record missing when the engine is InnoDB.

expected
+--------------+------------------+------------------------------------------+
| table_name   | expected_records | expected_crc                             |
+--------------+------------------+------------------------------------------+
| employees    |           300024 | 4d4aa689914d8fd41db7e45c2168e7dcb9697359 |
| departments  |                9 | 4b315afa0e35ca6649df897b958345bcb3d2b764 |
| dept_manager |               24 | 9687a7d6f93ca8847388a42a6d8d93982a841c6c |
| dept_emp     |           331603 | f16f6ce609d032d6b1b34748421e9195c5083da8 |
| titles       |           443308 | d12d5f746b88f07e69b9e36675b6067abb01b60e |
| salaries     |          2844047 | b5a1785c27d75e33a4173aaa22ccf41ebd7d4a9f |
+--------------+------------------+------------------------------------------+

found
+--------------+------------------+------------------------------------------+
| table_name   | found_records    | found_crc                                |
+--------------+------------------+------------------------------------------+
| employees    |           300024 | 4d4aa689914d8fd41db7e45c2168e7dcb9697359 |
| departments  |                9 | 4b315afa0e35ca6649df897b958345bcb3d2b764 |
| dept_manager |               24 | 9687a7d6f93ca8847388a42a6d8d93982a841c6c |
| dept_emp     |           331603 | f16f6ce609d032d6b1b34748421e9195c5083da8 |
| titles       |           443307 | 20b47c7a2efbc6d41c69717fa10e09f33ee72cd5 |
| salaries     |          2844047 | b5a1785c27d75e33a4173aaa22ccf41ebd7d4a9f |
+--------------+------------------+------------------------------------------+

summary
+--------------+---------------+-----------+
| table_name   | records_match | crc_match |
+--------------+---------------+-----------+
| employees    | OK            | OK        |
| departments  | OK            | OK        |
| dept_manager | OK            | OK        |
| dept_emp     | OK            | OK        |
| titles       | not ok        | not ok    |
| salaries     | OK            | OK        |
+--------------+---------------+-----------+

How to repeat:
1) download the sample employees database (26 MB)
   http://datacharmer.org/downloads/employees_db-1.0.2.tar.bz2
2) modify employees.sql to use InnoDB as default engine
3) source employees.sql
4) source test_employees.sql, to make sure that the data is correct
5) backup database employees to 'employees.bkp';
6) drop database employees;
7) restore from 'employees.bkp';
8) source test_employees.sql

# the same procedure works without errors for MyISAM

Suggested fix:
N/A
[1 Apr 2008 20:52] Sveta Smirnova 
Thank you for the report.

I can not repeat described behavior on my side. Do you always can repeat this problem or only under some circumstances?
[1 Apr 2008 22:23] Giuseppe Maxia 
The test was executed on a Linux 32 bit,  Ubuntu 7.10
CPU: Intel dual core 2.13GHz
RAM: 4 GB
Tree: mysql-6.0 ChangeSet@1.2610, 2008-03-28 11:53:03+01:00
Compiled with BUILD/compile-pentium-max (not debug)

Repeated as described on Mac OS X 10.5, on a MacBook Intel Dual Core with 2GB RAM
[2 Apr 2008 17:06] Sveta Smirnova 
Thank you for the feedback.

I could not repeat described problem with ChangeSet@1.2618, 2008-03-28 18:49:12+04:00, gshchepa@host.loc +26 -0 compiled as described. Please upgrade, run mysql_upgrade, then try to repeat on your side. If bug is still repeatable please provide your configuration file.
[16 Apr 2008 4:48] Giuseppe Maxia 
Tested again with mysql-6.0-backup tree, ChangeSet@1.2608, 2008-04-11 13:48:15-04:00
The bug can be reproduced in the same way.
Also tested on mysql-6.0 ChangeSet@1.2637.1.5, 2008-04-14 17:14:13+02:00
Same bug.
[24 Apr 2008 15:23] Giuseppe Maxia 
Apparently fixed in 6.0.6.
The buggy behaviour is no longer repeatable with this version.