0

I have a problem on emoji showing on webpage after migration to new DB Server. But I have a workaround. I will get the binary values of a certain field of TABLE1 on our OLD DB Server. Then update the TABLE1 on the new server using the script below:

UPDATE old_table t1 INNER JOIN new_table t2 ON t1.id = t2.id
SET t2.message = CAST(CONCAT("0x",HEX(t1.message)) AS BINARY);

But after executing this my message field on the new table becomes "0x1E395565"

If I do this script below, it will show the real message. But doing this for a million of records will take too long to execute hence the UPDATE INNER JOIN:

UPDATE new_table SET message = 0x1E395565 where id =1;

How to update this correctly? I concat "0x" on the HEX(t1.message) because when I do a mysqldump --hex-blob and charset=BINARY. There are 0x on the values.

My data type for message field is VARCHAR(1000) I know this might be wrong but is there any workaround without altering the table.

1
  • BINARY is string datatype with binary collation.
    – Akina
    Feb 18, 2021 at 17:31

2 Answers 2

0

Simply use UNHEX("1E395565").

There could be a lot of other issues going on. So please provide SHOW CREATE TABLE for the old and new tables. Also provide a sample of what is in the old table, but use

SELECT col, HEX(col) FROM ... WHERE ...

so we can see the value in an unambiguous way. (That is, not mangled or "fixed" by the client.)

Where does 1E395565 come from? It is not anything meaningful (not utf8, etc) that I recognize.

1
  • it is just a test values I created lol sorry.
    – JRA
    Feb 22, 2021 at 2:49
0

This is what I did that worked on me.

On my old database server (where emoji is showing before I migrate), since I just need the field where the emojis are being inputted and the primary key of the row, I created another table with just 2 fields containing the text and the primary key. Then filled it with the data from the old table.

After that I perform mysqldump with --hex-blob and charset=BINARY options.

On my new database server, I restored the dumpfile.

Then I just update the values on the current table with the values from the dump.

UPDATE prod.emoji as t1
INNER JOIN testdump.emoji_bin as t2 on t1.id = t2.id
SET t1.message = BINARY(t2.message)

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.