Cannot create SSH keys with a MySQL database after exporting and reimporting via mysqldump

症状

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-stash.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

診断

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 Stash database and scanning for the matching name:

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

Next describe the table and look for the id column. If your Stash 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    |       |
| ...     | ...         | ...  | ...     | ...     | ...   |
+---------+-------------+------+---------+---------+-------+

原因

If you dumped your Stash 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 Stash data via mysql the tables will be reconstructed and will lack the auto_increment modifier.

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

回避策

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

ソリューション

• To fix this problem after having re-imported a Stash 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 Stash tables and the tables of plugins that are shipped with Stash.

• 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.