Status / Advisor problem with MySQL 8.0.11

[tm8544]

Steps to reproduce

1. On PhpMyAdmin, select tab Status and the select link Advisor

Expected behaviour

Advisor output without errors

Actual behaviour

Errors occurred while executing rule expressions:
- Failed calculating value for rule 'Query cache disabled'. Error when evaluating: Variable "query_cache_size" is not valid around position 1 for expression `query_cache_size`.
- Failed evaluating precondition for rule 'Query cache efficiency (%)'. Error when evaluating: Variable "Qcache_hits" is not valid around position 14 for expression `Com_select + Qcache_hits > 0 && !fired('Query cache disabled')`.
- Failed calculating value for rule 'Query Cache usage'. Error when evaluating: Variable "Qcache_free_memory" is not valid around position 7 for expression `100 - Qcache_free_memory / query_cache_size * 100`.
- Failed calculating value for rule 'Query cache fragmentation'. Error when evaluating: Variable "Qcache_free_blocks" is not valid around position 1 for expression `Qcache_free_blocks / (Qcache_total_blocks / 2) * 100`.
- Failed evaluating precondition for rule 'Query cache low memory prunes'. Error when evaluating: Variable "Qcache_inserts" is not valid around position 1 for expression `Qcache_inserts > 0 && !fired('Query cache disabled')`.
- Failed calculating value for rule 'Query cache max size'. Error when evaluating: Variable "query_cache_size" is not valid around position 1 for expression `query_cache_size`.
- Failed calculating value for rule 'Query cache min result size'. Error when evaluating: Variable "query_cache_limit" is not valid around position 1 for expression `query_cache_limit`.

Server configuration

Operating system:
Windows 10 Pro x64

Web server:
Apache 2.4.33 (Win64)

Database:
MySQL 8.0.11

PHP version:
7.2.5 x64 (ApacheLounge)

phpMyAdmin version:
4.8.0.1

Client configuration

Browser:
Chrome

Operating system:
Windows 10 Home x64

[rotech1]

Seeing this also in Server version: 8.0.12 - MySQL Community Server - GPL ( running on CentOS 7)

Version information: 4.8.3 (up to date)

Apache/2.4.6 (CentOS) PHP/7.1.21
Database client version: libmysql - mysqlnd 5.0.12-dev - 20150407
PHP extension: mysqliDocumentation curlDocumentation mbstringDocumentation
PHP version: 7.1.21

[olokos]

So what is the solution? Referenced issue is also closed now, but no solution by following those issues.

[olokos]

Still present in 4.8.5

[tm8544]

Probably needs an update to libraries\advisory_rules.txt?

[Tithugues]

Just to be clear for developers that would like to work on this issue: some MySQL variables were removed in 8.0.3, like query_cache_size (see https://dev.mysql.com/doc/refman/8.0/en/server-system-variables.html).

I think that we will need to differentiate rules, regarding the MySQL version (and maybe regarding the MariaDB version).
Possible ways to fix it:

• if rule precondition allows to check MySQL version, then we can just update the preconditions
• have separated rules files

[Tithugues]

@tm8544 , would it be please possible that you test the branch in this PR: #15457?

[tm8544]

This is the result:

Errors occurred while executing rule expressions:

• Failed evaluating precondition for rule 'Query cache efficiency (%)'. Error when evaluating: Variable "Qcache_hits" is not valid around position 70 for expression (IS_MYSQL == false || PMA_MYSQL_INT_VERSION < 80003) && Com_select + Qcache_hits > 0 && !fired('Query cache disabled').
• Failed evaluating precondition for rule 'Query cache low memory prunes'. Error when evaluating: Variable "Qcache_inserts" is not valid around position 57 for expression (IS_MYSQL == false || PMA_MYSQL_INT_VERSION < 80003) && Qcache_inserts > 0 && !fired('Query cache disabled').

[Tithugues]

OK, sorry for that, I had some difficulties to use a real MySQL ≥8.0.3, so I didn't have this error…
Unfortunately, I'm not sure that the current changes will help us to fix these last bugs… So maybe we should really think about having many files and use the right one(s) regarding the MySQL version.

[Tithugues]

@tm8544 , based on what I already did, I split the rules file in 2 files and load the needed file(s) regarding the DBMS.
Can you please have another try?

Thank you.

[tm8544]

Tested, there are no errors anymore in advisor output.

[williamdes]

@tm8544 Can you close this issue if 4.9.1 solves it ?

[tm8544]

Can you confirm that this includes two separate advisory rule files as in 99b2dfa ?
Otherwise it will not work.

[Tithugues]

Yes, it is.

[tm8544]

When I download phpMyAdmin-4.9.1-english.zip, the libraries folder has only one advisory rule file, advisory_rules.txt.
It still produces the same error when Mysql > 8.0.3

[williamdes]

@tm8544 I forgot to merge the PR..
can you try the patch #15461 ?

[tm8544]

#15461 works ok.
I will close this when it is merged .