Doctrine DBAL
  1. Doctrine DBAL
  2. DBAL-761

Driver\ResultStatement::fetchAll() returns empty array on a seemingly valid Driver\PDOStatement object

    Details

    • Type: Improvement Improvement
    • Status: Open
    • Priority: Minor Minor
    • Resolution: Unresolved
    • Affects Version/s: 2.4
    • Fix Version/s: None
    • Component/s: Drivers
    • Environment:
      Windows 7 64 bit, Zend Server/Studio, PHP 5.4.16,
      Server: Apache/2.2.22 (Win32) mod_ssl/2.2.22 OpenSSL/0.9.8x

      Description

      I came across a weird issue, where when running:

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

      I would get:

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

      But I did have a column with the above name in the above table, so that was a weird message for me. So I traced it all the way to this line of code:

      https://github.com/doctrine/dbal/blob/2.4/lib/Doctrine/DBAL/Connection.php#L630

      If I remove "->fetchAll()" from that line, I get this object:

      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'" }

      Which has a valid SQL query that runs fine and shows Field names on my local machine's MySQL prompt. But when I add ->fetchAll() back in that line of code, an empty array is returned, field names are not returned, and a SchemaException is generated. I found this odd and wanted to report it. Whether it is a bug or not, hopefully I can find the cause of this issue.

      For loads more info, please see this: http://stackoverflow.com/questions/20907491/doctrine-2-there-is-no-column-with-name-columnname-on-table-table

        Activity

        Hide
        Steve Müller added a comment -

        Dennis Matveyev It seems you are using MySQL. Can you please tell which version you use?

        Show
        Steve Müller added a comment - Dennis Matveyev It seems you are using MySQL. Can you please tell which version you use?
        Hide
        Steve Müller added a comment - - edited

        Dennis Matveyev Please also provide whether you use mysqli or PDO_MYSQL.

        Show
        Steve Müller added a comment - - edited Dennis Matveyev Please also provide whether you use mysqli or PDO_MYSQL.
        Hide
        Steve Müller added a comment -

        Dennis Matveyev Sounds dumb, but also are you sure that the vendor/bin/doctrine-module orm:schema-tool:update uses the correct connection parameters and does not by error connect to another database server or something? Because otherwise everything looks good IMO.

        Show
        Steve Müller added a comment - Dennis Matveyev Sounds dumb, but also are you sure that the vendor/bin/doctrine-module orm:schema-tool:update uses the correct connection parameters and does not by error connect to another database server or something? Because otherwise everything looks good IMO.
        Hide
        Dennis Matveyev added a comment -

        Yes, I am using:
        Server version: 5.5.23-log MySQL Community Server (GPL)
        mysql client Ver 14.14 Distrib 5.5.23, for Win32 (x86)

        Doctrine connection string for 'driverClass' is 'Doctrine\DBAL\Driver\PDOMySql\Driver', so it is PDO_MYSQL.

        I am connecting to the right database, but your last suggestion is what helped to uncover the issue. When connecting to the database with the very same user/pass, and running the command, here is what I saw:

        mysql> select resource_id from role_resource;
        ERROR 1142 (42000): SELECT command denied to user 'login'@'localhost' for table 'role_resource'

        Running a GRANT command to allow SELECT for this user solved the problem.

        To improve debugging of similar issues, I'd see if there is a way to propagate the error from MySQL server to ./doctrine-module,
        or
        improve the error message of SchemaException, to i.e. "There is no column with name 'action_id' on table 'role_action', or database permissions prevent table access."

        Thanks!

        Show
        Dennis Matveyev added a comment - Yes, I am using: Server version: 5.5.23-log MySQL Community Server (GPL) mysql client Ver 14.14 Distrib 5.5.23, for Win32 (x86) Doctrine connection string for 'driverClass' is 'Doctrine\DBAL\Driver\PDOMySql\Driver', so it is PDO_MYSQL. I am connecting to the right database, but your last suggestion is what helped to uncover the issue. When connecting to the database with the very same user/pass, and running the command, here is what I saw: mysql> select resource_id from role_resource; ERROR 1142 (42000): SELECT command denied to user 'login'@'localhost' for table 'role_resource' Running a GRANT command to allow SELECT for this user solved the problem. To improve debugging of similar issues, I'd see if there is a way to propagate the error from MySQL server to ./doctrine-module, or improve the error message of SchemaException, to i.e. "There is no column with name 'action_id' on table 'role_action', or database permissions prevent table access." Thanks!
        Hide
        Steve Müller added a comment -

        Dennis Matveyev Thank you for reporting this and I'm glad I could help. I will mark this as improvement, though. You are right in that the root cause of the error should be propagated to the user instead.

        Show
        Steve Müller added a comment - Dennis Matveyev Thank you for reporting this and I'm glad I could help. I will mark this as improvement, though. You are right in that the root cause of the error should be propagated to the user instead.
        Hide
        Steve Müller added a comment -

        Dennis Matveyev Okay I found the root cause of the problem. See what MySQL states about access to the information_schema table for retrieving metadata about a certain database:

        Each MySQL user has the right to access these tables, but can see only the rows in the tables that correspond to objects for which the user has the proper access privileges. In some cases (for example, the ROUTINE_DEFINITION column in the INFORMATION_SCHEMA.ROUTINES table), users who have insufficient privileges will see NULL.

        So it returns NULL instead of raising an error and this is why Doctrine is not able to propagate the proper exception in this case. I'm not sure if changing the exception message in the SchemaException class is the proper way of handling this. Also please note that this is not only about columns (where you got stuck at) but can occurr for almost every list*() action called in the SchemaManager I suppose. Furthermore this might be a MySQL specific issue, not sure about that.

        The proper solution would be to throw an exception much earlier, to have a decent behaviour in such an edge case. I think we should throw an exception here: https://github.com/doctrine/dbal/blob/master/lib/Doctrine/DBAL/Schema/AbstractSchemaManager.php#L267 because a table cannot live without columns and indicates an error somewhere. But I would like to hear the opinion of Benjamin Eberlei on that issue.

        Show
        Steve Müller added a comment - Dennis Matveyev Okay I found the root cause of the problem. See what MySQL states about access to the information_schema table for retrieving metadata about a certain database: Each MySQL user has the right to access these tables, but can see only the rows in the tables that correspond to objects for which the user has the proper access privileges. In some cases (for example, the ROUTINE_DEFINITION column in the INFORMATION_SCHEMA.ROUTINES table), users who have insufficient privileges will see NULL. So it returns NULL instead of raising an error and this is why Doctrine is not able to propagate the proper exception in this case. I'm not sure if changing the exception message in the SchemaException class is the proper way of handling this. Also please note that this is not only about columns (where you got stuck at) but can occurr for almost every list*() action called in the SchemaManager I suppose. Furthermore this might be a MySQL specific issue, not sure about that. The proper solution would be to throw an exception much earlier, to have a decent behaviour in such an edge case. I think we should throw an exception here: https://github.com/doctrine/dbal/blob/master/lib/Doctrine/DBAL/Schema/AbstractSchemaManager.php#L267 because a table cannot live without columns and indicates an error somewhere. But I would like to hear the opinion of Benjamin Eberlei on that issue.
        Hide
        Steve Müller added a comment -

        I checked the SQL Server documentation and it seems they behave exactly the same: http://technet.microsoft.com/en-us/library/ms187113.aspx

        In SQL Server 2005 and later, the visibility of metadata is limited to securables that a user either owns or on which the user has been granted some permission. For example, the following query returns a row if the user has been granted a permission such as SELECT or INSERT on the table myTable.

        However, if the user does not have any permission on myTable, the query returns an empty result set.

        Show
        Steve Müller added a comment - I checked the SQL Server documentation and it seems they behave exactly the same: http://technet.microsoft.com/en-us/library/ms187113.aspx In SQL Server 2005 and later, the visibility of metadata is limited to securables that a user either owns or on which the user has been granted some permission. For example, the following query returns a row if the user has been granted a permission such as SELECT or INSERT on the table myTable. However, if the user does not have any permission on myTable, the query returns an empty result set.

          People

          • Assignee:
            Benjamin Eberlei
            Reporter:
            Dennis Matveyev
          • Votes:
            0 Vote for this issue
            Watchers:
            2 Start watching this issue

            Dates

            • Created:
              Updated: