I’ve got a new project going. For this project I wanted to be able to have a few different sections of the site act like different sites, and I wanted to be able to share administration with many people of varying admining abilities. From what I’d read about WordPress Multisite, I figured it was going to be the easiest way to do it.

I wasn’t entirely wrong, but it sure wasn’t as easy as I’d expected it to be. Mostly for two small reasons.

The setup

I decided I wanted to try some local development for this project, on my laptop. A google search revealed: the easiest way I found was using a piece of software called MAMP. It installs PHPmyAdmin, MySQL, and Apache, and has an easy interface to help you set everything up.

I followed these directions in order to install WordPress on my laptop, and started on these in order to get MultiSite set up. I got to step five before I encountered my first error.

The error

The second part of Step 5 is to add the lines that the network installation gives you to the wp-config.php. I did that, added the .htaccess lines. I then went back to my install to log in, and was faced with the giant error:

After a lot of troubleshooting and attempting to duplicate the error on my VPS, I figured out that WordPress Multisite is completely incompatible with InnoDB, and my MAMP install was set InnoDB by default.

The fix

So, to make this install work with MutliSite, I did this:

All in phpMyAdmin (which is installed by default with MAMP):
* I exported the database to an .sql file on my laptop.
* I dropped all of the tables in the database that I’d created for the WordPress install.

In your favorite text editor (or sed if you’re feeling adventurous)
* I opened the .sql file and changed all of the engine definitions for each table.
The line will look like this: ENGINE=InnoDB; Change it so they all say: ENGINE=MyISAM

Then, back in phpMyAdmin:
* Import the .sql file into your database.

That should fix the “Error Establishing Database Connection” error on your MultiSite install.

Fixing the default engine

The last thing I did, to prevent having to deal with this again, was change the default engine type in my local MySQL install using a combination of these and these instructions.
Summarized here:

Remember: This is assuming you’re running MAMP, on your Mac. Not a linux server.

First, enter MySQL

/Applications/MAMP/Library/bin/mysql --host=localhost -uroot -proot


Then, to see what your current default it set to the information_schema db.
USE information_schema;
SELECT * FROM information_schema.engines;

You’ll see something that looks like this:

mysql> SELECT * FROM information_schema.engines;
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
| ENGINE | SUPPORT | COMMENT | TRANSACTIONS | XA | SAVEPOINTS |
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
| FEDERATED | NO | Federated MySQL storage engine | NULL | NULL | NULL |
| MRG_MYISAM | YES | Collection of identical MyISAM tables | NO | NO | NO |
| MyISAM | YES | MyISAM storage engine | NO | NO | NO |
| BLACKHOLE | YES | /dev/null storage engine (anything you write to it disappears) | NO | NO | NO |
| CSV | YES | CSV storage engine | NO | NO | NO |
| MEMORY | YES | Hash based, stored in memory, useful for temporary tables | NO | NO | NO |
| ARCHIVE | YES | Archive storage engine | NO | NO | NO |
| InnoDB | DEFAULT | Supports transactions, row-level locking, and foreign keys | YES | YES | YES |
| PERFORMANCE_SCHEMA | YES | Performance Schema | NO | NO | NO |
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
9 rows in set (0.00 sec)

To change it, use the following:
SET GLOBAL storage_engine = MyISAM;

You won’t be able to see the changes reflected until you ‘exit’ and log back in (and I have no idea why that is) but as far as I can tell that’s just a technicality.

mysql> EXIT
Bye
Elita:~ root# /Applications/MAMP/Library/bin/mysql --host=localhost -uroot -proot
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 204
Server version: 5.5.9 Source distribution
Copyright (c) 2000, 2010, Oracle and/or its affiliates. All rights reserved
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql> SELECT * FROM information_schema.engines;
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
| ENGINE | SUPPORT | COMMENT | TRANSACTIONS | XA | SAVEPOINTS |
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
| FEDERATED | NO | Federated MySQL storage engine | NULL | NULL | NULL |
| MRG_MYISAM | YES | Collection of identical MyISAM tables | NO | NO | NO |
| MyISAM | DEFAULT | MyISAM storage engine | NO | NO | NO |
| BLACKHOLE | YES | /dev/null storage engine (anything you write to it disappears) | NO | NO | NO |
| CSV | YES | CSV storage engine | NO | NO | NO |
| MEMORY | YES | Hash based, stored in memory, useful for temporary tables | NO | NO | NO |
| ARCHIVE | YES | Archive storage engine | NO | NO | NO |
| InnoDB | YES | Supports transactions, row-level locking, and foreign keys | YES | YES | YES |
| PERFORMANCE_SCHEMA | YES | Performance Schema | NO | NO | NO |
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
9 rows in set (0.00 sec)

Hope this helps someone else avoid this level of frustration!