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
http://www.sumoc.com/blog/index.cfm/2005/12/30/MyS...
HTH
Yes, I agree, you still need the querystring. The problem described above is with the querystring. So it doesn't work with hotfix 2 and the querystring param.
Cheers,
Mark
We are trying to install latest updater to fix other issues, and this has busted a large part of one of our sites. Right or wrong it is how our developers pull back last inserted ID.
Like others, we have allowmultiqueries in our JDBC driver settnigs and it worked fine until now!
Unfortunately I have not seen any progress on this bug - apart from it being confirmed. I'm not holding out much hope for it as the response was a bit - "well it works in 8".
I've managed to work around it by leaving our servers at hotfix 1 and applying some fixes individually - specifically a locking one.
What is the specific issue that you've come across? Can you put a single patch in for it?
Cheers,
Mark
We are trying to resolve the issue where the mail spool gets locked up and we have to restart ColdFusion to get emails to process. I think there is a hotfix specific to this, but I wanted to install all of the patches.
We were also praying that it would somehow fix other instability issues with server - we have several CF servers but this one has something bad wrong with it.
Oh well, thanks again for the reply - we'll try the individual hotfix (IIRC).