9 Common SQL Script Pitfalls When Deploying to Production and How to Avoid Them

1. Missed Scripts

The most frequent problem is forgetting to include parts of the script, such as new fields, comments, new tables, or type changes. The solution is to treat SQL scripts as code: create a dedicated sql directory in the project, add a sub‑directory for each release (e.g., mv3.2.1), place the scripts there, and commit them to GitLab. This ensures scripts are version‑controlled and easily retrieved for both testing and production deployments.

2. Syntax Errors

Even scripts that passed testing can contain syntax errors when altered for production, such as combining multiple ALTER statements into one and accidentally omitting a comma. Always run the final script against a development database before submitting the DBA work order, and avoid rushing changes 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. Wrong Execution Order

If scripts are applied out of order, data may be missing or updates may affect zero rows. Coordinate with teammates to merge dependent scripts, ensuring initialization and subsequent updates occur in the correct sequence.

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

4. Incorrect Execution Timing

Distinguish between pre‑production (pre) and production (prod) environments. Scripts that modify schema or seed data should be run in the appropriate environment, and work order titles can be prefixed with prod_ to indicate production‑only scripts.

5. Wrong Database Selected

When using sharding, separate databases, or backup instances, explicitly qualify objects with the database name (e.g., sue.t_user) to avoid applying changes to the wrong database.

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

6. Long‑Running Scripts

Mass updates on large tables can lock the table for minutes, causing service disruption. Schedule such scripts during low‑traffic windows and split them into batches to minimize impact.

update user set status=0 where status=1;

7. Irreversible Changes

Ensure every DDL/DML operation can be rolled back. For updates, add a timestamp column (e.g., time=now()) or use temporary tables to record affected rows, facilitating later rollback.

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

8. Missing Indexes

After adding a new column, especially foreign‑key references, create an index to prevent full‑table scans and timeouts in production.

9. Renaming Columns in Production

Changing column names in production scripts is prohibited because code may still reference the old name, leading to runtime errors. Keep column names stable and use migration scripts only in pre‑production environments.