Error code 1051

I’m trying to drop a table from a schema I inherited. When I SHOW TABLES I get

+----------------------------+
| Tables_in_schema_a         |
+----------------------------+
| table_1                    |
| table_2                    |
| table_3                    |
| table_4                    |
| table_5                    |
| table_6                    |
+----------------------------+

But when I execute DROP TABLE table_1 I get

ERROR 1051 (42S02): Unknown table ‘table_1’

I’m using the correct schema. What’s going on?

P.S. This is MySQL server is 5.1.73.

Evan Carroll

76k45 gold badges251 silver badges443 bronze badges

asked Apr 19, 2016 at 15:13

Turns out SHOW TABLES is actually a bit of a misnomer. That table, table_1, was unknown because it’s actually a view. I ran SELECT table_name, table_type FROM information_schema.tables WHERE table_schema='schema_a' showed that it’s a view. DROP VIEW table_1 deleted it.

answered Apr 19, 2016 at 16:35

kaskas

8983 gold badges10 silver badges27 bronze badges

Check whether the table is a VIEW, if so use the command

drop view table_name;

Paul Roub

36.2k27 gold badges82 silver badges90 bronze badges

answered Jan 9, 2017 at 17:44

try the following command:

DROP TABLE database_name.table_name; or
DROP TABLE `table_name`;

answered Jan 6, 2022 at 10:16

Источник

#1 01.04.2012 12:25:09

Farhad: Участник; Зарегистрирован: 01.04.2012; Сообщений: 2

Ошибка 1051

DROP TABLE admin_login;

CREATE TABLE `admin_login` (
`userId` int(11) NOT NULL AUTO_INCREMENT,
`adminEmailId` varchar(255) COLLATE latin1_general_ci NOT NULL,
`adminPassword` varchar(255) COLLATE latin1_general_ci NOT NULL,
`lastLogin` datetime NOT NULL,
`lastLoginIpAddress` varchar(255) COLLATE latin1_general_ci NOT NULL,
`accountCreatedOn` datetime NOT NULL,
`flag` tinyint(1) NOT NULL DEFAULT ‘1’ COMMENT ‘1- Active 0 — In Active’,
PRIMARY KEY (`userId`)
) ENGINE=MyISAM AUTO_INCREMENT=26 DEFAULT CHARSET=latin1 COLLATE=latin1_general_ci;

Это часть базы данных, при импортировании выдает ошибку:
DROP TABLE admin_login;
#1051 — Unknown table ‘admin_login’

Помогите пожалуйста, срочно надо

Неактивен

#2 01.04.2012 12:27:56

rgbeast: Администратор; Откуда: Москва; Зарегистрирован: 21.01.2007; Сообщений: 3874

Re: Ошибка 1051

Замените DROP TABLE admin_login; на DROP TABLE IF EXISTS admin_login;

Неактивен

#3 01.04.2012 12:36:31

Farhad: Участник; Зарегистрирован: 01.04.2012; Сообщений: 2

Re: Ошибка 1051

rgbeast написал:

Замените DROP TABLE admin_login; на DROP TABLE IF EXISTS admin_login;

Спасибо большое от всего сердца спасибо!!!

Неактивен

Источник

Summary: in this tutorial, you will learn how to use the MySQL DROP VIEW statement to delete a view from the database.

Introduction to the MySQL `DROP VIEW` statement

The DROP VIEW statement deletes a view completely from the database. Here’s the basic syntax of the DROP VIEW statement:

DROP VIEW [IF EXISTS] view_name;
Code language: SQL (Structured Query Language) (sql)

In this syntax, you specify the name of the view that you want to drop after the DROP VIEW keywords. The optional IF EXISTS option conditionally removes the view only if it exists.

To remove multiple views in a single statement, you use the following syntax:

DROP VIEW [IF EXISTS] view_name1 [,view_name2]...;
Code language: SQL (Structured Query Language) (sql)

In this syntax, you specify a list of comma-separated views after the DROP VIEW keywords.

