On Fri, 27 Jan 2023, 09:55 Steven Haigh, <net...@crc.id.au> wrote:
>
>
> On Fri, Jan 27 2023 at 09:50:42 +0800, demerphq <demer...@gmail.com>
> wrote:
>
> On Fri, 27 Jan 2023, 09:43 Steven Haigh via modperl, <
> modperl@perl.apache.org> 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.....
>
Can you do a select * on the table and see if it returns what you expect?
Also the $sth should know how many rows are returned. So have it print out
the rows() property as well.
This issue rings a bell for me, but I can't put my finger on it. Been a
while since I last used mysql.
Double check that there isn't something going on in your direct client that
would account for the extra row. I have a hazy recollection of something
like that.
Yves
>
>>
