Out of range value for column "datestamp" at row 1

Out of range value for column "datestamp" at row 1

am 09.05.2010 13:29:28 von prabhat kumar

--0016363100e1f1601c0486279aef
Content-Type: text/plain; charset=ISO-8859-1

Hi,
I have setup replication between 2 servers, on both there is different
versions of mysql.

Master :5.0.67-log
Slave : 5.1.43sp1-enterprise-gpl-advanced-log

There is table:

CREATE TABLE `myTable_info` (
`id` int(11) NOT NULL auto_increment,
`range` varchar(255) NOT NULL,
`total_qt` smallint NOT NULL default '0',
`qt_correct` smallint NOT NULL default '0',
`finish_time` smallint NOT NULL default '0',
`username` varchar(100) NOT NULL,
* `datestamp` datetime NOT NULL default '0000-00-00 00:00:00',*
PRIMARY KEY (`id`)
) ENGINE=InnoDB ;

During replication insert on slave error caused .

INSERT INTO myTable_info (id,range, total_qt, qt_correct, finish_time,
username, datestamp) VALUES (NULL,'Kumar', '20', '17', '111', 'Prabhat','*
NOW()');*

Last_SQL_Error: Error 'You have an error in your SQL syntax; check the
manual that corresponds to your MySQL server version for the right syntax

I think problem with* `datestamp` datetime NOT NULL default '0000-00-00
00:00:00',*

Can any one please suggest me, how to deal with this error.

--
Best Regards,

Prabhat Kumar
MySQL DBA
Datavail-India Mumbai
Mobile : 91-9987681929
www.datavail.com

My Blog: http://adminlinux.blogspot.com
My LinkedIn: http://www.linkedin.com/in/profileprabhat

--0016363100e1f1601c0486279aef--

Re: Out of range value for column "datestamp" at row 1

am 09.05.2010 15:42:04 von Carsten Pedersen

Gving the full error message would be helpful.

Check that the sql mode settings for ALLOW_INVALID_DATE, NO_ZERO_DATE
and NO_ZERO_IN_DATE are the same on both master and slave.

/ Carsten

Prabhat Kumar skrev:
> Hi,
> I have setup replication between 2 servers, on both there is different
> versions of mysql.
>
> Master :5.0.67-log
> Slave : 5.1.43sp1-enterprise-gpl-advanced-log
>
> There is table:
>
> CREATE TABLE `myTable_info` (
> `id` int(11) NOT NULL auto_increment,
> `range` varchar(255) NOT NULL,
> `total_qt` smallint NOT NULL default '0',
> `qt_correct` smallint NOT NULL default '0',
> `finish_time` smallint NOT NULL default '0',
> `username` varchar(100) NOT NULL,
> * `datestamp` datetime NOT NULL default '0000-00-00 00:00:00',*
> PRIMARY KEY (`id`)
> ) ENGINE=InnoDB ;
>
> During replication insert on slave error caused .
>
> INSERT INTO myTable_info (id,range, total_qt, qt_correct, finish_time,
> username, datestamp) VALUES (NULL,'Kumar', '20', '17', '111', 'Prabhat','*
> NOW()');*
>
> Last_SQL_Error: Error 'You have an error in your SQL syntax; check the
> manual that corresponds to your MySQL server version for the right syntax
>
> I think problem with* `datestamp` datetime NOT NULL default '0000-00-00
> 00:00:00',*
>
> Can any one please suggest me, how to deal with this error.
>

--
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: Out of range value for column "datestamp" at row 1

am 10.05.2010 14:55:54 von Jay Ess

On 2010-05-09 13:29, Prabhat Kumar wrote:
> INSERT INTO myTable_info (id,range, total_qt, qt_correct, finish_time,
> username, datestamp) VALUES (NULL,'Kumar', '20', '17', '111', 'Prabhat','*
> NOW()');*
>
> Last_SQL_Error: Error 'You have an error in your SQL syntax; check the
> manual that corresponds to your MySQL server version for the right syntax
>
> I think problem with* `datestamp` datetime NOT NULL default '0000-00-00
> 00:00:00',*
>
> Can any one please suggest me, how to deal with this error.
>

Remove the ' around NOW(). ' Makes NOW() a literal string and not a function call.


--
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: Out of range value for column "datestamp" at row 1

am 10.05.2010 15:50:33 von prabhat kumar

--0016362841ec61076504863db1ad
Content-Type: text/plain; charset=ISO-8859-1

Thanks, It worked :)

On Mon, May 10, 2010 at 6:25 PM, Jay Ess wrote:

> On 2010-05-09 13:29, Prabhat Kumar wrote:
>
>> INSERT INTO myTable_info (id,range, total_qt, qt_correct, finish_time,
>> username, datestamp) VALUES (NULL,'Kumar', '20', '17', '111', 'Prabhat','*
>> NOW()');*
>>
>> Last_SQL_Error: Error 'You have an error in your SQL syntax; check the
>> manual that corresponds to your MySQL server version for the right syntax
>>
>> I think problem with* `datestamp` datetime NOT NULL default '0000-00-00
>> 00:00:00',*
>>
>> Can any one please suggest me, how to deal with this error.
>>
>>
> Remove the ' around NOW(). ' Makes NOW() a literal string and not a
> function call.
>
>
> --
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe:
> http://lists.mysql.com/mysql?unsub=aim.prabhat@gmail.com
>
>


--
Best Regards,

Prabhat Kumar
MySQL DBA
Datavail-India Mumbai
Mobile : 91-9987681929
www.datavail.com

My Blog: http://adminlinux.blogspot.com
My LinkedIn: http://www.linkedin.com/in/profileprabhat

--0016362841ec61076504863db1ad--