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
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;
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.
Change the parameter "host".
WHEREto modify the record in function of some others.
DELETE, the fields compared by
INmay not belong to the same type.
Use phpMyAdmin to find the exact syntax error location.
Replace the "IN" by some joins.
Manage research, learning and skills at NCR Works. Create an account using LinkedIn to manage and organize your omni-channel knowledge. NCR Works is like a shopping cart for information -- helping you to save, discuss and share.