SQL syntax debug - Revision history

Unknown user: /* Approach 1: Reduce the complexity of Sql query */

2020-09-07T03:39:53Z

Approach 1: Reduce the complexity of Sql query

← Older revision		Revision as of 11:39, 7 September 2020
Line 6:		Line 6:
	* Reduce the complexity of SQL syntax e.g. remove the {{ kbd \| key = WHERE }} condition		* Reduce the complexity of SQL syntax e.g. remove the {{ kbd \| key = WHERE }} condition
	* Examine the query result of subqueries		* Examine the query result of subqueries
	* Enable the MySQL server log contains each query or errors. Details on [[Testing#Database_server]]		* (optional) Enable the MySQL server log contains each query or errors. Details on [[Testing#Database_server]]
	* Disable unnecessary permission of database operation		* Disable unnecessary permission of database operation

2020-09-07T03:39:06Z

Approach 1: Reduce the complexity of Sql query

← Older revision		Revision as of 11:39, 7 September 2020
Line 4:		Line 4:

	Steps:		Steps:
	* Reduce the complexity of SQL syntax ~~ex:~~ remove the {{ kbd \| key = WHERE }} condition		* Reduce the complexity of SQL syntax e.g. remove the {{ kbd \| key = WHERE }} condition
	* Examine the ~~sub~~ query ~~part only~~		* Examine the query result of subqueries
	* Enable the MySQL server log contains each query or errors. Details on [[Testing#Database_server]]		* Enable the MySQL server log contains each query or errors. Details on [[Testing#Database_server]]
	* Disable unnecessary permission of database operation		* Disable unnecessary permission of database operation

2020-08-20T06:17:34Z

2020-08-19T12:55:37Z

Approach 1: Reduce the complexity of Sql query

← Older revision		Revision as of 20:55, 19 August 2020
Line 4:		Line 4:

	Steps:		Steps:
	* Reduce the complexity of SQL syntax ex: ~~reduce~~ the {{ kbd \| key = WHERE }} condition		* Reduce the complexity of SQL syntax ex: remove the {{ kbd \| key = WHERE }} condition
	* Examine the sub query part only		* Examine the sub query part only
	* Enable the MySQL server log contains each query or errors. Details on [[Testing#Database_server]]		* Enable the MySQL server log contains each query or errors. Details on [[Testing#Database_server]]

2020-08-19T12:54:39Z

Approach 1: Reduce the complexity of Sql query

← Older revision		Revision as of 20:54, 19 August 2020
Line 7:		Line 7:
	* Examine the sub query part only		* Examine the sub query part only
	* Enable the MySQL server log contains each query or errors. Details on [[Testing#Database_server]]		* Enable the MySQL server log contains each query or errors. Details on [[Testing#Database_server]]
	* Disable unnecessary database operation ~~permission~~		* Disable unnecessary permission of database operation

	== Approach 2: Using MySQL syntax checker ==		== Approach 2: Using MySQL syntax checker ==

2018-05-08T23:22:29Z

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

← Older revision		Revision as of 07:22, 9 May 2018
Line 30:		Line 30:
	SET @cnt_source := 31;		SET @cnt_source := 31;
	SET @cnt_target := 25;		SET @cnt_target := 25;
	~~-- SELECT @cnt_source <> @cnt_target;~~
	SELECT IF(@cnt_source <> @cnt_target, @cnt_error := @cnt_error + 1, '');		SELECT IF(@cnt_source <> @cnt_target, @cnt_error := @cnt_error + 1, '');
	SELECT IF(@cnt_source <> @cnt_target, @message := CONCAT(@message, 'some problem!', '\r'), '');		SELECT IF(@cnt_source <> @cnt_target, @message := CONCAT(@message, 'some problem!', '\r'), '');

2018-05-08T08:32:53Z

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

← Older revision		Revision as of 16:32, 8 May 2018
Line 20:		Line 20:
	* [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 ~~parsed~~ table ==		== 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>		<pre>
	SELECT @timer := CURRENT_TIMESTAMP();		SELECT @timer := CURRENT_TIMESTAMP();
Line 44:		Line 44:
	-- real data		-- real data
	SET @cnt_source := (SELECT COUNT(*) FROM `source_table`);		SET @cnt_source := (SELECT COUNT(*) FROM `source_table`);
	SET @cnt_target := (SELECT COUNT(*) FROM `~~parsed_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, @cnt_error := @cnt_error + 1, '');
	SELECT IF(@cnt_source <> @cnt_target, @message := CONCAT(@message, 'some problem!', '\r'), '');		SELECT IF(@cnt_source <> @cnt_target, @message := CONCAT(@message, 'some problem!', '\r'), '');

2018-05-08T04:12:32Z

Approach 4: Verify the row counts between source table with parsed table

← Older revision		Revision as of 12:12, 8 May 2018
Line 20:		Line 20:
	* [http://www.dpriver.com/pp/sqlformat.htm Instant SQL Formatter]		* [http://www.dpriver.com/pp/sqlformat.htm Instant SQL Formatter]

	== Approach 4: ~~Verify~~ the row counts between source table with parsed table ==		== Approach 4: Compare the row counts between source table with parsed table ==

	<pre>		<pre>

2018-05-08T04:06:06Z

Approach 4: Verify the row counts between source table with parsed table

← Older revision		Revision as of 12:06, 8 May 2018
Line 45:		Line 45:
	SET @cnt_source := (SELECT COUNT(*) FROM `source_table`);		SET @cnt_source := (SELECT COUNT(*) FROM `source_table`);
	SET @cnt_target := (SELECT COUNT(*) FROM `parsed_table`);		SET @cnt_target := (SELECT COUNT(*) FROM `parsed_table`);
	~~-- SELECT @cnt_source <> @cnt_target;~~
	SELECT IF(@cnt_source <> @cnt_target, @cnt_error := @cnt_error + 1, '');		SELECT IF(@cnt_source <> @cnt_target, @cnt_error := @cnt_error + 1, '');
	SELECT IF(@cnt_source <> @cnt_target, @message := CONCAT(@message, 'some problem!', '\r'), '');		SELECT IF(@cnt_source <> @cnt_target, @message := CONCAT(@message, 'some problem!', '\r'), '');

2018-05-08T04:04:27Z

@@ 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!
 * [http://www.dpriver.com/pp/sqlformat.htm Instant SQL Formatter]
 == Related articles ==

← Older revision		Revision as of 14:17, 20 August 2020
Line 54:		Line 54:

	== 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]