MySQL Bugs: #104663: Cannot rename table after moving the table.ibd to another disk (innodb

Bug #104663	Cannot rename table after moving the table.ibd to another disk (innodb_dir)
Submitted:	19 Aug 2021 7:55	Modified:	16 Sep 2021 14:12
Reporter:	Jehu Thomas	Email Updates:
Status:	Unsupported	Impact on me:	None
Category:	MySQL Server: Errors	Severity:	S2 (Serious)
Version:	8.0.26	OS:	CentOS (7.9.2009)
Assigned to:		CPU Architecture:	x86
Tags:	Cannot rename table, innodb_directories

Description:
Hi,

We have recently moved some 4TB of tables from the datadir (8TB disk) to innodb_directories (3 different disks) as there are issues in using a disk that is more than 4TB in Azure.

We followed the procedure mentioned in this link
https://dev.mysql.com/doc/refman/8.0/en/innodb-moving-data-files-offline.html

Everything worked fine after this procedure but recently we noticed that we cannot alter any tables that are in the 'innodb_directories'. It throws the below error though the table exists in the 'innodb_directories'. It seems to be looking for the table in the 'datadir' instead of the 'innodb_directories'.

[ERROR] [MY-012118] [InnoDB] Cannot rename '.dbname/table2.ibd' to 'dbname//#sql-ib143335659-1182235242.ibd' for space ID 2696974 because the source file does not exist.

At times the below errors appear after the alter table command
[ERROR] [MY-012596] [InnoDB] Error number 18 means 'Invalid cross-device link'
[ERROR] [MY-012592] [InnoDB] Operating system error number 18 in a file operation.

If we move the table back to the datadir, I think it will allow me to alter it.

How to repeat:
1. Stop the server.
2. Moved the tablespace files or directories to the desired location.

Source (datadir) directory
/data/mysql/dbname/table1.ibd
/data/mysql/dbname/table2.ibd
/data/mysql/dbname/table3.ibd

Destination (after moving 2 of the above tables to 2 different disks)
/data1/mysql/dbname/table2.ibd
/data2/mysql/dbname/table3.ibd

Source directory after moving the tables
/data/mysql/dbname/table1.ibd

3. Made the new directory known to InnoDB by adding the directory paths in innodb_directories.

[mysqld]
innodb_directories = "/data1/mysql/dbname/;/data2/mysql/dbname/"

4. Start the server
systemctl start mysqld

5. Execute an alter command
ALTER TABLE `'dbname`.`table2` 
ADD COLUMN `process_id` VARCHAR(50) NULL DEFAULT NULL AFTER `seq_id`;

Hi Mr. Thomas,

Thank you for your bug report.

However, you are using an ancient release of 8.0. Latest release is 8.0.26 and it contains thousands of bug fixes, including those in the ancient release, like the one you have used.

Also , please make sure that you followed all instructions as provided in the link that you posted.

Unsupported.

We are unable to upgrade from 8.0.15 and it was reported in the bug below:
https://bugs.mysql.com/bug.php?id=99461

Tried the suggestions provided in the link above but it didn't work.

Regarding this alter table issue, I think the steps we followed are straightforward. Out of the 4 steps to copy the table to another disk, the first and last steps are stopping and starting the MySQL service. The second step is to copy the IBD files to another disk and the third step is to add that path to the innodb_directories variable. There are no issues in the last 2 months. The only issue is with altering the tables in the innodb_directories.

I have fixed the MySQL upgrade. Now we are in 8.0.26 version. 
https://bugs.mysql.com/bug.php?id=99461

In the current version while altering the table we are getting the following error. Please let me know if the procedure I followed is wrong or if this is a bug: 