If the list contains a view that doesn’t exist, the DROP VIEW statement will fail and won’t delete any view. However, if you use the IF EXISTS option, the DROP VIEW statement will generate a NOTE for each non-existing view.

Note that in MySQL 5.7 or earlier, the DROP VIEW returns an error if there is any non-existing view. However, it drops the views that exist.

MySQL DROP VIEW statement examples

Let’s take some examples of using the DROP VIEW statement.

1) MySQL DROP VIEW – drop a view example

This statement creates a view named customerPayments based on the customers and payments tables:

CREATE VIEW customerPayments 
AS
    SELECT 
        customerName, 
        SUM(amount) payment
    FROM
        customers
    INNER JOIN payments 
        USING (customerNumber)
    GROUP BY 
        customerName;
Code language: SQL (Structured Query Language) (sql)

This example uses the DROP VIEW statement to drop the customerPayments view:

DROP VIEW IF EXISTS customerPayments;




  


                       
  



Code language: SQL (Structured Query Language) (sql)

2) MySQL `DROP VIEW` – drop multiple views example

This statement creates a view named employeeOffices based on the employees and offices tables:

CREATE VIEW employeeOffices AS
    SELECT 
        firstName, lastName, addressLine1, city
    FROM
        employees
            INNER JOIN
        offices USING (officeCode);
Code language: SQL (Structured Query Language) (sql)

The following statement uses the DROP VIEW statement to delete two views employeeOffices and eOffices:

DROP VIEW employeeOffices, eOffices;
Code language: SQL (Structured Query Language) (sql)

MySQL issued the following error:

Error Code: 1051. Unknown table 'classicmodels.eoffices'
Code language: SQL (Structured Query Language) (sql)

Let’s add the IF EXISTS option like this:

DROP VIEW IF EXISTS employeeOffices, eOffices;
Code language: SQL (Structured Query Language) (sql)

MySQL issued a warning instead:

1 warning(s): 1051 Unknown table 'classicmodels.eoffices'
Code language: SQL (Structured Query Language) (sql)

The employeeOffices view remains intact.

This statement creates a new view named productCatalogs based on the products and productLines tables:

CREATE VIEW productCatalogs AS
    SELECT 
        productLine, productName, msrp
    FROM
        products
            INNER JOIN
        productLines USING (productLine);
Code language: SQL (Structured Query Language) (sql)

The following example uses the DROP VIEW statement to delete the employeeOffices and productCatalogs views:

DROP VIEW employeeOffices, productCatalogs;
Code language: SQL (Structured Query Language) (sql)

MySQL deleted the views completely.

Summary

Use the DROP VIEW statement to delete one or more views from a database.
Use the IF EXISTS option to conditionally delete a view if it exists.

Was this tutorial helpful?

Источник

MariaDB shares error codes with MySQL, as well as adding a number of new error codes specific to MariaDB.

An example of an error code is as follows:

SELECT * FROM x;
ERROR 1046 (3D000): No database selected

Shared MariaDB/MySQL error codes
MariaDB-specific error codes

There are three pieces of information returned in an error:

A numeric error code, in this case 1046. Error codes from 1900 and up are specific to MariaDB, while error codes from 1000 to 1800 are shared by MySQL and MariaDB.
An SQLSTATE value, consisting of five characters, in this case 3D000. These codes are standard to ODBC and ANSI SQL. When MariaDB cannot allocate a standard SQLSTATE code, a generic HY000, or general error, is used.
A string describing the error, in this case No database selected.

New error codes are being continually being added as new features are added. For a definitive list, see the file sql/share/errmsg-utf8.txt, as well as include/mysqld_error.h in the build directory, generated by the comp_err tool. Also, the perror tool can be used to get the error message which is associated with a given error code.

MariaDB-specific error codes

← Identifier to File Name Mapping
↑ SQL Language Structure ↑
Numeric Literals →

Comments

Content reproduced on this site is the property of its respective owners,
and this content is not reviewed in advance by MariaDB. The views, information and opinions
expressed by this content do not necessarily represent those of MariaDB or any other party.

Источник

This article will be looking into how to drop a table if it exists in the database.

