MySQL "CREATE TABLE IF NOT EXISTS" -> Error 1050

user_199559 0
Viewed 326707

Using the command:

CREATE TABLE IF NOT EXISTS `test`.`t1` (
    `col` VARCHAR(16) NOT NULL
) ENGINE=MEMORY;

Running this twice in the MySQL Query Browser results in:

Table 't1' already exists Error 1050

I would have thought that creating the table "IF NOT EXISTS" would not throw errors. Am I missing something or is this a bug? I am running version 5.1. Thanks.

mysql-error-1050 mysql sql
9 Answers
create database if not exists `test`;

USE `test`;

SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0;

/*Table structure for table `test` */

***CREATE TABLE IF NOT EXISTS `tblsample` (
  `id` int(11) NOT NULL auto_increment,   
  `recid` int(11) NOT NULL default '0',       
  `cvfilename` varchar(250)  NOT NULL default '',     
  `cvpagenumber`  int(11) NULL,     
  `cilineno` int(11)  NULL,    
  `batchname`  varchar(100) NOT NULL default '',
  `type` varchar(20) NOT NULL default '',    
  `data` varchar(100) NOT NULL default '',
   PRIMARY KEY  (`id`)
);***
user_89504900

If anyone is getting this error after a Phpmyadmin export, using the custom options and adding the "drop tables" statements cleared this right up.

user_49596920

Well there are lot of answeres already provided and lot are making sense too.

Some mentioned it is just warning and some giving a temp way to disable warnings. All that will work but add risk when number of transactions in your DB is high.

I came across similar situation today and here is the query I came up with... 

declare
begin
  execute immediate '
    create table "TBL" ("ID" number not null)';
  exception when others then
    if SQLCODE = -955 then null; else raise; end if;
end;
/

This is simple, if exception come while running query it will be suppressed. and you can use same for SQL or Oracle.

user_109153840
Related