Suggested fix for MySQL encoding issues

optimiced

Hello,

First time here, first-time Zen Gallery user, first-time suggestion-maker! ;-)

OK, the Problem:

As you probably know, often the MySQL database default encoding is set to Latin-1 (and the collation to latin1-swedish-ci - MySQL is developed by Swedes, after all;-).

On the other hand, if your web application uses UTF-8 encoding (a quasi-standard nowadays), then you might get into trouble, especially if you write not in English, but French, Bulgarian, etc. (use of Cyrillic and/or accented characters, etc.). Your front-end will be fine, but in the MySQL database you'll see a lot of funny characters (for example: "РҐСѓРјРїС РќСѓРјРїС") and this might lead to other troubles in the future, as well (when making database backup and restore).

This problem usually can be fixed, either by:
1. changing the default encoding of the MySQL database to UTF-8 (and the collation to 'utf8_unicode_ci', for example) -- an option which is not usually available in many shared hosting environments, or,
2. by specifying the encoding during the connection of the web app to the MySQL database -- this is one of the best options, I think.

So, I installed yesterday Zen Gallery for the first time, made a few quick checks, and found that front-end is OK, text is readable (the Gallery will be using English and Bulgarian languages, all UTF-8), but in the MySQL database things look like "РҐСѓРјРїС").

I've run into this problem in the past, several times (not all web applications specify the encoding, when establishing the database connection, unfortunately) and using the 'trial-n-error' method, finally came up with the following fix (it's an old classic, actually):

1. Open the file `functions-db.php`, and...
2. ...find line #71, then change the following code from:
`$result = mysql_query($sql, $mysql_connection);

if (!$result && !$noerrmsg) {`
to:
`$result = mysql_query($sql, $mysql_connection);

mysql_query("SET NAMES 'utf8'");

if (!$result && !$noerrmsg) {`
(just to be on the safe side with the MySQL collation, too, the code could be:
`$result = mysql_query($sql, $mysql_connection);

mysql_query("SET NAMES 'utf8' COLLATE 'utf8_unicode_ci'");

if (!$result && !$noerrmsg) {`

After the fix is applied, things go back to normal: everything is encoded as UTF-8 both in the MySQL database and in the front end of the Zen Gallery.

Drawbacks of this fix:
1) For existing users of Zen Gallery, this method might not work, as the data in the MySQL database could be already encoded in the wrong encoding, and to make this all work could require quite a bit of time (and resources) -- see this article, for example.
2) So, if you make an upgrade, you should not have this line added in the `functions-db.php` file...
3) ..But for new users of Zen gallery, this fix would be more than welcome, as it'll probably prevent 99.9% of the (possible) problems with the encoding in the MySQL database data.

I'd suggest, then, not to hardcode the charset/collation in the `functions-db.php`, but go the WordPress way -

WordPress users have once had this problem. The fix was the same in the older versions of WP - 'hack' manually the PHP file, in which the connection with the MySQL is establiched (`wp-db.php`); then, after an upgrade, hack the file again. Since WP v.2.2, though, (or maybe 2.1, I am not sure), the MySQL charset and collation could be optionally set in the settings file (wp-config.php) - in our case, this should be `zp-config.php`.

So, say you specify:

`define('DB_CHARSET', 'utf8');

define('DB_COLLATE', 'utf8_unicode_ci');`

in wp-config.php, if you install a new WordPress blog - this prevents future MySQL encoding issues; and if you're an old WP user, you leave these two settings blank, and things continue as they were.

I think, a similar solution could be made in Zen Gallery -- and I am sure, there would be a lot of happy users around the world, which will appreciate this 'SET NAMES' fix! :-)

I am no PHP/MySQL coder, but I hope I was of some help, suggesting this fix for the MySQL encoding in Zen Gallery... :-) I am not adding this proposed fix to the TRAC system, as I am not sure how it'd be best implemented in Zen, but I've seen it work in WordPress, and if I might help with anything else, I would be more than happy to!

Cheers!

PS I just discovered the Zen Gallery and I am very happy with its lightweightness, and also I've found the Doug Bowman's Gallery Templates were ported as a theme, which is great news, indeed! Keep up the good work!

trisweb

Optimiced - thank you very much for your suggestion. We'll be sure to think about that one, and I like your solution so I'm pretty sure we'll implement it that way and it should be in the next release.

Thanks again.

P.S. - In the future, and if you like right now, could you use the bug tracker for things like this? http://zenphoto.org/trac/newticket

sbillard

In tonight's build

optimiced

Hello, trisweb!

Sure, I don't mind adding bugs to the TRAC. But I'm a newcomer, so I wanted first to throw the idea here...

Also, I hope you'll try to implement the fix the way WordPress did it, because the simplified version (which I used to fix things for me locally - simply adding the SET NAMES to the 'functions-db.php' file), will for sure break things for lots of users... The WP way is a bit more complicated (you specify the encoding and collation *optionally* in the wp-config.php file [functions-db.php in Zen's case], then the application uses these settings, if they're specified, when connecting to the database), but the risk of breaking anything with the encoding is minimal...

Let me know, if I can contribute a bit more to the idea:)

--Michel
(www.optimiced.com)

PS Now going to register at the TRAC ;-)

sbillard

The fix is in the zenphoto version of the WP way. A line in zp-config.php that controls the insertion of this query. Setup will edit it to cause the insertion when the install is fresh. Otherwise the user can edit it if he wants the query.

optimiced

@sbillard:

Sounds very good! Will be very curious to see next version;-)