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..

Answer 2

MySQL doesn't supports DDL Triggers. I can see in their Work Log that they've planned it for server 7.1.

The Task ID is WL#2418.

Answer 3

ALTER TABLE table
 CHANGE COLUMN old_col_name
 new_col_name column_definition;

This changes the column name without losing the data in the column (unless you change the data type as well and the new data type is smaller than the old data type, e.g. changing BIGINT to SMALLINT).

This command unfortunately requires that you repeat the current definition of the column, e.g. data type, NOT NULL, and other options. There is no command in MySQL to simply rename a column.

The CHANGE COLUMN clause is a MySQL extension to standard SQL, which provides no syntax to rename a column in place.

Another solution if you want to stick to standard SQL is to add a new column, copy the data from the old column to the new column, and then drop the old column:

ALTER TABLE table ADD COLUMN new_col_name column_definition;

UPDATE table SET new_col_name = old_col_name;

ALTER TABLE table DROP COLUMN old_col_name;

Answer 4

The biggest issue here is that if more than 1 column name (of the same type) was changed for the same table there is NO way to tell which of the columns was changed to which. (There could still be the option of actually comparing the data and hope that some data is the same in both tables, that way be able to deduce which column what changed to what, but it would be completely un-reliable)

Than there's also the issue of 1 (or more) field changed and 1 (or more) added, you would be running on the same exact issue as above again.

MySQLDiff mentioned above would be able to tell you which fields are new and which are old (and more like new tables etc), but it won't be able to tell you which column name got changed to what and it doesn't even try to. (No software can reliably do that, especially if your data is not the same between db's) You can see what it does in: mysql-1.5.0/library/database.lib.php and mysql-1.5.0/library/generator.lib.php

One thing I would highly recommend though, is introducing a very simple process in your development team. - Every time a change to db structure is made (or any records that are needed for the system to work) should be recorded into a eg. db_changes.sql file which is under version control. For every new revision, if such changes to the db are needed then the db_changes.sql file is wiped, changes get recorded and saved as a new revision. That way changes to the database structure are revised properly and applying them is just a matter of diff-ing 2 revision points. (MysqlWorkbench is one of the clients that if you change tables it will show you the query it will internally run, if your devs need to use a client at all)

Answer 5

I don't think you should be synchronizing the schema this way. You should be checking migrations scripts to source code every time you change anything, then have a table in your database that logs which migration was applied. This way you can make sure that all the migrations were applied on all databases. for example `

CREATE TABLE `ddl_version` (
version` varchar(32)  NOT NULL,
`applied_on` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
UNIQUE KEY `version` (`version`)

)`