Doctrine 2 Symfony 5 migration to an oracle database

Question

I have

• PHP 8.0
• Ubuntu 20.4 running in a VM
• Symfony Version 5
• doctrine/orm 2.8.2
• doctrine/dbal 2.12.1
• doctrine/migrations 3.1.1

and oracle19c running

I have also some entities which I want now to make:migrate with doctrine. But at this point I stuck on this error:

More information:

1. In doctrine.yaml:

dbal:

default_connection: oracle

    connections:
        default:
            schema_filter: ~^(?!t_cmdb_|m_cmdb_|migration_versions)~
            mapping_types:
                enum: string
   
            # configure these for your database server
            url: '%env(resolve:DATABASE_URL)%'
            driver: 'pdo_mysql'
            server_version: '5.7'
            charset: utf8mb4
        oracle:
            schema_filter: ~^(?!t_cmdb_|m_cmdb_|migration_versions)~
            mapping_types:
                enum: string
                
            # configure these for your database server
            url: '%env(resolve:DATABASE_CUSTOMER_URL)%'
            servicename: 'pdb'
            service: true
            driver: 'oci8'
            server_version: ~
            charset: AL32UTF8

I have a oracle database created with:

php bin/console doctrine:database:create --connection oracle

which goes through without any problems

Than I call

php bin/console make:migration -vvv

I got this error message:

[Doctrine\DBAL\Exception]
  Unknown database type interval day(3) to second(2) requested, Doctrine\DBAL\Platforms\OraclePlatform may not support it.

All my date columns have the type datetime there is no interval in there!

I have searched for this problem but I can't come through. Did somebody has this Problem while making migration for doctrine?

Thanks a lot for any hint.

Michael

Answer 1

Doctrine commands usually scan all tables in the user/schema defined in Symfony's .env file (look for DATABASE_USER). That error means that at least one of the tables in that schema has a column of type "INTERVAL...".

If you are sure that you don't have any table with an "INTERVAL..." type column, it may be that you are using directly the SYSTEM user/schema (or any other Oracle reserved user/schema). In this case, you need to create a separate user/schema for your project and move your project tables there.

Anyway, run this to check which tables under which user/schema (OWNER) have a column of type "INTERVAL...":

SELECT 
OWNER,
TABLE_NAME ,
    COLUMN_NAME,
    DATA_TYPE,
    DATA_LENGTH,
    DATA_PRECISION,
    DATA_SCALE
FROM ALL_TAB_COLUMNS
where 
data_type like 'INTERVAL%'
-- and OWNER = 'YOUR_USER_SCHEMA' -- must be in uppercase
ORDER BY TABLE_NAME

If you do use such a column type in your project, then you must add that column type in Doctrine's configuration: https://www.doctrine-project.org/projects/doctrine-orm/en/latest/cookbook/custom-mapping-types.html

If you discover that you were using the SYSTEM user/schema directly (perhaps in a Docker container?), then create your separate project user/schema:

CREATE USER your_separate_user_schema IDENTIFIED BY oracle; -- oracle is the password
Grant create session to your_separate_user_schema ; -- to be able to login with the new user
ALTER USER your_separate_user_schema quota unlimited on USERS; -- give your user schema disk space

To copy only the necessary tables from SYSTEM to your_separate_user_schema: while in system/sysdba, for each table do:

create table your_separate_user_schema.table_name as select * from table_name ;