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.

Improve this question

1 Answer 1

Reset to default
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

Improve this answer
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
    – RolandoMySQLDBA
    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.
    – RolandoMySQLDBA
    Jul 28, 2012 at 22:41
  • this is all, but how will this solve my issue?
    – ledy
    Jul 29, 2012 at 11:08

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.