[MDEV-30444] Set log-slow-verbosity=explain was not working Created: 2023-01-21  Updated: 2023-02-05  Resolved: 2023-02-05

Status: Closed
Project: MariaDB Server
Component/s: Parser
Affects Version/s: None
Fix Version/s: N/A

Type: Bug Priority: Minor
Reporter: Ragul Assignee: Unassigned
Resolution: Not a Bug Votes: 0
Labels: None


 Description   

Trying to enable slow_query with verbosity enabled with different options in Mariadb 10.6.*. But unable to set option- explain for log-slow-verbosity in session

https://mariadb.com/kb/en/explain-in-the-slow-query-log/

while we can set other options as query_plan and InnoDB in the session

> set log_slow_verbosity=explain;
 
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'explain' at line 1
 
 
 
> set log_slow_verbosity=query_plan;
 
Query OK, 0 rows affected (0.009 sec)
 
 
 
> set log_slow_verbosity=innodb;
 
Query OK, 0 rows affected (0.038 sec)
 
 
 
> show variables like '%slow%';
 
+------------------------------+------------------------------------+
 
| Variable_name                | Value                              |                                                                                                 
+------------------------------+------------------------------------+
 
| :::                          |                                    |
 
| log_slow_verbosity           | query_plan                                                                                                                                                                                                                                                        
| slow_query_log               | ON                                                                                                                                   
| slow_query_log_file          | /var/opt/na/log/mysql-1/slow-query.log                                                                                               
+------------------------------+------------------------------------+

And while the same is working fine when we set this variable as part of my.cnf file

> cat /etc/na/my-1.cnf | grep log-slow
 
log-slow-verbosity=query_plan,explain
 
 
 
> show variables like '%log_slow%';
 
+------------------------------+------------------------------------+
 
| Variable_name                | Value                              |                                                                                                 
+------------------------------+------------------------------------+
 
| log_slow_verbosity           | query_plan,explain                                                                                                                   |
 
+------------------------------+--------------------------------------------------------------------------------------------------------------------------------------+
 
6 rows in set (0.001 sec)

Is this option not supported in MariaDB 10.6 which is not mentioned in the docs (Not sure about other MariaDB version) or am I missing anything https://mariadb.com/kb/en/slow-query-log-extended-statistics/

 Comments   
Comment by Alice Sherepa [ 2023-01-23 ]

The "explain" option needs quotation marks:

MariaDB [test]> set log_slow_verbosity='explain';
 
Query OK, 0 rows affected (0,000 sec)
 
 
 
MariaDB [test]> show variables like 'log_slow_verbosity';
 
+--------------------+---------+
 
| Variable_name      | Value   |
 
+--------------------+---------+
 
| log_slow_verbosity | explain |
 
+--------------------+---------+
 
1 row in set (0,001 sec)
 
 
 
MariaDB [test]> select version();
 
+--------------------+
 
| version()          |
 
+--------------------+
 
| 10.6.9-MariaDB-log |
 
+--------------------+
 
1 row in set (0,000 sec)

Comment by Ragul [ 2023-01-23 ]

Thanks @alice. with quotes it works fine for explain

But intereting to know how its working without quotes for innodb,query_plan

Comment by Sergei Golubchik [ 2023-02-05 ]

you can omit quotes in some cases. But EXPLAIN is a keyword, that's why it has to be quoted, otherwise it'll be a syntax error.

Generated at Thu Feb 08 10:16:18 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.