Debug database queries

Debug database queries
This is a simple utility intended for opencart developers to inspect/debug sql queries.

The module will add extra html at the end of each page that displays all the sql queries issued to the database (by default this html will be hidden. You can inspect it using your browser's developer tools).

The duration of each query is also displayed (in milliseconds, the '.' is a decimal separatator).

Features
  • Show executed sql in the order the queries are issued to the database
  • Show duration of each query in milliseconds (php microtime is used for timing)
  • Show backtrace of the query - file/line. (many thanks to @geometr for his contribution, you can check out his github fork here)
  • Ability to sort the queries by duration (descending), execution order (ascending, this is the default) or file.
  • Supports basic SQL formatting for the select, from, where, order by clauses each displayed on a different line (configurable).
  • Supports basic SQL highlighting of SQL keywords for readability (configurable).
  • Supports showing/hiding the html separately for the front and back end (see constants defined at the start of the script).
The module by default hides the additional html for both front and back end. Review the constants defined in the start of the script for the available options that can be configured.

Installation
  • For older opencart versions 1.5.x you need to have VQmod installed (version 2.4.1 or greater). Copy the modifcation xml in <OPENCART>/vqmod/xml folder
  • For opencart 2.x copy the ocmod modifcation xml in <OPENCART>/system folder so you can easily change it if needed. Click "Refresh" in your modifcations page. (you need of course to do that, after each time you change the script).

Troubleshooting
In a fresh opencart installation, the module should work out of the box. However if you have plugins/themes installed and it doesn't work, here is a couple of things you can check:
  • Delete your modification cache and refresh modifications a couple of times.
  • Force your browser to reload the full page skipping its cache (usually with Ctrl-F5)
  • Make sure no php error exists in the log. If it does, you need to troubleshoot the problem yourself
  • Make sure no error exists in the modifications log. If it does, you need to troubleshoot the problem yourself
  • Enable logging in module's xml by changing the appropriate constant. It will write in the opencart log file any issues it encounters. The module tries to find the </body> tag to inject the html. If the response is not a full page and there is no such tag, the module will not add any html. If you need to work with such requests, you should adapt the module yourself
  • The module changes only two files: <OPENCART>/system/library/db.php where a new class named QueryDebug is added to gather query information and convert it to html and <OPENCART>/system/engine/front.php (router.php for Opencart 3) where it modifies the response text by searching the </body> tag to inject the queries html before it. Inspect the files generated in the modification cache folder (<OPENCART>/system/storage/modification) to verify that the changes are there
If all else fail leave a message in the comments section and I will try to answer it. However please note that this is a free module so no official support is provided. If I find the time to answer your comment and my answer works for you then that's great. If it doesn't and you can't troubleshoot the problem yourself, then please just don't use it.
In any case I would appreciate it if you don't spam the comments section with repeative requests for support




Download
  • Developed by OpenCart Community
  • Documentation Included

Rating

Compatibility
2.0.1.1, 2.0.2.0, 2.0.3.1, 2.1.0.1, 2.1.0.2, 2.2.0.0, 2.3.0.2, 3.0.1.1, 3.0.1.2, 3.0.2.0, 1.5.4, 1.5.4.1, 1.5.5.1, 1.5.6, 1.5.6.1, 1.5.6.2, 1.5.6.3, 1.5.6.4

Last Update
24 Oct 2017

Created
24 Dec 2013
2573 Downloads
21 Comments
open4dev
open4dev
Member since: 1 Oct 2013

View all extensions Get Support