Re: MySQL running out of date
Hans,
the problem is partially fixed in 4.1.1. You get a warning from inserting a
nonsensical date. But it still accepts '1996-02-31' without a warning,
though there are max 29 days in February. I have now notified the developers
about this bug.
Best regards,
Heikki Tuuri
Innobase Oy
http://www.innodb.com
Foreign keys, transactions, and row level locking for MySQL
InnoDB Hot Backup - a non-free hot backup tool for MySQL
.
[EMAIL PROTECTED]:~/mysql-4.1/client mysql test
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 1 to server version: 4.1.1-alpha-log
Type 'help;' or '\h' for help. Type '\c' to clear the buffer.
mysql create database data_test ;
Query OK, 1 row affected (0.01 sec)
mysql use data_test;
Database changed
mysql create table test3 (a date);
Query OK, 0 rows affected (0.18 sec)
mysql insert into test3 values (-1);
Query OK, 1 row affected, 1 warning (0.01 sec)
mysql insert into test3 values ('1996-02-31');
Query OK, 1 row affected (0.00 sec)
mysql insert into test3 values ('1996-67-31');
Query OK, 1 row affected, 1 warning (0.00 sec)
mysql select * from test3;
++
| a |
++
| -00-00 |
| 1996-02-31 |
| -00-00 |
++
3 rows in set (0.03 sec)
mysql
- Original Message -
From: Hans van Harten [EMAIL PROTECTED]
Newsgroups: mailing.database.mysql
Sent: Friday, August 22, 2003 12:27 AM
Subject: MySQL running out of date
--=_NextPart_000_0242_01C36839.43CE8B60
Content-Type: text/plain;
charset=iso-8859-1
Content-Transfer-Encoding: quoted-printable
Some make the laughing stock of MySQL with this code:
create database data_test ;
use data_test;
create table test3 (a date);
insert into test3 values (-1);
insert into test3 values ('1996-02-31');
insert into test3 values ('1996-67-31');
select * from test3;
I ran it on MYSQL-4.10-max and was not amused.
Anyone to comment??
HansH
--=_NextPart_000_0242_01C36839.43CE8B60--
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: MySQL running out of date
On Fri, Aug 22, 2003 at 01:16:48AM -0500, Hans van Harten wrote:
Rajesh Kumar wrote:
Peter Brawley unknowingly asked us:
Interestingly, use of date_add() and date_sub() on 'odd' dates such
as Feb 31 does produce sane results.
Subtract one from 2000 Feb 31, and you'll get 2000-03-01.
This is sane!!??
This is where Unix Timestamps come into action (and perhaps rescue)!
To be sure that we're storing a sane value in our DB, we could use:
SELECT unix_timestamp('2002-02-31');
which provides a correct result.
Some interesting results:
We could then probably eliminate all timestamps stored as 1969-12-31
as we know that they're illegal.
Uh, isn't it an signed integer, thus allowing date before 1970-01-01 too??
Anyway, all this led me towards is:
create database data_test ;
use data_test;
create table test3 (a date);
insert into test3 values (
from_unixtime(unix_timestamp('2002-102-31'),'%Y-%m-%d' ));
insert into test3 values (
from_unixtime(unix_timestamp('1996-02-31'),'%Y-%m-%d' ));
insert into test3 values (
from_unixtime(unix_timestamp('1996-67-31'),'%Y-%m-%d' ));
insert into test3 values ( from_unixtime(-2147483646));
select * from test3;
still resulting in a mash:
1970-01-01
1996-03-01
1970-01-01
1900-01-00
that comes with neither warnings nor errors reported ...
Error reports would have been nice, but why does your application supply
these incorrect dates anyway? Probably because some user typed them. And
what are you going to tell the user when you get a generic MySQL error
for an INSERT with many values? 'Oops, something went wrong'?
Just check the values in your application, like you should do for any
value coming from an external source. Then you can tell the user exactly
what went wrong for each field. It also prevents garbage or badly
formatted data from entering your database.
Regards,
Fred.
--
Fred van Engen XB Networks B.V.
email: [EMAIL PROTECTED]Televisieweg 2
tel: +31 36 5462400 1322 AC Almere
fax: +31 36 5462424 The Netherlands
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: MySQL running out of date
Rajesh Kumar wrote:
Hans van Harten unknowingly asked us:
create database data_test ;
use data_test;
create table test3 (a date);
insert into test3 values (
from_unixtime(unix_timestamp('2002-102-31'),'%Y-%m-%d' ));
insert into test3 values (
from_unixtime(unix_timestamp('1996-02-31'),'%Y-%m-%d' ));
insert into test3 values (
from_unixtime(unix_timestamp('1996-67-31'),'%Y-%m-%d' ));
insert into test3 values ( from_unixtime(-2147483646));
select * from test3;
still resulting in a mash:
1970-01-01
1996-03-01
1970-01-01
1900-01-00
that comes with neither warnings nor errors reported ...
I don't like to organise my dates this way ...
Why don't you use some middleware to validate your dates?
MySql is not intented to check the dates. It is up to the programmer
to validate the dates, and make sure legit dates are being inserted.
Why not throw away all types and just store anything as a blob of bytes, no
more fuz about types at all, but in the programmers logic...
Programmer wil have to check everything and anything anyway, as even
create database TEST;
create table TEST (FUN int);
insert TEST (FUN ) value ( 1212121212121212121212.12 );
select * from TEST;
returns garbage (2147483647) with neither warnings nor errors !!!
HansH
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: MySQL running out of date
On Sat, Aug 23, 2003 at 10:54:32AM +0200, Hans van Harten wrote: Fred van Engen wrote: On Fri, Aug 22, 2003 at 01:16:48AM -0500, Hans van Harten wrote: that comes with neither warnings nor errors reported ... Error reports would have been nice, but why does your application supply these incorrect dates anyway? Probably because some user typed them. And what are you going to tell the user when you get a generic MySQL error for an INSERT with many values? 'Oops, something went wrong'? Planned to dump it into MySQL and check only if MySQL croaked about it. Just check the values in your application, like you should do for any value coming from an external source. Then you can tell the user exactly what went wrong for each field. It also prevents garbage or badly formatted data from entering your database. My checks might not match those of (the next version of) MySQL and at that time the difference in thoughts will pass unnoticed ! I agree that MySQL should complain but I'm not sure it should fail. The problem is that MySQL has always behaved this way and is in many cases documented to do so. Some programs may expect MySQL to do clipping of large values (your example in another post) and will fail if this changes. Valid dates are clearly defined independently of MySQL. Just don't feed MySQL anything else. If the presentation changes (-MM-DD, DD-MM- or MM-DD-) that would need to be documented anyway. Valid ranges for any field are defined in your table definitions and the absolute limits are defined in the MySQL documentation. If MySQL should fail on a simple INSERT with out-of-bounds values, it should also fail when the out-of-bounds value is generated in a complex query involving expressions with fields (or subqueries). How would you know for which records an UPDATE or INSERT failed? Would you want it to fail the entire query and not just problematic records or even fields? That would hardly be possible with MyISAM tables and would need some kind of subtransactions in any transactioned tables. Partly failing is silly IMHO and failing completely is difficult. So I guess it should just warn you that you did something silly. If you use transactions, you can decide to abort or let MySQL decide that. Regards, Fred. -- Fred van Engen XB Networks B.V. email: [EMAIL PROTECTED]Televisieweg 2 tel: +31 36 5462400 1322 AC Almere fax: +31 36 5462424 The Netherlands -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: MySQL running out of date
Fred van Engen wrote: On Sat, Aug 23, 2003 at 10:54:32AM +0200, Hans van Harten wrote: My checks might not match those of (the next version of) MySQL and at that time the difference in thoughts will pass unnoticed ! I agree that MySQL should complain but I'm not sure it should fail. The problem is that MySQL has always behaved this way and is in many cases documented to do so. Some programs may expect MySQL to do clipping of large values (your example in another post) and will fail if this changes. Preserving the good old installed base, I cannot agree more!! If MySQL should fail on a simple INSERT with out-of-bounds values, it should also fail when the out-of-bounds value is generated in a complex query involving expressions with fields (or subqueries). Right ... 2^66 insert test (FUN ) value ( '25' ); select * from test where fun2147483648*2147483648*16; return 0 records. How would you know for which records an UPDATE or INSERT failed? Would you want it to fail the entire query and not just problematic records or even fields? Ordinary, UPDATE or INSERT would do one record at a time. BTW INSERT -or REPLACE- do croak about misfits while using FKs and then do not process any field -and none of the other records, if you used an record set- That would hardly be possible with MyISAM tables and would need some kind of subtransactions in any transactioned tables. My plan was to use rollback (on Innodb) directly after croak, then check and report. Ultimately you could write your own rollback-ish mechanisme... ... hell, could even write my own RDBMS or re-invent wheels ;-( Anyway, thanks to all responding. HansH -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: MySQL running out of date
On Sat, Aug 23, 2003 at 02:07:36PM +0200, Hans van Harten wrote: Fred van Engen wrote: On Sat, Aug 23, 2003 at 10:54:32AM +0200, Hans van Harten wrote: My checks might not match those of (the next version of) MySQL and at that time the difference in thoughts will pass unnoticed ! I agree that MySQL should complain but I'm not sure it should fail. The problem is that MySQL has always behaved this way and is in many cases documented to do so. Some programs may expect MySQL to do clipping of large values (your example in another post) and will fail if this changes. Preserving the good old installed base, I cannot agree more!! Well, the behaviour could change in a major release (5.0 ?) but not in a minor one (3.23.xx or 4.0.xx). If MySQL should fail on a simple INSERT with out-of-bounds values, it should also fail when the out-of-bounds value is generated in a complex query involving expressions with fields (or subqueries). Right ... 2^66 insert test (FUN ) value ( '25' ); select * from test where fun2147483648*2147483648*16; return 0 records. SELECT 2147483648*2147483648*4 returns 0. SELECT 2147483648*2147483648*2 returns -9223372036854775808. SELECT 2147483648*2147483648*2-1 returns 9223372036854775807. The same problem in most programming languages. What do other DBMS do and what do the SQL standards say? I really don't know. How would you know for which records an UPDATE or INSERT failed? Would you want it to fail the entire query and not just problematic records or even fields? Ordinary, UPDATE or INSERT would do one record at a time. INSERT INTO test(id, myint) VALUES (1,2147483647), (2,2147483648); The second is out-of-bounds. Should the first be revoked? INSERT INTO test(id, myint) VALUES (1,2147483646), (2,2147483647); UPDATE test SET myint = myint+1; INSERT INTO test(id, myint) VALUES (1,2147483646), (2,2147483647); INSERT into test2 SELECT id, myint+1 FROM test; The UPDATE and last INSERT are out-of-bounds for the second record. Should the update and insert of the first record be revoked? That's just too much work for current MyISAM tables. BTW INSERT -or REPLACE- do croak about misfits while using FKs and then do not process any field -and none of the other records, if you used an record set- Great. That's InnoDB, which could do the same for each of the earlier examples. People might expect that from transactioned tables. For other table types I guess it would be unrealistic. Regards, Fred. -- Fred van Engen XB Networks B.V. email: [EMAIL PROTECTED]Televisieweg 2 tel: +31 36 5462400 1322 AC Almere fax: +31 36 5462424 The Netherlands -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: MySQL running out of date
Rajesh Kumar wrote:
Peter Brawley unknowingly asked us:
Interestingly, use of date_add() and date_sub() on 'odd' dates such
as Feb 31 does produce sane results.
Subtract one from 2000 Feb 31, and you'll get 2000-03-01.
This is sane!!??
This is where Unix Timestamps come into action (and perhaps rescue)!
To be sure that we're storing a sane value in our DB, we could use:
SELECT unix_timestamp('2002-02-31');
which provides a correct result.
Some interesting results:
We could then probably eliminate all timestamps stored as 1969-12-31
as we know that they're illegal.
Uh, isn't it an signed integer, thus allowing date before 1970-01-01 too??
Anyway, all this led me towards is:
create database data_test ;
use data_test;
create table test3 (a date);
insert into test3 values (
from_unixtime(unix_timestamp('2002-102-31'),'%Y-%m-%d' ));
insert into test3 values (
from_unixtime(unix_timestamp('1996-02-31'),'%Y-%m-%d' ));
insert into test3 values (
from_unixtime(unix_timestamp('1996-67-31'),'%Y-%m-%d' ));
insert into test3 values ( from_unixtime(-2147483646));
select * from test3;
still resulting in a mash:
1970-01-01
1996-03-01
1970-01-01
1900-01-00
that comes with neither warnings nor errors reported ...
I don't like to organise my dates this way ...
HansH
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
MySQL running out of date
Some make the laughing stock of MySQL with this code:
create database data_test ;
use data_test;
create table test3 (a date);
insert into test3 values (-1);
insert into test3 values ('1996-02-31');
insert into test3 values ('1996-67-31');
select * from test3;
I ran it on MYSQL-4.10-max and was not amused.
Anyone to comment??
HansH
Re: MySQL running out of date
Hans van Harten unknowingly asked us:
Some make the laughing stock of MySQL with this code:
create database data_test ;
use data_test;
create table test3 (a date);
insert into test3 values (-1);
insert into test3 values ('1996-02-31');
insert into test3 values ('1996-67-31');
select * from test3;
I ran it on MYSQL-4.10-max and was not amused.
Anyone to comment??
Yeah, what's wrong with this? Absolutely expected results.
--
[ Rajesh Kumar ]
__
Meet the guy at http://www.meetRajesh.com/
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: MySQL running out of date
Same here.
Got 0's for the invalid dates, correct date for the other. What is wrong?
Adam Clauss
[EMAIL PROTECTED]
-Original Message-
From: Rajesh Kumar [mailto:[EMAIL PROTECTED]
Sent: Thursday, August 21, 2003 1:35 PM
To: Hans van Harten
Cc: [EMAIL PROTECTED]
Subject: Re: MySQL running out of date
Hans van Harten unknowingly asked us:
Some make the laughing stock of MySQL with this code:
create database data_test ;
use data_test;
create table test3 (a date);
insert into test3 values (-1);
insert into test3 values ('1996-02-31');
insert into test3 values ('1996-67-31');
select * from test3;
I ran it on MYSQL-4.10-max and was not amused.
Anyone to comment??
Yeah, what's wrong with this? Absolutely expected results.
--
[ Rajesh Kumar ]
__
Meet the guy at http://www.meetRajesh.com/
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:
http://lists.mysql.com/mysql? [EMAIL PROTECTED]
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: MySQL running out of date
As per the manual that is correct.
The only correct date will be 1996-02-31.
what is the problem?
-Original Message-
From: Hans van Harten [mailto:[EMAIL PROTECTED]
Sent: Thursday, August 21, 2003 4:09 PM
To: [EMAIL PROTECTED]
Subject: MySQL running out of date
Some make the laughing stock of MySQL with this code:
create database data_test ;
use data_test;
create table test3 (a date);
insert into test3 values (-1);
insert into test3 values ('1996-02-31');
insert into test3 values ('1996-67-31');
select * from test3;
I ran it on MYSQL-4.10-max and was not amused.
Anyone to comment??
HansH
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: MySQL running out of date
Adam Clauss and Rajesh Kumar wrote:
Hans van Harten unknowingly asked us:
LOL
Some make the laughing stock of MySQL with this code:
create database data_test ;
use data_test;
create table test3 (a date);
insert into test3 values (-1);
insert into test3 values ('1996-02-31');
insert into test3 values ('1996-67-31');
select * from test3;
I ran it on MYSQL-4.10-max and was not amused.
Anyone to comment??
Yeah, what's wrong with this? Absolutely expected results.
Same here.
Got 0's for the invalid dates, correct date for the other. What is
wrong?
I'ld say, NONE of them is correct ... Februari _31_st !!
Then I'ld expect at least a warning, rather an error, on each of the
samples!
HansH
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: MySQL running out of date
I agree it's unfortunate that the dates get stored. But some do seem to
prefer it this way.
To quote the manual at the bottom of:
http://www.mysql.com/doc/en/Using_DATE.html
If the date cannot be converted to any reasonable value, a 0 is
stored in the DATE field, which will be retrieved as -00-00. This is
both a speed and convenience issue as we believe that the database's
responsibility is to retrieve the same date you stored (even if the data
was not logically correct in all cases). We think it is up to the
application to check the dates, and not the server.
Interestingly, use of date_add() and date_sub() on 'odd' dates such as
Feb 31 does produce sane results.
Subtract one from 2000 Feb 31, and you'll get 2000-03-01.
TO test the format of an input date, you might try:
mysql select date_add('2000-11-31', interval 0 day);
++
| date_add('2000-11-31', interval 0 day) |
++
| 2000-12-01 |
++
1 row in set (0.00 sec)
You'll be able to tell whether you've got a good date by comparing the
results. And you can correct a weird-but-valid date by updating it
with this technique.
We may not like the way it works, but at least it's correct per the
manual.
-Original Message-
From: Hans van Harten [mailto:[EMAIL PROTECTED]
Sent: Thursday, August 21, 2003 3:05 PM
To: [EMAIL PROTECTED]
Subject: Re: MySQL running out of date
Adam Clauss and Rajesh Kumar wrote:
Hans van Harten unknowingly asked us:
LOL
Some make the laughing stock of MySQL with this code:
create database data_test ;
use data_test;
create table test3 (a date);
insert into test3 values (-1);
insert into test3 values ('1996-02-31');
insert into test3 values ('1996-67-31');
select * from test3;
I ran it on MYSQL-4.10-max and was not amused.
Anyone to comment??
Yeah, what's wrong with this? Absolutely expected results.
Same here.
Got 0's for the invalid dates, correct date for the other. What is
wrong?
I'ld say, NONE of them is correct ... Februari _31_st !!
Then I'ld expect at least a warning, rather an error, on each
of the samples!
HansH
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:
http://lists.mysql.com/mysql? [EMAIL PROTECTED]
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: MySQL running out of date
Interestingly, use of date_add() and date_sub() on 'odd' dates such as Feb 31 does produce sane results. Subtract one from 2000 Feb 31, and you'll get 2000-03-01. This is sane!!?? PB [mysql]
Re: MySQL running out of date
Kevin Fries wrote:
I agree it's unfortunate that the dates get stored. But some do seem
to prefer it this way.
To quote the manual at the bottom of:
http://www.mysql.com/doc/en/Using_DATE.html
If the date cannot be converted to any reasonable value, a 0 is
stored in the DATE field, which will be retrieved as -00-00. This
is both a speed and convenience issue as we believe that the
database's responsibility is to retrieve the same date you stored
(even if the data was not logically correct in all cases). We think
it is up to the application to check the dates, and not the server.
It was my understanding a server should validate the date per type.
Interestingly, use of date_add() and date_sub() on 'odd' dates such as
Feb 31 does produce sane results.
Only partionally ...
insert into test3 values ( date_add('1996-02-30', interval 0 day) );
insert into test3 values ( date_add('1996-02-61', interval 0 day) );
insert into test3 values ( date_add('1996-67-31', interval 0 day) );
select * from test3;
Returns
1996-03-01
NULL expected: April 1, 1996 ...
NULL expected: July 31, 2001 ...
We may not like the way it works, but at least it's correct per the
manual.
Does it comply to ANSI or SQL92 or ... ??
HansH
...
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: MySQL running out of date
Peter Brawley unknowingly asked us:
Interestingly, use of date_add() and date_sub() on 'odd' dates such as
Feb 31 does produce sane results.
Subtract one from 2000 Feb 31, and you'll get 2000-03-01.
This is sane!!??
This is where Unix Timestamps come into action (and perhaps rescue)!
To be sure that we're storing a sane value in our DB, we could use:
SELECT unix_timestamp('2002-02-31');
which provides a correct result.
Some interesting results:
mysql select from_unixtime(unix_timestamp('2000-11-31'),'%Y-%m-%d');
++
| from_unixtime(unix_timestamp('2000-11-31'),'%Y-%m-%d') |
++
| 2000-12-01 |
++
mysql select from_unixtime(unix_timestamp('2000-02-31'),'%Y-%m-%d');
++
| from_unixtime(unix_timestamp('2000-02-31'),'%Y-%m-%d') |
++
| 2000-03-02 |
++
And lastly, I don't know how this happens:
mysql select unix_timestamp('2002-102-31');
+---+
| unix_timestamp('2002-102-31') |
+---+
|1037026951 |
+---+
We could then probably eliminate all timestamps stored as 1969-12-31 as
we know that they're illegal.
--
[ Rajesh Kumar ]
__
Meet the guy at http://www.meetRajesh.com/
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