How to DROP Table IF EXISTS in MySQL
How to DROP Temporary Table IF EXISTS in MySQL

How to DROP Table IF EXISTS in MySQL

What all does a DROP TABLE statement does?

DROP TABLE statement will remove one or more table ( we must have the DROP privilege for each table).
Will remove the table definition and all table data.
Will drop any triggers for the table.
If the table is partitioned, DROP TABLE will remove the partitions and all the data present in those partitions.

Syntax:- DROP TABLE [IF EXISTS] table_name [, table_name] …

IF EXISTS clause in the DROP statement is optional and has a role to play in its behaviour.

IF EXISTS clause present: When we run a DROP statement and if the given table does not exist, there will be no error occurring, but a warning message will be displayed.
IF EXISTS clause absent: When we run a DROP statement, and if the given table does not exist, the DROP statement will fail, displaying that an error has occurred for the non-existing tables which could not be deleted.

Let us look into an example. There is an existing table sale_details.

Advertisements

Run the below query to verify the existence of the table.

SHOW TABLES LIKE "sale_details";

Output:-

figure 1.1

The result shows that table sale_details exist in the database.We will now be dropping this table, including IF EXISTS in the DROP statement. Observe the below statement and its output.

DROP TABLE IF EXISTS sale_details;

Action Output Message: DROP TABLE IF EXISTS sale_details 0 row(s) affected 0.023 sec

Again verify the table exists or not by running query “SHOW TABLES LIKE “sale_details”;” and observe the output.

figure 1.2

As we can see in the output, no tables are present in the result. Hence the table was dropped successfully. Since our table sale_details table does not exist, we will again run the DROP TABLE statement, including the IF EXISTS clause. Observe the below query.

DROP TABLE IF EXISTS sale_details;

Action Output Message: DROP TABLE IF EXISTS sale_details 0 row(s) affected, 1 warning(s): 1051 Unknown table ‘riti_sales_db.sale_details’ 0.0017 sec

figure 1.3

It shows a warning and not an error. Let us remove the IF EXISTS clause and watch the output.

DROP TABLE sale_details;

Action Output Message: DROP TABLE sale_details Error Code: 1051. Unknown table ‘riti_sales_db.sale_details’ 0.0013 sec

figure 1.4

This time it shows an error and not a warning.

How to DROP Temporary Table IF EXISTS in MySQL

A temporary table is a table that will store a temporary result set, which can be retrieved many times in a single session. DROP statement works the same way for temporary tables as well.

Syntax:- DROP TEMPORARY TABLE [IF EXISTS] table_name

We will create a temporary table sales_person_department from the sale_details table.

CREATE TEMPORARY TABLE sales_person_department
SELECT sale_person_name,sales_department FROM sale_details;

Action Output Message: CREATE TEMPORARY TABLE sales_person_department SELECT sale_person_name,sales_department FROM sale_details 12 row(s) affected Records: 12 Duplicates: 0 Warnings: 0 0.0023 sec.

The temporary table sales_person_department got created successfully. Now try dropping it using the IF EXISTS clause in DROP statement.

DROP TEMPORARY TABLE IF EXISTS sales_person_department;

Action Output Message:- DROP TEMPORARY TABLE IF EXISTS sales_person_department 0 row(s) affected 0.00046 sec.

figure 1.5

Table sales_person_department has been dropped successfully.

If we re-run the query to drop the temporary table sales_person_department, we will get a warning message.

DROP TEMPORARY TABLE IF EXISTS sales_person_department;

Action Output Message: DROP TEMPORARY TABLE IF EXISTS sales_person_department 0 row(s) affected, 1 warning(s): 1051 Unknown table ‘riti_sales_db.sales_person_department’ 0.00035 sec

figure 1.6

We hope this article provides a good understanding of DROP TABLE IF EXISTS in MySQL. Good Luck !!!

Advertisements

Thanks for reading.

Источник

In our attempts to upgrade our Aurora 2 MySql 5.7 DB to Aurora 3 MySql 8.0, we are running into a pre-check that we are unable to resolve. The error is:

