0

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 mysql session

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

while we can set other options as query_plan and innoDB in mysql 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 10.6 which is not mentioned in the docs or am i missing anything https://mariadb.com/kb/en/slow-query-log-extended-statistics/

2
  • jira.mariadb.org
    – Rick James
    Jan 20, 2023 at 16:12
  • Removed mysql tag. This is a feature of MariaDB specifically, and not supported by MySQL. Jan 20, 2023 at 18:57

1 Answer 1

1

Reported the same in MariaDB (https://jira.mariadb.org/browse/MDEV-30444) .

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.

"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 |
+--------------------+---------+

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.