Database encoding settings

[replytomk3]

Hello

I have been to lazy to get this straightened out in the beginning.

When I connect to an existing database from a new CMSMS install, I get weird characters on the frontend pages (website).

It seems like default config.php settings are

Code: Select all

$config['default_encoding'] = 'utf-8'; $config['admin_encoding'] = 'utf-8';

But the database will specify something like

Code: Select all

DEFAULT CHARACTER SET latin1 COLLATE latin1_swedish_ci

I might have posted about a solved issue, but I want to repost anyways for new people to see this.

I would appreciate a clear explanation on specifying / finding out encoding settings when connecting to an existing database.

[replytomk3]

http://forum.cmsmadesimple.org/index.php/topic,15578.msg160093.html#msg160093

[replytomk3]

Anyone? Still looking for THE answer.

[replytomk3]

The issue seems to be my mistake of running /install during a 1.6 upgrade, after I just overwrote all files on server. That must be /install/upgrade.php. I have done my best to document this in the wiki Documentation.

The following has been suggested on IRC, but not tested yet:
To solve this issue, rollback to 1.5.x. Rollback to the previous (pre-upgrade) database as well. Overwrite all files with 1.6 release. Run /install/upgrade.php again.

Tell me if that solves the problem or not. I have ran into stupid hosting limitations which prevent me from testing this.

I have just tested this.

I have installed the native CMSMS 1.2.5. I have created a new database, and have imported from the backup of the old (non-upgraded database). The characters are still there.

Therefore, we are still back to this being either an import problem (importing to mysql 5.0), or wrong encoding settings specified in config.php.

[replytomk3]

There's this: http://www.devcha.com/2008/03/convert-existing-mysql-database-from.html.

The question is then, how do I determine the original encoding of the database from a mysql dump file?

My dump says: DEFAULT CHARACTER SET latin1 COLLATE latin1_german2_ci;

[replytomk3]

Here is the very helpful response from Peciura:

I can see from sql your DB was not utf8 default collation was German.
There are sings like
"Â"
"–"
(generally they are quotes, apostrophes, and unused characters)


1. You have to set collation ("utf8_unicode_ci" or "utf8_general_ci") of DB by hand otherwise it will not work.
2. Then From SLQ file remove this line :
CREATE DATABASE `db217060405` DEFAULT CHARACTER SET latin1 COLLATE latin1_german2_ci;
USE db217060405;"
You do not need it because of (1.)
3. Also delete all occurrences of
DEFAULT CHARSET=latin1 COLLATE=latin1_german2_ci
DB settings (1.) will take effect.
4. Find-replace wrong symbols
5. Import sql file to test environment
6. when you are satisfied with result overwrite original DB

Exactly what I was looking for!

[Peciura]

On step 4 you need very good text editor:
quick (lots of find-replace to do),
reliable  and also
has binary editor

[replytomk3]

Update: I forgot to tell everyone what I have found.

NO CHANGES TO THE DATABASE ARE NECESSARY.

All you need to do is login into PHPMyAdmin, find out the encoding of tables (not the whole database from that create database line at the top), and input that encoding into config.php.

That's it!

Contact me if you too run across this problem.

[nils73]

Thanks for sharing this. I ran into this problem several times and never found a good solution. This seems to do the trick so I will give it a try next time I have to backup and restore my database.

Regards
Nils

[krussell]

Lifesaver! Brilliant tip, thanks.
I ran into the same problem after an upgrade and found posts about running scripts to trawl the tables for odd characters. However, as per your post above, all I had to do was edit config.php to change the default utf-8 encoding to utf8_general_ci, to match my database:
$config['default_encoding'] = 'utf8_general_ci';
$config['admin_encoding'] = 'utf8_general_ci';

N.B. you need to clear the cache in Site Admin/Global Settings/Advance Setup to see the changes

[polodesign]

I ran into this problem again a couple of weeks ago. I wish I'd searched the forum at that point but it was a smallish site with not too many odd characters to fix by hand. I've saved the URL for this topic for the next export I do. Thanks!