Updated: Multiple Databases

Version 0.6.0 of the Multiple DB package is now available. This new version includes the following changes:

1) wp-db.php synced to WordPress MU 1.3 wp-db.php

2) New script to help move blogs to the new databases

3) Updated documentation and example files

This release was basically a compatibility release. There will be another release soon that will include a few new features.

Please let us know if there are any problems and we’ll get them fixed ASAP.

Thanks,

Andrew

Mon, 12 Nov 2007 3:38:26

Andrew
- Champion of Loops
- 2,117 pts: Hero points 5,319 pts: Rep. points LEVEL 23
By the way, i’m not 100% positive this new release will work on 1.2.5a and previous. If someone has the time to test this i’d really appreciate it.

Thanks,

Andrew
Luke
- The Crimson Coder
- 3 pts: Hero points 638 pts: Rep. points LEVEL 6
Send in The Wolf.
drmike
- DEV MAN’s Mascot
- 466 pts: Hero points 6,133 pts: Rep. points LEVEL 24
Must be another one of those Kevin Smith references you’re so fond of. :wink:
andrea_r
- The Incredible Code Injector
- 3 pts: Hero points 463 pts: Rep. points LEVEL 5
My main install is about that old. I’ll be rolling multi-dbs in on US Thanksgiving.

Unless you need it sooner, in which case I can do it on the local install.
drmike
- DEV MAN’s Mascot
- 466 pts: Hero points 6,133 pts: Rep. points LEVEL 24
A script actually is fairly easy to code up. (As long as the username and password is the same) My issue with doing it was the auto_increment though and passing that variable over.

Does the actual number need to be passed over or can you just tell mysql to use auto_increment?
Andrew
- Champion of Loops
- 2,117 pts: Hero points 5,319 pts: Rep. points LEVEL 23
Just tell MySQL to use auto_increment and you’re good to go.
Nah, i’ve got plenty of test sites i’m just busy at the moment. I’ll do a bit of testing later this week.

Btw, is there a feature that anyone would really like to see added? Luke’s idea about multiple VIP databases is already on my list.

Thanks,

Andrew
Luke
- The Crimson Coder
- 3 pts: Hero points 638 pts: Rep. points LEVEL 6
If you want complete honesty, I’d like to see a shortened configuration. At least as an option.

I mean even with 256 db’s, that’s still more configuration than necessary, isn’t it?

Is someone really going to have 256 users and passwords?

Maybe they do, maybe not.

Here’s my idea, but remember as an option.

Have 16 username/password combos; 0-9, and a-f.

Then, when the a5 db is called it grabs the “a” username/password.

Thinking about it, even if you break it up onto multiple db servers, with 16 different db servers running in 4096 mode, that’s still 256 per server, which is 0-9 and a-f.

The only possible issue to this, would be the db_name. But, let’s say all my db’s are website_udb-0 through website_udb-f. (udb being short for user database.) The db_name in the settings could be “website_udb-“, and the script then tacks on the hash.

Unless you want to get creative with db names or whatever. But, since it would only be an option it wouldn’t kill that either.

I guess the other possibility would be to break up the configuration and have it load a configuration when it connects. Have all your VIP/Global stuff in the main config file, but then user db’s broken up into multiple files, included on the fly.

Keeping them in the directory right above the site root would keep them from being publicly accessible, plus the script can still grab it. Something like /path/db-config/a/a0.php would work. Since you’re not going user to user on tables, it would be OK.

Just a couple of thoughts I’ve been kicking around to squeeze every drop of performance possible out of it and MU.
Luke
- The Crimson Coder
- 3 pts: Hero points 638 pts: Rep. points LEVEL 6
The Doc said: “Must be another one of those Kevin Smith references you’re so fond of. :wink:”

Silly Doc, that one is right out of Pulp Fiction.
drmike
- DEV MAN’s Mascot
- 466 pts: Hero points 6,133 pts: Rep. points LEVEL 24
Never saw Pulp Fiction. Not much of a movie person anymore. Too big of a block of time required really plus no real spendable cash.

I don’t even remember the last movie I watched. I listened to the Simpson’s movie a few weeks ago here in the library while coding. That and the last Harry Potter.

+1 for the smaller file but it’s not that bad of an issue, at least not at 256. Even with your older, original code.

Couldn’t you do the username/password combos with the current code? You would just have to manually hardcode the file.
Luke
- The Crimson Coder
- 3 pts: Hero points 638 pts: Rep. points LEVEL 6
Actually, I already have something similar set up, with 16 user/pass/host options for a 256 install. I was just throwing it out if someone else found the concept useful.

