Unknown Collation [FIXED] : utf8mb4_0900_ai_ci In MYSQL

Unknown Collation [FIXED] : utf8mb4_0900_ai_ci In MYSQL

Published Jan 12,2023 by Kailash Singh

0 Comment     9911 Views    

In this post, we are going to teach you, how to fixed unknown collation  utf8mb4_0900_ai_ci error In MYSQL.

Unknown collation: 'utf8mb4_0900_ai_ci': This error is because the local server and live server have different versions of MYSQL. It means the error is because of a different version of MYSQL from were you exporting your SQL database and where you importing also a different version of MYSQL.

To solve this error follow the step guideline:

1. Open your .sql file in any editor(Notepad), Which you imported from the MYSQL server.
2. Find 'utf8mb4_0900_ai_ci' With given table name.
3. Replace and save the .sql file.
4. Upload it to the MYSQL server.

Error Line in your SQL file: 

ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

Solution: Open your .sql file, and replace it with this code.

ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_general_ci;

Previous  SQL Example: 

CREATE TABLE `studnet` (
  `id` bigint UNSIGNED NOT NULL,
  `name` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL,
  `class` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL
  `created_at` timestamp NULL DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;  // REMOVE THIS LINE

Correct SQL Example:

CREATE TABLE `studnet` (
  `id` bigint UNSIGNED NOT NULL,
  `name` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL,
  `class` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL
  `created_at` timestamp NULL DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_general_ci; // ADD THIS LINE