[Bug]: Unable to view test cases and execute test cases after upgrading

[snapshotleisure]

Is there an existing issue for this?

• I have searched the existing issues

What is the current behavior?

I am running Testsigma CE in Docker and after upgrading from v2.0.1 to v2.1 I am no longer able to see my test cases. I am also seeing errors in the log for my agent.

Errors are:
2022-09-23 00:43:06 981358AE6F094D7188E9813957868068 ERROR SqlExceptionHelper:logExceptions:142 qtp1586095399-323 Unknown column 'testdevice0_.prerequisite_test_device_result_id' in 'field list'

2022-09-23 00:43:46 EFA106077EF640E4B723D72C3CBAEE12 ERROR SqlExceptionHelper:logExceptions:142 qtp1586095399-462 Unknown column 'testdata0_.passwords' in 'field list'

I can see for V28__test_device.results.sql file has been modified to include a column prerequisite_test_device_result_id, however this change is never reflected in any of the migration script, so it will never get picked up when upgrading.

Also in v17__test_data.sql, the column passwords

What is the expected behavior?

When upgrading between versions of TestSigma, database changes should be picked up as a migration step.

Steps To Reproduce

In docker-compose, run v2.0.1 as the image and upgrade to v2.1

Version

Testsigma Community Edition

[snapshotleisure]

Running the following manual changes in the database will temporarily fix the issue:

use testsigma_opensource;
ALTER TABLE test_device_results ADD COLUMN prerequisite_test_device_result_id BIGINT(20);

use testsigma_opensource;
ALTER TABLE test_data ADD COLUMN passwords json;

[snapshotleisure]

Executing dry run also failed, also found additional columns:

use testsigma_opensource;
ALTER TABLE test_devices ADD COLUMN prerequisite_test_devices_id BIGINT(20);

[snapshotleisure]

use testsigma_opensource;
ALTER TABLE test_devices ADD COLUMN test_lab_type VARCHAR(255);

use testsigma_opensource;
ALTER TABLE test_device_results ADD COLUMN test_lab_type VARCHAR(255);

use testsigma_opensource;
ALTER TABLE test_devices ADD COLUMN workspace_version_id BIGINT(20);

use testsigma_opensource;
ALTER TABLE test_device_results ADD COLUMN workspace_version_id BIGINT(20);

[snapshotleisure]

Still Failing to execute a dry run

[tarun-testsigma]

@snapshotleisure Thanks for bringing it to our notice. We will provide an update regarding the fix by 26th September

[snapshotleisure]

Just for anyone who is having problems, this is the final sql list to resolve the issue, thank you Amit Savyasachi for the assistance.

ALTER TABLE test_devices add COLUMN prerequisite_test_devices_id bigint(20) DEFAULT NULL;

ALTER TABLE test_devices add COLUMN workspace_id bigint(20) DEFAULT NULL;

ALTER TABLE test_device_results add COLUMN prerequisite_test_device_result_id bigint(20) DEFAULT NULL;

ALTER table test_devices add column test_lab_type varchar(255) DEFAULT NULL;

ALTER table test_device_results add column test_lab_type varchar(255) DEFAULT NULL;

ALTER TABLE test_device_results add COLUMN workspace_id bigint(20) DEFAULT NULL;

ALTER table test_device_results add column target_machine bigint(20) DEFAULT NULL;

update test_devices env set workspace_id = (select workspace_version_id from test_plans where env.test_plan_id=id);

update test_device_results env_result set workspace_id = (select workspace_id from test_devices where env_result.test_device_id = id);

update test_devices env set test_lab_type = (select test_lab_type from test_plans where env.test_plan_id=id);

update test_device_results env_result set test_lab_type = (select test_lab_type from test_devices where env_result.test_device_id = id);

update test_device_results env_result set target_machine = (select target_machine from test_devices where env_result.test_device_id = id);

ALTER table test_devices change workspace_id workspace_version_id bigint(20) DEFAULT NULL;

ALTER table test_device_results change workspace_id workspace_version_id bigint(20) DEFAULT NULL;