Update Primary Key with DBIx::Class

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";
}
[download]

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.

Thanks.

-Phil-

Comment on Update Primary Key with DBIx::Class Download Code

Replies are listed 'Best First'.
Re: Update Primary Key with DBIx::Class by jcb (Parson) 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.	[reply] [d/l]
Re^2: Update Primary Key with DBIx::Class by Anonymous Monk on Sep 19, 2019 at 02:21 UTC
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.	[reply]
Re: Update Primary Key with DBIx::Class by talexb (Chancellor) 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.	[reply]
Re: Update Primary Key with DBIx::Class (discard_changes) by 1nickt (Canon) 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 }` [download] ... 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'}); }` [download] ... 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.	[reply] [d/l] [select]