Bug #93606 when use Connector/J preparestatement mysql returns the wrong result
Submitted: 14 Dec 2018 7:53 Modified: 10 Jan 6:54
Reporter: huiping wu Email Updates:
Status: Verified Impact on me:
None 
Category:Connector / J Severity:S2 (Serious)
Version:5.6.40,5.7.24 OS:CentOS (6.9)
Assigned to: CPU Architecture:x86
Tags: prepared statement

[14 Dec 2018 7:53] huiping wu
Description:
Using the cache prepare statement and server prepare statement in the Connector/J driver, the mysql database returns the wrong result.

table Structure:
CREATE TABLE `hhz_user_sign` (
  `box_id` int(11) NOT NULL AUTO_INCREMENT,
  `user_id` int(11) NOT NULL COMMENT '用户',
  `last_time` varchar(18) DEFAULT NULL COMMENT '上一次签到时间',
  `times` int(2) DEFAULT NULL COMMENT '周期连续签到(2017-09-01)次数',
  `time` varchar(10) DEFAULT NULL COMMENT '周期签到时间(2017-09-01)',
  `ctime` bigint(18) DEFAULT NULL COMMENT '签到时间',
  PRIMARY KEY (`box_id`),
  UNIQUE KEY `user_id` (`user_id`) USING BTREE
) ENGINE=InnoDB  DEFAULT CHARSET=utf8;

table data:
insert into hhz_user_sign set box_id=2613932,user_id=451720,last_time='2018-12-09',times=4,time='2018-12-10';

Open the cachePreparedStatement and serverPrepareStatement at the same time in the Connector/J driver, through the Connector/J driver, in the same database connection, and in the case of cross-day, execute the following statement twice:
SELECT IF(time=DATE_FORMAT(DATE_ADD(now(), INTERVAL - 1 DAY),'%Y-%m-%d'),1,0) isContinuity,DATE_FORMAT(DATE_ADD(NOW(), INTERVAL - 1 DAY),'%Y-%m-%d') as day_yesterday,DATE_ADD(NOW(), INTERVAL - 1 DAY) day_yesterdateimte,NOW() as day_now  FROM hhz_user_sign WHERE user_id = 451720 limit 1

Suppose the first query execution time is 2018-12-10 23:59:50, and the correct result is returned. The result of the isContinuity column is 0.

Suppose the second query execution time is 2018-12-11 00:00:00 or later, mysql returns the wrong result. The result of the isContinuity column should be 1, but the result returned is 0. But the result returned by day_yesterday is correct.

In the following cases, mysql returns the correct result:
1. If you do not use cachePreparedStatement in the Connector/J driver, the results of both queries are correct. In this case, mysql will execute prepare twice. In the case of using cachePreparedStatement, mysql only executes prepare once. When performing the second query, the Connector/J driver only sends a preprocessed ID from the cache to the mysql server.

2. Use the prepare statement in the mysql command line to return the correct result.

3. Replace the now() function in the SQL statement with sysdate(), and return the correct result: IF (time=DATE_FORMAT(DATE_ADD(now(), INTERVAL - 1 DAY), '%Y-%m-%d '),1,0) isContinuity changed to IF (time=DATE_FORMAT(DATE_ADD(sysdate(), INTERVAL - 1 DAY), '%Y-%m-%d'),1,0) isContinuity

4. Replace the now() function in the SQL statement with current_date and return the correct result: IF (time=DATE_FORMAT(DATE_ADD(now(), INTERVAL - 1 DAY), '%Y-%m-%d') ,1,0) isContinuity changed to IF ( time = DATE_ADD(current_date, INTERVAL - 1 DAY),1,0) isContinuity

However, in either of the above cases, the result returned by the day_yesterday column is correct. Therefore, I am not sure what the problem is.

How to repeat:
Environmental description:
centos6.9 x86_64
MySQL:5.6.40,5.7.23
JDK:1.8
Connector/J driver:mysql-connector-java-5.1.47.jar

Open server preparedstatement and cachePreparedstatement in the Connector/J driver:
source.setUseServerPreparedStmts(true);
source.setCachePreparedStatements(true);

create table in mysql server:
CREATE TABLE `hhz_user_sign` (
  `box_id` int(11) NOT NULL AUTO_INCREMENT,
  `user_id` int(11) NOT NULL COMMENT '用户',
  `last_time` varchar(18) DEFAULT NULL COMMENT '上一次签到时间',
  `times` int(2) DEFAULT NULL COMMENT '周期连续签到(2017-09-01)次数',
  `time` varchar(10) DEFAULT NULL COMMENT '周期签到时间(2017-09-01)',
  `ctime` bigint(18) DEFAULT NULL COMMENT '签到时间',
  PRIMARY KEY (`box_id`),
  UNIQUE KEY `user_id` (`user_id`) USING BTREE
) ENGINE=InnoDB  DEFAULT CHARSET=utf8;

insert data:
insert into hhz_user_sign set box_id=2613932,user_id=451720,last_time='2018-12-09',times=4,time='2018-12-10';

Running steps:
1、set the mysql database server time to 2018-12-10 23:59:50
date -s "2018-12-10 23:59:50"

2、Within 10 second, run the java program and specify mysql host, mysql port, mysql user, mysql password, database name, as follows:
java -jar test.jar 172.16.0.1 3306 test test test

