2

I have a databases with latin1 charachter set, I need to change tables character set to utf8, but I get error. the current character set is : 

mysql> show variables like "%character%"; +--------------------------+----------------------------+ | 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/share/mysql/charsets/ | +--------------------------+----------------------------+

the table that I want to change the character set is : 

mysql> describe spool; +------------+---------------------+------+-----+-------------------+----------------+ | Field | Type | Null | Key | Default | Extra | +------------+---------------------+------+-----+-------------------+----------------+ | username | varchar(250) | NO | MUL | NULL | | | xml | text | NO | | NULL | | | seq | bigint(20) unsigned | NO | PRI | NULL | auto_increment | | created_at | timestamp | NO | | CURRENT_TIMESTAMP | | +------------+---------------------+------+-----+-------------------+----------------+ 4 rows in set (0.00 sec)

when I want to change the character set I see bellow error : 

mysql> ALTER TABLE spool CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci; ERROR 1071 (42000): Specified key was too long; max key length is 767 bytes

It's a company functioning database and I don't want to disturb the functionality, so I don't want to change the size of the varchar(250) type, I wonder if there is a solution to change this encoding.

mysql> show create table spool\G; *************************** 1. row *************************** Table: spool Create Table: CREATE TABLE `spool` ( `username` varchar(250) NOT NULL, `xml` text NOT NULL, `seq` bigint(20) unsigned NOT NULL AUTO_INCREMENT, `created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP, UNIQUE KEY `seq` (`seq`), KEY `i_despool` (`username`) USING BTREE ) ENGINE=InnoDB AUTO_INCREMENT=23490775 DEFAULT CHARSET=utf8
Improve this question
2
  • 1
    Please add results of show create table spool;, it is more informative than describe. Commented Mar 1, 2016 at 12:21
  • 1
    dba.stackexchange.com/questions/49913/… - or use prefix index username(180) if applicable. Commented Mar 1, 2016 at 15:38

1 Answer 1

Reset to default
0

Solved :

I solve this problem, by creating new table like this spool. when we want to create a table with utf8mb4 COLLATE utf8mb4_unicode_ci character set we can not use varchar(250) because 250 * 4 = 1000 (4 byte per character in this new encoding). 1000 is more than max key 767 byte for this encoding. so I create a table spoolll with varchar(100) :

CREATE TABLE `spoollll` ( `username` varchar(100) NOT NULL, `xml` text NOT NULL, `seq` bigint(20) unsigned NOT NULL AUTO_INCREMENT, `created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP, UNIQUE KEY `seq` (`seq`), KEY `i_despool` (`username`) USING BTREE ) ENGINE=InnoDB AUTO_INCREMENT=23492667 DEFAULT CHARSET=utf8

and now I change the character set : 

mysql> ALTER TABLE spoollll CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci; Query OK, 0 rows affected (0.03 sec) Records: 0 Duplicates: 0 Warnings: 0

I would appreciate if someone comment on it and complete this issue.

Improve this answer

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.