Bamboo Build Failure Due to Platform Upgrade

[mozzy (Moses Mutesasira)]

We have got a bamboo buld failure for Reference Application Distribution after I upgraded the reff app distro to run on platform 2.3.0-SNAPSHOT

see Logs

Curently the qa-reffapp server , is also down , its encountering an error with data base update

Is it posiible to drop the data base entirely and just build a new one for the qa-refapp server ??
cc @cintiadr @dkayiwa

[cintiadr (Cintia Del Rio)]

Ok, so let’s go step-by-step.

First, I changed the ‘Deploy to qa server’ to be synchronous, so you can see the deployment logs. I don’t know what evidence you have to say to say that qa-refapp is failing due to database update, the logs you sent are unrelated to qa-refapp. Did you check the application logs at all?

The database is recreated from scratch on every deployment. Nothing is kept between runs.

The red job is not with QA-refapp. It’s to build the standalone, and the error is:

[WARNING] The POM for org.openmrs.api:openmrs-api:jar:2.3.0-SNAPSHOT is missing, no dependency information available
 	[ERROR] Failed to execute goal org.liquibase:liquibase-maven-plugin:2.0.5:update (empty-database-create-schema) on project referenceapplication-standalone: Execution empty-database-create-schema of goal org.liquibase:liquibase-maven-plugin:2.0.5:update failed: Plugin org.liquibase:liquibase-maven-plugin:2.0.5 or one of its dependencies could not be resolved: Could not find artifact org.openmrs.api:openmrs-api:jar:2.3.0-SNAPSHOT -> [Help 1]

So, is this artefact even in jfrog?

[cintiadr (Cintia Del Rio)]

Caused by: liquibase.exception.MigrationFailedException: Migration failed for change set liquibase-update-to-latest.xml::TRUNK-5672-201910211105::Mritunjay:
     Reason: liquibase.exception.DatabaseException: Error executing SQL UPDATE `users` SET `username` = 'admin' WHERE system_id='admin': admin user account is locked:
          Caused By: Error executing SQL UPDATE `users` SET `username` = 'admin' WHERE system_id='admin': admin user account is locked:
          Caused By: admin user account is locked
	at liquibase.changelog.ChangeSet.execute(ChangeSet.java:347)
	at liquibase.changelog.visitor.UpdateVisitor.visit(UpdateVisitor.java:27)
	at org.openmrs.util.DatabaseUpdater$1OpenmrsUpdateVisitor.visit(DatabaseUpdater.java:191)
	at liquibase.changelog.ChangeLogIterator.run(ChangeLogIterator.java:58)
	at org.openmrs.util.DatabaseUpdater.executeChangelog(DatabaseUpdater.java:220)
	at org.openmrs.util.DatabaseUpdater.executeChangelog(DatabaseUpdater.java:150)
	... 2 more
Caused by: liquibase.exception.DatabaseException: Error executing SQL UPDATE `users` SET `username` = 'admin' WHERE system_id='admin': admin user account is locked
	at liquibase.executor.jvm.JdbcExecutor.execute(JdbcExecutor.java:62)
	at liquibase.executor.jvm.JdbcExecutor.execute(JdbcExecutor.java:104)
	at liquibase.database.AbstractDatabase.execute(AbstractDatabase.java:1091)
	at liquibase.database.AbstractDatabase.executeStatements(AbstractDatabase.java:1075)
	at liquibase.changelog.ChangeSet.execute(ChangeSet.java:317)
	... 7 more
Caused by: java.sql.SQLException: admin user account is locked
	at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:965)
	at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3973)
	at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3909)
	at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:2527)
	at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:2680)
	at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2480)
	at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2438)
	at com.mysql.jdbc.StatementImpl.executeInternal(StatementImpl.java:845)
	at com.mysql.jdbc.StatementImpl.execute(StatementImpl.java:745)
	at liquibase.executor.jvm.JdbcExecutor$1ExecuteStatementCallback.doInStatement(JdbcExecutor.java:92)
	at liquibase.executor.jvm.JdbcExecutor.execute(JdbcExecutor.java:55)
	... 11 more

I can easily reproduce it locally as well.

Clone https://github.com/openmrs/openmrs-contrib-ansible-docker-compose, go to folder files/qa-refapp.

Run docker-compose up -d . It fails exactly the same way.

[cintiadr (Cintia Del Rio)]

Now, look at this sql:

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::TRUNK-5672-201910211105::Mritunjay:
     Reason: liquibase.exception.DatabaseException: Error executing SQL UPDATE `users` SET `username` = 'admin' WHERE system_id='admin': admin user account is locked:

What is that SQL trying to do?

[mozzy (Moses Mutesasira)]

@cintiadr

