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

Error 126 "Index file is crashed" or ER_NOT_KEYFILE and eventual "Table is crashed and last repair failed" upon creation or using table with FULLTEXT key on virtual column

    XMLWordPrintable

Details

    Description

      Note: I am filing it as Minor because it is not reproducible on 10.2 and higher, and there is no urgent need to fix it in old versions. However, I need something to account for various failures that we are getting in tests, and possibly it will explain some problems that external users might experience, so we'll have it on the record in JIRA.

      CREATE TABLE t1 ( 
        pk INTEGER AUTO_INCREMENT,
        c CHAR(133) NOT NULL DEFAULT '',
        vc CHAR(133) AS (c) VIRTUAL,
        PRIMARY KEY(pk),
        FULLTEXT KEY(vc)
      );
       
      INSERT IGNORE INTO t1 (c) VALUES
      ('wzinqrtm'),('i'),('r'),('iu'),('xac'),('i'),('el'),('kvxlo'),('i'),
      ('oudzr'),('zr'),('dv'),('kk'),('dwdiw'),('diwkk'),('w'),('jvknqv'),
      ('knqv'),('q'),(''),('qe'),('ekpv'),('vms'),('sj'),(''),(''),('n'),(''),
      ('av'),(''),('lxeovd'),('eov'),('v'),('lf'),('cld'),('dawevgnw'),('e'),
      ('gnwnbc'),('wnbcfcou'),(''),('fcouyjv'),(''),('yj'),('ifixy'),('xy'),
      ('yfb'),('b'),('o'),('lo'),('heqnpx'),('qnpxpivk'),('p'),('ivk'),('o'),
      ('s'),('qc'),('yltzcud'),('tz'),('g'),('d'),('m'),('lxhss'),('hs'),('a'),
      ('k'),('bya'),('x'),('log'),('lhpb'),(''),('y'),('u'),('bce'),('e'),
      ('mi'),('owxdqtc'),('xd'),('t'),('bs'),('bjw'),('g'),('z'),('jg'),
      ('xbmwb'),('g'),('hluhcku'),(''),('hckul'),('ku'),('ldakn'),('knr'),(''),
      ('c'),('obkit'),('kitz'),('x'),('sesfheh'),('sfhehe'),('ehet'),('t');
       
      --error 0,126,ER_NOT_KEYFILE
      DELETE FROM t1 WHERE pk = 1 ORDER BY vc;
       
      --error 0,126,ER_NOT_KEYFILE
      DELETE FROM t1 WHERE pk = 8 ORDER BY vc;
       
      SELECT * FROM t1 INTO OUTFILE 'load.data';
      LOAD DATA INFILE 'load.data' REPLACE INTO TABLE t1;
      FLUSH TABLES;
       
      --error 0,126,ER_NOT_KEYFILE
      DELETE FROM t1 LIMIT 4;
       
      SELECT pk FROM t1;
       
      # Cleanup
      --let $datadir= `SELECT @@datadir`
      --remove_file $datadir/test/load.data
      DROP TABLE t1;
      

      Depending on the engine in use, the actual result is different.

      With InnoDB, the failure happens upon table creation:

      # perl ./mtr bug.corrupt  --mysqld=--innodb --mysqld=--default-storage-engine=InnoDB
       
      mysqltest: At line 1: query 'CREATE TABLE t1 ( 
      pk INTEGER AUTO_INCREMENT,
      c CHAR(133) NOT NULL DEFAULT '',
      vc CHAR(133) AS (c) VIRTUAL,
      PRIMARY KEY(pk),
      FULLTEXT KEY(vc)
      )' failed: 1005: Can't create table `test`.`t1` (errno: 126 "Index file is crashed")
      

      With MyISAM and Aria, the table gets created and populated without complaints, but further statements cause errors.

      With MyISAM it's error 126, followed by error 144 " Table './test/t1' is marked as crashed and last (automatic?) repair failed":

      # perl ./mtr bug.corrupt --mysqld=--default-storage-engine=MyISAM --force --force
       
      DELETE FROM t1 WHERE pk = 1 ORDER BY vc;
      mysqltest: At line 22: query 'DELETE FROM t1 WHERE pk = 1 ORDER BY vc' failed: 126: Incorrect key file for table './test/t1.MYI'; try to repair it
      DELETE FROM t1 WHERE pk = 8 ORDER BY vc;
      mysqltest: At line 23: query 'DELETE FROM t1 WHERE pk = 8 ORDER BY vc' failed: 126: Incorrect key file for table './test/t1.MYI'; try to repair it
      SELECT * FROM t1 INTO OUTFILE 'load.data';
      LOAD DATA INFILE 'load.data' REPLACE INTO TABLE t1;
      FLUSH TABLES;
      DELETE FROM t1 LIMIT 4;
      mysqltest: At line 29: query 'DELETE FROM t1 LIMIT 4' failed: 1034: Incorrect key file for table 't1'; try to repair it
      SELECT pk FROM t1;
      mysqltest: At line 31: query 'SELECT pk FROM t1' failed: 144: Table './test/t1' is marked as crashed and last (automatic?) repair failed
      

      2018-11-05 19:42:53 140364583480064 [ERROR] mysqld: Incorrect key file for table './test/t1.MYI'; try to repair it
      2018-11-05 19:42:53 140364583480064 [ERROR] mysqld: Incorrect key file for table './test/t1.MYI'; try to repair it
      2018-11-05 19:42:53 140364583480064 [ERROR] Got an error from thread_id=3, /data/src/10.1/storage/myisam/mi_delete.c:121
      2018-11-05 19:42:53 140364583480064 [ERROR] MySQL thread id 3, OS thread handle 0x7fa92d232b00, query id 21 localhost root updating
      2018-11-05 19:42:53 140364583480064 [ERROR] mysqld: Incorrect key file for table './test/t1.MYI'; try to repair it
      2018-11-05 19:42:53 140364583480064 [ERROR] mysqld: Incorrect key file for table './test/t1.MYI'; try to repair it
      2018-11-05 19:42:53 140364583480064 [ERROR] Got an error from thread_id=3, /data/src/10.1/storage/myisam/mi_delete.c:121
      2018-11-05 19:42:53 140364583480064 [ERROR] MySQL thread id 3, OS thread handle 0x7fa92d232b00, query id 22 localhost root updating
      2018-11-05 19:42:53 140364583480064 [ERROR] mysqld: Table './test/t1' is marked as crashed and should be repaired
      2018-11-05 19:42:53 140364583480064 [Warning] Checking table:   './test/t1'
      2018-11-05 19:42:53 140364583480064 [Warning] Recovering table: './test/t1'
      2018-11-05 19:42:53 140364583480064 [ERROR] Couldn't repair table: test.t1
      2018-11-05 19:42:53 140364583480064 [ERROR] mysqld: Table './test/t1' is marked as crashed and last (automatic?) repair failed
      

      And with Aria it's 1034 ER_NOT_KEYFILE, also followed by error 144 " Table './test/t1' is marked as crashed and last (automatic?) repair failed":

      # perl ./mtr bug.corrupt  --mysqld=--default-storage-engine=Aria --force --force
       
      DELETE FROM t1 WHERE pk = 1 ORDER BY vc;
      mysqltest: At line 22: query 'DELETE FROM t1 WHERE pk = 1 ORDER BY vc' failed: 1034: Incorrect key file for table 't1'; try to repair it
      DELETE FROM t1 WHERE pk = 8 ORDER BY vc;
      mysqltest: At line 23: query 'DELETE FROM t1 WHERE pk = 8 ORDER BY vc' failed: 1034: Incorrect key file for table 't1'; try to repair it
      SELECT * FROM t1 INTO OUTFILE 'load.data';
      LOAD DATA INFILE 'load.data' REPLACE INTO TABLE t1;
      FLUSH TABLES;
      DELETE FROM t1 LIMIT 4;
      mysqltest: At line 29: query 'DELETE FROM t1 LIMIT 4' failed: 1034: Incorrect key file for table 't1'; try to repair it
      SELECT pk FROM t1;
      mysqltest: At line 31: query 'SELECT pk FROM t1' failed: 144: Table './test/t1' is marked as crashed and last (automatic?) repair failed
      

      2018-11-05 19:44:13 139798647491328 [ERROR] mysqld: Table './test/t1' is marked as crashed and should be repaired
      2018-11-05 19:44:13 139798647491328 [Warning] Recovering table: './test/t1'
      2018-11-05 19:44:13 139798647491328 [ERROR] Couldn't repair table: test.t1
      2018-11-05 19:44:13 139798647491328 [ERROR] mysqld: Table './test/t1' is marked as crashed and last (automatic?) repair failed
      

      All output above is from 10.1 1a89356382 debug build.

      Attachments

        Issue Links

          Activity

            People

              Unassigned Unassigned
              elenst Elena Stepanova
              Votes:
              0 Vote for this issue
              Watchers:
              4 Start watching this issue

              Dates

                Created:
                Updated:
                Resolved:

                Git Integration

                  Error rendering 'com.xiplink.jira.git.jira_git_plugin:git-issue-webpanel'. Please contact your Jira administrators.