Hi, I am experiencing strange behavior from a TIMESTAMP column in an InnoDB table on my MySQL 4.0.16 server. Specifically, it fails to select certain rows by this column. The table is defined as:
CREATE TABLE log (
s_id int(11) not null,
p_id int(11) not null,
action enum('A', 'B', 'C'),
tstamp timestamp(14),
index ( s_id, p_id ),
index ( tstamp )
);
My problem exists for the first few rows in this table. For example,
take the first row by date:
mysql> select min(tstamp) from log;
+----------------+
| min(tstamp) |
+----------------+
| 20031026010002 |
+----------------+
Now consider the following attempts to select this row:
mysql> select * from log where tstamp = 20031026010002;
mysql> select * from log where tstamp = '20031026010002';
mysql> select * from log where tstamp = "20031026010002";
mysql> select * from log where tstamp = "2003-10-26 01:00:02";
mysql> select * from log where tstamp > "20031026010001"
-> and tstamp < "20031026010003";
None of these return any rows. To add insult to injury, this database
is replicated, and on the *replicated* database, ALL of these queries
work. The only query by date that work on the main database is:
mysql> select * from log where tstamp < "20031026010003"
Which doesn't make any sense considering that the date range query
didn't work.
It seemed like this table must be corrupted somehow, so I ran a 'check
table' on it which return a status of OK.
Unfortunately I cannot replicate this problem, so my hope is that
someone else has experienced this as well, or that someone can suggest a
method to repair this table.
Some final notes. While this problem may have existed previously, I
only noticed it when I began deleting old log data by date via a
script. Furthermore, only the first 2000 or so earliest dates cannot be
selected. All dates after this 'blackout' period can be selected
without a problem.
--
|- Garth Webb -|
|- [EMAIL PROTECTED] -|
signature.asc
Description: This is a digitally signed message part
