0

I am planning an upgrade of a legacy MySQL5.6 Database to MYSQL8.0.19 database.

All my testing has been successful but have one issue preventing me from going live.

By default in MYSQL5.6 the following statement would return true

SELECT 'a ' = 'a';

But in MySQL 8, it returns false.

I understand that this has to do with the default collation on the database.

But when I try change it to

ALTER DATABASE **** CHARACTER SET utf8mb4 COLLATE utf8mb4_bin;

This collation supports PAD SPACE, however it does not return the functionality back to how it was in 5.6

My issue is that there are a lot of stored procedures that rely on the old functionality and would take a long time to change it.

2
  • when I try change it to Binary? On 8.0.19?? For to ignore trailing spaces??? fiddle
    – Akina
    Jan 27 '20 at 9:57
  • Yes Binary will make 'a '='a' true. Obviously the correct way is to trim it. But I am trying to speed up implementation. I found a way of doing it which I have put in the answer
    – Wize
    Jan 27 '20 at 12:45
0

The solutions to this problem is as follows

Added the following to my.cnf

 collation_server=utf8mb4_bin
 skip-character-set-client-handshake

Then in the Connection string I added the following

;old guids=true

I realize this is a hack, but there is a large amount ofstored procedures that compare data such as "where fieldx = 'example'" where the phrase 'example' in the field has trailing spaces.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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