Laravel data mismatch error while using \PDO::ATTR_EMULATE_PREPARES => true

Question

We have application build in Php Laravel and for the database we use postgres sql. And also on top of postgres we have configure pgBouncer to limit the maximum number of connections on server side by managing a pool of idle connections that can be used by any applications.

Now, we face the issue with the boolean values (True(0),False(1)) used in the application (Php Laravel). It gives below error when any CRUD operation is performed. In the below error column "revoked" is boolean type.

column \"revoked\" is of type boolean but expression is of type integer You will need to rewrite or cast the expression. (SQL: \"revoked\", \"created_at\") values (0, 2020-02-07 06:09:06)

Now after exploring, I came to know that boolean values needs to be consider to be string with the pgBouncer. So I have made changes in the connection.php file, located in "\vendor\laravel\framework\src\Illuminate\Database". I have change the code to consider the boolean value as mentioned below.

public function bindValues($statement, $bindings)
{
    foreach ($bindings as $key => $value) {
        //if(is_bool($value))
        $statement->bindValue(
            is_string($key) ? $key : $key + 1, $value,
            //is_int($value) ? PDO::PARAM_INT : PDO::PARAM_STR
            is_int($value) ? PDO::PARAM_INT : is_bool($value) ? PDO::PARAM_STR : PDO::PARAM_STR
        );
    }
}

After the above changes the error with the boolean values was solved.

But, now I am facing strange issues on the server, when I check the database log error I consistently get the below error.

ERROR: prepared statement "pdo_stmt_00000001" already exists STATEMENT: set names 'utf8' ERROR: prepared statement "pdo_stmt_00000001" does not exist STATEMENT: DEALLOCATE pdo_stmt_00000001

It really was strange, and after exploring the internet I have done the below changes in my database.php file, to disable the prepare statements.

'pgsql' => [
        'driver' => 'pgsql',
        'host' => env('DB_HOST', '127.0.0.1'),
        'port' => env('DB_PORT', '5432'),
        'database' => env('DB_DATABASE', 'forge'),
        'username' => env('DB_USERNAME', 'forge'),
        'password' => env('DB_PASSWORD', ''),
        'charset' => 'utf8',
        'collation' => 'utf8_unicode_ci',
        'prefix' => '',            
        'schema' => 'public',
        'sslmode' => 'prefer',
        'options' => [
            \PDO::ATTR_EMULATE_PREPARES => true
        ]
]

The reason behind seeting ATTR_EMULATE_PREPARES => true is becasue I have set "Transaction" mode in "pgbouncer.ini" file.

Now, to make prepared statements work in Transaction mode would need PgBouncer to keep track of them internally, which it does not do. So only way to keep using PgBouncer in this mode is to disable prepared statements in the client, which in my case is PHP Laravel and I have already handle it in the "database.php" file when the connection is made as shown in above code.

I have tried all the options, which are given in http://www.pgbouncer.org/faq.html#how-to-use-prepared-statements-with-transaction-pooling but it doesnot solve the prepare statment error shown in the database log.

ERROR: prepared statement "pdo_stmt_00000001" already exists STATEMENT: set names 'utf8' ERROR: prepared statement "pdo_stmt_00000001" does not exist STATEMENT: DEALLOCATE pdo_stmt_00000001

Please guide me on the same and what further settings are required for the error. Those errors are on the client production server and we cannot go ahead with those errors in production server.

Please give me your valuable feedback at the earliest as I am facing the issue since 5 days and try with all the options that come across.

Thanks!

Answer 1

1) First, you need to change the PDO option you are giving in the options in the pgsql array of your database.php the right way is as given below.

'pgsql' => [
        'driver' => 'pgsql',
        'host' => env('DB_HOST', '127.0.0.1'),
        'port' => env('DB_PORT', '5434'),
        'database' => env('DB_DATABASE', 'forge'),
        'username' => env('DB_USERNAME', 'forge'),
        'password' => env('DB_PASSWORD', ''),           
        'charset' => 'utf8',
        'prefix' => '',
        'schema' => 'public',
        'sslmode' => 'prefer',
        'options' => [
            PDO::ATTR_EMULATE_PREPARES => true
        ]       
        ]

2) Second, and the most important thing is to make sure that you use set the "ATTR_EMULATE_PREPARES" to "true" with each database connection you try to connect in your Database.php file.

For example,

'test' => [
        'driver' => 'pgsql',
        'host' => env('test', '127.0.0.1'),
        'port' => env('test', '5434'),
        'database' => env('DB_TEST_DATABASE', 'test'),
        'username' => env('DB_USERNAME', 'test'),
        'password' => env('DB_PASSWORD', ''),
        'charset' => 'utf8',
        'prefix' => '',
        'schema' => 'public',
        'sslmode' => 'prefer',
        'options' => [
            PDO::ATTR_EMULATE_PREPARES => true
        ]           
    ],
'test1' => [
        'driver' => 'pgsql',
        'host' => env('test1', '127.0.0.1'),
        'port' => env('test1', '5434'),
        'database' => env('DB_TEST1_DATABASE', 'test1'),
        'username' => env('DB_USERNAME', 'test'),
        'password' => env('DB_PASSWORD', ''),
        'charset' => 'utf8',
        'prefix' => '',
        'schema' => 'public',
        'sslmode' => 'prefer',
        'options' => [
            PDO::ATTR_EMULATE_PREPARES => true
        ]           
    ]

Please make sure to use the "ATTR_EMULATE_PREPARES" to true for each database connection you make in your application, in your comments you make connection with only "pgsql" which emphasis for postgres sql connection only, and not with the database that your application communicates which is in postgres.

Hope this helps you to resolve your query. Enjoy!!!