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

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 resu开发者_开发技巧lts 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.

---

Works fine for me in 5.0.27

I just get a warning (not an error) that the table exists;

---

As already stated, it's a warning not an error, but (if like me) you want things to run without warnings, you can disable that warning, then re-enable it again when you're done.

SET sql_notes = 0;      -- Temporarily disable the "Table already exists" warning
CREATE TABLE IF NOT EXISTS ...
SET sql_notes = 1;      -- And then re-enable the warning again

---

You can use the following query to create a table to a particular database in MySql.

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`)

);

---

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`)
);***

---

I have a solution to a problem that may also apply to you. My database was in a state where a DROP TABLE failed because it couldn't find the table... but a CREATE TABLE also failed because MySQL thought the table existed. (This state could easily mess with your IF NOT EXISTS clause).

I eventually found this solution:

sudo mysqladmin flush-tables

For me, without the sudo, I got the following error:

mysqladmin: refresh failed; error: 'Access denied; you need the RELOAD privilege for this operation'

(Running on OS X 10.6)

---

Create mysql connection with following parameter. "'raise_on_warnings': False". It will ignore the warning. e.g.

config = {'user': 'user','password': 'passwd','host': 'localhost','database': 'db',   'raise_on_warnings': False,}
cnx = mysql.connector.connect(**config)

---

I had a similar Problem as @CraigWalker on debian: My database was in a state where a DROP TABLE failed because it couldn't find the table, but a CREATE TABLE also failed because MySQL thought the table still existed. So the broken table still existed somewhere although it wasn't there when I looked in phpmyadmin.

I created this state by just copying the whole folder that contained a database with some MyISAM and some InnoDB tables

cp -a /var/lib/mysql/sometable /var/lib/mysql/test

(this is not recommended!)

All InnoDB tables where not visible in the new database test in phpmyadmin.

sudo mysqladmin flush-tables didn't help either.

My solution: I had to delete the new test database with drop database test and copy it with mysqldump instead:

mysqldump somedatabase -u username -p -r export.sql
mysql test -u username -p < export.sql

---

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.

---

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