1

Trying to re-sync a slave, I run into troubles with some of my tables which do not have a auto-increment primary index, but a primary unique ID (not auto-increment).

I run the re-sync with

mk-table-sync --execute --sync-to-master localhost --databases gman --table "list,of,my,problem,tables" --wait 60

This works for all tables with auto-inc primary ID, and for the others it says:

Can't make changes on the master because no unique index exists at /usr/bin/mk-table-sync line 7733. while doing db123.problem on localhost

How to fix this in another way? I do only want the slave to be synced with the proper data from master. If possible, i'd prefer doing this with mk-table-sync instead of LOCK, dump, insert the dump on the slave which is really old-fashioned and not very useful use-case.

Thanks for tips on maakit.

1 Answer 1

0

If the indexed column in question has an index but is not defined as a UNIQUE INDEX, mk-table-sync will not know it is UNIQUE and will regard the index as a secondary index. You would just have to redefine the index to be UNIQUE and run mk-table-sync again.

SUGGESTION : Please run mk-table-sync on each individual table but not with --execute

mk-table-sync --print --sync-to-master localhost --databases gman --table tblname --wait 60 > tblname.sql

That way you can look the SQL before excuting it on the Slave.

If the Slave is also a Master, block the Slave from Replicating

echo "SET SQL_LOG_BIN=0;" > tblname.sql

mk-table-sync --print --sync-to-master localhost --databases gman --table tblname --wait 60 >> tblname.sql

5
  • The table has has a unique index which is also primary. though, it fails. any other idea? (it's on a common slave)
    – ledy
    Jul 28, 2012 at 16:59
  • Please do SHOW CREATE TABLE tblname and display it Jul 28, 2012 at 17:04
  • CREATE TABLE prob_table ( id int(11) NOT NULL, PRIMARY KEY (id)) ENGINE=InnoDB DEFAULT
    – ledy
    Jul 28, 2012 at 21:04
  • If that's all the columns, there is nothing you can do. If there is more, please put the whole display in the question. Jul 28, 2012 at 22:41
  • this is all, but how will this solve my issue?
    – ledy
    Jul 29, 2012 at 11:08

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .