On Thu, Nov 5, 2009 at 5:18 PM, Jay Pipes <[email protected]> wrote: > Hi! > > Stewart and I had a good long chat last night about the workings of the > ALTER TABLE statement, and in particular what happens in the parser when an > ALTER TABLE is recognized. > > Work is currently underway to replace the HA_ALTER_INFO structure that is > constructed in the parser and passed to the mysql_alter_table() function > with a GPB message (message::AlterTable). > > Just like the HA_ALTER_INFO structure, this new GPB message would contain > the "instructions" parsed from the ALTER TABLE statement -- in other words, > a list of columns to add, to modify, to drop, etc. > Right now, what happens in the parser is basically this: > > a) Once the ALTER TABLE symbol is found... > b) Create an HA_ALTER_INFO > c) For each ADD COLUMN clause, add a field definition to the list of fields > to add to the HA_ALTER_INFO > d) Repeat c) but for dropped columns > e) Repeat c) but for modified columns > f) Tack on additional stuff to HA_ALTER_INFO like a changed engine name, > table collation, etc. > g) Tack the HA_ALTER_INFO onto the Session's Lex class. > > From a replication perspective, what the above procedures imply is that the > replication system should serialize this list of changes in the > HA_ALTER_INFO and send it to the replicas to use in altering the table on > the replica. > > I mentioned to Stewart last night that I think having the parser construct > this list of actions into a HA_ALTER_INFO (or message::AlterTable) may not > be the best overall solution, and that instead what the parser should do is > merely construct an "after image" of the message::Table definition that the > table should look like after the changes contained in the ALTER TABLE > statement. > > In other words, instead of the above procedures in the parser, do this: > > a) Once the ALTER TABLE symbol is found... > b) Grab the existing message::Table definition of the table in question > c) Create a copy of the existing message::Table definition > d) Modify this copy of the message::Table definition according to the > parsed ADD, DROP, MODIFY COLUMN clauses. > e) Send the original and new message::Table definitions to > mysql_alter_table() and the replication stream. > f) Have mysql_alter_table() (on the master AND the replica) handle figuring > out the actions needed to convert the underlying table from the before to > the after definition. > > In other words, have the parser simply change the definition of the Table > and pass the new definition to a function which handles the implementation > of the table modification... > > I believe this would simplify the parser and the general table alteration > procedures in the server, and would allow the replication of ALTER TABLE > events to be extremely simple: we would simply send a before and after > message::Table definition to the replicas... > > Thoughts? >
Sounds good. I guess it is better to do the work of finding the diff between the tables on the slave and since ALTER is not considered a frequent operation in most apps, the extra transaction log writing is not a problem even when having hundreds of columns. /Marcus
_______________________________________________ Mailing list: https://launchpad.net/~drizzle-discuss Post to : [email protected] Unsubscribe : https://launchpad.net/~drizzle-discuss More help : https://help.launchpad.net/ListHelp
