Database FAIL - The database schema is not in sync

问题:

Can anybody explain the following doctrine schema validation error message please:

Here is the the yaml ORM definition of each entity in the manyToMany relationship, created inline with section 5.9 of the documentation.

Rep\Bundle\ProjectBundle\Entity\User:
    type: entity
    table: User
    fields:
        id:
            id: true
            type: integer
            unsigned: true
            nullable: false
            generator:
                strategy: AUTO
        username:
            type: string
            length: 25
            fixed: false
            nullable: false
        salt:
            type: string
            length: 32
            fixed: false
            nullable: false
        password:
            type: string
            length: 40
            fixed: false
            nullable: false
        email:
            type: string
            length: 60
            fixed: false
            nullable: false
    manyToMany:
        roles:
            targetEntity: UserRole
            inversedBy: users
            joinTable:
                name: UserRoleLookup
                joinColumns:
                    user_id:
                        referencedColumnName: id
                inverseJoinColumns:
                    user_role_id:
                        referencedColumnName: id
    lifecycleCallbacks: {  }

And the UserRole inverse yaml configuration:

Rep\Bundle\ProjectBundle\Entity\UserRole:
    type: entity
    table: UserRole
    fields:
        id:
            id: true
            type: integer
            unsigned: true
            nullable: false
            generator:
                strategy: AUTO
        name:
            type: string
            length: 50
            fixed: false
            nullable: false
    manyToMany:
        users:
            targetEntity: User
            mappedBy: roles
    lifecycleCallbacks: {  }

Here is the User table schema:

CREATE TABLE `User` (
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `username` varchar(25) COLLATE utf8_unicode_ci NOT NULL,
  `salt` varchar(32) COLLATE utf8_unicode_ci NOT NULL,
  `password` varchar(40) COLLATE utf8_unicode_ci NOT NULL,
  `email` varchar(60) COLLATE utf8_unicode_ci NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

The UserRole table schema:

CREATE TABLE `UserRole` (
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `name` varchar(50) COLLATE utf8_unicode_ci NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

And the UserRoleLookup schema:

CREATE TABLE `UserRoleLookup` (
  `user_id` int(11) unsigned NOT NULL,
  `user_role_id` int(11) unsigned NOT NULL,
  PRIMARY KEY (`user_id`,`user_role_id`),
  KEY `user_role_id` (`user_role_id`),
  CONSTRAINT `userrolelookup_ibfk_2` FOREIGN KEY (`user_role_id`) REFERENCES `userrole` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT `userrolelookup_ibfk_1` FOREIGN KEY (`user_id`) REFERENCES `user` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

As you can see, it's a pretty simplistic setup with a look-up table to dictate a user's roles or the set of users in a given user role. However, I'm receiving this frustrating synch error. I've read nothing here or online which answers this question in any concise detail, I was hoping someone could clarify if I am safe to leave this configuration and ignore this error?

回答1:

Run this command to show the differences in the SQL without having to dump your db:

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

You can also run the following command to perform the changes:

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

For symfony2 it was

php app/console doctrine:schema:update --force --full-database

回答2:

for Symfony3:

app/console changed to bin/console, --full-database to --complete

so the final command will be:

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

回答3:

It's simple: some field or relation, or entity, etc. has not yet been translated as a column or table in your database schema. Update your schema and you'll be fine.

回答4:

For anyone interested in this, re-generating my table schema produced the following look-up schema:

CREATE TABLE `UserRoleLookup` (
  `user_id` int(11) NOT NULL,
  `user_role_id` int(11) NOT NULL,
  PRIMARY KEY (`user_id`,`user_role_id`),
  KEY `IDX_4511E771A76ED395` (`user_id`),
  KEY `IDX_4511E7718E0E3CA6` (`user_role_id`),
  CONSTRAINT `FK_4511E7718E0E3CA6` FOREIGN KEY (`user_role_id`) REFERENCES `UserRole` (`id`),
  CONSTRAINT `FK_4511E771A76ED395` FOREIGN KEY (`user_id`) REFERENCES `User` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;\

I guess symfony2-doctrine bundles aren't a big fan of unsigned integers, as I can see little change from the schema I posted. Anyway, problem solved.

回答5:

php bin/console doctrine:schema:update --dump-sql it may be works it fixed my problem

回答6:

I have the same problem. Besides, when running

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

It will always display same sql regardless I've executed the sql already. It looks like that above command fail to detect real difference between database schema and current entity metadata. I also verify that these kind of problem somehow is related to the database you used. Because I don't have such issues at least in MySQL 5.7.25, but MariaDB 10.2.24. check here for more info: https://github.com/symfony/symfony/issues/27166#issue-320494745

P.S. MariaDB causes me other trouble like "index key length 767 bytes". Do not mean it's bad. But remind me 3 years ago the first time I decide to use MariaDB, posts/news saying how good it is comparing to MySQL which is just acquired by Oracle. And news saying MySQL is going to be different and then the panics.... (just personal opinion)