Annoying "Record cannot be created because it already exists"

Question

Annoying "Record cannot be created because it already exists"

Tomasz Zadora Jun '19

Created Jun '19	Last Reply Jun '19	Replies 3	Views 274	Votes 0

Tomasz Zadora 10.4k

Jun '19

Hello,

I want to do quick import between databases, i've created service in DI for database im reading from. Before executing code below im doing TRUNCATE on local SQL table.

$entity->setReadConnectionService( 'service_for_reading' );
$cursor = $entity->find();
foreach ($cursor as $row) {
    $row->create();
}

And im getting "Record cannot be created because it already exists".

Doing this doesn't help either:

foreach ($cursor as $row) {
    $newEntity = new \EntityClass();
    $newEntity->create( $row->toArray() );
}

ORM should recognise that read and write connections are diffrent before checking if record already exists, or there should be way to switch this checking off.

Lajos Bencz
77.7k

Accepted
answer

edited Jun '19
Jun '19

https://github.com/phalcon/cphalcon/blob/master/phalcon/Mvc/Model.zep#L951

You could override the create method:

class MyBaseModel extends \Phalcon\Mvc\Model
{
    public function create()
    {
        if($this->_exists($this->getModelsMetaData(), $this->getWriteConnection())) {
            // generate error
            return false;
        }
        return $this->save();
    }
}

But it's an antipattern, since you could have a protocol write-only connection from which you cannot read at all.

Lajos Bencz · Answer 1 · 2019-06-24T01:15:08-07:00

ORM should recognise that read and write connections are diffrent before checking if record already exists

From the looks of it, you are trying to implement a DB replication logic from scratch, which the ORM was never designed to handle.

The reason we can specify read/write connection is because of DB replication like Galera

Tomasz Zadora · Answer 2 · 2019-06-24T12:41:48-07:00

I just need to disable that checking if record already exists, is that possible trough Model configuration?

Tomasz Zadora · Answer 3 · 2019-06-25T06:13:33-07:00

Thanks, i'll try.

I need just find a proper way to distinguish between normal operations and import.

Or I'll just do it like that:

class MyBaseModelForImport extends MyBaseModel

In MyBaseModelForImport I'll just overrid the create() method and that model will be used for import only.

Tomasz Zadora · Answer 4 · 2019-06-25T08:14:57-07:00

Regarding https://github.com/phalcon/cphalcon/blob/master/phalcon/Mvc/Model.zep#L951

there is still problem here in save method:

let exists = this->_exists(metaData, readConnection, table);

        if exists {
            let this->operationMade = self::OP_UPDATE;
        } else {
            let this->operationMade = self::OP_CREATE;
        }

so I rather need to override _exists method than create, anyway thanks for pointing me way to do it.

Dylan Anderson · Answer 5 · 2019-06-25T13:01:11-07:00

I know this is already marked as solved, but from what you've explained, writing code for this seems unnecessary.

Is the table structure the same between databasese? Why not just do an export from 1 database, then import into the other? Or a simple plain SQL statement:

INSERT INTO newdatabase.newtable SELECT * FROM olddatabase.oldtable

Tomasz Zadora · Answer 6 · 2019-06-25T14:26:37-07:00

Yes it's the same structure, but databases are on different machines. There is probably some way to do it by one SQL command, but I just needed quick solution for few tables, to use from time-to-time, not some complex system. But it must be done from www page by clicking link not by executing command at server console. Anyway it's working (I did it in even other way bu it doesnt matter) so I will not change it now.

Another thing is: doing it in plain SQL forbids any processing rows in PHP before importing,

BTW That checking if record already exists was problematic for me in few other cases. Framework creators should seriously consider option for switching it off (default on of course). That option could just change behavior of _exist method so it would return always false