db-sync fails to sync SSHPublicKeys

[geropl]

(internal discussion)

For unknown reasons db-sync refuses to insert public SSH keys. We have to find the offending query (e.g. by using staging) and understand what MySQL does not like about it.)

[loujaybee]

Thanks, Gero. I previously observed that I needed to restart workspace after uploading an SSH key for the connection to establish, I was guessing at the time that it could be a DB sync issue. Might be related.

[geropl]

I previously observed that I needed to restart workspace after uploading an SSH key for the connection to establish, I was guessing at the time that it could be a DB sync issue.

That sounds unrelated. Here the effect would be: If I connect to US cluster and store a secret, I cannot use it in the EU cluster.

[akosyakov]

@mustard-mh I don't see

gitpod/components/gitpod-db/src/typeorm/entity/db-code-sync-resource.ts

Line 47 in cf7d6de

@Index("ind_dbsync", ["created"]) // DBSync

on DBUserSshPublicKey

@geropl Is it not necessary anymore?

[geropl]

@geropl Is it not necessary anymore?

It's not technical necessary, but good practice to try to keep TS and SQL in sync.

[akosyakov]

@geropl @mustard-mh Can we verify somehow that all necessary db sync indexes are in place in production database?

[andrew-farries]

The error lies in the sql generated here:

gitpod/components/ee/db-sync/src/export.ts

Lines 143 to 145 in a75d168

	if(!forceInsert) {
	result.push(`UPDATE ${this.table.name} SET ${updates} WHERE ${updateConditions};`);
	}

Where we are not properly escaping the column names, which causes an error for the key column as it's a SQL keyword.