Mysql connection string - custom db session vars

[FrenchFry]

Hi, I'm having an issue with the initialization of my database session through coldfusion.

This is on MySQL 5.1.28

Specifically, it's an issue with the tx_isolation variable in MySQL.

When I initialize a database connection, I don't get the right session default. I have determined that this only occurs through CF. I don't get the same problem connecting through a java client or a client like Navicat and SqlYog, so it must be specific to the CF datasource setup.

When I connect:

My global variable is:
tx_isolation : repeatable-read

My session variable is:
tx_isolation : read-commited

As you can see, my session does not default to the server setting when I initialize my db connection.
How can I initialize my jdbc connection through CF so that the mysql session variable is correct?

Thanks in advance.

[BKBK]

You shouldn't be involved with such fine-grained details. Unless for specialist applications, such matters are better left to Coldfusion and MySQL.

In my experience, installing MySQL for Coldfusion, with everything set to default, works for almost every case. CF8 has an in-built driver for MySQL 5.1.x. If you're on CFMX7.x, the procedure is as follows.

Stop Coldfusion. Download the Connector/J jar-file for MySQL 5.1.x from MySQL.com. It is usually wrapped in a zip file. Extract the file and copy the jar-file to {CF_ROOT}/WEB-INF/lib. Restart Coldfusion. When you configure a datasource in the Administrator, choose the "other" option as the driver type.

[Guest]

We are posting this here for more detail but we will make a call to Adobe for help since this issue seems like it will require that .

We are experiencing the same issue with a database that has STATEMENT level logging on.

The issue occurs even when using the MySql Connector/J. CF is somehow still forcing the value change. It seems CF is hijacking any and all DSN's with com.mysql.jdbc.Driver. We have even set the default value into the url as an extra attempt. Adding this name/value pair does nothing to help: sessionVariables=tx_isolation='READ-COMMITTED'.

The error message is alway:
Binary logging not possible. Transaction level 'READ-COMMITTED' in InnoDB is not safe for binlog mode 'STATEMENT'



The issue seems to be that the CF datasource manager is forcing tx_isolation to READ-COMMITTED even when the MySql server global is set to REPEATABLE-READ.

We tested out how this could happen and how we could work around it. The follow results show the results running SQL direct via Java and then via the CFQUERY tag. We can see how the global value can be changed and we can change it. (The code that generated these results also follows)

Java forced @@session.tx_isolation = READ-COMMITTED

Java default @@session.tx_isolation = REPEATABLE-READ

CFQUERY Tag:
@@global.tx_isolation = REPEATABLE-READ
CFQUERY Tag:
@@session.tx_isolation = READ-COMMITTED

After forced with:
SET @@session.tx_isolation = 'REPEATABLE-READ';
@@session.tx_isolation = REPEATABLE-READ

-----------------
Code:

<cfset request.dsn = "xxxxx" />
<cfset request.dbs = "xxxxx:3306" />
<cfset request.user = "xxxxx" />
<cfset request.pass = "xxxxx" />

<cfscript>
oClass = CreateObject("java", "java.lang.Class");
oClass.forName("com.mysql.jdbc.Driver");
oDM = CreateObject("java", "java.sql.DriverManager");

oConn = oDM.getConnection("jdbc:mysql://#request.dbs#/eln?sessionVariables=tx_isolation='READ-COMMITTED'&user=#request.user#&password=#request.pass#");
oRS = oConn.createStatement().executeQuery("SELECT @@session.tx_isolation");
oRS.next();
/* this will force the tx_isolation= to 'READ-COMMITTED' the way the CF datasource manager is */
strJavaForce = "Java forced @@session.tx_isolation = ";
strJavaForce &= oRS.getObject( oRS.getMetaData().getColumnName(1) );
oConn.close();


/* this will just use the global tx_isolation */
oConn = oDM.getConnection("jdbc:mysql://#request.dbs#/eln?user=eln&password=eln");
oRS = oConn.createStatement().executeQuery("SELECT @@session.tx_isolation");
oRS.next();

