Forums / Upgrading from 1.2 to 1.3.x / After database import accents on text has gone funny

After database import accents on text has gone funny

Locked

Started by peanut77 on 22 Aug 2006, 1:09 PM · Views: 5,828

Results 1 to 11 of 11
This thread is locked. New replies are disabled.
22 Aug 2006, 1:09 PM
#1
peanut77 avatar

peanut77

Zen Follower

Join Date:
Feb 2005
Posts:
266
Plugin Contributions:
0

After database import accents on text has gone funny

Ok weird one - after importing my 1.27 database into 1.32 a lot of symbols seem to have went "funny" - i.e £ were all prefixed with £& other accents on names have also been lost - is this to do with the carachter set - it is an english site but with wines from around the world.

i.e Adobe Carmenère 2003/2004 has changed to Adobe Carmenère 2003/2004

How can I globally resolve this? or do I need to reimport my data and use a different charachter set?

The database was encoded with utf however when i imported it wanted to be latin 1?

22 Aug 2006, 1:28 PM
#2
peanut77 avatar

peanut77

Zen Follower

Join Date:
Feb 2005
Posts:
266
Plugin Contributions:
0

Re: After database import accents on text has gone funny

Hi it was the charachter set - sorted now.

13 Oct 2006, 3:14 PM
#3
danielle avatar

danielle

Totally Zenned

Join Date:
Oct 2004
Posts:
973
Plugin Contributions:
0

Re: After database import accents on text has gone funny

How did you fix this? I am having the same problem :(

Thanks :)

Danielle

13 Oct 2006, 3:20 PM
#4
peanut77 avatar

peanut77

Zen Follower

Join Date:
Feb 2005
Posts:
266
Plugin Contributions:
0

Re: After database import accents on text has gone funny

Hi

I reimported my database making sure I selected the correct charachater set - normally UTF (I think - whatevere is the standard)

But this totally fixed it.

13 Oct 2006, 3:40 PM
#5
danielle avatar

danielle

Totally Zenned

Join Date:
Oct 2004
Posts:
973
Plugin Contributions:
0

Re: After database import accents on text has gone funny

Thank you! I wasn't using phpMyAdmin, I was doing it using the backup function in cpanel. You just made my day :D

Danielle

13 Oct 2006, 3:54 PM
#6
peanut77 avatar

peanut77

Zen Follower

Join Date:
Feb 2005
Posts:
266
Plugin Contributions:
0

Re: After database import accents on text has gone funny

Fab! Glad to help - this one one of this things that nearly gave me a heart attack!

13 Oct 2006, 8:27 PM
#7
fymbscu avatar

fymbscu

New Zenner

Join Date:
Jan 2006
Posts:
38
Plugin Contributions:
0

Re: After database import accents on text has gone funny

Hi folks,

Sorry for my ignorance of SQL, but where would the charset change be made?

My challenge is that I have been building the site on a local server and we are about to go live. To verify that we were ready to do so, I used phpMyAdmin to backup the remote site and then when I attempt to upload to my local site database, I get a syntax error:

1064 You have an error in your SQL syntax. Check the manual that corresponds to your MySQL server version for the right syntax to use near 'DEFAULT CHARSET=latin1 AUTO_INCREMENT=6' at line 1

I have looked inside the various backup files I have made, and only the ones from the remote server have this "DEFAULT CHARSET=latin1" text in the file, but it is in multiple places.

The MySQL client versions are different on the machines. Could this be producing the incompatibility? Local machine version 3.23.49; Remote machine version: 4.1.21.

When I go to the export screen in phpMyAdmin, there are no Charset settings.

Please enlighten me.

Regards,

Mark Harrison

13 Oct 2006, 9:15 PM
#8
peanut77 avatar

peanut77

Zen Follower

Join Date:
Feb 2005
Posts:
266
Plugin Contributions:
0

Re: After database import accents on text has gone funny

Just so you know I'm no expert but if you use myphpadmin when you click the import link you get a box where you can run a query/import a database there is a dropdown list where you can choose the character set there.

I'm sure there is a good wiki article on this (it was a few weeks agao now I got stuck at this bit)

13 Oct 2006, 9:16 PM
#9
peanut77 avatar

peanut77

Zen Follower

Join Date:
Feb 2005
Posts:
266
Plugin Contributions:
0

Re: After database import accents on text has gone funny

Apologies I forgot say the database should export (as far as I know) as the correct character set - it's when you import you get the choice in the import/query screen bit.

13 Oct 2006, 10:00 PM
#10
fymbscu avatar

fymbscu

New Zenner

Join Date:
Jan 2006
Posts:
38
Plugin Contributions:
0

Re: After database import accents on text has gone funny

Thanks peanut77,

Although I have only used phpMyAdmin to perform the exports, and used the Admin:Tools:SQL Query Executor to perform the imports, I haven't seen the Character set choice in the Admin screen.

I have looked at both my remote and local import screens and the character set option is present in the remote screen but not on my local machine.

I have concluded this must be because of the discrepancy in MySQL versions. I am in the process of upgrading my local version and will let you know if this solves my problem.

Thanks for pointing out what I was missing. Knowing where to look is at least half the battle.

Regards,

Mark Harrison

27 Jan 2007, 12:39 AM
#11
fymbscu avatar

fymbscu

New Zenner

Join Date:
Jan 2006
Posts:
38
Plugin Contributions:
0

Re: After database import accents on text has gone funny

Hi guys,

Know it's been a while. The solution for me was to set the "SQL compatibility mode"
on the PHPMyAdmin Export page to MYSQL323.

I was unable to upgrade my local mysql because of the way it is knitted into the system software on my Mac.

This solved the moving data issue, but now I have been seeing text on my ez-pages bastardized when moving between local and remote carts through this backup method. Getting lots of "rn" characters and some of the punctuation is getting garbled. Any ideas how to solve this issue?

Regards,

Mark Harrison