Inconsistent MySQL settings in preview environments and staging/production

[mads-hartmann]

Bug description

The MySQL explicit_defaults_for_timestamp system variable (docs) is enabled in our preview environments but not staging (and probably not in production, haven't checked)

Having this variable enabled will cause things to break if we ever try to do an insert or update statement where we explicitly insert a NULL value. If the setting is disabled it will use the DEFAULT value of the column even if you insert NULL.

This currently breaks inserts from typescript into the d_b_prebuilt_workspace table as we use the MAP_ISO_STRING_TO_TIMESTAMP_DROP transformer which returns undefined no matter the value, and it appears this gets translated to an insert/update statement with an explicit NULL.

To fix this we should make sure we have the same setting for explicit_defaults_for_timestamp in all environments.

This is related to #4122

Steps to reproduce

You can check the current value of the setting by connecting to MySQL and run the following query:

SELECT @@GLOBAL.explicit_defaults_for_timestamp;

This returns 1, but in staging it returns 0.

Some of our DB tests current fail in the preview environments because of this. If you start a preview environment off main and run the following commands you should get failing tests.

1. Open a workspace off main https://gitpod.io/#https://github.com/gitpod-io/gitpod
2. In one terminal kubectl port-forward statefulset/mysql 23306:3306
3. In another terminal cd components/gitpod-db and yarn db-test

Expected behavior

No response

Example repository

No response

Anything else?

No response

[jankeromnes]

Many thanks @mads-hartmann for the co-op on debugging this!

Another symptom / way to reproduce this problem is to try the gitpod.io/#prebuild/ URL prefix in preview environments (it's currently broken in core-dev and io-dev):

1. Open <any-preview-env>/#prebuild/<any-repo>

2. It immediately fails with: ER_BAD_NULL_ERROR: Column 'creationTime' cannot be null:

---

Here is my current understanding of the differences between core-dev/io-dev and staging/production:

	core-dev and io-dev	staging and production
DB "type"	statefulset/mysql	deployments/db
MySQL version	5.7.34	5.7.33-google
explicit_defaults_for_timestamp	1	0
Affected?	yes	no

[geropl]

Nice find @mads-hartmann ! It definitely makes sense to:

1. verify staging and prod are identical
2. adjust core-dev/io-dev accordingly
   Thx for putting effort in bringing our tests back! 🙏

[geropl]

As I bumped into this today I tried to fix it. The journey:

• tried setting SET GLOBAL explicit_defaults_for_timestamp=0; (commit): does not work, because the mysql chart re-starts the DB after initialization, and the value is back to system default (1).
• sadly SET PERSIST is only introduced in MySQL 8.0. Setting the var at runtime does not help for whatever reason
• The MySQL helm chart we use does not support adding config lines (:facepalm: ); we'd need to copy'n'paste the default and append and have another ConfigMap for this
• Now I'm trying to setting in to CURRENT_TIMESTAMP in the transformer directly. This way we get the same result and do not rely on the non-standard behavior.

[jankeromnes]

Thanks for looking into it @geropl!

• Now I'm trying to setting in to CURRENT_TIMESTAMP in the transformer directly. This way we get the same result and do not rely on the non-standard behavior.

This is (sort of) what I did in 075ee07