Issue 46 in innotop: Query list shows invalid time

[inn...@googlecode.com]

Status: New
Owner: ----
Labels: Type-Defect Priority-Medium

New issue 46 by bassamta...@gmail.com: Query list shows invalid time
http://code.google.com/p/innotop/issues/detail?id=46

I'm running innotop against Percona Server 5.1.47-rel11.1 and I'm seeing
the following in the query list:

Cmd ID State User Host DB
Time Query
Ping 1172775 web ip-10-100-6-1 Account
213503982+08:01:49.551
Query 1172788 web ip-10-194-233 Account
213503982+08:01:49.551
Ping 1172791 web ip-10-244-182 Account
213503982+08:01:49.551
Daemon 883382 Waiting for next a event_sc
localhost 4+19:00:12.900
Change user 1172813 Reading from net web
ip-10-100-6-1 00:00.000

Note times such as "213503982+08:01:49.551" which are wrong. I only see
this after upgrading to innotop 1.8.0

[inn...@googlecode.com]

Updates:
Status: Accepted
Labels: -Type-Defect Type-Enhancement

Comment #1 on issue 46 by baron.sc...@gmail.com: Query list shows invalid
time
http://code.google.com/p/innotop/issues/detail?id=46

This is surely a bug in the INFORMATION_SCHEMA.PROCESSLIST table, which
innotop 1.8.0 automatically uses if it contains the TIME_MS column (because
it permits higher precision timing). We can probably add a way to disable
that if you like.

[inn...@googlecode.com]

Comment #2 on issue 46 by bassamta...@gmail.com: Query list shows invalid
time
http://code.google.com/p/innotop/issues/detail?id=46

that would be great. Is this a known bug in mysql?

[inn...@googlecode.com]

Comment #3 on issue 46 by oneth...@gmail.com: Query list shows invalid time
http://code.google.com/p/innotop/issues/detail?id=46

Any update on this? We see this with Percona 5.5+ and Innotop 1.8+.

[inn...@googlecode.com]

Comment #4 on issue 46 by baron.sc...@gmail.com: Query list shows invalid
time
http://code.google.com/p/innotop/issues/detail?id=46

No progress, sorry. I generally make changes to innotop in large batches so
I can focus my attention, so I will get to this at some point, I'm just not
sure when. In the meantime, as a workaround you can change the following
code:

4294 PROCESSLIST => sub {
4295 my ( $dbh ) = @_;
4296 # In newer versions of the server, use INFORMATION_SCHEMA table
if i
4297 # and use the TIME_MS column (in Percona Server) if that exists.
4298 my $sth;
4299 eval { # This can fail if the table doesn't exist,
INFORMATION_SCHEM
4300 my $cols = $dbh->selectall_arrayref(q{SHOW /*innotop*/
COLUMNS FR
4301 if ( @$cols ) { # The TIME_MS colum exists
4302 $sth = $dbh->prepare(q{SELECT /*innotop*/ ID, USER, HOST,
DB,
4303 . q{INFO FROM INFORMATION_SCHEMA.PROCESSLIST});
4304 }
4305 };
4306 $sth ||= $dbh->prepare('SHOW /*innotop*/ FULL PROCESSLIST');
4307 return $sth;

Move lines 4307 and 4307 just after line 4298, so it returns before it
looks at the information schema table.

[inn...@googlecode.com]

Comment #5 on issue 46 by dan...@percona.com: Query list shows invalid time
http://code.google.com/p/innotop/issues/detail?id=46

This issue was updated by revision r59.

First untested attempt to fix TIME_MS bug.

[inn...@googlecode.com]

Comment #6 on issue 46 by dan...@percona.com: Query list shows invalid time
http://code.google.com/p/innotop/issues/detail?id=46

This issue was updated by revision r60.

Better TIME_MS bug fix that works.

[inn...@googlecode.com]

Comment #7 on issue 46 by dan...@percona.com: Query list shows invalid time
http://code.google.com/p/innotop/issues/detail?id=46

This issue was updated by revision r61.

Fix new procs' time if > 1 year in.

[inn...@googlecode.com]

Comment #8 on issue 46 by dan...@percona.com: Query list shows invalid time
http://code.google.com/p/innotop/issues/detail?id=46

This issue was updated by revision r62.

Save adjusted time to current proc.

[inn...@googlecode.com]

Comment #9 on issue 46 by dan...@percona.com: Query list shows invalid time
http://code.google.com/p/innotop/issues/detail?id=46

This issue was updated by revision r63.

Simpler solution for TIME_MS bug.

[inn...@googlecode.com]

Comment #10 on issue 46 by dan...@percona.com: Query list shows invalid time
http://code.google.com/p/innotop/issues/detail?id=46

This issue was updated by revision r64.

Remove fix for TIME_MS bug. Use SQL CASE to select TIME_MS or TIME.

[inn...@googlecode.com]

Updates:
Status: Fixed

Comment #11 on issue 46 by baron.sc...@gmail.com: Query list shows invalid
time
http://code.google.com/p/innotop/issues/detail?id=46

This issue is fixed in trunk now.