Of course 256 doesn’t include your global, home, vip, and custom db’s either. Still, why do the work in ~1100-1200 lines when you can do it 100 or less? For me, it’s worked out well.

If at some point I need to move out to 4096 down the road, I’ll definitely segment the user database stuff into multiple files. Probably split the hash up, and have 16 directories with 256 files each or something. I dunno, I’m far from that yet.

I guess the whole way I see it is this:

If it’s one file that’s 2k lines, no biggie. But it’s when you go, “Oh, it’s only 2k lines” multiple times over multiple times (plugins, mu-plugins, includes, etc) where it adds up. Especially with mu-plugins, which is why any time I do an mu-plugin it’s set to only include the file(s) it needs, when its needed. If the condition for inclusion isn’t met, that’s a 4 or 5 line check vice loading the whole thing. You might be surprised, but it adds up quickly.

I’m revamping a client site at the moment, and their site was horrid. When I started, the main page was running 979 db queries on average with load times in the 10-15 second range. Tons of stuff in mu-plugins, and other stuff as well. Granted, the person they had originally working with them wasn’t quite tuned in to MU and its global scale.
Luke
- The Crimson Coder
- 3 pts: Hero points 638 pts: Rep. points LEVEL 6
Testing Report: move_blogs.php

Strange: On a 256 scale, it tried to connect to db_name_6, vice 06. For the time being, I just added a hard coded $newdbsize after the if/else sequence which worked.

Noticed: It doesn’t move global tables, which may/may not be an issue for some if they aren’t going to use a new db name for their global stuff.

Link error: To start the copy process click here: links to moveblogs.php, instead of move_blogs.php

Link error: Once completed, refresh this page by clicking here: also links to moveblogs.php as above.
Luke
- The Crimson Coder
- 3 pts: Hero points 638 pts: Rep. points LEVEL 6
Note:

To the “strange” report above:
```
if ($db_scaling = '16'){



$newdbsize = '1';



} else if ($db_scaling = '256'){



$newdbsize = '2';



} else if ($db_scaling = '4096'){



$newdbsize = '3';



}
```
Change to:
```
if ($db_scaling == '16'){



$newdbsize = '1';



} else if ($db_scaling == '256'){



$newdbsize = '2';



} else if ($db_scaling == '4096'){



$newdbsize = '3';



}
```
It was using a single =, vice a double so it was trying to hit it like it was 16 vice 256.

Additional:
```
mysql_free_result($sql_table);

mysql_free_result($insert_info);
```
At the bottom, should be after the queries run vice at the end of the file to be of any use.

— — —

As a positive note: it did move (as a copy) all the tables to the right spots. Including the global tables (which I hacked in) as well as my own specification for blog_id 1 to its own db (hacked in).

