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.cfm. 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.

[john.j.rice]

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 />
&nbsp;[I]SET @@session.tx_isolation = 'REPEATABLE-READ';<br />[/I]
@@session.tx_isolation = #qSession2.tx_i#
</p>
</cfoutput>

[john.j.rice]

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'</u
rl>
<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>