2021-09-09T11:29:40.434142Z 0 [System] [MY-010931] [Server] /usr/sbin/mysqld: ready for connections. Version: '8.0.26'  socket: '/database/datadir/mysql/data/mysql/mysql.sock'  port: 3306  MySQL Community Server - GPL.
2021-09-09T11:03:02.172277Z 15 [ERROR] [MY-012592] [InnoDB] Operating system error number 18 in a file operation.
2021-09-09T11:03:02.172416Z 15 [ERROR] [MY-012596] [InnoDB] Error number 18 means 'Invalid cross-device link'
2021-09-09T11:03:02.172561Z 15 [ERROR] [MY-013183] [InnoDB] Assertion failure: handler0alter.cc:6948:error == DB_SUCCESS thread 139639187433216
InnoDB: We intentionally generate a memory trap.
InnoDB: Submit a detailed bug report to http://bugs.mysql.com.
InnoDB: If you get repeated assertion failures or crashes, even
InnoDB: immediately after the mysqld startup, there may be
InnoDB: corruption in the InnoDB tablespace. Please refer to
InnoDB: http://dev.mysql.com/doc/refman/8.0/en/forcing-innodb-recovery.html
InnoDB: about forcing recovery.
11:03:02 UTC - mysqld got signal 6 ;
Most likely, you have hit a bug, but this error can also be caused by malfunctioning hardware.
Thread pointer: 0x7ecf4c0c8c30
Attempting backtrace. You can use the following information to find out
where mysqld died. If you see no messages after this, something went
terribly wrong...
stack_bottom = 7f004829dc70 thread_stack 0x46000
/usr/sbin/mysqld(my_print_stacktrace(unsigned char const*, unsigned long)+0x3d) [0x20e7edd]
/usr/sbin/mysqld(handle_fatal_signal+0x30b) [0xf387fb]
/lib64/libpthread.so.0(+0xf630) [0x7f00d8f06630]
/lib64/libc.so.6(gsignal+0x37) [0x7f00d7451387]
/lib64/libc.so.6(abort+0x148) [0x7f00d7452a78]
/usr/sbin/mysqld() [0xc76ef0]
/usr/sbin/mysqld(bool ha_innobase::commit_inplace_alter_table_impl<dd::Table>(TABLE*, Alter_inplace_info*, bool, dd::Table const*, dd::Table*)+0x18c8) [0x22146d8]
/usr/sbin/mysqld(ha_innobase::commit_inplace_alter_table(TABLE*, Alter_inplace_info*, bool, dd::Table const*, dd::Table*)+0xb7) [0x2215527]
/usr/sbin/mysqld() [0xe8af9f]
/usr/sbin/mysqld(mysql_alter_table(THD*, char const*, char const*, HA_CREATE_INFO*, TABLE_LIST*, Alter_info*)+0x7aab) [0xea05db]
/usr/sbin/mysqld(Sql_cmd_alter_table::execute(THD*)+0x4dc) [0x126715c]
/usr/sbin/mysqld(mysql_execute_command(THD*, bool)+0x9c8) [0xdf1ab8]
/usr/sbin/mysqld(dispatch_sql_command(THD*, Parser_state*)+0x40b) [0xdf50cb]
/usr/sbin/mysqld(dispatch_command(THD*, COM_DATA const*, enum_server_command)+0xdda) [0xdf638a]
/usr/sbin/mysqld(do_command(THD*)+0x194) [0xdf8184]
/usr/sbin/mysqld() [0xf29900]
/usr/sbin/mysqld() [0x259ac6e]
/lib64/libpthread.so.0(+0x7ea5) [0x7f00d8efeea5]
/lib64/libc.so.6(clone+0x6d) [0x7f00d751996d]

Trying to get some variables.
Some pointers may be invalid and cause the dump to abort.
Query (7ecf4c127e58): ALTER TABLE `database`.`location`  ADD COLUMN `customer_entity_id` BIGINT(20) UNSIGNED NULL DEFAULT NULL, ADD INDEX `customer_entity_id_idx1` (`customer_entity_id` ASC) VISIBLE
Connection ID (thread ID): 15
Status: NOT_KILLED

The manual page at http://dev.mysql.com/doc/mysql/en/crashing.html contains
information that should help you find out what is causing the crash.

Hi,

It actually means that your installation is not in full order. You either have wrong privileges, you have got unnecessary files left in some schema, or you are mixing some shared resource with your local ones.

In any case, this is not a bug.

I noticed that the temporary file #sql-ib2680325-3497247037.ibd is getting created on the 'datadir' instead of 'innodb_directories' while doing an alter query on a table that is inside the 'innodb_directories'

In this case, it has to move the table from 'datadir' disk to 'innodb_directories' after the end of the alteration. Some of the table sizes are around 400GB and so this will be an issue.

I checked the privileges and there are only .idb files in the schema folders.