CODEWITHSUNDEEP
perlmariadb
Peter Bauer
Peter Bauer

Reputation: 156

MariaDB replication some times does not working

I have a problem that I have never seen before with Mysql replication (Master/Slave): It works excellent when I execute on my PHP scripts, on the mysql console queries like "INSERT INTO". They are perfectly replicated on the mysql slave machine. But: Not if I do this same operations on a Perl script, using Perl DBI. Then its only stored on the Mysql master server. In the mysql.log file on the slave server, nothing arrives. I have this problem since I migrated on the two servers from Mysql to MariaDB. Is there something that a Perl DBI client should absolutely do that replicating would work?

I'm using this simple code to INSERT data:

#!/usr/bin/perl
use DBI;
$dsn = 'dbi:mysql:database=mysql;host=myhostname';
$dbh = DBI->connect($dsn, 'mouser', 'password',
            { RaiseError => 1, AutoCommit => 0 }) || exit(1);
...
$sth = $dbh->prepare("INSERT INTO mydatabase.mytable (user, domain) VALUES('$account_name', '$domain')");
$sth->execute();
$dbh->commit();

Is it possible that it does not replicate because DBI should use a mariadb driver? Like $dsn = 'dbi:mariadb:database=mysql;host=myhostname'; if that exists?

To be clear: Data inserted with your PHP script is still being replicated after switching to MariaDB, but data inserted with your Perl script isn't.

Upvotes: 2

Views: 288

Answers (1)

Peter Bauer
Peter Bauer

Reputation: 156

I found the reason why the replication failed with this simple Perl script: There is written:

$dsn = 'dbi:mysql:database=mysql;host=myhostname';
...
$sth = $dbh->prepare("INSERT INTO mydatabase.mytable (user, domain) VALUES('$account_name', '$domain')");

This is not working, because DBI connects to the database "mysql" and not "mydatabase" (see "database=mysql"). Even if the INSERT INTO inserts the row directly in the database "mydatabase", MariaDB master server ignores it for the replication. Its strange that it worked perfectly with MySQL and not for MariaDB (version 5.5.46). Maybe its a bug in MariaDB, I don't know.

Upvotes: 2

Related Questions