| Bug #3878 | key on TEXT column fails | ||
|---|---|---|---|
| Submitted: | 25 May 2004 2:20 | Modified: | 25 May 2004 10:36 |
| Reporter: | Scott Douglass | Email Updates: | |
| Status: | Closed | Impact on me: | |
| Category: | MySQL Server | Severity: | S3 (Non-critical) |
| Version: | 4.1.1-alpha | OS: | Linux (Linux x86/Fedora 2) |
| Assigned to: | CPU Architecture: | Any | |
[25 May 2004 3:02]
Scott Douglass
My fault. I built mysql setting the default character set to UTF-8. As a result of the larger number of bytes required per character, I found that using MEDIUMTEXT instead of TEXT for the column type was required to use 255 characters of it as the key.
[25 May 2004 3:45]
MySQL Verification Team
According with your own analyze.
[25 May 2004 4:01]
Scott Douglass
This is a bytes vs. characters issue. With the database compiled to use UTF-8 by default, the largest size (in characters) for a key on a TEXT column is 84. This corresponds approximately to 255 bytes (84 characters x 3 bytes per character). Probably something should be done inside mysql to make this bytes vs. characters issue more transparent to the SQL user.
[25 May 2004 10:36]
Sergei Golubchik
Thank you for your bug report. This issue has been committed to our
source repository of that product and will be incorporated into the
next release.
If necessary, you can access the source repository and build the latest
available version, including the bugfix, yourself. More information
about accessing the source trees is available at
http://www.mysql.com/doc/en/Installing_source_tree.html
Additional info:
max key length was already increased in 4.1.2. Wait for 4.1.2 to be out (very soon) or try a source snapshot.
Description: The database schema for the jabberd2 instant messaging server includes this table definition: CREATE TABLE `active` ( `collection-owner` TEXT NOT NULL, PRIMARY KEY(`collection-owner`(255)), `object-sequence` BIGINT NOT NULL AUTO_INCREMENT, KEY(`object-sequence`), `time` INT ); This SQL executes without error in MySQL 4.0.17, but generates the following error in 4.1.1-alpha: ERROR 1089 at line 30: Incorrect sub part key. The used key part isn't a string, the used length is longer than the key part or the storage engine doesn't support unique sub keys How to repeat: Run the following SQL in MySQL 4.1.1-alpha: CREATE TABLE `active` ( `collection-owner` TEXT NOT NULL, PRIMARY KEY(`collection-owner`(255)), `object-sequence` BIGINT NOT NULL AUTO_INCREMENT, KEY(`object-sequence`), `time` INT );