9 Common Pitfalls of SQL Scripts During System Deployment and How to Avoid Them

Introduction

Deploying a system to production often leads to unexpected issues, even if the same SQL scripts have been run successfully in a test environment. Small teams may let developers execute scripts directly, while larger organizations rely on DBAs, but mistakes still happen, such as executing updates in the wrong order.

1. Missing Scripts

The most frequent problem is forgetting to include a script, which can manifest as omitted fields, unchanged comments, uncreated tables, or wrong column types. The recommended solution is to treat SQL scripts as part of the codebase: create a dedicated sql directory, organize sub‑folders per iteration (e.g., mv3.2.1 ), and store the scripts there. All changes to tables—adding columns, modifying types, adding indexes, creating tables—should be captured in SQL statements and committed to GitLab, then retrieved for both test and production releases.

2. Script Syntax Errors

Even scripts that passed testing can contain syntax errors if they are not written according to standards. For example, adding multiple columns with separate ALTER statements may be collapsed into a single statement, but a missing comma can break the script. The safe practice is to always execute the script in a development database before submitting it to the DBA, never pushing directly to production.

alter table t_user add column `work` varchar(30) DEFAULT NULL COMMENT '工作';
alter table t_user add column `provice` varchar(10) DEFAULT NULL COMMENT '籍贯';

alter table t_user add column `work` varchar(30) DEFAULT NULL COMMENT '工作',
add column `provice` varchar(10) DEFAULT NULL COMMENT '籍贯';

3. Incorrect Script Order

If scripts are applied in the wrong sequence, data may be inconsistent. For instance, inserting a row before another team updates it can cause the update to affect zero rows. The fix is to coordinate with teammates and merge dependent scripts so that initialization and subsequent updates occur in the proper order.

INSERT INTO `sue`.`t_user`(`id`,`code`,`age`,`name`,`height`,`address`,`work`,`provice`) VALUES (1,'101',25,'周星驰',173,'香港',NULL,NULL);

4. Wrong Execution Timing

Distinguish between pre‑production (pre) and production (prod) environments. Some scripts, such as those altering column types or inserting new enum values, should only run in prod. Use naming conventions like a prod_ prefix on change‑request tickets to avoid accidental execution in the wrong environment.

5. Selecting the Wrong Database

In sharded or backup database setups, it is easy to submit a script to the wrong database. Adding the schema name (e.g., sue.t_user ) to the statements helps ensure the correct target.

alter table sue.t_user add column `work` varchar(30) DEFAULT NULL COMMENT '工作';

6. Long‑Running Scripts

Bulk updates on large tables can lock the table for minutes, causing timeouts and deadlocks. Schedule such operations during low‑traffic periods (e.g., early morning) and split them into smaller batches.

update user set status=0 where status=1;

7. Non‑Rollbackable Scripts

If a deployment fails, both code and database changes must be rolled back. Include reversible information, such as timestamps, or use temporary tables to record affected rows, making rollback easier.

update t_user set age=25, time=now(3) where id=1;

8. Missing Indexes

After adding a new column, especially foreign‑key IDs, forgetting to create an index can cause full table scans and severe performance degradation. Always evaluate whether an index is needed and add it before deployment.

9. Renaming Columns

Changing column names in production is risky because the code may still reference the old name, leading to runtime errors. Avoid renaming columns directly in prod; instead, add a new column, migrate data, and deprecate the old one.

Beyond these script‑related pitfalls, other deployment issues include failed releases, branch merges, MQ messages consumed in pre, and unrecoverable rollbacks.