Help with Timestamp invalid value error

I run a process which loads a series of timestamped data into a table.
I use the TIMESTAMP column.

I have a single value with a timestamp of '2008-03-9 2:56:34.737'
which fails on insert with 'incorrect datetime'. Days before, and
after work. Hours later in the day work. I thought it might be
related to the timezone, but have none of the TZ tables populated.

Can someone tell me why this date in particular fails?

The query that fails:
INSERT INTO secondarydata.trs_newslist (`timestamp`, TRStoryID,
topic_list, product_list)
VALUES (
'2008-03-9 2:56:34.737',
'',
'',
'')

Table:
newsID int(10) unsigned PRI auto_increment
timeStamp timestamp CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP
TRStoryID varchar(128)
topic_list varchar(512)
product_list varchar(512)

Thank you!
Martin
--
---
This is a signature.

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=gcdmg-mysql-2@m.gmane.org

RE: Help with Timestamp invalid value error

What is your sql_mode set to?

I tried inserting that value into a timestamp column on our test server and=
it works fine.

Regards

John Daisley
Mobile +44(0)7812 451238
Email john@butterflysystems.co.uk

Certified MySQL 5 Database Administrator (CMDBA)
Certified MySQL 5 Developer
Cognos BI Developer

-----------------------
Sent from HP IPAQ mobile device.



-----Original Message-----
From: Proemial
Sent: Friday, September 04, 2009 2:27 PM
To: mysql@lists.mysql.com
Subject: Help with Timestamp invalid value error

I run a process which loads a series of timestamped data into a table.
I use the TIMESTAMP column.

I have a single value with a timestamp of '2008-03-9 2:56:34.737'
which fails on insert with 'incorrect datetime'. Days before, and
after work. Hours later in the day work. I thought it might be
related to the timezone, but have none of the TZ tables populated.

Can someone tell me why this date in particular fails?

The query that fails:
INSERT INTO secondarydata.trs_newslist (`timestamp`, TRStoryID,
topic_list, product_list)
VALUES (
'2008-03-9 2:56:34.737',
'',
'',
'')

Table:
newsID int(10) unsigned PRI auto_increment
timeStamp timestamp CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP
TRStoryID varchar(128) =09
topic_list varchar(512)=09
product_list varchar(512)=09

=0A=
=0A=
[The entire original message is not included]=


--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=3Dgcdmg-mysql-2@m.gmane.o rg

Fwd: Help with Timestamp invalid value error

Currently set to:
NO_AUTO_VALUE_ON_ZERO,STRICT_TRANS_TABLES,NO_AUTO_CREATE_USE R,NO_ENGINE_SUB=
STITUTION

On Fri, Sep 4, 2009 at 10:24 AM, John
Daisley wrote:
> What is your sql_mode set to?
>
> I tried inserting that value into a timestamp column on our test server a=
nd it works fine.
>
> Regards
>
> John Daisley
> Mobile +44(0)7812 451238
> Email john@butterflysystems.co.uk
>
> Certified MySQL 5 Database Administrator (CMDBA)
> Certified MySQL 5 Developer
> Cognos BI Developer
>
> -----------------------
> Sent from HP IPAQ mobile device.
>
>
>
> -----Original Message-----
> From: Proemial
> Sent: Friday, September 04, 2009 2:27 PM
> To: mysql@lists.mysql.com
> Subject: Help with Timestamp invalid value error
>
> I run a process which loads a series of timestamped data into a table.
> =A0I =A0use the TIMESTAMP column.
>
> I have a single value with a timestamp of '2008-03-9 2:56:34.737'
> which fails on insert with 'incorrect datetime'. =A0Days before, and
> after work. =A0Hours later in the day work. =A0I thought it might be
> related to the timezone, but have none of the TZ tables populated.
>
> Can someone tell me why this date in particular fails?
>
> The query that fails:
> INSERT INTO secondarydata.trs_newslist (`timestamp`, TRStoryID,
> topic_list, product_list)
> VALUES (
> '2008-03-9 2:56:34.737',
> '',
> '',
> '')
>
> Table:
> newsID =A0int(10) unsigned =A0 =A0 =A0 =A0PRI =A0 =A0 =A0 =A0 =A0 =A0 aut=
o_increment
> timeStamp =A0 =A0 =A0 timestamp =A0 =A0 =A0 CURRENT_TIMESTAMP =A0 =A0 =A0=
on update CURRENT_TIMESTAMP
> TRStoryID =A0 =A0 =A0 varchar(128)
> topic_list =A0 =A0 =A0varchar(512)
> product_list =A0 =A0varchar(512)
>
>
>
> [The entire original message is not included]
>




--=20
---
This is a signature.

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=3Dgcdmg-mysql-2@m.gmane.o rg

RE: Fwd: Help with Timestamp invalid value error

Is your table innodb? If so i think the 'STRICT_TRANS_TABLES' sql_mode 'may=
' be causing the problem.

Try inserting the value as....

'2008-03-09 02:56:34.737'

Do you get the same error?

What mysql version is your server?



Regards

John Daisley
Mobile +44(0)7812 451238
Email john@butterflysystems.co.uk

Certified MySQL 5 Database Administrator (CMDBA)
Certified MySQL 5 Developer
Cognos BI Developer

-----------------------
Sent from HP IPAQ mobile device.



-----Original Message-----
From: Proemial
Sent: Friday, September 04, 2009 3:39 PM
To: mysql@lists.mysql.com
Subject: Fwd: Help with Timestamp invalid value error

Currently set to:
NO_AUTO_VALUE_ON_ZERO,STRICT_TRANS_TABLES,NO_AUTO_CREATE_USE R,NO_ENGINE_SUB=
STITUTION

