Ошибка sql 1046 no database selected

I wrote a stored procedure (sp_archivev3) on MySQl Workbench which is as follows. Basically, Inserting values from one database to another.

-- --------------------------------------------------------------------------------
-- Routine DDL
-- Note: comments before and after the routine body will not be stored by the server
-- --------------------------------------------------------------------------------
DELIMITER $$

CREATE DEFINER=`MailMe`@`%` PROCEDURE `sp_archivev3`()
BEGIN

INSERT INTO 
     send.sgev3_archive(a_bi,
                        b_vc,
                        c_int,
                        d_int,
                        e_vc,
                        f_vc,
                        g_vc,
                        h_vc,
                        i_dt,
                        j_vc,
                        k_vc,
                        l_vc,
                        m_dt,
                        n_vch,
                        o_bit)
SELECT     a_bi,
           b_vc,
           c_int,
           d_int,
           e_vc,
           f_vc,
           g_vc,
           h_vc,
           i_dt,
           j_vc,
           k_vc,
           l_vc,
           m_dt,
           n_vch,
           o_bit

FROM   send.sgev3

WHERE m_dt BETWEEN  '2014-06-09' AND CURDATE();


END

When I run call sp_archivev3(); , I get an error with an error code 1046: No database
selected SELECT the default DB to be used by double-clicking its name in the SCHEMAS list in the sidebar.

Please let me know what’s wrong with my stored procedure.

Страницы 1

Чтобы отправить ответ, вы должны войти или зарегистрироваться

Страницы 1

Чтобы отправить ответ, вы должны войти или зарегистрироваться

MySQL no database selected is an error that occurs when you execute a statement without first selecting a database. The database may be completely missing, or you may choose the wrong database if there is more than one database.

Therefore, if you have more than one database, know the currently selected database and on which database your query execution takes place. Read this article to understand MySQL error and how to fix it.

MySQL No Database Selected Error Causes

The MySQL1046 (3D000) error occurs when you do not select a database first when executing the MySQL statement. This error will mostly happen if you are trying to create a table in the MySQL database using the command prompt. While executing a command from the command prompt, you must also select the database. Otherwise, MySQL will not know from which database you are running the script.

MySQL has a command to help you determine the currently selected database. This is a quick way to help you ascertain if the problem is genuinely caused by not specifying a database. If the error arises due to not selecting a database, you can easily overcome it by replacing [database_name] with the correct database name in your server.

When creating a table statement from the MySQL workbench, you need to select the database to run before executing the statement. Note, the process of choosing the database is manual, not automatic. Similarly, when running a script from the command prompt screen, ensure that you provide that database name.

How to Resolve the No Database Selected Error During File Import

The error code 1046 no database selected MySQL workbench will pop up if you do not select a database before importing the SQL file. This can be disappointing if you do not know the origin of the problem. Now that you know the reason, here are some quick fixes.

– Create a New Database

• You must mention the name of the database prior to creating a table. To do so, use the command: USE database_name;
• If the database is not there, create a new database. Creating a new database can be quickly done by using the command: CREATE DATABASE database_name;

Now, use the newly created database with the command USE database_name. This should eliminate the 1046 error.

– Workbench Solution

This solution is specifically efficient when using the workbench. Experts suggest that you follow the steps below to eliminate the error:

• Find the welcome window, navigate to the left pane, and Object the browser
• From the drop-down list, select a database of interest
• Go to the SQL Development in the Workbench splash screen, look for the Manage Connections icon, and click on it.

– PhpMyAdmin Error Fix

This solution works for No database selected PhpMyAdmin errors. The approach tells you how you can resolve the error during the import. Just follow the steps below:

• Have a new database ready on Live Site (the company hosting you, e.g., Bluehost). This is mandatory.
• Navigate to phpMyAdmin on the live site and log in to the database
• Choose a database of interest from the list on the left side of the page. Usually, there is a grey bar on top with a PHPMyadmin inscription, and below it are two options – information_schema and the name of the database you logged into.
• From the top bar, click on the import button.
• Find and click on the Browse button, browse the files to find the SQL file you created or of interest and click to open it when you see it. If the file is zipped, unzip it first.
• Select SQL as the file format and press the Go button

