[FIXED] Unknown Collation: utf8mb4_0900_ai_ci In MYSQL

  • 03-08-2022
  • 899
  • MYSQL
  • Haresh Chauhan

Unknown collation: 'utf8mb4_0900_ai_ci'; This error is because the local server and live server have different versions of MYSQL. To solve this error follow the step guideline.

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.

  • 1. Open your .sql file in any editor, Which you imported from the MYSQL server.
  • 2. Find 'utf8mb4_0900_ai_ci' With given table name.
  • 3. Replace and save the .sqi file and upload it to the MYSQL server.
ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

Goto to your .sql file, and replace it with

ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_general_ci;

Example With Posts Table

CREATE TABLE `posts` (
  `id` bigint UNSIGNED NOT NULL,
  `title` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL,
  `post_description` text CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL,
  `deleted_at` timestamp NULL DEFAULT NULL,
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL,
  `source_code` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;  // REMOVE THIS

Replace with the below ENGINE=InnoDB.

CREATE TABLE `posts` (
  `id` bigint UNSIGNED NOT NULL,
  `title` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL,
  `post_description` text CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL,
  `deleted_at` timestamp NULL DEFAULT NULL,
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL,
  `source_code` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_general_ci; // ADD THIS

We always thanks to you for reading our blogs.


dharmesh-image

Dharmesh Chauhan

(Swapinfoway Founder)

Hello Sir, We are brothers origin from Gujarat India, Fullstack developers working together since 2016. We have lots of skills in web development in different technologies here I mention PHP, Laravel, Javascript, Vuejs, Ajax, API, Payment Gateway Integration, Database, HTML5, CSS3, and Server Administration. So you need our service Please Contact Us

haresh-image

Haresh Chauhan

(Co-Founder)


We Are Also Recommending You :