Change default collation in WampServer

0

I have just installed WampServer (x64) in order to install a WordPress site. In localhost/phpmyadmin/ the Server connection collation is utf8mb4_general_ci.

When I created a new DB, I defined the collation to be 'utf8_general_ci'

However, when I installed the wordpress I looked back in my DB and all the tables are in collation utf8mb4_unicode_520_ci

Why this was happened?

I tried and in C:\wamp64\bin\mysql\mysql5.7.14\my.ini file under the tag [mysqld] I added the:

character-set-server=utf8    
collation-server=utf8_general_ci

Then I restarted the wampserver, I installed the wordpress from scratch and again all the tables has Collation utf8mb4_unicode_520_ci

How can I resolve this?

yaylitzis

Posted 2017-01-12T10:26:38.017

Reputation: 187

If it's the server connection collation it could be different from the collation that is actually used in the background. What's the reason that you want to make that change? As for your approach it's correct. You could check an individual DB or Table for it's actual collation. This would make utf8mb4_unicode_520_ci seem to be a better choice?

– Seth – 2017-01-12T10:38:00.583

The problem started when I tried to migrate the website to server which didn't support utf8mb4.. So they told me to change the collation to utf8_general.. – yaylitzis – 2017-01-12T10:43:47.673

But that is a totally different problem from what you described? If you did a migration did you check your SQL files for a fixed setting of the collation? – Seth – 2017-01-12T10:48:25.917

I am a newbie so I am afraid I don't know what do you mean exactly.. what should I do; – yaylitzis – 2017-01-12T10:51:32.263

For your migration you probably exported the existing database in some form using phpMyAdmin or mysqldump? Right? In that case you ended up with a .sql file which is just a normal text file. You could just replace lines that mention utf8mb4_unicode_520_ci with ones that have whatever character set or collation you want or drop those all together to use the server default. Though that would involve some risk as for the stuff you have in your DB, depending on what it is. – Seth – 2017-01-12T11:29:18.240

well this exactly I did! However, now that I am building a new site I wanted to avoid this.. To export my DB, I do it from phpmyadmin.. – yaylitzis – 2017-01-12T14:27:48.170

Answers

0

You can define collations in multiple places. As discussed in the comments your MySQL server version supports setting the collation on a connection level.

To see whenever you modified the correct configuration you could run a simple CREATE TABLE statement like the following and check which charset and collation it would be using.

CREATE TABLE testSettings(
  Dummy TEXT
);

You didn't specify the WP version but you might try setting the char and collation to use using the configuration file [2]. On the current WP version (4.7.1) the default seems to be to set the charset to utf8 and not define a collation. You will have to test whenever setting the collation in the confguration would prevent the change.

But even if you do that I'm not sure it would save your hide as (since 2015 at least) WP is silently upgrading the collation to utf8mb4. If that information is not enough you could also look at this closed bug which might shine some light on the decision.

A way to circumvent the upgrade would be to use an older MySQL version. But looking at this I would suggest just changing the service that is running your DB. If they don't support utfmb4 it seems they're running a MySQL server that is older than 5.5.3 which was released in March 2010. Which would indicate that they didn't update their software for more than 6 years.

Seth

Posted 2017-01-12T10:26:38.017

Reputation: 7 657

Asked: 2017-01-12T10:26:38.017

Viewed: 1 012 times

Active: 2017-01-13T07:11:20.533