Symptoms

You are unable to store new SSH keys for users. Attempting to do so results in a 500 error.

The following appears in the atlassian-bitbucket.log:

com.atlassian.activeobjects.internal.ActiveObjectsSqlException: There was a SQL exception thrown by the Active Objects library:
Database:
	- name:MySQL
	- version:5.5.25
	- minor version:5
	- major version:5
Driver:
	- name:MySQL-AB JDBC Driver
	- version:mysql-connector-java-5.1.22 ( Revision: ${bzr.revision-id} )

java.sql.SQLException: Field 'ID' doesn't have a default value

Diagnosis

To diagnose this problem, you will need to inspect the schema for the public key table. First you will need to find its exact name (this will vary from release to release but will begin with "ao_" and end with "public_key"). You can find this by showing all tables for the Bitbucket Server database and scanning for the matching name:

show tables;
 
+---------------------+
| Tables_in_bitbucket     |
+---------------------+
| ...                 |
| ao_XXXX_public_key  |
| ...                 |
+---------------------+
 

Next describe the table and look for the id column. If your Bitbucket Server instance is suffering from this problem the extra column should be blank and should not contain "auto_increment".

describe ao_XXXX_public_key;


+---------+-------------+------+---------+---------+-------+
| Field   | Type        | Null | Key     | Default | Extra |
+---------+-------------+------+---------+---------+-------+
| ...     | ...         | ...  | ...     | ...     | ...   |
| id      | int(11)     | NO   | PRIMARY | NULL    |       |
| ...     | ...         | ...  | ...     | ...     | ...   |
+---------+-------------+------+---------+---------+-------+

Cause

If you dumped your Bitbucket Server MySQL tables to a file using mysqldump and used the --skip-opt setting, this will generate a SQL script that does not include the auto_increment in create table statements. Subsequently when you reimport your Bitbucket Server data via mysql the tables will be reconstructed and will lack the auto_increment modifier.

mysqldump --skip-opt -p bitbucket > bitbucket.sql
mysql -p bitbucket < bitbucket.sql 

Workaround

Do not use the --skip-opt flag of supplement it with --create-opt if you really must use --skip-opt.

Resolution

• To fix this problem after having re-imported a Bitbucket Server database with the --skip-opt but without the --create-opt arguments, you will need to restore the auto_increment columns.
• Speak with Atlassian support to get a SQL script that will restore all auto_increment columns for all Bitbucket Server tables and the tables of plugins that are shipped with Bitbucket Server.

• Speak with the vendors of any plugins you have installed to find out if their tables' columns need restoring. Typically any plugin that uses ActiveObjects might have an auto_increment column.