Character sets in MySQL wrong by installation script?

[merktnichts]

I just did the following queries in MySQL:


mysql> show variables like '%character_set%';
+--------------------------+----------------------------+
| Variable_name | Value |
+--------------------------+----------------------------+
| character_set_client | latin1 |
| character_set_connection | latin1 |
| character_set_database | latin1 |
| character_set_filesystem | binary |
| character_set_results | latin1 |
| character_set_server | latin1 |
| character_set_system | utf8 |
| character_sets_dir | /usr/share/mysql/charsets/ |
+--------------------------+----------------------------+


mysql> show variables like '%collat%';
+----------------------+-------------------+
| Variable_name | Value |
+----------------------+-------------------+
| collation_connection | latin1_swedish_ci |
| collation_database | latin1_swedish_ci |
| collation_server | latin1_swedish_ci |
+----------------------+-------------------+

That's confusing me a bit cause in another post I read (http://forums.opengarden.org/showpost.php?p=2742&postcount=2) it should all be utf8_general_ci for the collation variables and utf8 for the character_set_% variables.
Do my current settings have negative influence on how Deki works?

I remember that we had several reports concerning foreign characters and the Mindtouch crew fixed all of them, but the queries I did not make me wonder now if it was just a matter of wrong charsets?

The origin of my MySQL database is from a clean setup with a pure Deki 1.8.1 (without any letter appended). When I did that weeks ago (when 1.8.1 was current), I was following the installation guide religiously, so if there's something wrong with my charsets, the installation script most probably was the culprit.

Is there a way to correct it all without losing or changing our data? I wouldn't wanna destroy all the ä's, ö's, etc. And there are many of them in our DB now...

[royk]

Looks like our initial install scripts are faulty. Max has provided this query from the mySQL docs which change your charset and collations on a per-table basis:

ALTER DATABASE db_name
[[DEFAULT] CHARACTER SET charset_name]
[[DEFAULT] COLLATE collation_name]

You'll also need to change the collation and charset on the fields that I mentioned in that previous post.

[merktnichts]

I'm bad at MySQL stuff and as a result I rendered the whole DB unusable. Thank god I backupped my VM before.

Could you supply some php script that guys like me can execute to fix all this stuff automatically? Playing with all this MySQL db stuff without knowing what I'm doing is way too risky, Deki didn't work at all afterwards (some fatal error). If I hadn't had a backup of the VM, all our data would have been lost. Very bad. (Work from weeks!!!) :(

[merktnichts]

Could you supply some php script that guys like me can execute to fix all this stuff automatically?
Hmmm.... I guess there's not much interest in (providing) such a fixing script?

[PeteE]

Hmmm.... I guess there's not much interest in (providing) such a fixing script?

merktnichts - We actually do have a script that runs some ALTER table commands when converting from Gooseberry to Hayes. However, it's not run when upgrading between version of Hayes. My guess is that the 1.8.1 release had some tables still using the old charset and collation. Could you try running the following command and see if that does the trick?


mysql -u root -p<yourpass> wikidb < /var/www/deki-hayes/maintenance/archives/patch-utf8_table_conversion.sql

The patch-utf8_table_conversion.sql runs the following:


alter table `archive` convert to character set utf8;
alter table `attachments` convert to character set utf8;
alter table `brokenlinks` convert to character set utf8;
alter table `links` convert to character set utf8;
alter table `linkscc` convert to character set utf8;
alter table `logging` convert to character set utf8;
alter table `objectcache` convert to character set utf8;
alter table `old` convert to character set utf8;
alter table `querycache` convert to character set utf8;
alter table `recentchanges` convert to character set utf8;
alter table `site_stats` convert to character set utf8;
alter table `watchlist` convert to character set utf8;


Hopefully that covers all the tables you need. If not, let us know and we can fix it.

Thanks!
pete

[merktnichts]

PeteE, thanks for your answer. I just ran the script as you discribed and it looks like it didn't change anything. The queries I ran in the first post give exactly the same output.

Edit:
After reading your post again I noticed that the script changes the tables (which seem to be utf8 here). I think the queries Roy mentioned give some details about the database in whole, don't they? The deki tables are utf8, as they should. It's just that some of those database variables are Latin1 or Swedish (see first post). Don't ask me why it's Swedish, I've never set it to that. (German here anyway.)

Any clues? Or should I just forget about that and shouldn't worry anymore. I'm a bit confused now...