Lynch Consulting Blog - mysql

MySQL 5.1 logging changes - Log to DB and runtime config

Fri, 29 Jan 2010 08:15:00 --1000

While browsing around the MySQL site last night I discovered a number of nice new features of mysql 5.1 that relate to logging. These are:

Logging to DB instead of log files
Runtime configuration of logging.

Logging to DB instead of log files

Coming from a web development background rather than a sysadmin background I'm far more comfortable manipulating and analysing data using SQL. So to be able to log all the queries or just the slow queries for an application to the db during application development or load testing is a huge benefit. To enable logging to DB you can add the following to your my.cnf


log_output = TABLE

The logs will be written to the 'slow_log' and 'general_log' tables in the mysql database. Note - logging to tables has more overhead than logging to file, so would suggest using it primarily for development purposes. Full details of the options are on the mysql manual on log tables

Runtime configuration of logging.

This allows you to turn on and off logging without restarting MySQL - which just saves a little bit of time and makes it much nicer for debugging problems. To turn on the logging of all queries run:


SET GLOBAL general_log = 'ON';

And for just the slow query log:


SET GLOBAL slow_query_log = 'ON';

And to turn them both off use:


SET GLOBAL general_log = 'OFF';
SET GLOBAL slow_query_log = 'OFF';

If you also want to see queries not using indexes in the slow query log you can set the following variable:


SET GLOBAL log_queries_not_using_indexes = 'ON';

Hope it helps, Mark

CFMX Query Timeout Gotcha

Tue, 19 May 2009 14:04:00 --1000

NOTE: I found this blog post in my to be posted ones from 2008. While doing some load testing a while back we came across a strange error while simulating the load that we were expecting. The simulated load had a large first peak, followed by a quieter period, followed by some more peaks. During the load test we noticed a few query errors that we had never seen before, they related to the query connection timing out. It turns out that the way CF times out datasource connections is slightly quirky as described below:

The other timeout setting in the CF Admin datasource detail page is just labeled as Timeout, with a paired setting for Interval. This timeout value is how long a connection should be idle before it is removed. So if a connection goes unused for this period of time, then the next time ColdFusion checks that connection it will be removed from the pool. The frequency that ColdFusion checks the connection idle time is set by the Interval setting. The default for the Timeout setting is 20 minutes and the Interval is 7 minutes, so every 7 minutes ColdFusion will check connections for that datasource and remove connections that have been idle for 20 minutes or more. The number of connections idle connections removed from the pool at each interval is hardcoded in ColdFusion to just five. Say you have 20 idle connections hanging around in a pool, this would require at least 28 minutes to be cleaned up.

Timeout (min)

The number of minutes that ColdFusion MX maintains an unused connection before destroying it.

Interval (min)

The time (in minutes) that the server waits between cycles to check for expired data source connections to close.

Cheers, Mark

Loading MySQL timezone info on Linux and Macs

Mon, 08 Sep 2008 13:30:00 --1000

MySQL has some really useful functions that make timezone support a bit easier as described in a previous blog post.. However, you need to do a tiny bit of setup work before you can use the functions. You need to load the timezone info from your system into the mysql db. Thankfully, the MySQL Developers have included a very handy script that will do just that from a zoneinfo files which are used on Linux and on Mac's.


mysql_tzinfo_to_sql /usr/share/zoneinfo | mysql -u root mysql

Obviously change the mysql params or add a -p if you have a password or are connecting to a remote server. Check out the MySQL developer site for full details on MySQL timezone support Cheers, Mark

MySQL and Temporary tables and CASE syntax

Sun, 31 Aug 2008 20:47:00 --1000

For some reason I've always avoided the use of temporary tables in SQL (MySQL or otherwise) but today I came across a difficult problem that temporary tables made very simple. I needed to use a case function on a subquery value to return a tri-state value. I.e. yes, no or partial. The cut down query below demonstrates how it works.

