On 8/27/2021 11:16 AM, Jerry Malcolm wrote:
On 8/27/2021 11:55 AM, Christopher Schultz wrote:Chris, thanks for the info. Why timestamp? Unfortunately, some of this code was written 20+ years ago when I was a lot less knowledgeable... But too difficult to change now.Mark and Jerry, On 8/26/21 22:03, Mark Eggers wrote:Jerry, On 8/26/2021 6:35 PM, Jerry Malcolm wrote:I am encountering a weird problem. I'm getting the following SQL error on an INSERT command.com.mysql.cj.jdbc.exceptions.MysqlDataTruncation: Data truncation: Incorrect datetime value: '1969-12-31 18:00:00.0' for column...The column is a TIMESTAMP in mySQL.I pasted the SQL statement directly out of my log into phpMyAdmin, and it worked. When I change the date to '2021-08-27 01:03:18.1077537'it also works.I tried it on my production AWS server. The server timezone was different but same failure with '1970-01-01 00:00:00.0'I'm running Win10 with latest updates (AWS Linux 2 on production) TC 9.0.16 mysql-connector-java-8.0.26.jar mysql5.7.19I found some discussions on the web from around 2016. But it just said to update the connector and TC. My versions are already waypast 2016 versions.My biggest concern is that some dates work and some don't. If I have to avoid dates that fail, I can probably do that. But right now, I don't know what dates are going to work and what dates are going to fail.Am I missing something obvious? I've never had a SQL statement that failed consistently on TC but worked when pasted into phpMyAdmin.Suggestions? Thanks. JerryThere is a setting in the driver called something like "null means zero datetime" which may confuse the heck out of TIMESTAMP columns, which expect a UNIX-epoch timestamp value.The datetime value '1969-12-31 18:00:00.0' you may recognize as the start of the UNIX Epoch minus 6 hours, which suggests to me that your system is running in Us-Mountain Time, 6 hours behind UTC in the summer.I would bet that you are trying to insert a NULL into a TIMESTAMP, and that your driver is using MDT as your time zone, trying to convert NULL -> 1970-01-01 00:00:00 UTC -> 1969-12-31 18:00:00 MDT -> boom, since the minimum allowed TIMESTAMP value is 1970-01-01 00:00:00.Might I ask why you are using a TIMESTAMP field? IMHO they aren't good for much...-chrisI'm not inserting nulls. Always a quoted date/time string.You are correct about the timezone. That's on my dev laptop, and I never got around to setting the timezone stuff correctly on my my dev machine. However, my production server (Linux) does have the timezones all set correctly. My insert statement has a value of "new Timestamp(0).toString()". On the production server, this becomes '1970-01-01 00:00:00.0' and it still fails on production.Is the jdbc driver enforcing the minimum timestamp value? mySQL accepts 1969-12-31 18:00:00.0 in the insert statement. mySQL may be adjusting the time +6 on my laptop back up the epoch value before storing it. But the situation still remains that the same insert statement works on phpMyAdmin and fails on TC.The timezone thing is just adding unnecessary complexity to the problem. The production server fails on TC with '1970-01-01 00:00:00.0' in the insert statement, but works with that value when inserted into mySQL pasting the insert statement into phpMyAdmin.The exception is com.mysql.cj.jdbc.exceptions.MysqlDataTruncation. Is the driver detecting this and generating the exception? Or does the insert statement get all the way to mySQL and mySQL fails back to the driver followed by the driver throwing the exception?Jerry --------------------------------------------------------------------- To unsubscribe, e-mail: users-unsubscr...@tomcat.apache.org For additional commands, e-mail: users-h...@tomcat.apache.org
https://docs.oracle.com/javase/8/docs/api/java/sql/Timestamp.html See the constructor: public Timestamp(long time) . . . just my two cents /mde/
OpenPGP_signature
Description: OpenPGP digital signature
