SQL DB update error Platform 2.0.0-227a3f Installation Wizard

Application Name: OpenMRS Platform Version Number: Most Recent (Cloned from git today)

Question: When I go through the manual setup process on mac os x 10.10.5 (Yosemite) I get the following sql error on the database update step:

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::200902142213::ewolodzko: Reason: liquibase.exception.DatabaseException: Error executing SQL UPDATE person_attribute_type as A JOIN person_attribute_type as B ON A.name = B.name SET A.sort_weight = (select count() from (select * from person_attribute_type) pat where pat.name < A.name) WHERE A.sort_weight is null: You can’t specify target table ‘A’ for update in FROM clause: Caused By: Error executing SQL UPDATE person_attribute_type as A JOIN person_attribute_type as B ON A.name = B.name SET A.sort_weight = (select count() from (select * from person_attribute_type) pat where pat.name < A.name) WHERE A.sort_weight is null: You can’t specify target table ‘A’ for update in FROM clause: Caused By: You can’t specify target table ‘A’ for update in FROM clause Error while trying to update to the latest database version

I’ve tracked down an old error similar to this: “https://wiki.openmrs.org/questions/79665185/sql-error-in-manual-installation” and “https://wiki.openmrs.org/display/docs/Installation+for+Developers+on+Mac+OS” (comment at bottom). After trying the suggested solutions in those, as well as some linked Stackoverflow posts, I still can’t get openmrs up and running.

I’m new to openmrs and sql, so this may be any easy fix. But if someone could explain to me how to fix this error, it would be greatly appreciated. Thanks for taking the time to help a new dev!

2 Likes

You must be running MySQL version 5.7.* This is a known problem which has not yet been fixed. For now, just use mysql versions 5.6

2 Likes

That did it! Thank you so much!

I am on mysql Ver 14.14 Distrib 5.7.9, any other fix or ideas will be a great help, I am not getting past the initial setup, thanks,

The only solution that worked for me was downgrading mysql to 5.6.. Unfortunately OpenMRS isn’t cooperating with 5.7. as far as I know.

Hi @dkayiwa, is it still the case?

I’m trying to install Platform 2.0.0 beta and had something similar. Is the only solution downgrading mysql to 5.6? Thank you.

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::20110825-1000-creating-providers-for-persons-from-encounter::raff: Reason: liquibase.exception.DatabaseException: Error executing SQL insert into provider(person_id, identifier, creator, date_created, retired, retired_by, date_retired, retire_reason, uuid) select distinct person.person_id, user.system_id, 1, CURRENT_TIMESTAMP, user.retired, user.retired_by, user.date_retired, user.retire_reason, CONCAT('prov', SUBSTRING(person.uuid, 5)) from person as person inner join encounter as encounter on encounter.provider_id = person.person_id left join (select max(users.user_id) as user_id, users.system_id, users.person_id, users.retired, users.retired_by, users.date_retired, users.retire_reason from users as users group by users.person_id) as user on person.person_id = user.person_id where user.user_id is not null and encounter.provider_id not in (select person_id from provider): Expression #2 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'openmrs.users.system_id' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by: Caused By: Error executing SQL insert into provider(person_id, identifier, creator, date_created, retired, retired_by, date_retired, retire_reason, uuid) select distinct person.person_id, user.system_id, 1, CURRENT_TIMESTAMP, user.retired, user.retired_by, user.date_retired, user.retire_reason, CONCAT('prov', SUBSTRING(person.uuid, 5)) from person as person inner join encounter as encounter on encounter.provider_id = person.person_id left join (select max(users.user_id) as user_id, users.system_id, users.person_id, users.retired, users.retired_by, users.date_retired, users.retire_reason from users as users group by users.person_id) as user on person.person_id = user.person_id where user.user_id is not null and encounter.provider_id not in (select person_id from provider): Expression #2 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'openmrs.users.system_id' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by: Caused By: Expression #2 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'openmrs.users.system_id' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by Error while trying to update to the latest database version

For now yes. The workaround would be to turn off sql_mode=only_full_group_by

How should I turn it off? I checked /etc/mysql/my.cnf there was nothing about sql_mode. Should I set sql_mode to some specific value?

It is on by default. So if you need to disable it, you may look at a few google search results or