[MDEV-12945] JOIN on Compressed MyISAM tables failed randomly Created: 2017-05-29  Updated: 2022-09-08

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

Type: Bug Priority: Major
Reporter: Christian Stern Assignee: Elena Stepanova
Resolution: Unresolved Votes: 0
Labels: None
Environment:

Debian Jessie 32bit

Attachments: Text File TestProgram.cs     Zip Archive daemon.zip     File my.cnf     HTML File mysql     Zip Archive test.zip     Zip Archive testdb.zip    

 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.

 Comments   
Comment by Elena Stepanova [ 2017-06-05 ]

Couldn't reproduce it so far, will try again later.

Comment by Christian Stern [ 2017-06-06 ]

It can take several hours until the error occurs. Sometimes it was faster if you turn off query caching (query_cache_size=0).
I tested it on a VMware Debian Jessie 686 Image with 2 Processors and 1GB RAM.
Do you need any further informations?

Comment by Christian Stern [ 2017-06-27 ]

Did you try again to reproduce it?

Comment by Alice Sherepa [ 2017-07-05 ]

please can you provide your error.log and /etc/init.d/mysql script. I suspect that new instance of mysql starts, when then old one is still up,
(maybe sleep 1 is not enough).

Comment by Christian Stern [ 2017-07-05 ]

the error log /var/log/mysqld.log is empty.

the init script is the default of debian jessie package. I attached it.

Comment by Christian Stern [ 2017-07-05 ]

I also attached daemon.log

Comment by Alice Sherepa [ 2017-07-05 ]

I mean error log when you executing bash script. and mysqld.log please anyway. Can't reproduce it( Maybe this option --myisam_recover_options also will be helpful https://mariadb.com/kb/en/mariadb/myisam-system-variables/#myisam_recover_options

Comment by Christian Stern [ 2017-07-05 ]

the test.zip contains the output of the test bash script.

I can't attach mysqld.log because it is an empty file.

the option myisam_recover_options is not needed because the compressed table are read-only. I think that errors can only occur on write operations. The myisam files in filesystem are not touched during the test.

Comment by Alice Sherepa [ 2017-07-12 ]

I reproduced it once on VM debian 32bit with options from my.cnf and bash script from description. But can not repeat it more, I'm working on it.

Comment by Christian Stern [ 2017-07-13 ]

Great! As I said, it may take several hours for the error to occur. But it always appears.

Comment by Christian Stern [ 2017-08-09 ]

Did you find out whether the problem occurs only with compressed tables?

Comment by Christian Stern [ 2017-09-05 ]

Could you identify the problem? Please let me know as soon as possible. I still have trouble with the error...

Comment by Alice Sherepa [ 2017-09-05 ]

Hi Christian, i suggest to run mysqld directly, without using debian script and see if the problem will occur again. I repeated a problem several times, but could not make a reasonable test for that, not that time consuming.

Comment by Christian Stern [ 2017-10-11 ]

It does not matter if mysqld runs with debian script or not. The error occurs nevertheless. I also can not do a reliable test for that bug.

Comment by Christian Stern [ 2017-10-17 ]

How will you proceed with this error? For us, it would also be very important to know whether the error only occurs with compressed tables.

Comment by Elena Stepanova [ 2017-11-14 ]

christian.stern, since the failure is much more readily reproducible in your environment than in ours, the easiest way to find out whether the error only occurs with compressed tables is to try to reproduce it without compression on your side. If you do so, please comment with the results, other users could also benefit from this information, and it might help us to narrow down the problem. That's one of cases where we have to rely on the input from the community.

The bug report is currently in the backlog, it will be back in the work queue when we are closer to the next 10.0 release, which is scheduled for February 2018.

Comment by Christian Stern [ 2017-11-14 ]

elenst, thanks for answer.

The failure does not occur with uncompressed tables. I tried it with the same test environment several days and the error never occurs. But for me that is not reliable enough, because you can not exclude the error completely. Only after the actual problem has been found can one reliably say that the compression is crucial.

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