• 
  Please support me in the 2010 Ride to Conquer Cancer - last year we were able to raise $6.9 million for the Alberta Cancer Foundation - hopefully we can raise even more in 2010!

  ---

• Search for:

• Pages

  • About
    • Colophon
    • Contact
    • Curriculum Vitae
    • Presentations
  • Archives
    • Recent Comments
    • Shared Items
  • Asides
  • Photography
  • WordPress
    • Akismet Credit Inserter
    • Post Revision Display

• Categories

  • aside
  • digital photography sessions
  • fun
  • general
  • photography
  • work

• Blog under the Creative Commons Attribution 3.0 License
  

Sometime after upgrading our copy of MediaWiki from the antique version I’d had to run, to the shiny latest version, I noticed (well, some of the wiki users noticed first…) that there were some borked characters – accented French characters, Mandarin characters, and fancy schmancy “smart quotes” were displaying as gobbledygook gibberish text. Smelled like a UTF8-related issue – IIRC, MediaWiki switched the front end of the web app to be UTF8, but my database was languishing behind in krufty latin1 encoding. oops.

So, how to convert a database that’s approaching a gigabyte of data? I started googling. Found some hints. But none of the solutions I found actually worked for me. So I googled some more, and duct-tape-and-bubblegummed a script together that seems to have successfully converted the database from latin1 to UTF8.

Here’s what I did, in case I need to do it again…

#!/bin/bash

# general config stuff
mysql_path="/usr/local/mysql/bin"

# source database config
# change this to point to the database server that is currently hosting the database.
source_host="localhost"
source_db="source_database_name" # mine was mediawiki
source_user="source_database_username"
source_pw="password" #change this. duh.
temp_sql_dir="/path/to/a/directory"

# destination database config
# change this to point to the database server that will host the converted database
# this could be the same server as above, but use a different database on it.
dest_host="localhost"
dest_db="dest_database_name" # mine was mediawiki-utf8
dest_user="dest_database_username"
dest_pw="password"

# magic happens
clear
for table in `$mysql_path/mysql --host=$source_host --user=$source_user --password=$source_pw $source_db -e 'show tables' | egrep -v 'Tables_in_' `; do
	echo "Dumping $table"

	# dump the table from the first database
	$mysql_path/mysqldump --host=$source_host --user=$source_user --password=$source_pw --extended-insert=false --quote-names --default-character-set=latin1 $source_db $table > $temp_sql_dir/$table.sql

	# convert the charset declarations from latin1 to utf8
	# sed seems to bork mysteriously, and the mysql 'replace' command borks the files, so I settled on perl...
	# you could add other transformations here, too...
	perl -pi -w -e 's/latin1/utf8/g;' $temp_sql_dir/$table.sql

	# import the converted table data into a fresh database table
	$mysql_path/mysql --host=$dest_host --user=$dest_user --password=$dest_pw --default-character-set=utf8 $dest_db < $temp_sql_dir/$table.sql
done

# or not.
if [ "$table" = "" ]; then
	echo "No tables found in db: $db"
fi

It seems to have worked for me. It took maybe 5 minutes to convert almost a gig of data. It borked on one table – categorylinks failed because of some problem with the key, so I just manually copied it over myself after the script was finished.

It is highly probable that there is a better way to do this. Perhaps even some magic bit that could have been twiddled to do this automagically and/or instantly on the server. I couldn’t find the proper lever to throw, so wound up trying brute force. When in doubt, try brute force. It worked for me. It might not work for you. You’ve been warned.

I’ve been trying to get BuddyPress working on my WPMU installation that uses MultiDB for database partitioning. It’s been cranky, but I just realized I’m a complete idiot because I was overlooking the obvious (and drop dead simple) fix.

BuddyPress was acting up because it was creating tables in each blog’s database tableset. But MultiDB makes it easy to declare tables as belonging to a shared global database, so they don’t get recreated for each blog and are common across the entire service.

Thanks to a reminder by Andrew on the premium.wpmudev.org forum!

I edited my db-config.php file to declare the BuddyPress tables as being global, and copied the tables from the database where they had been collecting, into the global database.

