I have that error"#1273 - Unknown collation: 'utf8mb4_0900_ai_ci'"

Question

CREATE TABLE `payment_methods` (
  `payment_method_id` tinyint(4) NOT NULL AUTO_INCREMENT,
  `name` varchar(50) NOT NULL,
  PRIMARY KEY (`payment_method_id`)
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

Godfry

Answer 1

CREATE TABLE payment_methods 
    ( payment_method_id tinyint(4) NOT NULL AUTO_INCREMENT, 
      name varchar(50) NOT NULL, 
      PRIMARY KEY (payment_method_id) 
    ) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

NOTE: COLLATE=utf8mb4_0900_ai_ci replace with COLLATE=utf8mb4_general_ci

REFER: https://www.freakyjolly.com/resolved-when-i-faced-1273-unknown-collation-utf8mb4_0900_ai_ci-error/#.X2jHBGgzZPY

Rationalism

Answer 2

utf8mb4_0900_ai_ci is a collation that is new in MySQL 8.0

It is not recognized in earlier versions of MySQL, for example 5.7.

It may also not be recognized in forks of MySQL such as MariaDB.

You should only use that collation in MySQL 8.0.

Consult documentation for the version of software you are using to see the set of supported collations.

For example: https://dev.mysql.com/doc/refman/5.7/en/charset-charsets.html

Or query INFORMATION_SCHEMA.COLLATIONS to find the list.

Afoul

Or SHOW COLLATION; – Mattos

Answer 3

If you are working on a local server like XAMPP/WAMP which runs MariaDB, it would use utf8mb4_unicode_520_ci instead of MySQL's utf8mb4_0900_ai_ci.

To fix the issue:

Replace COLLATE=utf8mb4_0900_ai_ci with COLLATE=utf8mb4_unicode_520_ci.

Hewe