Home / MySQL

ERROR 1273 (HY000): Unknown collation: 'utf8mb4_0900_ai_ci'

Posted on:2019-10-07 Views:5503 Words:178

Comment function of my blog website was broken with error:

SQLSTATE[42S02]: Base table or view not found: 1146 Table ‘notefeel.spam_ip’ doesn’t exist

But when I create this lost table with SQL, error shows:

mysql> CREATE TABLE `spam_ip` (
    ->   `id` int(11) NOT NULL AUTO_INCREMENT,
    ->   `ip` varchar(45) NOT NULL,
    ->   `detail` text,
    ->   `created_at` datetime DEFAULT NULL,
    ->   `updated_at` datetime DEFAULT NULL,
    ->   PRIMARY KEY (`id`),
    ->   UNIQUE KEY `ip_UNIQUE` (`ip`)
    -> ) ENGINE=InnoDB AUTO_INCREMENT=365 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

ERROR 1273 (HY000): Unknown collation: ‘utf8mb4_0900_ai_ci’
It looks like utf8mb4_0900_ai_ci is supported on MySQL 8.0 of my develop PC, but not on MySQL 5.7.

$ mysqld --version
mysqld  Ver 5.7.27-0ubuntu0.18.04.1 for Linux on x86_64 ((Ubuntu))

So I change utf8mb4_0900_ai_ci to utf8mb4_unicode_ci:

mysql> CREATE TABLE `spam_ip` (
    ->   `id` int(11) NOT NULL AUTO_INCREMENT,
    ->   `ip` varchar(45) NOT NULL,
    ->   `detail` text,
    ->   `created_at` datetime DEFAULT NULL,
    ->   `updated_at` datetime DEFAULT NULL,
    ->   PRIMARY KEY (`id`),
    ->   UNIQUE KEY `ip_UNIQUE` (`ip`)
    -> ) ENGINE=InnoDB AUTO_INCREMENT=365 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
Query OK, 0 rows affected (0.04 sec)

Then it works.