OpenMRS Trunk

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 rev:9371 and to trunk in rev:9372

Replying to [djazayeri|comment:6]:
> 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:

------------------------------------------------------------------------+

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 TRAC-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|source:openmrs-modules/addresshierarchy/metadata/sqldiff.xml]