On 8/27/2021 1:30 PM, Mark Eggers wrote:
On 8/27/2021 11:16 AM, Jerry Malcolm wrote:
On 8/27/2021 11:55 AM, Christopher Schultz wrote:
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
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
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.
I'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/
|Timestamp
<https://docs.oracle.com/javase/8/docs/api/java/sql/Timestamp.html#Timestamp-long->(long time)|
Constructs a |Timestamp| object using a milliseconds time value.
|time| - milliseconds since January 1, 1970, 00:00:00 GMT. A negative
number is the number of milliseconds before January 1, 1970, 00:00:00 GMT.
This says that a timestamp can be before the epoch, no minimum time,
which agrees with what I'm seeing via phpMyAdmin. Which means that what
I'm providing in the sql insert statement should be accepted regardless
of timezone factors. Seems to me there's a bug in the TC driver (??)
And the error message I'm getting says "data truncation", which at best
is incorrect wording. Not sure how any truncation could occur on a date
string that parses to (long)0. I thought the .0 fractions of a second
on the end of the string could be the cause of 'truncation'. However,
the 6-digit microseconds on '2021-08-27 01:03:18.107753' does not cause
truncation error.
