Cannot set MYSQL 8 default encoding to Latin1 instead of UTF8
I'm currently in the process of setting up a new webserver.
I have installed CloudLinux v9.5.0 and cPanel & WHM v124.0.17 (STANDARD).
I'm using CageFS and Cloudlinux Alt PHP / PHP Selector feature.
Because i'm still using multiple old scripts (built in php 5.2 days) i'm trying to configure MYSQL 8 accordingly.
Until now i was using MYSQL 5.7 and with MYSQL4 (compatiblity) mode + setting MYISAM as default engine everything worked perfect. But MYSQL 8 removed MYSQL 4 compatiblity mode and now i'm having issues.
My /etc/my.cnf looks like this
[mysqld]
# Set default storage engine to MYSQL instead of InnoDB
default-storage-engine = MYISAM
# Set server character set to latin1
character-set-server = latin1
collation-server = latin1_swedish_ci
#
sql_mode="ALLOW_INVALID_DATES,NO_ZERO_IN_DATE,NO_ZERO_DATE,NO_ENGINE_SUBSTITUTION"
[client]
# Set client character set to latin1
default-character-set = latin1
[mysql]
# Set mysql command-line client character set to latin1
default-character-set = latin1
[mysqldump]
# Set mysqldump character set to latin1
default-character-set = latin1
But unless i add one of those two lines to [mysqld] section:
skip-character-set-client-handshake = 1
OR:
init_connect = 'SET NAMES latin1'
I end up with broken characters because it looks like the client connects using UTF8 instead of Latin1 despite i know the PHP script very well and it does not ask for a specific charset anywhere.
In php 5.2 you cannot configure a charset for mysql connection, it basically assumes latin1 because this was the standard back then. (default_charset does something else, yes i tried anyway.)
Altering the php scripts is NOT an option and i would also really like to avoid putting one of those two lines in my /etc/my.cnf
skip-character-set-client-handshake = 1
OR:
init_connect = 'SET NAMES latin1'
because i'm worried that some modern scripts might not work correctly when being forced to use latin1 instead of utf8.
Is there something i'm missing?
Can i somehow configure mysql 8 to use latin1 as default unless a script requests something else?
I tried solving this for 5 hours straight and i'm really desperate by now.
-
Hey there! I know you mention that updating the scripts isn't an option, but you'll have to do that at some point - nothing is perfect forever.
What do see right now when you run this command on the server?
mysql -e "show variables like 'char%';"
0 -
The output of
mysql -e "show variables like 'char%';"
Is:
character_set_client latin1 character_set_connection latin1 character_set_database latin1 character_set_filesystem binary character_set_results latin1 character_set_server latin1 character_set_system utf8mb3 character_sets_dir /usr/share/mysql-8.0/charsets/ But if i do the same with a php script the output is a mix of utf8 and latin1 EXCEPT if i put one of the following lines in my /etc/my.cnf:
skip-character-set-client-handshake = 1
OR:
init_connect = 'SET NAMES latin1'The good news is, even if i have
skip-character-set-client-handshake = 1
in there it seems not to break modern scripts like invision community, xenforo, mybb, phpbb, wordpress, joomla. (I tested it)
So i will just keep the line and call it a day for now.0 -
That sounds like a great solution!
0
Please sign in to leave a comment.
Comments
3 comments