Summary

Bitbucket server startup failed with the below error:

ORA-00933: sql command not properly ended

Diagnosis

You'll notice the below in the logs:

Caused by: liquibase.exception.DatabaseException: Error executing SQL UPDATE ADMIN.DATABASECHANGELOGLOCK SET LOCKED = 1, LOCKEDBY = 'tti-bbk001-cl.bps.private (28.12.11.57)', LOCKGRANTED = TO_TIMESTAMP('2021-08-26 04:03:36.019', 'YYYY-MM-DD HH24:MI:SS.FF') WHERE ID = 1 AND LOCKED = 0: ORA-00933: SQL command not properly ended

The lock is established during start-up, and then released once all changes to the database are done (all DDL, or structural changes). The node while joining the cluster is setting the lock into databasechangeloglock for the time required to verify the schema usually for few seconds or maybe a minute, but not more than that, then the lock is released and you would pretty much never see the lock again. There are cases where table databasechangeloglock has not been updated with the release lock information as described in KB Could not acquire change log lock.
It's the Liquibase that uses the DATABASECHANGELOGLOCK table to ensure only one instance of Liquibase is running at one time. So it prevents the racing condition during the startup of the nodes in the cluster or during the upgrade procedure when the lock stays during the whole schema upgrade procedure.

Cause

The message ORA-00933: sql command not properly ended. This error is usually caused by an SQL statement with a clause that is not allowed for that statement. Some examples that might cause this error are:

• An INSERT statement with an ORDER BY clause or an INNER JOIN
• A DELETE statement with an INNER JOIN or ORDER BY clause
• An UPDATE statement with an INNER JOIN
• If the SQL syntax is incorrect.

The error also might occur because of using a semicolon ";" at the end or incorrect syntax since the other causes involve joins.

Solution

Bitbucket Server needs an exclusive lock on the DATABASECHANGELOGLOCK table in order to start successfully. This table needs to be updated with the release lock information.

UPDATE DATABASECHANGELOGLOCK SET LOCKED=0, LOCKGRANTED=null, LOCKEDBY=null where ID=1;

Please refer to our KB article: 

• Bitbucket Server Does Not Start - Could not acquire change log lock