MySQL export problem

Hello,

I have a large database and when I try to export it as SQL queries and then re-import it I notice some of the " and ' are messed up. Several of the single quotes are coming back as a bunch of weird characters like this: ’

Any ideas how I can fix this?

 

What interface are you using to do the export?

 

I'm using phpMyAdmin.

 

lol..don't use phpMyadmin if youre a newbie on DB import/export.They really sucks dude.I have this bad experience also when I try to do this.Try using Navicat Mysql they're really cool.Hope this helps.

 

Right now I'm just using phpMyadmin which is built in to CPanel. How do I would I use Navicat Mysql? Is it a stand alone application? If so will I be able to connect to my database which is running on a seperate web host?

 

PhpMyAdmin is a good product and easy to use. Star, are you exporting from one version of MySQL and into another version? You can see the version on the main page of PhpMyadmin.

 

The version of MySQL on the web host which I'm exporting from is 4.1.18 and the version of MySQL am running locally (which I'm importing to) is 5.0.18.

 

The problem is the collation of the database/tables on your local mysql. Let me guess it's set to sweedish.

 

How do I check and see what it is set to?

I use WAMP Server. Are you familar with it? It is a program which installs PHP, Apache, and MySQL are all at once.

 

On your home server, just create the database first (on the phpmyadmin home page) and collate as utf8_general_ci then, import all your tables and data into that database. See if that helps

 

Ok locally I have been using the MySQL GUI called SQLyog but I can't find the "Collation" options anyway in it so I installed phpMyAdmin to use instead.

Now I have a fresh copy of phpMyAdmin installed and ready but when I go to run my sql script which I exported from my remote web host I get the error that the file is too large (The file is 50MB). In phpMyAdmin is says the largest file size is 2,048KB. I read where you can increase this by changing the following variables: upload_max_filesize, memory_limit, and post_max_size in the php.ini configuration file but after changing them and restarting the server it still will only allow 2MB files.

Ideas??

 

Ok, just use PhpMyAdmin to create the Database and collate it correctly, then use SQLyog to import the data.

 

Darn it!!! Everything imported correctly but the weird characters are still there!! I checked the weird characters are not in the database that I did the export from.

Thoughts?

 

In Phpmyadmin, open up teh database/table where you're seeing the weird chars and tell me what collation is listed there.

 

On a more practical note, is there any reason you need to use MySQL 5? Why don't you install 4.1.x on your home computer instead? Wamp should have versions with 4.1.x.

 

The collation is:

latin1_swedish_ci

I'll go and find a version of WAMP where the MySQL is 4.1.18 and try that.

 

If you followed my instructions the collation should have been utf8_general_ci To do it you need to create the database first, setting it's default collation at that time (in Phpmyadmin), then when you import your database, (and I guess I didn't explicitly say this), don't re-create the database. Remove any CREATE DATABASE statements and only run all the CREATE TABLE and INSERT statements. Sorry for not making that clear.

 

I did create the new database using utf8_general_ci, but like you said the export script probably re-created the database. I'll it again.

 

Ok.

On the remote web host.
The database collation is: latin1_swedish_ci
The tables collation are: latin1_swedish_ci

Now on my local box after creating the database and setting the collation to utf8_general_ci for the database and importing just the tables here are the collations:
Database: utf8_general_ci
Tables: latin1_swedish_ci

 

Ok, do the create table statements have an explicit collate attribute by any chance?