[MDEV-10003] main.range_innodb retuns a wrong number of rows in EXPLAIN Created: 2016-04-27  Updated: 2022-09-08  Resolved: 2022-09-07

Status: Closed
Project: MariaDB Server
Component/s: Storage Engine - InnoDB, Tests
Affects Version/s: 10.0.24
Fix Version/s: 10.6.10, 10.7.6, 10.8.5, 10.9.3, 10.10.2

Type: Bug Priority: Major
Reporter: David Gow Assignee: Marko Mäkelä
Resolution: Fixed Votes: 0
Labels: affects-tests, contribution, patch
Environment:

Linux 3.13 / Ubuntu 14.04 x86_64

Attachments: File 0001-De-flake-main.range_innodb.patch    
Issue Links:
Relates
relates to MDEV-4750 join_outer_innodb.test fails in 10.0-... Closed

 Description   

The main.range_innodb test relies on the output of an EXPLAIN statement on an InnoDB table to stay the same, but the estimated number of rows affected is not deterministic.

The attached patch masks out the row count.

 Comments   
Comment by Elena Stepanova [ 2016-04-28 ]

Thanks.
It seems straightforward, but on whatever reason according to the buildbot history we never get this test failure, so I'll assign it to psergey (the test owner) just to take a look.

Comment by Marko Mäkelä [ 2022-09-07 ]

vlad.lesin, can you please analyze this? I would suggest to analyze a trace generated by ./mtr --rr main.range_innodb (even for a test that does not fail), and to check where the dict_table_t::stat_n_rows is coming from.

This test is failing rather often starting with 10.6. Could my partial revert of MDEV-4750 explain this? That is, could the timing of the background collection of InnoDB persistent statistics play a role here?

Comment by Marko Mäkelä [ 2022-09-07 ]

The contributed patch is for a different section of the test, which does not seem to fail on our CI systems, at least not recently. The following would seem to fix the sporadic failures for me:

diff --git a/mysql-test/main/range_innodb.test b/mysql-test/main/range_innodb.test
 
index 8b9771d1d1e..df0fff403a8 100644
 
--- a/mysql-test/main/range_innodb.test
 
+++ b/mysql-test/main/range_innodb.test
 
@@ -54,11 +54,11 @@ drop table t0,t1,t2;
 
 create table t1 (
 
   pk int, a int, b int,
 
   primary key (pk), index idx1(b), index idx2(b)
 
-) engine=innodb;
 
+) engine=innodb STATS_AUTO_RECALC=0;
 
 insert into t1 values (1,6,0),(2,1,0),(3,5,2),(4,8,0);
 
-create table t2 (c int) engine=innodb;
 
+create table t2 (c int) engine=innodb STATS_AUTO_RECALC=0;
 
 insert into t2 values (1),(2);
 
-create table t3 (d int) engine=innodb;
 
+create table t3 (d int) engine=innodb STATS_AUTO_RECALC=0;
 
 insert into t3 values (3),(-1),(4);
 
 
 set @save_optimizer_switch=@@optimizer_switch;

With that fix, the test passed 56×100 rounds:

./mtr --parallel=56 --repeat=100 main.range_innodb{,,,,,,,,,,,,,}{,,,}
 
…
 
main.range_innodb 'innodb'               w18 [ 100 pass ]  10017
 
--------------------------------------------------------------------------
 
The servers were restarted 0 times
 
Spent 83770.863 of 1581 seconds executing testcases
 
 
 
Completed: All 5600 tests were successful.

Without the fix, an attempt to reproduce a failure was successful on the 9th round.

Generated at Thu Feb 08 07:38:56 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.