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.
| Resolution | Fixed [ 1 ] | |
| Status | Open [ 1 ] | Closed [ 6 ] |
| Workflow | defaullt [ 27810 ] | MariaDB v2 [ 43400 ] |
| Workflow | MariaDB v2 [ 43400 ] | MariaDB v3 [ 62493 ] |
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.
| Link |
This issue relates to |
| Workflow | MariaDB v3 [ 62493 ] | MariaDB v4 [ 146835 ] |
| Link |
This issue relates to |
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.