Strange characters after restoring

Get help with installation and running phpBB 3.0.x here. Please do not post bug reports, feature requests, or MOD-related questions here.
Scam Warning
Forum rules
END OF SUPPORT: 1 January 2017 (announcement)
Locked
agit
Registered User
Posts: 11
Joined: Sat Mar 29, 2003 1:30 pm
Location: Amsterdam, the netherlands
Contact:

Strange characters after restoring

Post by agit » Sat Nov 28, 2009 9:02 pm

Hi everybody,

I upgrade from 3.0.4 to 3.0.6 and which ended with alot of errors. After that i had to resotre my database but now my post and topics have strange charachters.
Like this: Helbest û Pirtûk .
Tables are utf8_bin and the database is now latin1_swedish_ci

Does anyone know what i have to do?

User avatar
stevemaury
Support Team Member
Support Team Member
Posts: 50561
Joined: Thu Nov 02, 2006 12:21 am
Location: The U.P.
Name: Steve
Contact:

Re: Strange characters after restoring

Post by stevemaury » Sat Nov 28, 2009 10:02 pm

The collation of the database and all its tables should be utf8-bin.
For REALLY good and VERY inexpensive hosting CLICK HERE

I can stop all your spam. I can upgrade or update your Board. PM or email me. (Paid support)

agit
Registered User
Posts: 11
Joined: Sat Mar 29, 2003 1:30 pm
Location: Amsterdam, the netherlands
Contact:

Re: Strange characters after restoring

Post by agit » Sat Nov 28, 2009 10:48 pm

Thankx fof replying.

I also found out that you cann't correct this. You have to change the characters one by one.

For my site i had to change code like this:

Code: Select all

update phpbb_topics set topic_title = replace(topic_title, 'Ç', 'Ç');
update phpbb_forums set forum_name = replace(forum_name, 'Ç', 'Ç');
update phpbb_users set username = replace(username, 'Ç', 'Ç');
update phpbb_users set username_clean = replace(username_clean , 'Ç', 'Ç');
update phpbb_posts set post_text = replace(post_text , 'Ç', 'Ç');

update phpbb_topics set topic_title = replace(topic_title, '’', '’');
update phpbb_forums set forum_name = replace(forum_name, '’', '’');
update phpbb_users set username = replace(username, '’', '’');
update phpbb_users set username_clean = replace(username_clean , '’', '’');
update phpbb_posts set post_text = replace(post_text , '’', '’');
'Ç =Ç
’ = ’
But check out your code, ‘ = ‘ doens't have to be right for you to.

See also: http://www.phpbb.com/community/viewtopi ... &t=1110045

MidnightCoder
Registered User
Posts: 16
Joined: Tue Sep 23, 2008 12:05 pm

Re: Strange characters after restoring

Post by MidnightCoder » Sat Nov 28, 2009 11:00 pm

Don't try to do it that way. I've been there, and it's pure horror. You should restore the database again, making sure that the target database and its tables are properly collated, and that your mysql connection charset is set to UTF8. You might have to find a way to explicitly state that on some servers, I've run into this problem a couple times but I'm so slammed with other issues today I can't hunt it down for you.

If the table creation lines in your database backup have incorrect collation you can change them to utf8 in a text editor before you begin the new restore.

EDIT: If your database backup itself is corrupted with the garbage characters, and you still have access to a copy still on the server, you should copy it into a new database on the server that uses the correct collation before taking a new backup (such as when doing a server move, you'd still likely have access to the old database - if it's been trashed on the server and the corrupt backup is all you have... try this next idea...)

Using Bigdump is an excellent method of restoring databases, and you can define the connection charset in the configuration section of the file. This gets around some weirdness I've run into using various versions of phpmyadmin.

Failing all that, if you google the problem you can find some character filters, and you can use those to replace the garbage characters locally, before you restore it. It's faster and easier than trying to do it in phpmyadmin, as you're just running preset find/replace operations on a text file. They're not perfect, however, and you'll still have the truncated post issue...

Locked

Return to “[3.0.x] Support Forum”

cron