Doctrine 2: There is no column with name '$columnName' on table '$table'

Question

When I do:

vendor/bin/doctrine-module orm:schema-tool:update

Doctrine 2.4 gives me this error:

[Doctrine\DBAL\Schema\SchemaException]
There is no column with name 'resource_id' on table 'role_resource'.

My actual MySQL database schema has the column and the table, as evident from running this command (no errors thrown):

mysql> select resource_id from role_resource;
Empty set (0.00 sec)

Thus, the error must be somewhere in the Doctrine's representation of the schema. I did a var_dump() of $this object, and here is what I get (partial):

object(Doctrine\DBAL\Schema\Table)#546 (10) {
    ["_name"   :protected] => string(13) "role_resource"
    ["_columns":protected] => array(0) { }

Note that indeed, the _columns key does not contain any columns, which is how Doctrine checks for column names.

In my case, the partial trace dump is as follows:

• SchemaException.php#L85
• Table.php#L252
• Table.php#L161

Reading other posts with similar problem, seem to suggest that I may have an error in the column case (upper vs lower). While it is possible I have missed something, but looking over my actual schema on the Database and the Annotations in my code seem to suggest a match (all lowercase). Similarly, Doctrine2's code does incorporate checks for such casing errors. So I am ruling out the error casing possibility.

Another post I've seen suggests that there may be an error in my Annotations, i.e. wrong naming, syntax, or id placement. I don't know, I checked it and it seems fine. Here is what I have:

class Role implements HierarchicalRoleInterface
{
/**
 * @var \Doctrine\Common\Collections\Collection
 * @ORM\ManyToMany(targetEntity="ModuleName\Entity\Resource")
 * @ORM\JoinTable(name="role_resource",
 * joinColumns={@ORM\JoinColumn(name="role_id", referencedColumnName="id")},
 * inverseJoinColumns={@ORM\JoinColumn(name="resource_id", referencedColumnName="id")}
 * )
 */
protected $resource;

So at the moment, I am stuck, and unable to use the ORM's schema-generation tools. This is a persistent error. I have scraped my database, generated schema anew using ORM, but still get stuck on this error whenever I try to do an update via ORM, as I describe in this post. Where perhaps should I look next?

---

Update: traced it to this code: $sql before this line ==

SELECT COLUMN_NAME AS Field, 
    COLUMN_TYPE AS Type,
    IS_NULLABLE AS `Null`,
    COLUMN_KEY AS `Key`,
    COLUMN_DEFAULT AS `Default`,
    EXTRA AS Extra,
    COLUMN_COMMENT AS Comment,
    CHARACTER_SET_NAME AS CharacterSet,
    COLLATION_NAME AS CollactionName,
FROM information_schema.COLUMNS 
WHERE TABLE_SCHEMA = 'loginauth' AND TABLE_NAME = 'role_resource'

which when I run it form MySQL prompt, returns (some columns were trimmed):

+-------------+---------+------+-----+--------------+----------------+
| Field       | Type    | Null | Key | CharacterSet | CollactionName |
+-------------+---------+------+-----+--------------+----------------+
| role_id     | int(11) | NO   | PRI | NULL         | NULL           |    
| resource_id | int(11) | NO   | PRI | NULL         | NULL           |
+-------------+---------+------+-----+--------------+----------------+

and the $this->executeQuery($sql, $params, $types) returns the proper(?) statement that runs fine on my prompt, but when ->fetchAll() is called, specifically this fetchAll() it breaks down and returns an empty array. Can I have someone make sense out of this?

MORE:

Essentially, from above links, $this->executeQuery($sql, $params, $types) returns:

object(Doctrine\DBAL\Driver\PDOStatement)#531 (1) {
    ["queryString"]=> string(332) "SELECT COLUMN_NAME AS Field, COLUMN_TYPE AS Type, IS_NULLABLE AS `Null`, COLUMN_KEY AS `Key`, COLUMN_DEFAULT AS `Default`, EXTRA AS Extra, COLUMN_COMMENT AS Comment, CHARACTER_SET_NAME AS CharacterSet, COLLATION_NAME AS CollactionName FROM information_schema.COLUMNS WHERE TABLE_SCHEMA = 'loginauth' AND TABLE_NAME = 'role_resource'"
}

but then $this->executeQuery($sql, $params, $types)->fetchAll() (adding fetchAll()), returns this:

array(0) {
}

And that is so sad my friends :( because I don't know why it returns an empty array, when the statement in queryString above is so clearly valid and fruitful.

Answer 1

Check that the column names used in 'index' and 'uniqueContraints' schema definitions actually exist:

For example using Annotations:

@ORM\Table(name="user_password_reset_keys", indexes={@ORM\Index(name="key_idx", columns={"key"})} )

I had renamed my column from 'key' to 'reset_key' and this column name mismatch caused the error

 /**
 * @var string
 *
 * @ORM\Column(name="reset_key", type="string", length=255, nullable=false)
 */
private $resetKey;