CODEWITHSUNDEEP
mysql
André Scaravelli
André Scaravelli

Reputation: 271

Error 1050 (Table already exists) when I am creating a new column

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é

Upvotes: 4

Views: 15970

Answers (3)

Rahul Kr Daman
Rahul Kr Daman

Reputation: 413

Try This Method

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

Upvotes: 0

Nikita Bosik
Nikita Bosik

Reputation: 865

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.

Upvotes: 0

mal-wan
mal-wan

Reputation: 4476

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:

  1. 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.

  2. 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

  3. 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

  4. 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)

Upvotes: 2

Related Questions