After restore database attachment and language problem

[Neverhurry]

After restore database to a new database of other site, i got attachment and language problems, pls see the attachment,
1, all the text become fxxxx it should be in chinese-simplified-utf8,
2, the second attachment folder, its numbers are 0, and its status is unused (that is why the attachment from the second attach folder can not be shown).

PLEASE HELP!

[Neverhurry]

OK, File Maintenanc in ACP under attachments and avatars repaired the attachment issue, but the language problem is still there, having no idea, can anybody help? That will be very much appreciated.

[CommanderMadi]

Maintenace>Recount all Statistics

[Neverhurry]

Maintenace>Recount all Statistics

thanks, i have tried that, no luck. 

[Allusion]

Was the original forum in UTF-8?

Is the page encoding on the new forum in Unicode (UTF-8)? (View menu --> Character Encoding in Firefox, View menu --> Encoding in IE)

Did you export and import the SQL file in the UTF-8 file format?

Is the collation of SMF tables and table fields in the new database set to utf8_general_ci or utf8_unicode_ci?

Have you uploaded the Chinese (Simplified) language pack to the new forum and set it as default?

[CommanderMadi]

mmmm.Okay, Go to Forum Maintenance>Database>Convert the database and data to UTF-8 and Click Run task now.. That may work

[Neverhurry]

mmmm.Okay, Go to Forum Maintenance>Database>Convert the database and data to UTF-8 and Click Run task now.. That may work

Thanks. Tried it, no luck again.

The collation of SMF tables and table fields in the new database set to latin1_swedish_ci (very strange, by default) and I checked other smf database they should be set to utf8_general_ci.

Is there a fast way to change all the collation latin1_swedish_ci to utf8_general_ci? that will be great, otherwise i have to manually edit every....

[Allusion]

phpMyAdmin should have set DEFAULT CHARSET=utf8 for every table, unless it's a very old version or you unselected some options when you exported the database. Can you open the SQL file and verify that the charset is specified?

You can also try this: drop (delete) and recreate the new database, or (if other scripts are using that database) remove the SMF tables, then select the database and change the default collation to utf8_general_ci from the Operations menu in phpMyAdmin. Then reimport the SQL file. Tables should now be recreated using the default collation of the database if the SQL doesn't specify the charset.

[Neverhurry]

I exported the db from SMF ACP, not with phpMyAdmin.

Can you open the SQL file and verify that the charset is specified?

I opened the sql file but i didn't find any charset, no latin1_swedish_ci nor utf8_general_ci.

You can also try this: drop (delete) and recreate the new database, or (if other scripts are using that database) remove the SMF tables, then select the database and change the default collation to utf8_general_ci from the Operations menu in phpMyAdmin. Then reimport the SQL file. Tables should now be recreated using the default collation of the database if the SQL doesn't specify the charset.

That is too difficult for me....

[Allusion]

You can also try this: drop (delete) and recreate the new database, or (if other scripts are using that database) remove the SMF tables, then select the database and change the default collation to utf8_general_ci from the Operations menu in phpMyAdmin. Then reimport the SQL file. Tables should now be recreated using the default collation of the database if the SQL doesn't specify the charset.

That is too difficult for me....

Okay, let's step back a bit.

1. Can you use phpMyAdmin?
2. Do you still have access to the original forum and database?
3. How did you create the database on the new forum?
4. Did you use an existing database or did you create a new one?

[Neverhurry]

1. Can you use phpMyAdmin?

A little, not too much.

2. Do you still have access to the original forum and database?

Yes, i do.

3. How did you create the database on the new forum?

i did it from here:

Administration Center    * Maintenance          o   Forum Maintenance...   + Database

Backup Database

Download a backup copy of your forums database in case of emergency.
             

4. Did you use an existing database or did you create a new one?

I use an existing database

[Neverhurry]

I have found from internet:

If you have a (gs) Grid-Service and wish to change the character set of a MySQL database you may use PHPMyAdmin which is available through the AccountCenter. You can also change the character set and collation of a database by using the following MySQL command:

ALTER DATABASE `db#####_dbname` DEFAULT CHARACTER SET [charset_old] COLLATE [charset_new]

Allusion, can you tell me if the following command has any mistakes?

Code Select Expand

ALTER DATABASE `moment10` DEFAULT CHARACTER SET latin1_swedish_ci COLLATE utf8_general_ci
do i have to add write "[" and "] and " ";" in the end?

[Neverhurry]

when i use

Code Select Expand

ALTER DATABASE `moment10` CHARACTER SET latin1_swedish_ci COLLATE utf8_general_ci i got this error:

#1115 - Unknown character set: 'latin1_swedish_ci'

