Importing database in Virtualmin changes charset

Submitted by Fredrik-s on Wed, 12/07/2011 - 11:30

Hello,

I have a site that I have had on another host. Now Im up to putting it on my own VPS with Virtualmin.

I have created a virtual server and importing the database through "Edit database > Manage.. > Perform SQL > Run SQL from file > Choosing the database from harddrive".

I have looked in the database-file which I try to upload all the charset are set to UTF-8 and MyISAM and all the chars are showing right in the SQL-file (the one I upload). But when I have uploading it, looking in the tabels and columns on my Virtualmin VPS I can se that the chars have changed and are being outputed wrong. So the Swedish chars Å Ä Ö show up like Ã¥, ö, ä.

How do I fix this, so the charset is right?

When creating database I have chosen the following: Database server type: MySQL (MyISAM as standard) Character set for new database: utf8 (UTF-8 Unicode) Collation order for new database: utf8_swedish_ci (UTF-8 Unicode)

Status: 
Closed (fixed)

Comments

Submitted by JamieCameron on Wed, 12/07/2011 - 16:29

Would it be possible for you to attach the SQL file (at least the first few hundred lines) to this bug report, or email it to me at jcameron@virtualmin.com ? Most likely the format is not what Webmin or mysql expects..

Submitted by Fredrik-s on Wed, 12/07/2011 - 16:57

Thanks for fast reply Jamie.

I will email you the first 1500 rows. As to be said is that this is a Wordpress-database exported through PHPMyadmin the regular way.

Submitted by JamieCameron on Wed, 12/07/2011 - 18:36

Thanks for the file - I see the issue now, which is that when Webmin executes an SQL file it doesn't specify a character set, causing the default to be used.

I will add a feature for this in the next Webmin release. The work-around till then is to import the SQL from the command line, with a command like :

mysql -u root -pyourrootpassword --default-character-set=utf8 databasename < file.sql

Submitted by Fredrik-s on Thu, 12/08/2011 - 07:19

Thanks for the answer and thanks for a work-around solution!

Submitted by Fredrik-s on Thu, 12/08/2011 - 13:03

I dosen't got it to work when trying the command:

mysql -u root -p yourrootpassword --default-character-set=utf8 databasename < file.sql

I have placed the sql-file in the "/root" and get the following error:

Variables (--variable-name=value) and boolean options {FALSE|TRUE} Value (after reading options)

auto-rehash TRUE character-sets-dir (No default value) default-character-set utf8 compress FALSE database (No default value) delimiter ; vertical FALSE force FALSE named-commands FALSE local-infile FALSE no-beep FALSE host (No default value) html FALSE xml FALSE line-numbers TRUE unbuffered FALSE column-names TRUE sigint-ignore FALSE port 3306 prompt mysql> quick FALSE raw FALSE reconnect FALSE socket /var/run/mysqld/mysqld.sock ssl FALSE ssl-ca (No default value) ssl-capath (No default value) ssl-cert (No default value) ssl-cipher (No default value) ssl-key (No default value) ssl-verify-server-cert FALSE table FALSE debug-info FALSE user root safe-updates FALSE i-am-a-dummy FALSE connect_timeout 0 max_allowed_packet 16777216 net_buffer_length 16384 select_limit 1000 max_join_size 1000000 secure-auth FALSE show-warnings FALSE

What am I doing wrong?

Submitted by JamieCameron on Thu, 12/08/2011 - 13:20

You need to remove the space between -p and your password.

Submitted by Fredrik-s on Thu, 12/08/2011 - 13:50

I'm afraid that doesn't work either.

I get "Error: Access denied for user 'root'@'localhost' (using password: YES)". And I have tried the right password like 5 times. I have tried the following lines also:

mysql -u root -pyourrootpassword --default-character-set=utf8 databasename < file.sql
mysql -u root -p yourrootpassword --default-character-set=utf8 databasename < file.sql
mysqlimport -u root -pyourrootpassword --default-character-set=utf8 databasename file.sql
mysqlimport -u root -p yourrootpassword --default-character-set=utf8 databasename file.sql

Get the same on all. I'm logged in as root over SSH. (ssh root@ip-adress, entered right password, logged in)

Submitted by JamieCameron on Thu, 12/08/2011 - 14:02

Are you sure the password and database name are correct? You can see the root password Virtualmin is using by running :

grep pass /etc/webmin/mysql/config

Submitted by Fredrik-s on Thu, 12/08/2011 - 14:07

Thanks that helped me!

For some reason the password wasn't the same as the root, which I use to login to the server through SSH. Strange. But now it worked. Thanks!

Submitted by Issues on Thu, 12/22/2011 - 16:18

Automatically closed -- issue fixed for 2 weeks with no activity.