Default charset and collation

[twv]

I created databases a few days ago and the default charset was utf8mb4 and the default collation utf8mb4_general_ci. Today I created a database and its default charset was latin1 and collation latin1_swedish_ci.

As far as I know, I have done nothing to change the defaults. Can anyone help me figure out why they changed and how to change them back?

 

[twv]

I fixed this by adding

character-set-server=utf8mb4
collation-server=utf8mb4_general_ci

to the [mysqld] of /etc/my.cnf.d/server.cnf

Still not sure why it changed.

 

[Richard G]

to the [mysqld] of /etc/my.cnf.d/server.cnf

Is that on an older server or do you use mysql instead of Mariadb?

Or did you install something of mariadb via your package manager? Because on newly installed servers the my.cnf.d directory does not exist anymore. So maybe something got mixed up somehow.

What is your Distro and version? And how old is the DA version and the server?

Normally your adjustment is correct if I'm not mistaken, but should be added under the [mysqld] part in the /etc/my.cnf file.

 

[twv]

AlmaLinux release 8.9 (Midnight Oncilla) converted from CentOS. First got the server in 2020.
Pretty vanilla setup
DirectAdmin 1.660

 

[Richard G]

Oke same as me, but indeed in 2020 the /etc/my.cnf.d directory was still created.
Still I would suggest to move the setting to the /etc/my.cnf file under de [mysqld] part, because later on that directory will not exist anymore and when comparing settings you might miss this one in that case.

Just out of curiousity, I just created a database and couldn't see directly if it was utf8 or latin or whatever. So I presume it must be filled with data first before it's visible which collation it has.

For example if I look in to my phpyadmin with a filled database, it says in "general" when logging in to phpmyadmin:
utf8mb4_general_ci

When I click the tab "databases", the "information_scheme" has nothing, but my own database says "latin1_swedish_ci" however....
When I click on my database itself, there are only tables in the utf8mb4_general_ci collation.

However on the last line it says:

	218 tabellen	Som	~1.098.426	InnoDB	latin1_swedish_ci	573,8 MiB	5,1 KiB

As you can see, 218 tables, Innodb, latin1_swedish_ci. So I'm wondering why in the total here it still says latin1_swedish_ci while there is not a single latin1_swedish_ci table in the database.

Any clue?

 

[twv]

Thanks, I will take your advice re: /etc/my.cnf.d/ Should I move everything in there to my.cnf? (I prefer my.cnf anyways, never really got the point of my.cnf.d/)

I have been creating databases in DA under User level -> Account Manager -> Databases -> Create Database. As soon as I create one, the next screen shows charset and collation (see screenshot). The odd thing is that in the last day or two the defaults for new databases changed.

I believe DA may have auto-updated in the last few days. I am wondering if that is the source of the change.

 

[Richard G]

Should I move everything in there to my.cnf?

I don't know what you have in there, but I would move the custome made changes to my.cnf indeed. Don't forget to restart mysqld afterwards, just to be sure.

As soon as I create one, the next screen shows charset and collation (see screenshot).

Yes that's what I'm having too.

This is what I get after I created the database and log in to phpmyadmin.


And below is what I get when I click the "databases" tab.



So it's odd indeed.

I believe DA may have auto-updated in the last few days. I am wondering if that is the source of the change.

I'm not sure as collation is normally a database thing. But on the other hand, it's DA creating the database so it might be the case.

There were some changed made in the option on how to create a database user, but as far as I can see not in the collation.

Maybe @jamgames2 has some idea about these collation changes?

 

[Ohm J]

I'm not sure about this, I always have "character-set-server=utf8mb4" config in my.cnf. Because I know somedays might have bug about default setting.

Since latin have some security issued. Make custom config is the best ways..

 

[Richard G]

I always have "character-set-server=utf8mb4" config in my.cnf.

Oke, that's good to know, then I'm going to add that to my configs too. I also just converted all remaining MyIsam to Innodb too last night.
Only that one or also that second line?
collation-server=utf8mb4_general_ci?

 

[Ohm J]

if put only first line, it automatic pickup default of any "utf8mb4_*". I don't know which default will pick. But it will inside this scope.