Description:
Hi dear all,
Here is the reproduce path:
mysql> use test;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Database changed
mysql> CREATE TABLE `t2` ( `c1` int(11) NOT NULL, PRIMARY KEY (`c1`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1 data directory='/tmp/foo';
Query OK, 0 rows affected (0,33 sec)
mysql> insert into t2 values(1);
Query OK, 1 row affected (0,06 sec)
mysql> insert into t2 values(2);
Query OK, 1 row affected (0,06 sec)
Normal shutdown:
root@shrzayev:~# service mysql stop
......
* MySQL Community Server 5.6.29 is stopped
Moving t2.ibd file from /tmp/foo/test to /var/lib/mysql/test and removing original path:
root@shrzayev:/tmp/foo/test# mv t2.ibd /var/lib/mysql/test/
root@shrzayev:/var/lib/mysql/test# ls -l
total 224
-rw-r----- 1 mysql mysql 65 Mar 5 22:17 db.opt
-rw-rw---- 1 mysql mysql 8556 Mar 5 22:50 t1.frm
-rw-r----- 1 mysql mysql 98304 Mar 5 22:52 t1.ibd
-rw-rw---- 1 mysql mysql 8556 Mar 6 13:09 t2.frm
-rw-rw---- 1 mysql mysql 98304 Mar 6 13:09 t2.ibd
-rw-rw---- 1 mysql mysql 20 Mar 6 13:09 t2.isl
root@shrzayev:/var/lib/mysql/test# cat t2.isl
/tmp/foo/test/t2.ibd
root@shrzayev:/tmp# rm -rf foo/
Starting MySQL:
2016-03-06 13:14:26 7f0698b15780 InnoDB: Operating system error number 2 in a file operation.
InnoDB: The error means the system cannot find the path specified.
InnoDB: If you are installing InnoDB, remember that you must create
InnoDB: directories yourself, InnoDB does not create them.
2016-03-06 13:14:26 8751 [ERROR] InnoDB: A link file was found named './test/t2.isl' but the linked tablespace '/tmp/foo/test/t2.ibd' could not be opened.
2016-03-06 13:14:26 8751 [Note] InnoDB: The InnoDB data dictionary table SYS_DATAFILES for tablespace ID 15 was updated to use file ./test/t2.ibd.
So it seems that, changes are reflected as shown in error log and in fact we can access table:
mysql> select * from t2;
+----+
| c1 |
+----+
| 1 |
| 2 |
+----+
2 rows in set (0,00 sec)
But if you try to drop table:
mysql> drop table t2;
ERROR 2013 (HY000): Lost connection to MySQL server during query
From error log:
2016-03-06 13:17:22 7f02307d3700 InnoDB: Assertion failure in thread 139647380174592 in file row0mysql.cc line 4202
InnoDB: Failing assertion: table->data_dir_path
/usr/sbin/mysqld[0x9cdebe]
/usr/sbin/mysqld[0x936b83]
/usr/sbin/mysqld(_Z15ha_delete_tableP3THDP10handlertonPKcS4_S4_b+0xb4)[0x5b9194]
/usr/sbin/mysqld(_Z23mysql_rm_table_no_locksP3THDP10TABLE_LISTbbbb+0x4ed)[0x73df0d]
/usr/sbin/mysqld(_Z14mysql_rm_tableP3THDP10TABLE_LISTcc+0x11f)[0x73f0df]
/usr/sbin/mysqld(_Z21mysql_execute_commandP3THD+0x3139)[0x6ed559]
/usr/sbin/mysqld(_Z11mysql_parseP3THDPcjP12Parser_state+0x368)[0x6f0c08]
/usr/sbin/mysqld(_Z16dispatch_command19enum_server_commandP3THDPcj+0x100f)[0x6f23af]
/usr/sbin/mysqld(_Z24do_handle_one_connectionP3THD+0x142)[0x6bfba2]
/usr/sbin/mysqld(handle_one_connection+0x40)[0x6bfc50]
/usr/sbin/mysqld(pfs_spawn_thread+0x143)[0x915273]
/lib/x86_64-linux-gnu/libpthread.so.0(+0x8182)[0x7f0244a47182]
/lib/x86_64-linux-gnu/libc.so.6(clone+0x6d)[0x7f0243f5447d]
From core dump:
[Thread debugging using libthread_db enabled]
Using host libthread_db library "/lib/x86_64-linux-gnu/libthread_db.so.1".
Core was generated by `/usr/sbin/mysqld --basedir=/usr --datadir=/var/lib/mysql --plugin-dir=/usr/lib/'.
Program terminated with signal SIGABRT, Aborted.
#0 0x00007f0697caf621 in __pthread_kill (threadid=<optimized out>, signo=6) at ../nptl/sysdeps/unix/sysv/linux/pthread_kill.c:61
61 ../nptl/sysdeps/unix/sysv/linux/pthread_kill.c: No such file or directory.
Traceback (most recent call last):
File "/usr/share/gdb/auto-load/usr/lib/x86_64-linux-gnu/libstdc++.so.6.0.19-gdb.py", line 63, in <module>
from libstdcxx.v6.printers import register_libstdcxx_printers
ImportError: No module named 'libstdcxx'
(gdb) bt
#0 0x00007f0697caf621 in __pthread_kill (threadid=<optimized out>, signo=6) at ../nptl/sysdeps/unix/sysv/linux/pthread_kill.c:61
#1 0x000000000067062d in handle_fatal_signal ()
#2 <signal handler called>
#3 0x00007f06970f3cc9 in __GI_raise (sig=sig@entry=6) at ../nptl/sysdeps/unix/sysv/linux/raise.c:56
#4 0x00007f06970f70d8 in __GI_abort () at abort.c:89
#5 0x00000000009cdebe in ?? ()
#6 0x0000000000936b83 in ?? ()
#7 0x00000000005b9194 in ha_delete_table(THD*, handlerton*, char const*, char const*, char const*, bool) ()
#8 0x000000000073df0d in mysql_rm_table_no_locks(THD*, TABLE_LIST*, bool, bool, bool, bool) ()
#9 0x000000000073f0df in mysql_rm_table(THD*, TABLE_LIST*, char, char) ()
#10 0x00000000006ed559 in mysql_execute_command(THD*) ()
#11 0x00000000006f0c08 in mysql_parse(THD*, char*, unsigned int, Parser_state*) ()
#12 0x00000000006f23af in dispatch_command(enum_server_command, THD*, char*, unsigned int) ()
#13 0x00000000006bfba2 in do_handle_one_connection(THD*) ()
#14 0x00000000006bfc50 in handle_one_connection ()
#15 0x0000000000915273 in pfs_spawn_thread ()
#16 0x00007f0697caa182 in start_thread (arg=0x7f0683a36700) at pthread_create.c:312
#17 0x00007f06971b747d in clone () at ../sysdeps/unix/sysv/linux/x86_64/clone.S:111
How to repeat:
See description.
Suggested fix:
Maybe such operation is not allowed. If so it should be indicated clearly.
From error log it is clear that the new tablespace path is updated. So it is a quite true action.