CODEWITHSUNDEEP
phpmysqlmariadb
mseifert
mseifert

Reputation: 5670

mysqldump stopped working with conversion to MariaDB - set-gtid-purged=OFF issue?

I am on a shared server (webhostinghub) and run a nightly CRON job to back up my mySQl InnoDB database. The CRON job runs a php script which in turn issues a mysqldump command via passthrough() as follows;

$return = -1;
$command = "mysqldump -h localhost -u server_user -ppassword --add-drop-table --quick --set-gtid-purged=OFF server_database > ./backup/backup-2018-02-19.sql"; 

passthrough($command, $return);    // note: I just added the $return parameter to see what it is returning which is `7` => non zero tells me there is some error but there is no further info I can find.

The script was working for years without error and recently started creating zero byte files.

I notice from phpMyAdmin that:

Server type: MariaDB
Server version: 10.2.11-MariaDB-log - MariaDB Server
Protocol version: 10

I don't remember being on MariaDB and it is obvious my provider switched me over without mentioning it (thanks webhostinghub) and this is the issue. I know they are supposed to be 99% compatible, but is this the 1%?

I removed the --set-gtid-purged=OFF parameter and the backup "appears" to have worked (I have not validated the data, but the file now has data).

I can't remember why I added the --set-gtid-purged=OFF parameter and what it does is not completely obvious to me. I know it is for Global Transaction Identifier’s (GTID’s) but am not sure if I use these. I do not "replicate" but want my exports and imports to keep unique primary ids. I import into a mysql database on my localhost.

I would appreciate any help in determining the equivalent mysqldump command for the MariaDB database and what to look out for when importing into my localhost mysql database

Thanks

Upvotes: 2

Views: 6907

Answers (1)

elenst
elenst

Reputation: 3987

Implementation of GTID-based replication in MariaDB is completely different from MySQL's, so the logic which supports it in tools such as mysqldump is also incompatible. set-gtid-purged option in MySQL's version of mysqldump sets a variable which doesn't exist and would have no meaning in MariaDB, so the option does not exist, either. If you don't use replication in general and GTID in particular, you can just remove the option from the command line, the rest should work identically. It should have no effect on primary keys, if that's what you mean by primary ids.

"Almost complete compatibility" on option/syntax level between MariaDB and MySQL is true for release lines up to and including 5.5. Starting from MariaDB 10.0 and MySQL 5.6, there are noticeable differences.

Here is a list of known incompatibilities between MariaDB and MySQL. It might be incomplete, but it should cover the most part.

Loading a dump of MySQL server into MariaDB server should work, unless MySQL server was using some syntax or features that MariaDB either does not have or has in a different form. Loading a dump made by MySQL's version of mysqldump into MariaDB server might cause problems, for example if it sets variables which MariaDB does not have (such as @@global.gtid_purged controlled by set-gtid-purged, there can be more). In this case, manual editing of the dump might be needed.

Upvotes: 5

Related Questions