1046 no database selected как исправить

Error
SQL query:

--
-- Database: `work`
--
-- --------------------------------------------------------
--
-- Table structure for table `administrators`
--
CREATE TABLE IF NOT EXISTS `administrators` (

`user_id` varchar( 30 ) NOT NULL ,
`password` varchar( 30 ) NOT NULL ) ENGINE = InnoDB DEFAULT CHARSET = latin1;

MySQL said:

#1046 - No database selected

need some help here.

OMG Ponies's user avatar

OMG Ponies

324k80 gold badges520 silver badges499 bronze badges

asked Oct 23, 2010 at 18:19

steph's user avatar

3

You need to tell MySQL which database to use:

USE database_name;

before you create a table.

In case the database does not exist, you need to create it as:

CREATE DATABASE database_name;

followed by:

USE database_name;

Piero's user avatar

Piero

9,12318 gold badges89 silver badges159 bronze badges

answered Oct 23, 2010 at 18:21

codaddict's user avatar

codaddictcodaddict

443k81 gold badges492 silver badges528 bronze badges

4

You can also tell MySQL what database to use (if you have it created already):

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

Daryl Gill's user avatar

Daryl Gill

5,4549 gold badges36 silver badges69 bronze badges

answered Feb 17, 2014 at 19:21

Shay Anderson's user avatar

1

I faced the same error when I tried to import a database created from before. Here is what I did to fix this issue:

1- Create new database

2- Use it by use command

enter image description here

3- Try again

This works for me.

HoldOffHunger's user avatar

answered Dec 6, 2015 at 8:26

Mina Fawzy's user avatar

Mina FawzyMina Fawzy

20.7k17 gold badges133 silver badges152 bronze badges

1

If you’re trying to do this via the command line…

If you’re trying to run the CREATE TABLE statement from the command line interface, you need to specify the database you’re working in before executing the query:

USE your_database;

Here’s the documentation.

If you’re trying to do this via MySQL Workbench…

…you need to select the appropriate database/catalog in the drop down menu found above the :Object Browser: tab. You can specify the default schema/database/catalog for the connection – click the “Manage Connections” options under the SQL Development heading of the Workbench splash screen.

Addendum

This all assumes there’s a database you want to create the table inside of – if not, you need to create the database before anything else:

CREATE DATABASE your_database;

answered Oct 23, 2010 at 18:24

OMG Ponies's user avatar

OMG PoniesOMG Ponies

324k80 gold badges520 silver badges499 bronze badges

4

If you are doing this through phpMyAdmin:

  • I’m assuming you already Created a new MySQL Database on Live Site (by live site I mean the company your hosting with (in my case Bluehost)).

  • Go to phpMyAdmin on live site – log in to the database you just created.

  • Now IMPORTANT! Before clicking the “import” option on the top bar, select your database on the left side of the page (grey bar, on the top has PHP Myadmin written, below it two options:information_schema and name of database you just logged into.

  • once you click the database you just created/logged into it will show you that database and then click the import option.

That did the trick for me. Really hope that helps

andrewtweber's user avatar

andrewtweber

24.1k22 gold badges88 silver badges110 bronze badges

answered Mar 18, 2014 at 1:25

Roanna's user avatar

RoannaRoanna

2512 silver badges2 bronze badges

2

For MySQL Workbench

  1. Select database from Schemas tab by right mouse clicking.
  2. Set database as Default Schema

enter image description here

answered Dec 6, 2018 at 14:12

Eric Korolev's user avatar

1

Assuming you are using the command line:

1. Find Database

show databases;

Example of a database list

2. Select a database from the list

e.g. USE classicmodels; and you should be off to the races! (Obviously, you’ll have to use the correctly named database in your list.

Why is this error occurring?

Mysql requires you to select the particular database you are working on. I presume it is a design decision they made: it avoids a lot of potential problems: e.g. it is entirely possible, for you to use the same table names across multiple databases e.g. a users table. In order to avoid these types of issues, they probably thought: “let’s make users select the database they want”.

answered Dec 12, 2020 at 23:44

BenKoshy's user avatar

BenKoshyBenKoshy

33k14 gold badges109 silver badges79 bronze badges

  • Edit your SQL file using Notepad or Notepad++
  • add the following 2 line:

CREATE DATABASE NAME;
USE NAME;

ckpepper02's user avatar

ckpepper02

3,2775 gold badges29 silver badges43 bronze badges

answered Oct 11, 2013 at 20:48

Ayham AlKawi's user avatar

1

If importing a database, you need to create one first with the same name, then select it and then IMPORT the existing database to it.

Hope it works for you!

answered Oct 25, 2011 at 16:38

ivan n's user avatar

ivan nivan n

991 silver badge1 bronze badge

1

be careful about blank passwords

mysqldump [options] -p '' --databases database_name

will ask for a password and complain with mysqldump: Got error: 1046: "No database selected" when selecting the database

the problem is that the -p option requires that there be no space between -p and the password.

mysqldump [options] -p'' --databases database_name

solved the problem (quotes are not needed anymore).

answered Jul 22, 2019 at 19:37

user3338098's user avatar

user3338098user3338098

9181 gold badge17 silver badges38 bronze badges

Check you have created the database first which you want.

If you have not created the dataBase you have to fire this query:

CREATE DATABASE data_base_name

If you have already created the database then you can simply fire this query and you will be able to create table on your database:

CREATE TABLE `data_base_name`.`table_name` (
 _id int not null,
 LastName varchar(255) NOT NULL,
 FirstName varchar(255),
 Age int,
 PRIMARY KEY (_id)
);

answered Apr 7, 2021 at 6:22

Sanket H patel's user avatar

Solution with an Example

  • Error 1046 occurs when we miss to connect our table with a database. In this case, we don’t have any database and that’s why at first we will create a new database and then will instruct to use that database for the created table.
# At first you have to create Database 
CREATE DATABASE student_sql;

# Next, specify the database to use
USE student_sql;

# Demo: create a table 
CREATE TABLE student_table(
    student_id INT PRIMARY KEY,
    name VARCHAR(20),
    major VARCHAR(20)
);

# Describe the table 
describe student_table;

answered May 28, 2022 at 20:02

sargupta's user avatar

sarguptasargupta

93513 silver badges25 bronze badges

quoting ivan n :
“If importing a database, you need to create one first with the same name, then select it and then IMPORT the existing database to it.
Hope it works for you!”

These are the steps:
Create a Database, for instance my_db1, utf8_general_ci.
Then click to go inside this database.
Then click “import”, and select the database: my_db1.sql

That should be all.

answered Apr 18, 2013 at 12:25

iversoncru's user avatar

iversoncruiversoncru

5798 silver badges22 bronze badges

1

first select database : USE db_name

then creat table:CREATE TABLE tb_name
(
id int,
name varchar(255),
salary int,
city varchar(255)
);

this for mysql 5.5 version syntax

answered Jul 4, 2015 at 12:46

veeru666's user avatar

I’m late i think :] soory,

If you are here like me searching for the solution when this error occurs with mysqldump instead of mysql, try this solution that i found on a german website out there by chance, so i wanted to share with homeless people who got headaches like me.

So the problem occurs because the lack -databases parameter before the database name

So your command must look like this:

mysqldump -pdbpass -udbuser --databases dbname

Another cause of the problem in my case was that i’m developping on local and the root user doesn’t have a password, so in this case you must use --password= instead of -pdbpass, so my final command was:

mysqldump -udbuser --password= --databases dbname

Link to the complete thread (in German) : https://marius.bloggt-in-braunschweig.de/2016/04/29/solution-mysqldump-no-database-selected-when-selecting-the-database/

answered Sep 23, 2018 at 2:52

moolsbytheway's user avatar

In Amazon RDS, merely writing use my-favorite-database does not work if that database’s name includes dashes. Furthermore, none of the following work, either:

use "my-favorite-database"
use `my-favorite-database`
use 'my-favorite-database'

Just click the “Change Database” button, select the desired database, and voilà.

answered Sep 8, 2021 at 18:21

David's user avatar

DavidDavid

8258 silver badges12 bronze badges

Although this is a pretty old thread, I just found something out. I created a new database, then added a user, and finally went to use phpMyAdmin to upload the .sql file. total failure. The system doesn’t recognize which DB I’m aiming at…

When I start fresh WITHOUT first attaching a new user, and then perform the same phpMyAdmin import, it works fine.

answered Sep 27, 2013 at 10:15

zipzit's user avatar

zipzitzipzit

3,6784 gold badges32 silver badges60 bronze badges

Just wanted to add: If you create a database in mySQL on a live site, then go into PHPMyAdmin and the database isn’t showing up – logout of cPanel then log back in, open PHPMyAdmin, and it should be there now.

answered Aug 4, 2014 at 23:42

the10thplanet's user avatar

For an added element of safety, when working with multiple DBs in the same script you can specify the DB in the query, e.g. “create table my_awesome_db.really_cool_table…”.

answered Jul 17, 2016 at 15:36

William T. Mallard's user avatar

jst create a new DB in mysql.Select that new DB.(if you r using mysql phpmyadmin now on the top it’l be like ‘Server:...* >> Database ).Now go to import tab select file.Import!

answered Oct 19, 2015 at 5:34

cs075's user avatar

0

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.

Страницы 1

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

1 2009-01-24 22:44:24

  • BadMoroz
  • Редкий гость
  • Неактивен
  • Зарегистрирован: 2009-01-24
  • Сообщений: 3

Тема: ошибка #1046 – No database selected

Привет всем!! У меня такая проблема( При импорте БД пишет такую ошбку #1046 – No database selected

Ошибка

SQL-запрос:

CREATE TABLE `jos_banner` (
`bid` int( 11 ) NOT NULL AUTO_INCREMENT ,
`cid` int( 11 ) NOT NULL default ‘0’,
`type` varchar( 30 ) NOT NULL default ‘banner’,
`name` varchar( 255 ) NOT NULL default ”,
`alias` varchar( 255 ) NOT NULL default ”,
`imptotal` int( 11 ) NOT NULL default ‘0’,
`impmade` int( 11 ) NOT NULL default ‘0’,
`clicks` int( 11 ) NOT NULL default ‘0’,
`imageurl` varchar( 100 ) NOT NULL default ”,
`clickurl` varchar( 200 ) NOT NULL default ”,
`date` datetime default NULL ,
`showBanner` tinyint( 1 ) NOT NULL default ‘0’,
X`checked_out` tinyint( 1 ) NOT NULL default ‘0’,
`checked_out_time` datetime NOT NULL default ‘0000-00-00 00:00:00’,
`editor` varchar( 50 ) default NULL ,
`custombannercode` text,
`catid` int( 10 ) unsigned NOT NULL default ‘0’,
`description` text NOT NULL ,
`sticky` tinyint( 1 ) unsigned NOT NULL default ‘0’,
`ordering` int( 11 ) NOT NULL default ‘0’,
`publish_up` datetime NOT NULL default ‘0000-00-00 00:00:00’,
`publish_down` datetime NOT NULL default ‘0000-00-00 00:00:00’,
`tags` text NOT NULL ,
`params` text NOT NULL ,
PRIMARY KEY ( `bid` ) ,
KEY `viewbanner` ( `showBanner` ) ,
KEY `idx_banner_catid` ( `catid` )
) ENGINE = MYISAM DEFAULT CHARSET = utf8 AUTO_INCREMENT =1

2 Ответ от Hanut 2009-01-25 01:53:52

  • Hanut
  • Hanut
  • Модератор
  • Неактивен
  • Откуда: Рига, Латвия
  • Зарегистрирован: 2006-07-02
  • Сообщений: 9,724

Re: ошибка #1046 – No database selected

BadMoroz
Сперва выберите (создайте, если надо) БД, в которую вы осуществляете импорт.

3 Ответ от BadMoroz 2009-01-25 15:06:44

  • BadMoroz
  • Редкий гость
  • Неактивен
  • Зарегистрирован: 2009-01-24
  • Сообщений: 3

Re: ошибка #1046 – No database selected

Hanut сказал:

BadMoroz
Сперва выберите (создайте, если надо) БД, в которую вы осуществляете импорт.

БД создана и выбрана. всёравно выкидывае ошибку((( #1046

4 Ответ от BadMoroz 2009-01-25 15:14:21

  • BadMoroz
  • Редкий гость
  • Неактивен
  • Зарегистрирован: 2009-01-24
  • Сообщений: 3

Re: ошибка #1046 – No database selected

Hanut сказал:

BadMoroz
Сперва выберите (создайте, если надо) БД, в которую вы осуществляете импорт.

Простите за невнимательность все загрузил! Большое спасибо

5 Ответ от Игорь Карасёв 2009-11-20 18:30:06

  • Игорь Карасёв
  • Редкий гость
  • Неактивен
  • Зарегистрирован: 2009-11-20
  • Сообщений: 6

Re: ошибка #1046 – No database selected

BadMoroz Расскажи как справился с проблемой?

6 Ответ от pritvorshik 2013-01-30 12:03:51

  • pritvorshik
  • Новичок
  • Неактивен
  • Зарегистрирован: 2013-01-30
  • Сообщений: 1

Re: ошибка #1046 – No database selected

Нужно слева в списке выбрать базу данных нажав на нее если она уже создана и лишь после импортировать файл имябазы.sql
Либо если там ее нет то создать, выбрать нажав на нее и лишь после импортировать файл с базой данных.
Так же если на хостинге разрешена лишь одна база данных с большим количеством мб, а сайтов можно создать более одного, два,  три и больше то ты просто меняешь либо добавляешь другой префикс к примеру на первый сайт префикс  ya_  на второй ti_  и в той же базе можешь повесить не один сайт c одной базой данных, с учетом если позволяет размер выделенный на базу хостером.

Страницы 1

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

The error no database selected frequently occurs in MySQL when you perform a statement without selecting a database first.

In the following example, I tried to query a students table immediately after connecting to the mysql command line:

mysql> SELECT * FROM students;

ERROR 1046 (3D000): No database selected

To resolve this error, you need to first select a database to use in the command line by running the USE command:

You need to replace [database_name] with the name of a database that exists in your MySQL server.

You can also list the names of all databases available on your server with the SHOW DATABASES command.

The following shows the output on my computer:

mysql> SHOW DATABASES;

+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| school_db          |
| sys                |
| test_db            |
+--------------------+

Next, issue the USE command as shown below:

mysql> USE school_db;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql> 

The error should be resolved once mysql responds with Database changed as shown above.

The same applies when you’re using a graphical user interface for managing MySQL databases like MySQL Workbench or Sequel Ace.

Just run the USE command before running any other statements:

USE school_db;
SELECT * FROM students;
SELECT * FROM cities;

The error can also happen when you run a .sql script file from the command line without adding a USE command:

mysql -uroot -p < ./files/query.sql
Enter password: 

ERROR 1046 (3D000) at line 1: No database selected

To run the .sql file, you need to add a USE statement inside the SQL file itself.

Alternatively, you can also select the database you want to use from the command line as follows:

mysql -uroot -p school_db < ./files/query.sql   
Enter password: 

id	name
3	Bristol
4	Liverpool
1	London
2	York

You need to add your database name after the -p option and before the < symbol.

And that’s how you can resolve the error no database selected in MySQL database server 😉

This article is a step-by-step guide to resolve the “MySQL ERROR 1046 (3D000) No Database Selected” error. If you are a DBA or a developer, this post will help you fix this MySQL 1046 error.

If you are getting this error message means you are trying to execute MySQL queries statement using the MySQL command prompt. Let’s go through why we get the error 1046 (3D000) followed by the step-by-step procedure to resolve this error.

Why you are getting MySQL Error 1046 (3D000) No database selected?

MySQL ERROR 1046 (3D000) No Database Selected

Let me tell you first why you are getting this 1046 MySQL error message.  You might have guessed it right by now; the error message is pretty clear that you have not selected the database before executing your MySQL statement.

This error generally occurs when you try to create a Table in the MySQL database using the command prompt.

Because while executing a command from the command prompt you need to select the database also since MySQL will not be able to know for which database you are executing the script.

When you execute create table statement from MySQL workbench then at that time you need to manually select the database then you execute your statement. Similarly, while executing a script from the command prompt screen, make sure you have provided the database name.

The next question is “how to provide the database name?” No worries. Here are the steps; just follow the below steps by step procedure to get rid of the error.

Steps to resolve MySQL ERROR 1046 (3D000) No Database Selected:

Step 1:

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

MySQL command line client

Step 2: Select the database

If you know the database name:

  • Select the Database on which you want to execute your script.
  • If you know the name of the database, then enter the database name in the following format.

use <database_name>;

select and use a database in MySQL

Note: Don’t skip this step, this is the solution to get rid of the 1046 (3D000) error message.

If you do not know the database name:

If you don’t know the available database names or the database on which you are going to execute the script, then you can list all available databases using the following command.

SHOW databases;

Show database command lists down all the databases available. Then you run use <database_name>;

Show database in MySQL

Step 3: Execute statement

Once the database is selected, you can execute your required SQL statement. Here we will execute create table statement in the database as an example.

mysql create table no database selected fix - table created

That’s it. You can see the created table in the MySQL database using MySQL workbench.

Table created in MySQL

Conclusion:

Is it not simple? I hope you now know the reason behind the “MySQL error 1046 No Database selected error” issue and how to fix it. Do share your feedback if this post helped you to fix the MySQL 1046 (3D000) error using the above steps in the comment section.

Cheers !!!

Similar article:
1. Fix “unknown collation ‘utf8mb4_unicode_520_ci’” Error

Добавить комментарий