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

``` mysql
 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)[^1]:

``` mysql
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.


[^1]: <http://stackoverflow.com/questions/2950676/difference-between-set-autocommit-1-and-start-transaction-in-mysql-have-i-misse>
