SQL syntax debug

From LemonWiki共筆
Jump to navigation Jump to search

How to debug MySQL query syntax

Approach 1: Reduce the complexity of Sql query

Steps:

  • Reduce the complexity of SQL syntax ex: remove the WHERE condition
  • Examine the sub query part only
  • Enable the MySQL server log contains each query or errors. Details on Testing#Database_server
  • Disable unnecessary permission of database operation

Approach 2: Using MySQL syntax checker

Approach 3: Beautify the Sql syntax

Beautify the SQL syntax for better readability.

  • Using the text editor Sublime, and enable the Menu option: View > Syntax >SQL. The syntax will become colorful for easy to read!
  • Instant SQL Formatter

Approach 4: Compare the row counts between source table with target table

The `source_table` contains raw data. Another `target_table` contains parsed data.

SELECT @timer := CURRENT_TIMESTAMP();
SET @cnt_error := 0;
SET @message := '';

-- un-expected condition
SET @cnt_source := 31;
SET @cnt_target := 25;
SELECT IF(@cnt_source <> @cnt_target, @cnt_error := @cnt_error + 1, '');
SELECT IF(@cnt_source <> @cnt_target, @message := CONCAT(@message, 'some problem!', '\r'), '');


-- expected condition
SET @cnt_source := 11;
SET @cnt_target := 11;
SELECT IF(@cnt_source <> @cnt_target, @cnt_error := @cnt_error + 1, '');
SELECT IF(@cnt_source <> @cnt_target, @message := CONCAT(@message, 'some problem!', '\r'), '');


-- real data
SET @cnt_source := (SELECT COUNT(*) FROM `source_table`);
SET @cnt_target := (SELECT COUNT(*) FROM `target_table`);
SELECT IF(@cnt_source <> @cnt_target, @cnt_error := @cnt_error + 1, '');
SELECT IF(@cnt_source <> @cnt_target, @message := CONCAT(@message, 'some problem!', '\r'), '');


SELECT CURRENT_TIMESTAMP() AS 'end time', @timer AS 'start time', TIMEDIFF(CURRENT_TIMESTAMP(), @timer) AS 'time elapsed';
SELECT @cnt_error;
SELECT @message;

Related articles

MySQL server configuration

Further reading


Troubleshooting of ...

Template