cockroachdb / cockroachdb/cockroach
CREATE EXTERNAL CONNECTION IF NOT EXISTS does not skip target validation on existing connection
- Dominant language
- Go
- Stars
- 32.5k
- Forks
- 4.1k
- PR merge metrics
- PR metrics pending
Description
**Describe the problem**
`CREATE EXTERNAL CONNECTION IF NOT EXISTS` performs validation at the specified target even when the connection already exists. If the connection exists, we should abort the command there and not perform any validation. This was observed on google storage and reproduced with nodelocal. When executing the command against an existing connection, the validation portion attempts to create the `crdb_external_storage_location`, attempts to delete it if it already exists, and as a failback creates a new `crdb_external_storage_location` file with what appears to be a random numeric suffix (`crdb_external_storage_location1583415292.tmp` and `crdb_external_storage_location2837093509.tmp` as examples).
**To Reproduce**
1. $ cockroach start-single-node --insecure --listen-addr=localhost:26257 --http-addr=localhost:8080
2. In SQL shell, run `create external connection if not exists permtest as 'nodelocal://1/permtest'`
3. In OS (or other provider) remove ability to delete file like with `chflags uchg cockroach-data/extern/permtest/crdb_external_storage_location` to make the file immutable
4. In SQL shell, run `create external connection if not exists permtest as 'nodelocal://1/permtest'` again
5. Observe error like below:
```
root@localhost:26257/defaultdb> create external connection if not exists permtest as 'nodelocal://1/permtest';
ERROR: failed to construct External Connection details: failed to create nodelocal external connection: failed to write a sentinel ExternalStorage file: closing object: moving temporary file to final location "/path/to/cockroach-data/extern/permtest/crdb_external_storage_location": rename /path/to/cockroach-data/extern/permtest/crdb_external_storage_location4002023838.tmp /path/to/cockroach-data/extern/permtest/crdb_external_storage_location: operation not permitted
```
6. Observe that the .tmp file still exists at `cockroach-data/extern/permtest/crdb_external_storage_location4002023838.tmp`
`chflags nouchg crdb_external_storage_location` reverts the file to normal if this approach was used to make the file immutable.
**Expected behavior**
If the connection already exists, we should abort and perform no operations on the external target.
**Environment:**
- CockroachDB version 24.3.12, but appears to exist from 23.2 through 25.2
- Server OS: All
**Additional context**
This can break automation as the `CREATE EXTERNAL CONNECTION IF NOT EXISTS` should not throw any errors if the connection already exists. Instead, in cases where the target validation fails we still throw an error.
Jira issue: CRDB-51117
Contributor guide
Assessment
This issue has not been assessed yet.