strJavaDefault = "Java default @@session.tx_isolation = ";
strJavaDefault &= oRS.getObject( oRS.getMetaData().getColumnName(1) );
oConn.close();

</cfscript>


<cfquery datasource="#request.dsn#" name="qGlobal">
SELECT @@global.tx_isolation AS tx_i;
</cfquery>
<cfquery datasource="#request.dsn#" name="qSession">
SELECT @@session.tx_isolation AS tx_i;
</cfquery>
<cfquery datasource="#request.dsn#">
SET @@session.tx_isolation = 'REPEATABLE-READ';
</cfquery>
<cfquery datasource="#request.dsn#" name="qSession2">
SELECT @@session.tx_isolation AS tx_i;
</cfquery>


<cfoutput>
<p>
#strJavaForce#
</p>
<p>
#strJavaDefault#
</p>
<p>
CFQUERY Tag:<br />
@@global.tx_isolation = #qGlobal.tx_i#
<BR />

</p>
<p>
CFQUERY Tag:<br />
@@session.tx_isolation = #qSession.tx_i#
</p>
<p>
After forced with:<br />
 <em>SET @@session.tx_isolation = 'REPEATABLE-READ';<br /></em>
@@session.tx_isolation = #qSession2.tx_i#
</p>
</cfoutput>

[Guest]

We were able to get a work-around in place by adding the DSN to the jrun resources.

We accessed the underlying JRUN admin interface, selected each individual cluster node and went to the resources tab. This apparently manages the jrun-resources.xml file that is stored in \cfusion.ear\cfusion.war\WEB-INF\cfusion\lib.

From there we went to JDBC Data Sources and added the settings. We then edited the jurn-resources.xml file directly and changed the driver from org.gjt.mm.mysql to:
<driver>com.mysql.jdbc.Driver</driver>

We also added:
<isolation-level>REPEATABLE-READ</isolation-level>

A restart of the CF service gave the result we'd been looking for:
CFQUERY Tag:
@@global.tx_isolation = REPEATABLE-READ
CFQUERY Tag:
@@session.tx_isolation = REPEATABLE-READ


The one problem now for us is that it was not a single DSN that has the issue. We have over three dozen datasources that all point to different databases on several mysql boxes. All of the CF datasource settings may have to be removed from CF and added in this way.

Here is the entry that was added to jrun-resources.xml.

<data-source>
<dbname>jrun_proof_of_concept</dbname>
<driver>com.mysql.jdbc.Driver</driver>
<url>jdbc:mysql://dbs:3306/db?sessionVariables=tx_isolation='REPEATABLE-READ'</url>
<username>un</username>
<password>pw</password>
<isolation-level>REPEATABLE-READ</isolation-level>
<encrypted>true</encrypted>
<encryption-class>jrun.security.JRunCrypterForTwofish</encryption-class>
<native-results>true</native-results>
<remove-on-exceptions>true</remove-on-exceptions>
<pool-statements>false</pool-statements>
<initial-connections>1</initial-connections>
<connection-timeout>1200</connection-timeout>
<pool-retry>30</pool-retry>
<transaction-timeout>20</transaction-timeout>
<cache-enabled>false</cache-enabled>
<cache-size>5</cache-size>
<cache-refresh-interval>30</cache-refresh-interval>
<jndi-name>jrun_proof_of_concept</jndi-name>
<poolname>Pool</poolname>
<minimum-size>0</minimum-size>
<maximum-size>2147483647</maximum-size>
<user-timeout>20</user-timeout>
<skimmer-frequency>420</skimmer-frequency>
<shrink-by>5</shrink-by>
<maximum-soft>true</maximum-soft>
<debugging>true</debugging>
<disable-pooling>true</disable-pooling>
<description>jrun_proof_of_concept</description></data-source>