search

mmatuson / SchemaSync

A MySQL Schema Versioning and Migration Utility
http://mmatuson.github.io/SchemaSync/
Other
339 stars 109 forks source link

No Sync with Foreign Keys in Schema #4

Closed ehardway closed 13 years ago

ehardway commented 14 years ago

Error

Traceback (most recent call last): File "/usr/bin/schemasync", line 7, in ? sys.exit( File "build/bdist.linux-x86_64/egg/schemasync/schemasync.py", line 264, in main File "build/bdist.linux-x86_64/egg/schemasync/schemasync.py", line 123, in processor File "build/bdist.linux-x86_64/egg/schemasync/schemasync.py", line 229, in app File "build/bdist.linux-x86_64/egg/schemasync/syncdb.py", line 45, in sync_schema File "build/bdist.linux-x86_64/egg/schemasync/syncdb.py", line 95, in sync_table File "build/bdist.linux-x86_64/egg/schemaobject/table.py", line 154, in foreign_keys File "build/bdist.linux-x86_64/egg/schemaobject/foreignkey.py", line 64, in ForeignKeySchemaBuilder TypeError: an integer is required

I am trying to run schemasync against a server running MySQL5.1.34 and a Server running MySQL5.1.48 with innodb_plugin.

I have done some testing, it will not work if a table in database has a foreign key.

mmatuson commented 14 years ago

According to the MySQL docs, "The value of POSITION_IN_UNIQUE_CONSTRAINT is NULL for unique and primary-key constraints. For foreign-key constraints, it is the ordinal position in key of the table that is being referenced. ". I am restricting it to CONSTRAINT_TYPE = 'FOREIGN KEY' but I may need to add a restriction in the KEY_COLUMN_USAGE table query.

I need to dig a little deeper to see what scenarios are causing this. Would you be able to provide the create table statements for the tables that are attempting to be synced?

mmatuson commented 14 years ago

BTW, this issue needs to be fixed in SchemaObject, so I will likely close this issue and re-open it there.

ehardway commented 14 years ago

CREATE TABLE table1 ( id bigint(20) unsigned NOT NULL AUTO_INCREMENT, tp_type char(2) COLLATE utf8_bin NOT NULL, tp_subtype varchar(4) COLLATE utf8_bin NOT NULL, tp_key varchar(255) COLLATE utf8_bin NOT NULL, status char(1) COLLATE utf8_bin NOT NULL, threat char(1) COLLATE utf8_bin NOT NULL, created int(10) unsigned NOT NULL, last_updated int(10) unsigned NOT NULL, last_queried int(10) unsigned NOT NULL, PRIMARY KEY (id), UNIQUE KEY table1_index (tp_type,tp_key), KEY last_updated_index (last_updated), KEY status_last_updated_index (status,last_updated) ) ENGINE=InnoDB AUTO_INCREMENT=867172394 DEFAULT CHARSET=utf8 COLLATE=utf8_bin ROW_FORMAT=DYNAMIC

CREATE TABLE table2 ( id bigint(20) unsigned NOT NULL, last_touched int(10) unsigned NOT NULL, UNIQUE KEY table2_table1_key (id), KEY last_touched_index (last_touched), CONSTRAINT table2_table1_key FOREIGN KEY (id) REFERENCES table1 (id) ON DELETE CASCADE ON UPDATE CASCADE ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin ROW_FORMAT=DYNAMIC

mmatuson commented 13 years ago

Sorry this took so long to fix. For a while I didn't have the bandwidth to work on it.

The fix is in SchemaObject 0.5.4. It will be available with SchemaSync in the next release but until then you can checkout a copy of the development version.

To upgrade your copy of SchemaObject, remove the old one and install the version you just checked out using the instructions in the README.

Let me know if you have any issues.