ALTER TABLE ADD COLUMN with AFTER gives a syntax error
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'AFTER'Short answer
The parenthesized form ADD COLUMN (a INT, b INT) cannot take FIRST or AFTER. Write one ADD COLUMN clause per column, each with its own AFTER, and separate the clauses with commas in a single ALTER TABLE statement.
Fix it fast
1Find out which cause you have
Look at your statement. If the new columns sit inside one pair of parentheses, that is the cause.
SHOW CREATE TABLE t1\G2Match what you see, then jump to its fix
What does the error look like?
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'AFTER'
Cause 1 of 2
AFTER used with the parenthesized column list
1Check
The syntax for the list form has no position clause. Only the single-column form has one.
SHOW COLUMNS FROM t1;2Fix
Split the list. Repeat ADD COLUMN for each column and give each its own position. A later column can be placed after one added earlier in the same statement.
ALTER TABLE t1
ADD COLUMN a INT AFTER c,
ADD COLUMN b VARCHAR(50) AFTER a;3Verify
The columns appear in the order you asked for.
SHOW COLUMNS FROM t1;Confirmed by dev.mysql.com
Cause 2 of 2
The same AFTER applied to the whole group by mistake
1Check
Decide the order you want, for example c, a, b.
SHOW COLUMNS FROM t1;2Fix
Chain them: put the first after the existing column, and each next one after the previous new column.
ALTER TABLE t1
ADD COLUMN a INT AFTER c,
ADD COLUMN b INT AFTER a,
ADD COLUMN d INT AFTER b;3Verify
The order is c, a, b, d.
SHOW COLUMNS FROM t1;Confirmed by dev.mysql.com
How do I stop it coming back?
- Use the parenthesized list only when column position does not matter; the default is to add columns last.
- Do not depend on column order in application code: select columns by name.
Sources
How was this page made?
I drafted this page with AI assistance from the official documents listed under Sources. Every cause above names the document that confirms it. I have not reproduced this fix on a machine of my own yet, so read it as documented guidance, not a test report. Found a mistake? Tell me.
Which stack is behind this page?
Projects of mine built on the same technologies:
- TaxiDexMy own multi-tenant taxi dispatch SaaS: API, dispatcher portal, driver and passenger apps, and the servers underneath.
- QistLockA NestJS and React SaaS driving an Android Device Owner agent that locks financed phones when payments lapse.
- ShopGoAn e-commerce aggregator with a Python scraping engine behind its catalog, past 10K Google Play downloads.
Related fixes
Databases
Installing the Airflow MySQL provider fails on pkg-config or mysqlclient
Building the mysqlclient wheel fails during the install
The Airflow MySQL provider depends on mysqlclient, which pip compiles against the MySQL C client library.
2 causes, each with a fixDatabases
A query on Aurora MySQL dies with 'table /rdsdbdata/tmp/#sql... is full'
ERROR 1114 (HY000): The table '/rdsdbdata/tmp/#sqlfd_1586_2' is full
In Aurora MySQL 3, internal temporary tables share one pool, and when that pool and the memory-mapped overflow file are used up, a reader instance ends the query with error 1114.
3 causes, each with a fixDatabases
Azure Database for MySQL refuses a connection that does not use SSL
ERROR 3159 (HY000): Connections using insecure transport are prohibited while --require_secure_transport=ON.
Azure Database for MySQL Flexible Server requires encrypted connections by default (require_secure_transport = ON, TLS 1.2).
4 causes, each with a fixDatabases
Compiling a C or C++ program that includes mysql.h fails
undefined reference to `mysql_init'
The compiler only finds mysql.h when the MySQL client development files are installed and their folder is on the include path.
3 causes, each with a fixDatabases
DBeaver fails to resolve Maven dependencies when downloading a database driver
A driver library does not download automatically
DBeaver downloads driver jars from a Maven repository.
3 causes, each with a fixDatabases
DBeaver cannot verify the MySQL server certificate
unable to find valid certification path to requested target
DBeaver's MySQL driver is checking the server certificate and Java does not trust the CA that signed it.
2 causes, each with a fix