Postfixadmin using replicated database

[LJ-CH]

Hi Everyone!

Can someone provide me with some help? I'm having a terrible time trying to get PFA working on a Galera cluster. When running setup.php its trying to create the domain_admins table but without a primary key so it fails.

CREATE TABLE IF NOT EXISTS domain_admins(usernamevarchar(255) NOT NULL default '',domainvarchar(255) NOT NULL default '',createddatetime NOT NULL default '2000-01-01 00:00:00',active tinyint(1) NOT NULL default '1', KEY username (username) ) CHARACTER SET latin1 COLLATE latin1_general_ci COMMENT='Postfix Admin - Domain Admins';

I looked at upgrade.php and could see in the code it was missing a primary key for that table so I added it. Next I got an error about the log table was also missing a primary key so I added that. I ran setup.php again.

I then got an error about duplicate primary keys.

ALTER TABLE domain_admins ADD COLUMN id int(11) not null auto_increment primary key

SQLSTATE[42000]: Syntax error or access violation: 1068 Multiple primary key defined

How do I get this working. I need to add a primary key to the table to install this but somewhere later on you are adding another key to the table.

I already saw the #475 thread but the modifications in that upgrade.php file do not work.

I'm stuck!

[DavidGoodwin]

I think the easiest solution would be to install/setup PostfixAdmin on a standalone database server, then do a dump of that database and then restore onto the galera one.

This will avoid the 'invalid' tables (assuming we've patched them all over time so they're all now acceptable!).

[LJ-CH]

Genius. I didn't think of that. I was also thinking of another solution. On Galera clusters in the /etc/mysql/mariadb.conf.d/60-galera.cnf file the setting innodb_force_primary_key = 1 is used to ensure all tables added to the cluster have a primary key. I THINK you could also set this is 0 on all nodes in the cluster which would allow update.php to add the correct tables. Assuming the primary keys are added later you can switch it back to 1 after PFA install.

I will report back once I have tried one of these options. Thanks @DavidGoodwin

[LJ-CH]

Update.

I decided to try to change the cluster settings to allow tables to be imported without a primary key. This worked but it crashed my cluster when I restarted it. For anyone else needing to do this, I would use @DavidGoodwin suggestion and set up PFA in a non-replication environment then dump and import the database to the replication cluster. ESPECIALLY IF YOU HAVE DATA IN YOUR CLUSTER.

Mine is a new cluster so it didn't matter. If you don't have any data in your database cluster I would use my option as it's slightly faster, Process below.

1. Stop MariaDB on all nodes.
2. Modify innodb_force_primary_key = 1 to innodb_force_primary_key = 0 in /etc/mysql/mariadb.conf.d/60-galera.cnf in all nodes.
3. cat /var/lib/mysql/grastate.dat and find a node that says safe_to_bootstrap: 1 (If there are none just edit the file and remove the 0 and add 1)
4. On the same node create a new cluster with galera_new_cluster
5. Start MariaDB on the remaining nodes

You will probably want to force primary keys again after you have got PFA installed.

Thanks again @DavidGoodwin