MySQL Bugs: #6034: Error code 124: Wrong medium type

Bug #6034	Error code 124: Wrong medium type
Submitted:	11 Oct 2004 19:55	Modified:	28 Jan 2005 0:59
Reporter:	Krzysztof Hryniewiecki
Status:	Closed
Category:	Server: MyISAM	Severity:	S3 (Non-critical)
Version:	4.0.16	OS:	Linux (RH Linux 9.2)
Assigned to:	Ingo Strüwing	Target Version:

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.

Teste on latest BK source.

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.

bk commit - 4.0 tree (ingo:1.2039) BUG#6034

Second attempt: bk commit - 4.0 tree (ingo:1.2025) BUG#6034
Date: Thu, 13 Jan 2005 13:45:45 +0100

Fixed in 4.0.24.

Mentioned in 4.0.24 change note.