Get Full MySQL Query String on Insert or Update

问题:

Need help with MySQL as it's not really my forte. So any help is appreciated.

I have issues on my site where UPDATE or INSERT were done with missing values. This caused some issues on other functions on the site, but I am not able to find where the UPDATE or INSERT were done in any of the classes.

Is there any way, maybe a MySQL trigger, that I could add to these tables that would allow me to store the original or full query of the UPDATE or INSERT. I have tried logging but that applies to the whole database and it takes up too much diskspace.

Thanks in advance for any replies.

PS: At the moment, the PHP classes are a bit messy as we're still in the development stage, so adding exceptions to the updates or inserts functions will take too much time. So please focus the answer to the question. Thanks again.

回答1:

You can get the current SQL query as a string with the following statement:

SELECT info FROM INFORMATION_SCHEMA.PROCESSLIST WHERE id = CONNECTION_ID()

So what you have to do is to create a TRIGGER which runs on insert and/or update operations on your table which should (i) get the current sql statement and (ii) insert it into another table, like so:

DELIMITER |

CREATE TRIGGER log_queries_insert BEFORE INSERT ON `your_table`
FOR EACH ROW
BEGIN
    DECLARE original_query VARCHAR(1024);
    SET original_query = (SELECT info FROM INFORMATION_SCHEMA.PROCESSLIST WHERE id = CONNECTION_ID());
    INSERT INTO `app_sql_debug_log`(`query`) VALUES (original_query);
END;
|
DELIMITER ;

You will have to create two triggers - one for updates and one for inserts. The trigger inserts the new query as a string in the app_sql_debug_log table in the query column.

回答2:

I think you need to check General Query Log of your db server.

The server ... ... logs each SQL statement received from clients. ... ... Since MySQL 5.1.6 log can be a file or a table.

回答3:

You don't exactly have to log it into a database.

Use file_put_contents with FILE_APPEND and store it in a text file using the tab delimiters or as a csv. That way you can easily import to a database when you need to or view the file in Excel or Numbers (in Mac) whenever you need them.

You can log the text file by dates, ie sql_queries_2012-05-24, and just store it in a folder. Text files shouldn't take as more space than storing it in a database. A simple 'if else' statement, with php date functions, should get the logging sorted by dates.

Using text files is certainly more cost-effective in resources than storing them in a database. Moreover, using php fgetcsv to index and INSERT from text files is much faster than using mysql UPDATE.

回答4:

If you don't want some value missing on the database, can you use constraint to enforce that?

回答5:

Simple PHP-based Solution

Use override_function in PHP to override mysql_query with your own function that you can use to profile and/or log any of the queries in question.

rename_function('mysql_query', 'mysql_query_orig');
override_function('mysql_query', '$query', 'return override_mysql_query($query);');

function override_mysql_query($query)
{
    $result = mysql_query_orig($query);

    if (stripos($query, "mytablename") !== FALSE) {
        // log, echo, etc.
    }

    return $result;
}

回答6:

If you neither can modify the application to log what you send to MySQL (normally, solution #1), nor you can enable general query log (solution #2, but yes, not for production) - you may want to use the MySQL Proxy. See this: https://github.com/mysql/mysql-proxy

MySQL Proxy is lightweight program which you can put between your client (PHP scripts) and the MySQL server. You can run MySQL Proxy on the same server you run the MySQL server itself, and make your clients connect to the proxy port instead of MySQL server directly (if you cannot modify client, you can move MySQL server to another port, and setup MySQL Proxy to the port previously used by MySQL Server).

Then, MySQL proxy can be extended with custom scripts, where your custom script may catch various types of events: new connections, queries sent to server, etc. For queries you have the flexibility to log them, or block them, or modify the query, or add new queries in addition to those actually sent, whatever.

Bad news - custom scripts are implemented in Lua ( http://dev.mysql.com/doc/refman/5.5/en/mysql-proxy-scripting.html). Good news - you can find lots of sample Lua scripts for MySQL proxy. For example here: http://forge.mysql.com/wiki/Lua_Scripts_For_MySQL_Proxy_Examples. In particular you may want to check "Blocking unwanted queries" example and modify it to your needs (you won't actually block anything, but will print certain queries to the log).

MySQL Proxy adds some overhead, but generally it is pretty lightweight. If you keep your Lua scripts light as well - it should work all right.

EDIT:

The repository was last updated on 2014, and currently, the version archives say this:

MySQL Proxy is not GA, and is not recommended for Production use.

We recommend MySQL Router for production use. Download MySQL Router »

MySQL Router doesn't look like it allows the same kind of functionality as MySQL Proxy sadly.

回答7:

If your server has PHP 5.3.0 or above installed then this might be helpful http://www.php.net/manual/en/mysqlnd.plugin.php

回答8:

You probably want to turn on binary logging so you can look at every update (note: not every query). You just need to add the --log-bin option, then look through the resulting log using mysqlbinlog. If necessary, you can start a copy of your database from a backup, start in the log at the position of the backup (saved in the backup using something like the --master-data option to mysqldump), and run the updates one at a time until you get a bad row. You then know which update is the culprit. You can script that last part and/or use binary search on the length of the log to make it go faster.