Mailing List Archive: Bricolage: users

"precision" = 'precision' MySQL Error

Index | Next | Previous | View Threaded

waldo at vqronline

Oct 27, 2009, 10:58 AM

Post #1 of 15 (1418 views)
Permalink

"precision" = 'precision' MySQL Error

Folks,

In anticipation of v2.0 being finished, I'm returning to my two-years-
on-hold project of moving VQR to Bricolage. Having installed the
latest development release (v1.11.1) for its MySQL support, I'm having
some trouble. I'm unable to add any new subelements, with all attempts
failing like such:

Unable to execute SQL statement: DBD::mysql::st execute failed: You
have an error in your SQL syntax; check the manual that corresponds to
your MySQL server version for the right syntax to use near
'"precision" = 'precision', cols = '40', rows = '4', length = '0',
vals = '', mul' at line 1 [.for Statement "UPDATE field_type SET
element_type__id = ?, name = ?, key_name = ?, description = ?, place
= ?, min_occurrence = ?, max_occurrence = ?, autopopulated = ?,
max_length = ?, sql_type = ?, widget_type = ?, "precision" = ?, cols
= ?, rows = ?, length = ?, vals = ?, multiple = ?, default_val = ?,
active = ? WHERE id=?" with ParamValues: 0='1', 1='Deck', 2='deck',
3='A one-sentence teaser or subhead.', 4='1', 5='1', 6='1', 7='0',
8='256', 9='short', 10='textarea', 11='precision', 12='40', 13='4',
14='0', 15='', 16='0', 17='', 18='1', 19='12'] at /usr/local/bricolage/
lib/Bric/Util/DBI.pm line 1 136, <GEN17> line 783.\n, referer: http://webhosting37.bnsi.net:8080/admin/profile/element_type/1

That's just a fragment of the query, of course; there's any way to see
the entire SQL string, I don't know what it is. To my eyes, the
problem seems quite clearly to be "precision" = 'precision'—
field_type.precision is defined as smallint(6), so attempting to set
precision to the textual value of "precision" *should* fail. Note that
the Deck is not the subelement that I'm trying to add—the failed query
is (apparently) coming from Bricolage trying to update that existing
custom field at the same time as I try to add a new field. Searching
through Lighthouse, I see no record of this as a known bug, although I
find it tough to believe—if I understand what's going on—that this
could have gone unnoticed.

Any ideas?

Best,
Waldo

---
Virginia Quarterly Review
One West Range, Box 400223
University of Virginia
Charlottesville, VA 22904-4223
434-243-4995

waldo at vqronline

Oct 27, 2009, 11:24 AM

Post #2 of 15 (1379 views)
Permalink

Re: "precision" = 'precision' MySQL Error [In reply to]

Folks,

Ah, the surest way to make process when debugging is to e-mail a group
about it, isn't it? :)

Of course, the entire MySQL string *is* listed, and the problem
appears to twofold. Not only should "precision" be in `backticks`
(because it's a reserved term in MySQL's vocabulary) but, again,
precision should not equal the text "precision," and instead should be
an integer. Is this right?

Best,
Waldo

On Oct 27, 2009, at 1:58 PM, Waldo Jaquith wrote:
> Folks,
>
> In anticipation of v2.0 being finished, I'm returning to my two-
> years-on-hold project of moving VQR to Bricolage. Having installed
> the latest development release (v1.11.1) for its MySQL support, I'm
> having some trouble. I'm unable to add any new subelements, with all
> attempts failing like such:
>
> Unable to execute SQL statement: DBD::mysql::st execute failed: You
> have an error in your SQL syntax; check the manual that corresponds
> to your MySQL server version for the right syntax to use near
> '"precision" = 'precision', cols = '40', rows = '4', length = '0',
> vals = '', mul' at line 1 [.for Statement "UPDATE field_type SET
> element_type__id = ?, name = ?, key_name = ?, description = ?, place
> = ?, min_occurrence = ?, max_occurrence = ?, autopopulated = ?,
> max_length = ?, sql_type = ?, widget_type = ?, "precision" = ?, cols
> = ?, rows = ?, length = ?, vals = ?, multiple = ?, default_val = ?,
> active = ? WHERE id=?" with ParamValues: 0='1', 1='Deck', 2='deck',
> 3='A one-sentence teaser or subhead.', 4='1', 5='1', 6='1', 7='0',
> 8='256', 9='short', 10='textarea', 11='precision', 12='40', 13='4',
> 14='0', 15='', 16='0', 17='', 18='1', 19='12'] at /usr/local/
> bricolage/lib/Bric/Util/DBI.pm line 1 136, <GEN17> line 783.\n,
> referer: http://webhosting37.bnsi.net:8080/admin/profile/element_type/1
>
> That's just a fragment of the query, of course; there's any way to
> see the entire SQL string, I don't know what it is. To my eyes, the
> problem seems quite clearly to be "precision" = 'precision'—
> field_type.precision is defined as smallint(6), so attempting to set
> precision to the textual value of "precision" *should* fail. Note
> that the Deck is not the subelement that I'm trying to add—the
> failed query is (apparently) coming from Bricolage trying to update
> that existing custom field at the same time as I try to add a new
> field. Searching through Lighthouse, I see no record of this as a
> known bug, although I find it tough to believe—if I understand
> what's going on—that this could have gone unnoticed.
>
> Any ideas?
>
> Best,
> Waldo

