Hi,

First post, be kind ;-)

Some background info:

Solaris 10 Generic_147440-27

$ perl -v

This is perl 5, version 16, subversion 1 (v5.16.1) built for sun4-solaris

$ perl -MDBI -le 'print $DBI::VERSION;'

1.622

$ perl -MDBD::mysql -le 'print $DBD::mysql::VERSION;'

4.022

mysql version: 5.0.67

In short I’m having an issue with some odd selectrow_array() behaviour, basically I’m getting an error returned whenever I run a query that returns null data (which in this case is a valid scenario, i.e. no data meets the criteria and the first column based on row count will always be a zero (and the remaining columns "NULL") as a result).

So, taking some code snippets:

my $perfdbh = DBI->connect("DBI:mysql:database=$database;host=$dbhost"
+, "******", "******", {RaiseError => 1, AutoCommit => 1}) or die $DBI
+::errstr;

[download]

Followed by: 

my $statement = "select $columns{$database} from $perftable where $que
+ry";
my ($days, $latest, $maxcpu, $avgcpu, $minfreemem, $avgfreemem) = ($pe
+rfdbh->selectrow_array($statement));

[download]

or: 

my ($days, $latest, $maxcpu, $avgcpu, $minfreemem, $avgfreemem) = ($pe
+rfdbh->selectrow_array("
select $columns{$database}
from $perftable
where $query"));

[download]

Works so long as a row is returned with non-NULL / undef.

The query changes based on a number of different scenarios we’re testing, for example if I insert a “print” into the code to display $statement we can see output such as:

select ( count(*) / 288 ), max(date), max(usrpercent+nicepercent+syspercent+wiopercent), avg(usrpercent+nicepercent+syspercent+wiopercent), max(memused - memcache), avg(memused - memcache) from SERVER where epoch >= (select max(epoch)-3456000 from SERVER) and (time >= '09:00' and time <= '17:00')

select ( count(*) / 288 ), max(date), max(usrpercent+nicepercent+syspercent+wiopercent), avg(usrpercent+nicepercent+syspercent+wiopercent), max(memused - memcache), avg(memused - memcache) from SERVER where epoch >= (select max(epoch)-3456000 from SERVER) and (time <= '09:00' or time >= '17:00')

ERROR 1064 (42000) at line 2: 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 'and epoch >= (select max(epoch)-3456000 from SERVER) and (time <= '09:00' or t' at line 4

The first query returns data, the second returns a decimal and five "NULL" (undef) as expected (and throws an error). If I run the same query via cli or connect directly to the DB it works as expected without generating an error: 

mysql> select ( count(*) / 288 ), max(date), max(usrpercent+nicepercen
+t+syspercent+wiopercent), avg(usrpercent+nicepercent+syspercent+wiope
+rcent), max(memused - memcache), avg(memused - memcache) from SERVER 
+where epoch >= (select max(epoch)-3456000 from SERVER) and (time <= '
+09:00' or time >= '17:00');
+--------------------+-----------+------------------------------------
+---------------+---------------------------------------------------+-
+------------------------+-------------------------+
| ( count(*) / 288 ) | max(date) | max(usrpercent+nicepercent+sysperce
+nt+wiopercent) | avg(usrpercent+nicepercent+syspercent+wiopercent) | 
+max(memused - memcache) | avg(memused - memcache) |
+--------------------+-----------+------------------------------------
+---------------+---------------------------------------------------+-
+------------------------+-------------------------+
|             0.0000 | NULL      |                                    
+          NULL |                                              NULL | 
+                   NULL |                    NULL | 
+--------------------+-----------+------------------------------------
+---------------+---------------------------------------------------+-
+------------------------+-------------------------+
1 row in set (0.01 sec)

[download]

More worryingly, look at the sql that doesn’t work with selectrow_array() and the error that’s generated as a result – notice the keywork to the left of ‘epoch >=’, they’re different??

This only happens when there is no data matching the search criteria and the results are consistent everytime!!

Any ideas?

Thanks!

In reply to selectrow_array() problem.... by CWJ

Title:
Use:  <p> text here (a paragraph) </p>
and:  <code> code here </code>
to format your post, it's "PerlMonks-approved HTML":


  • Posts are HTML formatted. Put <p> </p> tags around your paragraphs. Put <code> </code> tags around your code and data!
  • Titles consisting of a single word are discouraged, and in most cases are disallowed outright.
  • Read Where should I post X? if you're not absolutely sure you're posting in the right place.
  • Please read these before you post! —
  • Posts may use any of the Perl Monks Approved HTML tags:
    a, abbr, b, big, blockquote, br, caption, center, col, colgroup, dd, del, details, div, dl, dt, em, font, h1, h2, h3, h4, h5, h6, hr, i, ins, li, ol, p, pre, readmore, small, span, spoiler, strike, strong, sub, summary, sup, table, tbody, td, tfoot, th, thead, tr, tt, u, ul, wbr
  • You may need to use entities for some characters, as follows. (Exception: Within code tags, you can put the characters literally.)
            For:     Use:
    & &amp;
    < &lt;
    > &gt;
    [ &#91;
    ] &#93;
  • Link using PerlMonks shortcuts! What shortcuts can I use for linking?
  • See Writeup Formatting Tips and other pages linked from there for more info.