Bug #16124 SHOW STATUS counters are not updated when using Perl DBI
Submitted: 2 Jan 2006 5:50 Modified: 11 Jan 2006 16:25
Reporter: Shao Yi Tan Email Updates:
Status: Can't repeat Impact on me:
None 
Category:MySQL Server Severity:S3 (Non-critical)
Version:mysql-5.0.18 OS:Linux (Linux)
Assigned to: CPU Architecture:Any

[2 Jan 2006 5:50] Shao Yi Tan
Description:
Hello,

I hope I am reporting this under the right category. I am using DBI-1.48 with DBD-mysql-3.0002 and it appears that the counters in the output of SHOW STATUS are not updated when the corresponding commands are being executed from Perl DBI.

4.0.24 and 4.1.13 do not appear to exhibit this problem though.

I wrote a little Perl script (attached below) to repeatedly query a database with SELECT queries, however the value for Com_select never appears to get updated.

The counter is incremented correctly (in 5.0.18) if I used the mysql console client.

The same behaviour appears to occur for the other counters too, e.g., Com_{insert, update, delete, replace} etc.

How to repeat:
#!/usr/bin/perl

use DBI;
my $DSN='DBI:mysql:assets:localhost';
my $DBUSER='root';
my $DBPASSWD='xxxxxx';

my $dbh=DBI->connect($DSN, $DBUSER, $DBPASSWD) or die print "$!\n";
my $sql='SELECT * FROM buildings';
my $sth=$dbh->prepare($sql);
$sth->execute();
while(my $href=$sth->fetchrow_hashref()){
        print $href->{'buildingName'}."\n";
}

exit;
[11 Jan 2006 14:49] Valeriy Kravchuk
Thank you for a problem report. Sorry, but I was not able to repeat the problem you described with your script and DBD-mysql-3.0002, on 5.0.19-BK on Linux. Look:

[openxs@Fedora 5.0]$ bin/mysql -uroot -e "show global status like 'com_select'"
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| Com_select    | 28    |
+---------------+-------+
[openxs@Fedora 5.0]$ perl 16124.pl
small
smaller
nest
[openxs@Fedora 5.0]$ bin/mysql -uroot -e "show global status like 'com_select'"
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| Com_select    | 29    |
+---------------+-------+
[openxs@Fedora 5.0]$ cat 16124.pl
#!/usr/bin/perl

use DBI;
my $DSN='DBI:mysql:test:127.0.0.1';
my $DBUSER='root';
my $DBPASSWD='';

my $dbh=DBI->connect($DSN, $DBUSER, $DBPASSWD) or die print "$!\n";
my $sql='SELECT * FROM buildings';
my $sth=$dbh->prepare($sql);
$sth->execute();
while(my $href=$sth->fetchrow_hashref()){
        print $href->{'buildingName'}."\n";
}

exit;
[openxs@Fedora 5.0]$ bin/mysql -uroot test
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 19 to server version: 5.0.19

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> select * from buildings;
+--------------+
| buildingName |
+--------------+
| small        |
| smaller      |
| nest         |
+--------------+
3 rows in set (0.01 sec)

mysql> exit
Bye
[openxs@Fedora 5.0]$ bin/mysql -uroot -e "show global status like 'com_select'"
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| Com_select    | 30    |
+---------------+-------+

So, global (not session, used by default) counter is updated in both cases.
[11 Jan 2006 16:25] Shao Yi Tan
Hi Valeriy,

Thank you for your time in looking into the bug report.

May I know if the behaviour of SHOW STATUS counters was changed from 4.1.X (specifically 4.1.13 and 4.0.24)? This is because we have a script that plots the values (Com_select etc) from the output of SHOW STATUS.

In both 4.1.13 and 4.0.24, the values of SHOW STATUS Com_* appear to be global by default, but for 5.X they appear to be by session.
[28 Apr 2006 9:55] Heikki Hannikainen
It seems that in 5.0+ 'show status' is per session, not global. Starting from 5.0 you need to issue the command 'show global status' to get the server-wide counters. This breaks old server statistics scripts (which will need to figure out the server version before issuing the command, since 'show global status' is not supported before 5.0).

The new command is not documented (http://dev.mysql.com/doc/refman/5.0/en/server-status-variables.html) or mentioned in the upgrade instructions (http://dev.mysql.com/doc/refman/5.0/en/upgrading-from-4-1.html).