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

User · Answer

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

User · Answer

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  lt  export sql

User · Answer

Works fine for me in 5 0 27  I just get a warning  not an error  that the table exists

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

[sql] MySQL "CREATE TABLE IF NOT EXISTS" -> Error 1050

Examples related to sql

Examples related to mysql

Examples related to mysql-error-1050