MySQL DDL Trigger, Diff table schema for column rename

Question

I am creating a PHP script to compare schema of two databases.

I have managed to check for schema changes with regard to dropped/added tables, columns, indexes, references but when it comes to renamed columns I'm a bit stuck.

In the following example, the source database contains the most up to date schema and the destination database contains similar schema but is likely out of date.

Prerequisites:

• I am not aware of changes which have occurred since the last diff.
• The data in the databases will not match, but the schema should after the diff.

Take for example the following schema in the destination database.

Field   Type    Null    Key Default Extra
field1  int(11) NO      NULL     
field2  int(11) NO      NULL     
field3  int(11) NO      NULL     

And then assume the following schema in the source database.

Field   Type    Null    Key Default Extra
field1  int(11) NO      NULL     
field4  int(11) NO      NULL     
field3  int(11) NO      NULL     

Without knowing explicitly what occurred, I am unable to determine whether or not field2 changed to field4 by way of DROP, ADD AFTER or CHANGE COLUMN. The following two queries achieve the same result in terms of table structure, but the data is lost using the former.

(1)    ALTER TABLE `demo` DROP `field2`
       ALTER TABLE `demo` ADD `field4` INT( 11 ) NOT NULL AFTER `field1` 

(2)    ALTER TABLE `demo` CHANGE `field2` `field4` INT( 11 ) NOT NULL 

I can obviously drop the old column name and create a new one but that then loses any data in the original column. I need to use an ALTER TABLE table CHANGE COLUMN field new_name structure; query rather than DROP column FROM table followed by ALTER TABLE table ADD column definition;

I was hoping that I could use a DDL trigger to track changes in schema and insert a record of such changes into a table in the source database. I could later query this table to determine how a certain column came to be. However, as far as I can tell it's not possible to run triggers on DDL queries in MySQL which rules out logging these changes. I had a read of this worklog (WL#2418: DDL Triggers) on MySQL Forge (now residing in MySQL Developer Zone) but it appears to be pending implementation still unfortunately.

Is there a way in which I can update tables to match a schema with regard to renamed columns without data loss?

I have looked at things like MySQLDiff but it needs to be built into an existing bit of code so I'm having to build it myself.

Ideas I've Considered

• Add a comment to each column which is a unique number or string (call it a hash for the sake of argument). Query the information_schema table to retrieve this value and compare it on each column. If it's unique then it's a new column, or if it matches a hash but not a name or structure then it's been renamed/reconfigured.

• Compare the schema, if there's a new column- check it's position with regard to adjacent columns. If the name of the new column is in the same position as one which is missing compare the structure of that column. If it matches, consider it renamed. If not, consider it deleted then added.

Answer 1

There is no way to detect this but if you need this for your local machine then there is a way to do this.

1. Enable Mysql general logging to table
2. Now all queries will be saved in mysql.general_log table.
3. You can now run a query like SELECT * FROM mysql.general_log WHERE argument LIKE 'ALTER%' to give you all SQL statements that relate to database modification including column renames.

Hope that helps..