MySQL "Create Table If Not Exists" -> Error 1050

MySQL CREATE TABLE IF NOT EXISTS - Error 1050

Works fine for me in 5.0.27

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

Mysql 1050 Error Table already exists when in fact, it does not

Sounds like you have Schroedinger's table...

Seriously now, you probably have a broken table. Try:

• DROP TABLE IF EXISTS contenttype
• REPAIR TABLE contenttype
• If you have sufficient permissions, delete the data files (in /mysql/data/db_name)

MySQL 1050 Table doesn't exist, but MySQL thinks it does

My solution was to export all of the other databases and delete MySQL entirely.

From: http://johnmcostaiii.net/2011/removing-mysql-osx-lion/

sudo rm /usr/local/mysql
sudo rm -rf /usr/local/mysql*
sudo rm -rf /Library/StartupItems/MySQLCOM
sudo rm -rf /Library/PreferencePanes/My*
rm -rf ~/Library/PreferencePanes/My*
sudo rm -rf /Library/Receipts/mysql*
sudo rm -rf /Library/Receipts/MySQL*
sudo rm -rf /var/db/receipts/com.mysql.*

# Edit the following file, removing the line `MYSQLCOM=-YES-`.
# you may need sudo for write privileges to edit the file
# TIP: when using vim, use `dd` to delete the line and then `:wq` to save
#      the file
sudo vim /etc/hostconfig   # remove the line MYSQLCOM=-YES-

After reinstalling MySQL, the startup utilities, the preferences pane, and Workbench, I was able to successfully synchronize my database model.

@bancer's link in the comments may provide an alternative solution that doesn't require deleting MySQL entirely, but I'll be honest, it was quite overwhelming to look at that answer.

CREATE TABLE IF NOT EXISTS failing when the table exists

You should get a warning, not an error. What version are you running?
Anyway if you want to display erros type this before your SQL query:
SET sql_notes = 0;
and then type SET sql notes=1; after the query.

CREATE TABLE IF NOT EXISTS fails with table already exists

Try this

$query = "SELECT ID FROM USERS";
$result = mysqli_query($dbConnection, $query);

if(empty($result)) {
                $query = "CREATE TABLE USERS (
                          ID int(11) AUTO_INCREMENT,
                          EMAIL varchar(255) NOT NULL,
                          PASSWORD varchar(255) NOT NULL,
                          PERMISSION_LEVEL int,
                          APPLICATION_COMPLETED int,
                          APPLICATION_IN_PROGRESS int,
                          PRIMARY KEY  (ID)
                          )";
                $result = mysqli_query($dbConnection, $query);
}

This checks to see if anything is in the table and if it returns NULL you don't have a table.

Also there is no BOOLEAN datatype in mysql, you should INT and just set it to 1 or 0 when inserting into the table. You also don't need single quotes around everything, just when you are hardcoding data into the query.

Like this...

$query = "INSERT INTO USERS (EMAIL, PASSWORD, PERMISSION_LEVEL, APPLICATION_COMPLETED, APPLICATION_IN_PROGRESS) VALUES ('foobar@foobar.com', 'fjsdfbsjkbgs', 0, 0, 0)";

MySQL create table if not exists and insert record only if table was created

Combine the creation and insert into a single statement:

CREATE TABLE IF NOT EXISTS tableName (
    id int(9) NOT NULL, 
    col1 int(9) DEFAULT NULL, 
    col2 int(3) unsigned zerofill DEFAULT NULL,
    PRIMARY KEY(id)
)  ENGINE = InnoDB DEFAULT CHARSET = latin1
AS SELECT 1 AS id, 10 AS col1, 5 AS col2;

If it doesn't create the table, AS SELECT ... clause is ignored.

Adding foreign key to existing table gives error 1050 table already exists

So a team member figured this out. The one table was set with the type utf8_general, and another was set to the type default. I didn't think this was an issue, since the default is utf8_general, but apparently mysql just looks at the type names and not the underlying type.

---

Related Topics