Details
-
Task
-
Status: Closed (View Workflow)
-
Minor
-
Resolution: Done
Description
Development tasks:
MDEV-406 ANALYZE $stmt
MDEV-6388 ANALYZE $stmt output in the slow query log
MDEV-6382 ANALYZE $stmt and security
MDEV-6109 EXPLAIN JSON
Need to check also for
- Replication (ANALYZE may have an effect. Is it properly binlogged. Can a slave thread run ANALYZE stmt command?)
- Nested VIEWs and single-table UPDATE/DELETE (check EXPLAINs)
<spetrunia>: there was a problem with printing table names in ANALYZE
<spetrunia>: it was resolved
<spetrunia>: but I still had doubts.. so, it would be nice to make a testcase that would use nested update-able VIEW
<spetrunia>: then run EXPLAIN UPDATE/DELETE on it
<spetrunia>: then run ANALYZE UPDATE/DELETE
<spetrunia>: and check that the ouptuts match
<spetrunia>: the problem was : it used to print table aliases (as defined by the VIEW)
<spetrunia>: and not table names
<spetrunia>: or was it vice versa
<spetrunia>: anyhow, it was not what EXPLAIN printed
<spetrunia>: I don't think it's needed to spend much time on this..
<spetrunia>: create a test case; let view definitions use table aliases
<spetrunia>: which are different from the table names
<spetrunia>: if it works, ok - PS (binary protocol)
- Get testcases for
MDEV-6382 - Whether the fact that even basic statements, like "INSERT INTO tbl VALUES ('foo')" will have their "EXPLAIN" printed into the slow query log. This is a consequence of them having EXPLAINs. Do we need a feature that avoids printing 'degenerate' query plans into the slow query log?
Attachments
Issue Links
- is blocked by
-
MDEV-7024 Assertion `! is_set()' failed in Diagnostics_area::set_eof_status on executing ANALYZE SELECT via PS
-
- Closed
-
- relates to
-
-
- Closed
-
-
-
- Closed
-
-
-
- Open
-
-
-
- Confirmed
-
-
-
- Closed
-
-
-
- Closed
-
-
-
- Closed
-
-
-
- Closed
-
-
-
- Closed
-
(5 relates to)
Activity
| Fix Version/s | 10.1.1 [ 16801 ] |
| Labels | analyze-stmt |
| Description |
Need to check also for * Replication (ANALYZE may have an effect. Is it properly binlogged. Can a slave thread run ANALYZE stmt command?) * Nested VIEWs and single-table UPDATE/DELETE (check EXPLAINs) * PS (binary protocol) |
Need to check also for * Replication (ANALYZE may have an effect. Is it properly binlogged. Can a slave thread run ANALYZE stmt command?) * Nested VIEWs and single-table UPDATE/DELETE (check EXPLAINs) * PS (binary protocol) * Get testcases for |
| Description |
Need to check also for * Replication (ANALYZE may have an effect. Is it properly binlogged. Can a slave thread run ANALYZE stmt command?) * Nested VIEWs and single-table UPDATE/DELETE (check EXPLAINs) * PS (binary protocol) * Get testcases for |
Need to check also for * Replication (ANALYZE may have an effect. Is it properly binlogged. Can a slave thread run ANALYZE stmt command?) * Nested VIEWs and single-table UPDATE/DELETE (check EXPLAINs) * PS (binary protocol) * Get testcases for * Whether the fact that even basic statements, like "INSERT INTO tbl VALUES ('foo')" will have their "EXPLAIN" printed into the slow query log. This is a consequence of them having EXPLAINs. Do we need a feature that avoids printing 'degenerate' query plans into the slow query log? |
| Assignee | Sergei Petrunia [ psergey ] | Elena Stepanova [ elenst ] |
| Fix Version/s | 10.1 [ 16100 ] | |
| Fix Version/s | 10.1.1 [ 16801 ] |
| Parent |
|
|
| Issue Type | Task [ 3 ] | Technical task [ 7 ] |
| Affects Version/s | 10.1.0 [ 12200 ] |
| Status | Open [ 1 ] | In Progress [ 3 ] |
PS (binary protocol)
As of the current tree (43f185e171eecdce41e71c548ce0bc2bd6969c0f), blocked by MDEV-7024
Whether the fact that even basic statements, like "INSERT INTO tbl VALUES ('foo')" will have their "EXPLAIN" printed into the slow query log. This is a consequence of them having EXPLAINs. Do we need a feature that avoids printing 'degenerate' query plans into the slow query log?
My vote is that disabling degenerate plans might be a nice-to-have feature, but of a very low priority. Probably it makes most sense to leave it for now and to see if we have requests for that from actual users.
| Description |
Need to check also for * Replication (ANALYZE may have an effect. Is it properly binlogged. Can a slave thread run ANALYZE stmt command?) * Nested VIEWs and single-table UPDATE/DELETE (check EXPLAINs) * PS (binary protocol) * Get testcases for * Whether the fact that even basic statements, like "INSERT INTO tbl VALUES ('foo')" will have their "EXPLAIN" printed into the slow query log. This is a consequence of them having EXPLAINs. Do we need a feature that avoids printing 'degenerate' query plans into the slow query log? |
Need to check also for * Replication (ANALYZE may have an effect. Is it properly binlogged. Can a slave thread run ANALYZE stmt command?) * Nested VIEWs and single-table UPDATE/DELETE (check EXPLAINs) {quote} <spetrunia>: there was a problem with printing table names in ANALYZE <spetrunia>: it was resolved <spetrunia>: but I still had doubts.. so, it would be nice to make a testcase that would use nested update-able VIEW <spetrunia>: then run EXPLAIN UPDATE/DELETE on it <spetrunia>: then run ANALYZE UPDATE/DELETE <spetrunia>: and check that the ouptuts match <spetrunia>: the problem was : it used to print table aliases (as defined by the VIEW) <spetrunia>: and not table names <spetrunia>: or was it vice versa <spetrunia>: anyhow, it was not what EXPLAIN printed <spetrunia>: I don't think it's needed to spend much time on this.. <spetrunia>: create a test case; let view definitions use table aliases <spetrunia>: which are different from the table names <spetrunia>: if it works, ok {quote} * PS (binary protocol) * Get testcases for * Whether the fact that even basic statements, like "INSERT INTO tbl VALUES ('foo')" will have their "EXPLAIN" printed into the slow query log. This is a consequence of them having EXPLAINs. Do we need a feature that avoids printing 'degenerate' query plans into the slow query log? |
Nested VIEWs and single-table UPDATE/DELETE (check EXPLAINs)
I am not getting any difference at table alias names between EXPLAIN and ANALYZE:
MariaDB [test]> show create table t1 \G |
*************************** 1. row ***************************
|
Table: t1 |
Create Table: CREATE TABLE `t1` ( |
`a` int(11) DEFAULT NULL, |
`b` int(11) DEFAULT NULL, |
KEY `b` (`b`) |
) ENGINE=MyISAM DEFAULT CHARSET=latin1 |
1 row in set (0.00 sec) |
|
|
MariaDB [test]> create view v1 as select * from t1 alias1; |
Query OK, 0 rows affected (0.06 sec) |
|
|
MariaDB [test]> create view v2 as select * from v1 alias2; |
Query OK, 0 rows affected (0.07 sec) |
|
|
MariaDB [test]> create view v3 as select alias3.* from t1 alias3, t2 where alias3.b = t2.b; |
Query OK, 0 rows affected (0.13 sec) |
MariaDB [test]> explain select * from v1; |
+------+-------------+--------+------+---------------+------+---------+------+------+-------+ |
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | |
+------+-------------+--------+------+---------------+------+---------+------+------+-------+ |
| 1 | SIMPLE | alias1 | ALL | NULL | NULL | NULL | NULL | 1024 | | |
+------+-------------+--------+------+---------------+------+---------+------+------+-------+ |
1 row in set (0.01 sec) |
|
|
MariaDB [test]> analyze select * from v1; |
+------+-------------+--------+------+---------------+------+---------+------+------+--------+----------+------------+-------+ |
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | r_rows | filtered | r_filtered | Extra | |
+------+-------------+--------+------+---------------+------+---------+------+------+--------+----------+------------+-------+ |
| 1 | SIMPLE | alias1 | ALL | NULL | NULL | NULL | NULL | 1024 | 1024 | 100.00 | 100.00 | | |
+------+-------------+--------+------+---------------+------+---------+------+------+--------+----------+------------+-------+ |
1 row in set (0.01 sec) |
MariaDB [test]> explain select * from v2; |
+------+-------------+--------+------+---------------+------+---------+------+------+-------+ |
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | |
+------+-------------+--------+------+---------------+------+---------+------+------+-------+ |
| 1 | SIMPLE | alias1 | ALL | NULL | NULL | NULL | NULL | 1024 | | |
+------+-------------+--------+------+---------------+------+---------+------+------+-------+ |
1 row in set (0.00 sec) |
|
|
MariaDB [test]> analyze select * from v2; |
+------+-------------+--------+------+---------------+------+---------+------+------+--------+----------+------------+-------+ |
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | r_rows | filtered | r_filtered | Extra | |
+------+-------------+--------+------+---------------+------+---------+------+------+--------+----------+------------+-------+ |
| 1 | SIMPLE | alias1 | ALL | NULL | NULL | NULL | NULL | 1024 | 1024 | 100.00 | 100.00 | | |
+------+-------------+--------+------+---------------+------+---------+------+------+--------+----------+------------+-------+ |
1 row in set (0.00 sec) |
MariaDB [test]> explain select * from v3; |
+------+-------------+--------+-------+---------------+------+---------+-----------+------+--------------------------+ |
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | |
+------+-------------+--------+-------+---------------+------+---------+-----------+------+--------------------------+ |
| 1 | SIMPLE | t2 | index | b | b | 5 | NULL | 100 | Using where; Using index | |
| 1 | SIMPLE | alias3 | ref | b | b | 5 | test.t2.b | 64 | |
|
+------+-------------+--------+-------+---------------+------+---------+-----------+------+--------------------------+ |
2 rows in set (0.00 sec) |
|
|
MariaDB [test]> analyze select * from v3; |
+------+-------------+--------+-------+---------------+------+---------+-----------+------+--------+----------+------------+--------------------------+ |
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | r_rows | filtered | r_filtered | Extra | |
+------+-------------+--------+-------+---------------+------+---------+-----------+------+--------+----------+------------+--------------------------+ |
| 1 | SIMPLE | t2 | index | b | b | 5 | NULL | 100 | 100 | 100.00 | 100.00 | Using where; Using index | |
| 1 | SIMPLE | alias3 | ref | b | b | 5 | test.t2.b | 64 | 256 | 100.00 | 100.00 | |
|
+------+-------------+--------+-------+---------------+------+---------+-----------+------+--------+----------+------------+--------------------------+ |
2 rows in set (0.05 sec) |
MariaDB [test]> explain update v1 set a = 1 where b = 100; |
+------+-------------+--------+------+---------------+------+---------+------+------+-------------+ |
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | |
+------+-------------+--------+------+---------------+------+---------+------+------+-------------+ |
| 1 | SIMPLE | alias1 | ALL | b | NULL | NULL | NULL | 1024 | Using where | |
+------+-------------+--------+------+---------------+------+---------+------+------+-------------+ |
1 row in set (0.01 sec) |
|
|
MariaDB [test]> analyze update v1 set a = 1 where b = 100; |
+------+-------------+--------+------+---------------+------+---------+------+------+--------+----------+------------+-------------+ |
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | r_rows | filtered | r_filtered | Extra | |
+------+-------------+--------+------+---------------+------+---------+------+------+--------+----------+------------+-------------+ |
| 1 | SIMPLE | alias1 | ALL | b | NULL | NULL | NULL | 1024 | 1024 | 100.00 | 25.00 | Using where | |
+------+-------------+--------+------+---------------+------+---------+------+------+--------+----------+------------+-------------+ |
1 row in set (0.00 sec) |
MariaDB [test]> explain update v2 set a = 1 where b = 100; |
+------+-------------+--------+------+---------------+------+---------+------+------+-------------+ |
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | |
+------+-------------+--------+------+---------------+------+---------+------+------+-------------+ |
| 1 | SIMPLE | alias1 | ALL | b | NULL | NULL | NULL | 1024 | Using where | |
+------+-------------+--------+------+---------------+------+---------+------+------+-------------+ |
1 row in set (0.00 sec) |
|
|
MariaDB [test]> analyze update v2 set a = 1 where b = 100; |
+------+-------------+--------+------+---------------+------+---------+------+------+--------+----------+------------+-------------+ |
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | r_rows | filtered | r_filtered | Extra | |
+------+-------------+--------+------+---------------+------+---------+------+------+--------+----------+------------+-------------+ |
| 1 | SIMPLE | alias1 | ALL | b | NULL | NULL | NULL | 1024 | 1024 | 100.00 | 25.00 | Using where | |
+------+-------------+--------+------+---------------+------+---------+------+------+--------+----------+------------+-------------+ |
1 row in set (0.00 sec) |
MariaDB [test]> explain update v3 set a = 1 where b = 100; |
+------+-------------+--------+------+---------------+------+---------+-------+------+-------------+ |
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | |
+------+-------------+--------+------+---------------+------+---------+-------+------+-------------+ |
| 1 | SIMPLE | t2 | ref | b | b | 5 | const | 13 | Using index | |
| 1 | SIMPLE | alias3 | ref | b | b | 5 | const | 293 | |
|
+------+-------------+--------+------+---------------+------+---------+-------+------+-------------+ |
2 rows in set (0.00 sec) |
|
|
MariaDB [test]> analyze update v3 set a = 1 where b = 100; |
+------+-------------+--------+------+---------------+------+---------+-------+------+--------+----------+------------+-------------+ |
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | r_rows | filtered | r_filtered | Extra | |
+------+-------------+--------+------+---------------+------+---------+-------+------+--------+----------+------------+-------------+ |
| 1 | SIMPLE | t2 | ref | b | b | 5 | const | 13 | 25 | 100.00 | 100.00 | Using index | |
| 1 | SIMPLE | alias3 | ref | b | b | 5 | const | 293 | 256 | 100.00 | 100.00 | |
|
+------+-------------+--------+------+---------------+------+---------+-------+------+--------+----------+------------+-------------+ |
2 rows in set (0.02 sec) |
MariaDB [test]> explain delete from v1 where b = 100; |
+------+-------------+--------+------+---------------+------+---------+------+------+-------------+ |
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | |
+------+-------------+--------+------+---------------+------+---------+------+------+-------------+ |
| 1 | SIMPLE | alias1 | ALL | b | NULL | NULL | NULL | 1024 | Using where | |
+------+-------------+--------+------+---------------+------+---------+------+------+-------------+ |
1 row in set (0.00 sec) |
|
|
MariaDB [test]> analyze delete from v1 where b = 100; |
+------+-------------+--------+------+---------------+------+---------+------+------+--------+----------+------------+-------------+ |
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | r_rows | filtered | r_filtered | Extra | |
+------+-------------+--------+------+---------------+------+---------+------+------+--------+----------+------------+-------------+ |
| 1 | SIMPLE | alias1 | ALL | b | NULL | NULL | NULL | 1024 | 1024 | 100.00 | 25.00 | Using where | |
+------+-------------+--------+------+---------------+------+---------+------+------+--------+----------+------------+-------------+ |
1 row in set (0.01 sec) |
MariaDB [test]> explain delete from v2 where b = 200; |
+------+-------------+--------+------+---------------+------+---------+------+------+-------------+ |
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | |
+------+-------------+--------+------+---------------+------+---------+------+------+-------------+ |
| 1 | SIMPLE | alias1 | ALL | b | NULL | NULL | NULL | 768 | Using where | |
+------+-------------+--------+------+---------------+------+---------+------+------+-------------+ |
1 row in set (0.00 sec) |
|
|
MariaDB [test]> analyze delete from v2 where b = 200; |
+------+-------------+--------+------+---------------+------+---------+------+------+--------+----------+------------+-------------+ |
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | r_rows | filtered | r_filtered | Extra | |
+------+-------------+--------+------+---------------+------+---------+------+------+--------+----------+------------+-------------+ |
| 1 | SIMPLE | alias1 | ALL | b | NULL | NULL | NULL | 768 | 768 | 100.00 | 33.33 | Using where | |
+------+-------------+--------+------+---------------+------+---------+------+------+--------+----------+------------+-------------+ |
1 row in set (0.00 sec) |
| Description |
Need to check also for * Replication (ANALYZE may have an effect. Is it properly binlogged. Can a slave thread run ANALYZE stmt command?) * Nested VIEWs and single-table UPDATE/DELETE (check EXPLAINs) {quote} <spetrunia>: there was a problem with printing table names in ANALYZE <spetrunia>: it was resolved <spetrunia>: but I still had doubts.. so, it would be nice to make a testcase that would use nested update-able VIEW <spetrunia>: then run EXPLAIN UPDATE/DELETE on it <spetrunia>: then run ANALYZE UPDATE/DELETE <spetrunia>: and check that the ouptuts match <spetrunia>: the problem was : it used to print table aliases (as defined by the VIEW) <spetrunia>: and not table names <spetrunia>: or was it vice versa <spetrunia>: anyhow, it was not what EXPLAIN printed <spetrunia>: I don't think it's needed to spend much time on this.. <spetrunia>: create a test case; let view definitions use table aliases <spetrunia>: which are different from the table names <spetrunia>: if it works, ok {quote} * PS (binary protocol) * Get testcases for * Whether the fact that even basic statements, like "INSERT INTO tbl VALUES ('foo')" will have their "EXPLAIN" printed into the slow query log. This is a consequence of them having EXPLAINs. Do we need a feature that avoids printing 'degenerate' query plans into the slow query log? |
Need to check also for * Replication (ANALYZE may have an effect. Is it properly binlogged. Can a slave thread run ANALYZE stmt command?) (/) * Nested VIEWs and single-table UPDATE/DELETE (check EXPLAINs) (/) {quote} <spetrunia>: there was a problem with printing table names in ANALYZE <spetrunia>: it was resolved <spetrunia>: but I still had doubts.. so, it would be nice to make a testcase that would use nested update-able VIEW <spetrunia>: then run EXPLAIN UPDATE/DELETE on it <spetrunia>: then run ANALYZE UPDATE/DELETE <spetrunia>: and check that the ouptuts match <spetrunia>: the problem was : it used to print table aliases (as defined by the VIEW) <spetrunia>: and not table names <spetrunia>: or was it vice versa <spetrunia>: anyhow, it was not what EXPLAIN printed <spetrunia>: I don't think it's needed to spend much time on this.. <spetrunia>: create a test case; let view definitions use table aliases <spetrunia>: which are different from the table names <spetrunia>: if it works, ok {quote} * PS (binary protocol) (x) * Get testcases for * Whether the fact that even basic statements, like "INSERT INTO tbl VALUES ('foo')" will have their "EXPLAIN" printed into the slow query log. This is a consequence of them having EXPLAINs. Do we need a feature that avoids printing 'degenerate' query plans into the slow query log? (/) |
| Status | In Progress [ 3 ] | Stalled [ 10000 ] |
| Fix Version/s | 10.1.4 [ 18400 ] | |
| Fix Version/s | 10.1 [ 16100 ] |
| Status | Stalled [ 10000 ] | In Progress [ 3 ] |
| Fix Version/s | 10.1 [ 16100 ] | |
| Fix Version/s | 10.1.4 [ 18400 ] |
| Status | In Progress [ 3 ] | Stalled [ 10000 ] |
| Workflow | MariaDB v2 [ 49701 ] | MariaDB v3 [ 64530 ] |
| Status | Stalled [ 10000 ] | In Progress [ 3 ] |
| Description |
Need to check also for * Replication (ANALYZE may have an effect. Is it properly binlogged. Can a slave thread run ANALYZE stmt command?) (/) * Nested VIEWs and single-table UPDATE/DELETE (check EXPLAINs) (/) {quote} <spetrunia>: there was a problem with printing table names in ANALYZE <spetrunia>: it was resolved <spetrunia>: but I still had doubts.. so, it would be nice to make a testcase that would use nested update-able VIEW <spetrunia>: then run EXPLAIN UPDATE/DELETE on it <spetrunia>: then run ANALYZE UPDATE/DELETE <spetrunia>: and check that the ouptuts match <spetrunia>: the problem was : it used to print table aliases (as defined by the VIEW) <spetrunia>: and not table names <spetrunia>: or was it vice versa <spetrunia>: anyhow, it was not what EXPLAIN printed <spetrunia>: I don't think it's needed to spend much time on this.. <spetrunia>: create a test case; let view definitions use table aliases <spetrunia>: which are different from the table names <spetrunia>: if it works, ok {quote} * PS (binary protocol) (x) * Get testcases for * Whether the fact that even basic statements, like "INSERT INTO tbl VALUES ('foo')" will have their "EXPLAIN" printed into the slow query log. This is a consequence of them having EXPLAINs. Do we need a feature that avoids printing 'degenerate' query plans into the slow query log? (/) |
Development tasks: Need to check also for * Replication (ANALYZE may have an effect. Is it properly binlogged. Can a slave thread run ANALYZE stmt command?) (/) * Nested VIEWs and single-table UPDATE/DELETE (check EXPLAINs) (/) {quote} <spetrunia>: there was a problem with printing table names in ANALYZE <spetrunia>: it was resolved <spetrunia>: but I still had doubts.. so, it would be nice to make a testcase that would use nested update-able VIEW <spetrunia>: then run EXPLAIN UPDATE/DELETE on it <spetrunia>: then run ANALYZE UPDATE/DELETE <spetrunia>: and check that the ouptuts match <spetrunia>: the problem was : it used to print table aliases (as defined by the VIEW) <spetrunia>: and not table names <spetrunia>: or was it vice versa <spetrunia>: anyhow, it was not what EXPLAIN printed <spetrunia>: I don't think it's needed to spend much time on this.. <spetrunia>: create a test case; let view definitions use table aliases <spetrunia>: which are different from the table names <spetrunia>: if it works, ok {quote} * PS (binary protocol) (x) * Get testcases for * Whether the fact that even basic statements, like "INSERT INTO tbl VALUES ('foo')" will have their "EXPLAIN" printed into the slow query log. This is a consequence of them having EXPLAINs. Do we need a feature that avoids printing 'degenerate' query plans into the slow query log? (/) |
| Summary | More QA for ANALYZE stmt | More QA for ANALYZE stmt and JSON |
| Status | In Progress [ 3 ] | Stalled [ 10000 ] |
| Priority | Major [ 3 ] | Minor [ 4 ] |
| Assignee | Elena Stepanova [ elenst ] | Alice Sherepa [ alice ] |
| Summary | More QA for ANALYZE stmt and JSON | More testing for ANALYZE stmt and JSON |
| Parent |
|
|
| Affects Version/s | 10.1.0 [ 12200 ] | |
| Issue Type | Technical task [ 7 ] | Task [ 3 ] |
| Assignee | Alice Sherepa [ alice ] | Elena Stepanova [ elenst ] |
| Workflow | MariaDB v3 [ 64530 ] | MariaDB v4 [ 131636 ] |
| Component/s | Tests [ 10800 ] | |
| Fix Version/s | N/A [ 14700 ] | |
| Resolution | Done [ 10200 ] | |
| Status | Stalled [ 10000 ] | Closed [ 6 ] |
| Fix Version/s | 10.1 [ 16100 ] |
No obvious issues with replication.
In statement mode ANALYZE INSERT/UPDATE/DELETE is written to the binary log as is and is executed by the slave properly (of course it will cause problems on old slaves, but it's expected).
If the underlying INSERT/UPDATE/DELETE is unsafe for SBR, ANALYZE causes the warning just like the naked statement does.
In row mode the result of the underlying statement is written and replicated as row events, ANALYZE does not play a role.
In mixed mode, the format is chosen accordingly.