As soon as the new month rolled over, it happened again.
The problem went away sometime in mid-May. The only difference between
the current problems and the old is that we're now running 3.23.38.
Any ideas?
Chris
--
Forwarded message:
From [EMAIL PROTECTED] Tue May 1 14:37:53 2001
Date: Tue, 1 May 2001 14:37:53 -0500
From: Christopher P. Lindsey [EMAIL PROTECTED]
To: [EMAIL PROTECTED]
Subject: Dates not compared properly?
Message-ID: [EMAIL PROTECTED]
Mime-Version: 1.0
Content-Type: text/plain; charset=us-ascii
X-Mailer: Mutt 1.0.1i
Content-Length: 2290
Lines: 63
Howdy all...
I've run into a strange date problem as of midnight, May 1, 2001 on my
i686 Linux machine running mySQL 3.23.37.
For some reason, queries can no longer compare dates properly:
mysql select count(*) from hit where date NOW()-1135560;
+--+
| count(*) |
+--+
| 2633 |
+--+
1 row in set (2.05 sec)
mysql select count(*) from hit where date NOW()-1145560;
+--+
| count(*) |
+--+
|0 |
+--+
1 row in set (2.05 sec)
mysql select NOW()-1135560,NOW()-1145560;
+++
| NOW()-1135560 | NOW()-1145560 |
+++
| 2001057384 | 2001047384 |
+++
1 row in set (0.00 sec)
mysql describe hit;
++--+--+-+-+---+
| Field | Type | Null | Key | Default | Extra |
++--+--+-+-+---+
| dig_id | int(10) unsigned | | MUL | 0 | |
| date | timestamp(14)| YES | | NULL| |
| ip | varchar(16) | YES | | NULL| |
| site | varchar(255) | YES | | NULL| |
++--+--+-+-+---+
NOW() is being expanded properly, yet for some reason the comparison
of hit.date with NOW()-n fails when n is larger than an abitrary number.
As it becomes later and later in the day, n becomes larger and larger
(so eventually it will reach 1970 and everything will work again).
I've optimized the tables, but don't really have any other good ideas.
For now, I'm just doing
select count(*) as hits, substring(date,1,8) as foo from hit where ip NOT like
x.x.x.% AND ip != x.x.x.x group by foo order by foo desc limit 10;
to get the count of most recent hits instead of the faster
select count(*) as hits, substring(date,1,8) as whee from hit where date NOW()
- 1000 AND ip NOT like 64.5.98.% AND ip != 141.142.22.95 group by whee order
by whee desc;
Any ideas?
Thanks,
Chris
P.S. For those who are curious, the database tracks views of plant photos
on my Web site in real-time, including sites that have inlined.
-
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail [EMAIL PROTECTED]
To unsubscribe, e-mail [EMAIL PROTECTED]
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
