Uploaded image for project: 'Bitbucket Server'
  1. Bitbucket Server
  2. BSERV-6839

Stash migration to Postgres stops

    XMLWordPrintable

    Details

      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:

      1. Stop Stash
      2. Create a backup of the database
      3. 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>;
      4. Start Stash
      5. Perform the migration

        Attachments

          Activity

            People

            • Votes:
              4 Vote for this issue
              Watchers:
              8 Start watching this issue

              Dates

              • Created:
                Updated:
                Resolved: