MySQL/Debugging
Logging
editThere 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
editIn 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)[1]:
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
edit1130: Host 'example.com' is not allowed to connect to this MySQL server
editWhen 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
editIt 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
editChange the parameter "host".
Invalid use of group function
edit- In the case of a
SELECT
, useHAVING
instead ofWHERE
to modify the record in function of some others. - For an
UPDATE
or aDELETE
, the fields compared byIN
may not belong to the same type.
SQLSTATE[42000]: Syntax error or access violation
editUse phpMyAdmin to find the exact syntax error location.
This version of MySQL doesn't yet support 'LIMIT & IN/ALL/ANY/SOME subquery'
editReplace the "IN" by some joins.