MySQL / Coldfusion 8 : sql-mode

Question

We've been doing some testing with Coldfusion and MySQL and are a bit puzzled. We know that there are different 'sql-mode' flags you can set in the /etc/my.cnf file which will cause MySQL to behave certain ways, depending on the modes defined. We know you can also set these flags whenever starting MySQL using '--sql-mode=' flags.

In /etc/my.cnf, we see that there were no modes defined (one was commented out) so we added 'sql-nodes=""' and restarted mysql (/etc/init.d/mysqld restart). Whenever we go to the MySQL command line to see what mode is set, here's the output:

mysql> SELECT @@GLOBAL.sql_mode;
+-------------------+
| @@GLOBAL.sql_mode |
+-------------------+
|                   | 
+-------------------+
1 row in set (0.00 sec)

mysql> SELECT @@SESSION.sql_mode;
+--------------------+
| @@SESSION.sql_mode |
+--------------------+
|                    | 
+--------------------+
1 row in set (0.00 sec)

This tells me that there is no mode, which is what we're going for. However, doing the same thing in Coldfusion 8, we see the following:

---

SELECT @@GLOBAL.sql_mode;
[empty string]


SELECT @@SESSION.sql_mode;
STRICT_TRANS_TABLES

---

This shows the Global sql-mode is empty, while the Session sql-mode is set to 'STRICT_TRANS_TABLES' which is what we don't want.

Now, I can set the Session sql-mode to be empty in Coldfusion before we do anything else, which gives me the desired effect, but I don't really want to go to every query and add that.

I (tried) looking in the /etc/init.d/mysqld script to see if I could find where it would set the mode, but didn't see anything.

Do you guys have ANY suggestions or thoughts to what's going on? It's as if Coldfusion is setting it somewhere, however there's nothing in the Coldfusion Administrator that I can see that would set this.

Answer 1

My guess is that somewhere in the CF/JDBC connection is caching that information. Have you tried restarting CF or adding a new datasource to see what happens?

Answer 2

I did a little research on this, and it looks like it's a compatibility issue with either Java or JDBC:

The driver needs the STRICT_TRANS_TABLES mode enabled to enforce JDBC compliance on truncation checks.

If you can't use STRICT_TRANS_TABLES as part of your sql_mode, then you'll have to disable truncation checks by adding "jdbcCompliantTruncation=false" as a URL configuration parameter.

Found here: http://bugs.mysql.com/bug.php?id=23371

Answer 3

Based on what Ben Doom said, you might want to try setting up a datasource using MySQL's MyODBC driver for MYSQL to see if you get a different result.