mysql backup messed up!

dezinerite · 10-02-2008, 12:33 AM

Please help!

I backed up my site's mysql database and when I reloaded the database, some letters & punctuations turned into symbols/characters.

What happened, and how can I fix this?

My site's charset is Latin1. Is there a possibility that it was somehow converted to utf8 when it was being backed up so I reloaded the utf8 instead of the Latin1?

whitebark · 10-02-2008, 01:18 AM

Originally Posted by dezinerite

Please help!

What happened, and how can I fix this?

????: NamePros.com http://www.namepros.com/programming/520029-mysql-backup-messed-up.html
My site's charset is Latin1. Is there a possibility that it was somehow converted to utf8 when it was being backed up so I reloaded the utf8 instead of the Latin1?

That's most likely what happened - I know as I've done it before (wrong character set). Use an earlier backup if you have one. If you don't have one, and I'm not a wizard with databases, but I'd guess you can convert it again as it is. And if not you can use a good text editor and just do a find/replace to remove/replace the characters. I recommend notetab lite. http://www.notetab.com/ntl.php

Eric · 10-02-2008, 06:52 AM

From mysql.com:

Quote:

If you want to change the table default character set and all character columns (CHAR, VARCHAR, TEXT) to a new character set, use a statement like this:
????: NamePros.com http://www.namepros.com/showthread.php?t=520029

Code:

ALTER TABLE tbl_name CONVERT TO CHARACTER SET charset_name;

For a column that has a data type of VARCHAR or one of the TEXT types, CONVERT TO CHARACTER SET will change the data type as necessary to ensure that the new column is long enough to store as many characters as the original column. For example, a TEXT column has two length bytes, which store the byte-length of values in the column, up to a maximum of 65,535. For a latin1 TEXT column, each character requires a single byte, so the column can store up to 65,535 characters. If the column is converted to utf8, each character might require up to 3 bytes, for a maximum possible length of 3 × 65,535 = 196,605 bytes. That length will not fit in a TEXT column's length bytes, so MySQL will convert the data type to MEDIUMTEXT, which is the smallest string type for which the length bytes can record a value of 196,605. Similarly, a VARCHAR column might be converted to MEDIUMTEXT.
????: NamePros.com http://www.namepros.com/showthread.php?t=520029

To avoid data type changes of the type just described, do not use CONVERT TO CHARACTER SET. Instead, use MODIFY to change individual columns. For example:

Code:

ALTER TABLE t MODIFY latin1_text_col TEXT CHARACTER SET utf8;
ALTER TABLE t MODIFY latin1_varchar_col VARCHAR(M) CHARACTER SET utf8;

If you specify CONVERT TO CHARACTER SET binary, the CHAR, VARCHAR, and TEXT columns are converted to their corresponding binary string types (BINARY, VARBINARY, BLOB). This means that the columns no longer will have a character set and a subsequent CONVERT TO operation will not apply to them.

If charset_name is DEFAULT, the database character set is used.

Warning:
The CONVERT TO operation converts column values between the character sets. This is not what you want if you have a column in one character set (like latin1) but the stored values actually use some other, incompatible character set (like utf8). In this case, you have to do the following for each such column:

Code:

ALTER TABLE t1 CHANGE c1 c1 BLOB;
ALTER TABLE t1 CHANGE c1 c1 TEXT CHARACTER SET utf8;

The reason this works is that there is no conversion when you convert to or from BLOB columns.

To change only the default character set for a table, use this statement:

Code:

ALTER TABLE tbl_name DEFAULT CHARACTER SET charset_name;

The word DEFAULT is optional. The default character set is the character set that is used if you do not specify the character set for columns that you add to a table later (for example, with ALTER TABLE ... ADD column).

10-02-2008, 12:33 AM	THREAD STARTER #1 (permalink)
dezinerite www.bpdir.com Join Date: Jul 2007 Posts: 3,051 Follow @bpdir	mysql backup messed up! Please help! I backed up my site's mysql database and when I reloaded the database, some letters & punctuations turned into symbols/characters. What happened, and how can I fix this? My site's charset is Latin1. Is there a possibility that it was somehow converted to utf8 when it was being backed up so I reloaded the utf8 instead of the Latin1? __________________ Premium DoFollow Directory \| Seychelles Triathlon Federation \| Free Directory Since 2007!

Currently Active Users Viewing This Thread: 1 (0 members and 1 guests)