Mailing List Archive: SQL error: invalid input syntax for type boolean

SQL error: invalid input syntax for type boolean

Apr 21, 2011, 1:21 PM

Post #1 of 6 (6446 views)

Bricoleurs,

Has anyone seen an error like the one below? The element_type__id =
1251 is our "Thumbnail" element which is essentially just a container
for a Related Media. I'm guessing, if I read this error correctly, the
problem is because some code somewhere is attempting to set "displayed"
to "" (empty string) where it should be a boolean. Any ideas?

Thanks,
Nick

[Thu Apr 21 18:47:45 2011] [error] [client 140.141.9.37] Unable to
execute SQL statement: DBD::Pg::st execute failed: ERROR: invalid input
syntax for type boolean: "" [.for Statement "\n UPDATE
story_element\n SET element_type__id = ?, object_instance_id =
?, parent_id = ?, place = ?, object_order = ?, displayed = ?,
related_story__id = ?, related_media__id = ?, active = ?\n WHERE
id = ?\n " with ParamValues: 1='1251', 2='162196', 3='1773753',
4='0', 5='1', 6='', 7=undef, 8='54977', 9='1', 10='1773754'] at
/usr/local/bricolage/lib/Bric/Util/DBI.pm line 1138, <GEN128> line
19983.\n, referer:
https://freestyle.denison.edu/workflow/profile/story/24387
[/usr/local/bricolage/lib/Bric/Util/DBI.pm:1139]
[/usr/local/bricolage/lib/Bric/Biz/Element/Container.pm:2182]
[/usr/local/bricolage/lib/Bric/Biz/Element/Container.pm:1803]
[/usr/local/bricolage/lib/Bric/Biz/Element/Container.pm:2212]
[/usr/local/bricolage/lib/Bric/Biz/Element/Container.pm:1809]
[/usr/local/bricolage/lib/Bric/Biz/Element/Container.pm:2212]
[/usr/local/bricolage/lib/Bric/Biz/Element/Container.pm:1809]
[/usr/local/bricolage/lib/Bric/Biz/Element/Container.pm:2212]
[/usr/local/bricolage/lib/Bric/Biz/Element/Container.pm:1809]
[/usr/local/bricolage/lib/Bric/Biz/Element/Container.pm:2212]
[/usr/local/bricolage/lib/Bric/Biz/Element/Container.pm:1809]
[/usr/local/bricolage/lib/Bric/Biz/Element/Container.pm:2212]
[/usr/local/bricolage/lib/Bric/Biz/Element/Container.pm:1809]
[/usr/local/bricolage/lib/Bric/Biz/Asset/Business.pm:2283]
[/usr/local/bricolage/lib/Bric/Biz/Asset/Business/Story.pm:1917]
[/usr/local/bricolage/lib/Bric/App/Callback/Profile/Story.pm:196]
[/usr/local/share/perl5/Params/CallbackRequest.pm:296]
[/usr/local/share/perl5/MasonX/Interp/WithCallbacks.pm:119]
[/usr/local/share/perl5/HTML/Mason/ApacheHandler.pm:868]
[/usr/local/share/perl5/HTML/Mason/ApacheHandler.pm:822]
[/usr/local/bricolage/lib/Bric/App/Handler.pm:308]
[-e:0]

Re: SQL error: invalid input syntax for type boolean [ In reply to ]

david at kineticode

Apr 22, 2011, 10:02 AM

Post #2 of 6 (6367 views)

Permalink

On Apr 21, 2011, at 1:21 PM, Nick Legg wrote:

> Has anyone seen an error like the one below? The element_type__id = 1251 is our "Thumbnail" element which is essentially just a container for a Related Media. I'm guessing, if I read this error correctly, the problem is because some code somewhere is attempting to set "displayed" to "" (empty string) where it should be a boolean. Any ideas?
>
> Thanks,
> Nick
>
> [Thu Apr 21 18:47:45 2011] [error] [client 140.141.9.37] Unable to execute SQL statement: DBD::Pg::st execute failed: ERROR: invalid input syntax for type boolean: "" [.for Statement "\n UPDATE story_element\n SET element_type__id = ?, object_instance_id = ?, parent_id = ?, place = ?, object_order = ?, displayed = ?, related_story__id = ?, related_media__id = ?, active = ?\n WHERE id = ?\n " with ParamValues: 1='1251', 2='162196', 3='1773753', 4='0', 5='1', 6='', 7=undef, 8='54977', 9='1', 10='1773754'] at /usr/local/bricolage/lib/Bric/Util/DBI.pm line 1138, <GEN128> line 19983.\n, referer:https://freestyle.denison.edu/workflow/profile/story/24387

The `displayed` column is a boolean column. The value specified for it should be either '1' or '0' (with the quotation marks, they must be strings). The value passed here, however, is an empty string ( 6='').

Can you replicate this error? It's clearly a bug.

Best,

David

Re: SQL error: invalid input syntax for type boolean [ In reply to ]

bret at pectopah

Apr 22, 2011, 11:52 AM

Post #3 of 6 (6337 views)

Permalink

I've seen this myself, on a 2.0.1 install using templates from the 1.10
series. Here's how to make it happen:

