Description:
Hi,
With a multi-line schema, the slow query log file contains a multi-line "use", which is invalid because such "use" does not work in the MySQL client (I was not able to craft a "use" in the client that would work for a multi-line schema). See How to repeat for details.
I discovered this while working on adding schema in the slow query log file, more about this work in below.
https://jfg-mysql.blogspot.com/2024/06/rfc-database-schema-in-slow-query-log-file.html
Many thanks for looking into this,
Jean-François Gagné
How to repeat:
v=8.0.38
dbdeployer deploy single $v -c slow_query_log_file=slow.log -c slow_query_log=ON
cd ~/sandboxes/msb_${v//./_}
db_name='test
jfg'
./use <<< "create database \`$db_name\`; create table \`$db_name\`.t(id int); insert into \`$db_name\`.t values (1);"
./use "$db_name" <<< "set session long_query_time=0.5; select sleep(1), t.* from t;" > /dev/null
tail -n 7 data/slow.log
# Time: 2024-07-05T12:39:19.383640Z
# User@Host: msandbox[msandbox] @ localhost [] Id: 10
# Query_time: 1.003885 Lock_time: 0.000001 Rows_sent: 1 Rows_examined: 1
use test
jfg;
SET timestamp=1720183158;
select sleep(1), t.* from t;
Suggested fix:
I do not think there is currently a "good" way to put the "use" line in the slow query log file. The reason I think this is because I was not able to find a way to make a "use" work in the MySQL Client with a multi-line db.
One way to fix this could be to make "use" work in the client for a multi-line db and then format the slow query log file in the same way the client accepts such "use".
Another way to think about this is that a MySQL user that creates a multi-line db is looking for trouble and deserves such "breakage". But in this case, there should be a way to "refuse" the creation of a multi-line db, and this should be enabled by default. The "MySQL way" of doing this is to have a sql_mode for this, and a name for this mode could be NO_UNSAFE_SCHEMA_NAMES or ALLOW_UNSAFE_SCHEMA_NAMES.
