--- title: "Inconsistencies between system catalog and DocDB schema during DDL operations" url: "https://docs.yugabyte.com/stable/releases/techadvisories/ta-22802/" --- # Inconsistencies between system catalog and DocDB schema during DDL operations DDL operations may cause inconsistencies between the PostgreSQL system catalog and the underlying DocDB schema. 11 June 2024 | Product | Affected Versions | Related Issues | Fixed In | |---------------------------|------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------|--------------------------------------------------------------------------------------------------------------------------------|---------------------------------------------------------------| | [YSQL](/stable/api/ysql/) | [v2.14.0.0](/stable/releases/ybdb-releases/end-of-life/v2.14/#v2.14.0.0 "v2.14.0.0"), [v2.16.0.0](/stable/releases/ybdb-releases/end-of-life/v2.16/#v2.16.0.0 "v2.16.0.0"), [v2.18.0.0](/stable/releases/ybdb-releases/end-of-life/v2.18#v2.18.0.0), [v2.20.0.0](/stable/releases/ybdb-releases/end-of-life/v2.20#v2.20.0.0) | [#22802](https://github.com/yugabyte/yugabyte-db/issues/22802), [#16712](https://github.com/yugabyte/yugabyte-db/issues/16712) | [v2024.1](/stable/releases/ybdb-releases/end-of-life/v2024.1) | ## Description When DDL operations are executed, the database writes metadata to both the PostgreSQL system catalog and DocDB schema. If a DDL fails, in certain specific scenarios, the PostgreSQL system catalog changes are rolled back but the DocDB schema changes are not, causing inconsistency between the PostgreSQL system catalog and DocDB schema. Issues arising from inconsistency between the PostgreSQL system catalog and DocDB schema may include the following: - Inconsistent backup When ALTER TABLE operations cause inconsistency between the PostgreSQL system catalog and DocDB schema, subsequent backups can't be restored. DML operations on the table are unaffected. Restoring backups taken after the failed DDL fail with the error "YSQL table not found: <table\_name>\_temp\_old". - Failure to add column Failed ALTER TABLE ADD COLUMN operations roll back changes in the PostgreSQL system catalog but do not roll back the DocDB schema. This can result in subsequent ALTER TABLE ADD COLUMN operations with the same column name failing with the error "The column already exists". - Orphan tables Failed DDL operations can cause tables to be present in DocDB but not in the PostgreSQL system catalog. These tables are not accessible using the YSQL API but do not interfere with any future operations. Affected tables are listed in the YugabyteDB Anywhere UI, and can only be deleted manually. ## Mitigation ### Detection You can use the following script (available for Python 2 and 3) to check for potential issues. Copy the script to the [YB-Master leader](/stable/architecture/yb-master/#table-creation "YB-Master leader") node and execute it locally on the same node. 1. Sign in to the YB-Master leader node and use one of the following commands to download the appropriate version of the script. Python 3: ```sh wget https://raw.githubusercontent.com/yugabyte/yb-tools/main/ddl_atomicity/ddl_atomicity_check_script_python3.py ``` Python 2: ```sh wget https://raw.githubusercontent.com/yugabyte/yb-tools/main/ddl_atomicity/ddl_atomicity_check_script_python2.py ``` 2. Execute the script using one of the following commands. Python 3: ```python python3 ddl_atomicity_check_script_python3.py ``` Python 2: ```python python2 ddl_atomicity_check_script_python2.py ``` The script takes the following optional arguments: | Argument | Description | |---------------------------------------------------------|-----------------------------------------------------------------------------------| | -h, --help | Command line help. | | --ysqlsh\_path YSQLSH\_PATH | Path to ysqlsh executable. | | --master\_conf\_path MASTER\_CONF\_PATH | Path to master configuration file. | | --master\_interface\_address MASTER\_INTERFACE\_ADDRESS | Master UI interface IP. If not provided, it will be read from master\_conf\_path. | | --master\_interface\_port MASTER\_INTERFACE\_PORT | Port for the master UI interface. | | --ysql\_host YSQL\_HOST | Host for ysqlsh. | | --master\_leader\_only | Check if the node is the master leader and exit if it is not. | | --curl\_path CURL\_PATH | Path to curl executable. | | --grep\_path GREP\_PATH | Path to grep executable. | | --awk\_path AWK\_PATH | Path to awk executable. | The script returns one of the following messages when it detects an issue: - Inconsistent backup ```text Table with oid and uuid exists in but has a mismatched table name - TABLE NAME NEEDS TO BE FIXED ``` - Failure to add column ```text Column does not exist in table in database - ORPHANED COLUMN NEEDS TO BE DROPPED ``` - Orphan tables ```text Table with oid and uuid does not exist in database - ORPHANED TABLE NEEDS TO BE DROPPED ``` ### Issue mitigaton #### Inconsistent backup **Option 1**: Take a new backup using the following steps: 1. Rename the table for which restore is failing as follows: ```sql ALTER TABLE RENAME TO _temp_old; ALTER TABLE _temp_old RENAME TO ; ``` The first RENAME is required to align the PostgreSQL system catalog entries with the DocDB schema. The second RENAME renames the table to the original intended name. This step must be repeated for each table for which the issue has been reported. 2. Take a new backup. Restores of subsequent backups should be successful. **Option 2**: Modify an already taken backup if an existing backup requires a fix as follows: 1. Modify the schema dump taken by [ysql\_dump](/stable/admin/ysql-dump/ "ysql_dump") by changing all references to `` with `_temp_old` for all the tables for which failed restore was previously reported. 2. After restoring the backup, rename the table to its intended name. ```sql ALTER TABLE _temp_old RENAME TO ; ``` Contact [Yugabyte Support](https://support.yugabyte.com/ "Yugabyte Support") if you need additional help fixing the backup schema file. #### Failure to Add Column Contact [Yugabyte Support](https://support.yugabyte.com/ "Yugabyte Support") to resolve the issue. #### Orphan tables Contact [Yugabyte Support](https://support.yugabyte.com/ "Yugabyte Support") to resolve the issue. ## Details ALTER TABLE operations such as adding primary key (ALTER TABLE ADD PRIMARY KEY), or changing column type (ALTER TABLE ALTER COLUMN TYPE) require rewriting the table. To do this, ALTER TABLE operations rename the table to `_temp_old`, create a new table, and then drop the old one. If this operation fails after the rename, the PostgreSQL system catalog will be rolled back; however, the associated DocDB table will have the name `_temp_old`. If a backup is taken on a database in this state, when that backup is restored, the PostgreSQL system catalog refers to the table using the original name, while the DocDB schema refers to the table using the name `_temp_old`, causing inconsistency and restore operations to fail with the error "YSQL table not found: <table\_name>\_temp\_old". DML operations on the table continue normally as DML operations refer to the table by OID, not by name. When ADD COLUMN operations fail, the DocDB schema for newly added columns is not rolled back, so subsequent ADD COLUMN operations fail with the error message "The column already exists".