DEVELOPER ZONE ::
Login / Register
MySQL has several different logs that can help you find out what is going on inside mysqld:
| Log Type | Information Written to Log |
| The error log | Problems encountered starting, running, or stopping mysqld |
| The general query log | Established client connections and statements received from clients |
| The binary log | All statements that change data (also used for replication) |
| The slow query log | All queries that took more than long_query_time seconds to execute or didn't use indexes |
By default, all log files are created in the mysqld data directory. You can force mysqld to close and reopen the log files (or in some cases switch to a new log) by flushing the logs. Log flushing occurs when you issue a FLUSH LOGS statement or execute mysqladmin flush-logs or mysqladmin refresh. See Section 13.5.5.2, “FLUSH Syntax”, and Section 8.10, “mysqladmin — Client for Administering a MySQL Server”.
If you are using MySQL replication capabilities, slave replication servers maintain additional log files called relay logs. Chapter 6, Replication, discusses relay log contents and configuration.
User Comments
Given that you have the logs(query,update,error). How does one go about linking thread to user:host:query?
I spent ages going in circles figuring out how to get MySQL to display or log warnings encountered during imports of SQL files (e.g. those generated by mysqldump). The bad news is that there seems to be no way at all of logging or displaying warnings on the server side - log-warnings is to do with logging non-critical internal MySQL problems, not query issues.
The good news is that you CAN see warnings as of MySQL 5.0.6 in the MySQL CLIENT by specifying the --show-warnings options on the CLI. You can pipe output to a file so that you can peruse your warnings at your leisure. Though this is very helpful, it does only log the warning message and not the query that caused it.
Overall it would be nice to have an extra section in this chapter that covers these options for both client and server as I've not seen this important difference adequately explained anywhere in the docs.
To help identify the query that fails from the output of a scripted import process, I wrap the following header around each table's dataset, replacing each of the names in brackets with names that provide enough info to trace the errors. Then I use 'for i in *; do; echo $i; mysql --show-warnings -e "source $i;"; done' and re-direct output as needed:
USE [database];
SET foreign_key_checks = 0;
SET sql_notes = 0;
SET @tbl_name='[tablename]';
SELECT @tbl_name;
LOCK TABLES [tablename] WRITE;
INSERT INTO [tablename] VALUES
('1','1','70000'), \
('2','1','70001');
UNLOCK TABLES;
Add your own comment.