| Bug #50166 | Server provides varying results for the same query when run in rapid succession | ||
|---|---|---|---|
| Submitted: | 8 Jan 2010 0:01 | Modified: | 29 Nov 2010 12:48 |
| Reporter: | Patrick Crews | Email Updates: | |
| Status: | Can't repeat | Impact on me: | |
| Category: | MySQL Server: Optimizer | Severity: | S3 (Non-critical) |
| Version: | 6.0-codebase | OS: | Any |
| Assigned to: | CPU Architecture: | Any | |
| Tags: | index_condition_pushdown, innodb, optimizer_switch | ||
[8 Jan 2010 0:01]
Patrick Crews
[8 Jan 2010 23:45]
Patrick Crews
NOTE: This was easily repeatable with another RQG process (different server / ports) running on the same machine, but running with only the SelectStability process does not allow this to be repeated.
[9 Jan 2010 5:13]
Patrick Crews
EXPLAIN SELECT MAX( `col_time_key`) AS field1 , `pk` AS field2 FROM C AS table1 WHERE ( ( table1 . `pk` > 238 AND table1 . `pk` < ( 238 + 151 ) OR ( table1 . `pk` IN (114, 171) OR table1 . `col_varchar_key` <> 'i' ) ) AND table1 . `pk` <> 1 ) GROUP BY field2; +----+-------------+--------+-------+-------------------------+---------+---------+------+------+-------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+--------+-------+-------------------------+---------+---------+------+------+-------------+ | 1 | SIMPLE | table1 | range | PRIMARY,col_varchar_key | PRIMARY | 4 | NULL | 11 | Using where | +----+-------------+--------+-------+-------------------------+---------+---------+------+------+-------------+
[12 Jan 2010 17:39]
Patrick Crews
Was unable to repeat on 5.0 or 5.1. Appears to be controlled via engine_condition_pushdown variable. When this value is set to 1, the bug appears ~7 min into the run (with a separate Transformer RQG run in the background). With the variable set to 0, the bug does not appear at all. Test case in-progress.
[12 Jan 2010 20:45]
Patrick Crews
Currently unable to duplicate this bug by any means other than running two RQG processes.
To duplicate:
Run the following against a server installation - in my experiments, I am running against a separate 6.0-codebase server with the following workload:
perl ./runall.pl --threads=1 --Validator=Transformer --basedir=/<path>/xform/mysql-6.0 --mtr-build-thread=91 --queries=100000 --grammar=conf/optimizer_subquery.yy --mysqld=--init-file=/<path>/6.sql
Then, I run the following SelectStability process against another 6.0 server. The failure is showing up ~7 min. into this run:
perl ./runall.pl --threads=1 --debug --queries=10000 --Validator=SelectStability --engine=Innodb --basedir=/<path>/mysql-6.0 --mysqld=--init-file=/<path>/randgen/6.sql --grammar=conf/optimizer_no_subquery.yy --mtr-build-thread=81
I have tried the following perl script to substitute for the SelectStability process, but it is unable to duplicate the bug, regardless of the number of runs / how much background work is being done:
use DBI;
$| = 1;
my $dsn = 'dbi:mysql:host=127.0.0.1:port=10810:user=root:database=test';
my $query = " SELECT MAX( `col_time_key`) AS field1 , `pk` AS field2 FROM C AS table1 WHERE ( ( table1 . `pk` > 238 AND table1 . `pk` < ( 238 + 151 ) OR ( table1 . `pk` IN (114, 171) OR table1 . `col_varchar_key` <> 'i' ) ) AND table1 . `pk` <> 1 ) GROUP BY field2 ";
my $dbh = DBI->connect($dsn);
my %counts;
foreach my $trial (1..500000) {
my $sth = $dbh->prepare($query);
$sth->execute();
print localtime()." [$$] trial: $trial; rows ".$sth->rows()."\n";
$counts{$sth->rows()}++;
exit if scalar(keys %counts) > 1;
}
[12 Jan 2010 22:09]
Patrick Crews
After consultation with dev's, found that using the following in the init-file: SET GLOBAL debug = '+d,optimizer_innodb_ds_mrr,optimizer_no_icp'; Would remove the bug, regardless of engine_condition_pushdown settings. It should be noted that I tried an updated 6.0-codebase-bugfixing tree (rather than the ICP test tree) and was unable to duplicate this with any combination of @@optimizer_switch=engine_condition_pushdown=on|off,index_condition_pushdown=on|off;
