Beefy Boxes and Bandwidth Generously Provided by pair Networks
Syntactic Confectionery Delight

Update Primary Key with DBIx::Class

by phildeman (Scribe)
on Sep 18, 2019 at 19:43 UTC ( #11106350=perlquestion: print w/replies, xml ) Need Help??

phildeman has asked for the wisdom of the Perl Monks concerning the following question:


I was attempting to update a MySQL table's primary key using DBIx::Class. I do not get any errors executing the update.
However, when I check the table using Workbench or MySQL Client, it shows the old value.

Is DBIx::Class restricted from updating primary keys. It can be done with DBI.

Here is a snippet of code to perform the update:

use My::Data; my $schema = "My::Data"; my $acct = $schema->resultset( 'Accounts')->search({ acctid => 'pv001' + })->single; if($acct) { $acct->acctid( 'pv002' ); $acct->update; } else { print "Could not find account to update!\n\n"; }

As I stated, when I check the table, the value 'pv001' is still in the database. I searched for 'pv002', I could not find any record
with that value as the primary key.

Has anyone encounter this issue where DBIx::Class will not update the primary key? If yes, how did you resolve this issue?
I am hoping there is a resolution, before I use DBI.



Replies are listed 'Best First'.
Re: Update Primary Key with DBIx::Class
by jcb (Chaplain) on Sep 18, 2019 at 21:22 UTC

    While I normally use DBI and occasionally roll my own custom application-specific ORM layers when I need them, I will suggest checking the DBIx::Class documentation to see if there is a "commit" step that you might be missing. Your description sounds like you are finding the record in the DB and updating the copy in your program (and possibly in a pending transaction in the DB server) but not actually ever issuing the SQL COMMIT to make the changes durable in the DB.

      This is most likely to be it. (An SQL monitor, if you have one, can confirm it.) You should be able to modify the primary-key value like any other column, but there could well be auto-commit behavior. As an experiment, try changing other columns with your existing code. (Use a very distinct value then search the whole table to see if it shows up anywhere at all.) If it doesn't, it's almost certain to be a missing commit.
Re: Update Primary Key with DBIx::Class
by talexb (Canon) on Sep 18, 2019 at 21:23 UTC

    It may not be possible to change the value of a record's primary key.

    Perhaps what you can do instead is to create a new record from the old record, but store it with a new primary key -- then delete the record with the old primary key. That will work out to the result it sounds like you are looking for.

    Alex / talexb / Toronto

    Thanks PJ. We owe you so much. Groklaw -- RIP -- 2003 to 2013.

Re: Update Primary Key with DBIx::Class (discard_changes)
by 1nickt (Abbot) on Sep 19, 2019 at 09:54 UTC

    Hi, your problem can be solved by using either of:

    if($acct) { $acct->acctid( 'pv002' ); $acct->update; $acct->discard_changes; # add this }
    ... see the doc for discard_changes(), which has a somewhat counter-intuitive name but acts as a "commit" if you've made changes to your result object in your code.

    Or else:

    if($acct) { $acct->update({acctid => 'pv002'}); }
    ... which updates the record in the database immediately as well as in your program.

    To find out what queries DBIx::Class is sending to your database, run your program with DBIC_TRACE=1 set in your environment.

    Hope this helps!

    Update: fixed missing clsoing brace, thanks pryrt

    The way forward always starts with a minimal test.

Log In?

What's my password?
Create A New User
Node Status?
node history
Node Type: perlquestion [id://11106350]
Approved by johngg
Front-paged by 1nickt
and the web crawler heard nothing...

How do I use this? | Other CB clients
Other Users?
Others wandering the Monastery: (11)
As of 2019-10-21 10:12 GMT
Find Nodes?
    Voting Booth?