So, is this artefact even in jfrog?

unless mistaken , but i seem to see it there.

@cintiadr

I don’t know what evidence you have to say to say that qa-refapp is failing due to database update

Sure the logs are for failure to build the standalone,
Hovewever , the qa-server has failed to startup also, even when its being deployed succesfully

[cintiadr (Cintia Del Rio)]

All our servers block in the SQL level to update the admin user to prevent users from changing the admin user. That was the magical incantation that has been passed over and over.

We don’t need anyone changing it to confuse our test servers.

DELIMITER ;;
/*!50003 CREATE*/ /*!50017 DEFINER=`root`@`localhost`*/ /*!50003 TRIGGER users_update BEFORE UPDATE ON users FOR EACH ROW
IF OLD.user_id = 1 THEN
  SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'admin user account is locked';
END IF */;;
DELIMITER ;

It was never a requirement that an SQL would now change it. What can be the new requirement?

[mozzy (Moses Mutesasira)]

am seeing a talk post here , why they had to do it that way

Admin user doesn't have a username.

Hello People, We are working on enabling bahmni appointments to run on OpenMRS. This application leverages logged-in user’s username. It sets the current logged-in username in the cookies and uses it later. This works for all the users but the admin user. We see that admin user doesn’t have a username and the cookie is set to null. We checked out on bahmni-demo and found out that the username for admin is populated but not on OpenMRS Demo. Any specific reason why the admin user doesn’t have a…

[mozzy (Moses Mutesasira)]

here is the commit that has caused problems , is there are way we can add another pre-condition , so that ,that change set doesnt run on our servers ?? cc @dkayiwa

[cintiadr (Cintia Del Rio)]

Look, I don’t want to leave open for others to change the admin’s username in our servers.

I could just change the sql to have ‘admin’ as the username, BUT the SQL I have was generated from the SDK. I wonder if it should have been fixed there instead of do a migration. I’m not even sure if this needs to be a data migration or if the intent was only fix the demo server.

[mozzy (Moses Mutesasira)]

@cintiadr

Look, I don’t want to leave open for others to change the admin’s username in our servers

true

@cintiadr

I could just change the sql to have ‘admin’ as the username

if you do that , the above change set wont run , so we wont have any issues ,as the pre conditon looks for any existing user named “admin”

[mddubey (Mritunjay Dubey)]

@cintiadr

I’m not even sure if this needs to be a data migration or if the intent was only fix the demo server.

Hello, The fix was provided by me and the reason was we needed the admin user to have a username. For some reason it was never present and was using the system-id.

[cintiadr (Cintia Del Rio)]

@mddubey

Hello, The fix was provided by me and the reason was we needed the admin user to have a username. For some reason it was never present and was using the system-id .

But the question is: was the problem only relevant on the demo server? Was there any benefit to updating that in all OpenMRS instances when upgrading it? Wouldn’t it be better to just change the initial data instead?

[mddubey (Mritunjay Dubey)]

Our problem was not regarding the demo server. I had given that as a reference in the talk. In our implementation, we were using the session API to fetch the username of the logged-in user and use it further.

We figured out it was not working because of admin user not having a username and we raised a fix. Was it not given a username intentionally?

[burke (Burke Mamlin)]

Username was designed as a convenient moniker for users to use in place of their system ID for login. The super user account most likely never got a username because it had a convenient/special system ID (“admin”). I don’t think there is any harm to this account having a username other than the special case of our infrastructure locking down the account to prevent abuse (e.g., changing the password) of public systems.

What we missed in adding the username was ensuring this was done by default in the SDK (rather than relying on the liquibase change). If the SDK generates an admin user with a username, the problem will resolve.

[cintiadr (Cintia Del Rio)]

I agree with Burke, it sounds confusing to me to create the data without username, and then fix it via liquibase.

If we change the data creation, it would cover any new instance of OpenMRS. My question is: is there any need to fix existing OpenMRS instances? (note that all our test services are recreated every couple of days, so it’s just a new server).

[dkayiwa (Daniel Kayiwa)]

I have just released a new version 3.13.3 of the SDK which generates an admin user with a username.

[mozzy (Moses Mutesasira)]

Thanks @dkayiwa

[cintiadr (Cintia Del Rio)]

@dkayiwa do you want me to change our test environments to have that fix?

[dkayiwa (Daniel Kayiwa)]

Yes that would be great @cintiadr

[cintiadr (Cintia Del Rio)]

@permissionerror I do plan on doing this tonight, but if you are free you can do it before that.

So we need to go to the sql in all refapp and platform environments , and edit the SQL to add a username to the ‘admin’ user. We’ll roll it out using ansible as usual.

We’ll have to destroy and recreate all environments after that.