Details
-
Bug
-
Status: Closed (View Workflow)
-
Major
-
Resolution: Fixed
-
None
-
None
-
None
-
None
Description
It fails like this
[psergey@pylon-fedora15 mysql-test]$ diff -urp r/join_outer_innodb.re{sult,ject}
|
--- r/join_outer_innodb.result 2012-06-28 18:31:05.561398000 +0400
|
+++ r/join_outer_innodb.reject 2013-07-03 06:48:45.835839473 +0400
|
@@ -14,7 +14,7 @@ EXPLAIN
|
SELECT COUNT(*) FROM t2 LEFT JOIN t1 ON t2.fkey = t1.id
|
WHERE t1.name LIKE 'A%' OR FALSE;
|
id select_type table type possible_keys key key_len ref rows Extra
|
-1 SIMPLE t2 index NULL fkey 5 NULL 5 Using index
|
+1 SIMPLE t2 index NULL fkey 5 NULL 6 Using index
|
1 SIMPLE t1 eq_ref PRIMARY PRIMARY 4 test.t2.fkey 1 Using where
|
DROP TABLE t1,t2;
|
Attachments
Issue Links
- is part of
-
MDEV-3932 5.6 merge
-
- Closed
-
- relates to
-
-
- Closed
-
-
-
- Closed
-
Activity
Debugging, discovered that mysql-5.6 has a background statistics update process. See storage/innobase/dict/dict0stats_bg.cc, dict_stats_thread. This explains why EXPLAIN result changes when one re-runs EXPLAIN.
On the question of why does statistics says rows=6 when the table has only 5 rows:
(gdb) c
Continuing.
Hardware watchpoint 9: *$a3
Old value = 0
New value = 1
dict_table_n_rows_inc (table=0x7fffa4077d58) at /home/psergey/dev2/10.0-monty/storage/innobase/include/dict0dict.ic:415
(gdb) c
Continuing.
Hardware watchpoint 9: *$a3
Old value = 1
New value = 2
dict_table_n_rows_inc (table=0x7fffa4077d58) at /home/psergey/dev2/10.0-monty/storage/innobase/include/dict0dict.ic:415
(gdb) c
Continuing.
Hardware watchpoint 9: *$a3
Old value = 2
New value = 3
dict_table_n_rows_inc (table=0x7fffa4077d58) at /home/psergey/dev2/10.0-monty/storage/innobase/include/dict0dict.ic:415
(gdb) c
Continuing.
[Switching to Thread 0x7fffc2ffd700 (LWP 27712)]
Hardware watchpoint 9: *$a3
Old value = 3
New value = 4
dict_stats_update_persistent (table=0x7fffa4077d58) at /home/psergey/dev2/10.0-monty/storage/innobase/dict/dict0stats.cc:2065
(gdb) p index->stat_index_size
$101 = 1
(gdb)
(gdb) p table->stat_n_rows
$102 = 4
(gdb) c
Continuing.
[Switching to Thread 0x7fffe4193700 (LWP 27715)]
Hardware watchpoint 9: *$a3
Old value = 4
New value = 5
dict_table_n_rows_inc (table=0x7fffa4077d58) at /home/psergey/dev2/10.0-monty/storage/innobase/include/dict0dict.ic:415
(gdb) c
Continuing.
Hardware watchpoint 9: *$a3
Old value = 5
New value = 6
dict_table_n_rows_inc (table=0x7fffa4077d58) at /home/psergey/dev2/10.0-monty/storage/innobase/include/dict0dict.ic:415
(gdb)
(gdb) c
Continuing.
[Switching to Thread 0x7fffc2ffd700 (LWP 27712)]
Hardware watchpoint 9: *$a3
Old value = 6
New value = 5
I can observe the same thing in debugger on mysql-5.6.
I am running the statement
INSERT INTO t2 VALUES (1,1),(2,2),(3,2),(4,3),(5,3);
and see:
(gdb) set $q=& table->stat_n_rows
(gdb) watch *$q
Hardware watchpoint 5: *$q
(gdb) c
Continuing.
Hardware watchpoint 5: *$q
Old value = 0
New value = 1
0x0886379d in dict_table_n_rows_inc (table=0x965cb80) at /home/psergey/dev2/mysql-5.6-ga/storage/innobase/include/dict0dict.ic:412
(gdb) c
Continuing.
Hardware watchpoint 5: *$q
Old value = 1
New value = 2
0x0886379d in dict_table_n_rows_inc (table=0x965cb80) at /home/psergey/dev2/mysql-5.6-ga/storage/innobase/include/dict0dict.ic:412
(gdb) c
Continuing.
Hardware watchpoint 5: *$q
Old value = 2
New value = 3
0x0886379d in dict_table_n_rows_inc (table=0x965cb80) at /home/psergey/dev2/mysql-5.6-ga/storage/innobase/include/dict0dict.ic:412
(gdb) c
Continuing.
[Switching to Thread 0xa128bb90 (LWP 15090)]
Hardware watchpoint 5: *$q
Old value = 3
New value = 4
0x0899099d in dict_stats_update_persistent (table=0x965cb80) at /home/psergey/dev2/mysql-5.6-ga/storage/innobase/dict/dict0stats.cc:2044
(gdb) c
Continuing.
[Switching to Thread 0xa4fffb90 (LWP 15137)]
Hardware watchpoint 5: *$q
Old value = 4
New value = 5
0x0886379d in dict_table_n_rows_inc (table=0x965cb80) at /home/psergey/dev2/mysql-5.6-ga/storage/innobase/include/dict0dict.ic:412
(gdb) c
Continuing.
Hardware watchpoint 5: *$q
Old value = 5
New value = 6
0x0886379d in dict_table_n_rows_inc (table=0x965cb80) at /home/psergey/dev2/mysql-5.6-ga/storage/innobase/include/dict0dict.ic:412
(gdb) c
Continuing.
-
-
- At this point, the INSERT query is finished. In the client
- I see "Query OK, 5 rows affected"
- Note that the stats is 6. I suppose, if one gets a big enough table, the
- difference can be bigger.
-
[Switching to Thread 0xa128bb90 (LWP 15090)]
Breakpoint 4, dict_stats_update_persistent (table=0x965cb80) at /home/psergey/dev2/mysql-5.6-ga/storage/innobase/dict/dict0stats.cc:2044
(gdb) c
Continuing.
Hardware watchpoint 5: *$q
Old value = 6
New value = 5
0x0899099d in dict_stats_update_persistent (table=0x965cb80) at /home/psergey/dev2/mysql-5.6-ga/storage/innobase/dict/dict0stats.cc:2044
(gdb)
Wondering why mysql-5.6 doesn't have the problem with join_outer_innodb.test...
it seems, the cause is vasil.dimov@oracle.com-20120521133620-glj6l0ntcsrz0wbl ... They added ANALYZE TABLE statements to stabilize the statistics.
mysql 5.6's use of "-- disable_result_log" in test files was not helpful when analyzing this test failure...
Fixed by making mtr to run the testsuite without persistent stats.
Starting with MariaDB Server 10.6.5, we will no longer globally disable innodb_stats_persistent in all mtr tests. Instead, only a few special tests will disable statistics, adjust timeouts or whatever.
In mysql-5.6, the result is 5. The table has actually 5 rows:
INSERT INTO t2 VALUES (1,1),(2,2),(3,2),(4,3),(5,3);
Moreover, if I rerun the EXPLAIN, I get rows=6 change to rows=5. Sometimes it happens on the second re-run. Sometimes even the first EXPLAIN returns rows=5.