#8 no mysql replication for autoreply plugin sql insert

[Anonymous]

I have two mysql 5.0.84 servers with a working master-slave replication of whole postfix database with all tables (includes vacation). When autoreply plugin makes changes in postfix vacation table on master database, there is no replication of this changes to slave server (only on master) , but replication works fine with a custom CLI sql inserts to postfix vacation table.

Vacation plugin version 1.6.3
Roundcube version 0.3
Mysql version 5.0.48
PHP version 5.2.10

vacation config_inc.php

$rcmail_config['driver'] = 'virtual';
$rcmail_config['virtual']['dsn'] = 'mysql****';

[Jasper]

That's strange. But which user are you using? Is the same user as the one in the CLI?

[Nobody/Anonymous]

same user "vacation" for vacation plugin and CLI

[Nobody/Anonymous]

same user "vacation" for vacation plugin and CLI

[Nobody/Anonymous]

Replication problem has been solved.
SQL schema "use roundcube; insert into postfix.vacation ..." works with "replicate-wild-do-table = roundcube.%" and "replicate-wild-do-table = postfix.%" on slave host.
Please close this bug.

[Jasper]

Closed as requested

[Anonymous]

Hi, my mysql queries don't have the 'use roundcube; ' or 'use postfix' (or use postfixadmin in my case) statement. It seems the queries don't issue any USE statement at all.

example from mysql-bin.000001 (master replication thread)

*************************** 109. row ***************************
Log_name: mysql-bin.000001
Pos: 39383
Event_type: Query
Server_id: 1
End_log_pos: 39627
Info: INSERT INTO postfixadmin.alias (address,goto,active) VALUES ('emailaddres@somedomain.com','emailaddress@somedomain.com@autoreply.somedomain.com,emailaddress@somedomain.com','1')
***

This means that when the master replicates all databases these queries are included (don't use binlog_do_db), but the replication server will only pick it up when it is setup to replicate all databases. It is possible however to use the binlog_ignore_db directive to ignore other databases.

The correct fix for this would be to have each query (delete_query and insert_query from config.ini) start with USE dbase (where dbase is the database containing the vacation and vacation_notification tables). This way it is only needed to replicate a single database, instead of building a catch all or replicate_wild_do_table structure. Apparently user 'nobody' has a USE roundcube statement in each of these queries (?), while I do not.

*** Versions of software etc ***
config.ini queries
dbase = "postfixadmin"
select_query = "SELECT goto FROM %m.alias WHERE address='%e' AND goto='%g'"
delete_query = "DELETE FROM %m.alias WHERE address='%e'"
insert_query = "INSERT INTO %m.alias (address,goto,active) VALUES ('%e','%g','1')"

Vacation plugin=1.9.9
Roundcube=0.5.4 (debian squeeze-backports)
Mysql=5.1.49
Php=5.3.3

[Anonymous]

Addition/correction; the query does contain the USE roundcube statement (but it has many lines). The workaround mentioned does work:

##Add to /etc/mysql/my.cf (debian) on the slave mysql server
#Change USE roundcube:
replicate-wild-do-table = roundcube.%
#To USE postfixadmin (change 'postfixadmin' to your database containing the vacation and vacation_notification tables):
replicate-wild-do-table = postfixadmin.%

Best way to fix this nicely is to have the vacation plugin queries set their own USE parameter to the right database

[Anonymous]

Another addition/correction;

The smallest workaround for getting your postfix/postfixadmin table to work is to let the master export at least the roundcube and mail database. The slave needs to be set up with the mail database and have the following directives;

binlog_ignore_db = information_schema
binlog_ignore_db = mysql
binlog_ignore_db = phpmyadmin
binlog_ignore_db = roundcube
replicate-wild-do-table = postfixadmin.%

Another workaround apparently is to switch to ROW based replication (in newer mysql, the default is STATEMENT), but I have not tested is.
Bottom line, it would be good practise to get the plugin to issue a correct 'USE dbname', as it now calls USE roundcube database but changes lines in another database (the mailserver database).