Regression bug in 4.0.23? FROM_UNIXTIME(0) is now NULL, not 1970-01-01 01:00:00
Hi there, A bug was fixed in 4.0.23: * Fixed bug which caused FROM_UNIXTIME() function to return wrong result if the argument was too big. http://bugs.mysql.com/6439 However, it looks like it has side effects: select FROM_UNIXTIME(0) returns 1970-01-01 01:00:00 in 4.0.22 but NULL in 4.0.23... (European localtime) Needless to say, this confuses application logic (especially if the type is DATETIME NOT NULL). Is this a bug? Should I file a bug at http://bugs.mysql.com/ ? If it was intentional, wouldn't it have been prudent to mention this altered behavior in the release notes? Peter To reproduce: SELECT FROM_UNIXTIME(0); CREATE TABLE `fromunixtime1` ( `timestamp` TIMESTAMP NOT NULL ); INSERT INTO `fromunixtime1` (`timestamp`) values (FROM_UNIXTIME(0)); SELECT `timestamp` FROM `fromunixtime1`; CREATE TABLE `fromunixtime2` ( `datetime` DATETIME NOT NULL ); INSERT INTO `fromunixtime2` (`datetime`) values (FROM_UNIXTIME(0)); SELECT `datetime` FROM `fromunixtime2`; 4.0.22 output: +-+ | FROM_UNIXTIME(0)| +-+ | 1970-01-01 01:00:00 | +-+ 1 row in set (0.00 sec) ++ | timestamp | ++ | 00 | ++ 1 row in set (0.00 sec) +-+ | datetime| +-+ | 1970-01-01 01:00:00 | +-+ mysql --version mysql Ver 12.22 Distrib 4.0.22, for pc-linux (i386) (Debian unstable - a couple of days ago) 4.0.23 output: +--+ | FROM_UNIXTIME(0) | +--+ | NULL | +--+ 1 row in set (0.00 sec) ++ | timestamp | ++ | 20041228151915 | ++ 1 row in set (0.00 sec) ERROR 1048: Column 'datetime' cannot be null Empty set (0.00 sec) mysql --version mysql Ver 12.22 Distrib 4.0.23, for pc-linux-gnu (i386) (Debian unstable - present) -- Peter Valdemar Mørch http://www.morch.com -- No virus found in this outgoing message. Checked by AVG Anti-Virus. Version: 7.0.298 / Virus Database: 265.6.5 - Release Date: 12/26/2004 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: help with SQL (join?) query
Kris zoob-at-doomstar.com |Lists| wrote: I am trying to join to tables: users: uid name 1 john 2 jim 3 mary groups: groupid groupname groupowner groupcreator 1 test1 1 1 2test2 1 2 3 test32 3 My desired output would look like: groupid groupname owner creator 1 test1john john 2 test2john jim 3 test3jim mary I was unable to find an example online.. Can anyone help? try something like : select groupid, groupname, owner.name as f_owner, creator.name as f_creator from groups, users as owner, users as creator where groups.groupowner = owner.uid and groups.groupcreator = creator.uid; (untested - but principle should be sound...) Peter -- Peter Valdemar Mørch http://www.morch.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: timestamp and DST: impossible to backup database? *AND* bugs in TIMEDIFF, FROM_UNIXTIME, et.al.?
Thank you Michael for your very thoughtful reply. I know that it takes time and effort to answer at the level you did. Michael Stassen Michael.Stassen-at-verizon.net |Lists| wrote: You seem to have a fundamental misunderstanding of the TIMESTAMP type. No timezone or DST information is stored in a TIMESTAMP column. Yup. I thought it could be used to unambiguously represent any and all points in time. It can't. Thats it in a nutshell. I need to be able to sort, get and set the time unambiguously, also during the one problem hour in october. I need to know that if I put in a field with a time value I can reliably retrieve it again. And that if a record went in at time X and another in at time Y, Y-X is accurate for all values of Y and X, regardless of how we humans have decided to present X and Y to each other. (Standard computer stuff, no?) DATETIME is ambiguous, seconds since epoch UTC is not. Maybe my surprise is more: Hey, depending on now(), a UNIX_TIMESTAMP(2004-10-31 02:15:00) has two different interal values!!! (Why now() should have any effect on that is still weird to me... I realize *how* it ends up having an effect implementationally, but it *shouldn't*.) The other value is not representable at all by any DATETIME value. And sorting on a DATETIME gives one result now and another after a dump/restore cycle. And there is no way around that. We'll change our application to int(32) unsigned and handle presentation client-side. I don't think I'll ever use a DATETIME again... Maybe thats just me. Thanks again, Michael. Peter -- Peter Valdemar Mørch http://www.morch.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Pushing Files from Subversion
pete-at-holidian.com |Lists| wrote: Disclaimer: I'm new to source control in general and Subversion in specific ... I'd like to be able to make the change and then push those changes... I just wanted to make sure you are aware that the server doesn't know about / keep track of all the checked out working copies in the universe. The server doesn't know who to pus to. The client has to poll somehow or you need to do something specific for those clients in a post-commit hook script. I'll bet cron + svn update is your best friend right around now Peter -- Peter Valdemar Mørch http://www.morch.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: timestamp and DST: impossible to backup database? *AND* bugs in TIMEDIFF, FROM_UNIXTIME, et.al.?
From the lack of responses I take it that nobody disagrees that the handling of the timestamp type is fundamentally broken in every version of MySQL. I'll go ahead and file several bugs, and start changing our code to avoid the timestamp type altogther. I'm quite surprised! Peter Peter Valdemar Mørch swp5jhu02-at-sneakemail.com |Lists| wrote: *** Claim *** I seem to have discovered that MySQL cannot handle the hour where DST becomes non-DST reliably (on Oct 31st in CET): 2am 3am | problem | | time| --DST-/ / |/ / / /- non-DST -- Essentially, the problem seems to be that October 31, 2:15 am is ambiguous and MySQL cannot disambiguate between them. It seems that internally MySQL stores timestamp values with all timezone and DST/non-DST information intact. But all operations (e.g. TIMEDIFF(), FROM_UNIXTIME() and UPDATE TABLE SET timestamp=timestamp+0) destroy this important information and operate only now()'s timezone ignoring the appropriate original source time zone. I just find it impossibly difficult to believe I'm the first one finding this, so now I'm curious. Googling has come up short. I very much hope I'm mistaken and that I don't have to change our code, database format and contents to store # secs since 1970 GMT in an int everywhere and avoid timestamps altogether because they don't work... Evidence for claim Essentially we have a log table using a timestamp column as a sort key. For our logic to work we need to be able to: * Sort reliably, because the order of entries is very important (we use the last log entry to determine current state). Entries that are made during 2am-3am DST *must* come before log entries that are made 2am-3am non-DST just as they follow chronologically in real life. This works initially, but gets broken by a mysqldump db | mysql new_db cycle. Timestamp values in mysqldump output don't contain DST timezone information and hence of course it is lost during restore. There is no option to mysqldump to maintain this info in output. How does one backup a database then? (Other than cp or mysqlhotcopy which fails if host != localhost) * Be able to do TIMEDIFF() math and INSERT INTO TABLE ... VALUES(FROM_UNIXTIME(?)) maintaining the correct time. This is not possible. It seems that for all MySQL functions, the 2am-3am period (DST/non-DST) chosen depends on now(), not the value of ?, yeilding off-by-an-hour errors. As illustrated by this short example (more elaborate examples follow below): susan:~# cat /etc/timezone Europe/Copenhagen susan:~# mysql -e ' select 1099185600-1099181400; select TIMEDIFF(FROM_UNIXTIME(1099185600), FROM_UNIXTIME(1099181400));' +---+ | 1099185600-1099181400 | +---+ | 4200 | +---+ ++ | TIMEDIFF(FROM_UNIXTIME(1099185600), FROM_UNIXTIME(1099181400)) | ++ | 00:10:00 | ++ But 4200 secs is 01:10:00, not 00:10:00!!! Before we: 1) File an enhancement request against mysqldump 2) File bugs against UNIX_TIMESTAMP() and TIMEDIFF() et. al. 3) change our database format and code to avoid the timestamp type altogether and use secondsSince1970 int instead (huge) , I'd love to hear that I'm mistaken and that these problems can be solved with current mysql code. I've tried 4.1.7 and 4.0.22. * More examples * I've created a table only with an ID and a timestamp. I've tried filling it in a loop in two ways (perl source code link below): 1) modifying system time and inserting into table using implied timestamp=now(). This works (order by timestamp - IDs are in order): ++-+---+ | ID | timestamp | unix_timestamp(timestamp) | ++-+---+ | 1 | 2004-10-31 01:45:00 |1099179900 | | 2 | 2004-10-31 02:15:00 |1099181700 | | 3 | 2004-10-31 02:45:00 |1099183500 | | 4 | 2004-10-31 02:15:00 |1099185300 | | 5 | 2004-10-31 02:45:00 |1099187100 | | 6 | 2004-10-31 03:15:00 |1099188900 | ++-+---+ But TIMEDIFF() on this otherwise correct data is erroneous: select TIMEDIFF(A.timestamp, B.timestamp) from table as A, table as B where A.ID=4 and B.ID=2
timestamp and DST: impossible to backup database? *AND* bugs in TIMEDIFF, FROM_UNIXTIME, et.al.?
out of order, when order by timestamp! ++-+---+ | ID | timestamp | unix_timestamp(timestamp) | ++-+---+ | 1 | 2004-10-31 01:45:00 |1099179900 | | 2 | 2004-10-31 02:15:00 |1099185300 | | 4 | 2004-10-31 02:15:00 |1099185300 | | 3 | 2004-10-31 02:45:00 |1099187100 | | 5 | 2004-10-31 02:45:00 |1099187100 | | 6 | 2004-10-31 03:15:00 |1099188900 | ++-+---+ ^ | Out of numerical order 2) keeping now() in a single time zone and specifying timestamp=FROM_UNIXTIME($val) during insert. Specifying time fails (order by timestamp): - timestamps are duplicated - IDs are out of order! ++-+---+ | ID | timestamp | unix_timestamp(timestamp) | ++-+---+ | 1 | 2004-10-31 01:45:00 |1099179900 | | 2 | 2004-10-31 02:15:00 |1099185300 | | 4 | 2004-10-31 02:15:00 |1099185300 | | 3 | 2004-10-31 02:45:00 |1099187100 | | 5 | 2004-10-31 02:45:00 |1099187100 | | 6 | 2004-10-31 03:15:00 |1099188900 | ++-+---+ Extraordinary claims require extraordinary evidence. - Carl Sagan Sorry this is long but I couldn't make it much shorter... ** Source code to reproduce ** http://demo.capmon.dk/~pvm/mysqlTimestamps/timestamps.pl.txt Output of said code (WITH /etc/timezone == Europe/Copenhagen!!) http://demo.capmon.dk/~pvm/mysqlTimestamps/output.txt I really hope someone has a good idea what to do now! Peter -- Peter Valdemar Mørch http://www.morch.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
