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):

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.