Justin, can you get this error again and then do a "show innodb status" on the mysql command line? It should give the (more helpful) foreign key error in that output somewhere.

Correction, first run the sql that failed: "ALTER TABLE location ADD CONSTRAINT parent_location FOREIGN KEY (parent_location) REFERENCES location(location_id)" at the mysql command line and then do "show innodb status" and look at the foreigh key error section.

Hi all,

I am seeing this error too. I ran SHOW ENGINE INNODB STATUS and got this output:

LATEST FOREIGN KEY ERROR

090723 8:11:45 Error in foreign key constraint creation for table openmrs/#sql-a5e_c. A foreign key constraint of name openmrs/parent_location already exists. (Note that internally InnoDB adds 'databasename/' in front of the user-defined constraint name). Note that InnoDB's FOREIGN KEY system tables store constraint names as case-insensitive, with the MySQL standard latin1_swedish_ci collation. If you create tables or databases whose names differ only in the character case, then collisions in constraint names can occur. Workaround: name your constraints explicitly with unique names.

Comments?

Tried SET FOREIGN_KEY_CHECKS = 0 and got this error instead:

ERROR - UpdateFilter$UpdateFilterCompletion$1.run(492) |2009-07-23 08:15:02,913| Unable to update the database org.openmrs.util.DatabaseUpdateException: There was an error while updating the database to the latest. file: liquibase-update-to-latest.xml. Error: Migration failed for change set liquibase-update-to-latest.xml::20090224-1256::Keelhaul+bwolfe:

Reason: liquibase.exception.JDBCException: Error executing SQL ALTER TABLE location ADD parent_location INT(11):

Caused By: Error executing SQL ALTER TABLE location ADD parent_location INT(11): Caused By: Duplicate column name 'parent_location'k

I got past this error by dropping the existing parent location constraint:

ALTER TABLE lcation DROP FOREIGN KEY parent_location;

And then dropping the parent_location_id column. Not sure how kosher that is though.

Hi Malawi,

What does the 'location' table in your production 1.4 database look like? Does it have a parent_location_id column?

Are you using a location hierarchy module of some sort?

-Darius

(We should add a "precondition" that skips this changeset if both the column and foreign key exists)

Added precondition to 1.5.x in [9371] and to trunk in [9372]

Replying to djazayeri:

Hi Malawi, What does the 'location' table in your production 1.4 database look like? Does it have a parent_location_id column? Are you using a location hierarchy module of some sort? -Darius

Our location table does have a parent_location_id column:

| retire_reason | varchar(255) | YES | | NULL | | | parent_location_id | int(11) | YES | MUL | NULL | | | location_type_id | int(11) | YES | MUL | NULL | | +--------------------+--------------+------+-----+---------------------+----------------+

I don't think we are using a location hierarchy right now, although Jeff and I had discussed it. We would like to move to using one though.

ew

Ticket #169 had a patch file on it that added location.parent_location_id, but what got committed to trunk was location.parent_location. Perhaps someone applied that patch to test things out?

The addresshierarchy module does not add location.parent_location_id