Table already exists, with ERROR 1050 reported

This topic describes how to handle ERROR 1050, where a table to be created already exists in the MySQL-compatible mode of OceanBase Database.

Symptom

When you create a table named student, an error is reported, indicating that the table already exists.

obclient> CREATE TABLE student(
     id int ,
     name varchar(18),
     sex char(1),
     age int,
     address varchar(200),
       email varchar(100),
      date date,
      PRIMARY KEY (id)
       );
ERROR 1050 (42S01): Table 'student' already exists

The error codes corresponding to this error message are as follows:

• Error code: ERROR 1050

• Error code in OceanBase Database: 5020

• Error code compatible with MySQL Database: 1050

For more information about the error codes, see Overview of error messages.

Possible causes

If you add the IF NOT EXISTS keyword when you create a table, the system reports this error when the table already exists.

Troubleshooting procedure

Take the following steps to check whether a table already exists, rename the existing table, and then re-create the table.

1. Check whether this table already exists in the database. Here is an example:

   obclient> SHOW TABLES;
   +----------------+
   | Tables_in_test |
   +----------------+
   | student        |
   +----------------+
   1 row in set
   

2. If the table already exists, rename the existing table and then create the table again.

   For example, rename the existing student table as t_stu.

   obclient> ALTER TABLE student RENAME TO t_stu;
   Query OK, 0 rows affected
   

   Note

   If the table is no longer needed, you can also use the DROP TABLE statement to drop the table so that you can then create a new one.