Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

Doctrine\DBAL\Exception\InvalidFieldNameException when working with DB schema on PostgreSQL 10 #2868

Closed
skobkin opened this issue Sep 22, 2017 · 18 comments

Comments

Projects
None yet
@skobkin
Copy link

commented Sep 22, 2017

Since upgrading to PostgreSQL 10 I have an error in my projects using Doctrine when I trying to generate migrations or just check the schema difference:

$ sf doc:sche:up --dump-sql                                                                                          
  [Doctrine\DBAL\Exception\InvalidFieldNameException]                                                        
  An exception occurred while executing 'SELECT min_value, increment_by FROM "admin"."acl_classes_id_seq"':  
  SQLSTATE[42703]: Undefined column: 7 ERROR:  column "min_value" does not exist                             
  LINE 1: SELECT min_value, increment_by FROM "admin"."acl_classes_id_...                                    
                 ^
  [Doctrine\DBAL\Driver\PDOException]                                             
  SQLSTATE[42703]: Undefined column: 7 ERROR:  column "min_value" does not exist  
  LINE 1: SELECT min_value, increment_by FROM "admin"."acl_classes_id_...         
                 ^
  [PDOException]                                                                  
  SQLSTATE[42703]: Undefined column: 7 ERROR:  column "min_value" does not exist  
  LINE 1: SELECT min_value, increment_by FROM "admin"."acl_classes_id_...         
                 ^
doctrine:schema:update [--complete] [--dump-sql] [-f|--force] [--em [EM]]

Seems like it's related to nextcloud/server#5930 where @justin-sleep wrote:

This happens specifically because of changes to how PostgreSQL 10 handles sequence metadata.

@wzator

This comment has been minimized.

Copy link

commented Sep 26, 2017

Suggested solution:
PostgreSqlSchemaManager.php line 292:

    $data = $this->_conn->fetchAll('SELECT min_value, increment_by FROM ' . $this->_platform->quoteIdentifier($sequenceName));

to :

    $version = floatval($this->_conn->getWrappedConnection()->getServerVersion());

    if ($version >= 10) {
       $data = $this->_conn->fetchAll('SELECT min_value, increment_by FROM pg_sequences WHERE schemaname = \'public\' AND sequencename = '.$this->_conn->quote($sequenceName));
    }
    else
    {
        $data = $this->_conn->fetchAll('SELECT min_value, increment_by FROM ' . $this->_platform->quoteIdentifier($sequenceName));
    }
@justin-sleep

This comment has been minimized.

Copy link

commented Oct 5, 2017

PostgreSQL 10 is out of beta now: https://www.postgresql.org/about/news/1786/

@Majkl578

This comment has been minimized.

Copy link
Contributor

commented Oct 5, 2017

First we will need Travis CI to support it: travis-ci/travis-ci#8537

@iquad

This comment has been minimized.

Copy link

commented Nov 1, 2017

when it is going to be migrated? it definently solves the problem, and because of new sequence architecture on postgresql 10 we can't define a generic method for that.

@synthead

This comment has been minimized.

Copy link

commented Nov 14, 2017

Just tried @wzator's changes and it works fine here 👍

norbert-yoimo added a commit to yoimo/dbal that referenced this issue Nov 23, 2017

norbert-yoimo added a commit to yoimo/dbal that referenced this issue Nov 23, 2017

@exebece

This comment has been minimized.

Copy link

commented Nov 23, 2017

have this problem in doctrine2 using in symfony 3.3 version. no matter which ,,auto-identity'' aproach I chose, I got this error too. change suggested by wzator few lines above this solved the problem temporarily. thx for that.

@simPod

This comment has been minimized.

Copy link
Contributor

commented Nov 23, 2017

We are waiting for PSQL10 support in Travis I guess. Meanwhile you can use code from my PR. #2893

@exebece

This comment has been minimized.

Copy link

commented Nov 23, 2017

thank you, i'll analyze your commit and use it.

@Zyqsempai

This comment has been minimized.

Copy link

commented Dec 1, 2017

Guys, what the status of the issue?? When we can expect fix?

@simPod

This comment has been minimized.

Copy link
Contributor

commented Dec 1, 2017

Read the comments please

#2868 (comment)
#2868 (comment)

@Majkl578

This comment has been minimized.

Copy link
Contributor

commented Dec 27, 2017

#2893 has been merged and added Postgres 10 support, closing this one as duplicate.

@mca-digital

This comment has been minimized.

Copy link

commented Jan 25, 2018

@wzator´s suggestion worked perfectly!!!
Thanks!

@Andrioshe

This comment has been minimized.

Copy link

commented Feb 1, 2018

@wzator's suggestion worked like a charm. You can find this file in

./3rdparty/doctrine/dbal/lib/Doctrine/DBAL/Schema/PostgreSqlSchemaManager.php

@TiFaBl

This comment has been minimized.

Copy link

commented Apr 20, 2018

Hey,
i am not really sure about this, as i never before actually touched any doctrine implementation, but after the above fix worked a couple of weeks ago i set up a new project and apparently some changes were made from 2.6 to 2.7.

The erroneous $data assignment is now in line 311 and fetchAssoc() is used instead of fetchAll().

For me, the following adjustment worked:
Replace in PostgreSqlSchemaManager.php line 311:


$data      = $this->_conn->fetchAssoc('SELECT min_value, increment_by FROM ' . $this->_platform->quoteIdentifier($sequenceName));

with:


$version = floatval($this->_conn->getWrappedConnection()->getServerVersion());

if ($version >= 10) {
    $data = $this->_conn->fetchAssoc('SELECT min_value, increment_by FROM pg_sequences WHERE schemaname = \'public\' AND sequencename = '.$this->_conn->quote($sequenceName));
}
else
{
    $data = $this->_conn->fetchAssoc('SELECT min_value, increment_by FROM ' . $this->_platform->quoteIdentifier($sequenceName));
}
$sequence += $data;

@srigi

This comment has been minimized.

Copy link

commented Apr 30, 2018

This issue should be re-opened. Fetching data for sequence definition is not working with Postgres 10.3. Fix in #2893 in not enough.

Code from @TiFaBl actually fixes the problem. Whats the fastest way for fix to be released? Should I start PR with tests/fixed-code?

@Ocramius

This comment has been minimized.

Copy link
Member

commented Apr 30, 2018

Whats the fastest way for fix to be released? Should I start PR with tests/fixed-code?

First a failing test case

jeffesquivels added a commit to jeffesquivels/dbal that referenced this issue Jun 13, 2018

jeffesquivels added a commit to jeffesquivels/dbal that referenced this issue Jun 13, 2018

@PapsOu

This comment has been minimized.

Copy link

commented Aug 22, 2018

I confirm that the problem still occurs with PostgreSQL 10.5 and doctrine/dbal 2.8.0.

@Majkl578

This comment has been minimized.

Copy link
Contributor

commented Aug 22, 2018

DBAL is working as expected with PostgreSQL 10+, this is very likely a configuration issue in your code where you are not specifying serverVersion correctly.

The code path in question is covered by tests and tested against all PostgreSQL 9.x, PostgeSQL 10 and PostgreSQL 11 (this condition is not entered with PostgreSQL 10+ since these metadata are provided by the platform ahead).

Anyway, if you still feel this is a Doctrine issue rather than a configuration issue, please open new issue with failing test.

@doctrine doctrine locked as resolved and limited conversation to collaborators Aug 22, 2018

werrolf added a commit to werrolf/dbal that referenced this issue Jun 4, 2019

Sign up for free to subscribe to this conversation on GitHub. Already have an account? Sign in.
You can’t perform that action at this time.