Thursday, August 25, 2011

Mailpress and Wordpress Multi-Site

The Problem
I've got a client with a multi-site installation of Wordpress, who decided they needed to email both newsletters and individual blog posts not only from the main site, but from each sub-site as well.

MailPress seemed like the best choice, so I went with it. Installing and (Network) activating the plugin went fine. Setting up the main site went fine. Activating the customized MailPress theme (which was consistent across all sites) went fine.

But when I went to add users, I just flat-out couldn't. I saw the bulk-add box, but nothing else, even after I bulk added a couple of addresses.

The Cause
For whatever reason, MailPress created it's special tables for the main site, but not for the sub sites. These tables included mailpress_users which (as you might guess) hold the subscribed user names.

A Bit of Background
Wordpress multisite takes all the main tables (wp_options, wp_users, etc) and - for the subsites - adds a number. So your first subsite gets wp_2_options, wp_2_users, etc.

MailPress keeps that going but tagging the site prefix onto it's tables. Instead of mailpress_forms, mailpress_users, etc you get wp_2_mailpress_forms, wp_2_mailpress_users, and so on.

Deep in the heart of the MailPress installation there's a file /wp-content/plugins/mailpress/mp-admin/includes/install/mailpress.php. And in that file it indicates which tables should be created (or upgraded). I couldn't figure out what file is supposed to actually launch install/mailpress.php, but it doesn't matter, the commands to create the required tables were there, so I just pulled them out as you see below.

The Solution
If you are having this problem, open your favorite MySQL query tool (it's probably phpAdmin, and you probably launch it from your hosts control panel. And let's face it - if you don't know what I'm talking about at this point, the better part of valour is to find someone who IS comfortable with MySQL and queries.

I even know this guy I'd recommend - his rates are pretty reasonable.

Use the code below, changing wp_2_ to the number of each of your sites until the table are all created.

CREATE TABLE wp_2_mailpresss_mails (
 id                bigint(20)       UNSIGNED NOT NULL AUTO_INCREMENT,
 status            enum('draft', 'unsent', 'sending', 'sent', 'archived', '') NOT NULL,
 theme             varchar(255)     NOT NULL default '',
 themedir          varchar(255)     NOT NULL default '',
 template          varchar(255)     NOT NULL default '',
 fromemail         varchar(255)     NOT NULL default '',
 fromname          varchar(255)     NOT NULL default '',
 toname            varchar(255)     NOT NULL default '',
 charset           varchar(255)     NOT NULL default '',
 parent            bigint(20)       UNSIGNED NOT NULL default 0,
 child             bigint(20)       NOT NULL default 0,
 subject           varchar(255)     NOT NULL default '',
 created           timestamp        NOT NULL default '0000-00-00 00:00:00',
 created_user_id   bigint(20)       UNSIGNED NOT NULL default 0,
 sent              timestamp        NOT NULL default '0000-00-00 00:00:00',
 sent_user_id      bigint(20)       UNSIGNED NOT NULL default 0,
 toemail           longtext         NOT NULL,
 plaintext         longtext         NOT NULL,
 html              longtext         NOT NULL,
PRIMARY KEY (id),
KEY status (status)
);


CREATE TABLE wp_2_mailpresss_mailmeta (
 meta_id           bigint(20)       NOT NULL auto_increment,
 mp_mail_id        bigint(20)       NOT NULL default '0',
 meta_key          varchar(255)     default NULL,
 meta_value        longtext,
 PRIMARY KEY (meta_id),
 KEY mp_mail_id (mp_mail_id,meta_key)
);


CREATE TABLE wp_2_mailpresss_users (
 id                bigint(20)       UNSIGNED NOT NULL AUTO_INCREMENT, 
 email             varchar(100)     NOT NULL,
 name              varchar(100)     NOT NULL,
 status            enum('waiting', 'active', 'bounced', 'unsubscribed')    NOT NULL,
 confkey           varchar(100)     NOT NULL,
 created           timestamp        NOT NULL default '0000-00-00 00:00:00',
 created_IP        varchar(100)     NOT NULL default '',
 created_agent     text             NOT NULL,
 created_user_id   bigint(20)       UNSIGNED NOT NULL default 0,
 created_country   char(2)          NOT NULL default 'ZZ',
 created_US_state  char(2)          NOT NULL default 'ZZ',
 laststatus        timestamp        NOT NULL default '0000-00-00 00:00:00',
 laststatus_IP     varchar(100)     NOT NULL default '',
 laststatus_agent  text             NOT NULL,
 laststatus_user_id bigint(20)      UNSIGNED NOT NULL default 0,
 PRIMARY KEY (id),
 KEY status (status)
);


CREATE TABLE wp_2_mailpresss_usermeta (
 meta_id           bigint(20)       NOT NULL auto_increment,
 mp_user_id        bigint(20)       NOT NULL default '0',
 meta_key          varchar(255)     default NULL,
 meta_value        longtext,
 PRIMARY KEY (meta_id),
 KEY mp_user_id (mp_user_id,meta_key)
);


CREATE TABLE wp_2_mailpresss_stats (
 sdate             date             NOT NULL,
 stype             char(1)          NOT NULL,
 slib              varchar(45)      NOT NULL,
 scount            bigint           NOT NULL,
 PRIMARY KEY(stype, sdate, slib)
);

2 comments:

Jose said...

Hi,

From what I read, I assume this worked for you. I've got a question, then:

I did this, and I also created the other two tables (_stats and _usermeta) that MailPress creates for the main site of the MultiSite installation. But I'm still receiving the same error. It looks like the error is out there somewhere in the DB, as the mail is sent (from the form, I guess), but the user is later unrecognized (for there is no value saved in the DB).

Your post has put me in the correct way, I guess, but I still need a little push to get things working. Any ideas?

Thanks for your time :)

Leon said...

Jose: Check the script above. It should create the following tables:

_mails
_mailmeta
_users
_usermeta
_stats

Make sure you are correctly changing the table prefix (it might not be wp_2_. It might be mysite_3_ or something else. It has to match the other tables for THAT multisite install).

Hope that helps.