MySQL Workbench, Windows Server 2019, MySQL not showing Performance, Information of MySQL Schemas!

MySQLmysql-8.0mysql-workbenchwindows-server

This evening I installed MySQL on a new clean install of Windows 2019 server. When the install finished, the installer offered the possibility of opening Workbench so I said yes.

Then I noticed that there were schemas missing – as per the question, the performance, information and mysql schemas were missing (see the screenshot below¹)

But, when I log in using the mysql client, I get this

mysql> show schemas;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sakila             |
| sys                |
| world              |
+--------------------+
6 rows in set (0.00 sec)

In Workbench, I can access the "missing" schemas via queries like so

SELECT host, user FROM mysql.user;

host,user
%,xxx
localhost,mysql.infoschema
localhost,mysql.session
localhost,mysql.sys
localhost,root

Can anybody explain to me what's going on here? How can I get the missing schemas to appear in MySQL Workbench?

¹ I know that screenshots are normally not a good idea in this forum, but this time, it's the only way to show what I mean.

>mysqld.exe --version
\mysqld.exe  Ver 8.0.18 for Win64 on x86_64 (MySQL Community Server - GPL)

and

Workbench 8.0

and

Windows Server 2019 Standard

Best Answer

A similar issue, Information_Schema and mysql databases not shown, was submitted as a bug report to the MySQL Bug System nine years ago. The report was resolved as Not a Bug as the issue turned out to be something controlled by one of MySQL Workbench settings. Specifically, Valeriy Kravchuk suggested this resolution:

Please, check "Show Metadata Schemata" checkbox in the Query Editor group of the SQL Editor tab in the Workbench Preferences dialog box. Use Edit > Preferences... menu item to open it. Don't forget to click "Refresh all" in the Object Browser's context menu after that.

Related Solutions

Mysql Server Login and Permission Deny Error

Besides not running mysql_upgrade after upgrading MySQL from major subversion to major subversion (E.g 5.1 to 5.5), having the wrong permissions/ownership on the performance_schema folder and files within the data directory can also cause this error.

For example, a fresh install of MySQL 5.5's RPM will set the ownership of that folder and files within it to root:root. To fix this, you will need to change the ownership to mysql:mysql.

E.g.

chown -R mysql:mysql /var/lib/mysql/performance_schema

ls -al /var/lib/mysql

drwx------. 2 mysql mysql     4096 Jun 10 10:29 performance_schema

Mysql – Install MySQL for Windows from .zip and reset root password

Since you started mysqld with --skip-grant-tables, you cannot execute any standard GRANT, REVOKE, or SET PASSWORD commands. Notwithstanding, you can change the password of root@localhost as follows:

UPDATE mysql.user SET password=PASSWORD(‘passhere') WHERE user='root' and host='localhost';

Then, restart mysqld and you are back in business.

As to you being anonymous, if you login right now and run

SELECT USER() HowYouAttemptedToLogin,CURRENT_USER() HowYouWereAllowedToLogin;

You will note that the HowYouWereAllowedToLogin will have an blank username and some host.

GIVE IT A TRY !!!

UPDATE 2015-11-17 16:24 EST

The column known as password in mysql.user no longer exists in MySQL 5.7.

It was renamed authentication_string.

Proper Approach

What you should have done is run

mysqld --initialize

That would create the data folder for you.

The root@localhost was assigned a temporary password, which is visible in the error file starting with the following datetime and string

2015-11-17T20:56:02.175980Z 1 [Note] A temporary password is generated for root@localhost:

You could log in with it and then run ALTER USER

mysql> ALTER USER 'root'@'localhost' IDENTIFIED BY 'new_password';

See the MySQL 5.7 Documentation on SET PASSWORD