| Bug #6034 | Error code 124: Wrong medium type | ||
|---|---|---|---|
| Submitted: | 11 Oct 2004 17:55 | Modified: | 27 Jan 2005 23:59 |
| Reporter: | Krzysztof Hryniewiecki | Email Updates: | |
| Status: | Closed | Impact on me: | |
| Category: | MySQL Server: MyISAM storage engine | Severity: | S3 (Non-critical) |
| Version: | 4.0.16 | OS: | Linux (RH Linux 9.2) |
| Assigned to: | Ingo Strüwing | CPU Architecture: | Any |
[11 Oct 2004 18:24]
MySQL Verification Team
Teste on latest BK source.
[13 Oct 2004 18:34]
Ingo Strüwing
The situation here is that the select part uses the same table as the insert part. The parser does not care and creates two table structures. One is opened for writing, the other for reading. On the MyISAM level the match is detected. Both tables share the file descriptors and other information in a structure called MYISAM_SHARE. At the start of the INSERT SELECT command, the non-unique indexes for the insert table are deactivated. This improves the insert performance (but requires an implicit repair afterwards). The information about active and inactive indexes is contained in a bit vector, which is part of the MYISAM_SHARE structure. This means, in this case, that the non-unique indexes of the select table are diabled too. But since the table structues are distinct on the SQL level, this is not noticed here. The last piece in the puzzle is the optimizer. It decides that, in this case, the index 'TEST$NO' is the best path to get at MAX(NO). But this is a non-unique index, which is disabled... Possible solutions could be: 1. abstain from disabling indexes for INSERT SELECT, 2. update the keys_in_use of all select tables after the preparation phase. This is a design decision, which I have to discuss with other developers.
[18 Oct 2004 18:39]
Ingo Strüwing
bk commit - 4.0 tree (ingo:1.2039) BUG#6034
[13 Jan 2005 12:57]
Ingo Strüwing
Second attempt: bk commit - 4.0 tree (ingo:1.2025) BUG#6034 Date: Thu, 13 Jan 2005 13:45:45 +0100
[27 Jan 2005 18:44]
Ingo Strüwing
Fixed in 4.0.24.
[27 Jan 2005 23:59]
Paul DuBois
Mentioned in 4.0.24 change note.
Description: Rasing error 124 How to repeat: CREATE TABLE `TEST` ( `ID` int(11) NOT NULL auto_increment, `NO` int(11) NOT NULL default '0', `SEQ` int(11) NOT NULL default '0', PRIMARY KEY (`ID`), KEY `TEST$NO` (`SEQ`,`NO`) ) TYPE=MyISAM; INSERT INTO TEST (SEQ, NO) SELECT "1" AS SEQ, IF(MAX(NO) IS NULL, 0, MAX(NO)) + 1 AS NO FROM TEST WHERE (SEQ = 1); Suggested fix: 1. ALTER TABLE TEST DROP KEY TEST$NO; 2. In some cases, the error is raised only if table is empty. So, in these cases, the fix is to add one "dummy" record.