You are viewing a plain text version of this content. The canonical link for it is here.
Posted to users@tomcat.apache.org by Jerry Malcolm <te...@malcolms.com> on 2021/08/27 01:35:20 UTC
Timestamp Error
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
Re: Timestamp Error
Posted by Christopher Schultz <ch...@christopherschultz.net>.
Terrence and Jerry,
On 8/27/21 21:33, Terence M. Bandoian wrote:
> On 8/27/2021 2:31 PM, Jerry Malcolm wrote:
>>
>> 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-unsubscribe@tomcat.apache.org
>>>> For additional commands, e-mail: users-help@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.
>>
>
> Hi, Jerry-
>
> See the range in the TIMESTAMP section in:
>
> https://dev.mysql.com/doc/refman/5.7/en/date-and-time-type-syntax.html
+1
Just because java.sql.Timestamp represents a (MySQL) TIMESTAMP value
doesn't mean that the database won't balk at certain values.
> Also, be sure to check out the paragraph on timestamp conversion to and
> from UTC in:
>
> https://dev.mysql.com/doc/refman/5.7/en/datetime.html
>
> Finally, I would enable logging on your MySQL server to get a clear
> picture of what's taking place. That should tell you exactly where the
> error is detected.
You should be able to enable the "audit" log which records SQL queries
in a separate log file.
> In addition, I think it would be worth looking at your database with
> the MySQL command line client to see what it actually inserts in the
> database when the error is reported.
Probably nothing gets inserted. :)
-chris
---------------------------------------------------------------------
To unsubscribe, e-mail: users-unsubscribe@tomcat.apache.org
For additional commands, e-mail: users-help@tomcat.apache.org
Re: Timestamp Error
Posted by "Terence M. Bandoian" <te...@tmbsw.com>.
On 8/27/2021 2:31 PM, Jerry Malcolm wrote:
>
> 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-unsubscribe@tomcat.apache.org
>>> For additional commands, e-mail: users-help@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.
>
Hi, Jerry-
See the range in the TIMESTAMP section in:
https://dev.mysql.com/doc/refman/5.7/en/date-and-time-type-syntax.html
Also, be sure to check out the paragraph on timestamp conversion to and
from UTC in:
https://dev.mysql.com/doc/refman/5.7/en/datetime.html
Finally, I would enable logging on your MySQL server to get a clear
picture of what's taking place. That should tell you exactly where the
error is detected. In addition, I think it would be worth looking at
your database with the MySQL command line client to see what it actually
inserts in the database when the error is reported.
Hope that helps.
-Terence Bandoian
---------------------------------------------------------------------
To unsubscribe, e-mail: users-unsubscribe@tomcat.apache.org
For additional commands, e-mail: users-help@tomcat.apache.org
Re: Timestamp Error
Posted by Jerry Malcolm <te...@malcolms.com>.
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-unsubscribe@tomcat.apache.org
>> For additional commands, e-mail: users-help@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.
Re: Timestamp Error
Posted by Mark Eggers <it...@yahoo.com.INVALID>.
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-unsubscribe@tomcat.apache.org
> For additional commands, e-mail: users-help@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/
Re: Timestamp Error
Posted by Christopher Schultz <ch...@christopherschultz.net>.
Jerry,
On 8/27/21 14:16, 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.
WAIT. DO NOT DO THIS.
If you want to set a date/time field in the database, use:
ps = conn.prepareStatement("UPDATE ... SET field=? WHERE ...");
ps.setTimestamp(new Timestamp(0));
ps.executeQuery();
Don't convert to String. It's awful. If you use Timestamp directly, the
driver will figure out all the time zone issues and this shouldn't bite you.
I'm running MariaDB and here's what it has to say about TIMESTAMP fields:
MariaDB [diagnosis]> help timestamp;
Name: 'TIMESTAMP'
Description:
TIMESTAMP
A timestamp. The range is '1970-01-01 00:00:01' UTC to '2038-01-19
03:14:07' UTC. TIMESTAMP values are stored as the number of seconds
since the epoch ('1970-01-01 00:00:00' UTC). A TIMESTAMP cannot
represent the value '1970-01-01 00:00:00' because that is equivalent to
0 seconds from the epoch and the value 0 is reserved for representing
'0000-00-00 00:00:00', the "zero" TIMESTAMP value.
[...]
So you can't even properly store "0" in your database.
-chris
---------------------------------------------------------------------
To unsubscribe, e-mail: users-unsubscribe@tomcat.apache.org
For additional commands, e-mail: users-help@tomcat.apache.org
Re: Timestamp Error
Posted by Christopher Schultz <ch...@christopherschultz.net>.
Jerry,
On 8/27/21 14:16, 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?
Connector/J checks the time zone of the server relative to the time zone
of the java.sql.Timestamp (really java.util.Date) object and adjusts
accordingly. So if you are using "new Timestamp(0)" in your code, that
may be the difference. I find it odd that MySQL accepts the literal 1969
date, though. It's possible you are right and the date is being
fast-forwarded to UTC so it actually becomes 1970-01-01 00:00:00 by the
tie the server tries to store it.
If you are storing new Timestamp(0), you are most likely better off
storing NULL since the beginning of the Epoch is probably not a
meaningful value for you to store. Then again, if you have 100M rows,
adding NULLABLE to your table definition may not be on your short-list
of things to do.
-chris
---------------------------------------------------------------------
To unsubscribe, e-mail: users-unsubscribe@tomcat.apache.org
For additional commands, e-mail: users-help@tomcat.apache.org
Re: Timestamp Error
Posted by Jerry Malcolm <te...@malcolms.com>.
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-unsubscribe@tomcat.apache.org
For additional commands, e-mail: users-help@tomcat.apache.org
Re: Timestamp Error
Posted by Christopher Schultz <ch...@christopherschultz.net>.
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-unsubscribe@tomcat.apache.org
For additional commands, e-mail: users-help@tomcat.apache.org
Re: Timestamp Error
Posted by Mark Eggers <it...@yahoo.com.INVALID>.
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.
. . . just my two cents
/mde/