show variables like '%slow%';
show variables like '%long%';
- Enter the MySQL shell and run the following command:
set global slow_query_log = 'ON';
- Enable any other desired options. Here are some common examples:
Log details for queries expected to retrieve all rows instead of using an index:
set global log_queries_not_using_indexes = 'ON'
Set the path to the slow query log:
set global slow_query_log_file ='/var/log/mysql/slow-query.log';
Set the amount of time a query needs to run before being logged:
set global long_query_time = 0.01; (default is 10 seconds, Here 0.01= 100ms)
- Confirm the changes are active by entering the MySQL shell and running the following command:
show variables like '%slow%';
Edit the /etc/my.cnf file with your favorite text editor vi /etc/my.cnf
Add the following line under the “[mysqld]” section. Feel free to update the path to the log file to whatever you want:
log-slow-queries=/var/log/mysql/slow-query.log
- Enable additional options as needed. Here are the same commonly used examples from above:
Set the amount of time a query needs to run before being logged:
long_query_time=20 (default is 10 seconds)`
Log details for queries expected to retrieve all rows instead of using an index:
log-queries-not-using-indexes
- Restart the MySQL service:
service mysqld restart
- Confirm the change is active by entering the MySQL shell and running the following:
show variables like '%slow%';
According to MySQL docs, the error #1193 occurs when you use wrong code for SQLSTATE.
Message: Unknown system variable %s
And, as you can see on the same page, the SQLSTATE 99003 is not defined.
refer this link: