MySQL Bugs: #5670: Alter table add index () on innodb trashes table

Bug #5670	Alter table add index () on innodb trashes table
Submitted:	20 Sep 2004 16:58	Modified:	9 Oct 2012 22:24
Reporter:	Dave Hull	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: InnoDB storage engine	Severity:	S1 (Critical)
Version:	4.1.2, 5.1.47	OS:	Any
Assigned to:	Marko Mäkelä	CPU Architecture:	Any

Description:
One of our developers was having a problem with a foreign key constraint not working. I took a look and eventually determined that the problem was that one table was MyISAM rather than InnoDB.

I created a new new version of the MyISAM table specifying engine=innodb and inserted the data from the MyISAM table then dropped the MyISAM table and renamed the new InnoDB version accordingly.

The developer tried his query again and it was still bombing. Though 'show innodb status' was giving a different error message this time about not having matching indexes in both tables.

At this point, I did an 'alter table <table_name> add index (<field_name>). And it complained and the table was gone.

It turns out that the real problem was that the field in the table being referenced was defined as an int while in the table doing the referencing, the field was defined as a smallint. The referencing table already had an index on the field in question and my alter table statement was simply trying to add an index where an index already existed, but doing so caused that table to simply vanish.

Strangely enough, I could do a select * from the missing table, but not a describe nor could I select specific fields from it.

How to repeat:
I have not tried to repeat this, but you may be able to by creating an innodb table and a myisam table as follows:

Create a smallint field in the innodb table that references the int primary key from a myisam table.

Create a new innodb table that matches the myisam table. Insert all records from the myisam table into the innodb table and drop the myisam table and rename the innodb table to the myisam table's name.

Attempt to add an index to the innodb table for the smallint field that references the int field in the newly created innodb table.

At this point, if your system behaves like mine, your innodb table referencing the new innodb table will be gone, though you can still select * from it.

Sorry, I know this is convoluted, but this is what you get when java developers pretend they are DBAs and start creating tables as they need them.

Hi!

Can you send a complete SQL statement sequence that demonstrates the problem?

Note that MyISAM tables do not have FOREIGN KEYs. And a SMALLINT cannot reference an INT in a FOREIGN KEY constraint, because they are different data types.

Best regards,

Heikkui

I admit, this is extremely convoluted and the likelihood of this happening in the real world is extremely small. However, this did happen in our real-world, production system. Though I don't see how it could have happened. I don't think our developers know enough about MySQL to go through these steps, but here's how you can duplicate the bad behavior:

Here is a log of how I can reproduce this undesireable behavior:
dphull@www dphull$ mysql
Enter password: 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 4260 to server version: 4.1.2-alpha-standard-log

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

mysql> use test;
Database changed
mysql> create table test1 (test1_id int not null primary key auto_increment, fud text)engine=innodb;
Query OK, 0 rows affected (0.00 sec)

mysql> create table test2 (id int not null primary key auto_increment, test1_id int, constraint foreign key (test1_id) references test1(test1_id)) engine=innodb;
Query OK, 0 rows affected (0.00 sec)

mysql> alter table test1 engine=myisam;
Query OK, 0 rows affected (0.00 sec)
Records: 0  Duplicates: 0  Warnings: 0

