Set default collation of the database tables during initial install

[Freder]

CHANGED to 'Set default collation of the database tables during initial install'

Unfortunately CPG (as of 1.4.10) lacks this kind of functionality. I use it with the 'Polish' language setting, but any Polish characters sent to the database return as any variation of '?' signs... I patched my version like so (in functions.inc.php):

Code Select Expand

function cpg_db_connect()
{
        global $CONFIG;
        $result = @mysql_connect($CONFIG['dbserver'], $CONFIG['dbuser'], $CONFIG['dbpass']);
        if (!$result) {
                return false;
        }
        if (!mysql_select_db($CONFIG['dbname']))
                return false;
        mysql_query("SET NAMES 'latin2'");
        return $result;
}


After manually changing the encoding of the fields in the tables to 'latin2_bin' it works.

I suppose this is a 'hack' kind of solution, I suggest connecting this to the language selection in the configuration panel and using UTF-8 or some similar clean solution.

[Nibbler]

Coppermine already uses UTF-8 exclusively.

[Freder]

Yes, it does for displaying text, it does not during transactions with MySQL. This causes corruption of Polish (and any other unusual) characters. I suppose this can be fixed by setting the defaults in the database, but this 'per session' setting overrides it anyway, so setting it would create minimum overhead and guarantee correct characters (as long as the tables are set up correctly).

I suppose I will investigate this phenomenon further.

EDIT:

Yes, utf8 works without my hack. But only if the database is set to perform all transactions in 'utf8_unicode_ci' (or any variation of utf8_ )and the collation for fields in the tables are set to 'utf8_unicode_ci' (same as previous).

I'll take a look at the functions creating the databases to confirm where the problem lies (the fields had some strange collation in my DB; 'latin1_swedish_ci')

[Joachim Müller]

It's not surprising imo that you need to set the collation of your database to utf-8 if you want to store utf-8 content in it.

[Freder]

You are right. My bad in not finding that out earlier, that would save me a lot of fixing right now. :-)

Anyway, I checked the 'shema.sql' and 'install.php' files, they do not set the collation anywhere. This is why my original install corrupted the stings. After the initial install it is quite some work to fix all the collation settings for the tables (every field has to have it changed). Wouldn't it be easier to set 'DEFAULT CHARACTER SET utf8 COLLATE utf8_unicode_ci' in 'shema.sql' from the start? Or perhaps at least a mention of the issue in the install docs should be made (there is none at the time)...

[Joachim Müller]

OK, let's re-phrase your feature request then: make coppermine set the collation of the database tables up during initial install. OK?

[Freder]

Yes, that would make things much easier for people who have no idea about collation or character sets (and do not have to with UTF-8) like I did when I first installed Coppermine. 

BTW thanks for a great Gallery!

[Nibbler]

You can't specify the character set on pre 4.1 versions of mysql - that's why it's not in the schema.sql.

[Freder]

True, that produces an error. Maybe the database could be tested to be 'collation compliant'? I suppose somebody can be using pre 4.1 MySQL, but is it likely? I don't think it would be a good idea to duplicate any files (like 'shema.sql')...