MySQL Bugs: #48935: alter table to add an index leads to data loss on large table

Bug #48935	alter table to add an index leads to data loss on large table
Submitted:	20 Nov 2009 9:28	Modified:	23 Nov 2009 20:46
Reporter:	Shane Bester (Platinum Quality Contributor)	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: InnoDB Plugin storage engine	Severity:	S1 (Critical)
Version:	5.1.40	OS:	Any
Assigned to:		CPU Architecture:	Any

Description:
given a large table created using the plugin, we observe that adding a primary key causes data loss.  An excerpt from the testcase:

<insert>

Query OK, 47000000 rows affected, 65535 warnings (1 hour 1 min 1.13 sec)
Records: 47000000  Duplicates: 0  Warnings: 1162777934

mysql> 
mysql> select count(*) from t1;

+----------+
| count(*) |
+----------+
| 47000000 |
+----------+
1 row in set (2 min 52.78 sec)

mysql> select count(distinct invc_id) from t1;
+-------------------------+
| count(distinct invc_id) |
+-------------------------+
|                47000000 |
+-------------------------+
1 row in set (3 min 54.83 sec)

mysql> alter table t1 add primary key(invc_id);

Query OK, 0 rows affected (2 hours 14 min 6.62 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> 
mysql> select count(*) from t1;
+----------+
| count(*) |
+----------+
| 23456081 |
+----------+
1 row in set (1 min 7.52 sec)

mysql> select count(distinct invc_id) from invoice;
+-------------------------+
| count(distinct invc_id) |
+-------------------------+
|                23456081 |
+-------------------------+
1 row in set (1 min 20.95 sec)

What happened to the rest of the rows ?

How to repeat:
see private attached testcase.  I ran the server like this:

mysqld --no-defaults --port=3310 --socket=sock --skip-grant-tables 
--skip-name-resolve --innodb_flush_log_at_trx_commit=0  --ignore-builtin-innodb --plugin-load=innodb=ha_innodb_plugin.so --innodb_lock_wait_timeout=1 --innodb_buffer_pool_size=4G --innodb_file_per_table=1 --innodb_file_format=barracuda --innodb_doublewrite=0 --innodb-checksums=0 --innodb_log_buffer_size=200M --innodb_log_file_size=400M

this is no longer repeatable in 5.1.41 (plugin version 1.0.5):

Query OK, 47000000 rows affected, 65535 warnings (1 hour 45.58 sec)
Records: 47000000  Duplicates: 0  Warnings: 1162764590
mysql> 
mysql> select count(*) from invoice;
+----------+
| count(*) |
+----------+
| 47000000 |
+----------+
1 row in set (2 min 53.75 sec)

mysql> select count(distinct invc_id) from invoice;
+-------------------------+
| count(distinct invc_id) |
+-------------------------+
|                47000000 |
+-------------------------+
1 row in set (4 min 17.14 sec)

mysql> alter table invoice add primary key(invc_id);

Query OK, 0 rows affected (2 hours 19 min 41.83 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> select count(*) from invoice;
+----------+
| count(*) |
+----------+
| 47000000 |
+----------+
1 row in set (3 min 5.64 sec)

mysql> select count(distinct invc_id) from invoice;
+-------------------------+
| count(distinct invc_id) |
+-------------------------+
|                47000000 |
+-------------------------+
1 row in set (4 min 32.69 sec)

So, I wonder which bugfix made this work?

Thank you for your bug report. This issue has already been fixed in the latest released version of that product, which you can download at

  http://www.mysql.com/downloads/

Explanation: Shane, I can confirm your findings, this is no longer repeatable with 5.1.41.

Environment:
  Win2K8SE x64 localhost, MySQL server 5.1.41

...mysql-noinstall-5.1.41-winx64\bin>mysqld --no-defaults --port=3310 --socket=sock --skip-grant-tables --skip-name-resolve --innodb_flush_log_at_trx_commit=0  --ignore-builtin-innodb --plugin-load=innodb=ha_innodb_plugin.dll --innodb_lock_wait_timeout=1 --innodb_buffer_pool_size=4G --innodb_file_per_table=1 --innodb_file_format=barracuda --innodb_doublewrite=0 --innodb-checksums=0 --innodb_log_buffer_size=200M --innodb_log_file_size=400M --tmpdir=G:\

...mysql-noinstall-5.1.41-winx64\bin>mysql -uroot --port=3310 test < bug48935_testcase.sql
count(*)
47000000
count(distinct invc_id)
47000000
count(*)
47000000
count(distinct invc_id)
47000000

This looks like a duplicate of the bug that was reported on the InnoDB Forum:

http://forums.innodb.com/read.php?3,716,716

The fix missed the 1.0.4 release by a couple of weeks. It is included in 1.0.5 and later. The ChangeLog entry reads:

2009-08-27	The InnoDB Team

	* row/row0merge.c:
	Fix a bug in the merge sort that can corrupt indexes in fast index
	creation. Add some consistency checks. Check that the number of
	records remains constant in every merge sort pass.

Bug #48792 is a duplicate of this.

This bug affects CREATE INDEX and ALTER TABLE … ADD INDEX for all indexes, not just the primary key.