Re: [Maria-discuss] Replication Problem
Thomas Plant writes: > Your tip with 'MASTER_USE_GTID=slave_pos' fixed it.Will have to look > better in the documentation next time, never found the 'slave_pos' > mentioned or 'SET sql_log_bin=0'. > > Thank you very much for your help. Welcome, glad that you solved your problem. The slave_pos/current_pos is confusing a lot of users. It would probably have been better if current_pos had never been introduced. > so you mean that disabling the binary log on the slave would be > indicated? Can I do it while it is online? SET sql_log_bin=0 disables binlogging only for the following queries done in that connection (not in general). So yes, it can be done online. You would do it for queries that you will not want replicated to other servers. For example, if you later make this slave the master, and put the old master to replicate from the old slave (now new master), you probably do not want to replicate your earlier slave-fixup-query. Hence the suggestion to SET sql_log_bin=0 to avoid having this query in the slave binlog. But if using MASTER_USE_GTID=slave_pos, in most cases it won't matter one way or the other. - Kristian. ___ Mailing list: https://launchpad.net/~maria-discuss Post to : maria-discuss@lists.launchpad.net Unsubscribe : https://launchpad.net/~maria-discuss More help : https://help.launchpad.net/ListHelp
Re: [Maria-discuss] Replication Problem
Thomas Plant writes: > Today I had time to look at the error, removed the duplicate ID from > the table and started the slave thread again using 'start slave;'. > > But now I get another error: > > Last_IO_Error: Got fatal error 1236 from master when reading data from > binary log: 'Error: connecting slave requested to start from GTID > 0-2-2948175468, which is not in the master's binlog. Since the > master's binlog contains GTIDs with higher sequence numbers, it > probably means that the slave has diverged due to executing extra > erroneous transactions' So it seems you are using MariaDB Global Transaction ID with MASTER_USE_GTID=current_pos, and you forgot to do the duplicate ID removal under `SET sql_log_bin=0`. The easiest solution is probably to CHANGE MASTER TO MASTER_USE_GTID=slave_pos. This should make the slave ignore the local transaction and just connect to the master using the last replicated position. (current_pos tells the MariaDB server that you expect any local transactions on the slave to also be replicated to other servers, hence the error. current_pos is appropriate for an earlier master that is turned into a slave, but not for a slave where local "fixup" transactions ended up in the binlog). Hope this helps, - Kristian. ___ Mailing list: https://launchpad.net/~maria-discuss Post to : maria-discuss@lists.launchpad.net Unsubscribe : https://launchpad.net/~maria-discuss More help : https://help.launchpad.net/ListHelp
Re: [Maria-discuss] Replication Problem
About replication, I dont let it stop on duplicate keys, I prefer to use slave-skip-errors = all and let the primary key on the tables take care of duplicate records. Replication will only stop if something is corrupted, which may be your case. Some position the the binlog got lost in the power outage and you will have to reposition the master slave binlog, probably loosing some data on one side. []s. On Wed, Jul 4, 2018 at 8:02 AM, Guillaume Lefranc wrote: > Hi Thomas, > > Could you please show the output of SHOW SLAVE STATUS and SHOW VARIABLES > LIKE 'gtid%' ? > > Thanks > > Guillaume Lefranc > signal18.io consulting > > Le mer. 4 juil. 2018 à 12:31, Thomas Plant a > écrit : > >> Hello, >> >> I'd have a question about MariaDB 10.1 replication, which has been >> interrupted by a power outage in our datacenter. >> We started the master server and had a duplicate id in a table on the >> slave, so replication stopped. We did not have time to adjust this at >> the moment, there were a lot of other systems to start and check if all >> was alright. >> >> Today I had time to look at the error, removed the duplicate ID from the >> table and started the slave thread again using 'start slave;'. >> >> But now I get another error: >> >> Last_IO_Error: Got fatal error 1236 from master when reading data from >> binary log: 'Error: connecting slave requested to start from GTID >> 0-2-2948175468, which is not in the master's binlog. Since the master's >> binlog contains GTIDs with higher sequence numbers, it probably means >> that the slave has diverged due to executing extra erroneous transactions' >> >> Can this be resolved? Or will I have to start replication from scratch? >> >> Greetings, >> Thomas >> >> ___ >> Mailing list: https://launchpad.net/~maria-discuss >> Post to : maria-discuss@lists.launchpad.net >> Unsubscribe : https://launchpad.net/~maria-discuss >> More help : https://help.launchpad.net/ListHelp >> > > ___ > Mailing list: https://launchpad.net/~maria-discuss > Post to : maria-discuss@lists.launchpad.net > Unsubscribe : https://launchpad.net/~maria-discuss > More help : https://help.launchpad.net/ListHelp > > ___ Mailing list: https://launchpad.net/~maria-discuss Post to : maria-discuss@lists.launchpad.net Unsubscribe : https://launchpad.net/~maria-discuss More help : https://help.launchpad.net/ListHelp
Re: [Maria-discuss] Replication Problem
Hi Thomas, Could you please show the output of SHOW SLAVE STATUS and SHOW VARIABLES LIKE 'gtid%' ? Thanks Guillaume Lefranc signal18.io consulting Le mer. 4 juil. 2018 à 12:31, Thomas Plant a écrit : > Hello, > > I'd have a question about MariaDB 10.1 replication, which has been > interrupted by a power outage in our datacenter. > We started the master server and had a duplicate id in a table on the > slave, so replication stopped. We did not have time to adjust this at > the moment, there were a lot of other systems to start and check if all > was alright. > > Today I had time to look at the error, removed the duplicate ID from the > table and started the slave thread again using 'start slave;'. > > But now I get another error: > > Last_IO_Error: Got fatal error 1236 from master when reading data from > binary log: 'Error: connecting slave requested to start from GTID > 0-2-2948175468, which is not in the master's binlog. Since the master's > binlog contains GTIDs with higher sequence numbers, it probably means > that the slave has diverged due to executing extra erroneous transactions' > > Can this be resolved? Or will I have to start replication from scratch? > > Greetings, > Thomas > > ___ > Mailing list: https://launchpad.net/~maria-discuss > Post to : maria-discuss@lists.launchpad.net > Unsubscribe : https://launchpad.net/~maria-discuss > More help : https://help.launchpad.net/ListHelp > ___ Mailing list: https://launchpad.net/~maria-discuss Post to : maria-discuss@lists.launchpad.net Unsubscribe : https://launchpad.net/~maria-discuss More help : https://help.launchpad.net/ListHelp
[Maria-discuss] Replication Problem
Hello, I'd have a question about MariaDB 10.1 replication, which has been interrupted by a power outage in our datacenter. We started the master server and had a duplicate id in a table on the slave, so replication stopped. We did not have time to adjust this at the moment, there were a lot of other systems to start and check if all was alright. Today I had time to look at the error, removed the duplicate ID from the table and started the slave thread again using 'start slave;'. But now I get another error: Last_IO_Error: Got fatal error 1236 from master when reading data from binary log: 'Error: connecting slave requested to start from GTID 0-2-2948175468, which is not in the master's binlog. Since the master's binlog contains GTIDs with higher sequence numbers, it probably means that the slave has diverged due to executing extra erroneous transactions' Can this be resolved? Or will I have to start replication from scratch? Greetings, Thomas ___ Mailing list: https://launchpad.net/~maria-discuss Post to : maria-discuss@lists.launchpad.net Unsubscribe : https://launchpad.net/~maria-discuss More help : https://help.launchpad.net/ListHelp
