Site database created with wrong collation in phpMyAdmin

Problems with the Windows version of XAMPP, questions, comments, and anything related.

Site database created with wrong collation in phpMyAdmin

Postby NewSites » 13. April 2015 02:54

phpMyAdmin has a box on its main page for setting the "Server connection collation". Mine is set to the default, "utf8mb4_general_ci", which is what I want. (It gives the UTF-8 character set with variable-width encoding of one to four bytes.) The "Databases" page has a box for selecting the collation of a new database. But when a new database is created automatically upon the addition of a new user, there is nowhere to select the collation of the new db. I don't see anywhere else (such as on the Settings pages) for choosing a collation in phpMyAdmin and I don't see anywhere for changing the collation of an existing db.

I set up my first Drupal site today on my local machine. I'm pretty sure that when I looked before, phpMyAdmin reported that the collation for the site database was "utf8_general_ci", which is the previous standard and is inferior to "utf8mb4_general_ci". I was originally going to ask what to do about that.

But I just took another look and now phpMyAdmin is reporting that the database collation is "latin1_swedish_ci". Pardon me, but WTF?

In the list of databases, "test" also has the Swedish collation and the collation named on the "Total" row of the list is also the Swedish collation. I had noticed that collation in the list before and was puzzled by it. But now I'm concerned because it has become the collation of my site db, which is not likely to give me behavior that I want.

Can anyone tell me what's going on? More specifically and more importantly,
(a) How do I tell phpMyAdmin to create new dbs with the "utf8mb4_general_ci" collation?
(b) How do I change the collation of an existing db?

Thank you.
=====================
phpMyAdmin v 4.2.11
Server version: 5.6.21 - MySQL Community Server (GPL)
Both installed with XAMPP v 5.6.3 under Windows 7
NewSites
 
Posts: 2
Joined: 13. April 2015 02:42
Operating System: Windows 7

Re: Site database created with wrong collation in phpMyAdmin

Postby Nobbie » 13. April 2015 10:17

a) Create the database manually (not automatically after creating a new user)

b) http://stackoverflow.com/questions/5906 ... a-database
Nobbie
 
Posts: 8769
Joined: 09. March 2008 13:04

Re: Site database created with wrong collation in phpMyAdmin

Postby Altrea » 13. April 2015 17:18

NewSites wrote:(a) How do I tell phpMyAdmin to create new dbs with the "utf8mb4_general_ci" collation?

https://dev.mysql.com/doc/refman/5.6/en ... tions.html

NewSites wrote:(b) How do I change the collation of an existing db?

unspecific. A database has a default character set which can be set in the operations tab of the database.
If you already have database tables you need to change their character set too
If you have any data in your database tables, you need to convert them.
We don't provide any support via personal channels like PM, email, Skype, TeamViewer!

It's like porn for programmers 8)
User avatar
Altrea
AF Moderator
 
Posts: 8290
Joined: 17. August 2009 13:05
XAMPP version: several
Operating System: Windows 10 Pro x64

Re: Site database created with wrong collation in phpMyAdmin

Postby NewSites » 13. April 2015 22:53

I was wrong. As pointed out by Altrea, the collation can be changed in phpMyAdmin in:
Databases > [database name] > Operations
As long as I do that right after the table is created, there should be no issue with existing data.
NewSites
 
Posts: 2
Joined: 13. April 2015 02:42
Operating System: Windows 7


Return to XAMPP for Windows

Who is online

Users browsing this forum: No registered users and 82 guests