We were using MySQL 5.7, but we're trying to start using the latest version. In 5.7, there is no issue with the code, but in 8.0, the stored procedure doesn't work upon initial load.
Previous version info: MySQL Server 5.7 MySQL Workbench for Windows version 6.3.10 CE build 12092614 (64 bit)
New version info: MySQL Server 8.0.30 MySQL Workbench for Windows version 8.0.31 CE build 2235049 (64 bit)
Trying to run a stored procedure results in, "Error Code: 1271. Illegal mix of collations for operation 'concat'." Collation is not mentioned as part of the code, but the schema does use CHARACTER SET latin1 COLLATE latin1_general_cs.
The error seems to be fixed by altering the stored procedure with insignificant changes (adding just a comment line, an extra blank line, etc.) and applying the change. Saving this "fixed" version of the procedure in a dump file does not fix the error on other computers, though. It would be much better to have a truly fixed version to distribute.
After reviewing the responses to Troubleshooting "Illegal mix of collations" error in mysql I added 'COLLATE latin1_general_cs;' to all of the statements, and changed all character_set/collation variables to latin1/latin1_general_cs (except for character_set_system, which I couldn't edit). The error persisted upon initial load of the stored procedure, but was fixed with an insignificant edit applied within MySQL.
Has anyone else encountered this? Is there a better fix out there?