Study Guide: MySQL->Debugging
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)[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

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

  This article uses material from the Wikipedia page available here. It is released under the Creative Commons Attribution-Share-Alike License 3.0.

MySQL/Debugging
 



 

Connect with defaultLogic
What We've Done
Led Digital Marketing Efforts of Top 500 e-Retailers.
Worked with Top Brands at Leading Agencies.
Successfully Managed Over $50 million in Digital Ad Spend.
Developed Strategies and Processes that Enabled Brands to Grow During an Economic Downturn.
Taught Advanced Internet Marketing Strategies at the graduate level.


Manage research, learning and skills at defaultLogic. Create an account using LinkedIn or facebook to manage and organize your Digital Marketing and Technology knowledge. defaultLogic works like a shopping cart for information -- helping you to save, discuss and share.


  Contact Us