[Dbix-class] Unexpected duplicate-key error from find_or_create

[Peter Rabbitson]

Lee Goddard wrote:
> I do not often find myself using DBIx::Class, so please forgive any
> naivety or silliness in my question, but I would appreciate your help.
> 
>   my $o = $schema->resultset( $field )->find_or_create(
>     value => $val
>   );
> 
> The above code has been working well over a variety of data, until just
> now, when it died with the following words (where `331_chrs` represents
> a string of 331 characters):
> 
> SELECT me.id, me.value FROM licence me WHERE ( ( me.value = ? ) ):
> '331_chrs'
> INSERT INTO licence (value) VALUES (?): '331_chrs'
> DBIx::Class::ResultSet::find_or_create():
>   DBI Exception:
>   DBD::mysql::st execute failed:
>   Duplicate entry '%s' for key 2
>   [for Statement "INSERT INTO licence (value) VALUES (?)"] at ...
> 
> I have not yet taken to reading the source code, because I this is the
> one error I had hoped to avoid by using find_or_create.
> 
> The MySQL table in question has only two columns, one PRIMARY and one
> UNIQUE, and I'm developing with the fantastic DBIx::Class::Schema::Loader.
> 
> Have you seen such behaviour before? I am making a common error? Have I
> misconstrued something in the manual? Should I be reading the source?
> Could this be DBIx::Class::Schema::Loader...?
> 

Most likely this is MySQL allowing you to do something stupid and
feeling the fallout of it. If this is new code not chained to legacy
data, it would be _immensely_ beneficial and stress relieving to add the
following to the options of your ->connect arguments (the 4th element):

{
   ...
   on_connect_do => [q/SET SQL_MODE = 'TRADITIONAL'/, q/SET
SQL_AUTO_IS_NULL = 0/],
   ...
}