Encoding charset

Ch3vr0n

Verified User
Joined
Aug 24, 2014
Messages
88
After upgrading servers, it seems there was a misconfig by on the mysql level. There's a few joomla based sites that have begun displaying weird symbols.

Database collation is set to utf8_general_ci
Database connection: utf8mb4_general_ci

PHP is also set to "UTF8"

I believe the problem lies here

mysqli Character Set: utf8mb4
default Character Set: latin1

With the holidays and all i can't get an easy hold of host, but one of the sites i'm working on there's a deadline of end of this week to deliver. If i don't get that fixed today or tomorrow. I'll probably not make it. But i'm not skilled enough en ssh / linux / MySQL to fix this. and i don't see anywhere to fix this on the DA admin level.
 
Nope 3.9.14, all of them

Both hosts were running mysql 5.7 (latest build), not mariadb.
 
@smtalk a while ago you contacted me via pm in regards to that domain routing issues. Any chance you could do what you offered there? I believe the problem is that the old server has these settings

mysqli Character Set: utf8
default Character Set: utf8
Database Collation: utf8_general_ci

while the new one has these

mysqli Character Set: utf8
default Character Set: latin1
Database Collation: utf8_general_ci

Now since the problematic sites (in their configuration file) are set to

Database collation is set to utf8_general_ci
Database connection: utf8mb4_general_ci

The weird letters appear because of character mismatch.
 
Host is unhelpful due to it being an unmanaged vps @smtalk i've done some more research and it might be because

init_connect='SET collation_connection = utf8_unicode_ci'
init_connect='SET NAMES utf8'
character-set-server=utf8
collation-server=utf8_unicode_ci
skip-character-set-client-handshake

is missing in my.cnf

So i logged in on VPS, then su and mysql --help

I've got 3 different files so i don't know which one to edit (and the one i try to reports as "Read Only, W10"

/etc/my.cnf

#
# Remove leading # and set to the amount of RAM for the most important data
# cache in MySQL. Start at 70% of total RAM for dedicated server, else 10%.
# innodb_buffer_pool_size = 128M
#
# Remove leading # to turn on a very important data integrity option: logging
# changes to the binary log between backups.
# log_bin
#
# Remove leading # to set options mainly useful for reporting servers.
# The server defaults are faster for transactions and fast SELECTs.
# Adjust sizes as needed, experiment to find the optimal values.
# join_buffer_size = 128M
# sort_buffer_size = 2M
# read_rnd_buffer_size = 2M
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock

# Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links=0

log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid

/etc/my.cnf.old

[mysqld]
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
# Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links=0
# Settings user and group are ignored when systemd is used.
# If you need to run mysqld under a different user or group,
# customize your systemd unit file for mariadb according to the
# instructions in http://fedoraproject.org/wiki/Systemd

[mysqld_safe]
log-error=/var/log/mariadb/mariadb.log
pid-file=/var/run/mariadb/mariadb.pid

#
# include all files from the config directory
#
!includedir /etc/my.cnf.d

/etc/my.cnf.orig

#
# These groups are read by MariaDB server.
# Use it for options that only the server (but not clients) should see
#
# See the examples of server my.cnf files in /usr/share/mysql/
#

# this is read by the standalone daemon and embedded servers
[server]

# this is only for the mysqld standalone daemon
[mysqld]
local-infile = 0
explicit_defaults_for_timestamp=1
innodb_file_per_table=1
innodb_flush_method=O_DIRECT
innodb_buffer_pool_instances = 1
innodb_log_file_size = 16M

skip-host-cache
skip-name-resolve
thread_cache_size = 4
performance_schema = ON

query_cache_type = 0
query_cache_size = 32K
query_cache_limit = 32K

sort_buffer_size = 1M
join_buffer_size = 3M
read_buffer_size = 1M

tmp_table_size = 256M
max_heap_table_size = 256M
key_buffer_size = 32M
read_rnd_buffer_size = 128M
bulk_insert_buffer_size = 64M
open_files_limit=8192
socket = /var/lib/mysql/mysql.sock
max_allowed_packet = 100M
interactive_timeout=300
wait_timeout=300

collation-server = utf8_unicode_ci
init-connect='SET NAMES utf8'
character-set-server = utf8

[mysqldump]
skip-opt
skip-add-locks

[client]
default-character-set=utf8

[mysql]
default-character-set=utf8
 
I'd love to, but i'm getting a warning after using vi (i'm anything BUT a linux (CentOS 7) / ssh expert

W10: Warning: Changing a read-only file

No idea how to make it writeable, what to edit (if my above is correct) where to put it and how to make it back into read-only
 
i login as root. VPS provider did not give any SUDO credentials. Only admin and root
 
command 1 yields -rw-r--r--
so i assume nr 2 makes it writeable. How do i actually edit, with "vi" and then "i"? exit with "esc" and save with ":wq"

What's the command to make it read-only then to the config above? "chmod 644 /etc/my.cnf"?
 
readonly = 444
you may install MC (midnight commander), than use it's internal editor in graphical mode by F4, or to start editor from console:
mcedit /etc/my.cnf
 
yes, 644 = (rw-r--r--)
so you can edit it if you are owner or upper level (root), and everybody can read it
 
So fixed the default collation with

init-connect = 'SET NAMES utf8'
character-set-server = utf8
collation-server = utf8_unicode_ci

But the weird characters did NOT reconvert back into the proper utf8 display. Am i missing a few lines, or is there more to it?

For example: " é " is still being displayed as "é"
 
Last edited:
database tables themselves are utf8mb4

but i do have to say thx for that midnight commander. Makes things a whole lot easier. Now if only it supported keyboard numpad's, but that's a minor issue
 
You might need to set uft-8 in php.ini
Code:
; PHP's default character set is set to UTF-8.
; http://php.net/default-charset
default_charset = "UTF-8"

/usr/local/phpxx/lib/php.ini

Or set a header on top of every page:
Code:
header("Content-Type: text/html; charset=UTF-8");
 
That was already the case, i even said that in my initial post. That made it even weirder. Finally bit the bullet a couple hours ago. Manually fixed things through phpmyadmin.
 
Back
Top