Data Center configuration fails with "ERROR Couldn't initialize the database" while executing a "ON CONFLICT" clause in Postgres
Platform notice: Server and Data Center only. This article only applies to Atlassian products on the Server and Data Center platforms.
Support for Server* products ended on February 15th 2024. If you are running a Server product, you can visit the Atlassian Server end of support announcement to review your migration options.
*Except Fisheye and Crucible
Problem
The initial Data Center setup fails while initializing the database via hipchat datacenter configure --file /path/to/configuration.json.
The following appears in the /var/log/hipchat/datacenter.log (3.0.1):
sqlalchemy.exc.ProgrammingError: (psycopg2.ProgrammingError) syntax error at or near "ON"
LINE 1: ...ty_value) VALUES ('crowd', 'build.number', '216') ON CONFLIC...
^
[SQL: "INSERT INTO cwd_property(property_key,property_name,property_value) VALUES ('crowd', 'build.number', '216') ON CONFLICT DO NOTHING"]
2017-11-13 08:17:47,852 ERROR Couldn't initialize the database
Traceback (most recent call last):
File "/opt/atlassian/hipchat/sbin/_datacenter.py", line 105, in set_database
subprocess.check_call(params, stdout=out, stderr=err)
File "/usr/lib/python2.7/subprocess.py", line 540, in check_call
raise CalledProcessError(retcode, cmd)
CalledProcessError: Command '['/opt/atlassian/hipchat/sbin/create_db_schema.sh', u'hipchat', u'secret', u'10.1.98.51', u'5432', u'hipchatdb']' returned non-zero exit status 1
In 3.1.1 and new versions, you may receive the following error in the setup wizard:
Failed to create the HipChat schema
The following may be found in the /var/log/hipchat/web.log
"sqlalchemy.exc.ProgrammingError: (psycopg2.ProgrammingError) syntax error at or near \"ON\"","LINE 1: ...ty_value) VALUES ('crowd', 'build.number', '216') ON CONFLIC..."," ^"," [SQL: \"INSERT INTO cwd_property(property_key,property_name,property_value) VALUES ('crowd', 'build.number', '216') ON CONFLICT DO NOTHING\"]"]
Diagnosis
The error occurs when the following Postgres statement is being executed:
INSERT INTO cwd_property(property_key,property_name,property_value) VALUES ('crowd', 'build.number', '216') ON CONFLICT DO NOTHINGThe error is with the ON CONFLICT clause specifically:
sqlalchemy.exc.ProgrammingError: (psycopg2.ProgrammingError) syntax error at or near "ON"
LINE 1: ...ty_value) VALUES ('crowd', 'build.number', '216') ON CONFLIC...Cause
The root cause is running a Postgres Server older than 9.5. The ON CONFLICT clause was introduced in Postgres 9.5.
In our Postgres database requirements, we state that 9.5 is the only supported version:
Deploy a Postgres instance using version 9.5.
Resolution
Upgrade your Postgres Server to 9.5 or deploy a new one. Refer to the official Postgres installation guide or How to set up a basic PostgreSQL database for Hipchat Data Center for more information.