1

I have created views on my db and the corresponding Entity. All seems to work fine but whenever I run

php bin/console doctrine:schema:validate

it will tell me that the mapping is fine, but not the db, as follows:

[Database] FAIL - The database schema is not in sync with the current mapping file.

Looking it up I found that one can configure DBAL to filter out tables from validation.

This is what I attempted on config.yml ( check last line on code below). The intention is to exclude tables whose name start with "view" from validation.

doctrine:
dbal:
    default_connection: default
    connections:
        default:      
            driver: pdo_mysql
            host: '%database_host%'
            port: '%database_port%'
            dbname: '%database_name%'
            user: '%database_user%'
            password: '%database_password%'
            charset: utf8mb4
            default_table_options:
                charset: utf8mb4
                collate: utf8mb4_unicode_ci
            schema_filter: ~^(?!view_)~

So, the schema_filter as per this documentation should filter that out, but it doesn't.

I checked a few other questions, including this

Any ideas? Thanks

BernardA
  • 1,391
  • 19
  • 48

1 Answers1

3

Message tell you that your mapping it's not the same as your database schema, you must update your database scheme. For Symfony3 command is

php bin/console doctrine:schema:update --force

Actually, this command is incredibly powerful. It compares what your database should look like (based on the mapping information of your entities) with how it actually looks, and executes the SQL statements needed to update the database schema to where it should be

You can also use 

php bin/console doctrine:schema:update --dump-sql

to see SQL you need to run, but without the change database scheme.

When you run 

php bin/console doctrine:schema:validate

Doctrine will check your mapping files and for you is ok. After that will check your schema. In that moment your parameter schema_filter tell Doctrine to ignore all tables in database which name start with view, but in your mapping files exist entity with table name view... and for that you getting error

[Database] FAIL - The database schema is not in sync with the current mapping file.

So the schema_filter is use to tell Doctrine to ignore tables in database and not to ignore entities.

To see when to use schema_filter imagine situation that you need custom tables in database with names that start with custom_ , in your files you don't have entities mapped with this tables and if you call 

 php bin/console doctrine:migrations:diff

this will drop all custom tables, except if you in your config file tell Doctrine to ignore custom tables and you can do that if set up parameter 

 schema_filter: ~^(?!custom_)~

Please check Doctrine documentation

ilicmsreten
  • 428
  • 2
  • 5
  • Thanks @circleandsquare. I am aware of how to update schema. The issue is not that, but it's how to keep symfony from considering views whenever validating/updating db. The inclusion of the schema_filter on configuration was supposed to do that, but it does not. So symfony attempts to create a table with the same name as the view and, of course, it generates an error. – BernardA Oct 19 '17 at 08:12
  • As you can see on my config above, I already included the schema_filter directive and it's not working, ie, it is not filtering out the view on the db. Also, when I wrote this question I was not using doctrine migrations. I've installed it, but then I run into another issue. See this question https://stackoverflow.com/questions/46825774/how-set-up-symfony-3-doctrine-migrations-with-multiple-db?noredirect=1&lq=1 – BernardA Oct 20 '17 at 07:42
  • I just want to tell you that schema filter work correctly. Schema filter not ignore entities, and doesn't​ solve your problem. Symfony don`t provide command to exclude entities, so you can define two different entity manager one for view and second for other entities. – ilicmsreten Oct 20 '17 at 08:26
  • Still, that's exactly what the documentation says it does as per this http://symfony.com/doc/current/bundles/DoctrineMigrationsBundle/index.html#manual-tables. Creating another em for views seems like a huge workaround. I will look into it though. – BernardA Oct 20 '17 at 10:08
  • @BernardA what ilicmsreten is trying to convey is that when an entity class exists in your code, it will be used when comparing against the schema. The filter affects the schema side, i.e. it lets you make some tables/views invisible to the diff tool. But it does not make the entity class invisible. So, if you have an entity class in the code, it will be take part in diffing. – Meglio Mar 20 '20 at 10:01