0

In a MySQL MASTER MASTER scenario using InnoDB

When dropping an index on one instance will the same table on the other instance be available?

What is the sequence of activities? I assume the following sequence:

  1. DROP INDEX on 1st instance
  2. Added to the binary log
  3. DROP INDEX on 2nd instance

Can anyone confirm?

Kara
  • 6,115
  • 16
  • 50
  • 57
Kalisen
  • 467
  • 1
  • 5
  • 13

2 Answers2

0

In a MySQL MASTER MASTER scenario using InnoDB

In so far as I'm aware, such a thing is not possible. You'd need to use NDB, or be in a multi-master-slave environment with auto-incrementing fields configured to increment by 1/2/3/more. So assuming the latter. (Note: if you're aware of an InnoDB based solution, please share.)

When dropping an index on one instance will the same table on the other instance be available?

Dropping an index only means your index won't be available. Not the table. It'll be written (and propagated) to the binary log and begone with.

Denis de Bernardy
  • 75,850
  • 13
  • 131
  • 154
  • 2 'normal' servers (i.e. no mysqlcluster) can have each other as a master, and can replicate most engine-types with ease. Why do you think this is not possible? You could even make a ring, but that's a bit brittle. – Wrikken Jun 08 '11 at 17:59
  • Best I'm aware, the only way to get multiple masters with InnoDB is to configure the auto increments to yield numbers not one by one, but two (or three, four, etc.) by two (or three, four, etc.) in such a way that the masters will never interfere with each other when it comes to the latter. By contrast, NDB will allow any node to be used as master and handles the auto incrementing field normally on each of them. (I might be wrong, of course... I only recall this because of why I had picked NDB rather than InnoDB on a site.) – Denis de Bernardy Jun 08 '11 at 18:02
  • You are right about the auto-increment, but that's indeed easily configurable. I still don't get why you say a `MySQL MASTER MASTER scenario using InnoDB` is not possible: it very well is, 2 servers, each is both the master AND the slave of the other. Writing to both is allowed, but it's up to application logic to avoid UNIQUE clashes rather then the database itself, which is a drawback NDB indeed hasn't. On the other hand, NDB needs everything in memory, and that's something a normal master-master replication duo doesn't need. Best solution depends on de circumstances ;) – Wrikken Jun 08 '11 at 18:14
  • You are absolutely correct. Think of it as if I wrote: "not possible out of the box without ripping one's hair out with app logic". ;-) – Denis de Bernardy Jun 08 '11 at 18:21
  • Ack, then we are in agreement :) – Wrikken Jun 08 '11 at 18:22
0

I believe the following will happen:

  • Your DROP INDEX (which really runs an ALTER TABLE ... DROP INDEX) runs on the master
  • If the ALTER completes successfully the statement will then be added to the binlog and will be run on the slave

This means that the ALTER TABLE on the other machine won't start until the ALTER TABLE has successfully completed on the first (master) machine.

While the ALTER TABLE is running on either machine the table will be readable for a while and then neither readable/writeable as MySQL first makes a copy of the table internally then applies the changes.

From http://dev.mysql.com/doc/refman/5.0/en/alter-table.html

In most cases, ALTER TABLE works by making a temporary copy of the original table. The alteration is performed on the copy, and then the original table is deleted and the new one is renamed. While ALTER TABLE is executing, the original table is readable by other sessions. Updates and writes to the table are stalled until the new table is ready, and then are automatically redirected to the new table without any failed updates. The temporary table is created in the database directory of the new table. This can be different from the database directory of the original table if ALTER TABLE is renaming the table to a different database.

James C
  • 14,047
  • 1
  • 34
  • 43