Alter table modify column query results in SQL Syntax error 1064

Question

I get this error:

exception 'PDOException' with message 'SQLSTATE[42000]: Syntax error or access violation: 1064 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 '(sfname2 VARCHAR(255) NOT NULL , slname2 VARCHAR(255) NOT NULL , ' at line 2'

When I try to run this query:

$stmnt = $db->prepare('ALTER TABLE eventfields MODIFY  
                       (sfname2 VARCHAR(255) NOT NULL ,
                       slname2 VARCHAR(255) NOT NULL ,
                       ....
                       customfield1 VARCHAR(255) NOT NULL ,)');

Why?

John Conde · Accepted Answer · 2014-01-31 23:36:40Z

1

Get rid of the parenthesis around your columns and you need MODIFY COLUMN for each column being altered.

$stmnt = $db->prepare('ALTER TABLE eventfields   
                   MODIFY COLUMN sfname2 VARCHAR(255) NOT NULL ,
                   MODIFY COLUMN slname2 VARCHAR(255) NOT NULL ,
                   ....
                   MODIFY COLUMN customfield1 VARCHAR(255) NOT NULL ');

edited Jan 31, 2014 at 23:36

answered Jan 31, 2014 at 23:29

John Conde

220k100 gold badges464 silver badges504 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

gta0004 Over a year ago

Oh. I still get an error. Now its near 'slname2 VARCHAR(255) NOT NULL , school2 VARCHAR(255) NOT NULL , gr' at line 3'

gta0004 Over a year ago

ok. Is it possible to do NOT NULL DEFAULT 'something' in one query or does setting the default have to be in another one?

gta0004 Over a year ago

So now I am getting SQLSTATE[HY000]: General error: 2030 This command is not supported in the prepared statement protocol yet on (65): PDOStatement->execute()

gta0004 Over a year ago

had to do $db->exec(..) instead of prepare.

Lys777 · Accepted Answer · 2024-03-21 21:21:17Z

0

I was getting this same error on a MODIFY COLUMN after a MySQL upgrade: check the manual that corresponds to your MySQL server version for the right syntax to use near 'groups MODIFY COLUMN description TEXT'

Turns out my table/column names were newly added as MySQL reserved words, so I just had to add my backticks:

ALTER TABLE `groups` MODIFY COLUMN `description` TEXT

I realize this is not the exact same issue as the OP, but the error message initially let me to believe it was related to the MODIFY COLUMN, and this was the first SO match I found.

answered Mar 21, 2024 at 21:21

Lys777

4661 gold badge4 silver badges11 bronze badges

Collectives™ on Stack Overflow

Alter table modify column query results in SQL Syntax error 1064

2 Answers 2

4 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

4 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related