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
up vote 0 down vote accepted

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.

Your Answer

 
discard

By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.

Not the answer you're looking for? Browse other questions tagged or ask your own question.