{
      "id": "schemaInconsistencyCheck",
      "title": "Schema inconsistencies resulting from file removal or corruption",
      "status": "OK",
      "description": "Error: Following tables show signs that either table datadir directory or frm file was removed/corrupted. Please check server logs, examine datadir to detect the issue and fix it before upgrade",
      "detectedProblems": [
        {
          "level": "Error",
          "dbObject": "trax",
          "description": "present in INFORMATION_SCHEMA's INNODB_SYS_TABLES table but missing from TABLES table"
        }
      ]
    }

Simple searches for this error yield this promising serverFault post. I followed the steps there:

Running (Query1)

select * from information_schema.innodb_sys_tables where name like '%#%';

yeilds two tables: trax/#sql-ib2245-1278264407-0 & trax/#sql-ib2248-918043578-0

Working with the latter and following the answer in the post yields some queries I attempted to no avail:

drop table trax.`#sql-ib2248-918043578-0`;
drop table trax.`#mysql50##sql-ib2248-918043578-0`;
use trax;
drop table `#sql-ib2248-918043578-0`;
drop table `#mysql50##sql-ib2248-918043578-0`;

All of which fail with a variation of the same error:

Error Code: 1051. Unknown table 'trax.#mysql50##sql-ib2248-918043578-0'

Error Code: 1051. Unknown table 'trax.#sql-ib2248-918043578-0'

There’s one comment towards the end that seems like it could be helpful but doesn’t seem to lead anywhere:

Note that the existence of an #sql-ib* table indicates that some of your data may be corrupt. Match the numeric ID in the #sql-ib table name to the TABLE_ID column of INNODB_SYS_TABLES to determine which table was affected. Be sure to inspect your data for integrity before proceeding.

For the two tables returned by Query1 above, both the TABLE_ID and the «numeric ID in the #sql-ib table name» match already, and I’m not sure where else that TABLE_ID might point:

TABLE_ID, NAME
2245, trax/#sql-ib2245-1278264407-0
2248, trax/#sql-ib2248-918043578-0

for more context:

select * from INFORMATION_SCHEMA.TABLES where table_schema = 'trax' and table_type = 'BASE TABLE'; -- returns 309 entries
select * from INFORMATION_SCHEMA.innodb_sys_tables where name like 'trax/%'; -- returns 311 entries

The difference is just those two tables, trax/#sql-ib2245-1278264407-0 & trax/#sql-ib2248-918043578-0, so I think that implies they don’t correspond to real tables (unlike the resolution found in this MySql bug) and thus can just be removed; but I’m not sure how to do that since DROP TABLE is failing and I don’t believe we have access to frm files through AWS.

I’ve already asked this question on AWS re:Post

This is the last error holding up our upgrade process, so any help/suggestions would be appreciated.

Источник

#1 01.04.2012 12:25:09

Ошибка 1051

#2 01.04.2012 12:27:56

Re: Ошибка 1051

#3 01.04.2012 12:36:31

Re: Ошибка 1051

rgbeast написал:

Introduction to the MySQL `DROP VIEW` statement

MySQL DROP VIEW statement examples

1) MySQL DROP VIEW – drop a view example

2) MySQL `DROP VIEW` – drop multiple views example

Summary

Contents

MariaDB-specific error codes

Comments

How to DROP Table IF EXISTS in MySQL

How to DROP Temporary Table IF EXISTS in MySQL

Читайте также:

#1 01.04.2012 12:25:09

Ошибка 1051

#2 01.04.2012 12:27:56

Re: Ошибка 1051

#3 01.04.2012 12:36:31

Re: Ошибка 1051

rgbeast написал:

Introduction to the MySQL DROP VIEW statement

MySQL DROP VIEW statement examples

1) MySQL DROP VIEW – drop a view example

2) MySQL DROP VIEW – drop multiple views example

Summary

Contents

MariaDB-specific error codes

Comments

How to DROP Table IF EXISTS in MySQL

How to DROP Temporary Table IF EXISTS in MySQL

Читайте также:

Introduction to the MySQL `DROP VIEW` statement

2) MySQL `DROP VIEW` – drop multiple views example