Encoding charset

Ch3vr0n

Verified User
Joined
Aug 24, 2014
Messages
48
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.
 

Ch3vr0n

Verified User
Joined
Aug 24, 2014
Messages
48
Nope 3.9.14, all of them

Both hosts were running mysql 5.7 (latest build), not mariadb.
 

Ch3vr0n

Verified User
Joined
Aug 24, 2014
Messages
48
@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.
 

Ch3vr0n

Verified User
Joined
Aug 24, 2014
Messages
48
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
 

Ch3vr0n

Verified User
Joined
Aug 24, 2014
Messages
48
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
 

Ch3vr0n

Verified User
Joined
Aug 24, 2014
Messages
48
i login as root. VPS provider did not give any SUDO credentials. Only admin and root
 

Ch3vr0n

Verified User
Joined
Aug 24, 2014
Messages
48
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"?
 

Zhenyapan

Verified User
Joined
Feb 23, 2018
Messages
85
Location
UA
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
 

Zhenyapan

Verified User
Joined
Feb 23, 2018
Messages
85
Location
UA
yes, 644 = (rw-r--r--)
so you can edit it if you are owner or upper level (root), and everybody can read it
 

Ch3vr0n

Verified User
Joined
Aug 24, 2014
Messages
48
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:

Ch3vr0n

Verified User
Joined
Aug 24, 2014
Messages
48
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
 

Wanabo

Verified User
Joined
Jan 19, 2013
Messages
170
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");
 

Ch3vr0n

Verified User
Joined
Aug 24, 2014
Messages
48
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.
 
Top