Cannot execute INSERT in a read-only transaction - Laravel read/write

Question

I'm trying to set a read/write connection in Laravel 4.2.6 and I cannot perform INSERT, but I can do UPDATE and DELETE transactions. The message that I get is:

SQLSTATE[25006]: Read only sql transaction: 7 ERROR:  cannot execute INSERT in a read-only transaction

The configuration of the database is:

'connections' => [
    'pgsql' => [
        'read'     => [
            'host' => '192.168.22.10'
        ],
        'write'     => [
            'host' => '192.168.33.10'
        ],
        'driver'   => 'pgsql',
        'database' => 'simpo',
        'username' => 'postgres',
        'password' => 'xxx',
        'charset'  => 'utf8',
        'prefix'   => '',
        'schema'   => 'public',
    ],
]

I'm using Postgres 9.3 with 2 server (Virtual machines). The replication works fine and is a simple binary or "Hot Standby" replication (Master/Slave), where the stand by machine can only make read operation.

EDIT:

I tested using query builder and the INSERT works fine, but with Eloquent fails.

I'm thinking that maybe is a BUG with the connection in the Eloquent model but the unusual thing is that the only INSERT operation fails and not the UPDATE OR DELETE, so that make me think that is some operation that only happen when a INSERT is perform (Maybe with the ID that is generated?)

EDIT 2:

I finally noticed the problem. The error happens when Laravel try to insert and fetch the Id of the row. The problem happen because the framework call a 'select' method but this select execute and "INSERT ... returning 'ID'", so if the machine can only execute SELECT transaction fails because this SELECT come with a INSERT.

I make a pull request where I check if the select method start with a 'insert' operation, and depending of that get the correct connection https://github.com/laravel/framework/pull/4914

Sounds like your app is trying to do query routing, where it automatically sends read/write queries to the master and read-only queries to the replica(s). And it's doing it wrong. — Craig Ringer, Jul 01 '14 at 02:12

score 0 · Accepted Answer · answered Sep 23 '14 at 19:07

0

The Bug was finally fixed and I believe that should not be problems since version 4.2.9. Checkout the same link of the pull request.

answered Sep 23 '14 at 19:07

Rodrigo Gauzmanf

2,517
1
23
26

Cannot execute INSERT in a read-only transaction - Laravel read/write

1 Answers1