The program will lose the result twice in 10 seconds, as follows:
[root@mycentos6 ~]# java -jar test.jar  127.0.0.1 3306 wuhp maxwell maxwell
JDBC ***************** TEST
Mon Dec 10 23:59:52 CST 2018 WARN: Establishing SSL connection without server's identity verification is not recommended. According to MySQL 5.5.45+, 5.6.26+ and 5.7.6+ requirements SSL connection must be established by default if explicit option isn't set. For compliance with existing applications not using SSL the verifyServerCertificate property is set to 'false'. You need either to explicitly disable SSL by setting useSSL=false, or set useSSL=true and provide truststore for server certificate verification.
isContinuity=> 0
day_yesterday=> 2018-12-09
day_yesterdateimte=> 2018-12-09 23:59:52.0
day_now=> 2018-12-10 23:59:52.0
===================================
isContinuity=> 0
day_yesterday=> 2018-12-10
day_yesterdateimte=> 2018-12-10 00:00:02.0
day_now=> 2018-12-11 00:00:02.0

The isContinuity is still 0 in the result of the second output, and the correct result should be 1.

Then, run the program again, and the results of the two outputs are correct: isContinuity is 1.

[root@mycentos6 ~]# java -jar test.jar  127.0.0.1 3306 wuhp maxwell maxwell
JDBC ***************** TEST
Tue Dec 11 00:00:10 CST 2018 WARN: Establishing SSL connection without server's identity verification is not recommended. According to MySQL 5.5.45+, 5.6.26+ and 5.7.6+ requirements SSL connection must be established by default if explicit option isn't set. For compliance with existing applications not using SSL the verifyServerCertificate property is set to 'false'. You need either to explicitly disable SSL by setting useSSL=false, or set useSSL=true and provide truststore for server certificate verification.
isContinuity=> 1
day_yesterday=> 2018-12-10
day_yesterdateimte=> 2018-12-10 00:00:10.0
day_now=> 2018-12-11 00:00:10.0
===================================
isContinuity=> 1
day_yesterday=> 2018-12-10
day_yesterdateimte=> 2018-12-10 00:00:20.0
day_now=> 2018-12-11 00:00:20.0

Suggested fix:
I don't know how to add attachments, If you need to test the java program, please contact me at email 113194325@qq.com.I posted the java code below:

public class JDBCTest {
	public static Connection getConnection(String host, String port, String dbname, String user, String password) {
		System.out.println("JDBC ***************** TEST");
		Connection conn = null;
		try {
			Class.forName("com.mysql.jdbc.Driver");
		} catch (ClassNotFoundException e) {
			e.printStackTrace();
		}

		try {
			
			MysqlDataSource source = new MysqlDataSource();
			source.setURL("jdbc:mysql://" + host + ":" + port + "/" + dbname);
			source.setUser(user);
			source.setPassword(password);
			source.setUseServerPreparedStmts(true);
			source.setCachePreparedStatements(true);
			// source.setCachePrepStmts(true);
			source.setPreparedStatementCacheSize(128);
			source.setPreparedStatementCacheSqlLimit(1024);
			conn = source.getConnection();
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return conn;
	}

	public static void main(String[] args) {
		
		if (args.length != 5) {
			System.out.println("Error: parameters error");
			System.out.println("The first parameter is mysql host");
			System.out.println("The second parameter is mysql port");
			System.out.println("The third parameter is mysql dbname");
			System.out.println("The fourth parameter is mysql user");
			System.out.println("The fifth parameter is mysql password");
			System.out.println("Like this: java -jar test.jar 127.0.0.1 3306 dbname user password");
			System.exit(1);
		}

		String host = args[0].trim();
		String port = args[1].trim();
		String dbname = args[2].trim();
		String user = args[3].trim();
		String password = args[4].trim();

		PreparedStatement ps = null;
		ResultSet rs = null;
		Connection con = null;

		String sql = "SELECT IF ( time = DATE_FORMAT(DATE_ADD(now(), INTERVAL - 1 DAY),'%Y-%m-%d'),1,0)  isContinuity,"
				+ "DATE_FORMAT(DATE_ADD(NOW(), INTERVAL - 1 DAY),'%Y-%m-%d') as day_yesterday,DATE_ADD(NOW(), INTERVAL - 1 DAY) day_yesterdateimte,NOW() as day_now"
				+ "  FROM hhz_user_sign WHERE user_id = ? limit 1";
		try {
			con = JDBCTest.getConnection(host, port, dbname, user, password);
			ps = con.prepareStatement(sql);
		        ps.setLong(1, 451720);
			rs = ps.executeQuery();
			while (rs.next()) {
				
				System.out.println("isContinuity=> " + rs.getString("isContinuity"));
				System.out.println("day_yesterday=> " + rs.getString("day_yesterday"));
				System.out.println("day_yesterdateimte=> " + rs.getString("day_yesterdateimte"));
				System.out.println("day_now=> " + rs.getString("day_now"));
				
			}
			ps.close();
			rs.close();
			System.out.println("===================================");
			try {
				Thread.sleep(10 * 1000);
			} catch (InterruptedException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
			ps = con.prepareStatement(sql);
			ps.setLong(1, 451720);
			rs = ps.executeQuery();
			while (rs.next()) {
				
				System.out.println("isContinuity=> " + rs.getString("isContinuity"));
				System.out.println("day_yesterday=> " + rs.getString("day_yesterday"));
				System.out.println("day_yesterdateimte=> " + rs.getString("day_yesterdateimte"));
				System.out.println("day_now=> " + rs.getString("day_now"));
				
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			try {
				ps.close();
				rs.close();
				con.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}

		}
	}
}
[14 Dec 2018 8:02] huiping wu
HI:
Already uploaded to java program to sftp:sftp.oracle.com:/support/incoming/mysql-bug-data-93606.tar.gz
[9 Jan 12:15] Umesh Shastry
Hello huiping wu,

Thank you for the report and test case.
Verified as described with provided jar.
Could you please confirm exact C/J version used in your environment? Thank you.

regards,
Umesh
[10 Jan 6:54] huiping wu
hello Umesh Shastry!

The C/J version used in the environment is mysql-connector-java-5.1.47.jar.

thanks.