SQL syntax debug: Difference between revisions
Jump to navigation
Jump to search
| (13 intermediate revisions by the same user not shown) | |||
| Line 4: | Line 4: | ||
Steps: | Steps: | ||
* Reduce the complexity of SQL syntax | * Reduce the complexity of SQL syntax e.g. remove the {{ kbd | key = WHERE }} condition | ||
* Examine the | * Examine the query result of subqueries | ||
* Enable the MySQL server log. Details on [[Testing#Database_server]] | * (optional) Enable the MySQL server log contains each query or errors. Details on [[Testing#Database_server]] | ||
* Disable unnecessary database operation | * Disable unnecessary permission of database operation | ||
== Approach 2: Using MySQL syntax checker == | == Approach 2: Using MySQL syntax checker == | ||
* [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}} [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. | ||
* [http://developer.mimer.se/validator/index.htm Mimer SQL Developers - Mimer SQL Validator] Syntax checker for SQL-2003 standard and so on. | * {{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. | * [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] | * 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] | ||
| Line 19: | Line 19: | ||
* 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! | * 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] | * [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 == | == Related articles == | ||
Online IDE | |||
* {{Gd}} [https://sqliteonline.com/ SQL OnLine IDE] | |||
MySQL server configuration | MySQL server configuration | ||
* [http://mysqltuner.com/ MySQLTuner-perl by major] | * [http://mysqltuner.com/ MySQLTuner-perl by major] | ||
* [[Troubleshooting of MySQLTuner]] | |||
Further reading | Further reading | ||
* Craig Kerstiens (2016). [http://www.craigkerstiens.com/2016/01/08/writing-better-sql/ Writing more legible SQL] {{access | date = 2016-11-03}} | * 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]] | [[Category:Programming]] [[Category:MySQL]] [[Category:Data Science]] | ||
Latest revision as of 11:39, 7 September 2020
How to debug MySQL query syntax
Approach 1: Reduce the complexity of Sql query[edit]
Steps:
- Reduce the complexity of SQL syntax e.g. remove the 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[edit]
MySQL :: MySQL Workbench (blog: The MySQL Workbench Developer Central Site): Syntax checker for MySQL statements. Required to install the software.- 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.
- cweiske.de: tools: PHP-SQLlint: Required to install the software.
- More on Where can I find a good MySQL syntax checker? - Quora
Approach 3: Beautify the Sql syntax[edit]
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[edit]
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[edit]
Online IDE
MySQL server configuration
Further reading
- Craig Kerstiens (2016). Writing more legible SQL [Last visited: 2016-11-03]
Troubleshooting of ...
- PHP, cUrl, Python, selenium, HTTP status code errors
- Database: SQL syntax debug, MySQL errors, MySQLTuner errors or PostgreSQL errors
- HTML/Javascript: Troubleshooting of javascript, XPath
- Software: Mediawiki, Docker, FTP problems, online conference software
- Test connectivity for the web service, Web Ping, Network problem, Web user behavior, Web scrape troubleshooting
Template