When considering whether to manipulate the Confluence database directly without using the API or the GUI, you should be aware of the following risk and how it can be mitigated.

tl;dr
When moving data by directly manipulating the Confluence database, the length of URLs for Dev/Staging (lower) environments must be as short or shorter than URLs for Production environments.

Scenario:

You need to push data from Production to Dev/Stage/Test (lower tier) environments by manipulating the database directly.

Understanding the problem:

When you write scripts to push data from the Production server to Dev or to Test, the scripts will need to scour the database and rewrite the URLs in the lower tier environment(s). Some URLs are long enough to max-out the 256-byte field limit in the database. The problem is that when the 256-byte limit is exceeded, links break (in the lower-tier) if your Dev/Staging/Test URLs are longer in Dev than in Prod

The impact:

You may not notice the problem right away, but data will be truncated in your database if your Prod URLs are not as long or longer than the URLs used in lower environments.

The nitty gritty details: 

For example, let’s say I insert the following filter on a page in Confluence:
resolution in (Done, Fixed, “Won’t Fix”, Duplicate, “Cannot Reproduce”, “Won’t Do”, Incomplete, Approved, Cancelled, Closed, Delivered, Invalid, “Need More Info”, “Not A Defect”) AND resolved >= 2017-12-01 AND resolved <= 2017-12-12 AND assignee in (user11, user1234, user5678) ORDER BY priority DESC

Confluence creates the following link and stores it in the db:
https://jira.COMPNY.com/issues/?jql=resolution%20in%20(Done%2C%20Fixed%2C%20%22Won%27t%20Fix%22%2C%20Duplicate%2C%20%22Cannot%20Reproduce%22%2C%20%22Won%27t%20Do%22%2C%20Incomplete%2C%20Approved%2C%20Cancelled%2C%20Closed%2C%20Delivered%2C%20Invalid%2C%20%22Need%20More%20Info%22%2C%20%22Not%20A%20Defect%22)%20AND%20resolved%20%3E%3D%202017-11-10%20AND%20resolved%20%3C%3D%202017-11-26%20AND%20assignee%20in%20(user11%2C%20user1234%2C%20user5678)%20ORDER%20BY%20priority%20DESC%20%20

The above link is 484 bytes, but Confluence URL fields only store 256 bytes of data (red). Consequently, the URL is truncated at 256 bytes and the remaining bytes (blue) are copied into another field (that Confluence uses and appends onto the end of the previous field, so everything works in Prod).

The key:

When the rewrite is performed, for all links that are 256 bytes or larger (in Prod), IF the base URLs in Dev are even one character longer than in Prod, data will be lost.

In the example, above, this is the first 256 bytes of data:
https://jira.COMPNY.com/issues/?jql=resolution%20in%20(Done%2C%20Fixed%2C%20%22Won%27t%20Fix%22%2C%20Duplicate%2C%20%22Cannot%20Reproduce%22%2C%20%22Won%27t%20Do%22%2C%20Incomplete%2C%20Approved%2C%20Cancelled%2C%20Closed%2C%20Delivered%2C%20Invalid%2C%20%

We would rewrite the base URL portion of the link:
https://jira.COMPNY.com
with:
https://jira.dev.COMPNY.com
…which would result in the last few (4) bytes of data [%20%] being dropped from the URL, and break the link.

If you remove those few bytes of data from the URL, it’s broken:
https://jira.dev.COMPNY.com/issues/?jql=resolution%20in%20(Done%2C%20Fixed%2C%20%22Won%27t%20Fix%22%2C%20Duplicate%2C%20%22Cannot%20Reproduce%22%2C%20%22Won%27t%20Do%22%2C%20Incomplete%2C%20Approved%2C%20Cancelled%2C%20Closed%2C%20Delivered%2C%20Invalid%2C%20%22Need%20More%20Info%22%2C%20%22Not%20A%20Defect%22)%20AND%20resolved%20%3E%3D%202017-11-10%20AND%20resolved%20%3C%3D%202017-11-26%20AND%20assignee%20in%20(houset%2C%20ayyarrow%2C%20marchmic)%20ORDER%20BY%20priority%20DESC%20%20

Clicking the broken link spawns the following error message in Jira:
Error! Filename not specified.

Here’s an example of an error you might see in the Confluence log (atlassian-confluence.log), or in the SQL insert response:
ERROR:  value too long for type character varying(255)
CONTEXT:  COPY links, line 25892, column destpagetitle: “//jira.dev.COMPNY.com/issues/?jql=project%20%3D%20Devtools%20AND%20resolution%20%3D%20Unresolved%20A…”


How to prevent these types of issues:

  • Don’t manipulate the database directly
  • Make the URLs for the lower environments as short or shorter than the URLs for the Production environment

Cleaning up if you cannot make the lower environment URLs shorter and have to manipulate the database manually:

  • Restore the database to a lower tier
  • Start it up
  • Update the base URL and app links manually in the GUI
    • This manual work is tedious and prone to error.  Be careful to make these changes in the lower environment and not in Production!

Caution:

Do not succumb to the temptation to manually change the column size to compensate.  It will invariably come back to bite you, as the manual change will be reverted when Confluence is upgraded.

**CREDIT: Props to Adam Martin, who provided all of the technical detail for this article.