Details
-
Bug
-
Status: Closed (View Workflow)
-
Major
-
Resolution: Fixed
-
11.0(EOL)
Description
The failure looks like this:
CURRENT_TEST: innodb.mdev-14846
|
--- /home/buildbot/buildbot/build/mariadb-11.1.4/mysql-test/suite/innodb/r/mdev-14846.result 2023-11-24 15:03:29.000000000 +0000
|
+++ /home/buildbot/buildbot/build/mariadb-11.1.4/mysql-test/suite/innodb/r/mdev-14846.reject 2023-11-24 17:55:45.075140470 +0000
|
@@ -38,7 +38,7 @@
|
id select_type table type possible_keys key key_len ref rows Extra
|
1 PRIMARY alias1 ALL NULL NULL NULL NULL #
|
1 PRIMARY alias2 ALL NULL NULL NULL NULL # Using where
|
-2 DEPENDENT SUBQUERY t1 ALL NULL NULL NULL NULL # Using where
|
+2 MATERIALIZED t1 ALL NULL NULL NULL NULL #
|
UPDATE t3 AS alias1 LEFT JOIN t3 AS alias2 ON ( alias1.f1 <> alias1.f2 ) SET alias1.f3 = 59 WHERE ( EXISTS ( SELECT t1.f3 FROM t1 IGNORE INDEX (f1) WHERE t1.f1 = alias1.f1 ) ) OR alias2.f1 = 'h';
|
connect con2,localhost,root,,test;
|
set debug_sync='now WAIT_FOR con2_dml';
|
@@ -51,8 +51,8 @@
|
1 PRIMARY t1 index NULL f1 12 NULL # Using index
|
1 PRIMARY t1 ALL NULL NULL NULL NULL #
|
1 PRIMARY <subquery2> eq_ref distinct_key distinct_key 13 func #
|
-2 MATERIALIZED t3 ALL NULL NULL NULL NULL #
|
2 MATERIALIZED t2 ALL NULL NULL NULL NULL #
|
+2 MATERIALIZED t3 ALL NULL NULL NULL NULL #
|
UPDATE v4, t1 SET t1.pk = 76 WHERE t1.f2 IN ( SELECT t2.f FROM t2 INNER JOIN t3 );
|
connection default;
|
ERROR 40001: Deadlock found when trying to get lock; try restarting transaction
|
Attachments
Issue Links
- relates to
-
-
- Open
-
Activity
| Field | Original Value | New Value |
|---|---|---|
| Summary | innodb.mdev-14846 started to fail in 11.0 | innodb.mdev-14846 fails in 11.0 |
| Labels | innodb-noisy-estimates |
Test harness to look at trace difference:
diff --git a/mysql-test/suite/innodb/t/mdev-14846.test b/mysql-test/suite/innodb/t/mdev-14846.test
|
index a576d244007..4af8cc1cc6d 100644
|
--- a/mysql-test/suite/innodb/t/mdev-14846.test
|
+++ b/mysql-test/suite/innodb/t/mdev-14846.test
|
@@ -2,6 +2,7 @@
|
--source include/count_sessions.inc
|
--source include/have_debug_sync.inc
|
|
+--exec touch $MYSQLTEST_VARDIR/tmp/trace.log
|
CREATE TABLE t1 (
|
pk INT,
|
f1 VARCHAR(10) NOT NULL,
|
@@ -39,8 +40,12 @@ SET DEBUG_SYNC='now SIGNAL con2_dml';
|
|
--connection default
|
SET DEBUG_SYNC='now WAIT_FOR default_dml';
|
+--remove_file $MYSQLTEST_VARDIR/tmp/trace.log
|
+set optimizer_trace=1;
|
--replace_column 9 #
|
explain UPDATE t3 AS alias1 LEFT JOIN t3 AS alias2 ON ( alias1.f1 <> alias1.f2 ) SET alias1.f3 = 59 WHERE ( EXISTS ( SELECT t1.f3 FROM t1 IGNORE INDEX (f1) WHERE t1.f1 = alias1.f1 ) ) OR alias2.f1 = 'h';
|
+evalp select trace from information_schema.optimizer_trace INTO OUTFILE '$MYSQLTEST_VARDIR/tmp/trace.log';
|
+
|
--send UPDATE t3 AS alias1 LEFT JOIN t3 AS alias2 ON ( alias1.f1 <> alias1.f2 ) SET alias1.f3 = 59 WHERE ( EXISTS ( SELECT t1.f3 FROM t1 IGNORE INDEX (f1) WHERE t1.f1 = alias1.f1 ) ) OR alias2.f1 = 'h'
|
# It holds the lock of all record in t3 and tries to acquire record lock for the table t1.
|
|
|
| Attachment | bad-trace.log [ 72614 ] |
| Attachment | good-trace.log [ 72615 ] |
Diffing the .trace files between a passing and a failing run: bad-trace.log
good-trace.log ...
some off-by-1 estimate differences produce off-by-many difference in join output cardinality:
@@ -183,8 +183,8 @@
|
{\
|
"plan_prefix": "alias1",\
|
"table": "alias2",\
|
- "rows_for_plan": 16,\
|
- "cost_for_plan": 0.0550764\
|
+ "rows_for_plan": 9,\
|
+ "cost_for_plan": 0.0437368\
|
}\
|
]\ |
which causes one query plan to be picked instead of the other
@@ -316,9 +316,9 @@
|
{\
|
"subquery_plan": {\
|
"rows": 5,\
|
- "materialization_cost": 0.038299536,\
|
- "in_exist_cost": 0.0466792,\
|
- "choosen": "materialization"\
|
+ "materialization_cost": 0.038136232,\
|
+ "in_exist_cost": 0.0350094,\
|
+ "choosen": "in_to_exists"\
|
}\
|
},\ |
| Assignee | Sergei Petrunia [ psergey ] |
| Link | This issue relates to MDEV-32785 [ MDEV-32785 ] |
Take-aways from discussion with marko:
There is no global O(1) solution.
There seems to be no acceptable global setting that we could use for the whole testsuite to make all InnoDB's estimates stable.
I have actually tried this by setting innodb_stats_persistent=0 in MDEV-4750. This caused pushback from InnoDB team and Marko has reverted it in 10.6. The rationale is that tests should use execution paths close to the defaults.
Can do it on a per-table basis
Setting stats_auto_recalc=0 or stats_persistent=0 makes the problem go away.
Or, we could add ANALYZE TABLE statements after the table has been filled and before any EXPLAIN.
(a variant from the Optimizer call: or we could add a --analyze table mtr command. )
Can do it on a per-testcase basis
We can add a .opt file for the affected tests.
(Or try adding something like '--source include/requre-innodb-stable-stats.inc')
bb-11.0-mdev-32901-v2. oleg.smirnov, could you review this please?
| Status | Open [ 1 ] | In Progress [ 3 ] |
| Assignee | Sergei Petrunia [ psergey ] | Oleg Smirnov [ JIRAUSER50405 ] |
| Status | In Progress [ 3 ] | In Review [ 10002 ] |
| Assignee | Oleg Smirnov [ JIRAUSER50405 ] | Sergei Petrunia [ psergey ] |
| Status | In Review [ 10002 ] | Stalled [ 10000 ] |
| Fix Version/s | 10.4.33 [ 29516 ] | |
| Fix Version/s | 10.5.24 [ 29517 ] | |
| Fix Version/s | 10.6.17 [ 29518 ] | |
| Fix Version/s | 10.11.7 [ 29519 ] | |
| Fix Version/s | 11.0.5 [ 29520 ] | |
| Fix Version/s | 11.1.4 [ 29024 ] | |
| Fix Version/s | 11.2.3 [ 29521 ] | |
| Fix Version/s | 11.3.2 [ 29522 ] | |
| Fix Version/s | 11.4.1 [ 29523 ] | |
| Fix Version/s | 11.0 [ 28320 ] | |
| Resolution | Fixed [ 1 ] | |
| Status | Stalled [ 10000 ] | Closed [ 6 ] |
Reproducible with something like
./mtr --mem --repeat=200 --parallel=30 innodb.mdev-14846{,,,,,,,,,,,,,,,,,,}