Modify tep_db_query Logging for osCommerce-MS2

by Chemo

What does this contribution do?

This contribution modifes the stock osCommerce code for logging queries. Currently, the stock method is virtually useless. It will log every query and create a log file that may contain the data that you want but will have to get past the 99% of other stuff you don't.

Once installed you can select which queries to log based on the execution time. For instance, you can use "5" and all queries that take longer than 5 seconds will be logged to file. All those that take 4.9999 seconds and less will not be logged. This will help you zero in on those problem queries and fix them faster.

Example Output

COOKIES: Enabled IP ADDRESS: <removed> TIME: 11/17/2004 17:23:56 EXECUTION TIME: 23.917675 seconds SCRIPT: /product_info.php QUERY: select p.products_id, p.products_image from orders_products opa, orders_products opb, orders o, products p where opa.products_id = '124' and opa.orders_id = opb.orders_id and opb.products_id != '124' and opb.products_id = p.products_id and opb.orders_id = o.orders_id and p.products_status = '1' group by p.products_id order by o.date_purchased desc limit 6

How do I install this contribution?

STEP 1 - Edit *catalog*/includes/functions/database.php

Find the function tep_db_query()

function tep_db_query($query, $link = 'db_link') { global $$link; if (defined('STORE_DB_TRANSACTIONS') && (STORE_DB_TRANSACTIONS == 'true')) { error_log('QUERY ' . $query . "\n", 3, STORE_PAGE_PARSE_TIME_LOG); } $result = mysql_query($query, $$link) or tep_db_error($query, mysql_errno(), mysql_error()); if (defined('STORE_DB_TRANSACTIONS') && (STORE_DB_TRANSACTIONS == 'true')) { $result_error = mysql_error(); error_log('RESULT ' . $result . ' ' . $result_error . "\n", 3, STORE_PAGE_PARSE_TIME_LOG); } return $result; }

REPLACE WITH THIS CODE:

function tep_db_query($query, $link = 'db_link') { global $$link; # Change the value below to meet your needs $time_cutoff = 5.00; $_start = explode(' ', microtime()); $result = mysql_query($query, $$link) or tep_db_error($query, mysql_errno(), mysql_error()); $_end = explode(' ', microtime()); $_time = number_format(($_end[1] + $_end[0] - ($_start[1] + $_start[0])), 6); if (defined('STORE_DB_TRANSACTIONS') && (STORE_DB_TRANSACTIONS == 'true') && ($_time > $time_cutoff)) { if (SID) { $cookie_info = 'COOKIES: Not enabled'."\n"; } else { $cookie_info = 'COOKIES: Enabled'."\n"; } $ip_addy = 'IP ADDRESS: ' . $_SERVER["REMOTE_ADDR"] . "\n"; error_log($cookie_info. $ip_addy . 'TIME: '.strftime('%m/%d/%Y %H:%M:%S')."\n".'EXECUTION TIME: '.$_time .' seconds'."\n".'SCRIPT: '.$_SERVER["PHP_SELF"]."\n".'QUERY: ' . $query . "\n\n", 3, STORE_PAGE_PARSE_TIME_LOG); } return $result; }

STEP 2 - Edit *admin*/includes/functions/database.php

Do the same thing to this file. Replace tep_db_query() with the code above.

STEP 3 - Admin Control Panel -> Configuration -> Logging

Log Destination -> Make sure this is your FULL FILESYSTEM path and includes the filename

For example: /home/username/public_html/log/queries_debug.txt

I would recommend putting this file in your document root so you can inspect it as needed from any web browser. Most will display the contentst in the browser window but even a download will give instant access to the info without having to FTP the file.

Store Database Queries -> True

This will enable the logging of the queries.

Notes

The installation is pretty straight forward. The target audience for this contribution are coders, developers, and advanced webmasters that want to log only selective queries based on executing time.  Thus, if you are clueless and can't figure it out and/or correct the install problem please email: blackhole@mesoimpact.com

Support

osCommerce is a community driven organization and as such the support base will fall entirely on the [coder and developer] forum members. The code is thoroughly commented and should be easy to follow for any coder. I offer limited support in-between paid projects (feeding my family comes first before volunteer time).

If you use this contribution and find it useful a small donation can be made via PayPal.  If you're a coder, designer, or webmaster and would like to contract me for site analysis or custom coding please email me with the link at the top of this page.  Thank you for your support!

Enter Donation amount: $

Credits

This contribution is hack of the stock osC code. Credit goes to them for having the idea of logging the queries but my credit for modifying it so the data is useful. Enjoy!