Mailing List Archive: "precision" = 'precision' MySQL Error

"precision" = 'precision' MySQL Error

waldo at vqronline

Oct 27, 2009, 10:58 AM

Post #1 of 15 (5327 views)

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

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

waldo at vqronline

Oct 27, 2009, 11:24 AM

Post #2 of 15 (5266 views)

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

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

david at kineticode

Oct 27, 2009, 12:57 PM

Post #3 of 15 (5251 views)

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

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

waldo at vqronline

Oct 27, 2009, 1:56 PM

Post #4 of 15 (5249 views)

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

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

david at kineticode

Oct 27, 2009, 2:23 PM

Post #5 of 15 (5238 views)

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

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

alex at gossamer-threads

Oct 27, 2009, 2:32 PM

Post #6 of 15 (5241 views)

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@gossamer-threads.com>
Gossamer Threads Inc. http://www.gossamer-threads.com/
Tel: (604) 687-5804 Fax: (604) 687-5806

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

david at kineticode

Oct 27, 2009, 4:21 PM

Post #7 of 15 (5251 views)

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

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

waldo at vqronline

Oct 27, 2009, 5:30 PM

Post #8 of 15 (5248 views)

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

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

david at kineticode

Oct 27, 2009, 5:55 PM

Post #9 of 15 (5246 views)

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

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

waldo at vqronline

Oct 28, 2009, 7:17 AM

Post #10 of 15 (5240 views)

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

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

david at kineticode

Oct 28, 2009, 10:23 AM

Post #11 of 15 (5251 views)

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

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

waldo at vqronline

Oct 29, 2009, 8:14 AM

Post #12 of 15 (5235 views)

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

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

david at kineticode

Oct 29, 2009, 5:11 PM

Post #13 of 15 (5244 views)

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

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

bret at pectopah

Oct 29, 2009, 5:18 PM

Post #14 of 15 (5237 views)

> 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@pectopah.com
www.pectopah.com

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

fulekia at denison

Oct 30, 2009, 11:09 AM

Post #15 of 15 (5226 views)

> 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
---------------------------------