david at kineticode

Oct 27, 2009, 12:57 PM

Post #3 of 15 (1377 views)
Permalink

Re: "precision" = 'precision' MySQL Error [In reply to]

On Oct 27, 2009, at 11:24 AM, Waldo Jaquith wrote:

> Ah, the surest way to make process when debugging is to e-mail a
> group about it, isn't it? :)
>
> Of course, the entire MySQL string *is* listed, and the problem
> appears to twofold. Not only should "precision" be in `backticks`
> (because it's a reserved term in MySQL's vocabulary)

Jesus Christ. What does your /etc/my.cnf look like? IIRC, Bricolage
requires

sql-mode=ansi,strict_trans_tables
character-set-server=utf8
default-storage-engine=InnoDB
default-time-zone=utc

> but, again, precision should not equal the text "precision," and
> instead should be an integer. Is this right?

Yes, it is. That part, at least, sounds like a bug.

Best,

David

waldo at vqronline

Oct 27, 2009, 1:56 PM

Post #4 of 15 (1375 views)
Permalink

Re: "precision" = 'precision' MySQL Error [In reply to]

On Oct 27, 2009, at 3:57 PM, David E. Wheeler wrote:
> On Oct 27, 2009, at 11:24 AM, Waldo Jaquith wrote:
>> Ah, the surest way to make process when debugging is to e-mail a
>> group about it, isn't it? :)
>>
>> Of course, the entire MySQL string *is* listed, and the problem
>> appears to twofold. Not only should "precision" be in `backticks`
>> (because it's a reserved term in MySQL's vocabulary)
>
> Jesus Christ. What does your /etc/my.cnf look like? IIRC, Bricolage
> requires
>
> sql-mode=ansi,strict_trans_tables
> character-set-server=utf8
> default-storage-engine=InnoDB
> default-time-zone=utc

Ah, it's setting sql-mode to ANSI that'd make the difference. I can't
find any mention of any of those changes in the install or readme
files, so that explains why they're not in the my.cnf on my server. :)
Well, now I know what changes to make to get this working right, or at
least this portion of it. Thanks for your help, David!

Best,
Waldo

---
Virginia Quarterly Review
One West Range, Box 400223
University of Virginia
Charlottesville, VA 22904-4223
434-243-4995

david at kineticode

Oct 27, 2009, 2:23 PM

Post #5 of 15 (1371 views)
Permalink

Re: "precision" = 'precision' MySQL Error [In reply to]

On Oct 27, 2009, at 1:56 PM, Waldo Jaquith wrote:

> Ah, it's setting sql-mode to ANSI that'd make the difference. I
> can't find any mention of any of those changes in the install or
> readme files, so that explains why they're not in the my.cnf on my
> server. :) Well, now I know what changes to make to get this working
> right, or at least this portion of it. Thanks for your help, David!

Does that fix it? Perhaps we could set those from within Bricolage on
connecting to the database? Anyone know?

Best,

David

alex at gossamer-threads

Oct 27, 2009, 2:32 PM

Post #6 of 15 (1369 views)
Permalink

Re: "precision" = 'precision' MySQL Error [In reply to]

Hi,

> Does that fix it? Perhaps we could set those from within Bricolage on
> connecting to the database? Anyone know?

Yup, you can set this up with SET:

http://dev.mysql.com/doc/refman/5.0/en/server-sql-mode.html

Cheers,

Alex

--
Alex Krohn <alex [at] gossamer-threads>
Gossamer Threads Inc. http://www.gossamer-threads.com/
Tel: (604) 687-5804 Fax: (604) 687-5806

david at kineticode

Oct 27, 2009, 4:21 PM

Post #7 of 15 (1373 views)
Permalink

Re: "precision" = 'precision' MySQL Error [In reply to]

On Oct 27, 2009, at 2:32 PM, Alex Krohn wrote:

>> Does that fix it? Perhaps we could set those from within Bricolage on
>> connecting to the database? Anyone know?
>
> Yup, you can set this up with SET:
>
> http://dev.mysql.com/doc/refman/5.0/en/server-sql-mode.html

Ah, right, and it looks like the install scripts do that:

% ack benedict ~/dev/bricolage/theory work > ack sql_mode
sql/mysql/Bric.sql
76:SET GLOBAL sql_mode='ansi,strict_trans_tables';
77:SET SESSION
sql_mode='ansi,strict_trans_tables,no_auto_value_on_zero';

I suppose we should have Bricolage itself do it on connect, eh?

Best,

David

waldo at vqronline

Oct 27, 2009, 5:30 PM

Post #8 of 15 (1369 views)
Permalink

Re: "precision" = 'precision' MySQL Error [In reply to]

On Oct 27, 2009, at 7:21 PM, David E. Wheeler wrote:
> On Oct 27, 2009, at 2:32 PM, Alex Krohn wrote:
>>> Does that fix it? Perhaps we could set those from within Bricolage
>>> on
>>> connecting to the database? Anyone know?
>>
>> Yup, you can set this up with SET:
>>
>> http://dev.mysql.com/doc/refman/5.0/en/server-sql-mode.html
>
> Ah, right, and it looks like the install scripts do that:
>
> % ack benedict ~/dev/bricolage/theory work > ack sql_mode
> sql/mysql/Bric.sql
> 76:SET GLOBAL sql_mode='ansi,strict_trans_tables';
> 77:SET SESSION
> sql_mode='ansi,strict_trans_tables,no_auto_value_on_zero';
>
> I suppose we should have Bricolage itself do it on connect, eh?

I'd forgotten until now that the MySQL portion of the install scripts
failed for me, and I had to configure everything manually. (I think I
posted a note about it to the list at the time.) Of course, I believed
I'd gone through everything that the install script was supposed to
do, but apparently I missed something! I'll report back in the morning
about whether that one addition does the trick, though I certainly
assume that it will.

Best,
Waldo

---
Virginia Quarterly Review
One West Range, Box 400223
University of Virginia
Charlottesville, VA 22904-4223
434-243-4995

david at kineticode

Oct 27, 2009, 5:55 PM

Post #9 of 15 (1370 views)
Permalink

Re: "precision" = 'precision' MySQL Error [In reply to]

On Oct 27, 2009, at 5:30 PM, Waldo Jaquith wrote:

>> I suppose we should have Bricolage itself do it on connect, eh?
>
> I'd forgotten until now that the MySQL portion of the install
> scripts failed for me, and I had to configure everything manually.
> (I think I posted a note about it to the list at the time.) Of
> course, I believed I'd gone through everything that the install
> script was supposed to do, but apparently I missed something! I'll
> report back in the morning about whether that one addition does the
> trick, though I certainly assume that it will.

Thanks, appreciated. Let us know what you find and we can try to make
adjustments to that it's easier for our poor brethren stuck on an
inferior platform. ;-P

Best,

David

waldo at vqronline

Oct 28, 2009, 7:17 AM

Post #10 of 15 (1361 views)
Permalink

Re: "precision" = 'precision' MySQL Error [In reply to]

On Oct 27, 2009, at 5:23 PM, David E. Wheeler wrote:
>
> On Oct 27, 2009, at 1:56 PM, Waldo Jaquith wrote:
>
>> Ah, it's setting sql-mode to ANSI that'd make the difference. I
>> can't find any mention of any of those changes in the install or
>> readme files, so that explains why they're not in the my.cnf on my
>> server. :) Well, now I know what changes to make to get this
>> working right, or at least this portion of it. Thanks for your
>> help, David!
>
> Does that fix it? Perhaps we could set those from within Bricolage
> on connecting to the database? Anyone know?

It's possible that it would fix it, but switching to ANSI-
compatibility mode breaks so many other things that I don't dare leave
it long enough to find out. phpMyAdmin cannot run in ANSI <http://www.phpmyadmin.net/documentation/#faq3_9
>. Neither can PEAR Auth (which left my live website instantly
crippled). There may be other common packages that can't function in
ANSI mode, but since this MySQL server is in use on a production site,
I'm not going to find out. :)

Alex, your suggestion to establish an ANSI-compatible connection
within Bricolage's database connection preamble certainly makes a lot
of sense to me. I am insufficiently familiar with Bric's source to
know where to drop that in, but if anybody can point me in the right
direction, I'm happy to just add those few lines to my own beta server
and see if it does the trick.

Best,
Waldo

---
Virginia Quarterly Review
One West Range, Box 400223
University of Virginia
Charlottesville, VA 22904-4223
434-243-4995

david at kineticode

Oct 28, 2009, 10:23 AM

Post #11 of 15 (1369 views)
Permalink

Re: "precision" = 'precision' MySQL Error [In reply to]

On Oct 28, 2009, at 7:17 AM, Waldo Jaquith wrote:

> It's possible that it would fix it, but switching to ANSI-
> compatibility mode breaks so many other things that I don't dare
> leave it long enough to find out. phpMyAdmin cannot run in ANSI <http://www.phpmyadmin.net/documentation/#faq3_9
> >. Neither can PEAR Auth (which left my live website instantly
> crippled). There may be other common packages that can't function in
> ANSI mode, but since this MySQL server is in use on a production
> site, I'm not going to find out. :)
>
> Alex, your suggestion to establish an ANSI-compatible connection
> within Bricolage's database connection preamble certainly makes a
> lot of sense to me. I am insufficiently familiar with Bric's source
> to know where to drop that in, but if anybody can point me in the
> right direction, I'm happy to just add those few lines to my own
> beta server and see if it does the trick.

God damn I detest MySQL. Try applying the attached patch with `patch -
p1 mysql_hate.patch`. If it fixes the issue for you, I'll commit it.

Best,

David

waldo at vqronline

Oct 29, 2009, 8:14 AM

Post #12 of 15 (1359 views)
Permalink

Re: "precision" = 'precision' MySQL Error [In reply to]

On Oct 28, 2009, at 1:23 PM, David E. Wheeler wrote:
>
> On Oct 28, 2009, at 7:17 AM, Waldo Jaquith wrote:
>
>> Alex, your suggestion to establish an ANSI-compatible connection
>> within Bricolage's database connection preamble certainly makes a
>> lot of sense to me. I am insufficiently familiar with Bric's source
>> to know where to drop that in, but if anybody can point me in the
>> right direction, I'm happy to just add those few lines to my own
>> beta server and see if it does the trick.
>
> God damn I detest MySQL. Try applying the attached patch with `patch
> -p1 mysql_hate.patch`. If it fixes the issue for you, I'll commit it.

David, that worked just great. Thanks for the idea for that solution,
Alex, and thank you for implementing it, David!

You feel about MySQL as I do about Coke. It's mystifying to me how
millions (billions?) of people drink that vile brew and apparently
like it. Don't they know they could be drinking water, juice, chai, or—
my preference—sweet iced tea with mint? Anyhow, now I know what it's
like to be on the Coke-drinking side of that divide. :)

Best,
Waldo

---
Virginia Quarterly Review
One West Range, Box 400223
University of Virginia
Charlottesville, VA 22904-4223
434-243-4995

david at kineticode

Oct 29, 2009, 5:11 PM

Post #13 of 15 (1356 views)
Permalink

Re: "precision" = 'precision' MySQL Error [In reply to]

On Oct 29, 2009, at 8:14 AM, Waldo Jaquith wrote:

> David, that worked just great. Thanks for the idea for that
> solution, Alex, and thank you for implementing it, David!

Great, I'll commit it. And perhaps I'll remove the global setting in
mysql/Bric.sql while I'm at it -- I suspect that was the issue you had.

> You feel about MySQL as I do about Coke. It's mystifying to me how
> millions (billions?) of people drink that vile brew and apparently
> like it. Don't they know they could be drinking water, juice, chai,
> or—my preference—sweet iced tea with mint? Anyhow, now I know what
> it's like to be on the Coke-drinking side of that divide. :)

Try Mexican Coke. It's made with sugar instead of HFCS and tastes much
better.

Though I rarely drink either one myself.

Best,

David

bret at pectopah

Oct 29, 2009, 5:18 PM

Post #14 of 15 (1359 views)
Permalink

Re: "precision" = 'precision' MySQL Error [In reply to]

> Try Mexican Coke. It's made with sugar instead of HFCS and tastes much
> better.

Or beer. Or gin.

Yes: gin. Official spirit of Bricolage?

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

fulekia at denison

Oct 30, 2009, 11:09 AM

Post #15 of 15 (1353 views)
Permalink

Re: "precision" = 'precision' MySQL Error [In reply to]

> Or beer. Or gin.
>
> Yes: gin. Official spirit of Bricolage?

Beer: Official beer of Bricolage.

There - I fixed that for you.

---------------------------------
Aaron Fuleki
Senior Web Architect
Denison University
740.587.5752
---------------------------------

Index | Next | Previous | View Threaded

Interested in having your list archived? Contact Gossamer Threads