Squash upgrade from v9.0.1 to v10.1.0 fails with liquibase error

[cat_z]

Hi,

We have a Squash TM v9.0.1 running in a Kubernetes environment, using PostgreSQL 16.9 as the backend. We are attempting to upgrade to Squash TM v10.1.0, but the process fails with a Liquibase error and the application does not start.

The upgrade methods we tried:
1. Followed the database version upgrade guide with squash.db.update-mode=forced(https://tm-en.doc.squashtest.com/v9/ins ... sions.html). Squash failed to start with v10.1.0; pod logs showed database upgrade failure with Liquibase error.

2. Tried incremental upgrade path: 9.0.1 → 9.0.2 → 9.0.3 with squash.db.update-mode=forced, this worked fine.
But going from 9.0.3 → 10.1.0 failed with the same Liquibase error as above.

3. Repeated the above process with squash.db.update-mode=only, same result as before.

I have attached the complete error log also.

If anyone has faced a similar migration issue or knows a different method to go from 9.0.x to 10.1.0 for PostgreSQL, your guidance would be greatly appreciated.

[xdevroedt]

Hello,

Thank you for sharing the details of your issue. Based on similar cases we have encountered in the past, this problem may occur when an iteration is linked to more than one campaign.

To check if this is the case in your database, you can run the following query on your production database.:

Code : Tout sélectionner

SELECT ci.ITERATION_ID, i.NAME,  ci.CAMPAIGN_ID, c.NAME
FROM CAMPAIGN_ITERATION ci
JOIN ITERATION i on ci.ITERATION_ID = i.ITERATION_ID
JOIN CAMPAIGN_LIBRARY_NODE c on ci.CAMPAIGN_ID = c.CLN_ID
WHERE ci.ITERATION_ID IN (
    SELECT ci2.ITERATION_ID
    FROM CAMPAIGN_ITERATION ci2
    JOIN CAMPAIGN_LIBRARY_NODE cln ON ci2.CAMPAIGN_ID = cln.CLN_ID
    JOIN PROJECT p ON cln.PROJECT_ID = p.PROJECT_ID
    GROUP BY ci2.ITERATION_ID
    HAVING COUNT(*) > 1
);

If this query returns any results, it means there are iterations attached to multiple campaigns, which is not expected and can cause the upgrade script to fail.

• If this is the case, please send us the query response so that we can determine together what actions to take

Best regards,
Xavier Squash Support Team

[cat_z]

Hi Xavier,

Thank you for your quick response, and apologies for my delay in following up.
I ran the query you suggested, and here are the results:

Code : Tout sélectionner

iteration_id |               name                | campaign_id |        name         
--------------+-----------------------------------+-------------+---------------------
          178 | 09/01/2025 IWBDEF-69, 70, 71 & 05 |          97 | End-to-end Test run
          178 | 09/01/2025 IWBDEF-69, 70, 71 & 05 |         143 | Retesting
(2 rows)

Please let me know the next steps you would recommend based on these results.

[xdevroedt]

Hello,
In your case, the iteration (ID 178) named “09/01/2025 IWBDEF-69, 70, 71 & 05” is attached to two campaigns (ID 97 and 143).
What you need to do:

• Determine which single campaign this iteration should be attached to

• On a pre-production database (or make sure you have at least one dump of your production database), run the following query:

Code : Tout sélectionner

delete from CAMPAIGN_ITERATION
       where iteration_id=178
         and campaign_id=XXX;

• Where XXX is the ID of the unwanted campaign

• Run the following query again and check that no results are returned:

Code : Tout sélectionner

SELECT ci.ITERATION_ID, i.NAME,  ci.CAMPAIGN_ID, c.NAME
FROM CAMPAIGN_ITERATION ci
JOIN ITERATION i on ci.ITERATION_ID = i.ITERATION_ID
JOIN CAMPAIGN_LIBRARY_NODE c on ci.CAMPAIGN_ID = c.CLN_ID
WHERE ci.ITERATION_ID IN (
    SELECT ci2.ITERATION_ID
    FROM CAMPAIGN_ITERATION ci2
    JOIN CAMPAIGN_LIBRARY_NODE cln ON ci2.CAMPAIGN_ID = cln.CLN_ID
    JOIN PROJECT p ON cln.PROJECT_ID = p.PROJECT_ID
    GROUP BY ci2.ITERATION_ID
    HAVING COUNT(*) > 1
);

• If this is the case, you can try updating to Squash 10.X or v11.0.0, which is currently available.

Best regards,
Xavier, Squash Support Team

[cat_z]

Hi Xavier,

Thank you for your earlier guidance. I followed your steps and was able to clean up the database as advised.

After that, I attempted to upgrade our Squash instance to v10.1.0, but unfortunately, the upgrade failed again. I have attached the complete error log for your reference.

Could you please take a look and guide me on how to proceed from here?

Thanks again for your support.

[xdevroedt]

Hello,
Indeed, according to the logs sent, there are not only duplicate iterations but also executions attached to several iteration plans.

Can you send us the result of this query to run on your database?

Code : Tout sélectionner

SELECT
    STRING_AGG(ITERATION.ITERATION_ID::TEXT, ', ' ORDER BY ITERATION.ITERATION_ID) as iteration_ids,
    STRING_AGG(ITERATION.NAME::TEXT, ', ' ORDER BY ITERATION.ITERATION_ID) as iteration_ids,
    STRING_AGG(ITEM_TEST_PLAN_LIST.ITEM_TEST_PLAN_ID::TEXT, ', ' ORDER BY ITEM_TEST_PLAN_LIST.ITEM_TEST_PLAN_ID) as tpi_ids,
    STRING_AGG(ITERATION_TEST_PLAN_ITEM.LABEL::TEXT, ', ' ORDER BY ITERATION_TEST_PLAN_ITEM.ITEM_TEST_PLAN_ID) as tpi_labels,
    STRING_AGG(ITEM_TEST_PLAN_EXECUTION.EXECUTION_ORDER::TEXT, ', ' ORDER BY ITEM_TEST_PLAN_EXECUTION.EXECUTION_ORDER) as execution_orders,
    STRING_AGG(EXECUTION.LAST_EXECUTED_ON::TEXT, ', ' ORDER BY EXECUTION.EXECUTION_ID) as last_executed_on,
    ITEM_TEST_PLAN_EXECUTION.EXECUTION_ID,
    COUNT(*) as duplicate_count
FROM ITEM_TEST_PLAN_EXECUTION
LEFT JOIN EXECUTION on ITEM_TEST_PLAN_EXECUTION.EXECUTION_ID = EXECUTION.EXECUTION_ID
LEFT JOIN ITEM_TEST_PLAN_LIST on ITEM_TEST_PLAN_LIST.ITEM_TEST_PLAN_ID = ITEM_TEST_PLAN_EXECUTION.ITEM_TEST_PLAN_ID
LEFT JOIN ITERATION_TEST_PLAN_ITEM on ITERATION_TEST_PLAN_ITEM.ITEM_TEST_PLAN_ID = ITEM_TEST_PLAN_LIST.ITEM_TEST_PLAN_ID
LEFT JOIN ITERATION on ITERATION.ITERATION_ID = ITEM_TEST_PLAN_LIST.ITERATION_ID
GROUP BY ITEM_TEST_PLAN_EXECUTION.EXECUTION_ID
HAVING COUNT(*) > 1;

Can you send us the results of this query run on your database?
Depending on the number of results returned, we will see whether it is easier to delete them using the GUI (safer) or via a query.

Best regards,
Xavier, Squash Support Team

[cat_z]

Hi Xavier,
As requested, I have run the query on our database. But as per my manager’s guidance, the tpi_labels column values have been hashed to protect sensitive data.

Please find attached the resulting file with the query output. Let me know if you need any further details or additional outputs to help determine the next steps. Thank you.

[xdevroedt]

Hello,
Our developers are working on a corrective script. As your database is particularly damaged, we were expecting only a few duplicates, not 2,000 lines of returns.
In the meantime, could you please tell me which version your database was created in and whether any significant changes have been made to it?

Best regards,
Xavier, Squash Support Team

[xdevroedt]

Hello,
Here is the query that should correct the numerous duplicates in your database:

Code : Tout sélectionner

DELETE FROM ITEM_TEST_PLAN_EXECUTION
WHERE ctid NOT IN (
    SELECT ctid
    FROM (
             SELECT ctid,
                    ROW_NUMBER() OVER (
                        PARTITION BY EXECUTION_ID, ITEM_TEST_PLAN_ID
                        ORDER BY ctid
                        ) as rn
             FROM ITEM_TEST_PLAN_EXECUTION
         ) t
    WHERE rn = 1
);

We recommend that you run it in a pre-production environment, or at least make a dump of your production database.
Run the query again to check that no duplicates are detected:

Code : Tout sélectionner

SELECT
    STRING_AGG(ITERATION.ITERATION_ID::TEXT, ', ' ORDER BY ITERATION.ITERATION_ID) as iteration_ids,
    STRING_AGG(ITERATION.NAME::TEXT, ', ' ORDER BY ITERATION.ITERATION_ID) as iteration_ids,
    STRING_AGG(ITEM_TEST_PLAN_LIST.ITEM_TEST_PLAN_ID::TEXT, ', ' ORDER BY ITEM_TEST_PLAN_LIST.ITEM_TEST_PLAN_ID) as tpi_ids,
    STRING_AGG(ITERATION_TEST_PLAN_ITEM.LABEL::TEXT, ', ' ORDER BY ITERATION_TEST_PLAN_ITEM.ITEM_TEST_PLAN_ID) as tpi_labels,
    STRING_AGG(ITEM_TEST_PLAN_EXECUTION.EXECUTION_ORDER::TEXT, ', ' ORDER BY ITEM_TEST_PLAN_EXECUTION.EXECUTION_ORDER) as execution_orders,
    STRING_AGG(EXECUTION.LAST_EXECUTED_ON::TEXT, ', ' ORDER BY EXECUTION.EXECUTION_ID) as last_executed_on,
    ITEM_TEST_PLAN_EXECUTION.EXECUTION_ID,
    COUNT(*) as duplicate_count
FROM ITEM_TEST_PLAN_EXECUTION
LEFT JOIN EXECUTION on ITEM_TEST_PLAN_EXECUTION.EXECUTION_ID = EXECUTION.EXECUTION_ID
LEFT JOIN ITEM_TEST_PLAN_LIST on ITEM_TEST_PLAN_LIST.ITEM_TEST_PLAN_ID = ITEM_TEST_PLAN_EXECUTION.ITEM_TEST_PLAN_ID
LEFT JOIN ITERATION_TEST_PLAN_ITEM on ITERATION_TEST_PLAN_ITEM.ITEM_TEST_PLAN_ID = ITEM_TEST_PLAN_LIST.ITEM_TEST_PLAN_ID
LEFT JOIN ITERATION on ITERATION.ITERATION_ID = ITEM_TEST_PLAN_LIST.ITERATION_ID
GROUP BY ITEM_TEST_PLAN_EXECUTION.EXECUTION_ID
HAVING COUNT(*) > 1;

, then try upgrading to v10.X again.

Best regards,
Xavier, Squash Support Team

[cat_z]

Hi Xavier,

Apologies for the delay in getting back to you. I tried the queries you provided, and they worked perfectly. I was able to bring up Squash on version 10.1.0 without any further errors.

Thank you very much for your guidance and support on this, it was really helpful in resolving the migration issue.