First we drop the temporary table in case one has been left lying around.
Then we select a query into the temporary table which include some complex subqueries.
We can then query the temporary table to do further processing on the result, which in this case depending on the values of the class_count and class_count_assigned allows us to set a field in three states.
- 'yes' if all users for a class are selected
- 'partial' if some users are selected
- 'no' if none are selected.



	DROP TEMPORARY TABLE  IF EXISTS tmp_exams;
	/* Create temporary table query which includes student counts */
	CREATE TEMPORARY TABLE tmp_exams
	SELECT	e.id
			, e.name
			, (SELECT count(1) FROM tbl_user_classes uXc
                WHERE uXc.school_class_id = ) AS class_count
            , (SELECT count(1) FROM tbl_user_classes uXc
                INNER JOIN  tbl_users_exams uXe ON uXe.user_id = uXc.user_id
                WHERE uXc.school_class_id = 
                AND exam_id = e.id) AS class_count_assigned
	FROM	tbl_exams e;
	/* Select out the relevant info */
	SELECT e.id
			, e.name
			, e.class_count
			, e.class_count_assigned
		    ,(CASE WHEN class_count_assigned = 0 THEN 'no'
		    	WHEN class_count = class_count_assigned THEN 'yes'
		    	ELSE 'partial' END) AS active
	FROM tmp_exams e
	WHERE	1=1
	ORDER BY e.name

Mostly posted for my future reference but hope it helps out. Cheers, Mark

PHP and MySQL 5 bit fields

Mon, 25 Aug 2008 12:21:00 --1000

While debugging some PHP code the other night I came across a particularly strange problem with MySQL Bit fields. I was returning a query with some bit fields into my class but the it was not returning true or false when I switched the data. A bit of hunting and it turned out the that it was being returned as a binary type and after a few failed attempts to convert it to an integer or boolean on the PHP side I found this bug report on it. So the moral of the story is if you are selecting BIT types from a mysql DB in PHP don't do this:


 SELECT myBit
 FROM tbl_example
 WHERE id = 1

Instead cast the bit to an integer in MySQL like so:


 SELECT CAST(myBit AS unsigned integer) AS myBit, 
 FROM tbl_example
 WHERE id = 1

Voila, it works like expected. Cheers, Mark Lynch

CF Sandbox Security Tricks and Tips

Wed, 13 Aug 2008 11:07:00 --1000

I've been working on getting CF Sandbox security working. It's trickier than I first thought so here's how to do it for future reference:

Enable security Manager

Instructions from Steven Erat's blog:


Stop ColdFusion.
Locate the jvm.config file in jrun_root/bin.
Back up the file.
Open the file in a text editor.
Add the following lines to the java.args section:
-Djava.security.manager
"-Djava.security.policy=[cf_webapp_root]/WEB-INF/cfusion/lib/coldfusion.policy"
"-Djava.security.auth.policy=[cf_webapp_root]/WEB-INF/cfusion/lib/neo_jaas.policy"

NOTE the example from the adobe site has the quotes in the wrong place. Note you also need to change [cf_webapp_root] to match the location on your machine.

Datasources

After I enabled Datasource security I began to receive the following error, a bit of digging reminded me that the MySQL connector tries to do some autoconfiguration for coldfusion to optimise it.


Message: Can't find configuration template named 'coldFusion' 
Type: java.sql.SQLException

It was failing to load the configuration file which is inside the mysql connector jar file /com/mysql/jdbc/configs/coldFusion.properties I haven't gotten to the bottom of why it couldn't be loaded but adding the following to the datasource query string fixed it up:


autoConfigureForColdFusion=false

I would suggest adding some of the settings from this file as paramaters in your datasource settings as per previous post Note: This was using the following:

ColdFusion 7.0.2 Cumulative Hotfix 1 Multiserver install
MySQL connector/J 5.0.8

Hope it helps. Cheers, Mark

"Show Full Columns" problem with CFMX and MySQL solved

Sun, 01 Jun 2008 21:01:00 --1000

We've been performing some load testing on a new website we've developed and our helpful sysadmin noticed lots of queries happening on the DB that looked like this:


