MySQL Character Set and Collation Mismatch Error When Importing Sample Database

Other Questions MySQL
1

I downloaded a sample database and tried to import it into my MySQL server, but I’m getting a character set error. The import process stops with this error message:

ERROR: Error 1253: COLLATION ‘latin1_swedish_ci’ is not valid for CHARACTER SET ‘utf8’

Here’s the problematic SQL code that’s causing the issue:

CREATE TABLE IF NOT EXISTS `moviedb`.`client` (
  `client_id` INT(6) UNSIGNED NOT NULL AUTO_INCREMENT,
  `branch_id` TINYINT(2) UNSIGNED NOT NULL,
  `name` VARCHAR(50) NOT NULL,
  `surname` VARCHAR(50) NOT NULL,
  `phone` VARCHAR(20) NULL DEFAULT NULL,
  `location_id` INT(6) UNSIGNED NOT NULL,
  `status` BOOLEAN NOT NULL DEFAULT TRUE,
  `registration_date` DATETIME NOT NULL,
  `modified_date` TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`client_id`),
  INDEX `idx_branch` (`branch_id` ASC),
  INDEX `idx_location` (`location_id` ASC),
  INDEX `idx_surname` (`surname` ASC)
)
ENGINE = InnoDB
DEFAULT CHARACTER SET = utf8
COLLATE = latin1_swedish_ci

I can see the table is set to use UTF8 character set but latin1_swedish_ci collation. This seems to be causing the conflict. Only some tables were created successfully before this error stopped the process. I don’t have much database experience, so I’m not sure how to fix this. Has anyone encountered this issue before?

2

The error you’re encountering is due to a mismatch between the character set and collation specified in your SQL statement. In your case, utf8 requires a collation that supports it, such as utf8_general_ci or utf8_unicode_ci. You will need to update the COLLATE part of your table creation statement accordingly. For instance, change COLLATE = latin1_swedish_ci to COLLATE = utf8_unicode_ci. This should resolve the import issue. Additionally, if you’re working with multiple tables, consider performing a global search and replace in your SQL file to change all occurrences of latin1_swedish_ci to utf8_unicode_ci before attempting to import again.