General Query Log Parsing

[Allen Kinnard]

We turned the general query logs just to see what traffic is hitting this database before we decom it. MONyog is not parsing the prepared statements. Is there somewhere I can change it to grab the execute statements as well?

MONyog 4.2

MySQL 5.0.51a

Log file snip-it:

1693 Connect [email protected] on xxxxxxxx

1693 Query DESCRIBE `reviews`

1693 Prepare [1] SELECT `reviews`.* FROM `reviews` WHERE (title LIKE “%aewsome sauce%”) ORDER BY `reviewdate` desc, `overallscore` ASC LIMIT 26

1693 Execute [1] SELECT `reviews`.* FROM `reviews` WHERE (title LIKE “%aewsome sauce%”) ORDER BY `reviewdate` desc, `overallscore` ASC LIMIT 26

Right now I get the describe, but not the select.

[Mahesh]

Currently General Query log output does not show multiline queries.

It is known issue with MySQL There is no way to see where the query ends. We will have to assume that only one line applies.

Issue Report can be viewed here :

http://bugs.mysql.com/bug.php?id=48561

[Mahesh]

Ok .. Consider this example:

I have executed these statements from SQLyog:

prepare stmt1 from 'SELECT SQRT(POW(?,2) + POW(?,2)) AS hypotenuse';

set @a = 3;

set @b = 4;

execute stmt1 using @a, @b;

MySQL General Log entry shows:

9 Query PREPARE stmt1 FROM 'SELECT SQRT(POW(?,2) + POW(?,2)) AS hypotenuse'

9 Prepare SELECT SQRT(POW(?,2) + POW(?,2)) AS hypotenuse

9 Query SET @a = 3

9 Query SET @b = 4

9 Query EXECUTE stmt1 USING @a, @b

9 Execute SELECT SQRT(POW(3,2) + POW(4,2)) AS hypotenuse

MONyog shows Type = Query statements in output but it does not show Type = Prepare and Execute.

Means MONyog only parses

PREPARE stmt1 FROM 'SELECT SQRT(POW(?,2) + POW(?,2)) AS hypotenuse'

SET @a = 3

SET @b = 4

EXECUTE stmt1 USING @a, @b

and it does not parse Type= Prepare and Execute

Prepare SELECT SQRT(POW(?,2) + POW(?,2)) AS hypotenuse

Execute SELECT SQRT(POW(3,2) + POW(4,2)) AS hypotenuse

** One more thing whatever client executes MONyog records it ( Type= Prepare and Execute are handled by server internally)

Anyway it's a valid request and has been added in our issue tracking system.

Thanks,

Mahesh Patil

[Shalmali]

Hi Allen,

This has been fixed internally. We will be releasing MONyog 4.51 with this fix. This release is likely to be on the coming Friday(May 13th).

Thank you for reporting this issue.

Regards,

Shalmali

[Shalmali]

Hi Allen,

We have released MONyog 4.51 with this fix. For more information check http://www.webyog.com/blog/2011/05/13/monyog-mysql-monitor-4-51-has-been-released/. Please upgrade and verify.

– Shalmali

[Author]

Posts