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:

Then it will record every request of the server into the system database, 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) :

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  is automatically done after each operation (making the   useless). To deactivate it, launch

Attention: when several  are executed before one   (for instance in a loop), it will only cancel the operations consecutive to the last.

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.

2003: Can't connect to MySQL server
Change the parameter "host".

Invalid use of group function

 * In the case of a, use   instead of   to modify the record in function of some others.
 * For an  or a , the fields compared by   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.