Migrating Bitbucket Server database to MySQL - Unknown column 'closed_timestamp' in 'field list'

[Darren Pickering]

We’re trying to get our server version of Bitbucket (currently v4.8.1) onto the latest update. However our version of MSSQL is no longer supported (2008R2).

The plan then was to migrate the database to a supported version of MySQL and once this was done then update Bitbucket.

However when trying to migrate to MySQL 5.6.16 we are getting the following error:

Bitbucket could not be migrated to the new database. Some data may have already been written to the new database leaving it in an inconsistent state. You will need to empty or recreate the new database before trying again.        

   com.atlassian.stash.internal.maintenance.migration.DatabaseMigrationTask.convertToMigrationException(DatabaseMigrationTask.java:86)         com.atlassian.stash.internal.maintenance.migration.DatabaseMigrationTask.run(DatabaseMigrationTask.java:51)         com.atlassian.stash.internal.maintenance.DefaultMaintenanceTaskMonitor.run(DefaultMaintenanceTaskMonitor.java:212)         ...  

Failed to execute change: Insert Row; nested exception is com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: Unknown column 'closed_timestamp' in 'field list'         com.atlassian.stash.internal.backup.liquibase.DefaultLiquibaseDao.insert(DefaultLiquibaseDao.java:307)         com.atlassian.stash.internal.backup.liquibase.DatabaseUpdater.onChangesetContent(DatabaseUpdater.java:63)         com.atlassian.stash.internal.backup.liquibase.ChangeSetReader.endElement(ChangeSetReader.java:72)         ...  

Unknown column 'closed_timestamp' in 'field list'         sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method)         sun.reflect.NativeConstructorAccessorImpl.newInstance(Unknown Source)         sun.reflect.DelegatingConstructorAccessorImpl.newInstance(Unknown Source)         ...

[Jeff Thomas · Accepted solution]

Hi Darren,

The closed_timestamp column did not exist until an upgrade to Bitbucket Server 4.10. So, it appears that at some point, your MSSQL database was upgraded to at least 4.10. On the first start of a new version, all of the DB upgrade tasks will be run. In order to roll back to a previous version, you must restore the DB and file system to a state before the upgrade.

Do you recall which version that you may have run against the database?

If not, run the following SQL and provide atleast the first line, we'll be able to identify the version from it.

select * from databasechangelog order by dateexecuted desc;

If the highest version that was upgraded to was 4.14 or below, we can do the following:

1. Upgrade to the highest version that was run, ie 4.14, as MSSQL 2008 is still supported (although deprecated)
2. Migrate to MySQL and you should not receive any errors

Just in case you haven't seen yet, we typically don't recommend MySQL for larger instances due to performance issues, more information is available on Connecting Bitbucket Server to MySQL.

[Darren Pickering]

Hi Jeff,

as in attached photo it looks like database is on v4.10. I'll give it a go upgrading to this version on server then trying migration again after weekend / bank holiday.

I had seen the info about performance with MySQL so may review options again before I do this.

Thanks for your help so far

[Jeff Thomas]

Sounds good, Darren. Let us know how the upgrade to 4.10 and migration go.

[Darren Pickering]

Hi Jeff,

after the upgrade everything worked as it should.

We've stuck with MySQL for the moment and will see how that performs. Can always migrate again if it becomes an issue.

Thanks again for you help

[Jeff Thomas]

Great to hear, Darren!

[Ana Retamal]

Hi Darren, it looks like the target database is not empty. Can you try dropping it and then recreate it again? If after that it still fails, we'll need to see logs to get a better understanding of what's happening there.

Cheers!

Ana

[Darren Pickering]

Hi Ana,

it was a newly created database. Have just dropped and recreated to make sure and got the same error.

Which logs do you need, and where do I send them?

Thanks

Darren