// BuddyPress
add_global_table('bp_activity_sitewide');
add_global_table('bp_activity_user_activity');
add_global_table('bp_activity_user_activity_cached');
add_global_table('bp_friends');
add_global_table('bp_groups');
add_global_table('bp_groups_groupmeta');
add_global_table('bp_groups_members');
add_global_table('bp_groups_wire');
add_global_table('bp_messages_messages');
add_global_table('bp_messages_notices');
add_global_table('bp_messages_recipients');
add_global_table('bp_messages_threads');
add_global_table('bp_notifications');
add_global_table('bp_user_blogs');
add_global_table('bp_user_blogs_blogmeta');
add_global_table('bp_user_blogs_comments');
add_global_table('bp_user_blogs_posts');
add_global_table('bp_xprofile_data');
add_global_table('bp_xprofile_fields');
add_global_table('bp_xprofile_groups');
add_global_table('bp_xprofile_wire');

It seems to be working fine. I’ll do some more testing, but it’s looking promising. If it’s really working, I’ll be spending some time to BuddyPress-enable the main theme for the WPMU service, and roll it out properly.

I was asked to give a presentation for the From Courses to Dis/Course online conference last week, and chose the topic of identity as it relates to openness. My session, Identity in the Open Classroom, was a fun (for me, anyway) exploration of the issues, and I think served the purpose of framing discussion.

Here’s the video of the recording from the session:

the full chat transcript for the session is available, as well as the full Elluminate session recording.

and the slide that wound up framing much of the question and answer portion:

I’ve been running a copy of WordPress MultiUser for over a year now. Comment spam hasn’t been much of a problem, thanks to Akismet, but if I leave site registration open (so students and faculty can create new accounts and blogs), the evil spammers find it and start sending their bots en masse.

I tried a few plugins, with varying levels of success. There’s an interesting one that attempts to limit registrations to a specific country, but it falsely reported some valid users as not being in Canada. Captchas work, but also block some valid users (and the signup captcha plugin I’d been using is possibly abandoned).

So, I did some quick googling, and came across the page on the WordPress Codex about using .htaccess files to stop comment spam. I made some modifications to the technique, and am now running it on UCalgaryBlogs.ca with apparent success. The apache logs show the bot attacks are still in full force, but not a single one has gotten through in order to register. And valid users have been able to get through. That’s pretty promising.

Read more

The group of WPMU rockstars at UBC’s OLT just whipped up a fantastic new plugin for administrators of a WPMU site to get a feel for the growth of the community. It generates a graph to display growth in numbers of blog posts and comments over time, and uses the Google Data Visualization API to let you interactively define data ranges to be graphed.

Here’s the growth of UCalgaryBlogs.ca graphed for the last 2 semesters:

Another fantastic job by the OLT blogging platform crew. Now, to just add users and pages, and it’ll be perfect…

I’m working with a class of 250+ geology undergrads, split up into 53 groups. They’re using a WordPress site to publish online presentations as the product of a semester-long group project. I’m using the great WP-Sentry plugin to let them collaboratively author the pages without worrying about other students in the class being able to edit their work (I know – but it makes them more comfortable so it’s a good thing to add).

The premise is this – I created a Page called, creatively enough, “Winter 2009″ – and each of the groups is to create a page (or set of pages) and add them to the site – and selecting “Winter 2009″ as the parent page for the main page of their presentation. They are free to create as many other pages as they like, and can set those to use their first page as the parent, thereby generating a table of contents.

Works great. Except that the WP-Sentry plugin hijacks the “Private” state of pages, and the tree of Pages available in the Parent selector is based on “Published” pages.

Conflict. Confusion. Frustration.

The students could either collaborate on the pages, or organize them in the tree structure.

Of course they could create the pages and add them to the tree structure and THEN enable the WP-Sentry-managed group editing controls, but YOU try explaining that process to 250 undergrads, all stressed out about building web pages as part of a geology course.

So… I dug into the code to see what was yanking “Private” pages from the Parent list. Turns out, it’s in wp-includes/post.php, waaaay down on line 2618 (as of WPMU 2.7). All I did was remove the " AND post_status = 'publish'" bit, and it now appears to be listing all pages.

I’m quite sure I borked something else, but for now I’m leaving the Parent list wide open until the students are done publishing their presentations.

Update: Unintended consequence #242: Looks like with the tweak, Private pages show up where they’re not expected. I’m disabling the tweak for now until I can find a better way (if that’s even possible).

I tried the promising new iPhone / iPod Touch app “Blackboard Learning” hoping to have a cool and efficient way to connect to Blackboard from my pocket computer. No such luck. There’s always something in the way of making the LMS experience fun…

Error

Your institution is blocking Sync for the iPhone.

Doh.

I don’t know if this is a version mismatch – are we running the wrong version of Blackboard? – or if it’s just a new building block that needs to get rolled out on campus. Either way, frustration.

I’ve been running a copy of the excellent Feed2JS RSS feed embedder script on one of our servers for a few years(!) now. It’s a great way to embed any RSS feed onto any web page. The problem is that it’s a little too attractive to some of the more leecherly and unsavoury members of teh intarwebs. I occasionally take a peek at who’s using the script, and have found SEO tweakers, gambling sites, porn sites, warez, etc… all using it to aggregate their stuff together. That’s fine, but download your own copy rather than stuffing my server’s logs and cache directories with your crap.

So I just added a .htaccess file to the feed2js directory so that the php scripts are only visible if referred by a web page with “ucalgary” in the URL.

Basically, that says:

By default, block everyone. But, if the referrer for the request for any file in this directory contains “ucalgary” anywhere in the URL, case insensitively matched, then go ahead and let them in (actually, it says, if the url doesn’t contain ‘ucalgary’ – case insensitively matched – then fail).

It’s not bulletproof – they can still add “ucalgary” anywhere in the URL – could be the page filename, etc… but I figure if they’re willing to rename their crapware sites to “ucalgary” just to use the script, that’s just good marketing for us. Also, it’ll fail for valid https:// requests, but that’s easily fixed.

I had previously locked down access to the script only to browsers with UCalgary IP addresses – but then the scripts don’t work on valid sites if accessed off campus. Oops. But it worked This referrer blocking method should provide some flexibility.

To build a feed2js embed code, you’ll have to use this page to get started, but it’ll fail if you paste the code on a non-UCalgary server.

I followed Jim’s instructions to get UCalgaryBlogs.ca converted from using a single database (as is the default) to using multiple databases (17 separate databases now) via the premium.wpmudev.org Multi-DB code to prevent growing pains. The single database config is good for getting up and running, but with 300 blogs in the system, table explosion was causing grief on the shared MySQL database server – there were almost 3000 tables, which was making the automated backup script complain a bit.

While reading the documentation, I was rather confused by the term “global” – which appeared to be used in slightly different ways. Eventually, I plugged through, and got it working. The key is to test it all on a local copy of the database before running the migration script on the production server. Thankfully, the script doesn’t delete anything, so I was confident that if anything borked I could just back out the multi-db files and revert to single database config without losing anything.

“Global Tables” are tables that will be stored in a shared, common database rather than in each blog’s database in one of the 16 databases used by the multi-db code. These are things that are accessed by all blogs on the WPMU install, and include administrative stuff.

In the db-config-sample-16.php file that ships with multi-db, it also mentions “global-db”, “globaluser”, and “globalpassword” – those are just the database server address, username, and password to use when connecting to the “Global” database containing the “global tables”. They used “global-” in these parameters because it’s possible to configure each of the 17 databases to use different database servers, different usernames, and different passwords. For simplicity, I used the same database server and account for all 17 databases.

My db-config.php file was edited as follows:

