mz_internal.mz_aws_connections is missing a row after a Backup+Restore or upgrade about materialize

[philip-stoev]

@moulimukherjee @guswynn @benesch not sure who would be the right person/team to assign this to...

[philip-stoev]

Once it has failed, you can use:

psql -p  `docker port platform-checks-materialized-1 | grep '0.0' | grep 6875 | cut -d':' -f2` -h 127.0.0.1 -U materialize 

to log in the Mz instance and then you can run:

select * from mz_internal.mz_aws_connections;

and it will return 1 row. However,

materialize=> select count(*) from mz_connections where type = 'aws';
 count 
-------
     2
(1 row)

That is, the referential integrity between the two tables has been broken.

I am setting this to a release blocker as this is a form of data corruption with unknown downstream consequences.

[benesch]

Ah, this happens if the materialized instance is restarted without the necessary environment variables:

materialize/misc/python/materialize/checks/mzcompose_actions.py

Lines 44 to 46 in 079fbca

	"MZ_AWS_CONNECTION_ROLE_ARN=arn:aws:iam::123456789000:role/MaterializeConnection",
	"MZ_AWS_EXTERNAL_ID_PREFIX=eb5cb59b-e2fe-41f3-87ca-d2176a495345",
	*environment_extra,

@philip-stoev I suspect I added those environment variables to the wrong spot, and they are not in effect after the checks framework restarts the environment. I could use your help to figure out where I should have added those environment variables.

[philip-stoev]

@benesch oh , let me check that.

[philip-stoev]

@benesch It seems #24114 will do the trick, so I will merge it. However, test issues aside, is it a concern that the referential integrity was broken in this situation?

[benesch]

However, test issues aside, is it a concern that the referential integrity was broken in this situation?

Working as designed! If the AWS-related environment variables are missing on restart, I figured it was better to error! than to panic the whole environmentd and refuse to start up. I made the errors as loud as possible so that they get reported to Sentry in production.

There's no explicit foreign key between mz_connections and mz_aws_connections, so (IMO) not deep concerning correctness issues here. LMK if you disagree!