Bug #48228 UNIQUE_KEY_FLAG not respected for multiple columns in unique key
| Submitted: | 22 Oct 2009 13:17 | ||
|---|---|---|---|
| Reporter: | Tonci Grgin | Email Updates: | |
| Status: | Verified | Impact on me: | |
| Category: | MySQL Server: DDL | Severity: | S2 (Serious) |
| Version: | 5.0.88, 5.1.31, 5.1.41 | OS: | Any |
| Assigned to: | CPU Architecture: | Any |
[22 Oct 2009 13:17]
Tonci Grgin
[22 Oct 2009 13:23]
Valeriy Kravchuk
Verified with recent 5.0.88 and 5.1.41 from bzr on Mac OS X also.
[22 Oct 2009 13:42]
Tonci Grgin
Actually, Field 3 is *not* missing MULTIPLE_KEY_FLAG but it is wrongly put into Field 2 according to http://dev.mysql.com/doc/refman/5.1/en/c-api-datatypes.html: MULTIPLE_KEY_FLAG Field is part of a nonunique key Maybe docs are wrong?
[22 Oct 2009 14:47]
Paul DuBois
MULTIPLE_KEY is set if an index can contain multiple identical values. This can occur even for a UNIQUE index, if the columns in the index allow NULL values (as is the case for the keys_test table), because such a UNIQUE index allows multiple NULL entries. It does appear that MULTIPLE_KEY is set only for the *first* column of the index. mysql> desc keys_test; +-------+------------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +-------+------------+------+-----+---------+----------------+ | Id | int(11) | NO | PRI | NULL | auto_increment | | col2 | varchar(5) | YES | MUL | NULL | | | col3 | varchar(5) | YES | | NULL | | | col4 | varchar(5) | YES | | NULL | | | col5 | blob | YES | | NULL | | | col6 | int(11) | YES | UNI | NULL | | +-------+------------+------+-----+---------+----------------+
[22 Oct 2009 15:01]
Tonci Grgin
Somewhere around middle of first comment: Field 3 is missing UNIQUE_KEY_FLAG and MULTIPLE_KEY_FLAG It does not matter if fields are nullable or not. <<<
[22 Oct 2009 15:01]
Paul DuBois
Well, I tried again after declaring col2 and col3 as NOT NULL, and the MUL value is still the same in the DESC output. I don't know how to account for that.
[16 Apr 2013 15:15]
Igor Solodovnikov
This is not a CAPI bug. It is about how table column metadata is handled in the server.
Portions of this website are copyright © 2001, 2002 The PHP Group
Page generated in 0.057 sec. using MySQL 8.4.7-u6-cloud
Timestamp references displayed by the system are UTC.
Content reproduced on this site is the property of the
respective copyright holders.
It is not reviewed in advance
by Oracle and does not necessarily represent the opinion of
Oracle or any other party.