MySQL UPDATE changing existing characters

[jhande]

I tried Googling but that wasn't very helpful, it just confussed the heck out of me.

 

I'm working on a localhost updating (adding, changing prices, etc...) my products.

 

Whenever I update my online database with my new changes, it changes different characters on me.

 

Such as:

- (hyphen) becomes ?

¼ (fractions) becomes ?

' (apostrophe) becomes ?

and more...

 

When I log into both phpMyAdmin locations I see this:

MySQL charset: UTF-8 Unicode (utf8)

MySQL connection collation: [ utf8_unicode_ci ]

 

When I check on the tables from both databases I see this:

Collation

latin1_swedish_ci

 

When I check my dump file I see this:

) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=787 ;

 

Having these changing characters really messed me up from what should have been a simple database update.

Now I have to physically edit hundreds of entries to fix them.

 

Any ideas or how to fix these character changes would be truely appreciated.

 

[♥FWR Media]

I tried Googling but that wasn't very helpful, it just confussed the heck out of me.

 

I'm working on a localhost updating (adding, changing prices, etc...) my products.

 

Whenever I update my online database with my new changes, it changes different characters on me.

 

Such as:

- (hyphen) becomes ?

¼ (fractions) becomes ?

' (apostrophe) becomes ?

and more...

 

When I log into both phpMyAdmin locations I see this:

MySQL charset: UTF-8 Unicode (utf8)

MySQL connection collation: [ utf8_unicode_ci ]

 

When I check on the tables from both databases I see this:

Collation

latin1_swedish_ci

 

When I check my dump file I see this:

) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=787 ;

 

Having these changing characters really messed me up from what should have been a simple database update.

Now I have to physically edit hundreds of entries to fix them.

 

Any ideas or how to fix these character changes would be truely appreciated.

 

 

Presuming the database settings are exactly the same, which you haven't mentioned. You also haven't mentioned how you are changing the data.

[jhande]

Presuming the database settings are exactly the same, which you haven't mentioned.

Sorry Robert... not sure what settings or where to find them.

 

You also haven't mentioned how you are changing the data.

I have a local version of osC installed with XAMPP and enter products in osC admin panel just as I would online.

Then I go to my localhost phpMyAdmin and export the products table and then the products_description table.

Export settings -

-- View dump (schema) of table --

- Export -

SQL

- SQL options -

SQL compatibility mode: none

- Structure -

Add AUTO_INCREMENT value

Enclose table and field names with backquotes

- Data -

Maximal length of created query: 50000

Use hexadecimal for binary fields

Export type: tried both - INSERT and UPDATE

 

Copy and Paste that into a notepad file and save it.

 

Then I go to my online CPanel > phpMyAdmin > database > table (products then products_description) > Import

- File to import -

Browse to my Notepad file

Character set of the file: utf8

- Partial import -

nothing checked and zero

- Format of imported file -

SQL selected

SQL compatibility mode NONE

 

Then click Go button.

 

-- EDIT --

When I setup my localhost I ran a fresh install of osC. I then imported my online database to the newly created local one.

[jhande]

I just found out how to check on the database default collation if that helps?

 

I ran this query on both databases:

 

SELECT DEFAULT_COLLATION_NAME
FROM information_schema.SCHEMATA
WHERE SCHEMA_NAME = 'database_name';

 

Online database

DEFAULT_COLLATION_NAME

latin1_swedish_ci

 

Localhost database

DEFAULT_COLLATION_NAME

latin1_general_ci

 

Information Schema - Both databases

All tables:

Collation: utf8_general_ci

 

But yet the actual database tables are showing: latin1_swedish_ci

 

When I import the table(s) I choose 'Character set of the file: utf8', is that correct?

Do I need to change the default database collation name of say the localhost from latin1_general_ci to latin1_swedish_ci? Or vice versus?

 

Any ideas??

[MrPhil]

It sounds like your databases have Latin-1 (ISO-8859-1) encoded text data, but your page is being displayed in UTF-8. Such characters as you listed (in the upper 128 characters of Latin-1) are invalid encodings in UTF-8 and will give you (typically) a question mark in a diamond symbol. Anyway, you want to get your database, language files, and page display all speaking the same encoding: Latin-1 or UTF-8, but not a mixture of both. View your page source in a browser and see what kind of encoding it lists (if any) in the <head> section. If "UTF8", that's your problem. If "Latin-1" or "ISO-8859-1" (or nothing -- Latin-1 is the default), then the problem is elsewhere.

[jhande]

Thanks for the reply Phil.

 

When I View > Source in my browser this is at both local and online =

<meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1">When I View > Encoding in my browser = Western European (ISO) [default IE7 setting].

 

While I was importing the table data and chose utf8 was because that was showing by default, guess that was wrong.

 

So now I guess I need to change (somehow):

 

Online database tables from - latin1_general_ci to latin1_swedish_ci ?

 

Both database information schema from utf8_general_ci to latin1_swedish_ci ?

 

Then when I import my tables chose latin1 ?

 

 

 

 

[MrPhil]

OK, it sounds like your database and your page are in Latin-1.

While I was importing the table data and chose utf8 was because that was showing by default, guess that was wrong.

Oooh, not good. So your files were in Latin-1 and you told MySQL that they were UTF-8? My guess is that it tried changing non-ASCII characters (such as your special characters) to Latin-1, and as they were invalid UTF-8 characters in the first place, they were just replaced by a "?". I'm surprised you didn't get any error messages during the process about "invalid characters".

 

So the damage is done. If you go into phpMyAdmin and display the text, are they ? in there too? If so, it probably means that your original text (special characters) is gone. Do you still have the original .sql files, or even if so, have you added a bunch of stuff?

 

So now I guess I need to change (somehow):

 

Online database tables from - latin1_general_ci to latin1_swedish_ci ?

I wouldn't worry about "general" versus "swedish" -- that's just collation order for certain accented characters (like where does AE ligature go in alphabetical order). The "latin1" is the important thing.

 

Both database information schema from utf8_general_ci to latin1_swedish_ci ?

I don't know what the consequences of that will be. You can try it, if you have a good database backup to restore! I don't have any experience with converting "database information schema" separately from database tables -- are these just tables themselves? Is there any data within them, or do they just describe the other tables? If they are just descriptive tables, you could try converting them to Latin-1. Just be sure to have a backup (dump) of the database in case something goes terribly wrong.

 

Then when I import my tables chose latin1 ?

Are you going to wipe out your current database and start over? If so, just create everything as Latin-1 (the default) and import your backup as Latin-1, and everything should be consistent. I'm not sure what you can do to salvage an existing database -- maybe export (dump) it into an .sql file and then manually repair the broken characters before importing back into a freshly initialized database?

 

If you can successfully convert the database information schema table(s) to Latin-1, and everything else seems OK, you might use phpMyAdmin table edit to manually fix up your bad characters in your tables. Or, you can export (dump) the database to an .sql file and use an ordinary editor (ViM, Notepad++) to edit the text to the correct special characters, empty the tables, and import the data (remove any CREATE TABLE and DROP TABLE commands first).

[jhande]

Thanks Phil for the help, truely appreciated!

 

The damage is done with the characters in the two databases, question marks all over.

 

Thanks for explaining what needs to be done and how I should have done things.

 

I'll read up on these things and figure out the best way to correct my mess.

 

Thanks again for pointing me in the right direction.