Description:
Environment Linux Redhat
java 1.4.2_10, mysql server 4.1.11
My code stops working correctly when I've upgraded to mysql connector 3.1.8,the result is even worse with 3.1.13. The problem is described below, java code is provided in #4.
The problem occurrs only if PreparedStatment setTimestamp method is used, otherwise everything works fine (#5 contains working code for all three connectors)
How to repeat:
1. Create and populate table
Table:
mysql create table test_table( t_id int(10), test_date timestamp(30) NOT NULL,
primary key t_pk (t_id) );
mysql> describe test_table;
+-----------+-----------+------+-----+-------------------+-------+
| Field | Type | Null | Key | Default | Extra |
+-----------+-----------+------+-----+-------------------+-------+
| t_id | int(10) | | PRI | 0 | |
| test_date | timestamp | YES | | CURRENT_TIMESTAMP | |
+-----------+-----------+------+-----+-------------------+-------+
2 rows in set (0.00 sec)
insert into test_table values (1,NOW());
mysql> select * from test_table;
+------+---------------------+
| t_id | test_date |
+------+---------------------+
| 1 | 2007-08-03 23:28:01 |
+------+---------------------+
1 row in set (0.00 sec)
2. Java MysqlTest1.java is compiled and ran with 3.0.11 connector produces the correct results:
output:
executedQuery: com.mysql.jdbc.PreparedStatement@60420f: UPDATE test_table SET te
st_date=ADDDATE('2006-08-03 23:33:51',INTERVAL 1 YEAR) WHERE t_id=1
committing
closing connections
in database:
mysql> select * from test_table;
+------+---------------------+
| t_id | test_date |
+------+---------------------+
| 1 | 2007-08-03 23:33:51 |
+------+---------------------+
1 row in set (0.00 sec)
3. Java MysqlTest1.java is compiled and ran with 3.1.8 connector does not generate any error but produces wrong results
output:
executedQuery: com.mysql.jdbc.ServerPreparedStatement[1] - UPDATE test_table SET
test_date=ADDDATE('2006-08-03 23:35:40',INTERVAL 1 YEAR) WHERE t_id=1
committing
closing connections
but in database:
mysql> select * from test_table;
+------+---------------------+
| t_id | test_date |
+------+---------------------+
| 1 | 0000-00-00 00:00:00 |
+------+---------------------+
4.Java MysqlTest1.java is compiled and ran with 3.1.13 connector throws MysqlDataTruncation
output:
java MysqlTest1
executedQuery: com.mysql.jdbc.ServerPreparedStatement[1] - UPDATE test_table SET
test_date=ADDDATE('2006-08-03 23:38:18',INTERVAL 1 YEAR) WHERE t_id=1
com.mysql.jdbc.MysqlDataTruncation: Data truncation: Data truncated for column '
test_date' at row 1
at com.mysql.jdbc.SQLError.convertShowWarningsToSQLWarnings(SQLError.jav
a:709)
at com.mysql.jdbc.MysqlIO.scanForAndThrowDataTruncation(MysqlIO.java:346
1)
at com.mysql.jdbc.ServerPreparedStatement.serverExecute(ServerPreparedSt
atement.java:1198)
at com.mysql.jdbc.ServerPreparedStatement.executeInternal(ServerPrepared
Statement.java:677)
at com.mysql.jdbc.PreparedStatement.executeUpdate(PreparedStatement.java
:1357)
at com.mysql.jdbc.PreparedStatement.executeUpdate(PreparedStatement.java
:1274)
at com.mysql.jdbc.PreparedStatement.executeUpdate(PreparedStatement.java
:1259)
at MysqlTest1.execute(MysqlTest1.java:29)
at MysqlTest1.main(MysqlTest1.java:52)
Data truncation: Data truncated for column 'test_date' at row 1
rollback
5. Java code (MysqlTest1.java):
*************************************
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.Timestamp;
import java.util.Date;
public class MysqlTest1 {
private Connection connection=null;
public MysqlTest1() throws Exception{
String username="user"; //change it !!
String password="password"; //change it !!!
String url="jdbc:mysql://localhost.localdomain:49151/test";
String driver="org.gjt.mm.mysql.Driver";
Class.forName(driver);
connection=DriverManager.getConnection(url,username,password);
connection.setAutoCommit (false);
}
public void execute(String message) throws Exception{
PreparedStatement stmt = connection.prepareStatement(message);
stmt.setTimestamp(1,new Timestamp(new Date().getTime()));
System.out.println("executedQuery: "+stmt.toString());
stmt.executeUpdate();
stmt.close();
System.out.println("committing ");
connection.commit();
}
public void close() throws Exception{
connection.close();
System.out.println("closing connections");
}
public void rollback() throws Exception{
connection.rollback();
System.out.println("rollback ");
}
public static void main(String[] args) throws Exception{
MysqlTest1 test=null;
String sql="UPDATE test_table SET test_date=ADDDATE(?,INTERVAL 1
YEAR) WHERE t_id=1";
try {
test=new MysqlTest1();
test.execute(sql);
test.close();
} catch (Exception e) {
e.printStackTrace();
System.out.println(e.getMessage());
if (test!=null) {
test.rollback();
test.close();
}
}
}
}
*****************************************************************************
#5 working code for all 3 connectors:
*************************************
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.Timestamp;
import java.util.Date;
public class MysqlTest1 {
private Connection connection=null;
public MysqlTest1() throws Exception{
String username="user"; //change it !!
String password="password"; //change it !!!
String url="jdbc:mysql://localhost.localdomain:49151/test";
String driver="org.gjt.mm.mysql.Driver";
Class.forName(driver);
connection=DriverManager.getConnection(url,username,password);
connection.setAutoCommit (false);
}
public void execute(String message) throws Exception{
PreparedStatement stmt = connection.prepareStatement(message);
//stmt.setTimestamp(1,new Timestamp(new Date().getTime()));
//Commented out from #4
System.out.println("executedQuery: "+stmt.toString());
stmt.executeUpdate();
stmt.close();
System.out.println("committing ");
connection.commit();
}
public void close() throws Exception{
connection.close();
System.out.println("closing connections");
}
public void rollback() throws Exception{
connection.rollback();
System.out.println("rollback ");
}
public static void main(String[] args) throws Exception{
MysqlTest1 test=null;
//String sql="UPDATE test_table SET test_date=ADDDATE(?,INTERVAL 1 YEAR) WHERE t_id=1";
String sql="UPDATE test_table SET test_date=ADDDATE('"+
new Timestamp(new Date().getTime())+"',INTERVAL 1
YEAR) WHERE t_id=1";
try {
test=new MysqlTest1();
test.execute(sql);
test.close();
} catch (Exception e) {
e.printStackTrace();
System.out.println(e.getMessage());
if (test!=null) {
test.rollback();
test.close();
}
}
}
}
*****************************************************************************
Description: Environment Linux Redhat java 1.4.2_10, mysql server 4.1.11 My code stops working correctly when I've upgraded to mysql connector 3.1.8,the result is even worse with 3.1.13. The problem is described below, java code is provided in #4. The problem occurrs only if PreparedStatment setTimestamp method is used, otherwise everything works fine (#5 contains working code for all three connectors) How to repeat: 1. Create and populate table Table: mysql create table test_table( t_id int(10), test_date timestamp(30) NOT NULL, primary key t_pk (t_id) ); mysql> describe test_table; +-----------+-----------+------+-----+-------------------+-------+ | Field | Type | Null | Key | Default | Extra | +-----------+-----------+------+-----+-------------------+-------+ | t_id | int(10) | | PRI | 0 | | | test_date | timestamp | YES | | CURRENT_TIMESTAMP | | +-----------+-----------+------+-----+-------------------+-------+ 2 rows in set (0.00 sec) insert into test_table values (1,NOW()); mysql> select * from test_table; +------+---------------------+ | t_id | test_date | +------+---------------------+ | 1 | 2007-08-03 23:28:01 | +------+---------------------+ 1 row in set (0.00 sec) 2. Java MysqlTest1.java is compiled and ran with 3.0.11 connector produces the correct results: output: executedQuery: com.mysql.jdbc.PreparedStatement@60420f: UPDATE test_table SET te st_date=ADDDATE('2006-08-03 23:33:51',INTERVAL 1 YEAR) WHERE t_id=1 committing closing connections in database: mysql> select * from test_table; +------+---------------------+ | t_id | test_date | +------+---------------------+ | 1 | 2007-08-03 23:33:51 | +------+---------------------+ 1 row in set (0.00 sec) 3. Java MysqlTest1.java is compiled and ran with 3.1.8 connector does not generate any error but produces wrong results output: executedQuery: com.mysql.jdbc.ServerPreparedStatement[1] - UPDATE test_table SET test_date=ADDDATE('2006-08-03 23:35:40',INTERVAL 1 YEAR) WHERE t_id=1 committing closing connections but in database: mysql> select * from test_table; +------+---------------------+ | t_id | test_date | +------+---------------------+ | 1 | 0000-00-00 00:00:00 | +------+---------------------+ 4.Java MysqlTest1.java is compiled and ran with 3.1.13 connector throws MysqlDataTruncation output: java MysqlTest1 executedQuery: com.mysql.jdbc.ServerPreparedStatement[1] - UPDATE test_table SET test_date=ADDDATE('2006-08-03 23:38:18',INTERVAL 1 YEAR) WHERE t_id=1 com.mysql.jdbc.MysqlDataTruncation: Data truncation: Data truncated for column ' test_date' at row 1 at com.mysql.jdbc.SQLError.convertShowWarningsToSQLWarnings(SQLError.jav a:709) at com.mysql.jdbc.MysqlIO.scanForAndThrowDataTruncation(MysqlIO.java:346 1) at com.mysql.jdbc.ServerPreparedStatement.serverExecute(ServerPreparedSt atement.java:1198) at com.mysql.jdbc.ServerPreparedStatement.executeInternal(ServerPrepared Statement.java:677) at com.mysql.jdbc.PreparedStatement.executeUpdate(PreparedStatement.java :1357) at com.mysql.jdbc.PreparedStatement.executeUpdate(PreparedStatement.java :1274) at com.mysql.jdbc.PreparedStatement.executeUpdate(PreparedStatement.java :1259) at MysqlTest1.execute(MysqlTest1.java:29) at MysqlTest1.main(MysqlTest1.java:52) Data truncation: Data truncated for column 'test_date' at row 1 rollback 5. Java code (MysqlTest1.java): ************************************* import java.sql.Connection; import java.sql.DriverManager; import java.sql.PreparedStatement; import java.sql.Timestamp; import java.util.Date; public class MysqlTest1 { private Connection connection=null; public MysqlTest1() throws Exception{ String username="user"; //change it !! String password="password"; //change it !!! String url="jdbc:mysql://localhost.localdomain:49151/test"; String driver="org.gjt.mm.mysql.Driver"; Class.forName(driver); connection=DriverManager.getConnection(url,username,password); connection.setAutoCommit (false); } public void execute(String message) throws Exception{ PreparedStatement stmt = connection.prepareStatement(message); stmt.setTimestamp(1,new Timestamp(new Date().getTime())); System.out.println("executedQuery: "+stmt.toString()); stmt.executeUpdate(); stmt.close(); System.out.println("committing "); connection.commit(); } public void close() throws Exception{ connection.close(); System.out.println("closing connections"); } public void rollback() throws Exception{ connection.rollback(); System.out.println("rollback "); } public static void main(String[] args) throws Exception{ MysqlTest1 test=null; String sql="UPDATE test_table SET test_date=ADDDATE(?,INTERVAL 1 YEAR) WHERE t_id=1"; try { test=new MysqlTest1(); test.execute(sql); test.close(); } catch (Exception e) { e.printStackTrace(); System.out.println(e.getMessage()); if (test!=null) { test.rollback(); test.close(); } } } } ***************************************************************************** #5 working code for all 3 connectors: ************************************* import java.sql.Connection; import java.sql.DriverManager; import java.sql.PreparedStatement; import java.sql.Timestamp; import java.util.Date; public class MysqlTest1 { private Connection connection=null; public MysqlTest1() throws Exception{ String username="user"; //change it !! String password="password"; //change it !!! String url="jdbc:mysql://localhost.localdomain:49151/test"; String driver="org.gjt.mm.mysql.Driver"; Class.forName(driver); connection=DriverManager.getConnection(url,username,password); connection.setAutoCommit (false); } public void execute(String message) throws Exception{ PreparedStatement stmt = connection.prepareStatement(message); //stmt.setTimestamp(1,new Timestamp(new Date().getTime())); //Commented out from #4 System.out.println("executedQuery: "+stmt.toString()); stmt.executeUpdate(); stmt.close(); System.out.println("committing "); connection.commit(); } public void close() throws Exception{ connection.close(); System.out.println("closing connections"); } public void rollback() throws Exception{ connection.rollback(); System.out.println("rollback "); } public static void main(String[] args) throws Exception{ MysqlTest1 test=null; //String sql="UPDATE test_table SET test_date=ADDDATE(?,INTERVAL 1 YEAR) WHERE t_id=1"; String sql="UPDATE test_table SET test_date=ADDDATE('"+ new Timestamp(new Date().getTime())+"',INTERVAL 1 YEAR) WHERE t_id=1"; try { test=new MysqlTest1(); test.execute(sql); test.close(); } catch (Exception e) { e.printStackTrace(); System.out.println(e.getMessage()); if (test!=null) { test.rollback(); test.close(); } } } } *****************************************************************************