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

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

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

Enable security Manager

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.

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:

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

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