To me, however, if the tables are only being copied it would make sense to support moving the global tables to their own global db. Which if that becomes the case, it would be nice for the linked tools (http://calc.idtstudios.com/db_sql.php) to also support spitting out global table info as well.
Andrew
- Champion of Loops
- 2,117 pts: Hero points 5,319 pts: Rep. points LEVEL 23
I’ll fix all of these tonight and put out a new release.

Thanks,

Andrew
Andrew
- Champion of Loops
- 2,117 pts: Hero points 5,319 pts: Rep. points LEVEL 23
Thanks for catching that. Me and my typos…
Luke
- The Crimson Coder
- 3 pts: Hero points 638 pts: Rep. points LEVEL 6
I feel ya. I do it on occasion too, and then kick myself in the ass later on. Such is code though.

I will note though, that once it runs, it runs like a champ.

Put it this way, locally (of course) it took almost 10 minutes for PhpMyAdmin to “rename” the table. Had to increase the execution time up to like 15 minutes just for this particular project so I could work with its database. (Command line, I know, but I’m lazy like that. lol)

This script took less than 2 minutes.

~1800 users / 18k tables

And yeah, I’m not complaining. It did save a days worth of work. :wink:
Luke
- The Crimson Coder
- 3 pts: Hero points 638 pts: Rep. points LEVEL 6
Oh, further note, this “site” is a hybrid where some crap has been updated and some not starting with 1.2.1, and merging certain things up to one of the 1.3 RC’s.

I’ll try it out on the local copy of the Lounge, as it’s running 1.2.5(a) still, and I have a 1.2.3 running around here somewhere as well.
Andrew
- Champion of Loops
- 2,117 pts: Hero points 5,319 pts: Rep. points LEVEL 23
The only bit i’m not sure about is using the script to transfer global tables. I could easily modify it to move the default wpmu tables but i’m not sure how to make it recognize custom global tables. Do you think it would be ok if I just added an array to the “config” section that users could fill out with their custom tables?

Thanks,

Andrew
Luke
- The Crimson Coder
- 3 pts: Hero points 638 pts: Rep. points LEVEL 6
Looking at the script now, it’s checking the table name for the blog_id with is_numeric.

If that condition is not met, then it must be a global table right? :wink:

If someone goes beyond that, and has a few different custom db’s with custom tables and such, moving those could be done by hand. If you want, I can email the file I’ve modified and you can pick and choose from that.

// Next topic:

Subdirectory install, pulls up the global tables and the home blog tables fine, but is crapping out on individual users and returning a 404. Replacing with the original config and wpdb resolves the issue.

Not completely sure about what’s going on with it, but thought I would make note of it.

Username/pass is good (it’s local so they’re all the same), as well as the table name and such.

I’ll have to look deeper into it before I say it’s a bug, but thought I’d mention it in case someone else has had the same issue.
Luke
- The Crimson Coder
- 3 pts: Hero points 638 pts: Rep. points LEVEL 6
Scratch the “next” thing above. It’s something in the config file, but I’m still looking at what specifically.
Andrew
- Champion of Loops
- 2,117 pts: Hero points 5,319 pts: Rep. points LEVEL 23
Feel free to send me your modified file. I’m going to have a busy evening so I appreciate any help I can get.

Thanks,

Andrew
Luke
- The Crimson Coder
- 3 pts: Hero points 638 pts: Rep. points LEVEL 6
File’s in the mail.

// Mind my custom hackery. :wink:
minusme
- WPMU DEV Initiate
- 3 pts: Hero points 17 pts: Rep. points LEVEL 1
Looking at the script now, it’s checking the table name for the blog_id with is_numeric.

If that condition is not met, then it must be a global table right? :wink:

Exactly. It would talk all of 5 minutes to reverse the script (including code beauty and witty comments) to only copy non-blog tables. As long as you don’t have any non-blog tables that start with wp_ and then a #, it would be easy. I guess I figured why move them if they are working where they are.
drmike
- DEV MAN’s Mascot
- 466 pts: Hero points 6,133 pts: Rep. points LEVEL 24
Something else that comes to mind since we’re doing moving of databases. Seems like there was a rash of charsets getting mucked up in the forums recently. (I just kept out of those.) I know there’s ways to convert different charsets into utf8 which is what mu is supposed to run under and it appears that many folks weren’t doing it as such. Care to add in a conversion script?

edit: Reference: http://textsnippets.com/posts/show/84
Luke
- The Crimson Coder
- 3 pts: Hero points 638 pts: Rep. points LEVEL 6
Pretty much minusme. I emailed Andrew a copy of what I’d changed (plus the other fixes above).

Thanks for saving me a couple days of mundane and boring work though. :smiley:

The script was pretty quick on my “loaded test server”, and once I fixed up the bugs and actually ran it, it was flawless.

All tables copied to the right spot, as it should be.

@Doc, that would be handy. 99% of the time is seems that “most” db’s start out (in terms of MU) as latin_sweedish_1, with fields as latin_sweedish_ci (or whatever it is).

When I finally upgrade to 1.3, I’ll probably export the data, change the fields to the correct format, and import.

Then again, I don’t have any internationalization going on, so it makes it a lot easier to play with.
minusme
- WPMU DEV Initiate
- 3 pts: Hero points 17 pts: Rep. points LEVEL 1
The script was pretty quick on my “loaded test server”, and once I fixed up the bugs and actually ran it, it was flawless.

Not to be too obnoxious, but the errors came from changes that were made to the script I originally sent. Not that I never make errors, but I didn’t make ‘those’ errors, LOL :wink:

If you’ve already made the changes, perhaps Andrew will incorporate them into my script. Andrew let me know if you need any more tweaks, and I’ll be happy to update.

Care to add in a conversion script?

I really haven’t needed this myself, but I’ll take a look. If it’s relatively easy, I’ll look at adding it in.

Cheers….
Luke
- The Crimson Coder
- 3 pts: Hero points 638 pts: Rep. points LEVEL 6
Yeah, I know. I sent a fixed version over, so it should be updated in the near future. Mostly were just minor typo’s, but I also had the check for non-blog tables in there. (Really simple, actually, if it doesn’t meet the first criteria, it must be global, so I just added an else to the sequence. With a global table variable at the top in the settings as well.)

I would say that having a script detect and then change to utf8 would be handy for some folks. Heck, it might be beneficial even if not using other languages.
Andrew
- Champion of Loops
- 2,117 pts: Hero points 5,319 pts: Rep. points LEVEL 23
Hi guys,

The changes were indeed made by me. I made a note at the top of the file about it :wink:

Thanks,

Andrew