1. Get a template that creates subelements. (The old
image_resize_and_relate, for example.)
2. From the story profile, trigger a preview, which should create one or
more subelements in the current story. Note that these subelements will
not appear in the UI, because there's no mechanism for updating it if
something happens during preview.
3. Attempt a save or a save and stay. You'll see the error.

What's happening, I think, is that the form submitted by the save does
not contain any information about the new subelements, so there's
nothing passed for the "displayed" field, and that bangs into the
boolean constraint.

If you run do almost the same thing, but trigger the preview from your
workspace or a desk, the subelements get created cleanly and are present
when you go back into the story profile.

My approach was to hide: I just make the thumbnail images themselves
with find_or_create_alternate(), but no longer bother relating them to
the uploaded image container.

The ideal would be to have the preview process finish by refreshing the
story profile update itself, I suppose. AJAXers, would that be hard?

Cheers,

Bret

On Fri, 2011-04-22 at 10:02 -0700, David E. Wheeler wrote:
> On Apr 21, 2011, at 1:21 PM, Nick Legg wrote:
>
> > Has anyone seen an error like the one below? The element_type__id = 1251 is our "Thumbnail" element which is essentially just a container for a Related Media. I'm guessing, if I read this error correctly, the problem is because some code somewhere is attempting to set "displayed" to "" (empty string) where it should be a boolean. Any ideas?
> >
> > Thanks,
> > Nick
> >
> > [Thu Apr 21 18:47:45 2011] [error] [client 140.141.9.37] Unable to execute SQL statement: DBD::Pg::st execute failed: ERROR: invalid input syntax for type boolean: "" [.for Statement "\n UPDATE story_element\n SET element_type__id = ?, object_instance_id = ?, parent_id = ?, place = ?, object_order = ?, displayed = ?, related_story__id = ?, related_media__id = ?, active = ?\n WHERE id = ?\n " with ParamValues: 1='1251', 2='162196', 3='1773753', 4='0', 5='1', 6='', 7=undef, 8='54977', 9='1', 10='1773754'] at /usr/local/bricolage/lib/Bric/Util/DBI.pm line 1138, <GEN128> line 19983.\n, referer:https://freestyle.denison.edu/workflow/profile/story/24387
>
> The `displayed` column is a boolean column. The value specified for it should be either '1' or '0' (with the quotation marks, they must be strings). The value passed here, however, is an empty string ( 6='').
>
> Can you replicate this error? It's clearly a bug.
>
> Best,
>
> David
>
>

--
Bret Dawson
Producer
Pectopah Productions Inc.
(416) 895-7635
bret@pectopah.com
www.pectopah.com

Re: SQL error: invalid input syntax for type boolean [ In reply to ]

leggn at denison

Apr 25, 2011, 6:14 AM

Post #4 of 6 (6303 views)

Permalink

On 4/22/2011 2:52 PM, Bret Dawson wrote:
> I've seen this myself, on a 2.0.1 install using templates from the 1.10
> series. Here's how to make it happen:
>
> 1. Get a template that creates subelements. (The old
> image_resize_and_relate, for example.)
> 2. From the story profile, trigger a preview, which should create one or
> more subelements in the current story. Note that these subelements will
> not appear in the UI, because there's no mechanism for updating it if
> something happens during preview.
> 3. Attempt a save or a save and stay. You'll see the error.
>
Yes, I can reproduce the issue using these steps.
> The ideal would be to have the preview process finish by refreshing the
> story profile update itself, I suppose. AJAXers, would that be hard?
This seems to be a likely solution. If I manually refresh my browser on
the story profile before attempting step 3 above, the error does *not*
occur. I'm not sure how to leverage prototype or the API to accomplish
this in code, however.

Re: SQL error: invalid input syntax for type boolean [ In reply to ]

david at kineticode

Apr 25, 2011, 9:24 AM

Post #5 of 6 (6305 views)

Permalink

On Apr 25, 2011, at 6:14 AM, Nick Legg wrote:

> Yes, I can reproduce the issue using these steps.
>> The ideal would be to have the preview process finish by refreshing the
>> story profile update itself, I suppose. AJAXers, would that be hard?
> This seems to be a likely solution. If I manually refresh my browser on the story profile before attempting step 3 above, the error does *not* occur. I'm not sure how to leverage prototype or the API to accomplish this in code, however.

Would someone file a bug report then, please?

Thanks,

David

Re: SQL error: invalid input syntax for type boolean [ In reply to ]

leggn at denison

Apr 25, 2011, 10:13 AM

Post #6 of 6 (6303 views)

Permalink

Done.

http://bricolage.lighthouseapp.com/projects/29601-bricolage/tickets/255-sql-error-regarding-undefined-display-field

-Nick

On 4/25/2011 12:24 PM, David E. Wheeler wrote:
> On Apr 25, 2011, at 6:14 AM, Nick Legg wrote:
>
>> Yes, I can reproduce the issue using these steps.
>>> The ideal would be to have the preview process finish by refreshing the
>>> story profile update itself, I suppose. AJAXers, would that be hard?
>> This seems to be a likely solution. If I manually refresh my browser on the story profile before attempting step 3 above, the error does *not* occur. I'm not sure how to leverage prototype or the API to accomplish this in code, however.
> Would someone file a bug report then, please?
>
> Thanks,
>
> David
>

--
Nick Legg
Information Technology Services
Web Developer
Denison University
740-587-6537
leggn@denison.edu