Garth Webb <[EMAIL PROTECTED]> wrote:
>
> 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.
>
Recreate indexes and see if it helps.
--
For technical support contracts, goto https://order.mysql.com/?ref=ensita
This email is sponsored by Ensita.net http://www.ensita.net/
__ ___ ___ ____ __
/ |/ /_ __/ __/ __ \/ / Egor Egorov
/ /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED]
/_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.net
<___/ www.mysql.com
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
