MySQL Bugs: #23371: Automatic append of STRICT_TRANS_TABLES to the sql

Bug #23371	Automatic append of STRICT_TRANS_TABLES to the sql_mode.
Submitted:	17 Oct 2006 14:01	Modified:	17 Oct 2006 15:55
Reporter:	Sébastien Ayotte	Email Updates:
Status:	Not a Bug	Impact on me:	None
Category:	Connector / J	Severity:	S3 (Non-critical)
Version:	5.0.3	OS:	Linux (Linux)
Assigned to:		CPU Architecture:	Any

Description:
Server : Ubuntu 2.6.12-10-386

MySQL : 5.0.16-max

java -version
java version "1.5.0_07"
Java(TM) 2 Runtime Environment, Standard Edition (build 1.5.0_07-b03)
Java HotSpot(TM) Client VM (build 1.5.0_07-b03, mixed mode, sharing)

DBCP 1.2.1

I don't know if it supose to be the default behavior of the driver but if I try to set the sql_mode to any value, the driver append the STRICT_TRANS_TABLES mode to my setting. That prevent me to set the mode I needed.

How to repeat:
import org.apache.commons.dbcp.BasicDataSource;
import java.sql.*;

public class Test {

  public static void main(String[] args) {
    try {
      BasicDataSource datasource= new BasicDataSource();
      datasource.addConnectionProperty("sessionVariables",         "@@session.sql_mode=''");
      datasource.setDriverClassName("com.mysql.jdbc.Driver");
      datasource.setPassword("something");
      datasource.setUrl("jdbc:mysql://bd/something");
      datasource.setUsername("something");
      
      String query = "select @@session.sql_mode;";
      
      Connection con = datasource.getConnection();
      Statement s = con.createStatement();
      ResultSet rs = s.executeQuery(query);
      while (rs.next()) {
        System.out.println("\n\n\n SESSION : " + rs.getString(1) + "\n\n\n");
      }
      rs.close();
      s.close();
      con.close();
    } catch (Exception e) {
      e.printStackTrace();
    }   
  }
}

Suggested fix:
In com.mysql.jdbc.Connection at ligne 4104 I replaced commandBuf.append("STRICT_TRANS_TABLES'"); by 
commandBuf.append("'");

That was it.

Sébastien,

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.

Thanks Mark,

I was going throught the doc and couldn't find the rigth options to set to disable the feature. I had old code to maintain and I need a not to strict mode for the SQL.

Thanks for the quick reply :-)

Bug #114615 marked as duplicate of this one.