Detect defaults drift after guarded database creation
The isolated fixture prepares cxdbmslabexisting. Attempt CREATE DATABASE IF NOT EXISTS with utf8mb4 and utf8mb4unicodeci, then compare the actual catalog defaults with requesteddefaults. Return databasename and defaultsmatch as 1 only when both defaults match. Do not alter the existing database or remove it. The fixture setup deliberately selects the initial defaults for each case.
MySQL dialect.
sqlCREATE TABLE requested_defaults (character_set_name VARCHAR(40) NOT NULL, collation_name VARCHAR(80) NOT NULL); CREATE DATABASE IF NOT EXISTS cx_dbms_lab_existing; ALTER DATABASE cx_dbms_lab_existing CHARACTER SET latin1 COLLATE latin1_swedish_ci; INSERT INTO requested_defaults VALUES ('utf8mb4','utf8mb4_unicode_ci');
Required ordering: Exactly one comparison row.
Edge cases:
- Existing latin1 defaults remain a mismatch.
- Already matching defaults yield 1.
CREATE DATABASE IF NOT EXISTS cx_dbms_lab_existing; ALTER DATABASE cx_dbms_lab_existing CHARACTER SET latin1 COLLATE latin1_swedish_ci; INSERT INTO requested_defaults VALUES ('utf8mb4','utf8mb4_unicode_ci');database_name defaults_match cx_dbms_lab_existing 0
Explanation: Guarded creation preserves existing defaults; the report exposes agreement rather than silently changing an existing object.
- Administrative lab only; fixed disposable database name.
- The solution must not repair or remove an existing database.
Hint 1
IF NOT EXISTS does not change existing defaults.
Hint 2
Compare both observed attributes rather than only the database name.
Guarded creation preserves existing defaults; the report exposes agreement rather than silently changing an existing object.
Guarded creation preserves existing defaults; the report exposes agreement rather than silently changing an existing object.
Related Problems
- Create a disposable database with explicit text defaultsEasy · sqlSolve Problem
- Duplicate EnrollmentsMedium · sqlSolve Problem
- Employees With No Real DepartmentMedium · sqlSolve Problem