Turning on general_log in MySQL with MAMP

Backstory: something was screwing up data in my database, and for the life of me I could not find what was being derpy. Possibly seeing the actual SQL queries that the database got would shed some light (spoiler: it did), so here’s what I’ve done:

1. I shall assume you’re on OSX. Other systems will be similar, but a) consult the manual for MySQL, and b) paths WILL be different
2. turn on general_log in MySQL under MAMP
3. how I debugged once I had that

Turn on logging

We have to add a directive to the my.cnf file for MySQL to read and interpret. The sad thing is that if it’s a fresh install, MAMP will not have that file, anywhere. You’ll need to create it.

$ touch /Applications/MAMP/conf/my.cnf && subl /Applications/MAMP/conf/my.cnf

If the file is empty, this should be the only content:

[mysqld]
general_log = 1

If the file is not empty, find the [mysqld] header, and add the general_log directive there (or flip it on by using 1 or ON as values).

If it doesn’t have that header, add the header and the directive to the end of the file, and save it.

Restart MAMP.

Fire up an SQL window, and run this:

SHOW VARIABLES;

You should see two things there: general_log and general_log_file. If the latter does not exist, the content of the file above should be

[mysqld]
general_log = 1
general_log_file = '/path/to/file.log'

Remember to restart MAMP. If MySQL does not start, there’s something wrong with the config file (wrong syntax, etc).

How to debug

Stick a placeholder query in the codebase. I’m using WordPress, so my example queries are

global $wpdb; // If this is not done in the function yet
$wpdb->query( "SELECT 12345" );

That means that when I look at the logs, I can totally just search for 12345, and immediately see what’s up.

A query block looks something like this in the log file:

150401  1:21:31	  204 Query	SELECT option_value FROM wp_options WHERE option_name = '_wc_session_1' LIMIT 1
		  204 Query	SELECT option_value FROM wp_options WHERE option_name = 'woocommerce_tax_display_cart' LIMIT 1
		  204 Query	SELECT option_value FROM wp_options WHERE option_name = 'widget_pages' LIMIT 1
		  204 Query	SELECT option_value FROM wp_options WHERE option_name = 'widget_calendar' LIMIT 1
		  204 Query	SELECT option_value FROM wp_options WHERE option_name = 'widget_tag_cloud' LIMIT 1
		  204 Query	SELECT option_value FROM wp_options WHERE option_name = 'widget_nav_menu' LIMIT 1
		  204 Query	SELECT option_value FROM wp_options WHERE option_name = 'widget_woocommerce_widget_cart' LIMIT 1
		  204 Query	SELECT option_value FROM wp_options WHERE option_name = 'widget_woocommerce_layered_nav_filters' LIMIT 1
		  204 Query	SELECT option_value FROM wp_options WHERE option_name = 'widget_woocommerce_layered_nav' LIMIT 1
		  204 Query	SELECT option_value FROM wp_options WHERE option_name = 'widget_woocommerce_price_filter' LIMIT 1
		  204 Query	SELECT option_value FROM wp_options WHERE option_name = 'widget_woocommerce_product_categories' LIMIT 1
		  204 Query	SELECT option_value FROM wp_options WHERE option_name = 'widget_woocommerce_product_search' LIMIT 1
		  204 Query	SELECT option_value FROM wp_options WHERE option_name = 'widget_woocommerce_product_tag_cloud' LIMIT 1
		  204 Query	SELECT option_value FROM wp_options WHERE option_name = 'widget_woocommerce_products' LIMIT 1
		  204 Query	SELECT option_value FROM wp_options WHERE option_name = 'widget_woocommerce_recent_reviews' LIMIT 1
		  204 Query	SELECT option_value FROM wp_options WHERE option_name = 'widget_woocommerce_recently_viewed_products' LIMIT 1
		  204 Query	SELECT option_value FROM wp_options WHERE option_name = 'widget_woocommerce_top_rated_products' LIMIT 1
		  204 Query	SELECT option_value FROM wp_options WHERE option_name = 'woocommerce_permalinks' LIMIT 1
		  204 Query	SELECT * FROM wp_posts WHERE ID = 4 LIMIT 1
		  204 Query	SELECT option_value FROM wp_options WHERE option_name = 'woocommerce_enable_coupons' LIMIT 1
		  204 Query	SELECT ID FROM wp_posts WHERE post_type = 'shop_subscription'
		  204 Query	SELECT option_value FROM wp_options WHERE option_name = 'woocommerce_paypal_settings' LIMIT 1
		  204 Query	SELECT option_value FROM wp_options WHERE option_name = 'theme_switched' LIMIT 1
		  204 Query	SELECT option_value FROM wp_options WHERE option_name = 'woocommerce_lock_down_admin' LIMIT 1
          204 Query SELECT 12345
		  204 Quit

Moving that example query, or sticking multiple ones in there I can home in on the code.

I’ll also use this:

die( '<pre>' . var_export( debug_backtrace( false ), true ) . '</​pre>' );

which will print a neat little backtrace of what called the function I’m currently in, so I can also move in between functions I need to place the placeholder query earlier.