[Neverhurry]

i found this:

WARNING: Attempting to change the database character set to a character set that is not a strict superset can result in data loss and data corruption. To ensure data integrity, whenever migrating to a new character set that is not a strict superset, you must use export/import. It is essential to do a full backup of the database before using the ALTER DATABASE [NATIONAL] CHARACTER SET statement, since the command cannot be rolled back. The syntax is:

ALTER DATABASE [<db_name>] CHARACTER SET <new_character_set>;
ALTER DATABASE [<db_name>] NATIONAL CHARACTER SET <new_NCHAR_character_set>;

The database name is optional. The character set name should be specified without quotes, for example:

ALTER DATABASE CHARACTER SET WE8ISO8859P1;

Can anybody tell me, if it is enough to make a query like this:

Code Select Expand

ALTER DATABASE CHARACTER SET utf8_general_ci

Thank you!!!

[Allusion]

3. How did you create the database on the new forum?

i did it from here:

Administration Center    * Maintenance          o   Forum Maintenance...   + Database

Backup Database

Download a backup copy of your forums database in case of emergency.

I understand, but I didn't ask how you backed up the old database. I asked how you created the new one.

4. Did you use an existing database or did you create a new one?

I use an existing database

Do you know how the existing database was created?

Can you create a new database in phpMyAdmin, for the new forum? Or do you need to use an existing database?

Allusion, can you tell me if the following command has any mistakes?

Code Select Expand

ALTER DATABASE `moment10` DEFAULT CHARACTER SET latin1_swedish_ci COLLATE utf8_general_ci
do i have to add ";" in the end?

The command is not correct. The example isn't correct either.

If you're sure that the database name is correct, you can use this command:

Code Select Expand

ALTER DATABASE `moment10` DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci

Note that cPanel and some other control panels create databases with the database username as a prefix of the database name; for example, if the MySQL user is mnt10 and you enter moment10 as the database name, the full database name will be mnt10_moment10.

And no, you don't have to add ";" in the end if you use phpMyAdmin (it adds the ";" automatically).

However... are there other tables in that database? In other words, is that database used by other scripts?

[Neverhurry]

Hi Allusion, thanks for your reply. I used your cole, phpMyAdmin says it is successful, but the languauge collation remains untouched in the tabls, it is strange.

IT IS STILL latin1_swedish_ci, maybe the code has some problem?

Code Select Expand

ALTER DATABASE `moment10` DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci

Is it possible that "SET utf8" should be changed to "SET latin1_swedish_ci" which is the older character set?

I understand, but I didn't ask how you backed up the old database. I asked how you created the new one.

I created the db from cpanel, through database wizard, like always.

Can you create a new database in phpMyAdmin, for the new forum? Or do you need to use an existing database?

yes, i can create a new db in phpMyAdimin for the new forum. I didn't use an existing database, nor do i need to.

However... are there other tables in that database? In other words, is that database used by other scripts?

How can I find are there other tables in that database used by other scripts? That's a problem. I get the db through SMF's db backup function, maybe it includes automatically all the added tables from installation mods. Is there a list of SMF tables so I can check if there are other tables used by other scripts?

[Neverhurry]

Hi Allusion, I dropped all the tables of the db. Select the db in phpMyAdmin, went to the page "operation", at the bottom, chose "utf8_general_ci" and clicked "go", and that did the trick. Thank you very much for your help. All the best with your SMF forum.

[Allusion]

I'm glad you managed to fix the issue.

How can I find are there other tables in that database used by other scripts? That's a problem. I get the db through SMF's db backup function, maybe it includes automatically all the added tables from installation mods. Is there a list of SMF tables so I can check if there are other tables used by other scripts?

The SMF database backup function includes all tables with the SMF database prefix (smf_ by default) in their names. So it's just a matter of looking at the list of tables in the database using phpMyAdmin. If all tables have names starting with smf_, you're fine.

If I may give you an advice: Don't back up the SMF database from SMF. There are some bugs that might cause problems in the future. SMF also times out when dealing with large databases.

The database backup functionality in cPanel (in the Backups page) works very well and should automatically include the charset declaration I mentioned previously.

phpMyAdmin is also very good, but keep in mind that it has limitations imposed by the PHP configuration regarding the size of SQL files (compressed or not) that you can import.

There are other ways and scripts you can use to import databases correctly, but it's very important that you have correct exports (backups) in the first place.

[Neverhurry]

Allusion, thanks a lot for info. Is the The database backup functionality inside the Cpanel -> Files -> backups -> Download a MySQL Database Backup? If yes, then i found it. Great stuff. After that, one can very easily choose "restore a MySQL database" to restore.... just amazing.