Details
-
Bug
-
Status: Open (View Workflow)
-
Major
-
Resolution: Unresolved
-
10.0.31
-
None
-
None
-
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.