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.

9 Answers

Works fine for me in 5.0.27

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

1

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

1

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)

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)

1

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.

Your Answer

Sign up or log in

Sign up using Google Sign up using Facebook Sign up using Email and Password Submit

Post as a guest

Post Your Answer Discard

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy