New to the community? Start here

Multi-Db v3.1

After upgrading, I got some of the funny words like below;

â€¦!

You can view some “Live” example here > http://cca.sg/wiki/about/sports-for-life/program-outline/

Mon, 17 Oct 2011 5:58:42

Philip John
- DEV MAN’s Apprentice
- 754 pts: Hero points 8,909 pts: Rep. points LEVEL 26
Hiya!

First off, can you check that the encoding on all your databases is the same please? It should be utf8_general_ci

Thanks,

Phil
Aaron
- Ex Staff
- 11,047 pts: Hero points 20,397 pts: Rep. points LEVEL 30
Along with what encoding your databases and speciifcally blog tables are in, what is set in wp-confing for:

DB_CHARSET and DB_COLLATE
Barry
- DEV MAN’s Mascot
- 526 pts: Hero points 6,840 pts: Rep. points LEVEL 24
As Phil says, can you check the encoding of your database and your tables, it should match what it set in your wp-config.php file with the DB_CHARSET define. The default it UTF8, so if your tables and db aren’t encoded with that then you will need to either change the db, or change the wp-config define so that they match.
Barry
- DEV MAN’s Mascot
- 526 pts: Hero points 6,840 pts: Rep. points LEVEL 24
Ah – it looks like everyone had the same idea :slight_smile:
Kwee Chek
- The Incredible Code Injector
- 60 pts: Hero points 483 pts: Rep. points LEVEL 5
ok – I’ve checked the wp-config.php as follows;

/** Database Charset to use in creating database tables. */

