我们有一个基于Java的程序,其中包含数十万行代码,这些代码在过去的8-9年之前以及在MySQL 5.5之前都可以正常运行。一个客户已经安装了mysql 5.6.17,现在我们面临一个大问题:日期时间值变为0000-00-00 00:00:00这是表格之一:如您所见,默认值为Null:
+-------+-------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+----------------+
| Id | int(11) | NO | PRI | NULL | auto_increment |
| Key1 | varchar(10) | NO | MUL | NULL | |
| Key2 | varchar(25) | NO | | NULL | |
| Date | datetime | YES | | NULL | |
| Value | text | NO | | NULL | |
然后通过mysql-connector在Java代码中插入以下代码:
String sql = "INSERT INTO DATA_SUNDRYMATRIX (Key1, Key2, Date, Value) VALUES(?,?,?,?)";
PreparedStatement p = c.prepareStatement(sql,Statement.RETURN_GENERATED_KEYS);
p.setString(1, i.getKey1());
p.setString(2, i.getKey2());
p.setTimestamp(3, i.getDate()==null?null:new Timestamp(i.getDate().getTime()));
p.setString(4, i.getValue());
p.executeUpdate();
我可以在Preparedstatement上将系统带出println,以便在executeupdate之前查看sql,它看起来像这样:
com.mysql.jdbc.ServerPreparedStatement[1] - INSERT INTO DATA_SUNDRYMATRIX (Key1, Key2, Date, Value) VALUES('TEST2','','2014-08-04 14:46:15','')
但是结果仍然是表中的'0000-00-00 00:00:00。这是为什么??真正奇怪的是,如果我将插入查询复制/粘贴到mysql控制台,则会正确设置datetime值。我试过升级到mysql-connector-java-5.1.31-bin.jar,但是没有运气。SELECT @@ sql_mode; 给出了NO_ENGINE_SUBSTITUTION A,由于大量的旧表和代码,我们无法将其设置为严格模式。我不认为?zeroDateTimeBehavior = convertToNull可以提供帮助,因为我们不希望空值,而是想要实际值,即使表的默认值为NULL,它们也将以某种方式转换为0000-00-00。 (顺便说一下,更新也会发生同样的事情)这个mysql设置到底有什么问题?
编辑:我有一些更多的信息。我现在用以下代码扩展了我的代码:
System.out.println(p);
p.executeUpdate();
SQLWarning warning = p.getWarnings();
while (warning != null){
System.out.println("ToString(): "+warning.toString());
System.out.println("ErrorCode: "+warning.getErrorCode());
System.out.println("LocalizedMessage: "+warning.getLocalizedMessage());
System.out.println("State: "+warning.getSQLState());
warning = warning.getNextWarning();
}
输出为:
com.mysql.jdbc.ServerPreparedStatement[1] - INSERT INTO DATA_SUNDRYMATRIX (Key1, Key2, Date, Value) VALUES('TEST2','','2014-08-04 15:35:02','')
ToString(): java.sql.SQLWarning: Data truncated for column 'Date' at row 1
ErrorCode: 1265
LocalizedMessage: Data truncated for column 'Date' at row 1
State: 01000
因此,即使我正确使用了preparedstatements setTimeStamp(就像我们尝试过的所有其他mysql版本一样),我实际上还是收到了数据截断警告,并且在执行PreparedStatement对象之前打印该sql时看起来还不错。为什么那么会导致数据被截断呢?
EDIT#2我被告知尝试使用setDate而不是setTimestamp来查看是否给出相同的错误。它没有,但是我不能使用setDate,因为那时我没有得到DATETIME的时间部分,而只有日期。正如我所说,PreparedStatement的System.out.println给出“ INSERT INTO DATA_SUNDRYMATRIX(Key1,Key2,Date,Value)VALUES('TEST2','','2014-08-04 15:35:02', ”)”是有效的sql,将其复制到mysql控制台时不会出现警告和截断的情况。我现在也通过以下代码直接尝试了该sql:
String sql = "INSERT INTO DATA_SUNDRYMATRIX (Key1, Key2, Date, Value) VALUES('TEST2','','2014-08-04 15:35:02','')";
PreparedStatement p = c.prepareStatement(sql,Statement.RETURN_GENERATED_KEYS);
p.executeUpdate();
这也可以给出正确的结果,而不会发出警告和截断。仅当我在PreparedStatement上使用setTimestamp时才会发生截断。我已经尝试使用旧版本和新版本的jconnector 3.1.10、5.1.18和5.1.31。所有版本均可在我的旧mysql版本上正常使用,并在mysql 5.6.17中产生此特殊错误。
EDIT#3我注意到setTimestamp(1,new Timestamp(0)); 没有截断,但给出了1970年1月的正确日期。因此,我做了一些测试代码:
//CREATE TABLE `test` (Id BIGINT NOT NULL AUTO_INCREMENT PRIMARY KEY, `Test` DATETIME NULL DEFAULT NULL);
String sql = "INSERT INTO Test (Id, Test) VALUES (?,?)";
PreparedStatement p = c.prepareStatement(sql,Statement.RETURN_GENERATED_KEYS);
long current = System.currentTimeMillis();
for (long t = 0; t < current; t += (long)100000000){
p.setLong(1,t);
p.setTimestamp(2, new Timestamp(t));
p.executeUpdate();
}
p.close();
p = c.prepareStatement(sql,Statement.RETURN_GENERATED_KEYS);
p.setLong(1,current);
p.setTimestamp(2, new Timestamp(current));
p.executeUpdate();
p.close();
这只会给出一个不正确的结果,最后一个。SELECT * FROM test提供以下最后一行:
| Id | Test |
+---------------+---------------------+
....
| 1407000000000 | 2014-08-02 19:20:00 |
| 1407100000000 | 2014-08-03 23:06:40 |
| 1407200000000 | 2014-08-05 02:53:20 |
| 1407224107714 | 0000-00-00 00:00:00 |
EDIT#4似乎存在一个错误,即在设置setTimestamp时,如果时间戳中存在亚秒级精度,则整个日期时间都会被破坏,而不是截断纳秒。如果执行以下操作,似乎可以正常工作:
p.setTimestamp(2, new Timestamp(current/1000*1000));
然后我将得到结果
| 1407225888000 | 2014-08-05 10:04:48 |
我可能会很快将其写为解决方案。但是我将不得不在我们的代码中修复很多setTimestamp值。在5.6.17之后,谁能确认这是否仍是mysql数据库中的错误?
EDIT#5(解决方案)useServerPrepStmts = false的构造方法建议解决了该问题!这是我发现的我自己的/ 1000 * 1000-解决方案更好的解决方案,因为我只需要更改连接String即可,而不必每次调用setTimestamp-call(好吧,我已经在最后一个小时准备好了,但是很高兴能也是'真正的'解决方案;-))
由于默认情况下从Connector / J 5.0.5开始禁用服务器端准备好的语句,所以很可能与MySQL 5.6.4中引入的分数秒支持相结合会引起您的问题。
尝试添加useServerPrepStmts=false
到您的连接字符串中(这也应该确保,它System.out.println
确实打印了已执行的SQL,而不仅仅是打印近似值)。
本文收集自互联网,转载请注明来源。
如有侵权,请联系 [email protected] 删除。
我来说两句