[NOTE: Shouldn't this previous step always fail? I am able to reproduce this sporadically.]

mysql> show tables;
+----------------+
| Tables_in_test |
+----------------+
| test1          |
| test2          |
+----------------+
2 rows in set (0.01 sec)

mysql> alter table test2 modify test1_id smallint;
Query OK, 0 rows affected (0.00 sec)
Records: 0  Duplicates: 0  Warnings: 0

[This is the state I found the database in yesterday when the developers called me to take a look at it. I have talked to the developer who created the tables in our production environment and she assures me she did not go through these artificial steps (i.e. creating the tables correctly, then altering the engine type), so I have no idea how we got to this point, but we did and that's really not relevant to the actual problem (coming soon) where the test2 table gets dropped inadvertently]

mysql> insert into test1 values (0, 'Test entry 1');
Query OK, 1 row affected (0.01 sec)

mysql> insert into test2 values (0, 1);
ERROR 1216 (23000): Cannot add or update a child row: a foreign key constraint fails

[This is the exact problem the developers were unable to solve and this is when they called me. So I did a 'show innodb status' to get more information. Then I did a 'show create table test1' and 'show create table test2' and noticed the engine types were different.]

mysql> show create table test1;
+-------+-------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table                                                                                                                                          |
+-------+-------------------------------------------------------------------------------------------------------------------------------------------------------+
| test1 | CREATE TABLE `test1` (
  `test1_id` int(11) NOT NULL auto_increment,
  `fud` text,
  PRIMARY KEY  (`test1_id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 |
+-------+-------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

mysql> show create table test2;
+-------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table                                                                                                                                                                                                                                                                            |
+-------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| test2 | CREATE TABLE `test2` (
  `id` int(11) NOT NULL auto_increment,
  `test1_id` smallint(6) default NULL,
  PRIMARY KEY  (`id`),
  KEY `test1_id` (`test1_id`),
  CONSTRAINT `test2_ibfk_1` FOREIGN KEY (`test1_id`) REFERENCES `test1` (`test1_id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 |
+-------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

[At this point, I tried to 'alter table test1 engine=innodb;', but that failed.]

mysql> alter table test1 engine=innodb;
ERROR 1025 (HY000): Error on rename of './test/#sql-55d_10a4' to './test/test1' (errno: 150)

[So I created a new table and populated it as follows.]

mysql> create table test3 (test1_id int not null primary key auto_increment, fud text)engine=innodb;
Query OK, 0 rows affected (0.00 sec)

mysql> insert into test3 (select * from test1);
Query OK, 1 row affected (0.00 sec)
Records: 1  Duplicates: 0  Warnings: 0

[Next, I dropped test1 and renamed test3 to test1 as follows.]

mysql> drop table test1;
Query OK, 0 rows affected (0.00 sec)

mysql> rename table test3 to test1;
Query OK, 0 rows affected (0.00 sec)

[Thinking everything was good now, I asked the developers to retry their code. Of course, it still failed due to test2's smallint not matching test1's int for the test1_id field, though I still didn't recognize this as the problem... yet.]

mysql> insert into test2 values (0, 1);
ERROR 1216 (23000): Cannot add or update a child row: a foreign key constraint fails

[I did a 'show innodb status' and saw the following:]

------------------------
LATEST FOREIGN KEY ERROR
------------------------
040921 11:04:55 Transaction:
TRANSACTION 0 183609, ACTIVE 0 sec, process no 4419, OS thread id 81941 inserting, thread declared inside InnoDB 499
mysql tables in use 1, locked 1
2 lock struct(s), heap size 320, undo log entries 1
MySQL thread id 4260, query id 43256 localhost myRoot update
insert into test2 values (0, 1)
Foreign key constraint fails for table `test/test2`:
,
  CONSTRAINT `test2_ibfk_1` FOREIGN KEY (`test1_id`) REFERENCES `test1` (`test1_id`)
Trying to add to index `test1_id` tuple:
DATA TUPLE: 2 fields;
 0: len 2; hex 8001; asc   ;; 1: len 4; hex 80000001; asc     ;;

But the parent table `test/test1` does not currently exist!

[This didn't make a whole lot of sense to me, test/test1 certainly does exist! The bit about trying to add to index test1_id made me think that I needed to add an index to test2 so that's what I tried next.]

mysql> alter table test2 add index (test1_id);
ERROR 1025 (HY000): Error on rename of './test/#sql-55d_10a4' to './test/test2' (errno: 150)

[At this point, table test2 no longer exists! THIS IS A MAJOR PROBLEM, MySQL is not failing gracefully in this instance.]

mysql> show tables;
+----------------+
| Tables_in_test |
+----------------+
| test1          |
+----------------+
1 row in set (0.00 sec)

[Where did the table go? And how do I get it back? Luckily, I had taken a backup a couple of hours before this happened in our production environment so we were able to recover with very little loss of data, but recreating the table with the correct int field type on the reference yielded the following.]

mysql> create table test2 (id int not null primary key auto_increment, test1_id int, constraint foreign key (test1_id) references test1(test1_id)) engine=innodb;
ERROR 1005 (HY000): Can't create table './test/test2.frm' (errno: 121)

[Running 'show innodb status' again shows the following.]

------------------------
LATEST FOREIGN KEY ERROR
------------------------
040921 11:14:22 Error in foreign key constraint creation for table `test/test2`.
A foreign key constraint of name `test/test2_ibfk_1`
already exists.  (Note that internally InnoDB adds 'databasename/'
in front of the user-defined constraint name).

[Ok, I'll create the table and explicitly name the constraint.]

mysql> create table test2 (id int not null primary key auto_increment, test1_id int, constraint mcbugz foreign key (test1_id) references test1(test1_id)) engine=innodb;
Query OK, 0 rows affected (0.00 sec)

mysql> show create table test2;
+-------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table                                                                                                                                                                                                                                                                                                                                                      |
+-------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| test2 | CREATE TABLE `test2` (
  `id` int(11) NOT NULL auto_increment,
  `test1_id` int(11) default NULL,
  PRIMARY KEY  (`id`),
  KEY `mcbugz` (`test1_id`),
  CONSTRAINT `mcbugz` FOREIGN KEY (`test1_id`) REFERENCES `test1` (`test1_id`),
  CONSTRAINT `test2_ibfk_1` FOREIGN KEY (`test1_id`) REFERENCES `test1` (`test1_id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 |
+-------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

[Interesting, the old constraint shows up. It was orphaned when the table was dropped and reattached itself when the table was recreated. At this point, I dropped the table and recreated it again to get rid of the extra constraint.]

mysql> drop table test2;
Query OK, 0 rows affected (0.00 sec)

mysql> create table test2 (id int not null primary key auto_increment, test1_id int, constraint foreign key (test1_id) references test1(test1_id)) engine=innodb;
Query OK, 0 rows affected (0.01 sec)

mysql> show create table test2;
+-------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table                                                                                                                                                                                                                                                                        |
+-------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| test2 | CREATE TABLE `test2` (
  `id` int(11) NOT NULL auto_increment,
  `test1_id` int(11) default NULL,
  PRIMARY KEY  (`id`),
  KEY `test1_id` (`test1_id`),
  CONSTRAINT `test2_ibfk_1` FOREIGN KEY (`test1_id`) REFERENCES `test1` (`test1_id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 |
+-------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

[At this point, I restored our data into the production tables and we were back up and running, but the developers were very scared for about a half an hour. It seems to me there are a couple of problems, one, I should not have been able to 'alter table test1 engine=myisam' when there was a reference from test2 to test1 and after some testing, I have found that I am not always able to run that 'alter table' command successfully, as I said above, it's sporadic, but if I open a new mysql session and create the two tables and immediately alter the engine type on test1, it seems to always work. The second issue, and this is the critical failure, is that attempting to add the index to test2 should not cause MySQL to drop the table altogether.]

If there's anything else I can help you with, let me know. Again, I know this seems completely convoluted, but we really did run into this situation in our production environment and without deliberately trying to break anything.

Hi!

This is probably a duplicate of bug #5574.

Funny that 2 people report the same improbable bug at almost the same time :).

The priority of fixing this bug is low, since it only happens after a misuse of FOREIGN KEYs and MyISAM tables.

Best regards,

Heikki

I understand you think this requires a misuse of foreign keys and MyIsam tables, but as I said, this happened to us in a production environment where no one was actively trying to break anything or misuse anything. It was completely accidental.

And that piece is, as I said in my previous post on this bug, tertiary to the CRITICAL problem of MySQL completely dropping a table without the user having any clue about it. I don't see any mention of this in bug #5574. So I would say that at least that part of it is a separate bug.

Anyhow, thanks for your time and keep up all the great work. I've been using MySQL, Oracle and Sybase for years. MySQL is by far the most fun to work with.

Hi!

I think part of this bug may have been fixed in:

"
C.9.1 MySQL/InnoDB-4.0.21, September 10, 2004

If we RENAMEd a table, InnoDB forgot to load the FOREIGN KEY constraints that reference the new table name, and forgot to check that they are compatible with the table. 
"

I am changing the status of this bug report back to 'open', so that we will remember to check this when we fix #5574.

Maybe the best fix would be to forbid

ALTER TABLE ... TYPE=MyISAM;

if there are FOREIGN KEY constraints on the table, or referring the table.

About the disappearing table. I have tried to design MySQL/InnoDB such that even in bug cases in ALTER TABLE, the table is not deleted inadvertently. It might be visible as a #sql... TEMPORARY table. See 
http://dev.mysql.com/doc/mysql/en/InnoDB_troubleshooting_datadict.html

Thank you,

Heikki

Verified using 4.1.9 NT

when executing 'rename table test3 test1' I received the following error:

mysql> rename table test3 to test1;
ERROR 1025 (HY000): Error on rename of '.\test\test3' to '.\test\test1' (errno:
150)

Marko's patch will block this kind of ALTER.

Thank you for your bug report. This issue has been committed to our
source repository of that product and will be incorporated into the
next release.

If necessary, you can access the source repository and build the latest
available version, including the bugfix, yourself. More information 
about accessing the source trees is available at
    http://www.mysql.com/doc/en/Installing_source_tree.html

Additional info:

The fix of Bug #5574 fixes also this bug. It will be included in MySQL 4.1.12 and 5.0.5.

DROP DATABASE IF EXISTS test;
CREATE DATABASE test;
USE test;
CREATE TABLE test1 (test1_id INT NOT NULL PRIMARY KEY AUTO_INCREMENT, test1_id2 text) ENGINE=InnoDB;
CREATE TABLE test2 (test2_id INT NOT NULL PRIMARY KEY AUTO_INCREMENT, test1_id INT, CONSTRAINT FOREIGN KEY (test1_id) REFERENCES test1(test1_id)) ENGINE=InnoDB;
ALTER TABLE test2 MODIFY test1_id SMALLINT;

Gives:

mysql> ALTER TABLE test2 MODIFY test1_id SMALLINT;
ERROR 1025 (HY000): Error on rename of '.\test\#sql-1874_3' to '.\test\test2' (errno: 150)

On 5.1.47

See also bug #15324 and bug #55361

However, RENAME TABLE succeeds now:

mysql> RENAME TABLE test2 TO test3;
Query OK, 0 rows affected (0.01 sec)

Not just a wrong error message. It looks like a temporary table was already build, then a rename attempted. Also see just-created bug #55465.

Yes, at least some action happens:

===========
mysql> SELECT CURRENT_TIME; ALTER TABLE test2 MODIFY test1_id SMALLINT; SELECT CURRENT_TIME;
+--------------+
| CURRENT_TIME |
+--------------+
| 13:46:25     |
+--------------+

ERROR 1025 (HY000): Error on rename of '.\test\#sql-d94_1' to '.\test\test2' (errno: 150)

+--------------+
| CURRENT_TIME |
+--------------+
| 13:46:36     |
+--------------+

mysql> SELECT COUNT(*) FROM TEST2;
+----------+
| COUNT(*) |
+----------+
|   500000 |
+----------+
===========

Added to changelog for 5.6.7: 

Various inconsistent behaviors, including tables becoming
inaccessible, were cleaned up for ALTER TABLE statements involving
InnoDB tables involved in foreign key relationships.