MySQL Bugs: #18706: InnoDB Tablespace per Table Symlink causes InnoDB Cache Load Error

Bug #18706	InnoDB Tablespace per Table Symlink causes InnoDB Cache Load Error
Submitted:	1 Apr 2006 10:25	Modified:	3 Apr 2006 6:59
Reporter:	time e.less	Email Updates:
Status:	Not a Bug	Impact on me:	None
Category:	MySQL Server: InnoDB storage engine	Severity:	S2 (Serious)
Version:	5.0.16	OS:	Linux (Debian Linux AMD Opteron)
Assigned to:	Heikki Tuuri	CPU Architecture:	Any

Description:
Despite the dates involved, this is not an April Fools' joke. It just happened to happen today.

Apr  1 01:31:38 d41 mysqld[3531]: 060401  1:31:38  InnoDB: error: space object of table production/commentdiggs,
Apr  1 01:31:38 d41 mysqld[3531]: InnoDB: space id 23 did not exist in memory. Retrying an open.
Apr  1 01:31:38 d41 mysqld[3531]: InnoDB: Error: trying to add tablespace 23 of name './production/commentdiggs.ibd'
Apr  1 01:31:38 d41 mysqld[3531]: InnoDB: to the tablespace memory cache, but tablespace
Apr  1 01:31:38 d41 mysqld[3531]: InnoDB: 23 of name './disk2/commentdiggs.ibd' already exists in the tablespace

It did this for several tables, not just this one. Then finally segfaulted and gave up the ghost.

How to repeat:
Use innodb_file_per_table. Alter a few high-transaction tables to engine=InnoDB to get an .ibd file per table. Shut down MySQL. Move the file to another physical disk. Symlink .ibds. Start MySQL again. Run for a while at high transaction volume. Something like the following:

#  mysql -uroot -e "select concat('alter table ',table_name,' engine=InnoDB;') from tables where engine='InnoDB' and table_schema='production'" information_schema | grep -v concat | mysql -uroot production
# /etc/init.d/mysql stop
# cd /var/lib/mysql/production
# mv *.ibd ../disk2/
# ln -s ../disk2/*.ibd ./
# /etc/init.d/mysql start

Suggested fix:
I will be reading the documentation to see if MySQL has a recommended way of putting the .ibd files onto another physical disk, but this method seemed the obvious solution to me. Perhaps you could support it, or disallow symlinking to .ibd files at MySQL startup time?

Or, perhaps the bug has something to do with the fact that the second disk is mounted inside /var/lib/mysql (the MySQL data directory)?

Timeless,

symlinking .ibd files should work on Unix. Please test with some small test table.

The errors you got may be a result of moving .ibd files around. Please double check that you did not make that error.

Note that an ALTER TABLE will rebuild the table physically to back to the database directory, as it is not aware that the original table was symlinked. Therefore, it is better to symlink whole database directories.

Regards,

Heikki

Good day, Heikki!

"The errors you got may be a result of moving .ibd files around. Please double check that you did not make that error."

Do you mean to say that while MySQL server is currently running, the .ibd is moved and symlinked? I thought doing such a thing wouldn't work, so I took steps to avoid doing that, but it was late. Is this the only known case where this error occurs?

"Note that an ALTER TABLE will rebuild the table physically to back to the database directory, as it is not aware that the original table was symlinked. Therefore, it is better to symlink whole database directories."

So if I do this:

# Shut down MySQL
# Move the .ibd
# Symlink it
# Start MySQL
# alter table engine=InnoDB

This could cause the problem? When I try this on a test environment, I simply end up with a .ibd in both locations, and the original symlink is removed.

Timeless,

if you want to move an InnoDB table t from a database a to database b, you must use RENAME TABLE:

RENAME TABLE a.t TO b.t;

You cannot move .ibd files around by copying them yourself (except in the case where you physically move the .ibd file and replace it with a symlink). The InnoDB internal data dictionary is in the the ibdata1 file, and it will be confused if it does not find the .ibd file from the directory where it expects to find it.

http://dev.mysql.com/doc/refman/5.0/en/multiple-tablespaces.html

Regards,

Heikki

Classifying this as not a bug, as this was probably a user error, moving .ibd files around. If you encounter the error again, please reopen this bug report.