Details
-
Bug
-
Resolution: Fixed
-
Medium
-
3.3.0
Description
Migration to Postgres database is being stopped with this error when NULL characters exist in the database:
Stash 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.run(DatabaseMigrationTask.java:51) com.atlassian.stash.internal.maintenance.DefaultMaintenanceTaskMonitor.run(DefaultMaintenanceTaskMonitor.java:183) java.util.concurrent.Executors$RunnableAdapter.call(Unknown Source) ... Failed to execute change: Insert Row; nested exception is org.postgresql.util.PSQLException: ERROR: invalid byte sequence for encoding "UTF8": 0x00 com.atlassian.stash.internal.backup.liquibase.DefaultLiquibaseDao.insert(DefaultLiquibaseDao.java:274) com.atlassian.stash.internal.backup.liquibase.DatabaseUpdater.endElement(DatabaseUpdater.java:81) org.apache.xerces.parsers.AbstractSAXParser.endElement(Unknown Source) ... ERROR: invalid byte sequence for encoding "UTF8": 0x00 org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorImpl.java:2102) org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:1835) org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:257)
In pgsql.log the following error occurs:
pgsql.log
< 2014-12-02 15:46:46.774 EST >ERROR: invalid byte sequence for encoding "UTF8": 0x00 < 2014-12-02 15:46:46.774 EST >STATEMENT: insert into sta_comment (id, entity_version, author_id, created_timestamp, updated_timestamp, comment_text) values ($1, $2, $3, $4, $5, $6) < 2014-12-02 15:46:46.778 EST >ERROR: current transaction is aborted, commands ignored until end of transaction block
This happens even when the destination database is clean.
Seems to be related with this postgres forum link.
Workaround
Identify which comments have the NULL characters in their text so that we can remove them before the migration with the following query:
select * from sta_comment where comment_text like concat('%', 0x00, '%');
From here, we can replace the NULL characters with empty strings so that the migration can proceed. Take note of the values in the id column from the query above so that we can construct our update queries. Please do the following:
- Stop Stash
- Create a backup of the database
- Run the following query for each comment identified with the query above and replace <COMMENT_ID> with the appropriate value from the id column
update sta_comment set comment_text = replace(comment_text, 0x00, '') where id = <COMMENT_ID>;
- Start Stash
- Perform the migration
Attachments
Issue Links
- links to