[Dbix-class] The Trouble With Inserts

Eric Wright info at rapidsynergy.com
Wed Jan 7 16:28:02 GMT 2009


Ah-ha! I had wondered if MySQL might somehow be the culprit here.

Thanks Ash! That's a very valuable bit of info. We'll probably be moving to
Postgres down the road eventually because of issues like this...

-Eric

On Wed, Jan 7, 2009 at 10:14 AM, Ash Berlin <ash_cpan at firemirror.com> wrote:

>
> 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 inser=
ts
>> 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 prim=
ary
>> key I get the last record inserted. This behavior does not fix itself un=
less
>> I reboot Catalyst.
>>
>> my $rec =3D $c->model("DB::MyTable")->new({ value =3D> $some_value });
>> $rec->insert;
>>
>> Later on...
>>
>> my $record =3D $c->model("DB::MyTable")->find_or_new({ id =3D> undef });
>> #Returns last inserted record!
>>
>
> This is MySQL being hateful:
>
>
> http://mirror.facebook.com/mysql/doc/refman/5.0/en/server-session-variabl=
es.html#option_mysqld_sql_auto_is_null
>
> SQL_AUTO_IS_NULL =3D {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 =3D 0;' to your on_connect_do sql.
>
>
>
> Remember kids: MySQL only looks like a database.
>
>
>
> -ash
>
>
>
> _______________________________________________
> List: http://lists.scsys.co.uk/cgi-bin/mailman/listinfo/dbix-class
> IRC: irc.perl.org#dbix-class
> SVN: http://dev.catalyst.perl.org/repos/bast/DBIx-Class/
> Searchable Archive:
> http://www.grokbase.com/group/dbix-class@lists.scsys.co.uk
>



-- =

-----------------------------------------------------------
Eric Wright
Rapid Synergy LLC
Web Development, Software & Consulting
V: 203.758.9270  F: 203.725.0853
http://www.rapidsynergy.com
-----------------------------------------------------------
-------------- next part --------------
An HTML attachment was scrubbed...
URL: http://lists.scsys.co.uk/pipermail/dbix-class/attachments/20090107/1a3=
b86ee/attachment.htm


More information about the DBIx-Class mailing list