Upgrade to 10.3

Hi,
I have 2 envs PROD and TEST.
Prod v10.1 (Docker)
Test - try to upgrade from 12.2 to 10.3 (Docker)
DB sql server 12.0.2000.8 (Azure)

For every upgrade of Prod env, I export DB data from Prod to Test (via SQL Import and Export Withard)
This time I got an error on the import table dbo.snapshots column purget. Prod DB does not have this column so I dropped the column in Test DB. During the DB upgrade SQ tried to create the column but failed:


.s.p.d.m.DatabaseMigrationImpl] Starting DB Migration and container restart
2024-01-11T16:53:37.620841361Z 2024.01.11 16:53:37 INFO  web[][DbMigrations] Executing DB migrations...
2024-01-11T16:53:37.630822653Z 2024.01.11 16:53:37 INFO  web[][DbMigrations] #102020 'Create index 'snapshots_root_component_uuid' in 'snapshots' table'...
2024-01-11T16:53:40.184888032Z 2024.01.11 16:53:40 INFO  web[][DbMigrations] #102020 'Create index 'snapshots_root_component_uuid' in 'snapshots' table': success | time=2563ms
2024-01-11T16:53:40.186624887Z 2024.01.11 16:53:40 INFO  web[][DbMigrations] #102021 'Create 'purged' column in 'snapshots' table'...
2024-01-11T16:53:40.367960194Z 2024.01.11 16:53:40 INFO  web[][DbMigrations] #102021 'Create 'purged' column in 'snapshots' table': success | time=182ms
2024-01-11T16:53:40.369014489Z 2024.01.11 16:53:40 INFO  web[][DbMigrations] #102022 'Populate 'purged' column in 'snapshots' table'...
2024-01-11T16:53:40.414014311Z 2024.01.11 16:53:40 INFO  web[][DbMigrations] #102022 'Populate 'purged' column in 'snapshots' table': success | time=45ms
2024-01-11T16:53:40.415230920Z 2024.01.11 16:53:40 INFO  web[][DbMigrations] #102023 'Make 'purged' column not nullable in 'snapshots' table'...
2024-01-11T16:53:40.445350212Z 2024.01.11 16:53:40 ERROR web[][DbMigrations] #102023 'Make 'purged' column not nullable in 'snapshots' table': failure | time=30ms
2024-01-11T16:53:40.446777539Z 2024.01.11 16:53:40 ERROR web[][DbMigrations] Executed DB migrations: failure | time=2827ms
2024-01-11T16:53:40.451510362Z 2024.01.11 16:53:40 ERROR web[][o.s.s.p.d.m.DatabaseMigrationImpl] DB migration failed | time=2865ms
2024-01-11T16:53:40.453561345Z 2024.01.11 16:53:40 ERROR web[][o.s.s.p.d.m.DatabaseMigrationImpl] DB migration ended with an exception
2024-01-11T16:53:40.453583747Z org.sonar.server.platform.db.migration.step.MigrationStepExecutionException: Execution of migration step #102023 'Make 'purged' column not nullable in 'snapshots' table' failed
2024-01-11T16:53:40.453591148Z 	at org.sonar.server.platform.db.migration.step.MigrationStepsExecutorImpl.execute(MigrationStepsExecutorImpl.java:79)
2024-01-11T16:53:40.453779965Z 	at org.sonar.server.platform.db.migration.step.MigrationStepsExecutorImpl.execute(MigrationStepsExecutorImpl.java:67)
2024-01-11T16:53:40.453790866Z 	at com.google.common.collect.ImmutableList.forEach(ImmutableList.java:422)
2024-01-11T16:53:40.457181769Z 	at org.sonar.server.platform.db.migration.step.MigrationStepsExecutorImpl.execute(MigrationStepsExecutorImpl.java:52)
2024-01-11T16:53:40.457210072Z 	at org.sonar.server.platform.db.migration.engine.MigrationEngineImpl.execute(MigrationEngineImpl.java:54)
2024-01-11T16:53:40.457216172Z 	at org.sonar.server.platform.db.migration.DatabaseMigrationImpl.doUpgradeDb(DatabaseMigrationImpl.java:105)
2024-01-11T16:53:40.457221373Z 	at org.sonar.server.platform.db.migration.DatabaseMigrationImpl.doDatabaseMigration(DatabaseMigrationImpl.java:80)
2024-01-11T16:53:40.457226373Z 	at java.base/java.util.concurrent.ThreadPoolExecutor.runWorker(Unknown Source)
2024-01-11T16:53:40.457231073Z 	at java.base/java.util.concurrent.ThreadPoolExecutor$Worker.run(Unknown Source)
2024-01-11T16:53:40.457235774Z 	at java.base/java.lang.Thread.run(Unknown Source)
2024-01-11T16:53:40.457240474Z Caused by: java.lang.IllegalStateException: Fail to execute ALTER TABLE snapshots ALTER COLUMN purged BIT NOT NULL
2024-01-11T16:53:40.457245475Z 	at org.sonar.server.platform.db.migration.step.DdlChange$ContextImpl.execute(DdlChange.java:106)
2024-01-11T16:53:40.457250075Z 	at org.sonar.server.platform.db.migration.step.DdlChange$ContextImpl.execute(DdlChange.java:86)
2024-01-11T16:53:40.457254676Z 	at org.sonar.server.platform.db.migration.step.DdlChange$ContextImpl.execute(DdlChange.java:128)
2024-01-11T16:53:40.457259376Z 	at org.sonar.server.platform.db.migration.version.v102.MakePurgedColumnNotNullableInSnapshots.execute(MakePurgedColumnNotNullableInSnapshots.java:37)
2024-01-11T16:53:40.457264076Z 	at org.sonar.server.platform.db.migration.step.DdlChange.execute(DdlChange.java:45)
2024-01-11T16:53:40.457268677Z 	at org.sonar.server.platform.db.migration.step.MigrationStepsExecutorImpl.execute(MigrationStepsExecutorImpl.java:75)
2024-01-11T16:53:40.457273377Z 	... 9 common frames omitted
2024-01-11T16:53:40.457277778Z Caused by: com.microsoft.sqlserver.jdbc.SQLServerException: Cannot insert the value NULL into column 'purged', table 'SQProdCopy.dbo.snapshots'; column does not allow nulls. UPDATE fails.
2024-01-11T16:53:40.457291879Z 	at com.microsoft.sqlserver.jdbc.SQLServerException.makeFromDatabaseError(SQLServerException.java:265)
2024-01-11T16:53:40.457296679Z 	at com.microsoft.sqlserver.jdbc.SQLServerStatement.getNextResult(SQLServerStatement.java:1695)
2024-01-11T16:53:40.457301280Z 	at com.microsoft.sqlserver.jdbc.SQLServerStatement.doExecuteStatement(SQLServerStatement.java:920)
2024-01-11T16:53:40.457314481Z 	at com.microsoft.sqlserver.jdbc.SQLServerStatement$StmtExecCmd.doExecute(SQLServerStatement.java:814)
2024-01-11T16:53:40.457319481Z 	at com.microsoft.sqlserver.jdbc.TDSCommand.execute(IOBuffer.java:7685)
2024-01-11T16:53:40.457653611Z 	at com.microsoft.sqlserver.jdbc.SQLServerConnection.executeCommand(SQLServerConnection.java:4048)
2024-01-11T16:53:40.457664112Z 	at com.microsoft.sqlserver.jdbc.SQLServerStatement.executeCommand(SQLServerStatement.java:272)
2024-01-11T16:53:40.457931236Z 	at com.microsoft.sqlserver.jdbc.SQLServerStatement.executeStatement(SQLServerStatement.java:246)
2024-01-11T16:53:40.458031445Z 	at com.microsoft.sqlserver.jdbc.SQLServerStatement.execute(SQLServerStatement.java:787)
2024-01-11T16:53:40.458133754Z 	at com.zaxxer.hikari.pool.ProxyStatement.execute(ProxyStatement.java:94)
2024-01-11T16:53:40.458854019Z 	at com.zaxxer.hikari.pool.HikariProxyStatement.execute(HikariProxyStatement.java)
2024-01-11T16:53:40.459132243Z 	at org.sonar.server.platform.db.migration.step.DdlChange$ContextImpl.execute(DdlChange.java:91)

I checked that the column was created with NULL value so I was unable to set NOT NULL constrain.
Can you advise me, on how to handle such discrepancies in export/import across the versions?

Hi,

You can’t. You need to make sure the versions match between the source and target instances. Then from that baseline, you can test your upgrade.

 
HTH,
Ann

Hi Ann,
Thanks for the reply. I won’t do it again.
It has been worked before, but not this time. It seems version 10.2 has a lot of differences from 10.0 in the DB part.

1 Like