USE test;
DROP TABLE IF EXISTS foo;
CREATE TABLE foo (
id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
d CHAR(10) NOT NULL,
dt DATETIME NOT NULL,
KEY (d),
KEY (dt)
) ENGINE = MyISAM;
## Fill with 100 random records:
INSERT INTO foo (d,dt) VALUES ('XXHLSVJQWE', '2005-01-31 08:32:09');
## ...
INSERT INTO foo (d,dt) VALUES ('TLMXIQUAHY', '2005-04-08 09:11:48');
## Run (bogus) query
SELECT
f1.id,
f2.id
FROM foo f1
JOIN foo AS f2 ON f2.dt < f1.dt AND f2.d LIKE 'B%'
WHERE
f1.d LIKE 'B%';
## Result:
## ERROR 126 (HY000): Incorrect key file for table './test/foo.MYI'; try to repair it
REPAIR TABLE foo;
## Result:
## +----------+--------+----------+----------+
## | Table | Op | Msg_type | Msg_text |
## +----------+--------+----------+----------+
## | test.foo | repair | status | OK |
## +----------+--------+----------+----------+
## 1 row in set (0.49 sec)
## Query again
SELECT
f1.id,
f2.id
FROM foo f1
JOIN foo AS f2 ON f2.dt < f1.dt AND f2.d LIKE 'B%'
WHERE
f1.d LIKE 'B%';
## Same error.
## MySQL version 5.0.11-beta-standard-log on
## Red Hat Enterprise Linux ES release 3 (Taroon Update 4)
## Kernel 2.4.21-27.ELsmp
## Doing the same on MySQL 5.0.3-beta-nt gives
## ERROR 1030 (HY000): Got error 124 from storage engine
## No problems when table is InnoDB.
--
felix
Please use
BBCode to format your messages in this forum.