roachtest: backup-restore/online-restore failed [schema change workload created a duplicate constraint]

[cockroach-teamcity]

roachtest.backup-restore/online-restore failed with artifacts on release-24.1 @ 41e76d2515efb83cd76c7645f1692ac516ebd4b8:

(monitor.go:149).Wait: monitor failure: schema change workload failed: full command output in run_070613.173544280_n4_COCKROACHRANDOMSEED4.log: COMMAND_PROBLEM: exit status 1 [owner=sql-foundations]
test artifacts and logs in: /artifacts/backup-restore/online-restore/run_1

Parameters:

• arch=amd64
• cloud=gce
• coverageBuild=false
• cpu=4
• encrypted=false
• fs=ext4
• localSSD=true
• runtimeAssertionsBuild=false
• ssd=0

Help

See: roachtest README

See: How To Investigate (internal)

See: Grafana

Same failure on other branches

• roachtest: backup-restore/online-restore failed #138026 roachtest: backup-restore/online-restore failed [A-disaster-recovery C-test-failure O-roachtest O-robot T-disaster-recovery branch-release-24.3 release-blocker]

/cc @cockroachdb/sql-foundations

This test on roachdash | Improve this report!

Jira issue: CRDB-46167

[rafiss]

{
 "workerId": 4,
 "clientTimestamp": "07:06:38.886795",
 "ops": [
  "BEGIN",
  {
   "sql": "ALTER TABLE schema_w4_125.table_w1_139 ADD CONSTRAINT \"table_w4_127_c'Ol127_w4_128_table_w1_139_col139_w1_145_fk\" FOREIGN KEY (col139_w1_145) REFERENCES schema_w3_24.table_w4_127 (\"c'Ol127_w4_128\") ON DELETE CASCADE ON UPDATE CASCADE",
   "potentialExecErr": "23503"
  }
 ],
 "expectedExecErrors": "",
 "expectedCommitErrors": "",
 "message": "***UNEXPECTED ERROR; Received an unexpected execution error.: ERROR: duplicate constraint name: \"table_w4_127_c'Ol127_w4_128_table_w1_139_col139_w1_145_fk\" (SQLSTATE 42710)",
 "errorState": {
  "potentialErrors": [
   "23503"
  ],
  "potentialCommitErrors": [
   "23503",
   "42P01"
  ],

Looking earlier in the logs, there indeed was a constraint added with this name already, so this is a workload bug. The two operations were 2 seconds apart, so maybe the logic to detect existing constraints is racy.

{
 "workerId": 0,
 "clientTimestamp": "07:06:36.345261",
 "ops": [
  "BEGIN",
  {
   "sql": "ALTER TABLE schema_w4_125.table_w1_139 ADD CONSTRAINT \"table_w4_127_c'Ol127_w4_128_table_w1_139_col139_w1_145_fk\" FOREIGN KEY (col139_w1_145) REFERENCES schema_w3_24.table_w4_127 (\"c'Ol127_w4_128\") ON DELETE CASCADE ON UPDATE CASCADE",
   "potentialExecErr": "23503"
  },
  "COMMIT"
 ],
 "expectedExecErrors": "",
 "expectedCommitErrors": "",
 "message": ""
}