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

Databases - Error detection methods in SQL application

Terms: Databases - Error detection methods in SQL application