MySQL Bugs: #21680: ALTER TABLE tab ADD INDEX, with 4 million rows fails

Bug #21680	ALTER TABLE tab ADD INDEX, with 4 million rows fails
Submitted:	16 Aug 2006 22:46	Modified:	24 Aug 2006 12:29
Reporter:	Alexandre Pereira	Email Updates:
Status:	Not a Bug	Impact on me:	None
Category:	MySQL Server	Severity:	S1 (Critical)
Version:	5.1.11	OS:	Linux (Linux 2.6 (Slackware))
Assigned to:		CPU Architecture:	Any
Tags:	ALTER TABLE, huge table, innodb

Description:
I have a INNODB table with 4.3 million rows.
Is has some indexes. I tried to add 2 more indexes in console with 'mysql'.

First attemp: one index at a time.
First ADD INDEX took 5 minutes to accomplish (Pentium Dual Core, 4GB RAM).
On the second ADD INDEX, I got the error: 'ERROR 2013 (HY000): Lost connection to MySQL server during query'.
I lost the table, lost all its data, and two new tables remained in the database: tables named #sql-1075_71, #sql2-1075-71, or so. It was very difficult to drop the database. I got the following error: 'ERROR 1010 (HY000): Error dropping database (can't rmdir './domque' errno: 39)'
Then, when I tried to restore the database from a backup, I got the error: 'ERROR 1005 (HY000): Can't create table dom'
I had to clean everything, even some files in the /tmp directory, remove Mysql and install it again.

Second attempt: Tried to add both indexes at the same time. Got the same error: 'ERROR 2013 (HY000): Lost connection to MySQL server during query'.
Apparently I didn't lost any data, but the indexes were not created and I have a new table named: #sql-1073_71 with 3800000 rows (and with the new indexes), whilst my original table have 4326522 rows.

How to repeat:
Here is my table:

CREATE TABLE dom (
  dom_id INT NOT NULL PRIMARY KEY AUTO_INCREMENT,
  dom_nome VARCHAR(255) NOT NULL,
  dom_tldid INT,
  dom_utlid INT,
  dom_invid INT,
  dom_count INT DEFAULT 0,
  dom_data INT UNSIGNED,
  dom_status TINYINT DEFAULT 1,
  dom_discdata INT UNSIGNED,
  INDEX fkix_domcontador (dom_contador),
  INDEX fkix_domtldid (dom_tldid),
  INDEX fkix_domutlid (dom_utlid),
  INDEX fkix_dominvid (dom_invid),
  FOREIGN KEY (dom_tldid) REFERENCES tld (tld_id) ON UPDATE CASCADE,
  FOREIGN KEY (dom_utlid) REFERENCES utl (utl_id) ON UPDATE CASCADE,
  FOREIGN KEY (dom_invid) REFERENCES inv (inv_id) ON UPDATE CASCADE,
  UNIQUE INDEX ixdomnome (dom_nome)
  )ENGINE=INNODB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

You may fill it with 4.326.522 rows.

Then try to add the 2 indexes:

First attempt:
ALTER TABLE dom ADD INDEX fkix_domstatus (dom_status);
ALTER TABLE dom ADD INDEX fkix_domdiscdata (dom_discdata);

Second attempt:
ALTER TABLE dom ADD INDEX fkix_domstatus (dom_status), ADD INDEX fkix_domdiscdata (dom_discdata);

I use my-huge.cnf, with the following configuration (Pentium D with 4GB RAM, 6GB swap):

# Uncomment the following if you are using InnoDB tables
innodb_data_home_dir = /var/lib/mysql/
innodb_data_file_path = ibdata1:2000M;ibdata2:10M:autoextend
innodb_log_group_home_dir = /var/lib/mysql/
innodb_log_arch_dir = /var/lib/mysql/
# You can set .._buffer_pool_size up to 50 - 80 %
# of RAM but beware of setting memory usage too high
innodb_buffer_pool_size = 1G
innodb_additional_mem_pool_size = 20M
# Set .._log_file_size to 25 % of buffer pool size
innodb_log_file_size = 250M
innodb_log_buffer_size = 8M
innodb_flush_log_at_trx_commit = 1
innodb_lock_wait_timeout = 50

I installed a new snap of the operating system (Slackware Linux 2.6.16.9; Aug 19, 2006) and compiled MySQL again in this OS.

Now I can't repeat the submitted bug, so I come to the conclusion it was not a MySQL bug. I'm changing the status to "Not a Bug".