MySQL/Debugging

Logging There a a few ways to debug a MySQL script. For example, if can become necessary to log every SQL request. To do so: SET GLOBAL general_log = 'ON'; SET GLOBAL log_output = 'TABLE'; Then it will record every request of the server into the system database mysql, table general_log. Exceptions handling In MySQL, the anomalies like "division by zero" don't return any error, but NULL. However, some exceptions may occur when manipulating tables, for example to avoid that a list of insertions stops in its middle, because of a "UNIQUE" constraint. The following example functions on an InnoDB table (and not MyISAM)http://stackoverflow.com/questions/2950676/difference-between-set-autocommit-1-and-start-transaction-in-mysql-have-i-misse: ALTER TABLE `MyTable1` ADD UNIQUE(`id`); INSERT INTO MyTable1 (id) VALUES('1'); START TRANSACTION; INSERT INTO MyTable1 (id) VALUES('2'); INSERT INTO MyTable1 (id) VALUES('3'); INSERT INTO MyTable1 (id) VALUES('1'); IF condition THEN COMMIT; ELSE ROLLBACK; END IF; Here, an error rises when inserting a second id=1. But according to one condition, the script can cancel the insertions of 2 and 3, or commit them anyway. By default, MySQL is set to autocommit, it means that a COMMIT is automatically done after each operation (making the ROLLBACK useless). To deactivate it, launch SET autocommit = 0; Attention: when several COMMIT are executed before one ROLLBACK (for instance in a loop), it will only cancel the operations consecutive to the last COMMIT. Errors 1130: Host 'example.com' is not allowed to connect to this MySQL server When connecting from a remote computer, the account used is not authorized. It should be set so: GRANT ALL PRIVILEGES ON *.* TO 'MyUser1'@'%' WITH GRANT OPTION; instead of or in addition to: GRANT ALL PRIVILEGES ON *.* TO 'MyUser1'@'localhost' WITH GRANT OPTION; 1093 - You can't specify target table '...' for update in FROM clause It occurs when trying to delete some lines according to a selection of these same lines. It just needs to use some intermediary CREATE TEMPORARY TABLE. 2003: Can't connect to MySQL server Change the parameter "host". Invalid use of group function In the case of a SELECT, use HAVING instead of WHERE to modify the record in function of some others. For an UPDATE or a DELETE, the fields compared by IN may not belong to the same type. SQLSTATE[42000]: Syntax error or access violation Use phpMyAdmin to find the exact syntax error location. This version of MySQL doesn't yet support 'LIMIT & IN/ALL/ANY/SOME subquery' Replace the "IN" by some joins.