Error 1050 when using MySQL’s “CREATE TABLE IF NOT EXISTS” command.

Problem

Using the command:

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

When you run this twice in the MySQL Query Browser, you get:

I assumed that establishing the table “IF NOT EXISTS” would not result in any issues. Is this a bug or am I missing something? Version 5.1 is what I’m using. Thanks.

Asked by user199559

Solution #1

In 5.0.27, everything works perfectly for me.

I only get a warning that the table exists (not an error).

Answered by Eli

Solution #2

It’s a warning, not an error, but if you’re like me and prefer things to run without warnings, you can disable the warning and then re-enable it when you’re finished.

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

Answered by gdt

Solution #3

In MySql, you can use the following query to create a table for a certain database.

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

);

Answered by Sachin Parse

Solution #4

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

Answered by Balkishan

Solution #5

I have a solution to a problem that you might find useful. A DROP TABLE failed because MySQL couldn’t discover the table, but a CREATE TABLE also failed because MySQL thought the table existed. (Your IF NOT EXISTS clause could be easily messed up by this state.)

I eventually found this solution:

sudo mysqladmin flush-tables

Without sudo, I get the following error:

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

(Running on OS X 10.6)

Answered by Craig Walker

Post is based on https://stackoverflow.com/questions/1650946/mysql-create-table-if-not-exists-error-1050