define(‘DB_CHARSET’, ‘utf8’:wink:;

/** The Database Collate type. Don’t change this if in doubt. */

define(‘DB_COLLATE’, ”:wink:;

So, where can I check the encoding of my databases? Thank you.
Kwee Chek
- The Incredible Code Injector
- 60 pts: Hero points 483 pts: Rep. points LEVEL 5
It seems that is an old issue:

https://vvpmudev.com/forums/topic/multidb-305-breaks-just-like-other-older-versions-for-strange-characters

Should I follow the steps in this thread;

https://vvpmudev.com/forums/topic/critical-character-issue-bug-report
Kwee Chek
- The Incredible Code Injector
- 60 pts: Hero points 483 pts: Rep. points LEVEL 5
I’ve this in my db.php;

if ( is_multisite() ) {

$this->charset = ‘utf8’;

if ( defined( ‘DB_COLLATE’ ) && DB_COLLATE )

$this->collate = DB_COLLATE;

else

$this->collate = ‘utf8_general_ci’;

} elseif ( defined( ‘DB_COLLATE’ ) ) {

$this->collate = DB_COLLATE;

}

if ( defined( ‘DB_CHARSET’ ) )

$this->charset = DB_CHARSET;
Kwee Chek
- The Incredible Code Injector
- 60 pts: Hero points 483 pts: Rep. points LEVEL 5
I've check in the phpMyAdmin and explore the database which have the following screen below & it shown "lati1_swedish_ci". Any permanent solution on this as it seems like it occurs in previous Multi-db upgrade?

[attachments are only viewable by logged-in members]
Aaron
- Ex Staff
- 11,047 pts: Hero points 20,397 pts: Rep. points LEVEL 30
Have you tried changing DB_COLLATE to latin1_swedish_ci?

Change to define('DB_COLLATE', 'latin1_swedish_ci');

I think that will solve your problem. Of course ideally in the long run you want all your tables to be utf_unicode to support other languages.
troykd
- The Crimson Coder
- 75 pts: Hero points 617 pts: Rep. points LEVEL 6
Sounds like the accented character issue that was reported a long time ago is still continuing through each upgrade.

https://vvpmudev.com/forums/topic/accented-characters

Why are we patching still?
Mark de Scande
- Syntax Hero
- 606 pts: Hero points 1,049 pts: Rep. points LEVEL 8
Hey guys i have also seen this on BlogLines.co.za â€¦! Â

Also After Upgrade to 3.1

I did log in to MySql and i did change the utf8_general_ci

Let me know if you guys want to have a look around :slight_smile:
Aaron
- Ex Staff
- 11,047 pts: Hero points 20,397 pts: Rep. points LEVEL 30
You need to change DB_COLLATE in wp-config to the true collation of your tables.
Mark de Scande
- Syntax Hero
- 606 pts: Hero points 1,049 pts: Rep. points LEVEL 8
@Aaron Buddy i have to ask a silly question please what need to go it to DB_COLLATE

This is how the file is at the moment and how it always was :slight_smile:
```
/** Database Charset to use in creating database tables. */

define('DB_CHARSET', 'utf8');



/** The Database Collate type. Don't change this if in doubt. */

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

Mark de Scande

Syntax Hero

606 pts: Hero points 1,049 pts: Rep. points LEVEL 8

@Aaron Buddy But the outher day i was told not the change any thing the the wp-config.php

So now here is the question

The DB blogline_wrdp1 is still on the server but no longer used as the new DB is blogline_global can i not change it in wp-config.php

define('DB_NAME', 'blogline_wrdp1');



/** MySQL database username */

define('DB_USER', 'blogline_wrdp1');



/** MySQL database password */

define('DB_PASSWORD', 'smile');



/** MySQL hostname */

define('DB_HOST', 'localhost');



/** Database Charset to use in creating database tables. */

define('DB_CHARSET', 'utf8');



/** The Database Collate type. Don't change this if in doubt. */

define('DB_COLLATE', 'utf8_unicode_ci');

Barry
- DEV MAN’s Mascot
- 526 pts: Hero points 6,840 pts: Rep. points LEVEL 24
I did log in to MySql and i did change the utf8_general_ci

What did you change to utf8_general_ci? Can you walk us through the steps you took, because it’s quite a complicated change to make.
Mark de Scande
- Syntax Hero
- 606 pts: Hero points 1,049 pts: Rep. points LEVEL 8
@Barry

I log in to PHPMyAdmin and did this to 16 DB + 1db

Ie blogline_wrdp1 Then Operations Then Collation: and i clicked GO "ScreenShot"

So my question is

1) The DB blogline_wrdp1 is still on the server but no longer used as the new DB is blogline_global can i not change it in wp-config.php ?

2) define('DB_COLLATE', '':wink:; I have remove the utf8_unicode_ci from wp-config.php as BlogLines.co.za was not pulling up the front page.

3) http://pastebin.com/4wNxk35k Err Log for you to have a look at from

I hope this helps :slight_smile:

[attachments are only viewable by logged-in members]
Mark de Scande
- Syntax Hero
- 606 pts: Hero points 1,049 pts: Rep. points LEVEL 8
Last one :slight_smile: is this a bug or not

If i run this below what DB is it working on :slight_smile:

http://bloglines.co.za/wp-admin/maint/repair.php

Please let me know of the outher points i have made in this post tread.
Barry
- DEV MAN’s Mascot
- 526 pts: Hero points 6,840 pts: Rep. points LEVEL 24
What’s that repair.php file?
Mark de Scande
- Syntax Hero
- 606 pts: Hero points 1,049 pts: Rep. points LEVEL 8
@Barry It is what i build in to WordPress

http://codex.wordpress.org/Editing_wp-config.php#Automatic_Database_Optimizing

The script can be found at {$your_site}/wp-admin/maint/repair.php
Kwee Chek
- The Incredible Code Injector
- 60 pts: Hero points 483 pts: Rep. points LEVEL 5
Since it is caused by the upgrade, can I have a permanent solution as I do want my tables are still able to support other languages. We need to solve this urgently, as I’ve been receiving queries on this.

The wp-config.php setup has been there since day 1 of my installed.
Aaron
- Ex Staff
- 11,047 pts: Hero points 20,397 pts: Rep. points LEVEL 30
Freedom, you are welcome to downgrade if you need to on your production site for now.

Basically we fixed a long outstanding bug in previous versions, but the fix when applied to some sites with wrong encoding need to define their collate in wp-config.
Kwee Chek
- The Incredible Code Injector
- 60 pts: Hero points 483 pts: Rep. points LEVEL 5
@Aaron – downgraded.
Barry
- DEV MAN’s Mascot
- 526 pts: Hero points 6,840 pts: Rep. points LEVEL 24
@freedom – are your databases encoded with latin1? It seems that utf8 and latin1 are the two most common – if you can let us know then I can let you know the steps to have your system use that encoding system rather than utf8
Kwee Chek
- The Incredible Code Injector
- 60 pts: Hero points 483 pts: Rep. points LEVEL 5
@Barry – even after downgraded to 3.0.7, am still getting below funny character as shown attached but this time only one.

/** Database Charset to use in creating database tables. */

define('DB_CHARSET', 'utf8':wink:;

The above is my wp-config.php so my databases should be encoded with utf8 right? If not, how to check? Thank you.

[attachments are only viewable by logged-in members]
Barry
- DEV MAN’s Mascot
- 526 pts: Hero points 6,840 pts: Rep. points LEVEL 24
The above is my wp-config.php so my databases should be encoded with utf8 right? If not, how to check? Thank you.

Not necessarily no, that line means that WP will try to read your databases as UTF8, *but* if your databases and tables are latin1 encoded then you will get strange characters. You need to match the encoding in your wp-config.php with the encoding of your database.

What do you use to manage your db? phpmyadmin or some other app / web based system and I’ll pop up how to check the encoding.

Here are some steps to convert to utf8 (and some history on the charsets WP used), but lets see if we can take the easier step first :slight_smile:

http://codex.wordpress.org/Converting_Database_Character_Sets
Kwee Chek
- The Incredible Code Injector
- 60 pts: Hero points 483 pts: Rep. points LEVEL 5
Am using phpmyadmin.

I've check in the phpMyAdmin and explore the database which have the following screen below & it shown "lati1_swedish_ci".

[attachments are only viewable by logged-in members]
Barry
- DEV MAN’s Mascot
- 526 pts: Hero points 6,840 pts: Rep. points LEVEL 24
Ok, can you try the following and see if that gets rid of any of the strange characters –

In wp-config.php, set your DB_CHARSET to ‘latin1’

Set your DB_COLLATE to ‘latin1_swedish_ci’

Save it and upload to your server if you need to.

Note: adding handy link for future reference in case we need it:

http://dev.mysql.com/doc/refman/5.0/en/charset-charsets.html
Kwee Chek
- The Incredible Code Injector
- 60 pts: Hero points 483 pts: Rep. points LEVEL 5
I’ve upgraded back to v3.1 and did what you indicated. But, still having the same funny word.

What should I do next? Thank you.
Mark de Scande
- Syntax Hero
- 606 pts: Hero points 1,049 pts: Rep. points LEVEL 8
@freedom

Do you have this plug on your server ?

http://wordpress.org/extend/plugins/unfiltered-mu/

If yes remove it and see if the problem goes away

This is just a tough i had

I hope this helps.
Kwee Chek
- The Incredible Code Injector
- 60 pts: Hero points 483 pts: Rep. points LEVEL 5
@Mark de Scande

No, I have removed it long along. Thanks for the advice.
Barry
- DEV MAN’s Mascot
- 526 pts: Hero points 6,840 pts: Rep. points LEVEL 24
Is it just historic content that has this issue? If you enter new content do you get the same characters appearing?

If the older version still works ok for you, then roll back to that one and I’ll see if I can put together a little debugging plugin and get that up so we can see what is happening on your site, and whether it is keeping the correct encoding all the way through.
Barry
- DEV MAN’s Mascot
- 526 pts: Hero points 6,840 pts: Rep. points LEVEL 24
Note: assume you already did roll back, as I can’t see the characters now on the original url you gave.
Kwee Chek
- The Incredible Code Injector
- 60 pts: Hero points 483 pts: Rep. points LEVEL 5
@Barry

oh…the original link provided above has been updated manually.

Yes, you are right, it happens to only historic contents.

You may view below links for the funny characters including my sub-sites.

http://cca.sg/2011/10/17/wordpress-clinic-i/

http://arts.cca.sg/category/news/

What can we do now? Thanks.
Kwee Chek
- The Incredible Code Injector
- 60 pts: Hero points 483 pts: Rep. points LEVEL 5
hmm…any other solution? Thanks.
Mark de Scande
- Syntax Hero
- 606 pts: Hero points 1,049 pts: Rep. points LEVEL 8
@freedom Ps just give this a try it is not a fix but it will help

From SSH

mysqlcheck -u root -p --auto-repair --check --optimize --all-databases

If it ask for a pass word then you would have to ask you host for the MySql root pass or just ask him to run it for you
Kwee Chek
- The Incredible Code Injector
- 60 pts: Hero points 483 pts: Rep. points LEVEL 5
@Mark de Scande – Tried but doesn’t help. Any more advice? Hmm….my users confident will drop if every upgrade they need to edit their posts to get it right…..
Barry
- DEV MAN’s Mascot
- 526 pts: Hero points 6,840 pts: Rep. points LEVEL 24
@freedom – if it’s working with the older version then stay with that. I’ve just got back but am going to get a test plugin up as soon as I can so we can see what’s happening with your connection.
Kwee Chek
- The Incredible Code Injector
- 60 pts: Hero points 483 pts: Rep. points LEVEL 5
@Barry – as mentioned even with v3.07, I still have the funny characters but not as many as v3.1

Did as instructed for v3.07;

In wp-config.php, set your DB_CHARSET to ‘latin1’

Set your DB_COLLATE to ‘latin1_swedish_ci’

Should I change the Reading Settings for “Encoding for pages and feeds” to “latin1” as well? Currently it is “UTF-8”.
Mark de Scande
- Syntax Hero
- 606 pts: Hero points 1,049 pts: Rep. points LEVEL 8
@freedom This is the way mine is set up u so i guess the way you want to set it up is perfectly

/** Database Charset to use in creating database tables. */

define(‘DB_CHARSET’, ‘utf8’:wink:;

/** The Database Collate type. Don’t change this if in doubt. */

define(‘DB_COLLATE’, ‘utf8_general_ci’:wink:;

@Barry What i dont understand why is this setting needed now in the past it never use to be there DB_COLLATE was always empty now we have to add utf8_general_ci so it is not making any sense.

As for the rest of WP-Config.php what must go in there i have ask this a over and over and still not getting and good answers Sould we remove the old DB details and then add the global db details in there and then dump the old db from the system

@Barry thank you for always helping out
Mark de Scande
- Syntax Hero
- 606 pts: Hero points 1,049 pts: Rep. points LEVEL 8
@freedom I Google this and i got some post :slight_smile:

This was the fix Just un comment (I have tested it and it seems to work)

//define(‘DB_COLLATE’, ‘latin1_swedish_ci’:wink:;

//define(‘DB_CHARSET’, ‘latin1’:wink:;

http://www.northforkmedia.com/blogger/2008/09/strange-characters-and-in-wordpress.html

More Posts

http://wordpress.org/support/topic/strange-characters-in-past-posts

http://wordpress.org/support/topic/please-help-me-get-rid-of-these-a

I how this fix i provided is correct if not sorry for point you in to the wrong direction.
Kwee Chek
- The Incredible Code Injector
- 60 pts: Hero points 483 pts: Rep. points LEVEL 5
@Mark de Scande – Will try & thank you ! ONE POINT FOR YOU for being so handful !

@Barry – any comments on the recommendation to un-comment the two define lines?
Kwee Chek
- The Incredible Code Injector
- 60 pts: Hero points 483 pts: Rep. points LEVEL 5
Hi, we all various tries still couldn’t resolve the funny characters for those old posts…..
Barry
- DEV MAN’s Mascot
- 526 pts: Hero points 6,840 pts: Rep. points LEVEL 24
I think the problem you have now, is that you have a mixture of latin1 and utf8 characters in your database.

This can be confirmed by switching the settings in your config file, which will switch the weird characters between the old posts and the new ones.

I think the simplest / well only option, would be to transfer your database to a utf8 format.

Have a read of this page (which I posted a few posts up) http://codex.wordpress.org/Converting_Database_Character_Sets and see if you feel confident of attempting those steps?
Mark de Scande
- Syntax Hero
- 606 pts: Hero points 1,049 pts: Rep. points LEVEL 8
@Barry Make my day and give me a script to do this please :slight_smile: your 100% correct yes i have mix db latin1 and utf8

If we leave it like this what would happen in the long run ?
Mark de Scande
- Syntax Hero
- 606 pts: Hero points 1,049 pts: Rep. points LEVEL 8
@freedom This will convert the global DB http://wordpress.org/extend/plugins/utf-8-database-converter/installation/

I am working on a fix for the rest of the DB

Will update as soon as i have a fix
Mark de Scande
- Syntax Hero
- 606 pts: Hero points 1,049 pts: Rep. points LEVEL 8
I have a fix :slight_smile: let me just test it on my server and then i will give it to you :slight_smile:
Mark de Scande
- Syntax Hero
- 606 pts: Hero points 1,049 pts: Rep. points LEVEL 8
The Fix CONVERT TO CHARACTER SET utf8 COLLATE utf8_unicode_ci

First Make a back up of your DB and set this in your wp-config
```
/** Database Charset to use in creating database tables. */

define('DB_CHARSET', 'utf8');

define('DB_COLLATE', 'utf8_general_ci');
```
1) Just copy the code below to a text file rename it to mydb.php and upload it to your root
```
<?php

// your connection

mysql_connect("localhost","root","***");

mysql_select_db("blogline_global");



// convert code

$res = mysql_query("SHOW TABLES");

while ($row = mysql_fetch_array($res))

{

foreach ($row as $key => $table)

{

mysql_query("ALTER TABLE " . $table . " CONVERT TO CHARACTER SET utf8 COLLATE utf8_unicode_ci");

echo $key . " => " . $table . " CONVERTED<br />";

}

}

?>
```
2) Right click in FileZille and say edit mydb.php (Keep it open in your editor)

3) Right click in FileZille and say edit /home/blogline/public_html/wp-content db-config.php (Keep it open in your editor)

4) Put the first db name mysql_select_db(“blogline_global”:wink:; and just Ctrl S then have a look at FileZille and reupload the edit file to the server

5) Now run it Ie BlogLines.co.za/DB.php and hit enter give it a sec or to done

6) Now open PHPMyadmin and go to the DB and you will see it is not converted to utf8_unicode_ci

Now here is the work you have to do this for all your DB you have set up. I had to do it 16 times.

This works i have done it and it is working perfectly on http://www.bloglines.co.za

I hope this helps if it did rep point are welcome.
Kwee Chek
- The Incredible Code Injector
- 60 pts: Hero points 483 pts: Rep. points LEVEL 5
Is this plugin > http://wordpress.org/extend/plugins/utf-8-db-converter/ works on multi-db? Or can we have something like this being offer here as am sure it will benefits many members like me using the multi-db. Thank you.
Mark de Scande
- Syntax Hero
- 606 pts: Hero points 1,049 pts: Rep. points LEVEL 8
@freedom yest that is the one i used but it only did the first DB

Please read trough https://vvpmudev.com/forums/topic/multi-db-v31#post-147306 it was easy to do and it took me about a hour to get running
Kwee Chek
- The Incredible Code Injector
- 60 pts: Hero points 483 pts: Rep. points LEVEL 5
@Mark de Scande – I’ve 256 databases. Need to resolve it more efficiently. Problem is because of the WordPress & Multidb upgrades and left with the character set mis-match. Now, the users have to go through the hard ways to get it manually done and yet there is no permanent and effective solution being offered here :slight_frown:
Mark de Scande
- Syntax Hero
- 606 pts: Hero points 1,049 pts: Rep. points LEVEL 8
@Barry Make @freedom day and work on a script to do this please :slight_smile:
Aaron
- Ex Staff
- 11,047 pts: Hero points 20,397 pts: Rep. points LEVEL 30
Just to let you know we have an update on this coming very shortly…
S H Mohanjith
- Recruit
- 279 pts: Hero points 1,240 pts: Rep. points LEVEL 8
Please replace your db.php from the attached archive. Then upload fix-db-encoding.php to wp-content/scripts/.

***Please back up your DBs and double check that everything is is there***

Add the following line to wp-config.php

define('WP_ALLOW_REPAIR', true);

Go to http://yoursite/wp-content/scripts/fix-db-encoding.php and follow the instructions on screen. Please let us know if it resolves the issue then we can release it. Thanks :slight_smile:
Kwee Chek
- The Incredible Code Injector
- 60 pts: Hero points 483 pts: Rep. points LEVEL 5
should I leave the two define statements in wp-config as below before running your scripts?

/** Database Charset to use in creating database tables. */

define(‘DB_CHARSET’, ‘utf8’:wink:;

/** The Database Collate type. Don’t change this if in doubt. */

define(‘DB_COLLATE’, ”:wink:;

Thanks.
S H Mohanjith
- Recruit
- 279 pts: Hero points 1,240 pts: Rep. points LEVEL 8
@freedom yes, don’t change those.
Kwee Chek
- The Incredible Code Injector
- 60 pts: Hero points 483 pts: Rep. points LEVEL 5
@S H Mohanjith > Executed but still have some funny words as shown below, but it does improves the no. of characters. What should I do next? Thanks.

An extract from what Lionel had to say regarding his achievement:

” It was really exiciting to represent Singapore in a World Canoe event and it was really special and meaningful to me. The journey to the World Championships was a rough one, having to juggle with two different types of training, short sprints and long distances. With training up to 3 times a day, 6 days a week, it wasnâ€™t an easy one. Upon completion of the race, I felt honoured to bring glory to Singapore and to Singapore Polytechnic knowing that despite my age and paddling experience disadvantage, I had managed to clinch the 3rd in Asia for the opens category. I learnt one thing from the World Championships, â€œItâ€™s not about what you are capable of, but how much you want it.â€

Let’s congratulate Lionel for his impressive feat!
S H Mohanjith
- Recruit
- 279 pts: Hero points 1,240 pts: Rep. points LEVEL 8
Would it be possible for you to send the wp_x_posts table schema and data (where x is the blog_id) to me via the contact form? Being able to play with the table helps test the fix. Thanks.
Kwee Chek
- The Incredible Code Injector
- 60 pts: Hero points 483 pts: Rep. points LEVEL 5
How to do that?
S H Mohanjith
- Recruit
- 279 pts: Hero points 1,240 pts: Rep. points LEVEL 8
Please send a mail through the contact form first. Then I’ll reply and you could attach to your reply. Even the whole DB dump would do for me if it’s inconvenient to dump just that table. Thanks.
Kwee Chek
- The Incredible Code Injector
- 60 pts: Hero points 483 pts: Rep. points LEVEL 5
Emailed.
S H Mohanjith
- Recruit
- 279 pts: Hero points 1,240 pts: Rep. points LEVEL 8
Here is a new and improved fix.
Kwee Chek
- The Incredible Code Injector
- 60 pts: Hero points 483 pts: Rep. points LEVEL 5
Thanks to S H Mohanjith for the hard work as we have been communicate via email to solve my existing charset mismatch issue. And I would like to conclude that it have been resolved.
S H Mohanjith
- Recruit
- 279 pts: Hero points 1,240 pts: Rep. points LEVEL 8
Yay, guess it’s good enough to be released :slight_smile:
Mark de Scande
- Syntax Hero
- 606 pts: Hero points 1,049 pts: Rep. points LEVEL 8
@S H Mohanjith I have also run this https://vvpmudev.com/forums/topic/multi-db-v31#post-152293

and it is working 100% :slight_smile: on bloglines.co.za
nickd32
- The Incredible Code Injector
- 732 pts: Hero points 348 pts: Rep. points LEVEL 5
A few comments…

1. Shouldn’t the fix-db-encoding.php file be placed in /wp-admin/ instead of /wp-content/scripts/ (for security reasons) ?

Anyone can access /wp-content/scripts/ on a website. Seems like a powerful script like this needs to be accessible only to a Network Admin once they’re logged in.

2. Should we turn off this line in wp-config.php once we’re finished?

define('WP_ALLOW_REPAIR', true);

3. How often do you recommend doing a “repair and optimize” on a Multi-DB setup? Monthly?
Aaron
- Ex Staff
- 11,047 pts: Hero points 20,397 pts: Rep. points LEVEL 30
1. Doesn’t matter where, it’s still publically accessible. It’s mean for only temporary use, then delete.

2. Yes, definitely!

3. Only when there are problems, rarely.
nickd32
- The Incredible Code Injector
- 732 pts: Hero points 348 pts: Rep. points LEVEL 5
@S H Mohanjith – I just sent a message through the WPMU DEV contact form

I’m having issues getting the fix-db-encoding.php script to work. I recorded a little video to show you.

http://screencast.com/t/0eJVNTzwPOqx

Could this be a security setting on my server? Any thoughts?
nickd32
- The Incredible Code Injector
- 732 pts: Hero points 348 pts: Rep. points LEVEL 5
OK, I think we’ve fixed it by increasing the timeout in the server settings. Seems to be working now.

How do you rate me?

Thank you for rating your experience!