After pressing the Go button, you will wait for a few minutes before seeing the results. Typically, the amount of wait time depends on the size of the database. The name of the created database must be similar to the name mentioned in the file. Otherwise, it will still throw the error.

– Other Solutions for Database Not Selected Error

One of the solutions requires that you create a database first before importing it. Here is the procedure.

• Create a new database using MySQL phpMyAdmin
• Use the database by simply running the command use database_name

And, finally, the easiest solution among all as it allows you to select a database using the command:

mysql -u example_user -p –database=work < ./work.sql

How to View Currently Selected Database: Avoiding MySQL No Database Selected Error

If you want to view the presently selected database, use the MySQL command below:

SELECT DATABASE();

You can execute this command from both two points – MySQL command line or MySQL workbench. The procedure for both processes is pretty straightforward.

If you are accessing it from the workbench, open it and execute the command:

SELECT DATABASE();

This action will expose the currently selected database, i.e., it returns the database you have selected. Usually, the database is also displayed in bold on the left side.

If you are working from the MySQL terminal, you must log in using your username and password and execute the command:

SELECT DATABASE();

This action also presents the selected database. However, the command will return null if you have not selected a database. This is common when you log into the terminal for the first time.

How to Import Files to Mysql Correctly

When the error emerges on your screen, you know the real cause of the issue. But, do you know how to select the database in MySQL? So, if you encounter a ”no database selected” error in PHP, Python, or any other program, you didn’t import your file correctly. Here is how you can import the files:

1. The step one is opening the Command :

• Open the command prompt on MySQL
• Navigate to the start menu and open Command Line Client.

2. The second step is selecting the Database:

• Selecting the database takes two forms: first, if you know the database, and second, if you do not know the database.

3. Let’s look at the first case (you know the database name):

• Select the database you wish to execute your script
• Do you know the name of the database? If yes, enter it in the format, use <database_name>;

Knowing the database name is critical since it is a solution to getting rid of the 1046 (3D000) error message.

4. Let’s look at the second option (you do not know the database name):

• In case you don’t know the database or database name on which you want to execute the script, list all the databases available using the command SHOW databases;
• This command, i.e., SHOW databases, will list all available databases making it easy to spot the database of interest.
• Run the use <database_name> command to select the database you want

5. And now we can execute the Statement:

• After you successfully select a database of interest, execute the needed statement. Typically, you will be executing create table statement in the database. This action creates a table in the database in MySQL using the workbench.

FAQ

– How Do I Switch Between Mysql Databases?

If you have more than one database in MySQL, indicate each with the USE command. This command helps you select or switch between databases in a MySQL server. However, you must choose the correct database each time you switch databases or start a new MySQL session.

– How Do I Select a Schema in Mysql?

Right-click on MySQL connection from the home screen and select edit connect. Set the desired default schema on the Default Schema box. The schema you select will be displayed in bold in the schema navigator. Use Filter to This Schema functionality to target specific schemas in the list.

Conclusion

Error 1046 usually occurs if you do not select the correct database when importing files in MySQL. That’s what we have discussed in detail in this article. The main points in this article are:

• Always select the database before clicking the import button
• Use the command SELECT Database when selecting a specific database in MySQL to work with when you have multiple databases. However, if you have one database, use SQL command USE.
• When MySQL ERROR 1046 (3D000) occurs, ensure you select the database. You can use the exact name to locate the file. Otherwise, use the command SHOW databases. This command displays all databases letting you select the right one.
• The first step in preventing the 1046 error is learning how to import files.

First, we have shown you how to import files correctly, and second, we have given you tips on how to solve the error 1046 when it occurs. These fixes are pretty straightforward, so why can’t you start applying them today?

15 ответов

USE database_name;

CREATE DATABASE database_name;

USE database_name;

 mysql -u example_user -p --database=example < ./example.sql

USE your_database;

CREATE DATABASE your_database;

mysqldump -pdbpass -udbuser --databases dbname

mysqldump -udbuser --password= --databases dbname