one of the application server is internally creating the database on my mysql
but when ever the following create table command gets executed
CREATE TABLE ofRoster (
rosterID BIGINT NOT NULL,
username VARCHAR(64) NOT NULL,
jid VARCHAR(1024) NOT NULL,
sub TINYINT NOT NULL,
ask TINYINT NOT NULL,
recv TINYINT NOT NULL,
nick VARCHAR(255),
PRIMARY KEY (rosterID),
INDEX ofRoster_unameid_idx (username),
INDEX ofRoster_jid_idx (jid)
)
i am getting following error
ERROR 1071 (42000): Specified key was too long; max key length is 1000 bytes
i have set my default engine to MyISAM
because i was getting following error in InnoDB
specified key was too long max key length is 767 bytes
my current engines are as follows
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
| Engine | Support | Comment | Transactions | XA | Savepoints |
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
| FEDERATED | NO | Federated MySQL storage engine | NULL | NULL | NULL |
| MRG_MYISAM | YES | Collection of identical MyISAM tables | NO | NO | NO |
| MyISAM | DEFAULT | MyISAM storage engine | NO | NO | NO |
| BLACKHOLE | YES | /dev/null storage engine (anything you write to it disappears) | NO | NO | NO |
| CSV | YES | CSV storage engine | NO | NO | NO |
| MEMORY | YES | Hash based, stored in memory, useful for temporary tables | NO | NO | NO |
| ARCHIVE | YES | Archive storage engine | NO | NO | NO |
| InnoDB | YES | Supports transactions, row-level locking, and foreign keys | YES | YES | YES |
| PERFORMANCE_SCHEMA | YES | Performance Schema | NO | NO | NO |
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
now i really don't know how would i get rid off it as the application server itself is creating database automatically in mysql so i dont have control over it.
i am using Server version: 5.6.10 MySQL Community Server (GPL)
version
character set of column
+--------------------------+--------------------------------------------------------+
| Variable_name | Value |
+--------------------------+--------------------------------------------------------+
| character_set_client | utf8 |
| character_set_connection | utf8 |
| character_set_database | latin1 |
| character_set_filesystem | binary |
| character_set_results | utf8 |
| character_set_server | latin1 |
| character_set_system | utf8 |
| character_sets_dir | /usr/local/mysql-5.6.10-osx10.6-x86_64/share/charsets/ |
+--------------------------+--------------------------------------------------------+
Best Answer
You can increase the maximum InnoDB index prefix size in MySQL 5.6 to 3072 bytes by setting
innodb_large_prefix
toON
along with other settings that you'll also need in order to enable that one, discussed here:http://dev.mysql.com/doc/refman/5.6/en/innodb-parameters.html#sysvar_innodb_large_prefix
These changes should allow these indexes to be valid for InnoDB tables.
With a character set of (I assume)
utf8
, aVARCHAR(1024)
would need 1024 x 3 = 3072 bytes for its index.Updates:
I incorrectly showed the value of the variable as
Yes
when it should have beenON
.But also:
When I thought about this, it sounded correct, because 1 byte is needed to store the size of the value when the value is 255 bytes or less, and 2 bytes are needed otherwise.
However, testing reveals that this isn't the case, in this case -- InnoDB using the
COMPRESSED
row format from Barracuda can actually index the full size of aVARCHAR(1024)
... so either they've documented it strangely or theCOMPRESSED
row format stores the length out-of-band along with another block of metadata, so it doesn't count in the total bytes in this format.COMPRESSED
doesn't truncate the index into a prefix index and throw a warning until you go toVARCHAR(1025)
.It's nice the way it throws a warning instead of throwing an error, but that doesn't help us here because this still requires the explicit
ROW_FORMAT
declaration to trigger this behavior.So, my initial answer is still wrong, because you have to explicitly add
ROW_FORMAT=COMPRESSED
to the end of the table definition. Otherwise you still get the same old error.Actually, you get two different errors in 5.6.10. If you try to create a table with a fully-indexed
VARCHAR(1024)
you get this:...but if you try with a fully-indexed
VARCHAR(1025)
you get this:That's sloppy code but the bottom line is that my answer doesn't actually fix this problem.
I don't see a way to use
ROW_FORMAT=COMPRESSED
by default, nor does it seem like a good idea if it were possible, and I'm inclined to suggest that the answer is......there's not a readily available workaround here. My other thought was "character sets" but the difference between
latin1
andutf8
still isn't sufficient to explain 1,024 vs 1000 or 767. I'll happily get behind a better idea but at the moment, I can't think of a version of MySQL Server that this code would work properly on.