| Bug #26507 | Error 121 upon creating InnoDB table (in replicated setup) | ||
|---|---|---|---|
| Submitted: | 20 Feb 2007 17:59 | Modified: | 16 Oct 2012 5:21 |
| Reporter: | Patrick Galbraith | Email Updates: | |
| Status: | Not a Bug | Impact on me: | |
| Category: | MySQL Server: InnoDB storage engine | Severity: | S2 (Serious) |
| Version: | 5.0.27-max-log, 5.1.36, 5.0, 5.1 bzr | OS: | Linux (Linux, Fedora Core 6) |
| Assigned to: | Jimmy Yang | CPU Architecture: | Any |
[21 Feb 2007 8:18]
Sveta Smirnova
Thank you for the report. Please, send us content of the error log file.
[21 Feb 2007 15:18]
Patrick Galbraith
Ok, even stranger, if I create the table with a different table name, it works, but when I use 'profiles', it fails! mysql> connect grazr_accounts Connection id: 33 Current database: grazr_accounts mysql> CREATE TABLE `bug26507` ( `profile_id` bigint(20) NOT NULL auto_increment, `uid` bigint(20) NOT NULL, `name` varchar(32) NOT NULL default '', `title` varchar(64) NOT NULL default '', `last_update` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP, `access` int(4) default NULL, PRIMARY KEY (`profile_id`), KEY `uid` (`uid`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1; Query OK, 0 rows affected (0.00 sec) mysql> show create table bug26507 -> ; +----------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | Table | Create Table | +----------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | bug26507 | CREATE TABLE `bug26507` ( `profile_id` bigint(20) NOT NULL auto_increment, `uid` bigint(20) NOT NULL, `name` varchar(32) NOT NULL default '', `title` varchar(64) NOT NULL default '', `last_update` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP, `access` int(4) default NULL, PRIMARY KEY (`profile_id`), KEY `uid` (`uid`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1 | +----------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ 1 row in set (0.00 sec) mysql> drop table bug26507 -> ; Query OK, 0 rows affected (0.00 sec) mysql> drop table profiles; Query OK, 0 rows affected (0.00 sec) mysql> CREATE TABLE `profiles` ( `profile_id` bigint(20) NOT NULL auto_increment, `uid` bigint(20) NOT NULL, `name` varchar(32) NOT NULL default '', `title` varchar(64) NOT NULL default '', `last_update` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP, `access` int(4) default NULL, PRIMARY KEY (`profile_id`), KEY `uid` (`uid`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1; ERROR 1005 (HY000): Can't create table './grazr_accounts/profiles.frm' (errno: 121) mysql> I will attach the snippit from the log containing the output from this error.
[21 Feb 2007 15:24]
MySQL Verification Team
#define HA_ERR_FOUND_DUPP_KEY 121 /* Dupplicate key on write */
[21 Feb 2007 15:26]
Heikki Tuuri
Patrick, is it this problem: heikki@ws38:~/mysql-5.0/sql$ ./mysqld 070221 17:25:10 [Warning] Changed limits: max_open_files: 1024 max_connections: 886 table_cache: 64 070221 17:25:10 InnoDB: Started; log sequence number 0 46521 070221 17:25:10 [Note] /home/heikki/mysql-5.0/sql/.libs/lt-mysqld: ready for connections. Version: '5.0.38-debug-log' socket: '/home/heikki/bugsocket' port: 3307 Source distribution 070221 17:25:17 InnoDB: Error: table `test/t` already exists in InnoDB internal InnoDB: data dictionary. Have you deleted the .frm file InnoDB: and not used DROP TABLE? Have you used DROP DATABASE InnoDB: for InnoDB tables in MySQL version <= 3.23.43? InnoDB: See the Restrictions section of the InnoDB manual. InnoDB: You can drop the orphaned table inside InnoDB by InnoDB: creating an InnoDB table with the same name in another InnoDB: database and copying the .frm file to the current database. InnoDB: Then MySQL thinks the table exists, and DROP TABLE will InnoDB: succeed. InnoDB: You can look for further help from InnoDB: http://dev.mysql.com/doc/refman/5.0/en/innodb-troubleshooting.html heikki@ws38:~/mysql-5.0/client$ ./mysql -uroot test Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 1 Server version: 5.0.38-debug-log Source distribution Type 'help;' or '\h' for help. Type '\c' to clear the buffer. mysql> create table t(a int) engine = innodb; ERROR 1005 (HY000): Can't create table './test/t.frm' (errno: 121) mysql> ? Regards, Heikki
[21 Feb 2007 15:35]
Patrick Galbraith
log file output for this particular problem
Attachment: bug26507.log (application/octet-stream, text), 2.87 KiB.
[21 Feb 2007 15:38]
Patrick Galbraith
Heikki, Why yes, it certainly is! See attached file. Right as I saw your reply in the bug, I saw this same error in the log. Strangely enough, if I create this same table on the other master, server id 2, it works, but complains in the slave log for it failing on server id 1 Thanks!
[21 Feb 2007 15:39]
Heikki Tuuri
Patrick, this may be a replication bug. How did you end up having the table created inside InnoDB, but with no .frm file? Did you delete files manually? Regards, Heikki
[21 Feb 2007 15:42]
Patrick Galbraith
Heikki, The only files I have deleted in any of this setup are the relay logs. What I could do is start over with new ndb files (this is a new setup, so I can afford to do this at this stage). I am doing nothing different between both servers other than server id and hostname. Again, Thanks!
[21 Feb 2007 15:51]
Patrick Galbraith
Ok, so the conclusion is, I started with a clean slate and this all worked. Not sure what caused this failure in the first place as I never deleted anything but relay logs to clear other replication errors. I'm not sure whether this is a bug or something that needs to be documented, as I don't know what caused this to fail in the first place. I'm sure I could reproduce it by stopping the server, deleting the .frm files for the given tables, then restarting, and re-creating the tables that had their .frm files deleted. Again, I never deleted the .frm files, so in my opinion, this might be a user situation that needs a little documentation. Question: When you drop an innodb table, is the frm file deleted before or after it is deleted from innodb's data dictionary? Maybe something like that could have ocurred.
[21 Feb 2007 16:33]
Heikki Tuuri
Patrick, when an InnoDB table is created, MySQL first creates the .frm file. Conversely, when an InnoDB table is dropped, MySQL last drops the .frm file. But there may be some cleanup operation in replication that removes the .frm file if a table create operation or drop operation was incompletely processed in replication. This is yet another bug that comes from the fact that DDL operations are not 'atomic' in MySQL + storage engine. Regards, Heikki
[16 Mar 2007 13:09]
Valeriy Kravchuk
Please, try to repeat with a newer version, 5.0.37, and inform about the results.
[16 Apr 2007 23:00]
Bugs System
No feedback was provided for this bug for over a month, so it is being suspended automatically. If you are able to provide the information that was originally requested, please do so and change the status of the bug back to "Open".
[6 Feb 2008 20:42]
PELLICIER Raphaël
I have the same difficulty with mySQL 5.0.37 I tried to create a table deleted in PHP and I had Can't create table '.\arbo\arbo_meta_fr.frm' (errno: 121). ID = 1005 Solution : in the end of the request add " ENGINE = MYISAM "
[7 Feb 2008 16:49]
Valeriy Kravchuk
All reporters: Please, try to repeat with a newer version, 5.0.51a, and inform about the results.
[8 Mar 2008 0:00]
Bugs System
No feedback was provided for this bug for over a month, so it is being suspended automatically. If you are able to provide the information that was originally requested, please do so and change the status of the bug back to "Open".
[16 Jun 2008 19:32]
Mark Strecker
I also had this failure, but with slightly different circumstances. I am replicating on XP with MySQL 5.0.45. I altered an existing table on the master to add a foreign key and it worked on the master and failed on the slave with errno: 121. I restarted the slave (stop slave;start slave;) and it ran fine. The index is now on the slave and replication continues. I do wonder about the comments asking if it still fails in 5.0.51a. Is there a fix which applies to this?
[17 Jun 2008 8:56]
Susanne Ebrecht
Many thanks for feedback but we need to know this for newer version. All reporters, please try with our newest version MySQL 5.0.51b and let us know if you still will get this weird behaviour.
[3 Jul 2008 13:26]
Janne Pikkarainen
We just did hit this bug. The MySQL version in use is the 5.0.22 Red Hat ships with Red Hat Enterprise Linux 5.1, so this bug report can be a false alarm. Anyway, worth reporting. The following CREATE clause borks with Error 121 when we use InnoDB tables: --- CREATE TABLE `ITEM_TEST` ( `ID` bigint(20) NOT NULL auto_increment, `LAST_MODIFIED` datetime default NULL, `IS_AUCTION` bit(1) NOT NULL, `BIDDER_COUNT` int(11) NOT NULL, `IS_BUYNOW` bit(1) NOT NULL, `PRICE_BUYNOW` bigint(20) default NULL, `CITY` varchar(100) NOT NULL, `ITEM_CONDITION` int(11) NOT NULL, `COUNTRY_ID` varchar(3) NOT NULL, `CURRENT_PRICE` bigint(20) NOT NULL, `DESCRIPTION` text, `END_TIME` datetime NOT NULL, `MIN_PRICE_INCREASE` bigint(20) NOT NULL, `MIN_PRICE_TO_SELL` bigint(20) default NULL, `NAME` varchar(255) NOT NULL, `ORIGINAL_QUANTITY` int(11) NOT NULL, `OTHER_DELIVERYMETHOD` text, `BANKTRANSFER` bit(1) default NULL, `CASH` bit(1) default NULL, `CASHONDELIVERY` bit(1) default NULL, `OTHER` bit(1) default NULL, `QUANTITY` int(11) NOT NULL, `RESTRICT_IDENTIFIED` bit(1) default NULL, `RESTRICT_MIN_FB` int(11) default NULL, `STARTING_PRICE` bigint(20) NOT NULL, `STARTING_TIME` datetime NOT NULL, `STATUS` int(11) default NULL, `VAT` int(11) default NULL, `PRIMARY_CATEGORY` bigint(20) NOT NULL, `USER_ID` bigint(20) NOT NULL, `SHOP_ID` bigint(20) default NULL, PRIMARY KEY (`ID`), KEY `FK_SHOP_ID_2` (`SHOP_ID`), KEY `FK_PRIMARY_CATEGORY_ID` (`PRIMARY_CATEGORY`), KEY `FK_SELLER_ID` (`USER_ID`), KEY `FK_STATUS` USING BTREE (`STATUS`), KEY `FK_END_TIME` (`END_TIME`), KEY `FK_STATUS_2` (`STATUS`,`END_TIME`), CONSTRAINT `FK_PRIMARY_CATEGORY_ID` FOREIGN KEY (`PRIMARY_CATEGORY`) REFERENCES `CATEGORY` (`ID`), CONSTRAINT `FK_SELLER_ID` FOREIGN KEY (`USER_ID`) REFERENCES `HUUTO_USER` (`ID`) ON DELETE CASCADE, CONSTRAINT `FK_SHOP_ID_2` FOREIGN KEY (`SHOP_ID`) REFERENCES `SHOP` (`ID`) ) DEFAULT CHARSET=utf8; --- But it works, if we remove the USING btree declaration near the end. In other words, if this line --- KEY `FK_STATUS` USING BTREE (`STATUS`), --- is changed to --- KEY `FK_STATUS` (`STATUS`), --- then table creation works. I hope this helps someone.
[17 Jul 2008 23:00]
Bugs System
No feedback was provided for this bug for over a month, so it is being suspended automatically. If you are able to provide the information that was originally requested, please do so and change the status of the bug back to "Open".
[15 Jun 2010 16:22]
James McKelvey
We are seeing this same bug on 5.1-36-log, X86_64. The table cannot be created as InnoDB, with errno 121 as reported before. It does not show up in any of the information_schema or mysql tables. It can be created as MyISAM. If we then try to change the engine to InnoDB, it fails with 121 again. A second try succeeds. We are not doing replication. Log entries are as previously reported.
[15 Jun 2010 16:40]
Sveta Smirnova
Thank you for the feedback. Which filesystem do you use?
[15 Jun 2010 16:44]
James McKelvey
The DB is on ext3.
[15 Jun 2010 17:04]
Sveta Smirnova
Thank you for the feedback. Is it possible if MySQL binaries or logs are at NAS, NFS or other network solution?
[15 Jun 2010 17:22]
James McKelvey
On one of the hosts, it is direct attached RAID. On the other, direct attached local discs.
[15 Jun 2010 17:28]
Sveta Smirnova
Thank you for the feedback. Please also check if this still exists in current version 5.1.47.
[15 Jun 2010 19:43]
James McKelvey
mysql> DROP TABLE IF EXISTS Host; Query OK, 0 rows affected, 1 warning (0.00 sec) mysql> CREATE TABLE Host(x int) ENGINE=InnoDB; ERROR 1005 (HY000): Can't create table 'msl_gdsdb_v5_0.Host' (errno: 121) mysql> CREATE TABLE Host(x int) ENGINE=MyIsam; Query OK, 0 rows affected (0.00 sec) mysql> ALTER TABLE Host Engine=InnoDB; ERROR 1025 (HY000): Error on rename of './msl_gdsdb_v5_0/#sql-504f_1' to './msl_gdsdb_v5_0/Host' (errno: 121) mysql> ALTER TABLE Host Engine=InnoDB; Query OK, 0 rows affected (0.00 sec) Records: 0 Duplicates: 0 Warnings: 0 OK, the above shows what we see. We have an existing database which we copy, and then try to upgrade the copy. The trick of creating as MyIsam and then changing the engine to InnoDB works, but only on the second try. We can repeat, but we have to do the copy each time.
[16 Jun 2010 7:35]
Francesc Ortiz
I had error 121. The error didn't appear when i removed one of the foreign keys in the create table query. I could avoid the error by duplicating another table with the same structure and giving it the desired name.
[23 Jun 2010 7:25]
Sveta Smirnova
Thank you for the feedback. This can happen if slave started without InnoDB support due to error or option, then table was deleted. Verified using following test case: $./libexec/mysqld --defaults-file=support-files/my-small.cnf --basedir=. --datadir=./data --log-error --port=33051 --socket=/tmp/mysql_ssmirnova.sock & [1] 29699 $100623 9:19:49 [Warning] '--skip-locking' is deprecated and will be removed in a future release. Please use '--skip-external-locking' instead. 100623 9:19:49 [Warning] '--default-character-set' is deprecated and will be removed in a future release. Please use '--character-set-server' instead. $./bin/mysql -uroot -S /tmp/mysql_ssmirnova.sock test Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 1 Server version: 5.1.49-debug-log Source distribution Copyright (c) 2000, 2010, Oracle and/or its affiliates. All rights reserved. This software comes with ABSOLUTELY NO WARRANTY. This is free software, and you are welcome to modify and redistribute it under the GPL v2 license Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. mysql> CREATE TABLE `profiles` ( `profile_id` bigint(20) NOT NULL auto_increment, `uid` bigint(20) NOT NULL, `name` varchar(32) NOT NULL default '', `title` varchar(64) NOT NULL default '', `last_update` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP, `access` int(4) default NULL, PRIMARY KEY (`profile_id`), KEY `uid` (`uid`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1; Query OK, 0 rows affected (0.01 sec) mysql> \q Bye $./bin/mysqladmin shutdown -uroot -S /tmp/mysql_ssmirnova.sock [1]+ Done ./libexec/mysqld --defaults-file=support-files/my-small.cnf --basedir=. --datadir=./data --log-error --port=33051 --socket=/tmp/mysql_ssmirnova.sock $./libexec/mysqld --defaults-file=support-files/my-small.cnf --basedir=. --datadir=./data --log-error --port=33051 --socket=/tmp/mysql_ssmirnova.sock --skip-innodb & [1] 29718 $100623 9:20:17 [Warning] '--skip-locking' is deprecated and will be removed in a future release. Please use '--skip-external-locking' instead. 100623 9:20:17 [Warning] '--default-character-set' is deprecated and will be removed in a future release. Please use '--character-set-server' instead. $./bin/mysql -uroot -S /tmp/mysql_ssmirnova.sock test Reading table information for completion of table and column names You can turn off this feature to get a quicker startup with -A Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 1 Server version: 5.1.49-debug-log Source distribution Copyright (c) 2000, 2010, Oracle and/or its affiliates. All rights reserved. This software comes with ABSOLUTELY NO WARRANTY. This is free software, and you are welcome to modify and redistribute it under the GPL v2 license Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. mysql> drop table profiles;Query OK, 0 rows affected (0.00 sec) mysql> \q Bye $./bin/mysqladmin shutdown -uroot -S /tmp/mysql_ssmirnova.sock [1]+ Done ./libexec/mysqld --defaults-file=support-files/my-small.cnf --basedir=. --datadir=./data --log-error --port=33051 --socket=/tmp/mysql_ssmirnova.sock --skip-innodb $./libexec/mysqld --defaults-file=support-files/my-small.cnf --basedir=. --datadir=./data --log-error --port=33051 --socket=/tmp/mysql_ssmirnova.sock & [1] 29727 $100623 9:20:36 [Warning] '--skip-locking' is deprecated and will be removed in a future release. Please use '--skip-external-locking' instead. 100623 9:20:36 [Warning] '--default-character-set' is deprecated and will be removed in a future release. Please use '--character-set-server' instead. $./bin/mysql -uroot -S /tmp/mysql_ssmirnova.sock test Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 1 Server version: 5.1.49-debug-log Source distribution Copyright (c) 2000, 2010, Oracle and/or its affiliates. All rights reserved. This software comes with ABSOLUTELY NO WARRANTY. This is free software, and you are welcome to modify and redistribute it under the GPL v2 license Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. mysql> CREATE TABLE `profiles` ( `profile_id` bigint(20) NOT NULL auto_increment, `uid` bigint(20) NOT NULL, `name` varchar(32) NOT NULL default '', `title` varchar(64) NOT NULL default '', `last_update` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP, `access` int(4) default NULL, PRIMARY KEY (`profile_id`), KEY `uid` (`uid`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1; ERROR 1005 (HY000): Can't create table 'test.profiles' (errno: 121) mysql> CREATE TABLE `profiles` ( `profile_id` bigint(20) NOT NULL auto_increment, `uid` bigint(20) NOT NULL, `name` varchar(32) NOT NULL default '', `title` varchar(64) NOT NULL default '', `last_update` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP, `access` int(4) default NULL, PRIMARY KEY (`profile_id`), KEY `uid` (`uid`) ) ENGINE=MyISAM DEFAULT CHARSET=latin1; Query OK, 0 rows affected (0.01 sec) mysql> alter table profiles engine=innodb; ERROR 1050 (42S01): Table './test/profiles' already exists mysql> alter table profiles engine=innodb; Query OK, 0 rows affected (0.01 sec) Records: 0 Duplicates: 0 Warnings: 0 In my opinion main problem here is confusing error message.
[28 Jun 2010 15:23]
Mikhail Izioumtchenko
Sveta, which error message do you think is confusing. here's how I understand it, please correct me if I'm wrong: - create an innodb table - restart mysqld without innodb support, drop the table - restart mysqld with innodb support # now we have a situation where there's a table in InnoDB data dictionary, # the .ibd file or the table data in the system tablespace, but no .frm file - recreate the .frm file by creating the identical MyIsam table - alter table engine=innodb to me, there are three questions, with #2 looking like an InnoDB bug. 1. is it a legitimate recovery procedure for the loss of the .frm file? I wonder if we should just say 'please recover the .frm file from backup' 2. nevertheless, why does the first ALTER TABLE fail while the second succeed? 3. out of curiosity, what happens with the table data in InnoDB if there was some? ALTER implies it should be replaced with an empty table.
[29 Jun 2010 5:43]
Sveta Smirnova
Michael, procedure to repeat the bug is correct. > 1. is it a legitimate recovery procedure for the loss of the .frm file? > I wonder if we should just say 'please recover the .frm file from backup' If you mean record in the error log it is what usually printed there in such cases. > 2. nevertheless, why does the first ALTER TABLE fail while the second succeed? Yes, I agree this is a bug too. Also error "ERROR 1005 (HY000): Can't create table 'test.profiles' (errno: 121)" on create looks confusing. perror returns "OS error code 121: Remote I/O error", but this can be InnoDB error. > 3. out of curiosity, what happens with the table data in InnoDB if there was some? > ALTER implies it should be replaced with an empty table. Same behavior if table has data. SELECT count(*) from this table returns 0.
[29 Jun 2010 13:14]
Mikhail Izioumtchenko
both cases innodb_file_per_table=0|should be tested. The question is, how InnoDB reacts to an ALTER TABLE foo engine=innodb when there's already foo in InnoDB data dictionary [lacking an InnoDB table foo.frm file]. I'd say InnoDB should do what ALTER demands, removing the previously existing foo table record and its data. Without being asked twice. This amounts to performing a DROP TABLE foo first internally. A question arises what happens if the resulting table foo data that came from MyISAM, violates a foreign key constraint. In this case I'd suggest throwing a relevant error and refusing ALTER as I guess we'do for any other ALTER of the kind.
[30 Sep 2010 4:03]
Jimmy Yang
In the errorlog output for the alter table, it stated how we drop the orphan table:
InnoDB: You can drop the orphaned table inside InnoDB by
InnoDB: creating an InnoDB table with the same name in another
InnoDB: database and copying the .frm file to the current database.
InnoDB: Then MySQL thinks the table exists, and DROP TABLE will
InnoDB: succeed.
Interestingly, in our case, the first alter table drop the InnoDB table (I guess not intentionally) when it did post failure processing:
Stack:
row_drop_table_for_mysql (name=0xa9146df4 "test/t1"...)
ha_innobase::delete_table
handler::ha_delete_table
ha_delete_table
quick_rm_table
mysql_alter_table
In mysql_alter_table(), it tries to remove the table as it thought it is created in this alter table operation.
mysql_alter_table()
{
...
else if (mysql_rename_table())
{
/* Try to get everything back. */
error=1;
VOID(quick_rm_table(new_db_type,new_db,new_alias, 0));
VOID(quick_rm_table(new_db_type, new_db, tmp_name, FN_IS_TMP));
VOID(mysql_rename_table(old_db_type, db, old_name, db, alias,
FN_FROM_IS_TMP));
}
So the first alter table operation actually cleaned up the InnoDB orphan table. So subsequent create table or alter table would succeed. This operation (drop InnoDB table) is instructed by code in MySQL layer, which did not expect there is an orphan table.
The root cause of these issue is still we keep two table metadata, and there is way to make them inconsistent.
The suggested way to deal with orphan table is still:
InnoDB: You can drop the orphaned table inside InnoDB by
InnoDB: creating an InnoDB table with the same name in another
InnoDB: database and copying the .frm file to the current database.
InnoDB: Then MySQL thinks the table exists, and DROP TABLE will
InnoDB: succeed.
[2 Nov 2012 20:04]
Sveta Smirnova
Reason to close this was: "there will be always way to create mismatched dictionary between InnoDB and MySQL, and we have the suggestion how to resolve it." This is just gotcha of storage engine feature and actually not a bug.
Description: I cannot create a legitimate InnoDB table in a database that is being replicated because of this error: mysql> CREATE TABLE `profiles` ( -> `profile_id` bigint(20) NOT NULL auto_increment, -> `uid` bigint(20) NOT NULL, -> `name` varchar(32) NOT NULL default '', -> `title` varchar(64) NOT NULL default '', -> `last_update` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP, -> `access` int(4) default NULL, -> PRIMARY KEY (`profile_id`), -> KEY `uid` (`uid`) -> ) ENGINE=InnoDB DEFAULT CHARSET=latin1; ERROR 1005 (HY000): Can't create table './grazr_accounts/profiles.frm' (errno: 121) The error is this: [root@db1 var]# perror 121 OS error code 121: Remote I/O error If I create this same table with MyISAM, it works: mysql> CREATE TABLE `profiles` ( `profile_id` bigint(20) NOT NULL auto_increment, `uid` bigint(20) NOT NULL, `name` varchar(32) NOT NULL default '', `title` varchar(64) NOT NULL default '', `last_update` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP, `access` int(4) default NULL, PRIMARY KEY (`profile_id`), KEY `uid` (`uid`) ) ENGINE=MyISAM DEFAULT CHARSET=latin1; Query OK, 0 rows affected (0.00 sec) Also, if I do this via a non-replicated db: mysql> CREATE TABLE `profiles` ( `profile_id` bigint(20) NOT NULL auto_increment, `uid` bigint(20) NOT NULL, `name` varchar(32) NOT NULL default '', `title` varchar(64) NOT NULL default '', `last_update` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP, `access` int(4) default NULL, PRIMARY KEY (`profile_id`), KEY `uid` (`uid`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1; ERROR 1005 (HY000): Can't create table './grazr_accounts/profiles.frm' (errno: 121) mysql> connect foo; Connection id: 8 Current database: foo mysql> CREATE TABLE `profiles` ( `profile_id` bigint(20) NOT NULL auto_increment, `uid` bigint(20) NOT NULL, `name` varchar(32) NOT NULL default '', `title` varchar(64) NOT NULL default '', `last_update` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP, `access` int(4) default NULL, PRIMARY KEY (`profile_id`), KEY `uid` (`uid`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1; Query OK, 0 rows affected (0.01 sec) Here are my specifics: mysql> select version(); +----------------+ | version() | +----------------+ | 5.0.27-max-log | +----------------+ 1 row in set (0.00 sec) Here is my my.cnf: [mysqld] port = 3306 sock = /tmp/mysql.sock skip-locking key_buffer = 256M max_allowed_packet = 1M table_cache = 256 sort_buffer_size = 1M read_buffer_size = 1M read_rnd_buffer_size = 4M myisam_sort_buffer_size = 64M thread_cache_size = 8 query_cache_size = 16M # Try number of CPU's*2 for thread_concurrency thread_concurrency = 4 # InnoDB parameters innodb_data_home_dir = /usr/local/mysql/var/ innodb_data_file_path = ibdata1:10M:autoextend innodb_log_group_home_dir = /usr/local/mysql/var/ innodb_log_arch_dir = /usr/local/mysql/var/ # You can set .._buffer_pool_size up to 50 - 80 % # of RAM but beware of setting memory usage too high innodb_buffer_pool_size = 256M innodb_additional_mem_pool_size = 20M # Set .._log_file_size to 25 % of buffer pool size innodb_log_file_size = 64M innodb_log_buffer_size = 8M innodb_flush_log_at_trx_commit = 1 innodb_lock_wait_timeout = 50 datadir = /usr/local/mysql/var server-id = 1 log-bin = /usr/local/mysql/var/bin.log log-bin-index = /usr/local/mysql/var/log-bin.index log-error = /usr/local/mysql/var/error.log relay-log = /usr/local/mysql/var/relay.log relay-log-info-file = /usr/local/mysql/var/relay-log.info relay-log-index = /usr/local/mysql/var/relay-log.index auto_increment_increment = 10 auto_increment_offset = 1 master-host = db2.grazr.com master-user = rpluserfoo master-password = foopass replicate-do-db = test replicate-do-db = grazr_accounts How to repeat: Set up replication, multi-master, create an innodb table: CREATE TABLE `profiles` ( `profile_id` bigint(20) NOT NULL auto_increment, `uid` bigint(20) NOT NULL, `name` varchar(32) NOT NULL default '', `title` varchar(64) NOT NULL default '', `last_update` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP, `access` int(4) default NULL, PRIMARY KEY (`profile_id`), KEY `uid` (`uid`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1;