One of the many gotchas when using MySQL. And the reasons I refuse
to use it. By default MySQL is not ACID compliant. Try the following:
from 5.1.11-beta:
> create table test (x tinyint, d date);
> insert into test values (300, '2006-02-30');
> select * from test;
+----------+----------------+
| x | d |
+----------+----------------+
| 127 | 0000-00-00 |
+----------+----------------+
MySQL has changed the values stored from the values you insert
without any complaints whatsoever. This is a major violation if the
I in ACID (integrity).
My 2c.
Mark
On Sep 23, 2006, at 7:17 AM, Thorsten Hohage wrote:
Hello,
I spend four hours this morning even before breakfast(!!!) to get
behind this issue.
A customer reported to me: A lot of date entries in our start
changing "magically" to today, without doing anything except
viewing the record!
Result: Using mySQL 4.x or even 5.0.x it's possible to have
datetimes like '2004-00-08 10:22:03' as "valid" values - mySQL
throws no exceptions / gives no error nothing, it's e.g. possible
to do a "where month(myDate) = 0" with a valid result. After
scrolling through the archives all this records are batch-imported
in the database at some different times, the source should (?) be
for all of them a FileMaker Pro 5.x - through CSV-Export. And at
none of this operations there are any error messages!
Stepping deep in my backups these errors seems really be imported,
not because of table / database corruption or user error.
Far more interesting, when looking on the corrupted data, approx.
70% of the dates are from 1. - 9. Oktober, 2004 some other dates
all with a one digit day, but this Oktober seems to have had some
"devil's day". And no, all these Okt-2004-records are not
"related" (several different imports at different times, from
different files and different source systems!).
SO the RB conclusion: Before start debugging through all your
RealBasic-Application check the validity of the date- / datetime-
colums in mySQL.
Happy weekend,
NOW time for breakfast,
ciao
Thorsten Hohage
--
that-Office.de Softwaredesign - Hamburg,Germany
_______________________________________________
Unsubscribe or switch delivery mode:
<http://www.realsoftware.com/support/listmanager/>
Search the archives of this list here:
<http://support.realsoftware.com/listarchives/lists.html>
_______________________________________________
Unsubscribe or switch delivery mode:
<http://www.realsoftware.com/support/listmanager/>
Search the archives of this list here:
<http://support.realsoftware.com/listarchives/lists.html>