SHOW FULL COLUMNS FROM `dbname`.`tablename`

We weren't intentionally doing these queries but they were coming from somewhere. A bit of detective work via google found a very enlightening article about this problem. A quick read of this explained:

It turns out ColdFusion was asking Connector/J for the metadata on every field, which in turn triggered a SHOW FULL COLUMNS query for every varchar and text column returned.

It also went on to say that is had been fixed as of Connector/J 5.07. On reading the release notes it mentions that the

Driver detects when it is running in a ColdFusion MX server (tested with version 7), and uses the configuration bundle coldFusion, which sets useDynamicCharsetInfo to false (see previous entry), and sets useLocalSessionState and autoReconnect to true.

However, from my testing it wasn't doing it as we were still seeing the problem. However, we are running the MultiServer version of ColdFusion which sits atop Jrun4. So I added the parameter directly to the querystring:


&useDynamicCharsetInfo=false

So it now looks like:


jdbc:mysql://127.0.0.1:3306/mydbname?allowMultiQueries=true&useDynamicCharsetInfo=false

This has stopped the problem, and given a significant performance gain. On one of the sites I was load testing it gave a 12% throughput increase and on another one which had less queries it gave a 4% increase. Hope it helps. Cheers, Mark

CF 7.0.2 Cumulative Hotfix 2 breaks MySQL Multiple Queries

Wed, 23 Apr 2008 13:40:00 --1000

I've just spent a while figuring out a painful bug in CF and am posting here so that I can reference it from the Adobe bug tracker as I can't explain the problem in 2000 characters.

Summary of Problem

CF 7.0.2 Cumulative Hotfix 2 and 3 breaks the use of Multiple SQL statements with MySQL connectorJ connector. This is a regression as it works perfectly in CF 7.0.2 and CF 7.0.2 with Cumulative hotfix 1. I have tested the following configurations:

CF7.0.2 MySQL connectorJ 5.0.8 - works
CF7.0.2 hotfix 1 MySQL connectorJ 5.0.8 - works
CF7.0.2 hotfix 2 MySQL connectorJ 5.0.8 - broken
CF7.0.2 hotfix 3 MySQL connectorJ 5.0.8 - broken
CF7.0.2 MySQL connectorJ 3.1.10 - works
CF7.0.2 hotfix 1 MySQL connectorJ 3.1.10 - works
CF7.0.2 hotfix 2 MySQL connectorJ 3.1.10 - broken
CF7.0.2 hotfix 3 MySQL connectorJ 3.1.10 - broken

Steps to reproduce bug:

1. Set up a CF 7.0.2 Multiserver install with built in webserver 2. Download and install ConnectorJ JDBC driver for MySQL and install as per Adobe Technote http://www.adobe.com/go/6ef0253 3. Create a mysql database named "test" the and create a table with the following script:


CREATE TABLE `tbl_test` ( 
	`id`  	int(11) AUTO_INCREMENT NOT NULL,
	`data`	varchar(50) NULL,
	PRIMARY KEY(`id`)
)

4. In CF Admin create a DSN named dsn_test with following settings

JDBC Url: jdbc:mysql://127.0.0.1:3306/test?allowMultiQueries=true
Driver Class: com.mysql.jdbc.Driver
Driver name: (leave blank)
username: (valid user for your db - default: root)
password: (valid password for your db - default: 'blank')
Place the following file in the /opt/jrun4/servers/cfusion/cfusion-ear/cfusion-war directory


MySQL driver version:


 #oDriver.getMajorVersion()#.#oDriver.getMinorVersion()#

	Unknown




	INSERT INTO tbl_test (data)
	VALUES ('Test String');
	SELECT LAST_INSERT_ID() as lastInsertID;




	No Query set returned from query.

This code should function correctly and return a cfdump of the auto generated primary key as per expected screenshot. Next apply Coldfusion Cumulative hotfix 2 and restart coldfusion as per Adobe Technote http://www.adobe.com/go/kb400996 Re run the test and no query set will be returned from the driver. The qTest variable does not get set as per the broken screenshot.

