Loading...
X

“ERROR 1046 (3D000): No database selected” in MySQL and MariaDB (SOLVED)

How to fix “ERROR 1046 (3D000): No database selected”

The error “ERROR 1046 (3D000): No database selected” occurs most often due to haste – of course, before performing actions with tables and the information stored in them, you must specify (that is, select to use) database. This simple mistake is a good reason to tell you about at least 5 ways to choose a database – perhaps you did not know about some of them and in the future they will help improve your productivity.

In this note, I'm looking at ways to select databases when connecting to a MySQL and MariaDB server using a CLI client. In the next post, we will look at ways to select a MySQL and MariaDB database in a PHP script.

Consider the following very simple queries.

The first query prints out the data stored in the TestTABLE table:

SELECT * FROM TestTABLE;

The result of the query execution is an error – the database is not selected, that is, the DBMS does not understand which table from which database the previous query is intended for:

ERROR 1046 (3D000): No database selected

The following query lists tables:

SHOW TABLES;

The result and reason are identical – the database for which the request is intended is not specified.

ERROR 1046 (3D000): No database selected

1. Select a database using the “USE” statement

You can specify the database to use with the “USE” statement (replace _DATABASE_NAME_ with the name of your database):

USE _DATABASE_NAME_;

For example, the following sequence of queries no longer causes an error:

USE TestDB;
SELECT * FROM TestTABLE;

The name of the selected database (that is, the database for which the query is intended) is usually specified at the command prompt (although this can be changed in the settings).

See also: How to change the command line prompt for MySQL (MariaDB)

After using the “USE” statement, the confirmation “Database changed” is displayed, that is, the database has been changed.

It is not necessary to use the “USE” statement before each subsequent request, since the client remembers the selected database during the current session:

SHOW TABLES;

As you can see, the request was successful because the database was selected earlier.

Information about the selected database is reset if you disconnect from the MySQL (MariaDB) server and connect again.

To select a different database, simply use the query with the “USE” statement again. You do not need to unselect the previous database in any way – this will be done automatically when a new database is selected.

2. Specify the database in the query

In SELECT queries and other expressions that use the table name, you can specify the “path” to the table in the format “DATABASE.TABLE”. That is, at the beginning you should specify the name of the database, then a period and then the name of the table.

An example of a query that does not need to be preceded by a “USE” statement (replace “_DB_” with the database name and “_TestTABLE_” with the table name):

SELECT * FROM _DB_._TestTABLE_;

An example request that no longer throws an error:

SELECT * FROM TestDB.TestTABLE;

You need to know this syntax because it is impossible to do without it when querying multiple tables from different databases.

It is also possible to specify a database in the “SHOW TABLES” statement, and therefore it is not necessary to use the “USE” statement first:

SHOW TABLES FROM TestDB;

3. Specify database when connecting to MySQL and MariaDB server

You can select the database to use directly in the command line for connecting to the MySQL and MariaDB server (replace “_USERNAME_” with the username and “_DB_” with the database name):

mariadb -u _USERNAME_ -p _DB_

The following command will connect to the MariaDB server and select the “TestDB” database to use:

mariadb -u root TestDB

This syntax is also worth knowing as it is very useful when executing SQL queries in non-interactive (batch) mode, such as when executing queries from a text file. The most common example of using a non-interactive mode for executing SQL queries is importing tables into a database.

If you are executing SQL queries within an interactive session, then you can also specify the database when connecting. Subsequently, if you want to switch to another database, you can easily do this with the “USE” statement – it is not necessary to disconnect from the server.

4. Specify the database using the command line option

There is another way to specify the database to use on the command line, namely with the -D option (or the longer --database), the syntax is as follows (replace “_USERNAME_” with the username and “_DB_” with the database name) :

mariadb -u _USERNAME_ -p -D _DB_

mariadb -u _USERNAME_ -p --database=_DB_

The following command will connect to the MariaDB server and select the “TestDB” database to use:

mariadb -u root -D TestDB

The difference between this option and the previous one is that if you do not use the -D (or --database) option, then the database name must be at the very end of the command. When using the -D option, the database name must follow this option, but together they can appear anywhere on the command line.

5. Setting the default database in the configuration file

If you are (mostly) working with only one database, then you can set the default database in the MySQL and MariaDB configuration file.

See also: How to determine the location and name of the MySQL (MariaDB) configuration file. How to find the group name for MySQL and MariaDB configuration files

When connecting to a MySQL and MariaDB server, you will no longer need to specify the database to use.

However, if you want to query another database, you can use the “USE” statement to select another database to use at any time.

To add a default database option, open the /etc/my.cnf configuration file:

sudo gedit /etc/my.cnf

And in the [client] settings group (if there is one, then create it) add the line (replace “TestDB” with the name of your database):

[client]
database=TestDB

Save and close the /etc/my.cnf file.

You do not need to restart the MySQL (MariaDB) server for this setting to take effect, as this setting is used by the client.

As you can see in the following screenshot, the database specified in the “database” setting is now automatically selected when connecting.


Leave Your Observation

Your email address will not be published. Required fields are marked *