MySQL Bugs: #52809: No performance

Bug #52809	No performance_schema on WIndows
Submitted:	14 Apr 2010 7:07	Modified:	9 Sep 2010 13:53
Reporter:	Peter Laursen (Basic Quality Contributor)	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: Installing	Severity:	S2 (Serious)
Version:	5.5.3	OS:	Windows (XP-SP3 (any))
Assigned to:	Daniel Fischer	CPU Architecture:	Any
Tags:	performance_schema windows, qc

Description:
Reference: http://dev.mysql.com/doc/refman/5.5/en/performance-schema-runtime-configuration.html

The startup parameter 'performance_schema' works as expected in the respect that SHOW VARIABLES LIKE 'performance_schema' returns ON or OFF depending on whether the option 

How to repeat:
SHOW GLOBAL VARIABLES LIKE 'perf%';
/* returns

Variable_name                                      Value 
-------------------------------------------------  ------
performance_schema                                 ON    
performance_schema_events_waits_history_long_size  10000 
performance_schema_events_waits_history_size       10    
performance_schema_max_cond_classes                80    
performance_schema_max_cond_instances              1000  
performance_schema_max_file_classes                50    
performance_schema_max_file_handles                32768 
performance_schema_max_file_instances              10000 
performance_schema_max_mutex_classes               200   
performance_schema_max_mutex_instances             1000  
performance_schema_max_rwlock_classes              20    
performance_schema_max_rwlock_instances            1000  
performance_schema_max_table_handles               100000
performance_schema_max_table_instances             50000 
performance_schema_max_thread_classes              50    
performance_schema_max_thread_instances            1000  */

SHOW DATABASES;
/* returns

Database          
------------------
information_schema
mysql             
test              */

Suggested fix:
Not sure if this is a packaging issue.  On Linux (installed with RPM for RedHat) I find a file in /datadir with P_S configuration options that I do not find on Windows and here SHOW DATABASES returns the P_S database. 

It may also be a known issue or limitation at the current stage, but then at least http://dev.mysql.com/doc/refman/5.5/en/performance-schema.html should document it and/or SHOW ... should always return OFF on Windows (or all the above variables should not exist at all).

1) please correct truncation:

depending on whether the option

>>

depending on whether the option was set at startup or not.  But there is no Performance_Schema database in either case. 

2) Also I should mention that this is a completely fresh install installed using the .msi (32 bit)

Thank you for the report.

Is it clean install or do you use datadir from previous versions?

i used: mysql-noinstall-5.5.3-m3-winx64.zip
extracted, and run like this, it works fine:

mysqld --no-defaults --console --performance-schema

maybe you're using an old version datadir and didn't run mysql_upgrade?
<cut>
mysql> select version();
+--------------------+
| version()          |
+--------------------+
| 5.5.3-m3-community |
+--------------------+
1 row in set (0.00 sec)
mysql> use performance_schema
Database changed
mysql> show table status;
+----------------------------------------------+------
| Name                                         | Engin
+----------------------------------------------+------
| COND_INSTANCES                               | PERFO
| EVENTS_WAITS_CURRENT                         | PERFO
| EVENTS_WAITS_HISTORY                         | PERFO
</cut>

I told it was a fresh install.  I uninstalled, deleted the 5.5.2 installation folder as well as the 5.5.2 /datadir before installing 5.5.3.

