From: Date: December 6 2006 10:48pm Subject: Connector/J commit: r6127 - branches/branch_5_0/connector-j branches/branch_5_0/connector-j/src/com/mysql/jdbc branches/branch_5_0/connector-j/src/testsuite/regression trunk/connector-j trunk/connector-j/src/com/mysql/jdbc trunk/connector-j/src/testsuite/regression List-Archive: http://lists.mysql.com/commits/16553 X-Bug: 24344 Message-Id: <200612062148.kB6Lmh9m000325@bk-internal.mysql.com> Content-Type: text/plain; charset=UTF-8 Content-Transfer-Encoding: 8bit Modified: branches/branch_5_0/connector-j/CHANGES branches/branch_5_0/connector-j/src/com/mysql/jdbc/ConnectionProperties.java branches/branch_5_0/connector-j/src/com/mysql/jdbc/PreparedStatement.java branches/branch_5_0/connector-j/src/testsuite/regression/StatementRegressionTest.java trunk/connector-j/CHANGES trunk/connector-j/src/com/mysql/jdbc/ConnectionProperties.java trunk/connector-j/src/com/mysql/jdbc/PreparedStatement.java trunk/connector-j/src/testsuite/regression/StatementRegressionTest.java Log: Fixed BUG#24344 - useJDBCCompliantTimezoneShift with server-side prepared statements gives different behavior than when using client-side prepared statements. (this is now fixed if moving from server-side prepared statements to client-side prepared statements by setting "useSSPSCompatibleTimezoneShift" to true", as the driver can't tell if this is a new deployment that never used server-side prepared statements, or if it is an existing deployment that is switching to client-side prepared statements from server-side prepared statements. Modified: branches/branch_5_0/connector-j/CHANGES =================================================================== --- branches/branch_5_0/connector-j/CHANGES 2006-12-06 01:01:36 UTC (rev 6126) +++ branches/branch_5_0/connector-j/CHANGES 2006-12-06 21:48:40 UTC (rev 6127) @@ -16,7 +16,16 @@ columns not referenced in them. - Fixed BUG#24360 .setFetchSize() breaks prepared SHOW and other commands. - + + + - Fixed BUG#24344 - useJDBCCompliantTimezoneShift with server-side prepared + statements gives different behavior than when using client-side prepared + statements. (this is now fixed if moving from server-side prepared statements + to client-side prepared statements by setting "useSSPSCompatibleTimezoneShift" to + true", as the driver can't tell if this is a new deployment that never used + server-side prepared statements, or if it is an existing deployment that is + switching to client-side prepared statements from server-side prepared statements. + 10-20-06 - Version 5.0.4 - Fixed BUG#21379 - column names don't match metadata in cases Modified: branches/branch_5_0/connector-j/src/com/mysql/jdbc/ConnectionProperties.java =================================================================== --- branches/branch_5_0/connector-j/src/com/mysql/jdbc/ConnectionProperties.java 2006-12-06 01:01:36 UTC (rev 6126) +++ branches/branch_5_0/connector-j/src/com/mysql/jdbc/ConnectionProperties.java 2006-12-06 21:48:40 UTC (rev 6127) @@ -1354,6 +1354,14 @@ "Use SSL when communicating with the server (true/false), defaults to 'false'", "3.0.2", SECURITY_CATEGORY, 2); + private BooleanConnectionProperty useSSPSCompatibleTimezoneShift = new BooleanConnectionProperty( + "useSSPSCompatibleTimezoneShift", + false, + "If migrating from an environment that was using server-side prepared statements, and the" + + " configuration property \"useJDBCCompliantTimeZoneShift\" set to \"true\", use compatible behavior" + + " when not using server-side prepared statements when sending TIMESTAMP values to the MySQL server.", + "5.0.5", MISC_CATEGORY, Integer.MIN_VALUE); + private BooleanConnectionProperty useStreamLengthsInPrepStmts = new BooleanConnectionProperty( "useStreamLengthsInPrepStmts", true, @@ -3717,4 +3725,12 @@ public void setUseOldAliasMetadataBehavior(boolean flag) { this.useOldAliasMetadataBehavior.setValue(flag); } + + public boolean getUseSSPSCompatibleTimezoneShift() { + return this.useSSPSCompatibleTimezoneShift.getValueAsBoolean(); + } + + public void setUseSSPSCompatibleTimezoneShift(boolean flag) { + this.useSSPSCompatibleTimezoneShift.setValue(flag); + } } Modified: branches/branch_5_0/connector-j/src/com/mysql/jdbc/PreparedStatement.java =================================================================== --- branches/branch_5_0/connector-j/src/com/mysql/jdbc/PreparedStatement.java 2006-12-06 01:01:36 UTC (rev 6126) +++ branches/branch_5_0/connector-j/src/com/mysql/jdbc/PreparedStatement.java 2006-12-06 21:48:40 UTC (rev 6127) @@ -3543,15 +3543,95 @@ .getServerTimezoneTZ(), rollForward); } - if (this.tsdf == null) { - this.tsdf = new SimpleDateFormat("''yyyy-MM-dd HH:mm:ss''", Locale.US); //$NON-NLS-1$ + + if (this.connection.getUseSSPSCompatibleTimezoneShift()) { + doSSPSCompatibleTimezoneShift(parameterIndex, x, sessionCalendar); + } else { + + if (this.tsdf == null) { + this.tsdf = new SimpleDateFormat("''yyyy-MM-dd HH:mm:ss''", Locale.US); //$NON-NLS-1$ + } + + timestampString = this.tsdf.format(x); + + setInternal(parameterIndex, timestampString); // SimpleDateFormat is not + // thread-safe } + } + } - timestampString = this.tsdf.format(x); + private void doSSPSCompatibleTimezoneShift(int parameterIndex, Timestamp x, Calendar sessionCalendar) throws SQLException { + Calendar sessionCalendar2 = (this.connection + .getUseJDBCCompliantTimezoneShift()) ? this.connection + .getUtcCalendar() + : getCalendarInstanceForSessionOrNew(); - setInternal(parameterIndex, timestampString); // SimpleDateFormat - // is not - // thread-safe + synchronized (sessionCalendar2) { + java.util.Date oldTime = sessionCalendar2.getTime(); + + try { + sessionCalendar2.setTime(x); + + int year = sessionCalendar2.get(Calendar.YEAR); + int month = sessionCalendar2.get(Calendar.MONTH) + 1; + int date = sessionCalendar2.get(Calendar.DAY_OF_MONTH); + + int hour = sessionCalendar2.get(Calendar.HOUR_OF_DAY); + int minute = sessionCalendar2.get(Calendar.MINUTE); + int seconds = sessionCalendar2.get(Calendar.SECOND); + + StringBuffer tsBuf = new StringBuffer(); + + tsBuf.append('\''); + tsBuf.append(year); + + tsBuf.append("-"); + + if (month < 10) { + tsBuf.append('0'); + } + + tsBuf.append(month); + + tsBuf.append('-'); + + if (date < 10) { + tsBuf.append('0'); + } + + tsBuf.append(date); + + tsBuf.append(' '); + + if (hour < 10) { + tsBuf.append('0'); + } + + tsBuf.append(hour); + + tsBuf.append(':'); + + if (minute < 10) { + tsBuf.append('0'); + } + + tsBuf.append(minute); + + tsBuf.append(':'); + + if (seconds < 10) { + tsBuf.append('0'); + } + + tsBuf.append(seconds); + + tsBuf.append('\''); + + setInternal(parameterIndex, tsBuf.toString()); + + } finally { + sessionCalendar.setTime(oldTime); + } } } Modified: branches/branch_5_0/connector-j/src/testsuite/regression/StatementRegressionTest.java =================================================================== --- branches/branch_5_0/connector-j/src/testsuite/regression/StatementRegressionTest.java 2006-12-06 01:01:36 UTC (rev 6126) +++ branches/branch_5_0/connector-j/src/testsuite/regression/StatementRegressionTest.java 2006-12-06 21:48:40 UTC (rev 6127) @@ -3525,4 +3525,77 @@ } } } + + /** + * Tests fix for BUG#24344 - useJDBCCompliantTimezoneShift with server-side prepared + * statements gives different behavior than when using client-side prepared + * statements. (this is now fixed if moving from server-side prepared statements + * to client-side prepared statements by setting "useSSPSCompatibleTimezoneShift" to + * "true", as the driver can't tell if this is a new deployment that never used + * server-side prepared statements, or if it is an existing deployment that is + * switching to client-side prepared statements from server-side prepared statements. + * + * @throws Exception if the test fails + */ + public void testBug24344() throws Exception { + + super.createTable("testBug24344", + "(i INT AUTO_INCREMENT, t1 DATETIME, PRIMARY KEY (i)) ENGINE = MyISAM"); + + Connection conn2 = null; + + try { + Properties props = new Properties(); + props.setProperty("useServerPrepStmts", "true"); + props.setProperty("useJDBCCompliantTimezoneShift", "true"); + conn2 = super.getConnectionWithProps(props); + this.pstmt = conn2.prepareStatement("INSERT INTO testBug24344 (t1) VALUES (?)"); + Calendar c = Calendar.getInstance(); + this.pstmt.setTimestamp(1, new Timestamp(c.getTimeInMillis())); + this.pstmt.execute(); + this.pstmt.close(); + conn2.close(); + + props.setProperty("useServerPrepStmts", "false"); + props.setProperty("useJDBCCompliantTimezoneShift", "true"); + props.setProperty("useSSPSCompatibleTimezoneShift", "true"); + + conn2 = super.getConnectionWithProps(props); + this.pstmt = conn2.prepareStatement("INSERT INTO testBug24344 (t1) VALUES (?)"); + this.pstmt.setTimestamp(1, new Timestamp(c.getTimeInMillis())); + this.pstmt.execute(); + this.pstmt.close(); + conn2.close(); + + props.setProperty("useServerPrepStmts", "false"); + props.setProperty("useJDBCCompliantTimezoneShift", "false"); + props.setProperty("useSSPSCompatibleTimezoneShift", "false"); + conn2 = super.getConnectionWithProps(props); + this.pstmt = conn2.prepareStatement("INSERT INTO testBug24344 (t1) VALUES (?)"); + this.pstmt.setTimestamp(1, new Timestamp(c.getTimeInMillis())); + this.pstmt.execute(); + this.pstmt.close(); + + Statement s = conn2.createStatement(); + this.rs = s.executeQuery("SELECT t1 FROM testBug24344 ORDER BY i ASC"); + + Timestamp[] dates = new Timestamp[3]; + + int i = 0; + + while(rs.next()){ + dates[i++] = rs.getTimestamp(1); + } + + assertEquals( "Number of rows should be 3.", 3, i); + assertEquals(dates[0], dates[1]); + assertTrue(!dates[1].equals(dates[2])); + } finally { + closeMemberJDBCResources(); + + if (conn2 != null) { + conn2.close(); + } + } + } } Modified: trunk/connector-j/CHANGES =================================================================== --- trunk/connector-j/CHANGES 2006-12-06 01:01:36 UTC (rev 6126) +++ trunk/connector-j/CHANGES 2006-12-06 21:48:40 UTC (rev 6127) @@ -13,7 +13,15 @@ what is required. - Fixed BUG#24360 .setFetchSize() breaks prepared SHOW and other commands. - + + - Fixed BUG#24344 - useJDBCCompliantTimezoneShift with server-side prepared + statements gives different behavior than when using client-side prepared + statements. (this is now fixed if moving from server-side prepared statements + to client-side prepared statements by setting "useSSPSCompatibleTimezoneShift" to + true", as the driver can't tell if this is a new deployment that never used + server-side prepared statements, or if it is an existing deployment that is + switching to client-side prepared statements from server-side prepared statements. + 10-20-06 - Version 5.0.4 - Fixed BUG#21379 - column names don't match metadata in cases Modified: trunk/connector-j/src/com/mysql/jdbc/ConnectionProperties.java =================================================================== --- trunk/connector-j/src/com/mysql/jdbc/ConnectionProperties.java 2006-12-06 01:01:36 UTC (rev 6126) +++ trunk/connector-j/src/com/mysql/jdbc/ConnectionProperties.java 2006-12-06 21:48:40 UTC (rev 6127) @@ -1357,6 +1357,14 @@ "Use SSL when communicating with the server (true/false), defaults to 'false'", "3.0.2", SECURITY_CATEGORY, 2); + private BooleanConnectionProperty useSSPSCompatibleTimezoneShift = new BooleanConnectionProperty( + "useSSPSCompatibleTimezoneShift", + false, + "If migrating from an environment that was using server-side prepared statements, and the" + + " configuration property \"useJDBCCompliantTimeZoneShift\" set to \"true\", use compatible behavior" + + " when not using server-side prepared statements when sending TIMESTAMP values to the MySQL server.", + "5.0.5", MISC_CATEGORY, Integer.MIN_VALUE); + private BooleanConnectionProperty useStreamLengthsInPrepStmts = new BooleanConnectionProperty( "useStreamLengthsInPrepStmts", true, @@ -3812,4 +3820,12 @@ String value) { this.trustCertificateKeyStoreUrl.setValue(value); } + + public boolean getUseSSPSCompatibleTimezoneShift() { + return this.useSSPSCompatibleTimezoneShift.getValueAsBoolean(); + } + + public void setUseSSPSCompatibleTimezoneShift(boolean flag) { + this.useSSPSCompatibleTimezoneShift.setValue(flag); + } } Modified: trunk/connector-j/src/com/mysql/jdbc/PreparedStatement.java =================================================================== --- trunk/connector-j/src/com/mysql/jdbc/PreparedStatement.java 2006-12-06 01:01:36 UTC (rev 6126) +++ trunk/connector-j/src/com/mysql/jdbc/PreparedStatement.java 2006-12-06 21:48:40 UTC (rev 6127) @@ -3774,15 +3774,94 @@ .getServerTimezoneTZ(), rollForward); } - if (this.tsdf == null) { - this.tsdf = new SimpleDateFormat("''yyyy-MM-dd HH:mm:ss''", Locale.US); //$NON-NLS-1$ + if (this.connection.getUseSSPSCompatibleTimezoneShift()) { + doSSPSCompatibleTimezoneShift(parameterIndex, x, sessionCalendar); + } else { + + if (this.tsdf == null) { + this.tsdf = new SimpleDateFormat("''yyyy-MM-dd HH:mm:ss''", Locale.US); //$NON-NLS-1$ + } + + timestampString = this.tsdf.format(x); + + setInternal(parameterIndex, timestampString); // SimpleDateFormat is not + // thread-safe } + } + } - timestampString = this.tsdf.format(x); + private void doSSPSCompatibleTimezoneShift(int parameterIndex, Timestamp x, Calendar sessionCalendar) throws SQLException { + Calendar sessionCalendar2 = (this.connection + .getUseJDBCCompliantTimezoneShift()) ? this.connection + .getUtcCalendar() + : getCalendarInstanceForSessionOrNew(); - setInternal(parameterIndex, timestampString); // SimpleDateFormat - // is not - // thread-safe + synchronized (sessionCalendar2) { + java.util.Date oldTime = sessionCalendar2.getTime(); + + try { + sessionCalendar2.setTime(x); + + int year = sessionCalendar2.get(Calendar.YEAR); + int month = sessionCalendar2.get(Calendar.MONTH) + 1; + int date = sessionCalendar2.get(Calendar.DAY_OF_MONTH); + + int hour = sessionCalendar2.get(Calendar.HOUR_OF_DAY); + int minute = sessionCalendar2.get(Calendar.MINUTE); + int seconds = sessionCalendar2.get(Calendar.SECOND); + + StringBuffer tsBuf = new StringBuffer(); + + tsBuf.append('\''); + tsBuf.append(year); + + tsBuf.append("-"); + + if (month < 10) { + tsBuf.append('0'); + } + + tsBuf.append(month); + + tsBuf.append('-'); + + if (date < 10) { + tsBuf.append('0'); + } + + tsBuf.append(date); + + tsBuf.append(' '); + + if (hour < 10) { + tsBuf.append('0'); + } + + tsBuf.append(hour); + + tsBuf.append(':'); + + if (minute < 10) { + tsBuf.append('0'); + } + + tsBuf.append(minute); + + tsBuf.append(':'); + + if (seconds < 10) { + tsBuf.append('0'); + } + + tsBuf.append(seconds); + + tsBuf.append('\''); + + setInternal(parameterIndex, tsBuf.toString()); + + } finally { + sessionCalendar.setTime(oldTime); + } } } Modified: trunk/connector-j/src/testsuite/regression/StatementRegressionTest.java =================================================================== --- trunk/connector-j/src/testsuite/regression/StatementRegressionTest.java 2006-12-06 01:01:36 UTC (rev 6126) +++ trunk/connector-j/src/testsuite/regression/StatementRegressionTest.java 2006-12-06 21:48:40 UTC (rev 6127) @@ -3529,4 +3529,77 @@ } } } + + /** + * Tests fix for BUG#24344 - useJDBCCompliantTimezoneShift with server-side prepared + * statements gives different behavior than when using client-side prepared + * statements. (this is now fixed if moving from server-side prepared statements + * to client-side prepared statements by setting "useSSPSCompatibleTimezoneShift" to + * "true", as the driver can't tell if this is a new deployment that never used + * server-side prepared statements, or if it is an existing deployment that is + * switching to client-side prepared statements from server-side prepared statements. + * + * @throws Exception if the test fails + */ + public void testBug24344() throws Exception { + + super.createTable("testBug24344", + "(i INT AUTO_INCREMENT, t1 DATETIME, PRIMARY KEY (i)) ENGINE = MyISAM"); + + Connection conn2 = null; + + try { + Properties props = new Properties(); + props.setProperty("useServerPrepStmts", "true"); + props.setProperty("useJDBCCompliantTimezoneShift", "true"); + conn2 = super.getConnectionWithProps(props); + this.pstmt = conn2.prepareStatement("INSERT INTO testBug24344 (t1) VALUES (?)"); + Calendar c = Calendar.getInstance(); + this.pstmt.setTimestamp(1, new Timestamp(c.getTimeInMillis())); + this.pstmt.execute(); + this.pstmt.close(); + conn2.close(); + + props.setProperty("useServerPrepStmts", "false"); + props.setProperty("useJDBCCompliantTimezoneShift", "true"); + props.setProperty("useSSPSCompatibleTimezoneShift", "true"); + + conn2 = super.getConnectionWithProps(props); + this.pstmt = conn2.prepareStatement("INSERT INTO testBug24344 (t1) VALUES (?)"); + this.pstmt.setTimestamp(1, new Timestamp(c.getTimeInMillis())); + this.pstmt.execute(); + this.pstmt.close(); + conn2.close(); + + props.setProperty("useServerPrepStmts", "false"); + props.setProperty("useJDBCCompliantTimezoneShift", "false"); + props.setProperty("useSSPSCompatibleTimezoneShift", "false"); + conn2 = super.getConnectionWithProps(props); + this.pstmt = conn2.prepareStatement("INSERT INTO testBug24344 (t1) VALUES (?)"); + this.pstmt.setTimestamp(1, new Timestamp(c.getTimeInMillis())); + this.pstmt.execute(); + this.pstmt.close(); + + Statement s = conn2.createStatement(); + this.rs = s.executeQuery("SELECT t1 FROM testBug24344 ORDER BY i ASC"); + + Timestamp[] dates = new Timestamp[3]; + + int i = 0; + + while(rs.next()){ + dates[i++] = rs.getTimestamp(1); + } + + assertEquals( "Number of rows should be 3.", 3, i); + assertEquals(dates[0], dates[1]); + assertTrue(!dates[1].equals(dates[2])); + } finally { + closeMemberJDBCResources(); + + if (conn2 != null) { + conn2.close(); + } + } + } }