<?php
//	Plugin Name: Multi-DB
//	Plugin URI: http://premium.wpmudev.org/project/Multiple-Databases
//	Author: Andrew Billits (Incsub)
//  Version: 2.7.0
//------------------------------------------------------------------------//
//---DB Scaling-----------------------------------------------------------//
//------------------------------------------------------------------------//
//	16,256,4096
define ('DB_SCALING', '16'); // use 16 databases for the blogs
//------------------------------------------------------------------------//
//---DC IPs---------------------------------------------------------------//
//------------------------------------------------------------------------//
//	Usage: add_dc_ip(IP, DC)
//	EX: add_dc_ip('123.123.123.', 'dc1');
add_dc_ip('127.0.0.1', 'dc1'); // DN: change this to the IP address of your WEB SERVER
//------------------------------------------------------------------------//
//---Global Tables--------------------------------------------------------//
//------------------------------------------------------------------------//
//	Do not include default global tables
//	Leave off base prefix (eg: wp_)
//
//	Usage: add_global_table(TABLE_NAME)
//	EX: add_global_table('something');
// DN: These are tables that will be stored in the global database configured below (wpmu_global)
//     rather than in the 16 blog databases.
add_global_table('mass_mailer');
add_global_table('registration_log');
add_global_table('reports_comment_activity');
add_global_table('reports_post_activity');
add_global_table('reports_user_activity');
add_global_table('signups');
add_global_table('support_faq');
add_global_table('support_faq_cats');
add_global_table('support_tickets');
add_global_table('support_tickets_cats');
add_global_table('support_tickets_messages');
add_global_table('domain_mapping');
add_global_table('comment_activity');
add_global_table('blog_activity');
add_global_table('user_activity');
add_global_table('post_activity');

//------------------------------------------------------------------------//
//---DB Servers-----------------------------------------------------------//
//------------------------------------------------------------------------//
//	Database servers grouped by dataset.
//	R can be 0 (no reads) or a positive integer indicating the order
//	in which to attempt communication (all locals, then all remotes)
//
//	Usage: add_db_server(DS, DC, READ, WRITE, HOST, LAN_HOST, NAME, USER, PASS)
//	EX: add_db_server('global', 'dc1', 1, 1,'global.mysql.example.com:3509','global.mysql.example.lan:3509', 'global-db', 'globaluser',  'globalpassword');
// DN: NOTE: change 'dbserver.com' to the address of the mysql server,
//   'username' to your mysql username,
//   'password' to the appropriate password.

add_db_server('global', 'dc1', 1, 1, 'dbserver.com', 'dbserver.com', 'wpmu_global', 'username', 'password');
add_db_server('0', 'dc1', 1, 1, 'dbserver.com', 'dbserver.com', 'wpmu_0', 'username', 'password');
add_db_server('1', 'dc1', 1, 1, 'dbserver.com', 'dbserver.com', 'wpmu_1', 'username', 'password');
add_db_server('2', 'dc1', 1, 1, 'dbserver.com', 'dbserver.com', 'wpmu_2', 'username', 'password');
add_db_server('3', 'dc1', 1, 1, 'dbserver.com', 'dbserver.com', 'wpmu_3', 'username', 'password');
add_db_server('4', 'dc1', 1, 1, 'dbserver.com', 'dbserver.com', 'wpmu_4', 'username', 'password');
add_db_server('5', 'dc1', 1, 1, 'dbserver.com', 'dbserver.com', 'wpmu_5', 'username', 'password');
add_db_server('6', 'dc1', 1, 1, 'dbserver.com', 'dbserver.com', 'wpmu_6', 'username', 'password');
add_db_server('7', 'dc1', 1, 1, 'dbserver.com', 'dbserver.com', 'wpmu_7', 'username', 'password');
add_db_server('8', 'dc1', 1, 1, 'dbserver.com', 'dbserver.com', 'wpmu_8', 'username', 'password');
add_db_server('9', 'dc1', 1, 1, 'dbserver.com', 'dbserver.com', 'wpmu_9', 'username', 'password');
add_db_server('a', 'dc1', 1, 1, 'dbserver.com', 'dbserver.com', 'wpmu_a', 'username', 'password');
add_db_server('b', 'dc1', 1, 1, 'dbserver.com', 'dbserver.com', 'wpmu_b', 'username', 'password');
add_db_server('c', 'dc1', 1, 1, 'dbserver.com', 'dbserver.com', 'wpmu_c', 'username', 'password');
add_db_server('d', 'dc1', 1, 1, 'dbserver.com', 'dbserver.com', 'wpmu_d', 'username', 'password');
add_db_server('e', 'dc1', 1, 1, 'dbserver.com', 'dbserver.com', 'wpmu_e', 'username', 'password');
add_db_server('f', 'dc1', 1, 1, 'dbserver.com', 'dbserver.com', 'wpmu_f', 'username', 'password');

