Questions about database schema and migration

Hi we are trying to run our hosted instance of Gitpod and there are a few questions related to the database definition:

  1. In one of the commits, a bunch of old migration files were deleted and replaced with a new 1592203031938-Baseline.ts. However, there appears to be some differences between the entity class definition and DB schema …

eg.
In db-app-installation.ts, the creationTime is defined with a default of CURRENT_TIMESTAMP(6). In the 1592203031938-Baseline.ts, it is defined as DEFAULT CURRENT_TIMESTAMP(6) ON UPDATE CURRENT_TIMESTAMP(6).

    @Column({
        type: 'timestamp',
        precision: 6,
        default: () => 'CURRENT_TIMESTAMP(6)',
        transformer: Transformer.MAP_ISO_STRING_TO_TIMESTAMP_DROP
    })
    creationTime: string;
CREATE TABLE IF NOT EXISTS d_b_app_installation (  
    platform varchar(255) NOT NULL,  
    installationID varchar(255) NOT NULL,  
    ownerUserID char(36) DEFAULT NULL,  
    platformUserID varchar(255) DEFAULT NULL,  
    state char(36) NOT NULL,  
    creationTime timestamp(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6) ON UPDATE CURRENT_TIMESTAMP(6),  
    lastUpdateTime timestamp(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6) ON UPDATE CURRENT_TIMESTAMP(6),  
    PRIMARY KEY (platform,installationID,state),  
    KEY ind_dbsync (creationTime)
) 
ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

It seems odd to update the creationTime ON UPDATE – is this going to cause any problem(s) with the app logic?

Other similar occurrences in:

  • db-prebuilt-workspace.tscreationTime
  • db-snapshot.tscreationTime

  1. After running the full set of migration files on an older DB (i) and comparing to a DB created using the baseline.ts (ii), the following tables were found in (i) but not in (ii)
  • d_b_team_membership_invite
  • d_b_team_membership
  • d_b_team
  • d_b_project
  • d_b_oauth_auth_code_entry

Are these tables necessary? Or should they be removed?

1 Like

Additional note for #1:
In 1538732744133-InitialSetup.ts, creationTime timestamp(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6) and without ON UPDATE .... I wonder if this is a copy-n-paste error in 1592203031938-Baseline.ts?

@AlexTugarev, as you were one of the committers in both of these files. I am tagging you on this post to see if you can potentially help answer these questions? Thanks.

@charleswhchan, that’s a great catch! Thanks for investigating. Indeed, ON UPDATE CURRENT_TIMESTAMP(6) is bogus for creationTime.

1 Like

The baseline was just a squashed version of the migrations we ran before open-sourcing Gitpod, thus the other migrations should still be applied afterwards to generate the current state of the DB schema.

@AlexTugarev Thanks for confirming the issue for item 1. I see issue #5252 has been created as well.

For item 2: Confirm, after migrating running the migration, the following tables are there – even though (I think) the entity files don’t exist. Are you saying we should just leave them alone?

1 Like