Trigger and "Warning: #1265 Data truncated"

I'm very new to triggers, so I suspect I've done something naive.

When a row is inserted into a table, I want populate a 'date' column with
the date 45 days hence. I've created a trigger:

CREATE TRIGGER `test`.`setExpiryDate` BEFORE INSERT ON `test`.`mysql_auth`
FOR EACH ROW SET NEW.expires = curdate()+interval 45 day

It works, doing what I want, but I get the following error:

Warning: #1265 Data truncated for column 'expires' at row 1

The table is:

CREATE TABLE `mysql_auth` (
`id` int(11) NOT NULL auto_increment,
`user` varchar(25) NOT NULL,
`password` varchar(32) NOT NULL,
`expires` date NOT NULL,
`login_failures` int(11) NOT NULL default '0',
`last_login_failure` datetime default NULL,
PRIMARY KEY (`id`)
)

Can someone help me understand what it happening here, and how to fix it?

Thanks

--
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: Trigger and "Warning: #1265 Data truncated"

Curdate() returns a datetime, which you are pushing into a date field.
It is truncating the time part. Just truncate it and you should be
fine :)

Walter

On Fri, Jun 5, 2009 at 5:56 AM, Keith Edmunds wrote:
> I'm very new to triggers, so I suspect I've done something naive.
>
> When a row is inserted into a table, I want populate a 'date' column with
> the date 45 days hence. I've created a trigger:
>
> CREATE TRIGGER `test`.`setExpiryDate` BEFORE INSERT ON `test`.`mysql_auth=
`
> FOR EACH ROW SET NEW.expires =3D curdate()+interval 45 day
>
> It works, doing what I want, but I get the following error:
>
> Warning: #1265 Data truncated for column 'expires' at row 1
>
> The table is:
>
> CREATE TABLE `mysql_auth` (
> `id` int(11) NOT NULL auto_increment,
> `user` varchar(25) NOT NULL,
> `password` varchar(32) NOT NULL,
> `expires` date NOT NULL,
> `login_failures` int(11) NOT NULL default '0',
> `last_login_failure` datetime default NULL,
> PRIMARY KEY (`id`)
> )
>
> Can someone help me understand what it happening here, and how to fix it?
>
> Thanks
>
> --
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe:    http://lists.mysql.com/mysql?unsub=3Dlists@o=
lindata.com
>
>



--=20
Walter Heck, Engineer @ Open Query (http://openquery.com)
Affordable Training and ProActive Support for MySQL & related technologies

Follow our blog at http://openquery.com/blog/
OurDelta: free enhanced builds for MySQL @ http://ourdelta.org

--
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: Trigger and "Warning: #1265 Data truncated"

> Curdate() returns a datetime, which you are pushing into a date field.
> It is truncating the time part. Just truncate it and you should be
> fine :)

Hmmm. Not according to
http://dev.mysql.com/doc/refman/5.0/en/date-and-time-functio ns.html#function_curdate
or, indeed, experiments:

mysql> select curdate();
+------------+
| curdate() |
+------------+
| 2009-06-09 |
+------------+
1 row in set (0.02 sec)

So have I misunderstood your answer, or is there some other reason for the
error I get?

Thanks,
Keith

--
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