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

Question

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.

score 79 · Accepted Answer · answered Oct 30 '09 at 16:47

79

Works fine for me in 5.0.27

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

answered Oct 30 '09 at 16:47

Eli

5,500
1
29
27

9

Thanks Eli, you're right. It is my 3rd party client software that is raising this warning as an exception for me which is my problem. – user199559 Nov 02 '09 at 09:48

score 40 · Answer 2 · answered Aug 08 '10 at 12:33

40

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

answered Aug 08 '10 at 12:33

gdt

1,822
17
19

Interesting idea if obtuse. In some languages like CSS warnings are all but useless while in other languages like PHP warnings are very important to take note of. While I'm no master of SQL I am curious what other warnings can occur and whether or not ignoring them could lead to potential attack vectors? – John Jun 12 '17 at 20:42

score 16 · Answer 3 · edited Dec 18 '15 at 07:52

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

);

score 11 · Answer 4 · edited Jun 02 '18 at 05:21

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

score 4 · Answer 5 · answered Nov 23 '10 at 00:05

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)

score 0 · Answer 6 · answered Feb 08 '14 at 10:25

0

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)

answered Feb 08 '14 at 10:25

Vinay

433
1
5
11

I was having the same issue in Python. I had raise_on_warnings set to "true", so an exception was being raised even though I was using `IF NOT EXISTS`. – Pikamander2 Feb 06 '20 at 04:37

rubo77 · Answer 7 · 2017-06-22T04:30:00.307

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

score 0 · Answer 8 · edited Aug 14 '20 at 13:59

0

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

edited Aug 14 '20 at 13:59

HoldOffHunger

18,769
10
104
133

answered Dec 02 '18 at 17:44

nizz0k

471
1
8
23

score 0 · Answer 9 · answered Apr 23 '20 at 11:16

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.

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

9 Answers9

Linked