Geek Answers Handbook

MySQL UTF8 migration to UTF8MB4 problems and issues

I am trying to convert my UTF8 MySQL 5.5.30 database to UTF8MB4. I looked at this article https://mathiasbynens.be/notes/mysql-utf8mb4, but I have a few questions.

I did it

ALTER DATABASE database_name CHARACTER SET = utf8mb4 COLLATE = utf8mb4_unicode_ci;
ALTER TABLE table_name CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

The latter was done manually with 62 tables, one of them gave me this warning

13:08:30 ALTER TABLE bradspelold.games CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci 101289 lines, 2 warnings: 1071 The specified key is too long; The maximum key length is 767 bytes. 1071 The specified key is too long; The maximum key length is 767 bytes. Records: 101289, duplicates: 0, warnings: 2, 3.016 sec.

  1. This is problem? What could I do to fix this?

The next step

ALTER TABLE table_name CHANGE column_name column_name
         VARCHAR(191) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
  1. , 2 column_name?
  2. VARCHAR (191)? , - ?
  3. - , , ?

:

Table Show

CREATE  TABLE 'games' (
        'id' int(10) unsigned NOT NULL DEFAULT \'0\',
        'name' varchar(255) NOT NULL,
        'description' mediumtext,
        'yearPublished' datetime NOT NULL,
        'minPlayers' int(10) unsigned NOT NULL,
        'maxPlayers' int(10) unsigned NOT NULL,
        'playingTime' varchar(127) NOT NULL,
        'grade' double NOT NULL DEFAULT \'0\',
        'updated' datetime NOT NULL,
        'forumParentId' int(10) unsigned DEFAULT \'0\',
        'lastVisited' datetime DEFAULT NULL,
        'inactivatedDate' datetime DEFAULT NULL,
        'bggGrade' double DEFAULT NULL,
        PRIMARY KEY ('id'),
        KEY 'inactivatedDate' ('inactivatedDate'),
        KEY 'name' ('name')
) ENGINE=InnoDB DEFAULT CHARSET=utf8'

2:

    'CREATE TABLE 'forum_threads' (
      'id' int(10) unsigned NOT NULL AUTO_INCREMENT,
      'title' varchar(150) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT '''',
      'description' varchar(150) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT '''',
      'createdDate' datetime NOT NULL DEFAULT ''0000-00-00 00:00:00'',
      'createrId' int(10) unsigned DEFAULT NULL,
      'replys' int(10) unsigned NOT NULL DEFAULT ''0'',
      'lastPostUserId' int(10) unsigned DEFAULT NULL,
      'lastPostId' int(10) unsigned DEFAULT NULL,
      'forumId' int(10) unsigned DEFAULT NULL,
      'visits' int(10) unsigned NOT NULL DEFAULT ''0'',
      'lastPostCreated' datetime NOT NULL DEFAULT ''0000-00-00 00:00:00'',
      'lastPostNickName' varchar(30) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT '''',
      'createrNickName' varchar(30) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT '''',
      'solved' tinyint(1) NOT NULL DEFAULT ''0'',
      'locked' tinyint(1) NOT NULL DEFAULT ''0'',
      'lockedByUserId' int(10) unsigned NOT NULL DEFAULT ''0'',
      'lockedDate' datetime NOT NULL DEFAULT ''0000-00-00 00:00:00'',
      'alteredDate' datetime NOT NULL DEFAULT ''0000-00-00 00:00:00'',
      'alteredUserId' int(10) unsigned DEFAULT NULL,
      'glued' tinyint(1) NOT NULL DEFAULT ''0'',
      'pollId' int(10) unsigned DEFAULT NULL,
      'facebookPostId' bigint(20) DEFAULT NULL,
      'facebookImportedDate' datetime DEFAULT NULL,
      PRIMARY KEY ('id'),
      KEY 'FK_forum_threads_1' ('forumId'),
      KEY 'FK_forum_threads_2' ('pollId'),
      KEY 'createdDate' ('createdDate'),
      KEY 'createrId' ('createrId'),
      KEY 'lastPostCreated' ('lastPostCreated'),
      CONSTRAINT 'FK_forum_threads_1' FOREIGN KEY ('forumId') REFERENCES 'forum' ('id') ON DELETE CASCADE
    ) ENGINE=InnoDB AUTO_INCREMENT=4306 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci'

