DBIC Storage::DBI::SQLite insert DATETIME set_on_create exception error ...

5 messages

DBIC Storage::DBI::SQLite insert DATETIME set_on_create exception error ...

Reply Threaded

Hi there.

Since I upgraded DBIx::Class from 0.08103 to 0.08106, I've been having
problems inserting records using SQLite, where a column is defined with
data_type 'DATETIME', is_nullable => 0 and set_on_create => 1;

I've defined a database with the following columns:

__PACKAGE__->add_columns(
username => {
data_type => 'VARCHAR',
size => 25,
is_nullable => 0,
is_foreign_key => 1,
},
group_id => {
data_type => 'VARCHAR',
size => 64,
is_nullable => 0,
is_foreign_key => 1,
},
from_date => {
data_type => 'DATETIME',
is_nullable => 0,
timezone => 'UTC',
set_on_create => 1,
},
thru_date => {
data_type => 'DATETIME',
is_nullable => 1,
timezone => 'UTC',
},
);

The following call:

$schema->populate(
'UserLoginSecurityGroup', [
[qw/ username group_id /],
[ 'admin', 'FULLADMIN' ],
[ 'admin', 'PARTYADMIN' ],
[ 'admin', 'ACCOUNT_OWNER' ],
]
);

causes an exception with the following error:

kiffin@kiffin-laptop:~/opencmt $ DBIC_TRACE=1 prove -lv t/schema_File.t
t/schema_File.t .. CREATE TABLE bkm_mark (
mark_id INTEGER PRIMARY KEY NOT NULL,
created_by INT(11) NOT NULL,
title VARCHAR(64),INSERT INTO user_login ( email, party_id, password,
username) VALUES ( ?, ?, ?, ? ): '0', '2', '1', '3'

<...>

INSERT INTO user_login ( email, party_id, password, username) VALUES
( ?, ?, ?, ? ): '0', '2', '1', '3'
INSERT INTO user_login_security_group ( group_id, username) VALUES
( ?, ? ): '0', '1'
user_login_security_group.from_date may not be NULL for populate slice:
{
group_id => "FULLADMIN",
username => "admin"
}
at /usr/local/share/perl/5.10.0/DBIx/Class/Schema.pm line 1010

DBIx::Class::Schema::throw_exception('CMT::Store=HASH(0x88c16a0)',
'user_login_security_group.from_date may not be NULL for popul...')
called at /usr/local/share/perl/5.10.0/DBIx/Class/Storage.pm line 122

DBIx::Class::Storage::throw_exception('DBIx::Class::Storage::DBI::SQLite=HASH(0x9716f40)', 'user_login_security_group.from_date may not be NULL for popul...') called at /usr/local/share/perl/5.10.0/DBIx/Class/Storage/DBI.pm line 1073

DBIx::Class::Storage::DBI::insert_bulk('DBIx::Class::Storage::DBI::SQLite=HASH(0x9716f40)', 'DBIx::Class::ResultSource::Table=HASH(0x979a0d0)', 'ARRAY(0x8f373f8)', 'ARRAY(0x8f379b8)') called at /usr/local/share/perl/5.10.0/DBIx/Class/ResultSet.pm line 1615

DBIx::Class::ResultSet::populate('CMT::Store::Base::ResultSet::Serialize=HASH(0x9be16a0)', 'ARRAY(0x9b282b0)') called at /usr/local/share/perl/5.10.0/DBIx/Class/Schema.pm line 764
DBIx::Class::Schema::populate('CMT::Store=HASH(0x88c16a0)',
'UserLoginSecurityGroup', 'ARRAY(0x9b282b0)') called
at /home/kiffin/opencmt/lib/CMT/Store.pm line 243
CMT::Store::seed('CMT::Store=HASH(0x88c16a0)') called at
t/schema_File.t line 12
Dubious, test returned 2 (wstat 512, 0x200)
No subtests run

Test Summary Report
-------------------
t/schema_File.t (Wstat: 512 Tests: 0 Failed: 0)
Non-zero exit status: 2
Parse errors: No plan found in TAP output
Files=1, Tests=0, 1 wallclock secs ( 0.03 usr 0.01 sys + 0.83 cusr
0.06 csys = 0.93 CPU)
Result: FAIL

address VARCHAR(64) NOT NULL,
description VARCHAR(256)
):
...

I noticed in the CHANGES file the following text:

0.08106 2009-06-11 21:42:00 (UTC)
- Switched SQLite storage driver to DateTime::Format::SQLite
(proper timezone handling)
- Fix more test problems

Is this a known problem, and if so how can I get this fixed?

Regards,
Kiffin

--
Kiffin Gish <[hidden email]>
Gouda, The Netherlands

_______________________________________________
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@...

Peter Rabbitson-2

Re: DBIC Storage::DBI::SQLite insert DATETIME set_on_create exception error ...

Reply Threaded

Kiffin Gish wrote:

> Hi there.
>
> Since I upgraded DBIx::Class from 0.08103 to 0.08106, I've been having
> problems inserting records using SQLite, where a column is defined with
> data_type 'DATETIME', is_nullable => 0 and set_on_create => 1;
>
> I've defined a database with the following columns:
>
> __PACKAGE__->add_columns(
> username => {
> data_type => 'VARCHAR',
> size => 25,
> is_nullable => 0,
> is_foreign_key => 1,
> },
> group_id => {
> data_type => 'VARCHAR',
> size => 64,
> is_nullable => 0,
> is_foreign_key => 1,
> },
> from_date => {
> data_type => 'DATETIME',
> is_nullable => 0,
> timezone => 'UTC',
> set_on_create => 1,
> },
> thru_date => {
> data_type => 'DATETIME',
> is_nullable => 1,
> timezone => 'UTC',
> },
> );
>
> The following call:
>
> $schema->populate(
> 'UserLoginSecurityGroup', [
> [qw/ username group_id /],
> [ 'admin', 'FULLADMIN' ],
> [ 'admin', 'PARTYADMIN' ],
> [ 'admin', 'ACCOUNT_OWNER' ],
> ]
> );

> INSERT INTO user_login ( email, party_id, password, username) VALUES
> ( ?, ?, ?, ? ): '0', '2', '1', '3'
> INSERT INTO user_login_security_group ( group_id, username) VALUES
> ( ?, ? ): '0', '1'
> user_login_security_group.from_date may not be NULL for populate slice:
> {
> group_id => "FULLADMIN",
> username => "admin"
> }
> at /usr/local/share/perl/5.10.0/DBIx/Class/Schema.pm line 1010
>
> DBIx::Class::Schema::throw_exception('CMT::Store=HASH(0x88c16a0)',
> 'user_login_security_group.from_date may not be NULL for popul...')
> called at /usr/local/share/perl/5.10.0/DBIx/Class/Storage.pm line 122
>
>
> I noticed in the CHANGES file the following text:
>
> 0.08106 2009-06-11 21:42:00 (UTC)
> - Switched SQLite storage driver to DateTime::Format::SQLite
> (proper timezone handling)
> - Fix more test problems
>

The correct changelog entry to look for is:
- Fixed corner case when populate() erroneously falls back to
create()

Basically before create() was being called instead of populate, which
properly invoked the set_on_create flag. populate() in void context
explicitly does not do this. Either switch the context, or supply the
value.

_______________________________________________
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@...

Kiffin Gish

Re: DBIC Storage::DBI::SQLite insert DATETIME set_on_create exception error ...

Reply Threaded

So are you saying that I need to replace my call $schema->populate(...)
with something like var $results = $schema->populate(...)?

Doesn't seem very straight-forward to me or am I missing something?

I might also mention that DBIC Storage::DBI::MySQL works fine.

-
Kiffin Gish <[hidden email]>
Gouda, The Netherlands

On Sat, 2009-06-20 at 00:29 +0200, Peter Rabbitson wrote:

> Kiffin Gish wrote:
> > Hi there.
> >
> > Since I upgraded DBIx::Class from 0.08103 to 0.08106, I've been having
> > problems inserting records using SQLite, where a column is defined with
> > data_type 'DATETIME', is_nullable => 0 and set_on_create => 1;
> >
> > I've defined a database with the following columns:
> >
> > __PACKAGE__->add_columns(
> > username => {
> > data_type => 'VARCHAR',
> > size => 25,
> > is_nullable => 0,
> > is_foreign_key => 1,
> > },
> > group_id => {
> > data_type => 'VARCHAR',
> > size => 64,
> > is_nullable => 0,
> > is_foreign_key => 1,
> > },
> > from_date => {
> > data_type => 'DATETIME',
> > is_nullable => 0,
> > timezone => 'UTC',
> > set_on_create => 1,
> > },
> > thru_date => {
> > data_type => 'DATETIME',
> > is_nullable => 1,
> > timezone => 'UTC',
> > },
> > );
> >
> > The following call:
> >
> > $schema->populate(
> > 'UserLoginSecurityGroup', [
> > [qw/ username group_id /],
> > [ 'admin', 'FULLADMIN' ],
> > [ 'admin', 'PARTYADMIN' ],
> > [ 'admin', 'ACCOUNT_OWNER' ],
> > ]
> > );
>
> > INSERT INTO user_login ( email, party_id, password, username) VALUES
> > ( ?, ?, ?, ? ): '0', '2', '1', '3'
> > INSERT INTO user_login_security_group ( group_id, username) VALUES
> > ( ?, ? ): '0', '1'
> > user_login_security_group.from_date may not be NULL for populate slice:
> > {
> > group_id => "FULLADMIN",
> > username => "admin"
> > }
> > at /usr/local/share/perl/5.10.0/DBIx/Class/Schema.pm line 1010
> >
> > DBIx::Class::Schema::throw_exception('CMT::Store=HASH(0x88c16a0)',
> > 'user_login_security_group.from_date may not be NULL for popul...')
> > called at /usr/local/share/perl/5.10.0/DBIx/Class/Storage.pm line 122
> >
> >
> > I noticed in the CHANGES file the following text:
> >
> > 0.08106 2009-06-11 21:42:00 (UTC)
> > - Switched SQLite storage driver to DateTime::Format::SQLite
> > (proper timezone handling)
> > - Fix more test problems
> >
>
> The correct changelog entry to look for is:
> - Fixed corner case when populate() erroneously falls back to
> create()
>
> Basically before create() was being called instead of populate, which
> properly invoked the set_on_create flag. populate() in void context
> explicitly does not do this. Either switch the context, or supply the
> value.
>
> _______________________________________________
> 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@...

