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

Question

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?

Answer 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

Answer 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

Answer 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.

Answer 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.