//
//	Note: you can also place this section in a file called db-list.php in wp-content
//  EX: add_db_server('global', 'dc1', 1, 1,'global.mysql.example.com:3509','global.mysql.example.lan:3509', 'global-db', 'globaluser',  'globalpassword');
//------------------------------------------------------------------------//
//---VIP Blogs------------------------------------------------------------//
//------------------------------------------------------------------------//
//	Usage: add_vip_blog(BLOG_ID, DS)
//	EX: add_vip_blog(1, 'vip1');
// DN: I didn't add any VIP blogs.
?>

To create the databases, I used the script at http://db-tools.wpmudev.org/db.php and it generated the code below, which I ran on the MySQL server to create the databases:

CREATE DATABASE `wpmu_global` DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci;
CREATE DATABASE `wpmu_0` DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci;
CREATE DATABASE `wpmu_1` DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci;
CREATE DATABASE `wpmu_2` DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci;
CREATE DATABASE `wpmu_3` DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci;
CREATE DATABASE `wpmu_4` DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci;
CREATE DATABASE `wpmu_5` DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci;
CREATE DATABASE `wpmu_6` DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci;
CREATE DATABASE `wpmu_7` DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci;
CREATE DATABASE `wpmu_8` DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci;
CREATE DATABASE `wpmu_9` DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci;
CREATE DATABASE `wpmu_a` DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci;
CREATE DATABASE `wpmu_b` DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci;
CREATE DATABASE `wpmu_c` DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci;
CREATE DATABASE `wpmu_d` DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci;
CREATE DATABASE `wpmu_e` DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci;
CREATE DATABASE `wpmu_f` DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci;

After copying the db.php and db-config.php files into place as per Jim’s instructions, it all Just Worked™. New content was being stored in the 16 blog databases, and sites were behaving as expected, but with slightly less table explosion bloat as before.

One thing that makes me a little nervous is that the multi-db code isn’t core to WordPress, and is part of the premium.wpmudev.org subscription. This means that it can break in the future – there is no obligation for WordPress to continue to work with it, and if for some reason premium.wpmudev.org decides to abandon the plugin or stop updating it, I’m locked into WordPress 2.7. Neither of these made me lose too much sleep. Worst case scenario, I can always recombine the tables from all 17 databases back into a single überdatabase, assuming we haven’t outgrown the physical limits of a single MySQL database by then.

I just tried logging into ucalgaryblogs.ca using a test user account, and was surprised to see a strange item in the admin bar at the top of the page:

I was curious, so I clicked it.

mwah? Those are site-admin items, being displayed to a non-admin user. I was actually able to click the “Admin Message” item to set that, even though the logged in user wasn’t an admin. Scary. Luckily, nobody’s noticed the extra menu yet – or if they have, they’ve behaved.

I poked around in the wordpress-admin-bar.php file to see if I could plug the hole. I have no idea if this is the right way, but I’ve added this bit:

} else {
    if ($menu[0]['title'] === null)  continue; // this is the line I added
    echo '                  <li class="wpabar-menu_';
    if ( TRUE === $menu[0]['custom'] ) echo 'admin-php_';

It’s down around line 320 or so. It’s probably not the correct or most reliable way to strip that menu from non-admin-users’ version of the admin bar, but it worked. Here’s the result:

Has anyone else seen the extra menu? Could it have just been a freak thing only on my WPMU install, or is this a wide open potential security problem in the shipping wordpress-admin-bar.php file? It was written for non-WPMU WordPress, so it’s quite possible it just doesn’t grok the different types of users in WPMU.

Next Page →