Re: Confusion about SQL results - row counts and contents differ

On Fri, Jan 27 2023 at 09:50:42 +0800, demerphq <[email protected]> 
wrote:
> On Fri, 27 Jan 2023, 09:43 Steven Haigh via modperl, 
> <[email protected] <mailto:[email protected]>> wrote:
>> Hi all,
>> 
>> I'm a little confused about my little mod_perl web site at the 
>> moment.
>> 
>> I'm doing an SQL query, then punting the results into a dataset to 
>> display via the web page.
>> 
>> When I do the query directly on the SQL server, I see:
>> 
>> SELECT COUNT(*) FROM printers;
>> 30
>> 
>> When I do the same in code via the web page, I get:
>>         $sth = $dbh->prepare("SELECT COUNT(*) FROM printers");
>>         $vars->{"count"} = $dbh->selectcol_arrayref($sth, undef);
>> 
>> 'count' => [ 29 ]
>> 
>> Even trying a slightly different approach:
>>         $sth = $dbh->prepare("SELECT COUNT(*) FROM printers");
>>         $sth->execute();
>>         $vars->{"count"} = $sth->fetchrow_hashref();
>> 
>> count' => { 'COUNT(*)' => 29 }
>> 
>> This table is only ever updated manually - so there is no possible 
>> way that the contents are being updated during these runs - however 
>> I also note that the data returned in something like `SELECT * FROM 
>> printers` is different than what I get by running the query directly.
> 
> What do you mean by different?
>> 
>> Annoyingly, even after restarting apache, restarting mariadb, and 
>> even restarting the entire machine, I still see this issue.
>> 
>> That leaves me completely stuck - how can old data be returned - 
>> even after a full machine reboot?
>> 
>> Am I missing something kinda obvious?
>> 
>> The system is Fedora 37 with the following versions:
>> mariadb-10.9.4-1
>> mod_perl-2.0.12-5
>> httpd-2.4.54-5
>> perl-DBD-MariaDB-1.22-3
>> perl-DBD-MySQL-4.050-15
>> 
>> I've tried both the dbi:mysql and dbi:MariaDB - and the same occurs.
> 
> Are you sure you are connecting to the same dB?
> 
> I'd debug this with a simple perl script and no mod_perl, and then 
> work forward from there.

I stripped this back to:

#!/usr/bin/perl
use strict;
use warnings;
use POSIX qw(strftime);
use Data::Dumper;
use DBI;

## Connect to the database...
my $sqldb = "dbname";
my $sqlusername = "dbusername";
my $sqlpassword = "dbpassword";
my $sqlhost = "localhost";
my $dbh = DBI->connect("dbi:MariaDB:$sqldb:$sqlhost", $sqlusername, 
$sqlpassword) || die "Unable to connect to database! $!";

my $sth = $dbh->prepare("SELECT COUNT(*) FROM printers");
$sth->execute();

print Dumper($sth->fetchrow_hashref());

Yet that also returns 29.

So now I'm *really* confused - but at least that rules out mod_perl.....

> Yves
> 
> 
>> --
>> Steven Haigh
>> 
>> 📧 [email protected] <mailto:[email protected]>
>> 💻 https://crc.id.au <https://www.crc.id.au/>