SHOW DATABASES lists only some of the databases that exist
SHOW DATABASES does not list all of my databasesShort answer
SHOW DATABASES is filtered by privileges: an account sees only the databases it holds some privilege on, unless it has the global SHOW DATABASES privilege. So the missing databases are hidden from this account, not gone. Check which account the server matched, what it was granted, and whether a role that carries the grants is active.
Fix it fast
1Find out which cause you have
Ask the server which account it matched and what that account may do. The two answers separate the causes below.
SELECT CURRENT_USER(); SHOW GRANTS; SELECT CURRENT_ROLE();2Match what you see, then jump to its fix
- 1
SHOW GRANTS lists grants for one or two named databases and no global SHOW DATABASESThe account has privileges on only some of the databasesFix - 2
SHOW GRANTS FOR the account USING the role shows the privileges, but CURRENT_ROLE() returns NONEThe privileges come from a role that is not active in your sessionFix - 3
CURRENT_USER() returns a value such as @localhost, with an empty user nameThe server matched a different account than the one you meantFix
- 1
What does the error look like?
SHOW DATABASES does not list all of my databasesA database is visible to an administrator account and missing for the application user
Cause 1 of 3
The account has privileges on only some of the databases
1Check
An account without the global SHOW DATABASES privilege sees only the databases on which it holds some privilege. A grant on one database makes that one database visible and nothing else.
SHOW GRANTS FOR CURRENT_USER;
SELECT * FROM INFORMATION_SCHEMA.SCHEMATA;2Fix
Decide what the account should see. Either grant a privilege on each missing database, or, if seeing every database name is acceptable, give the account the global SHOW DATABASES privilege. Run the GRANT as an administrator. Note the opposite risk: any static global privilege also shows every database name, except databases restricted by partial revokes.
GRANT SHOW DATABASES ON *.* TO 'appuser'@'localhost';
-- or one database at a time
GRANT SELECT ON reports.* TO 'appuser'@'localhost';3Verify
Reconnect as that account and list again.
SHOW DATABASES;Confirmed by dev.mysql.com
Cause 2 of 3
The privileges come from a role that is not active in your session
1Check
In MySQL 8, a role that has been granted to an account is not active by default when the account connects. Until it is active, its privileges do not count, and the databases it covers stay hidden.
SELECT CURRENT_ROLE();
SHOW GRANTS FOR 'appuser'@'localhost';
SHOW GRANTS FOR 'appuser'@'localhost' USING 'app_read';2Fix
Make the roles default roles, so they are active at each login, or switch the roles on for the current session. A server-wide alternative is to enable the activate_all_roles_on_login system variable.
SET DEFAULT ROLE ALL TO 'appuser'@'localhost';
-- in an open session, without reconnecting
SET ROLE DEFAULT;3Verify
Reconnect, check that CURRENT_ROLE() lists the role, and list the databases.
SELECT CURRENT_ROLE();
SHOW DATABASES;Confirmed by dev.mysql.com
Cause 3 of 3
The server matched a different account than the one you meant
1Check
The server sorts its account rows and takes the first one that matches the connecting host and user name. Rows with a blank user name sort after named users only for the same host value. A blank-user row for localhost therefore beats a named user whose host is %, and you are treated as the anonymous user with the anonymous account's privileges.
SELECT CURRENT_USER();
SELECT User, Host FROM mysql.user ORDER BY User, Host;2Fix
Have an administrator remove the anonymous row, or create the named account for the exact host you connect from, so it is the most specific match. Then connect again.
DROP USER ''@'localhost';3Verify
After reconnecting, CURRENT_USER() shows the account you meant.
SELECT CURRENT_USER();Confirmed by dev.mysql.com
How do I stop it coming back?
- Give each application account the grants it needs by name, and write them in one script that is kept with the project.
- When you use roles, set the default roles in the same script that grants them, and test with a fresh login.
- Remove anonymous accounts on every new server, and look at CURRENT_USER() whenever privileges are not what you expect.
- Treat the global SHOW DATABASES privilege as a visibility grant on all database names, and give it only to accounts that may see them.
Sources
How was this page made?
I drafted this page with AI assistance from the official documents listed under Sources. Every cause above names the document that confirms it. I have not reproduced this fix on a machine of my own yet, so read it as documented guidance, not a test report. Found a mistake? Tell me.
Which stack is behind this page?
Projects of mine built on the same technologies:
- TaxiDexMy own multi-tenant taxi dispatch SaaS: API, dispatcher portal, driver and passenger apps, and the servers underneath.
- QistLockA NestJS and React SaaS driving an Android Device Owner agent that locks financed phones when payments lapse.
- ShopGoAn e-commerce aggregator with a Python scraping engine behind its catalog, past 10K Google Play downloads.
Related fixes
Databases
ERROR 1049 (42000): Unknown database
Unknown database 'mydatabasename'
Error 1049 means the server has no database with that exact name.
3 causes, each with a fixDatabases
Installing the Airflow MySQL provider fails on pkg-config or mysqlclient
Building the mysqlclient wheel fails during the install
The Airflow MySQL provider depends on mysqlclient, which pip compiles against the MySQL C client library.
2 causes, each with a fixDatabases
A query on Aurora MySQL dies with 'table /rdsdbdata/tmp/#sql... is full'
ERROR 1114 (HY000): The table '/rdsdbdata/tmp/#sqlfd_1586_2' is full
In Aurora MySQL 3, internal temporary tables share one pool, and when that pool and the memory-mapped overflow file are used up, a reader instance ends the query with error 1114.
3 causes, each with a fixDatabases
Azure Database for MySQL refuses a connection that does not use SSL
ERROR 3159 (HY000): Connections using insecure transport are prohibited while --require_secure_transport=ON.
Azure Database for MySQL Flexible Server requires encrypted connections by default (require_secure_transport = ON, TLS 1.2).
4 causes, each with a fixDatabases
Compiling a C or C++ program that includes mysql.h fails
undefined reference to `mysql_init'
The compiler only finds mysql.h when the MySQL client development files are installed and their folder is on the include path.
3 causes, each with a fixDatabases
DBeaver fails to resolve Maven dependencies when downloading a database driver
A driver library does not download automatically
DBeaver downloads driver jars from a Maven repository.
3 causes, each with a fix