0

I have a mysql database in Gcloud using utf8 enconding and I want to change it to utf8mb4 to support storing emoticons.

I did this using my local version of mysql in my laptop and it worked fine using:

ALTER TABLE table_name CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

ALTER DATABASE db_name CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

But when I try to do the same in Gcloud, the commands are accepted and the encoding changes but I keep receiving this error when trying to insert the record:

"error": "could not execute statement; nested exception is org.hibernate.exception.GenericJDBCException: could not execute statement: Incorrect string value: '\xF0\x9F\xA6\x95' for column 'text' at row 1" }

I also changed character_set_server variable in gcloud to utf8mb4 and restarted the engine but it does not work. Does anybody know what else should I change?

Thanks!

lisi1986
  • 1
  • 1
  • 1
    Asked and answered here: https://stackoverflow.com/a/61703576/8016720 Do not skip the repair step. Do not skip the Cloud SQL flag setting. – John Hanley Oct 19 '21 at 00:59
  • Yes, I already tried that, but when trying to change the table encoding I get an error because of the foreign key constraints like this: ERROR 1832 (HY000): Cannot change column 'xxxx': used in a foreign key constraint 'FKd8k0643uhxq76e5bd8argd9mj' . I also tried disabling the foreign key check. In that case I am allowed to change the encoding but the complete database gets corrupted. Do you have any other idea? – lisi1986 Oct 21 '21 at 00:10
  • Dump the table, edit the schema and recreate. – John Hanley Oct 21 '21 at 00:22
  • I tried exporting the data, creating a new database with the correct encoding, then I change every utf8 enconding and collate in the sql export file and re imported in the new database and it did not work either. I believe it has something to do with Gcloud because as I said, it works fine with my local mysql service and the code is the same. – lisi1986 Oct 21 '21 at 12:31
  • I solved it. I had to set the character_set_server variable value from gcloud for it to get persisted. I was setting the value using the terminal but it did not persist that value in the engine. – lisi1986 Oct 21 '21 at 23:44

0 Answers0