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/...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ÏÐ

At 4:56 PM -0700 8/27/07, 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/...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!

Why do you consider this extremely obnoxious? Also, I'm curious why
you tried to use the user comment page to report a bug, given that
the page says: "If you've found a bug or wish to request a feature MySQL
is missing, please use the bug system. When doing this, please perform
a search first in order not to report a bug or request a feature that's
already in that system."

The option is there to let you know that the user comment system
isn't for reporting bugs *and* to let you know the correct place
to report them. It's there as extra reinforcement for people who
ignore or overlook the statement that I mentioned in the preceding
paragraph.

--
Paul DuBois, MySQL Documentation Team
Madison, Wisconsin, USA
MySQL AB, www.mysql.com

> -----Original Message-----
> From: Paul DuBois [mailto:paul@mysql.com]
> Sent: Monday, August 27, 2007 5:04 PM
> To: Daevid Vincent; 'MySQL General'
> Subject: Re: BUG: DATE_ADD 99999 fails, but 9999 works.
>
> At 4:56 PM -0700 8/27/07, 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/...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!
>
> Why do you consider this extremely obnoxious? Also, I'm curious why
> you tried to use the user comment page to report a bug, given that
> the page says: "If you've found a bug or wish to request a
> feature MySQL
> is missing, please use the bug system. When doing this, please perform
> a search first in order not to report a bug or request a
> feature that's
> already in that system."
>
> The option is there to let you know that the user comment system
> isn't for reporting bugs *and* to let you know the correct place
> to report them. It's there as extra reinforcement for people who
> ignore or overlook the statement that I mentioned in the preceding
> paragraph.
>
> --
> Paul DuBois, MySQL Documentation Team
> Madison, Wisconsin, USA
> MySQL AB, www.mysql.com
>

Because it's also a "tip" as your form has an option for, so some poor
sucker doesn't spend hours trying to figure out why their passwords are
all expired and nobody can log into their $40,000 appliance and a shit
storm comes down upon their head because some dumb user thought they'd
_try_ to put 99999 in so their password wouldn't expire for 274 years.

Plus I did do a bug search and found nothing for DATE_ADD.

Not everyone peruses the 6 million bugs in your system, trying every
possible keyword... It is more useful to have something listed at the
bottom there (as I did), so someone that was doing a search on that page
for DATE_ADD would then see this _tip_.

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.


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/...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ÏÐ
>
>
>
>

> -----Original Message-----
> From: Chris [mailto:listschris@leftbrained.org]
> Sent: Monday, August 27, 2007 5:45 PM
>
> 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.

You appear to be correct, burried in the plethora of bullet points here:
http://dev.mysql.com/doc/refman/5.0/en/datetime.html
"For example, TIMESTAMP values cannot be earlier than 1970 or later than
2038."

So that _is_ the root cause of the problem, but it's still a bug.

There is no reason (from a mySQL user/PHP developer's perspective) that
2038 should be my upper year limit. I should be able to make any date up
to "9999-12-31" !!?

This is absurd. We're making enterprise level tools that run at US
Government offices, The entire state of Alaska, Military, Colleges,
Fortune 500 companies.... You mean in 21 years from now, all this will
just fail miserably because of some obscure 2038 limitation? This is Y2K
all over again -- unless mySQL fixes this bug.

> So I guess either change your timestamp column to a datetime column,

Interesting thing with that, we used to use datetime columns (where
applicable) but since we store everything in UTC now, as this product is
international, we had to switch (painfully I might add) to timestamp. I
forget the exact reason, and this was about a year ago, so it may be
moot now anyways -- it had to do with using mySQL's conversion routines
so the dates would display in the GUI for the user's local timezone they
set in their profile, and those routines didn't work on datetime or some
such nonsense.

> or prevent users from putting invalid data in.

I've limited the text field to 4 digits from 5. but that doesn't make
this any less of a mySQL "issue", that's just a band-aid to mask an
inadequacy of the RDBMS.

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/...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/mysql?unsub=paul@mysql.com


--
Paul DuBois, MySQL Documentation Team
Madison, Wisconsin, USA
MySQL AB, www.mysql.com

On 8/27/07, Daevid Vincent <daevid@daevid.com> wrote:
> Fortune 500 companies.... You mean in 21 years from now, all this will
> just fail miserably because of some obscure 2038 limitation? This is Y2K
> all over again -- unless mySQL fixes this bug.

Obscure? Not really - lots of software is going to have problems
around that time. Wikipedia has a good write-up of it:

http://en.wikipedia.org/wiki/Year_2038_problem

And yes, it will be Y2K all over again - hype, hassle, and probably
not much real trouble (famous last words!)

--
_________________
Kirk Friggstad, Daft Viking Consulting
email / msn / gtalk: kirk@daftviking.com
aim / skype: daftviking
mobile / SMS: 306-867-0010

So, I found out why we had to switch all of our datetimes to timestamps:

http://dev.mysql.com/doc/refman/5.0/...e-support.html

"The current time zone setting does not affect values in DATE, TIME, or
DATETIME columns."

So it appears this is a catch 22... In order to store things in UTC and then
use the user's local TZ, you need to use 'timestamps'. But then you can't
use them for years past 2038... However 'datetime' will store any date, but
you can't store in UTC and display via the TZ setting.

:-\

> -----Original Message-----
> From: Daevid Vincent [mailto:daevid@daevid.com]
> Sent: Monday, August 27, 2007 6:09 PM
> To: 'MySQL General'
> Cc: 'Chris'
> Subject: RE: BUG: DATE_ADD 99999 fails, but 9999 works.
>
> > -----Original Message-----
> > From: Chris [mailto:listschris@leftbrained.org]
> > Sent: Monday, August 27, 2007 5:45 PM
> >
> > 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.
>
> You appear to be correct, burried in the plethora of bullet
> points here:
> http://dev.mysql.com/doc/refman/5.0/en/datetime.html
> "For example, TIMESTAMP values cannot be earlier than 1970 or
> later than
> 2038."
>
> So that _is_ the root cause of the problem, but it's still a bug.
>
> There is no reason (from a mySQL user/PHP developer's
> perspective) that
> 2038 should be my upper year limit. I should be able to make
> any date up
> to "9999-12-31" !!?
>
> This is absurd. We're making enterprise level tools that run at US
> Government offices, The entire state of Alaska, Military, Colleges,
> Fortune 500 companies.... You mean in 21 years from now, all this will
> just fail miserably because of some obscure 2038 limitation?
> This is Y2K
> all over again -- unless mySQL fixes this bug.
>
> > So I guess either change your timestamp column to a
> datetime column,
>
> Interesting thing with that, we used to use datetime columns (where
> applicable) but since we store everything in UTC now, as this
> product is
> international, we had to switch (painfully I might add) to
> timestamp. I
> forget the exact reason, and this was about a year ago, so it may be
> moot now anyways -- it had to do with using mySQL's
> conversion routines
> so the dates would display in the GUI for the user's local
> timezone they
> set in their profile, and those routines didn't work on
> datetime or some
> such nonsense.
>
> > or prevent users from putting invalid data in.
>
> I've limited the text field to 4 digits from 5. but that doesn't make
> this any less of a mySQL "issue", that's just a band-aid to mask an
> inadequacy of the RDBMS.
>
>
> --
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe:
> http://lists.mysql.com/mysql?unsub=daevid@daevid.com
>
>