Orm: Flush on MariaDB fails to Syntax error

[NikoGrano]

Bug Report

| Q | A
|------------ | ------
| Version | 2.7.0
| PHP | 7.4.1
| Database | MariaDB 10.4.11
| Framework | Symfony 4.4

Summary

I am trying to persist entity with to the database. It fails.

Current behaviour

  [PDOException (42000)]                                                                                
  SQLSTATE[42000]: Syntax error or access violation: 1064 You have an error in your SQL syntax; check   
  the manual that corresponds to your MariaDB server version for the right syntax to use near 'index,   
  recorded, type) VALUES (0, '2020-01-09 22:27:32.066694', 'App.Stock.Domai' at line 1 

How to reproduce

I have Entity extending abstract entity.
When trying to do following, I will get mentioned failure.

$this->em = EntityManagerInterface::class;
$entity = new Entity(...)

$this->em->persist($entity);
$this->em->flush();

I do not expect you to fix this, if this is bug with my code, but as I have this code publicly available, it might help you. Repository is available here

Expected behaviour

Entity gets persisted.

Next Steps in debugging

~I will be trying to run this under MariaDB 10.3. However, it should not affect this at all and do not wait me while I do it.~
Same error under 10.3.21

[NikoGrano]

Ref https://github.com/doctrine/dbal/issues/3815

[NikoGrano]

I tried to debug this following way:

1. I removed everything else from entity except index and ran bin/console doctrine:schema:update --force. This worked fine as it should, but when I try again persist on that Entity, I got this error

In PDOStatement.php line 121:

  [PDOException (42000)]                                                                                
  SQLSTATE[42000]: Syntax error or access violation: 1064 You have an error in your SQL syntax; check   
  the manual that corresponds to your MariaDB server version for the right syntax to use near 'index)   
  VALUES (0)' at line 1

But, when I try run query via DB CLI, it works fine.

Query working directly on DB.

INSERT INTO `stock_inventory_events` (`index`) VALUES ('1')

This seems like, either of ORM or DBAL is generating faulty query for the database.

[nikophil]

i guess index is a reserved word in sql, you have to explicitely escape it in your column declaration in the entity

/** @ORM\Column(name="`index`") */
$index

(notice the wrapping backticks)

[NikoGrano]

Yes, it works now. Thank you. (Closing due this is not anymore issue, but needs discussing.)

Shouldn't this be automatically detected by ORM? Like, if field name would map into reserved word it would add backticks automatically?

[nikophil]

i don't know why this is not directly handled, maybe a doctrine expert could answer that, i'm curious as well, i had this problem at some time which is tricky to detect
@Ocramius @alcaeus

[NikoGrano]

Reopening to gain attention.

[alcaeus]

Sorry, don't use ORM or MariaDB, so I have no clue. There's also no need to go close/reopen issues - just because it's at the top of my notification list doesn't mean I'll get to it any faster.

[NikoGrano]

Related to most of databases anyways, tought it will go past, if you see this as closed. Will be waiting for you reply.

[lcobucci]

This is a documented behaviour of v2.x: https://www.doctrine-project.org/projects/doctrine-orm/en/2.7/reference/basic-mapping.html#quoting-reserved-words

Things are changing for new versions but this is what we have for now.

[NikoGrano]

@lcobucci Can you answer to this? So is this going to be fixed in future to be working automatically or can I create PR for it and fix it.

i don't know why this is not directly handled, maybe a doctrine expert could answer that, i'm curious as well, i had this problem at some time which is tricky to detect
@Ocramius @alcaeus

[lcobucci]

What I meant with "things are changing for new versions" is that it's already implemented for v3.0-dev and won't be ported back on v2.x unless we manage to have BC-compatible implementation.

Not having automatic quoting isn't seen as a bug but a missing feature, so such implementation should come in a minor release (and not a patch). Feel free to send a PR 😁