0

Now I have character set utf8mb3 for my database. I want to change it to utf8mb4. For this task, I create a script, that creates SQL script, that doing it. This script can generate rows like this:

ALTER DATABASE `database_name` CHARACTER SET = utf8mb4 COLLATE = utf8mb4_general_ci;
ALTER TABLE `database_name`.`table1` CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;
ALTER TABLE `database_name`.`table2` CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;
...
ALTER TABLE `database_name`.`table1` CHANGE `column1` `column1` varchar(36) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL;
ALTER TABLE `database_name`.`table1` CHANGE `column2` `column2` varchar(36) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL;
ALTER TABLE `database_name`.`table1` CHANGE `column3` `column3` varchar(36) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL;
...

I realize, that I have problems with foreign keys and unique keys (I think) and add such rows to my generated script:

SET SESSION FOREIGN_KEY_CHECKS = 0;
SET SESSION UNIQUE_CHECKS = 0;
...
SET SESSION FOREIGN_KEY_CHECKS = 1;
SET SESSION UNIQUE_CHECKS = 1;

The issues with foreign keys are gone, but I still have such errors with unique keys:

ERROR 1062 (23000) at line 14: Duplicate entry 'some_id' for key 'table1.column1'

Can I just set some variable to ignore such errors? What can I do with them?

Tut budet Ima
  • 57
  • 5
  • Let's see the specific `some_id` that caused trouble. And let's see `SHOW CREATE TABLE`. – Rick James Jul 25 '23 at 01:47
  • @RickJames if it is important, ID looks like "d1341b05-4aoc-4307-8195-38fee15c8e4f-hash", similar ID for it is "d1341b05-4aoc-4307-8195-38fee15c8e4f-Hash". It's a unique key. But, I think, that the same problem will manifest itself on other tables. I would like to find ultimate solution for it – Tut budet Ima Jul 25 '23 at 06:38
  • @RickJames also I didn't understand `SHOW CREATE TABLE`) MySQL says "You have an error in your SQL syntax;" – Tut budet Ima Jul 25 '23 at 06:40
  • Does this answer your question? [SQL unique varchar case sensitivity question](https://stackoverflow.com/questions/6448825/sql-unique-varchar-case-sensitivity-question) – JosefZ Jul 25 '23 at 13:20
  • `SHOW CREATE TABLE table1;` – Rick James Jul 25 '23 at 23:10

0 Answers0