Error updating entity - Symfony 3

Question

There's something very very wrong with Doctrine/MySQL or I'm just completly dumb. I'm trying to execute a simple update query on Question entity:

+---------------+--------------+------+-----+---------+----------------+
| Field         | Type         | Null | Key | Default | Extra          |
+---------------+--------------+------+-----+---------+----------------+
| id            | int(11)      | NO   | PRI | NULL    | auto_increment |
| form_id       | int(11)      | YES  | MUL | NULL    |                |
| name          | varchar(255) | NO   |     | NULL    |                |
| question_type | varchar(255) | NO   |     | NULL    |                |
| validation    | longtext     | YES  |     | NULL    |                |
| required      | tinyint(1)   | YES  |     | NULL    |                |
| order         | int(11)      | NO   |     | NULL    |                |
+---------------+--------------+------+-----+---------+----------------+

With:

public function buildForm(FormBuilderInterface $builder, array $options) {
    $builder
        ->add('order', IntegerType::class)    
        ->add('name', TextType::class)
        ->add('questionType', TextType::class)
        ->add('validation', TextType::class)
        ->add('required', CheckboxType::class)     
    ;         
}

and

...
$em->persist($question);
...

I'm getting this error:

Uncaught PHP Exception Doctrine\DBAL\Exception\SyntaxErrorException: "An exception occurred while executing 'UPDATE questions SET order = ? WHERE id = ?' with params [1, 12]

Every other field validates with no problem!

When I'm trying to run the same query in the console, this is the result:

mysql> UPDATE questions SET order=1 WHERE id=14;
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 'order=1 WHERE id=14' at line 1

When I run the same query in Workbench, everything is ok:

UPDATE `questions` SET `order`='2' WHERE `id`='7';

Finally, updating another column in this table (also integer!), like so:

update questions set form_id=5 where id=7;

Goes as expected.

What is going on?

EDIT: To simplify, why this works:

UPDATE questions SET form_id=5 WHERE id=7;

And this doesn't:

UPDATE questions SET order=3 WHERE id=7;

user10089632 · Accepted Answer · 2017-09-01T22:55:57.680

1

order is a reserved word for Mysql
That's why it runs when (escaped with backticks)
It goes without saying that choosing another name would be safer
See this answer for more informations

edited Sep 01 '17 at 22:55

answered Sep 01 '17 at 22:47

user10089632

5,216
1
26
34

Right, I just got it work renaming to (random name) orderx ;) I dodn't think of that because order is always followed by BY. So is BY also reserved? Anyway, thanks. – Tompo Sep 01 '17 at 22:57
You're welcome, well I guess the language parser is triggered whenever there is `order` followed by a space – user10089632 Sep 01 '17 at 23:02

Error updating entity - Symfony 3

1 Answers1