MySQL Bugs: #22120: sessionVariables don't work

Bug #22120	sessionVariables don't work
Submitted:	8 Sep 2006 14:57	Modified:	26 Sep 2006 18:30
Reporter:	William Chiquito	Email Updates:
Status:	Can't repeat	Impact on me:	None
Category:	Connector / J	Severity:	S3 (Non-critical)
Version:	5.0.3	OS:	Windows (Windows XP SP2)
Assigned to:	Todd Farmer	CPU Architecture:	Any

Description:
I have a stringconnection (URL):

String connectionURL = "jdbc:mysql://localhost:3306/world?user=user&password=password&sessionVariables=sql_mode='STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION'";

Soon I execute stored procedure to see the value of the session variable sql_mode and the result is empty (sql_mode='')

MySQL 5.0.24a

WHY?

How to repeat:
1.- Create Stored Procedure:

DELIMITER $$

DROP PROCEDURE IF EXISTS `world`.`MODO`$$

CREATE DEFINER=`root`@`localhost` PROCEDURE `modo`()
BEGIN
	SELECT @@session.sql_mode AS SQLMODE;
END$$

DELIMITER ;

2.- Create page test:

<%@ page import="java.sql.*" %>
<%@ page import="java.util.*" %>

<%

String connectionURL = "jdbc:mysql://localhost:3306/world?user=user&password=password&sessionVariables=sql_mode='STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION'";
Connection connection = null;
Statement statement = null;
Class.forName("com.mysql.jdbc.Driver").newInstance();
connection = DriverManager.getConnection(connectionURL);
CallableStatement msProcedure = connection.prepareCall("{ call MODO() }");
ResultSet rs = msProcedure.executeQuery();
while (rs.next())
	{
		out.print("sql_mode: " + rs.getString("SQLMODE"));
	}
rs.close();
msProcedure.close();
%>

Hi William,

Thank you for your bug report.  I am unable to reproduce the problem you
reported, however.  Below is my test case and configuration information:

Configuration:
Windows XP
MySQL 5.0.18-nt
JRE 1.5.0_06
mysql-connector-java-5.0.3

Java code:

package mytest;

import java.sql.CallableStatement;
import java.sql.ResultSet;

import testsuite.BaseTestCase;

public class Bug22120 extends BaseTestCase {

	public Bug22120(String name) {
		super(name);
	}

	public void testBug22120() throws Exception {

		createTestProc();

		CallableStatement msProcedure = this.conn
				.prepareCall("{ call BUG_22120() }");
		ResultSet rs = msProcedure.executeQuery();
		while (rs.next()) {
			String str = rs.getString("SQLMODE");
			super.assertEquals(
							"STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER",
							str);
			System.out.println(str);
		}
		dropProcedureIfExists("BUG_22120");
	}

	public static void main(String[] args) {
		junit.textui.TestRunner.run(Bug22120.class);
	}

	private void createTestProc() throws Exception {

		dropProcedureIfExists("BUG_22120");

		this.stmt.executeUpdate("CREATE PROCEDURE `BUG_22120`()" + " BEGIN "
				+ " SELECT @@session.sql_mode AS SQLMODE;" + " END");
	}

	private void dropProcedureIfExists(String procName) throws Exception {
		this.stmt.executeUpdate("DROP PROCEDURE IF EXISTS " + procName);
	}

}

Java output:

.Loading JDBC driver 'com.mysql.jdbc.Driver'
Done.
Done.
STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER
Time: 0.406
OK (1 test)
Connected to 5.0.18-nt

Hi Todd,

My configuration information:

Windows XP SP2
MySQL 5.0.24a-community-max
JRE 1.4.2_05
mysql-connector-java-5.0.3
My page .jsp is running in Tomcat 4.1

What you think that it can be?

Thanks.

William,

What results do you get when you run the stored procedure from the command-line client?  What about when you execute the SELECT statement directly using Connector/J (outside the context of the stored procedure):

SELECT @@session.sql_mode AS SQLMODE;

Hi Todd,

command-line client:

mysql> use world
Database changed
mysql> call modo();
+---------+
| SQLMODE |
+---------+
|         |
+---------+
1 row in set (0.01 sec)

Query OK, 0 rows affected (0.01 sec)

mysql> select @@session.sql_mode;
+----------------------------------------------------------------+
| @@session.sql_mode                                             |
+----------------------------------------------------------------+
| STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION |
+----------------------------------------------------------------+
1 row in set (0.00 sec)

SELECT statement directly using Connector/J:

El Modo es: STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION

Some idea?

thanks.

William,

This behavior appears to be dependent on the sql_mode in effect when the stored procedure was created.  The online documentation contains the following note in http://dev.mysql.com/doc/refman/5.0/en/create-procedure.html :

MySQL stores the sql_mode system variable setting that is in effect at the time a routine is created, and always executes the routine with this setting in force.

The information you provided establishes that the Connector/J results are consistent with the command-line results.

Thanks Todd.

That was the problem.

thanks again.