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

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:

And to turn them both off use:

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

Hope it helps, Mark

I found it in this excellent article on optimising MSSQL

The times that are returned from the queries are in micro seconds as documented on msdn.

Cheers, Mark

You can use it to clear the query cache - to ensure you are starting from a level playing field. Or if you are tuning queries in a application and want to ensure they are not cached run it before each run:

Cheers, Mark

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.

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

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.

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

Cheers, Mark

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:

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

Voila, it works like expected.

Cheers, Mark Lynch

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:

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

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:

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

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

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

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.

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

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.

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:

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:

Hope it helps, Cheers, Mark