get_mysql_stats.php with mysql 5.6

[Matt Day]

Hi,

We've recently upgraded to MySQL 5.6. Prior to this, the SQL graphs in Cacti were drawing OK, but since upgraded they are not drawing at all.

I've run ss_get_mysql_stats.php from the command line and whenever I do a load of the values are all showing as -1. Most of these seem to be related to the Innodb status and query time histogram. The other MySQL related stats are collecting OK.

As far as I can tell the script isn't running the Innodb section, so doesn't have any values to populate it with, resulting in the -1 entries. The Cacti graphs then show NaN, as there are no valid values to pick up.

I've run the script with debugging turned on, and at no point in the Innodb section in the debug file. I've been searching all day for an answer to this, but can't find anyone using this script with MySQL 5.6. I'm not sure if this is a bug or just a config problem.

I've checked the outputs for CHECK ENGINE INNODB STATUS\G and there is no difference between 5.5 and 5.6, so I'm not sure if why the script isn't returning the correct values.

If you need any more information, just ask and I'll oblige.

Thanks.

[Danijel Krmar (krmard)]

Hi,

Took me a while, but I have found the reason for this problem.

From MySQL version 5.6.3 the have_innodb variable is removed from the SHOW VARIABLES output. And the percona ss_get_mysql_stats.php uses this variable to check if innodb exists.

A workaround I am using now is to check the SHOW ENGINES output for InnoDB.

So i have changed the following lines in the script (beginning on line 471):

- && array_key_exists('have_innodb', $status)
- && $status['have_innodb'] == 'YES'

to

+ && array_key_exists('InnoDB', $status)
+ && $status['InnoDB'] == 'DEFAULT'
+ || $status['InnoDB'] == 'YES'

Also, I had to add the following status check (anywhere after line 356):
+ # Get SHOW ENGINES and do the same thing, adding it to the $status array.
+ $result = run_query("SHOW ENGINES", $conn);
+ foreach ( $result as $row ) {
+ $status[$row[0]] = $row[1];
+ }