Related Resources

Technote re upgrading MySQL JDBC Drivers http://www.adobe.com/go/6ef0253 CF Updates page http://www.adobe.com/support/coldfusion/downloads_updates.html#mx7 CF 7.0.2 Cumulative hot 3 http://www.adobe.com/go/kb402465 CF 7.0.2 Cumulative hot 2 http://www.adobe.com/go/kb400996 CF 7.0.2 Cumulative hot 1 http://www.adobe.com/go/kb400074 MySQL ConnectorJ http://www.mysql.com/products/connector/j/ MySQL ConnectorJ 3.1.10 Download: http://dev.mysql.com/get/Downloads/Connector-J/mysql-connector-java-3.1.10.tar.gz/from/pick#mirrors

Multiple queries with CFMX-MySQL JDBC connector

Sun, 10 Feb 2008 21:32:00 --1000

I found a little snippet the other day and included it in the tail end of a previous post - however, on reflection I thought it deserved a post of it's own. If you want to utilise multiple queries per sql statement and you are using the MYSQL JDBC connector you need to add the following to your jdbc connector querystring.


allowMultiQueries=true

So you querystring would look like:


jdbc:mysql://127.0.0.1:3306/mydbname?allowMultiQueries=true

This will allow you to do queries like the following to return autoincrement values:




 INSERT INTO tbl_demo (name)
 VALUES ( );
 SELECT last_insert_id() as newID

ID of value is: #qInsert.newID#

Please note however that this functionality is disabled as it can leave the door open for SQL Injection attacks. However, as long as you always use cfqueryparam for all the dynamic parts of your query you will be fine.

CFMX Mysql Query String for UTF8 and Multiple Queries

Thu, 07 Feb 2008 12:56:00 --1000

While testing some of the code we've written for UTF8 support for double byte characters (Chinese, Japanese, Malaysian) I discovered that there are some voodoo JDBC connection string parameters that have to be set to make it work. Firstly, CFMX used UTF8 by default so it's already configured. You need to make sure that your MySQL DB table has a Charset of utf8 as per example below:


CREATE TABLE `utf8test` ( 
	`id`     	int(11) NOT NULL,
	`data`    	varchar(25) NULL,
	PRIMARY KEY(`id`)
)CHARACTER SET utf8 COLLATE utf8_general_ci;

Then you need to add the following params to the connection string:

characterEncoding=UTF8
characterSetResults=UTF8

I also add the "allowMultiQueries=true" string which allows you to execute multiple queries in the one statement. It is disabled by default as it can open up a window for SQL Injection attacks, but this can be mitigated by always using CFQUERYPARAM's. So the final connection string looks like this:


jdbc:mysql://127.0.0.1:3306/utf8demo?allowMultiQueries=true&characterEncoding=UTF8&characterSetResults=UTF8

Hope it helps, Cheers, Mark

HOWTO Backup and restore MySQL db's

Thu, 22 Nov 2007 20:08:00 --1000

Handy reference for backing up and restoring db's via the mysql command line tools as it is by far the quickest way to do it. We do this regularly as we take copies of our staging db's from a central DB server and put them on our local machines for development work.

Backup a database & zip it up


mysqldump -h db.example.com -p mydbname | gzip > mydbname.sql.gz

This will back the mydbname database from the server db.example.com and will prompt for a password (much better than writing it in the command line) and will then gzip it into the file mydbname.sql.gz

Create a database (if it doesn't exist)


mysqladmin -h 127.0.0.1 -u root create mydbname

This is only necessary the first you are creating a copy of the db on your machine.

Restore DB backup

Note: this will overwrite all local changes to the DB


zcat mydbname.sql.gz | mysql -h 127.0.0.1 -u root mydbname

This will take your previously gzipped backup copy and restore it into the local mysql instance to the db called mydbname Hope it helps. Cheers, Mark