Details
-
Type:
Bug
-
Status: Closed
-
Priority:
Major
-
Resolution: Fixed
-
Affects Version/s: None
-
Fix Version/s: None
-
Component/s: None
-
Labels: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;
Gliffy Diagrams
Attachments
Issue Links
- is part of
-
MDEV-3932 5.6 merge
-
- Closed
-
Activity
- All
- Comments
- Work Log
- History
- Activity
- Transitions
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.
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.