[Dbix-class] can't get last insert id on create() with mysql

[Peter Rabbitson]

Robert Stockdale wrote:
> Hello,
> 
> I have a mysql table with a two column primary key which I am attempting
> to insert into using the create() method. One of the PK field values is
> being set using a subquery, for example:
> 
>     # code to set other fields here...
>     $fields->{id} = \'(SELECT FIELD1 FROM (SELECT MAX(FIELD1) + 1 FIELD1
> FROM TABLE1) TEMP_T)';
>     $c->model('DB::Table1')->create($fields);
> 
> The record is created as expected but the code blows up with "Can't get
> last insert id" error.
> 
> Based on docs from DBIx::Class::Manual::Troubleshooting  I thought the
> issue might be with DBI or DBD::mysql so I updated DBI to the latest
> version from CPAN and I updated DBD::mysql to the latest version in the
> fedora repos (having trouble w/ the CPAN install for this one so I can't
> get the absolute newest version but its pretty close -- 4.005).
> 
> Is this an issue with DBIC, DBI, or DBD::mysql? I was hoping someone
> might be able to point me in the right direction for diagnosing or
> working around this issue. Or if it looks like I am going about this all
> wrong, please let me know that as well. :-)

Several things strike me as wrong:

*) Why is the primary key a composite, if the above subquery guarantees that
the single column 'id' will always be unique?
*) Why not simply use autoincrementing columns, but instead incur the speed
penalty of a subquery on every insert?
*) Bonus - if you have a field 'id' and a multicolumn PK, you need to
redefine the accessor for this particular column. Otherwise you're
overloading the internal 'id' method[1].

If you're hellbent on using the subquery - you need to roughly do this:

my $next_id_sth = $schema->storage->dbh->prepare_cached (<your sql>)

....
$schema->txn_do (sub {
	my ($id) = ${$next_id_sth->selectall_arrayref}[0];
	...
	$fields->{id} = $id
	...
});


The reason it doesn't work the way you did it:
1) DBIC executes the SQL you expect when creating the row
2) DBIC then runs through the columns of the just created object,
and makes sure all PK columns are defined non-ref values
3) It finds something odd and calls $storage->_dbh_last_insert_id,
since a full PK condition is necessary to do any further work with
this object (i.e. when you update/delete you need all the values to
make sure you're working with the correct row)
4) Of course the storage driver does not return anything, as the
DBD driver did not use an autoincrement, thus has no idea what the
value of the subquery was
5) DBIC dies instead of leaving you with a broken row object

Cheers