_______________________________________________
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@...

Peter Rabbitson-2

Re: DBIC Storage::DBI::SQLite insert DATETIME set_on_create exception error ...

Reply Threaded

Kiffin Gish wrote:
> So are you saying that I need to replace my call $schema->populate(...)
> with something like var $results = $schema->populate(...)?
>
> Doesn't seem very straight-forward to me or am I missing something?

Yes you are missing something. From the populate() POD:

Please note an important effect on your data when choosing between void
and wantarray context. Since void context goes straight to insert_bulk
in DBIx::Class::Storage::DBI this will skip any component that is
overriding insert. So if you are using something like DBIx-Class-UUIDColumns
to create primary keys for you, you will find that your PKs are empty. In
this case you will have to use the wantarray context in order to create
those values.

set_on_create => 1 *is* filled in in the create() chain, which you
explicitly bypass.

> I might also mention that DBIC Storage::DBI::MySQL works fine.
>

Most likely your column allow NULL in mysql - this is why it appears
to work (but in fact you don't insert anything).

P.S. Also don't top post

>
>
> On Sat, 2009-06-20 at 00:29 +0200, Peter Rabbitson wrote:
>> Kiffin Gish wrote:
>>> Hi there.
>>>
>>> Since I upgraded DBIx::Class from 0.08103 to 0.08106, I've been having
>>> problems inserting records using SQLite, where a column is defined with
>>> data_type 'DATETIME', is_nullable => 0 and set_on_create => 1;
>>>
>>> I've defined a database with the following columns:
>>>
>>> __PACKAGE__->add_columns(
>>> username => {
>>> data_type => 'VARCHAR',
>>> size => 25,
>>> is_nullable => 0,
>>> is_foreign_key => 1,
>>> },
>>> group_id => {
>>> data_type => 'VARCHAR',
>>> size => 64,
>>> is_nullable => 0,
>>> is_foreign_key => 1,
>>> },
>>> from_date => {
>>> data_type => 'DATETIME',
>>> is_nullable => 0,
>>> timezone => 'UTC',
>>> set_on_create => 1,
>>> },
>>> thru_date => {
>>> data_type => 'DATETIME',
>>> is_nullable => 1,
>>> timezone => 'UTC',
>>> },
>>> );
>>>
>>> The following call:
>>>
>>> $schema->populate(
>>> 'UserLoginSecurityGroup', [
>>> [qw/ username group_id /],
>>> [ 'admin', 'FULLADMIN' ],
>>> [ 'admin', 'PARTYADMIN' ],
>>> [ 'admin', 'ACCOUNT_OWNER' ],
>>> ]
>>> );
>>> INSERT INTO user_login ( email, party_id, password, username) VALUES
>>> ( ?, ?, ?, ? ): '0', '2', '1', '3'
>>> INSERT INTO user_login_security_group ( group_id, username) VALUES
>>> ( ?, ? ): '0', '1'
>>> user_login_security_group.from_date may not be NULL for populate slice:
>>> {
>>> group_id => "FULLADMIN",
>>> username => "admin"
>>> }
>>> at /usr/local/share/perl/5.10.0/DBIx/Class/Schema.pm line 1010
>>>
>>> DBIx::Class::Schema::throw_exception('CMT::Store=HASH(0x88c16a0)',
>>> 'user_login_security_group.from_date may not be NULL for popul...')
>>> called at /usr/local/share/perl/5.10.0/DBIx/Class/Storage.pm line 122
>>>
>>>
>>> I noticed in the CHANGES file the following text:
>>>
>>> 0.08106 2009-06-11 21:42:00 (UTC)
>>> - Switched SQLite storage driver to DateTime::Format::SQLite
>>> (proper timezone handling)
>>> - Fix more test problems
>>>
>> The correct changelog entry to look for is:
>> - Fixed corner case when populate() erroneously falls back to
>> create()
>>
>> Basically before create() was being called instead of populate, which
>> properly invoked the set_on_create flag. populate() in void context
>> explicitly does not do this. Either switch the context, or supply the
>> value.
>>

Kiffin Gish

Re: DBIC Storage::DBI::SQLite insert DATETIME set_on_create exception error ...

Reply Threaded

Thanks for the explanation, it now makes sense. Although at first glance
it is not very obvious to me.

On Sat, 2009-06-20 at 14:25 +0200, Peter Rabbitson wrote:

> Kiffin Gish wrote:
> > So are you saying that I need to replace my call $schema->populate(...)
> > with something like var $results = $schema->populate(...)?
> >
> > Doesn't seem very straight-forward to me or am I missing something?
>
> Yes you are missing something. From the populate() POD:
>
> Please note an important effect on your data when choosing between void
> and wantarray context. Since void context goes straight to insert_bulk
> in DBIx::Class::Storage::DBI this will skip any component that is
> overriding insert. So if you are using something like DBIx-Class-UUIDColumns
> to create primary keys for you, you will find that your PKs are empty. In
> this case you will have to use the wantarray context in order to create
> those values.
>
> set_on_create => 1 *is* filled in in the create() chain, which you
> explicitly bypass.
>
> > I might also mention that DBIC Storage::DBI::MySQL works fine.
> >
>
> Most likely your column allow NULL in mysql - this is why it appears
> to work (but in fact you don't insert anything).
>
> P.S. Also don't top post
>
> >
> >
> > On Sat, 2009-06-20 at 00:29 +0200, Peter Rabbitson wrote:
> >> Kiffin Gish wrote:
> >>> Hi there.
> >>>
> >>> Since I upgraded DBIx::Class from 0.08103 to 0.08106, I've been having
> >>> problems inserting records using SQLite, where a column is defined with
> >>> data_type 'DATETIME', is_nullable => 0 and set_on_create => 1;
> >>>
> >>> I've defined a database with the following columns:
> >>>
> >>> __PACKAGE__->add_columns(
> >>> username => {
> >>> data_type => 'VARCHAR',
> >>> size => 25,
> >>> is_nullable => 0,
> >>> is_foreign_key => 1,
> >>> },
> >>> group_id => {
> >>> data_type => 'VARCHAR',
> >>> size => 64,
> >>> is_nullable => 0,
> >>> is_foreign_key => 1,
> >>> },
> >>> from_date => {
> >>> data_type => 'DATETIME',
> >>> is_nullable => 0,
> >>> timezone => 'UTC',
> >>> set_on_create => 1,
> >>> },
> >>> thru_date => {
> >>> data_type => 'DATETIME',
> >>> is_nullable => 1,
> >>> timezone => 'UTC',
> >>> },
> >>> );
> >>>
> >>> The following call:
> >>>
> >>> $schema->populate(
> >>> 'UserLoginSecurityGroup', [
> >>> [qw/ username group_id /],
> >>> [ 'admin', 'FULLADMIN' ],
> >>> [ 'admin', 'PARTYADMIN' ],
> >>> [ 'admin', 'ACCOUNT_OWNER' ],
> >>> ]
> >>> );
> >>> INSERT INTO user_login ( email, party_id, password, username) VALUES
> >>> ( ?, ?, ?, ? ): '0', '2', '1', '3'
> >>> INSERT INTO user_login_security_group ( group_id, username) VALUES
> >>> ( ?, ? ): '0', '1'
> >>> user_login_security_group.from_date may not be NULL for populate slice:
> >>> {
> >>> group_id => "FULLADMIN",
> >>> username => "admin"
> >>> }
> >>> at /usr/local/share/perl/5.10.0/DBIx/Class/Schema.pm line 1010
> >>>
> >>> DBIx::Class::Schema::throw_exception('CMT::Store=HASH(0x88c16a0)',
> >>> 'user_login_security_group.from_date may not be NULL for popul...')
> >>> called at /usr/local/share/perl/5.10.0/DBIx/Class/Storage.pm line 122
> >>>
> >>>
> >>> I noticed in the CHANGES file the following text:
> >>>
> >>> 0.08106 2009-06-11 21:42:00 (UTC)
> >>> - Switched SQLite storage driver to DateTime::Format::SQLite
> >>> (proper timezone handling)
> >>> - Fix more test problems
> >>>
> >> The correct changelog entry to look for is:
> >> - Fixed corner case when populate() erroneously falls back to
> >> create()
> >>
> >> Basically before create() was being called instead of populate, which
> >> properly invoked the set_on_create flag. populate() in void context
> >> explicitly does not do this. Either switch the context, or supply the
> >> value.
> >>
>
> _______________________________________________
> 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@...