You get a bonus - 1 coin for daily activity. Now you have 1 coin

general recommendations for detecting errors in SQL single queries

Lecture



Summing up described in this part, I would recommend the following:

Always try to find a query that causes unwanted behavior.

Use logs:

  1. General query log
  2. Logs in your application
  3. MySQL Proxy or any other proxy
  4. Other

Analyze what is wrong, correct the problem according to the result

Below is a list of techniques that we discussed in the first part.

Reception # 1: use the inference operator to output the query in the form in which the DBMS receives it.

Method # 2: use the general query log if you need to determine which particular query causes the wrong behavior of your application.

Admission number 3: after you have identified the request causing the problem, run it on the command line and analyze the result.

Technique # 4: Try to change the SQL so that the result is correct. Use search engines to find workaround.

Method 5: Use EXPLAIN EXTENDED to understand how the SQL query is optimized (and therefore executed).

Reception number 6: convert DML queries to the appropriate SELECT to find out which lines will be changed.

Reception number 7: check your script step by step in the reverse order until you find the problem query.

Reception number 8: always check the result of the query! Use your connector tools or interactive client output.

Admission number 9: set up your application in such a way that it will record request logs on its own.

Method # 10: use MySQL Proxy or any other proxy.


Comments


To leave a comment
If you have any suggestion, idea, thanks or comment, feel free to write. We really value feedback and are glad to hear your opinion.
To reply

Error detection methods in SQL application

Terms: Error detection methods in SQL application