Ошибка 1050 sql - Не ошибается лишь тот, кто ничего не делает!

I’m adding this table:

CREATE TABLE contenttype (
        contenttypeid INT UNSIGNED NOT NULL AUTO_INCREMENT,
        class VARBINARY(50) NOT NULL,
        packageid INT UNSIGNED NOT NULL,
        canplace ENUM('0','1') NOT NULL DEFAULT '0',
        cansearch ENUM('0','1') NOT NULL DEFAULT '0',
        cantag ENUM('0','1') DEFAULT '0',
        canattach ENUM('0','1') DEFAULT '0',
        isaggregator ENUM('0', '1') NOT NULL DEFAULT '0',
        PRIMARY KEY (contenttypeid),
        UNIQUE KEY packageclass (packageid, class)
);

And I get a 1050 «table already exists»

But the table does NOT exist. Any ideas?

EDIT: more details because everyone seems to not believe me

DESCRIBE contenttype

yields:

1146 — Table ‘gunzfact_vbforumdb.contenttype’ doesn’t exist

and

CREATE TABLE gunzfact_vbforumdb.contenttype(
contenttypeid INT UNSIGNED NOT NULL AUTO_INCREMENT ,
class VARBINARY( 50 ) NOT NULL ,
packageid INT UNSIGNED NOT NULL ,
canplace ENUM( '0', '1' ) NOT NULL DEFAULT '0',
cansearch ENUM( '0', '1' ) NOT NULL DEFAULT '0',
cantag ENUM( '0', '1' ) DEFAULT '0',
canattach ENUM( '0', '1' ) DEFAULT '0',
isaggregator ENUM( '0', '1' ) NOT NULL DEFAULT '0',
PRIMARY KEY ( contenttypeid ) ,

Yields:

1050 — Table ‘contenttype’ already exists

Источник

I am trying to create a new column in a mysql table using the following command:

ALTER TABLE PEDIDOS_DETALHE
ADD COLUMN QTD_CAIXAS INT NOT NULL DEFAULT ‘0’;

When I execute this command, I get the following error:

Error Code: 1050. Table ‘magazine/#sql-ib583’ already exists

The mysql gives an error in a table with another name.

What is the problem? I didn’t understand the error.

Thanks a lot

André

asked Oct 20, 2015 at 1:22

Sounds like you’ve got an orphaned temp table.

Have a look at the «Orphaned Intermediate Tables» section in the following link for troubleshooting:

https://dev.mysql.com/doc/refman/5.0/en/innodb-troubleshooting-datadict.html

If MySQL crashes in the middle of an ALTER TABLE operation, you may be left with an orphaned intermediate table. Intermediate table names begin with “#sql-”. In your data directory you will see an #sql-.ibd file and an accompanying #sql-.frm file with the same name. The intermediate table is also listed in Table Monitor output.

If both the #sql-.ibd and #sql-.frm files appear in your data directory, drop the intermediate table by issuing a DROP TABLE statement, enclosing the table name in backticks. For example:

mysql> DROP TABLE #sql-1291_3;
Query OK, 0 rows affected (0.01 sec)

Enclosing the table name in backticks is required to perform SQL statements on table names with special characters such as “#”.

If there is no table format file (#sql-.frm file) in your data directory or the DROP TABLE operation fails, create a new .frm file that matches the table schema of the #sql-.ibd file (it must have the same columns and indexes defined). To do this, perform the following steps:

Determine if the #sql-*.ibd file has a pre-ALTER or post-ALTER schema definition. You can view the columns and indexes of the intermediate table using the Table Monitor.

Once you have determined if the #sql-.ibd file has a pre-ALTER or post-ALTER schema definition, create a matching #sql-.frm file in a different database directory. For example, if an intermediate table has a post-ALTER schema definition, create an .frm file that matches the altered schema definition:

mysql> CREATE TABLE tmp LIKE employees.salaries; ALTER TABLE tmp DROP COLUMN to_date;
Query OK, 0 rows affected (0.02 sec)

Query OK, 0 rows affected (0.06 sec)
Records: 0 Duplicates: 0 Warnings: 0

Copy the .frm file to the database directory where the orphaned table is located and rename it to match the name of the #sql-*.ibd file

shell> cp tmp.frm employees/#sql-sql-1291_3.frm

Drop the intermediate table by issuing a DROP TABLE statement, enclosing the table name in backticks. For example:

mysql> DROP TABLE #sql-1291_3;
Query OK, 0 rows affected (0.01 sec)

answered Oct 20, 2015 at 1:40

In my case problem was missing ALTER permission, so I had to grant it like

mysql -u root -p
/* type your password */
GRANT ALTER ON yourschema.* TO 'youruser'@'%';

Without this permission MySQL Workbench modifies original ALTER query and tries to create my table – and obviously fails as the table already exists.

answered Apr 21, 2018 at 0:59

Nikita BosikNikita Bosik

8411 gold badge14 silver badges20 bronze badges

Try This Method

ALTER TABLE table_name ADD COLUMN IF NOT EXISTS column_name tinyint(1) DEFAULT 0;

answered Dec 4, 2019 at 8:11

Источник

If you’re getting an error that reads something like “ERROR 1050 (42S01): Table ‘customers’ already exists” when trying to create a table in MySQL, it’s probably because there’s already a table in the database with the same name.

To fix this issue, either change the name of the table you’re trying to create, or check the existing table to see if it’s the one you actually need.

Example of Error

Here’s an example of code that produces the error:

CREATE TABLE Customers (
  CustomerId int NOT NULL PRIMARY KEY,
  CustomerName varchar(60) NOT NULL
  );

Result:

ERROR 1050 (42S01): Table 'customers' already exists

In this case, I’m trying to create a table called Customers, but it already exists in the database.

Solution 1

The most obvious solution is to change the name of the table we’re creating:

CREATE TABLE Customers2 (
  CustomerId int NOT NULL PRIMARY KEY,
  CustomerName varchar(60) NOT NULL
  );

Result:

Query OK, 0 rows affected (0.02 sec)

Here, I simply renamed the table to Customers2. In practice, we would probably give it a more appropriate name.

We should also be mindful that if there’s already a table with the same name as the one we’re trying to create, there’s a possibility that our desired table has already been created. In this case we wouldn’t need to recreate it (unless we had good reason). We could either just go ahead and use it, or we could alter it to suit our new requirements.

Solution 2

Another way to deal with this error is to suppress it. We can modify our CREATE TABLE statement to only create the table if it doesn’t already exist:

CREATE TABLE IF NOT EXISTS Customers (
  CustomerId int NOT NULL PRIMARY KEY,
  CustomerName varchar(60) NOT NULL
  );

Result:

Query OK, 0 rows affected, 1 warning (0.00 sec)

In this case, we got a warning. Let’s check the warning:

SHOW WARNINGS;

Result:

+-------+------+----------------------------------+
| Level | Code | Message                          |
+-------+------+----------------------------------+
| Note  | 1050 | Table 'Customers' already exists |
+-------+------+----------------------------------+
1 row in set (0.00 sec)

The warning explicitly tells us that the table already exists.

The Table REALLY Doesn’t Exist?

If you believe that the table really doesn’t exist, perhaps there’s something else going on. See this article on Stack Overflow for a discussion on possible solutions.

Источник

Выдаёт ошибку — #1050 — Table ‘users’ already exists.

CREATE TABLE users (
    usersId int(11) PRIMARY KEY AUTO_INCREMENT NOT NULL,
    usersEmail varchar(128) NOT NULL,
    usersUid varchar(128) NOT NULL,
    usersPwd varchar(128) NOT NULL
);

Вопрос задан

04 авг. 2022
1657 просмотров

CREATE TABLE IF NOT EXISTS users (

Потому что нельзя создать таблицу с именем существующей.

DROP TABLE users;
CREATE TABLE users (
    usersId int(11) PRIMARY KEY AUTO_INCREMENT NOT NULL,
    usersEmail varchar(128) NOT NULL,
    usersUid varchar(128) NOT NULL,
    usersPwd varchar(128) NOT NULL
);

Пригласить эксперта

Показать ещё
Загружается…

05 июн. 2023, в 12:46

5000 руб./за проект

05 июн. 2023, в 12:40

40000 руб./за проект

05 июн. 2023, в 12:34

10000 руб./за проект

Минуточку внимания

Источник

Я добавляю эту таблицу:

CREATE TABLE contenttype (
        contenttypeid INT UNSIGNED NOT NULL AUTO_INCREMENT,
        class VARBINARY(50) NOT NULL,
        packageid INT UNSIGNED NOT NULL,
        canplace ENUM('0','1') NOT NULL DEFAULT '0',
        cansearch ENUM('0','1') NOT NULL DEFAULT '0',
        cantag ENUM('0','1') DEFAULT '0',
        canattach ENUM('0','1') DEFAULT '0',
        isaggregator ENUM('0', '1') NOT NULL DEFAULT '0',
        PRIMARY KEY (contenttypeid),
        UNIQUE KEY packageclass (packageid, class)
);

И я получаю таблицу 1050 «уже существует»

Но таблица НЕ существует. Любые идеи?

EDIT: подробности, потому что все, кажется, не верят мне:)

DESCRIBE contenttype

дает:

1146 — Таблица ‘gunzfact_vbforumdb.contenttype’ не существует

CREATE TABLE gunzfact_vbforumdb.contenttype(
contenttypeid INT UNSIGNED NOT NULL AUTO_INCREMENT ,
class VARBINARY( 50 ) NOT NULL ,
packageid INT UNSIGNED NOT NULL ,
canplace ENUM( '0', '1' ) NOT NULL DEFAULT '0',
cansearch ENUM( '0', '1' ) NOT NULL DEFAULT '0',
cantag ENUM( '0', '1' ) DEFAULT '0',
canattach ENUM( '0', '1' ) DEFAULT '0',
isaggregator ENUM( '0', '1' ) NOT NULL DEFAULT '0',
PRIMARY KEY ( contenttypeid ) ,

Урожайность: