[MDEV-13237] Renaming ISL-linked InnoDB table fails with errno 18 unless you SHOW TABLE first Created: 2017-07-03  Updated: 2017-12-16

Status: Open
Project: MariaDB Server
Component/s: Storage Engine - InnoDB
Affects Version/s: 10.1.23
Fix Version/s: None

Type: Bug Priority: Major
Reporter: Richard Stanway Assignee: Unassigned
Resolution: Unresolved Votes: 0
Labels: None
Environment:

Debian 8, Linux 64 bit, ZFS



 Description   

I have a separate partition for InnoDB data files and MyISAM data files in order to take advantage of optimal ZFS record sizes. This is done in a supported way, using .ISL "symbolic link files" in the MyISAM directory pointing to the .IBD file on the other partition. There are no soft or hard links on either partition.

Attempting to rename an InnoDB table will sometimes return the following:

ERROR 1025 (HY000): Error on rename of './xxxx/yyyy' to './xxxx/yyyy_old' (errno: -1 "Internal error < 0 (Not system error)")

The server logs the following:

mysqld[37357]: 2017-07-04 03:06:04 7f8010df8b00  InnoDB: Operating system error number 18 in a file operation.
mysqld[37357]: InnoDB: Error number 18 means 'Invalid cross-device link'.
mysqld[37357]: InnoDB: Some operating system error numbers are described at
mysqld[37357]: InnoDB: http://dev.mysql.com/doc/refman/5.6/en/operating-system-error-codes.html

After much head scratching and verifying permissions, ISL paths, it turns out the solution is to SHOW TABLE first (yes, really!).

MariaDB [xxxx]> show table status like 'yyyy%';
 
MariaDB [xxxx]> rename table xxxx.yyyy to xxxx.yyyy_old;
Query OK, 0 rows affected (0.00 sec)

I am guessing that the SHOW TABLE statement causes MySQL to "notice" the ISL file and use the correct paths. Why it was trying to create a cross-device symbolic link I have no idea though.



 Comments   
Comment by Elena Stepanova [ 2017-07-03 ]

Please provide an example of the exact structure of a table with which you experience the error (SHOW CREATE TABLE ...).
Does it only happen on RENAME?
Does only SHOW TABLE STATUS help? What about, let's say, selecting from the table?
Is there a lot of activity on the server and on the machine around the time when the problem occurs?

Please also attach your cnf file(s).

Comment by Richard Stanway [ 2017-07-03 ]

The table structures affected were several Mediawiki tables. Both of them have blob columns, but that may be coincidental.

CREATE TABLE `wiki_interwiki` (
  `iw_prefix` varbinary(32) NOT NULL,
  `iw_url` blob NOT NULL,
  `iw_api` blob NOT NULL,
  `iw_wikiid` varbinary(64) NOT NULL,
  `iw_local` tinyint(1) NOT NULL,
  `iw_trans` tinyint(4) NOT NULL DEFAULT '0',
  UNIQUE KEY `iw_prefix` (`iw_prefix`)
) ENGINE=InnoDB DEFAULT CHARSET=binary DATA DIRECTORY='/var/lib/mysql/innodb_data/'

I attempted the rename first by ALTER TABLE .. RENAME then RENAME TABLE, both with the same result.

I do still have several tables on our production server that are affected, they are no longer used by our application but are still exhibiting the strange rename behavior. Neither SELECT nor DESCRIBE result in "fixing" the affected tables. SHOW CREATE TABLE does fix it. Interestingly, the table remains "stuck" again immediately after the rename operation. Here's my session output:

MariaDB [yyy]> alter table xxx_interwiki rename xxx_interwiki_test;
ERROR 1025 (HY000): Error on rename of './yyy/xxx_interwiki' to './yyy/xxx_interwiki_test' (errno: -1 "Internal error < 0 (Not system error)")
 
MariaDB [yyy]> show create table xxx_interwiki;
[...]
1 row in set (0.00 sec)
 
MariaDB [yyy]> alter table xxx_interwiki rename xxx_interwiki_test;
Query OK, 0 rows affected (0.00 sec)
 
MariaDB [yyy]> alter table xxx_interwiki_test rename xxx_interwiki;
ERROR 1025 (HY000): Error on rename of './yyy/xxx_interwiki_test' to './yyy/xxx_interwiki' (errno: -1 "Internal error < 0 (Not system error)")
 
MariaDB [yyy]> show create table xxx_interwiki_test;
[...]
1 row in set (0.00 sec)
 
MariaDB [yyy]> alter table xxx_interwiki_test rename xxx_interwiki;
Query OK, 0 rows affected (0.00 sec)

The server is always rather active, averaging 2.5k QPS. There is very little I/O load (our dataset fits in RAM). I'd prefer not posting the full config, however it is mostly defaults with a larger innodb_log_file_size and innodb_buffer_pool_size and skip-innodb_doublewrite, separate innodb_data_home_dir and innodb_log_group_home_dir directories (separate ZFS datasets).

Comment by Alice Sherepa [ 2017-08-07 ]

I can't reproduce it so far.
I found some bugs that look alike to your problem, https://bugs.mysql.com/bug.php?id=67656 and https://bugs.mysql.com/bug.php?id=15991, but both of them use symbolic links, not DATA DIRECTORY clause.
What is innodb_flush_method variable value? Maybe you are willing to provide more information (configuration file) to the private ftp? (ftp.askmonty.org/private)

Comment by Richard Stanway [ 2017-08-07 ]

Hello,
I've uploaded the config to the private FTP as MDEV-13237-mariadb.cnf (please ignore the MDEV-13237-my.cnf file, this was not the correct version).

innodb_flush_method is O_DIRECT

I've double checked and there are no filesystem-level symlinks under the /var/lib/mysql directory.

Generated at Thu Feb 08 08:03:59 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.