- 1 Logging
- 2 Exceptions handling
- 3 Errors
- 3.1 1130: Host 'example.com' is not allowed to connect to this MySQL server
- 3.2 1093 - You can't specify target table '...' for update in FROM clause
- 3.3 2003: Can't connect to MySQL server
- 3.4 Invalid use of group function
- 3.5 SQLSTATE: Syntax error or access violation
- 3.6 This version of MySQL doesn't yet support 'LIMIT & IN/ALL/ANY/SOME subquery'
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.
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):
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
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
WHEREto modify the record in function of some others.
- For an
DELETE, the fields compared by
INmay not belong to the same type.
SQLSTATE: 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.