MySQL Error: #1046 - No database selected

Question

I ran into an issue using MySQL 5.5 running on xampp (Version 1.8.1). I kept receiving the "#1046 - No database selected" error when trying to add a table to a database via the console window.

I had made sure the database was selected "USE database_name;", however I was still receiving the same error.

I double checked and found all privileges for the user were enabled. Frustrated, I "Uncheck All" then "Check All" global privileges.

I logged out, restarted server services, closed the browser, restarted server services, opened browser, and logged in.

Now I am able to create tables via the console. It appears to me resetting the privileges fixed the issue.

Can anyone tell me if this is a known bug, if I got lucky, or missed the point completely? Documentation I found concerning this error was mostly for importing a database and using the "Use Database_Name;" to ensure the database was indeed selected. However I don't believe this was the issue.

Please Note: While trying to create new databases I am once again receiving the #1046 error. The method I mentioned above was a fluke and is not allowing me to create any new tables inside a new database. However I am still able to work with the first database I created.

Answer 1

Another late answer but this can also happen when the table name (.*) is not specified. I ran fixed by adding the .* after database name to get it to work.

Before:

GRANT ALL PRIVILEGES ON my_db_name TO ...

After:

GRANT ALL PRIVILEGES ON my_db_name.* TO ...

This change fixed the somewhat ambiguous error "ERROR 1046 (3D000): No database selected"

Answer 2

I know this is a very late answer but for the benefit of anyone coming across this error, it might also pop up when the database name includes a dash. If so you need to put backticks around the database name, like so:

GRANT ALL ON `database-name`.* TO 'username'@'localhost';

The error message is not very helpful as it suggest you need to select a database in order to grant privileges to a user.

Answer 3

If privileges have been directly inserted into the table 'user' (many web frontends such as phpMyAdmin do that), one needs to execute FLUSH PRIVILEGES (phpMyAdmin has a button for this) or restart the server to reload the privileges. Neither is required when GRANT is used.