At 5:44 PM -0700 8/27/07, Chris wrote:
I don't think this is a bug. I think what's
happening is that your timestamp column can't
hold that date, it's max value is somewhere in
2038.
So I guess either change your timestamp column
to a datetime column, or prevent users from
putting invalid data in.
Ahh ... yes, indeed.
mysql> select DATE_ADD(CURRENT_DATE(),INTERVAL 99999 DAY);
+---------------------------------------------+
| DATE_ADD(CURRENT_DATE(),INTERVAL 99999 DAY) |
+---------------------------------------------+
| 2281-06-10 |
+---------------------------------------------+
Daevid, one strategy that might work for you is to enable
strict or traditional SQL mode so that you get an error if the
timestamp value is out of range:
mysql> UPDATE Users SET password_expire = DATE_ADD(CURRENT_DATE(),INTERVAL
-> 99999 DAY) WHERE CoreID = 1 LIMIT 1;
Query OK, 1 row affected, 1 warning (0.01 sec)
Rows matched: 1 Changed: 1 Warnings: 1
mysql> show warnings;
+---------+------+----------------------------------------------------------+
| Level | Code | Message |
+---------+------+----------------------------------------------------------+
| Warning | 1264 | Out of range value for column 'password_expire' at row 1 |
+---------+------+----------------------------------------------------------+
1 row in set (0.00 sec)
mysql> set sql_mode='traditional';
Query OK, 0 rows affected (0.01 sec)
mysql> UPDATE Users SET password_expire =
DATE_ADD(CURRENT_DATE(),INTERVAL 99999 DAY) WHERE
CoreID = 1 LIMIT 1;
ERROR 1292 (22007): Incorrect datetime value:
'2281-06-10' for column 'password_expire' at row 1
Daevid Vincent wrote:
using 99999 as the DATE_ADD interval value will result in 000-00-00 but
one less 9 will work.
root# mysql --version
mysql Ver 14.12 Distrib 5.0.41, for pc-linux-gnu (i686) using EditLine
wrapper
CREATE TABLE `Users` ( `CoreID` int(10)
unsigned NOT NULL auto_increment,
`Username` varchar(155) default NULL,
`Password` varchar(64) default NULL,
`password_expire` timestamp NOT NULL
default '0000-00-00
00:00:00',
PRIMARY KEY (`CoreID`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
UPDATE Users SET password_expire = DATE_ADD(CURRENT_DATE(),INTERVAL 9999
DAY) WHERE CoreID = 1 LIMIT 1;
UPDATE Users SET password_expire = DATE_ADD(CURRENT_DATE(),INTERVAL
99999 DAY) WHERE CoreID = 1 LIMIT 1;
Added as bug:
http://bugs.mysql.com/bug.php?id=30656
Added as a tip:
http://dev.mysql.com/doc/refman/5.0/en/date-and-time-functions.html
BTW, it's extremely obnoxious that when I enter in a comment on that
page, then choose "bug" from the select box, it throws me to another
page that says, "Sorry, but this is not the correct place to report
bugs. You need to report bugs using our online bug reporting system. You
can start filling out a bug report with the text you have already
entered by clicking the following button:" ... Well why the heck did you
let me choose that option then! UGH!
ÐÆ5ÏÐ
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
--
Paul DuBois, MySQL Documentation Team
Madison, Wisconsin, USA
MySQL AB, www.mysql.com
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
