Skip to content

[Doctrine][Messenger] Fix Identity columns not supported by Oracle driver #58504

New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

Sign up for GitHub

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

Jump to bottom
Closed
devloop42 opened this issue Oct 8, 2024 · 10 comments
Closed

[Doctrine][Messenger] Fix Identity columns not supported by Oracle driver #58504

devloop42 opened this issue Oct 8, 2024 · 10 comments
Labels
Bug Status: Needs Review

Comments

@devloop42
Copy link

Symfony version(s) affected

6.4.12

Description

Error Message
IdentityColumnsNotSupported > DriverException > TransportException : An exception occurred in the driver: The driver does not support identity columns.

Context
Usage of async symfony/messenger with doctrine as transport

Explanation

When dealing with an Oracle database, the dispatched message is well inserted into the database but an exception is encountered when trying to get the last inserted id. That exception lead to a rollback and therefore a failing process of queing the messages.
IDs are generated with sequence instead of identity column as you have it with MySQL database for exemple.
A specific treatment is needed as done for PostgreSQL database

How to reproduce

Environment

  • PHP 8.3
  • Symfony 6.4.12
  • OS : Windows
  • DB : Oracle Database 19c Standard Edition 2 Release 19.0.0.0.0
  • Extensions: oci_8_19, ...

How to reproduce

  1. Set up a project
  • Create a symfony web project
  • Create a Controller/route to use as test
  1. Check symfony/messenger works well as sync
  • Install symfony/messenger if not installed
  • Create a message and it's handler and test the dispatch and execution of the hander
  1. Test symfony/messenger works well as async
  • Install symfony/doctrine-messenger
  • Set the process as async

When testing the async process, the error should appear as the message can't be recorded into the database

Stack-Traces.txt

Possible Solution

Target the OraclePlatform to get the last inserted id as needed for that database :

...
    elseif ($this->driverConnection->getDatabasePlatform() instanceof OraclePlatform) {
          $sequenceName = strtoupper($this->configuration['table_name'].'_seq');

          $this->driverConnection->executeStatement($sql, $parameters, $types);
                
          $result = $this->driverConnection->fetchOne('select last_number from user_sequences where sequence_name = \''.$sequenceName.'\'');

          $id = (int) $result;

          if (!$id) {
              throw new TransportException('no id was found in Oracle for sequence: '.$sequenceName);
          }
    }
...

Important :

  • On GitHub, for the 6.4.x version, a solution is implemented but doesn't work and produce an error (ORA-02289). Should be adapted as shown above
    • Maybe it is in beta as the deployed version on my machine doesn't have that elseif block !
  • Fix should also be ported to symfony/doctrine-messenger 7.1.x

Additional Context

No response
@devloop42 devloop42 added the Bug label Oct 8, 2024
@xabbuh
Copy link
Member

xabbuh commented Oct 9, 2024

#54566 does not (fully) solve this. Do I understand that correctly?

@xabbuh
Copy link
Member

xabbuh commented Oct 9, 2024

@rjd22 FYI

@rjd22
Copy link
Contributor

rjd22 commented Oct 9, 2024 •
edited
Loading

@xabbuh I don't have a very new Oracle version to test this on. But I would say unless they deprecated something my solution should solve this.

Honestly using the user_sequences table to get the last id sounds like something that is not portable between different Oracle versions.

Edit: checked but my changes are not yet in 6.12

@devloop42
Copy link
Author

#54566 does not (fully) solve this. Do I understand that correctly?

@xabbuh : Yes. It doesn't solve the issue. Generated sequences always end with _seq (in my case anyway and if size not limited by config). And the request to get the ID ('SELECT '.$sequenceName.'.CURRVAL FROM DUAL') throws ORA-08002 error.

@rjd22 : I do agree. Using the user_sequences table is pretty ugly but I don't have a better solution as sequenceName.currval is not supported.

@rjd22
Copy link
Contributor

rjd22 commented Oct 9, 2024 •
edited
Loading

Well I would recommend doing some extended research what the new official way of getting currval is so I can check backwards compatibility. I cannot imagine it's using the user_sequences table.

I don't have access to your Oracle version so I cannot do the research myself.

@devloop42
Copy link
Author

My bad. 'SELECT '.$sequenceName.'.CURRVAL FROM DUAL' works like a charm 👍 . I was testing that request outside the current session.

@devloop42
Copy link
Author

For the sequence name, the auto_setup create it by adding the suffix _seq to the table name. And so far, all generated sequences by doctrine have been set that way. Is there a reason for forcing it as prefix to the table name ?

@rjd22
Copy link
Contributor

rjd22 commented Oct 9, 2024 •
edited
Loading

No reason except that prefixed sequences are an old Oracle default I fell back on. It might have changed in the years.

Edit: I have no strong feelings about the prefixing or suffixing so if feel it's better to change it you're free to make an PR.

@devloop42
Copy link
Author

@rjd22, @xabbuh : It's advised to not submit the PR on symfony/doctrine-messenger but on symfony/symfony instead.
But that repo is not proposed as base repository ... What am I missing 😶?

@xabbuh
Copy link
Member

xabbuh commented Oct 10, 2024

You need to fork the symfony/symfony repository and make the changes in that fork not in your fork of the symfony/doctrine-messenger repository.

This was referenced Oct 10, 2024
Closed
Merged
@devloop42 devloop42 changed the title [Doctrine-Messenger] Identity columns (lastInsertId) not supported with Oracle driver/database [Messenger][Doctrine] Fix Identity columns not supported by Oracle driver Oct 14, 2024
@devloop42 devloop42 changed the title [Messenger][Doctrine] Fix Identity columns not supported by Oracle driver [Doctrine][Messenger] Fix Identity columns not supported by Oracle driver Oct 17, 2024
@xabbuh xabbuh closed this as completed in 5bc387b Oct 18, 2024
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Assignees
No one assigned
Labels
Bug Status: Needs Review
Projects
None yet
Milestone
No milestone
Development

No branches or pull requests
4 participants
@rjd22 @xabbuh @carsonbot @devloop42