A colleague experiences same issue as me.  He also tells he did a fresh install.  We both have other MySQL servers (running with different service name, port and each using a distinct datadir (using default config wizard settings)

USE performance_schema
-- Error Code : 1049
-- Unknown database 'performance_schema'

But as Shane used the 'noinstall' build and I used the .msi it looks like a packaging/scripting issue with the .msi. Note my first remark that /datadir does not have P_S config data file(s) as I find them on Linux/CentOS. So maybe simple the server fails to initialize the P_S database due to this (my guesswork only).

I've got the same problem with 32-bit 5.5.3 when used 32-bit .msi installer (installed on a fresh datadir, without any 5.x.y installed there before). As a workaround, run mysql_upgrade.exe to get this:

C:\Program Files\MySQL\MySQL Server 5.5\bin>mysql_upgrade.exe
Looking for 'mysql.exe' as: C:\Program Files\MySQL\MySQL Server 5.5\bin\mysql.ex
e
Looking for 'mysqlcheck.exe' as: C:\Program Files\MySQL\MySQL Server 5.5\bin\mys
qlcheck.exe
Running 'mysqlcheck' with connection arguments: "--port=3312"
Running 'mysqlcheck' with connection arguments: "--port=3312"
mysql.columns_priv                                 OK
mysql.db                                           OK
mysql.event                                        OK
mysql.func                                         OK
mysql.general_log
Error    : You can't use locks with log tables.
status   : OK
mysql.help_category                                OK
mysql.help_keyword                                 OK
mysql.help_relation                                OK
mysql.help_topic                                   OK
mysql.host                                         OK
mysql.ndb_binlog_index                             OK
mysql.plugin                                       OK
mysql.proc                                         OK
mysql.procs_priv                                   OK
mysql.servers                                      OK
mysql.slow_log
Error    : You can't use locks with log tables.
status   : OK
mysql.tables_priv                                  OK
mysql.time_zone                                    OK
mysql.time_zone_leap_second                        OK
mysql.time_zone_name                               OK
mysql.time_zone_transition                         OK
mysql.time_zone_transition_type                    OK
mysql.user                                         OK
Running 'mysql_fix_privilege_tables'...
OK

C:\Program Files\MySQL\MySQL Server 5.5\bin>mysql -uroot -P3312 test
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 27
Server version: 5.5.3-m3-community MySQL Community Server (GPL)

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| test               |
+--------------------+
4 rows in set (0.00 sec)

Thanks for verification and the workaround not at least. 

Now .. I think I understand that the new audit plugin is also supposed to ship with 5.5.3.  If I am correct I think the build people also check that this is packaged correctly with the .msi.

I'm facing similar problems (I'm the colleague @Peter mentioned). @Valeriy's workaround worked somewhat in the sense that SHOW DATABASES now shows 'performance_schema':

mysql> SHOW DATABASES;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| test               |
+--------------------+
4 rows in set (0.02 sec)

Also, USE 'performance_schema'; SHOW TABLES; list all tables correctly:
+----------------------------------------------+
| Tables_in_performance_schema                 |
+----------------------------------------------+
| cond_instances                               |
| events_waits_current                         |
| events_waits_history                         |
| events_waits_history_long                    |
| events_waits_summary_by_event_name           |
| events_waits_summary_by_instance             |
| events_waits_summary_by_thread_by_event_name |
| file_instances                               |
| file_summary_by_event_name                   |
| file_summary_by_instance                     |
| mutex_instances                              |
| performance_timers                           |
| processlist                                  |
| rwlock_instances                             |
| setup_consumers                              |
| setup_instruments                            |
| setup_objects                                |
| setup_timers                                 |
+----------------------------------------------+
18 rows in set (0.00 sec)

But:

mysql> SELECT * FROM cond_instances;
ERROR 1682 (HY000): Native table 'performance_schema'.'COND_INSTANCES' has the w
rong structure
mysql>

The same output for SELECT on every table.

It's an MSI packaging issue. ZIPs are not affected. Will replace MSI packaging process with a sane process later in 5.5.

"later" must mean before next .msi is released for the public?

No, that is not what my dictionary says "later" means.

Well .. 'later' could equally well (at least according to my dictionary) mean 'later today' or 'later tonight' as 'later this year/decade/millenium'. ;-)

For those people that have read this report and can manage to install without an installer if is no big problem.  But for most of people it will be waste of time downloading, installing, (re)searching, figuring out, reporting, getting reply 'duplicate of bug 52809').

A new build process for Windows installers that we will hopefully start to use with 5.5.5 does not exhibit this problem; the tables are there and appear to be working.

yes .. 5.5.5 seems to be OK in this respect!

A note has been added to the 5.5.6 changelog: 

 The <literal>PERFORMANCE_SCHEMA</literal> database was not correctly created and populated on Windows.