SQL Import fails

[TeeruKu]

Website URL

NA this is a MySQL query

Error Message

#1253 - COLLATION ‘utf8mb4_unicode_ci’ is not valid for CHARACTER SET ‘utf8’

Other Information

I am trying to import a MySQL Database but am getting the error shown. any ideas?

(other information and details relevant to your question)

[Greenreader9]

Can you please share the SQL statement you are trying to execute?

[Admin]

Every database, table and column have a charset (that determines how text is stored on the system) and a collation (that determines how text is compared). Which collation can be used depends on the charset being used.

The error message tells me that the database/table/column is using the utf8 charset, but is configured to use a collation intended for the utf8mb4 charset. That’s not possible of course.

As to why this is happening to your import, a few possible reasons come to mind:

• The database server you’ve exported from has some inconsistent charset configuration, older MySQL versions tend to be more forgiving in this regard.
• The database import does specify a collation but not a charset, so MySQL defaults to the standard charset on our server, which is utf8.

If you still have access to the original database, you could try exporting and importing the tables one by one and see which one is causing this issue. You can then try to fix this issue for the table. If you need help doing that, please export the structure/schema of the table and share it here.

If you don’t have access to the original database, we can still make it work, but it’s harder and will involve manual edits to the export.

[TeeruKu]

Here is the database table schema

DROP TABLE IF EXISTS `chv_albums`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!50503 SET character_set_client = utf8mb4 */;
CREATE TABLE `chv_albums` (
  `album_id` bigint NOT NULL AUTO_INCREMENT,
  `album_name` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL,
  `album_user_id` bigint DEFAULT NULL,
  `album_date` datetime NOT NULL,
  `album_date_gmt` datetime NOT NULL,
  `album_creation_ip` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL,
  `album_privacy` enum('public','password','private','private_but_link','custom') CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT 'public',
  `album_privacy_extra` mediumtext CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci,
  `album_password` mediumtext CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci,
  `album_image_count` bigint NOT NULL DEFAULT '0',
  `album_description` mediumtext CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci,
  `album_likes` bigint NOT NULL DEFAULT '0',
  `album_views` bigint NOT NULL DEFAULT '0',
  `album_cover_id` bigint DEFAULT NULL,
  `album_parent_id` bigint DEFAULT NULL,
  `album_cta_enable` tinyint(1) NOT NULL DEFAULT '0',
  `album_cta` longtext COLLATE utf8mb4_unicode_ci,
  PRIMARY KEY (`album_id`),
  KEY `album_name` (`album_name`),
  KEY `album_user_id` (`album_user_id`),
  KEY `album_date_gmt` (`album_date_gmt`),
  KEY `album_privacy` (`album_privacy`),
  KEY `album_image_count` (`album_image_count`),
  KEY `album_creation_ip` (`album_creation_ip`(191)),
  KEY `album_likes` (`album_likes`),
  KEY `album_views` (`album_views`),
  KEY `album_parent_id` (`album_parent_id`),
  FULLTEXT KEY `searchindex` (`album_name`,`album_description`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8mb4_unicode_ci;
/*!40101 SET character_set_client = @saved_cs_client */;

[Oxy]

correct the last line to this

ENGINE = InnoDB DEFAULT CHARSET = utf8mb4 COLLATE = utf8mb4_unicode_ci;

---

error is here