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

JOIN on Compressed MyISAM tables failed randomly

    XMLWordPrintable

    Details

    • Type: Bug
    • Status: Open (View Workflow)
    • Priority: Major
    • Resolution: Unresolved
    • Affects Version/s: 10.0.31
    • Fix Version/s: 10.0
    • Labels:
      None
    • Environment:
      Debian Jessie 32bit

      Description

      we have 2 myisam tables that are compressed and selected with a JOIN. The SELECT randomly fails with the entry in log:
      170516 10:24:19 [ERROR] Got error 127 when reading table 'xxxx'
      The error occurs only a few time, then the error disappears without any manual interventions.

      With given TestProgram.cs and database schema I can reproduce the error:

      CREATE  TABLE IF NOT EXISTS `test_table_first` (
        `AutoIdx` INT(11) UNSIGNED NOT NULL AUTO_INCREMENT ,
        `TestId` INT(11) UNSIGNED NOT NULL DEFAULT '0' ,
        `TestName` VARCHAR(50) CHARACTER SET 'latin1' COLLATE 'latin1_german1_ci' NOT NULL DEFAULT '' ,
        `TestDescription` VARCHAR(255) CHARACTER SET 'latin1' COLLATE 'latin1_german1_ci' NULL DEFAULT NULL ,
        `TestDefaultValue` VARCHAR(50) CHARACTER SET 'latin1' COLLATE 'latin1_german1_ci' NULL DEFAULT NULL ,
        PRIMARY KEY (`AutoIdx`) ,
        INDEX `KTestId` (`TestId` ASC) )
      ENGINE = MyISAM
      DEFAULT CHARACTER SET = latin1
      COLLATE = latin1_german1_ci
      COMMENT = 'Test table 1';
       
      CREATE  TABLE IF NOT EXISTS `test_table_second` (
        `SecondTestId` INT(11) UNSIGNED NOT NULL AUTO_INCREMENT ,
        `SecondTestName` VARCHAR(50) CHARACTER SET 'latin1' COLLATE 'latin1_german1_ci' NOT NULL DEFAULT '' ,
        `SecondTestDescription` VARCHAR(255) CHARACTER SET 'latin1' COLLATE 'latin1_german1_ci' NULL DEFAULT NULL ,
        `SecondTestText` TEXT CHARACTER SET 'latin1' COLLATE 'latin1_german1_ci' NULL DEFAULT NULL ,
        `TestId` INT(11) UNSIGNED NOT NULL DEFAULT '0' ,
        PRIMARY KEY (`SecondTestId`) ,
        INDEX `KSecondTestName` (`SecondTestName` ASC) ,
        INDEX `FKTestId` (`TestId` ASC) )
      ENGINE = MyISAM
      DEFAULT CHARACTER SET = latin1
      COLLATE = latin1_german1_ci
      COMMENT = 'Test table 2';
       
      myisampack test_table_first
      myisamchk -rq test_table_first
      myisampack test_table_second
      myisamchk -rq test_table_second
       
      SELECT test_table_second.SecondTestId, test_table_second.SecondTestName, test_table_second.SecondTestDescription, test_table_second.SecondTestText, test_table_first.TestId, test_table_first.TestName, test_table_first.TestDescription, test_table_first.TestDefaultValue FROM test_table_first INNER JOIN test_table_second ON (test_table_first.TestId = test_table_second.TestId) ORDER BY SecondTestId DESC;
      

      It may take a few hours for the error to occur.
      In testdb.zip are the generated tables.

      Also reproducible with given bash script:

      #!/bin/bash
      i=0
      while [ $i -le 10000 ]
      do
      d=0
      echo "-----------------------Stop mariadb--------------------------"
      echo $i
      let i=$i+1
      /etc/init.d/mysql stop
      sleep 1
      echo "-----------------------Start mariadb-------------------------"
      /etc/init.d/mysql start
      while [ $d -le 100 ]
      do
      let d=$d+1
      echo "------------------------mysql abfrage--------------------------------"
      mysql --database=testdb --execute="SELECT NOW(), test_table_second.SecondTestId, test_table_second.SecondTestName, test_table_second.SecondTestDescription, test_table_second.SecondTestText, test_table_first.TestId, test_table_first.TestName, test_table_first.TestDescription, test_table_first.TestDefaultValue FROM test_table_first INNER JOIN test_table_second ON (test_table_first.TestId = test_table_second.TestId) ORDER BY SecondTestId DESC LIMIT 1;" | grep crashed
      mysql --database=testdb --execute="SELECT NOW(), test_table_second.SecondTestId, test_table_second.SecondTestName, test_table_second.SecondTestDescription, test_table_second.SecondTestText, test_table_first.TestId, test_table_first.TestName, test_table_first.TestDescription, test_table_first.TestDefaultValue FROM test_table_first INNER JOIN test_table_second ON (test_table_first.TestId = test_table_second.TestId) ORDER BY SecondTestId ASC LIMIT 1;" | grep crashed
      done
      done
      

      Error Message:
      ERROR 1194 (HY000) at line 1: Table 'test_table_second' is marked as crashed and should be repaired

      Because the tables are compressed, they are read-only. Therefore the tables can never be corrupt!
      When I check the tables with "myisamchk -ce" or "mysqlcheck -ce" the tables are OK.

        Attachments

        1. daemon.zip
          1.10 MB
        2. my.cnf
          1 kB
        3. mysql
          5 kB
        4. test.zip
          202 kB
        5. testdb.zip
          2.29 MB
        6. TestProgram.cs
          7 kB

          Activity

            People

            Assignee:
            elenst Elena Stepanova
            Reporter:
            christian.stern Christian Stern
            Votes:
            0 Vote for this issue
            Watchers:
            4 Start watching this issue

              Dates

              Created:
              Updated:

                Git Integration

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