Editing SQL syntax debug

How to debug MySQL query syntax

== Approach 1: Reduce the complexity of Sql query ==

Steps:
* Reduce the complexity of SQL syntax e.g. remove the {{ kbd | key = WHERE }} condition
* Examine the query result of subqueries
* (optional) 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 ==
* {{Gd}} [https://www.mysql.com/products/workbench/ MySQL :: MySQL Workbench] (blog: [http://mysqlworkbench.org/ The MySQL Workbench Developer Central Site]): Syntax checker for MySQL statements. Required to install the software.
* {{Gd}} [http://developer.mimer.se/validator/index.htm Mimer SQL Developers - Mimer SQL Validator] Syntax checker for SQL-2003 standard and so on. Not required to install the software, checking result was able to be viewed using the browser.
* [http://cweiske.de/php-sqllint.htm cweiske.de: tools: PHP-SQLlint]: Required to install the software. 
* More on [https://www.quora.com/Where-can-I-find-a-good-MySQL-syntax-checker Where can I find a good MySQL syntax checker? - Quora]

== Approach 3: Beautify the Sql syntax ==
Beautify the SQL syntax for better readability.
* Using the text editor [http://www.sublimetext.com/ Sublime], and enable the Menu option: {{ kbd | key = View > Syntax >SQL }}. The syntax will become colorful for easy to read!
* [http://www.dpriver.com/pp/sqlformat.htm Instant SQL Formatter]

== Approach 4: Compare the row counts between source table with target table ==
The {{kbd | key=<nowiki>`source_table`</nowiki>}} contains raw data. Another {{kbd | key=<nowiki>`target_table`</nowiki>}} contains parsed data.
<pre>
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;
</pre>

== Related articles ==
Online IDE
* {{Gd}} [https://sqliteonline.com/ SQL OnLine IDE]

MySQL server configuration
* [http://mysqltuner.com/ MySQLTuner-perl by major]
* [[Troubleshooting of MySQLTuner]]

Further reading
* Craig Kerstiens (2016). [http://www.craigkerstiens.com/2016/01/08/writing-better-sql/ Writing more legible SQL] {{access | date = 2016-11-03}}

{{Template:Troubleshooting}}

[[Category:Programming]] [[Category:MySQL]] [[Category:Data Science]]