On Fri, Sep 4, 2009 at 10:24 AM, John
Daisley wrote:
> What is your sql_mode set to?
>
> I tried inserting that value into a timestamp column on our test server a=
nd it works fine.
>
> Regards
>
> John Daisley
> Mobile +44(0)7812 451238
> Email john@butterflysystems.co.uk
>
> Certified MySQL 5 Database Administrator (CMDBA)
> Certified MySQL 5 Developer
> Cognos BI Developer
>
> -----------------------
> Sent from HP IPAQ mobile device.
>
>
>


[The entire original message is not included]=


--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=3Dgcdmg-mysql-2@m.gmane.o rg

Re: Fwd: Help with Timestamp invalid value error

I had already tried that, actually. Produces the same error. I
should have mentioned that as well, sorry!

The version is 5.1.34

thanks for the help, btw!
Martin

On Fri, Sep 4, 2009 at 11:14 AM, John
Daisley wrote:
> Is your table innodb? If so i think the 'STRICT_TRANS_TABLES' sql_mode 'm=
ay' be causing the problem.
>
> Try inserting the value as....
>
> =A0'2008-03-09 02:56:34.737'
>
> Do you get the same error?
>
> What mysql version is your server?
>
>
>
> Regards
>
> John Daisley
> Mobile +44(0)7812 451238
> Email john@butterflysystems.co.uk
>
> Certified MySQL 5 Database Administrator (CMDBA)
> Certified MySQL 5 Developer
> Cognos BI Developer
>
> -----------------------
> Sent from HP IPAQ mobile device.
>
>
>
> -----Original Message-----
> From: Proemial
> Sent: Friday, September 04, 2009 3:39 PM
> To: mysql@lists.mysql.com
> Subject: Fwd: Help with Timestamp invalid value error
>
> Currently set to:
> NO_AUTO_VALUE_ON_ZERO,STRICT_TRANS_TABLES,NO_AUTO_CREATE_USE R,NO_ENGINE_S=
UBSTITUTION
>
> On Fri, Sep 4, 2009 at 10:24 AM, John
> Daisley wrote:
>> What is your sql_mode set to?
>>
>> I tried inserting that value into a timestamp column on our test server =
and it works fine.
>>
>> Regards
>>
>> John Daisley
>> Mobile +44(0)7812 451238
>> Email john@butterflysystems.co.uk
>>
>> Certified MySQL 5 Database Administrator (CMDBA)
>> Certified MySQL 5 Developer
>> Cognos BI Developer
>>
>> -----------------------
>> Sent from HP IPAQ mobile device.
>>
>>
>>
>
>
> [The entire original message is not included]
>



--=20
---
This is a signature.

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=3Dgcdmg-mysql-2@m.gmane.o rg

RE: Fwd: Help with Timestamp invalid value error

Mysql doesn't store sub-second values.

try
2008-03-09 02:56:34
Instead of
2008-03-09 02:56:34.737

Regards,
Gavin Towey

-----Original Message-----
From: Proemial [mailto:proemial@gmail.com]
Sent: Friday, September 04, 2009 8:37 AM
To: John Daisley
Cc: mysql@lists.mysql.com
Subject: Re: Fwd: Help with Timestamp invalid value error

I had already tried that, actually. Produces the same error. I
should have mentioned that as well, sorry!

The version is 5.1.34

thanks for the help, btw!
Martin

On Fri, Sep 4, 2009 at 11:14 AM, John
Daisley wrote:
> Is your table innodb? If so i think the 'STRICT_TRANS_TABLES' sql_mode 'm=
ay' be causing the problem.
>
> Try inserting the value as....
>
> '2008-03-09 02:56:34.737'
>
> Do you get the same error?
>
> What mysql version is your server?
>
>
>
> Regards
>
> John Daisley
> Mobile +44(0)7812 451238
> Email john@butterflysystems.co.uk
>
> Certified MySQL 5 Database Administrator (CMDBA)
> Certified MySQL 5 Developer
> Cognos BI Developer
>
> -----------------------
> Sent from HP IPAQ mobile device.
>
>
>
> -----Original Message-----
> From: Proemial
> Sent: Friday, September 04, 2009 3:39 PM
> To: mysql@lists.mysql.com
> Subject: Fwd: Help with Timestamp invalid value error
>
> Currently set to:
> NO_AUTO_VALUE_ON_ZERO,STRICT_TRANS_TABLES,NO_AUTO_CREATE_USE R,NO_ENGINE_S=
UBSTITUTION
>
> On Fri, Sep 4, 2009 at 10:24 AM, John
> Daisley wrote:
>> What is your sql_mode set to?
>>
>> I tried inserting that value into a timestamp column on our test server =
and it works fine.
>>
>> Regards
>>
>> John Daisley
>> Mobile +44(0)7812 451238
>> Email john@butterflysystems.co.uk
>>
>> Certified MySQL 5 Database Administrator (CMDBA)
>> Certified MySQL 5 Developer
>> Cognos BI Developer
>>
>> -----------------------
>> Sent from HP IPAQ mobile device.
>>
>>
>>
>
>
> [The entire original message is not included]
>



--
---
This is a signature.

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=3Dgtowey@ffn.com


The information contained in this transmission may contain privileged and c=
onfidential information. It is intended only for the use of the person(s) n=
amed above. If you are not the intended recipient, you are hereby notified =
that any review, dissemination, distribution or duplication of this communi=
cation is strictly prohibited. If you are not the intended recipient, pleas=
e contact the sender by reply email and destroy all copies of the original =
message.

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=3Dgcdmg-mysql-2@m.gmane.o rg