'CREATE TABLE 'forum' (
  'id' int(10) unsigned NOT NULL AUTO_INCREMENT,
  'title' varchar(80) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT '''',
  'description' varchar(150) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT '''',
  'createdDate' datetime NOT NULL DEFAULT ''0000-00-00 00:00:00'',
  'threads' int(10) unsigned NOT NULL DEFAULT ''0'',
  'createrId' int(10) unsigned DEFAULT NULL,
  'lastPostUserId' int(10) unsigned DEFAULT NULL,
  'lastThreadId' int(10) unsigned DEFAULT NULL,
  'parentForumId' int(10) unsigned DEFAULT NULL,
  'lastPostNickName' varchar(30) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT '''',
  'lastPostCreated' datetime NOT NULL DEFAULT ''0000-00-00 00:00:00'',
  'lastThreadTitle' varchar(80) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT '''',
  'alteredDate' datetime NOT NULL DEFAULT ''0000-00-00 00:00:00'',
  'alteredUserId' int(10) unsigned DEFAULT NULL,
  'placeOrder' int(10) unsigned NOT NULL DEFAULT ''0'',
  'separator' tinyint(1) NOT NULL DEFAULT ''0'',
  'rightLevel' int(10) unsigned NOT NULL DEFAULT ''1'',
  'createChildForum' tinyint(3) unsigned NOT NULL DEFAULT ''1'',
  'createThreads' tinyint(3) unsigned NOT NULL DEFAULT ''1'',
  PRIMARY KEY ('id'),
  KEY 'Index_1' ('id','parentForumId')
) ENGINE=InnoDB AUTO_INCREMENT=375 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci'
+6
3
  • INDEX. , utf8mb4 4 , , utf8 3. INDEX .

"" , . ()

2.

ALTER TABLE t CHANGE col col ...


ALTER TABLE t MODIFY col ...

, , , .

  1. , VARCHAR(255), 767 utf8 (3 * 255 + 2, "2" - ). 4- utf8mb4 (191) (4 * 191 + 2 = 766, 191).

  2. . , , , - , .

...

A. foo VARCHAR(255), utf8? ( ) 191 ? , ALTER.

B. 191, INDEX? DROP INDEX .

C. : INDEX(foo(191)), VARCHAR(255). "" , , .

, , SHOW CREATE TABLE INDEX.

+5
DB="database_name"
USER="mysql_user"
PASS="mysql_password"
(
    echo 'ALTER DATABASE `'"$DB"'` CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;'
    mysql -p$PASS -u $USER "$DB" -e "SHOW TABLES" --batch --skip-column-names \
    | xargs -I{} echo 'ALTER TABLE `'{}'` CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;'
) \
| mysql -p$PASS -u $USER "$DB"

  • script, :

    • nano convert_to_utf8mb4.sh
    • script
    • sudo chmod 755 convert_to_utf8mb4.sh( )
    • script . /convert _to_utf8mb4.sh

    , !

+3

this is an old question, but following some of the answers here 5 years later, as I discovered, is a bad idea. Do not resize the fields VARCHAR, you can damage your data and ruin everything.

In current versions of MySQL and MaraiDB, add this to your config and it will support the larger keys needed for UTF8mb4

innodb_large_prefix=1

I also suggest adding innodb_file_per_table =1 innodb_file_format=Barracuda

then the conversion will occur without errors / warnings about the key length

0
source

More articles:


All Articles