Uploaded image for project: 'MariaDB Server'
  1. MariaDB Server
  2. MDEV-17470

Orphan temporary files after interrupted ALTER cause InnoDB: Operating system error number 17 and eventual fatal error 71

    Details

      Description

      --source include/have_innodb.inc
       
      CREATE TABLE t1 (pk INT AUTO_INCREMENT PRIMARY KEY, i INT) ENGINE=InnoDB;
      INSERT INTO t1 VALUES (NULL,1),(NULL,2),(NULL,3),(NULL,4),(NULL,5),(NULL,6),(NULL,7),(NULL,8);
      INSERT INTO t1 SELECT NULL, i FROM t1;
      INSERT INTO t1 SELECT NULL, i FROM t1;
      INSERT INTO t1 SELECT NULL, i FROM t1;
      INSERT INTO t1 SELECT NULL, i FROM t1;
      INSERT INTO t1 SELECT NULL, i FROM t1;
       
      CREATE TABLE t2 (a INT) ENGINE=InnoDB;
      CREATE TABLE t3 (b INT) ENGINE=InnoDB;
       
      LOCK TABLE t1 READ;
       
      --connect (con1,localhost,root,,test)
      SET max_statement_time= 1;
      --error ER_STATEMENT_TIMEOUT
      ALTER TABLE t1 FORCE, ALGORITHM=COPY;
      --error 0,ER_TABLESPACE_EXISTS
      ALTER TABLE t2 FORCE, ALGORITHM=COPY;
      --error 0,ER_TABLE_EXISTS_ERROR,ER_TABLESPACE_EXISTS
      ALTER TABLE t3 FORCE, ALGORITHM=COPY;
      --disconnect con1
       
      --connection default
      --sleep 1
      UNLOCK TABLES;
      DROP TABLE t1, t2, t3;
      

      In the test case above, things start going wrong after the first ALTER, but I added error codes for the consequent ALTER and the sleep to bring the test case to the fatal end on 10.4:

      10.4 27f3329ff6

      2018-11-27 14:27:49 10 [ERROR] InnoDB: Operating system error number 17 in a file operation.
      2018-11-27 14:27:49 10 [ERROR] InnoDB: Error number 17 means 'File exists'
      2018-11-27 14:27:49 10 [Note] InnoDB: Some operating system error numbers are described at https://mariadb.com/kb/en/library/operating-system-error-codes/
      2018-11-27 14:27:49 10 [Note] InnoDB: The file './test/#sql-699d_a.ibd' already exists though the corresponding table did not exist in the InnoDB data dictionary. You can resolve the problem by removing the file.
      2018-11-27 14:27:49 10 [ERROR] InnoDB: Cannot create file './test/#sql-699d_a.ibd'
      2018-11-27 14:27:49 10 [ERROR] InnoDB: Failed to find tablespace for table `test`.`#sql-699d_a` in the cache. Attempting to load the tablespace with space id 8
      2018-11-27 14:27:49 10 [ERROR] InnoDB: Refusing to load './test/#sql-699d_a.ibd' (id=10, flags=0x21); dictionary contains id=8, flags=0x21
      2018-11-27 14:27:49 10 [ERROR] InnoDB: Operating system error number 2 in a file operation.
      2018-11-27 14:27:49 10 [ERROR] InnoDB: The error means the system cannot find the path specified.
      2018-11-27 14:27:49 10 [ERROR] InnoDB: Could not find a valid tablespace file for ``test`.`#sql-699d_a``. Please refer to http://dev.mysql.com/doc/refman/5.7/en/innodb-troubleshooting-datadict.html for how to resolve the issue.
      2018-11-27 14:27:49 10 [Note] InnoDB: Deleting ./test/#sql-699d_a.ibd
      2018-11-27 14:27:50 0 [ERROR] InnoDB: Operating system error number 2 in a file operation.
      2018-11-27 14:27:50 0 [ERROR] InnoDB: The error means the system cannot find the path specified.
      2018-11-27 14:27:50 0 [ERROR] InnoDB: File ./test/#sql-699d_a.ibd: 'open' returned OS error 71. Cannot continue operation
      181127 14:27:50 [ERROR] mysqld got signal 6 ;
       
      #5  0x00007fe74aed43fa in abort () from /lib/x86_64-linux-gnu/libc.so.6
      #6  0x000055c73ef2e544 in os_file_handle_error_cond_exit (name=0x7fe6f001c2f0 "./test/#sql-699d_a.ibd", operation=0x55c73f604c81 "open", should_abort=true, on_error_silent=false) at /data/src/10.4/storage/innobase/os/os0file.cc:5207
      #7  0x000055c73ef28591 in os_file_handle_error (name=0x7fe6f001c2f0 "./test/#sql-699d_a.ibd", operation=0x55c73f604c81 "open") at /data/src/10.4/storage/innobase/os/os0file.cc:726
      #8  0x000055c73ef2c6c1 in os_file_create_func (name=0x7fe6f001c2f0 "./test/#sql-699d_a.ibd", create_mode=51, purpose=61, type=100, read_only=false, success=0x7fe73cff823d) at /data/src/10.4/storage/innobase/os/os0file.cc:2994
      #9  0x000055c73f176f9b in pfs_os_file_create_func (key=47, name=0x7fe6f001c2f0 "./test/#sql-699d_a.ibd", create_mode=51, purpose=61, type=100, read_only=false, success=0x7fe73cff823d, src_file=0x55c73f6ed830 "/data/src/10.4/storage/innobase/fil/fil0fil.cc", src_line=692) at /data/src/10.4/storage/innobase/include/os0file.ic:170
      #10 0x000055c73f179694 in fil_node_open_file (node=0x7fe6f002cd70) at /data/src/10.4/storage/innobase/fil/fil0fil.cc:692
      #11 0x000055c73f184543 in fil_node_prepare_for_io (node=0x7fe6f002cd70, space=0x7fe6f002cb20) at /data/src/10.4/storage/innobase/fil/fil0fil.cc:4132
      #12 0x000055c73f1852ef in fil_io (type=..., sync=false, page_id=..., page_size=..., byte_offset=0, len=16384, buf=0x7fe744a30000, message=0x7fe7444e43b0, ignore_missing_space=false) at /data/src/10.4/storage/innobase/fil/fil0fil.cc:4374
      #13 0x000055c73f10156c in buf_dblwr_write_block_to_datafile (bpage=0x7fe7444e43b0, sync=false) at /data/src/10.4/storage/innobase/buf/buf0dblwr.cc:926
      #14 0x000055c73f101b1d in buf_dblwr_flush_buffered_writes () at /data/src/10.4/storage/innobase/buf/buf0dblwr.cc:1072
      #15 0x000055c73f10c64c in buf_flush_end (buf_pool=0x55c7414adf50, flush_type=BUF_FLUSH_LIST) at /data/src/10.4/storage/innobase/buf/buf0flu.cc:1972
      #16 0x000055c73f10c804 in buf_flush_do_batch (buf_pool=0x55c7414adf50, type=BUF_FLUSH_LIST, min_n=200, lsn_limit=18446744073709551615, n=0x7fe73cff8cb0) at /data/src/10.4/storage/innobase/buf/buf0flu.cc:2041
      #17 0x000055c73f10cac2 in buf_flush_lists (min_n=200, lsn_limit=18446744073709551615, n_processed=0x7fe73cff8d10) at /data/src/10.4/storage/innobase/buf/buf0flu.cc:2145
      #18 0x000055c73f110857 in buf_flush_page_cleaner_coordinator () at /data/src/10.4/storage/innobase/buf/buf0flu.cc:3297
      #19 0x00007fe74c987494 in start_thread (arg=0x7fe73cff9700) at pthread_create.c:333
      #20 0x00007fe74af8893f in clone () from /lib/x86_64-linux-gnu/libc.so.6
      

      On 10.1-10.3 it's non-fatal, but still quite erroneous:

      10.1 8324e5e84dc

      2018-11-27 14:31:25 7f0c2bf71b00  InnoDB: Operating system error number 17 in a file operation.
      InnoDB: Error number 17 means 'File exists'.
      InnoDB: Some operating system error numbers are described at
      InnoDB: http://dev.mysql.com/doc/refman/5.6/en/operating-system-error-codes.html
      2018-11-27 14:31:25 139690253949696 [ERROR] InnoDB: Cannot create file './test/#sql-6a86_4.ibd'
       
      2018-11-27 14:31:25 139690253949696 [ERROR] InnoDB: The file './test/#sql-6a86_4.ibd' already exists though the corresponding table did not exist in the InnoDB data dictionary. Have you moved InnoDB .ibd files around without using the SQL commands DISCARD TABLESPACE and IMPORT TABLESPACE, or did mysqld crash in the middle of CREATE TABLE? You can resolve the problem by removing the file './test/#sql-6a86_4.ibd' under the 'datadir' of MySQL.
      2018-11-27 14:31:25 7f0c2bf71b00  InnoDB: Operating system error number 17 in a file operation.
      InnoDB: Error number 17 means 'File exists'.
      InnoDB: Some operating system error numbers are described at
      InnoDB: http://dev.mysql.com/doc/refman/5.6/en/operating-system-error-codes.html
      2018-11-27 14:31:25 139690253949696 [ERROR] InnoDB: Cannot create file './test/#sql-6a86_4.ibd'
       
      2018-11-27 14:31:25 139690253949696 [ERROR] InnoDB: The file './test/#sql-6a86_4.ibd' already exists though the corresponding table did not exist in the InnoDB data dictionary. Have you moved InnoDB .ibd files around without using the SQL commands DISCARD TABLESPACE and IMPORT TABLESPACE, or did mysqld crash in the middle of CREATE TABLE? You can resolve the problem by removing the file './test/#sql-6a86_4.ibd' under the 'datadir' of MySQL.
      

      10.2 3728b11f87

      2018-11-27 14:32:00 140339065894656 [ERROR] InnoDB: Operating system error number 17 in a file operation.
      2018-11-27 14:32:00 140339065894656 [ERROR] InnoDB: Error number 17 means 'File exists'
      2018-11-27 14:32:00 140339065894656 [Note] InnoDB: Some operating system error numbers are described at https://mariadb.com/kb/en/library/operating-system-error-codes/
      2018-11-27 14:32:00 140339065894656 [ERROR] InnoDB: Cannot create file './test/#sql-6af4_a.ibd'
      2018-11-27 14:32:00 140339065894656 [Note] InnoDB: The file './test/#sql-6af4_a.ibd' already exists though the corresponding table did not exist in the InnoDB data dictionary. You can resolve the problem by removing the file.
      2018-11-27 14:32:00 140339065894656 [ERROR] InnoDB: Operating system error number 17 in a file operation.
      2018-11-27 14:32:00 140339065894656 [ERROR] InnoDB: Error number 17 means 'File exists'
      2018-11-27 14:32:00 140339065894656 [Note] InnoDB: Some operating system error numbers are described at https://mariadb.com/kb/en/library/operating-system-error-codes/
      2018-11-27 14:32:00 140339065894656 [ERROR] InnoDB: Cannot create file './test/#sql-6af4_a.ibd'
      2018-11-27 14:32:00 140339065894656 [Note] InnoDB: The file './test/#sql-6af4_a.ibd' already exists though the corresponding table did not exist in the InnoDB data dictionary. You can resolve the problem by removing the file.
      

      Both debug and non-debug builds are affected.

        Attachments

          Issue Links

            Activity

              People

              • Assignee:
                marko Marko Mäkelä
                Reporter:
                elenst Elena Stepanova
              • Votes:
                0 Vote for this issue
                Watchers:
                3 Start watching this issue

                Dates

                • Created:
                  Updated:
                  Resolved: