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!