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.19
I found some discussions on the web from around 2016. But it just
said to update the connector and TC. My versions are already way
past 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.
Jerry
https://dev.mysql.com/doc/refman/5.7/en/datetime.html
When you paste from the logs, you're not pasting what the original
INSERT command is doing. Therefore, it will work, since the error
message is giving the minimum date back that is supported by MySQL.
There 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...
-chris
---------------------------------------------------------------------
To unsubscribe, e-mail: users-unsubscr...@tomcat.apache.org
For additional commands, e-mail: users-h...@tomcat.apache.org
