CF 7.0.2 Cumulative Hotfix 2 breaks MySQL Multiple Queries
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:
`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
<cftry>
<cfset oDriver = createObject("java","com.mysql.jdbc.Driver")>
<cfoutput> #oDriver.getMajorVersion()#.#oDriver.getMinorVersion()#</cfoutput>
<cfcatch type="any">
Unknown
</cfcatch>
</cftry>
<cfquery name="qTest" datasource="dsn_test">
INSERT INTO tbl_test (data)
VALUES ('Test String');
SELECT LAST_INSERT_ID() as lastInsertID;
</cfquery>
<cftry>
<cfdump var='#qTest#'>
<cfcatch type="any">
No Query set returned from query.
</cfcatch>
</cftry>
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/6ef0253CF 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
I did some searching and couldn't find any references for it broken or otherwise, however, as we are using MyISAM which doesn't support transactions, the only reliable way to get the auto insert ID on MySQL that I have found is this way.
It's been working flawlessly in my development system but when I pushed it onto staging it was not working at all. Much frustration later I discovered that is was the Cumulative Hotfix patch 2. It's not the kind of feature that I would expect to break in a hotfix so hence the frustration.
I've submitted a bug/wish to Adobe and I hope someone takes it up and can see why it's not working. Re expecting it to work - that's generally what I expect ColdFusion to do, and not break between minor revisions. BTW - has it ever been officially unsupported? I.e. I've never found much to say either way except from a few comments from Christian Cantrell.
Anyway, thanks for the feedback.
Cheers,
Mark
Cheers,
Mark