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 results in:

Table 't1' already exists Error 1050

I would have thought that creating the table "IF NOT EXISTS" would not throw errors. 

 Answers

Works fine for me in 5.0.27

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

---

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

);

---

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

---

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)