[Dbix-class] The Trouble With Inserts

[Ash Berlin]

On 7 Jan 2009, at 14:15, Eric Wright wrote:

> I've been working with Catalyst and DBIx::Class quite a bit lately  
> and think that they are both awesome. However as my app has been  
> growing in complexity I've started to notice some troubling quirks  
> with my DB inserts and selects. I'm not sure if it's a Catalyst  
> issue or a DBIC issue or an issue with my code. I am using MySQL  
> 5.0.27.
>
> In a nutshell, what seems to be a recurring pattern is that when a  
> record is inserted with an auto incremented PK, that record  
> *temporarily* shows up in a select on that same table. For instance,  
> if I insert a record into some table and then do a find_or_new on  
> that same table for an undefined primary key I get the last record  
> inserted. This behavior does not fix itself unless I reboot Catalyst.
>
> my $rec = $c->model("DB::MyTable")->new({ value => $some_value });
> $rec->insert;
>
> Later on...
>
> my $record = $c->model("DB::MyTable")->find_or_new({ id => undef });  
> #Returns last inserted record!

This is MySQL being hateful:

http://mirror.facebook.com/mysql/doc/refman/5.0/en/server-session-variables.html#option_mysqld_sql_auto_is_null

SQL_AUTO_IS_NULL = {0 | 1}

If set to 1 (the default), you can find the last inserted row for a  
table that contains an AUTO_INCREMENT column by using the following  
construct:

WHERE auto_increment_column IS NULL
This behavior is used by some ODBC programs, such as Access.



I suggest you add 'SET SQL_AUTO_IS_NULL = 0;' to your on_connect_do sql.



Remember kids: MySQL only looks like a database.



-ash