CREATE TABLE IF NOT EXISTS fails with table already exists

Question

I have the following code:

$db_host = 'localhost';
$db_port = '3306';
$db_username = 'root';
$db_password = 'root';
$db_primaryDatabase = 'dsl_ams';

// Connect to the database, using the predefined database variables in /assets/repository/mysql.php
$dbConnection = new mysqli($db_host, $db_username, $db_password, $db_primaryDatabase);

// If there are errors (if the no# of errors is > 1), print out the error and cancel loading the page via exit();
if (mysqli_connect_errno()) {
    printf("Could not connect to MySQL databse: %s\n", mysqli_connect_error());
    exit();
}

$queryCreateUsersTable = "CREATE TABLE IF NOT EXISTS `USERS` (
    `ID` int(11) unsigned NOT NULL auto_increment,
    `EMAIL` varchar(255) NOT NULL default '',
    `PASSWORD` varchar(255) NOT NULL default '',
    `PERMISSION_LEVEL` tinyint(1) unsigned NOT NULL default '1',
    `APPLICATION_COMPLETED` boolean NOT NULL default '0',
    `APPLICATION_IN_PROGRESS` boolean NOT NULL default '0',
    PRIMARY KEY  (`ID`)
)";

if(!$dbConnection->query($queryCreateUsersTable)){
    echo "Table creation failed: (" . $dbConnection->errno . ") " . $dbConnection->error;
}

Which outputs...

Table creation failed: (1050) Table '`dsl_ams`.`USERS`' already exists

What I don't understand is: isn't IF NOT EXISTS supposed to cancel the execution of the SQL query if that table already exists? In other words, if the table exists, shouldn't it exit that if statement and not echo anything out at all, and not attempt to execute the query?

Just trying to find the best way to "create a table if it doesn't exist" without outputting anything to the user.

`$queryCreateUsersTable!=$queryCreateTable`, I'd enable notices to see that kind of errors. — Wrikken, May 14 '13 at 23:47
"enable notices"? what do you mean? and yeah, total derp on my part. — Samuel Stiles, May 14 '13 at 23:49
Setting error_reporting to the correct level (`error_reporting(E_ALL | E_STRICT);` is what I'd use during developing) & `ini_set('display_errors',1);` on your development environment, `ini_set('log_errors',1);` on your production box (no need to display errors to end users) — Wrikken, May 14 '13 at 23:51
I updated my question; fixed the previous issue, ran into a new one. — Samuel Stiles, May 15 '13 at 00:00
Hm, that _should_ be only a warning, not an error if you use `IF NOT EXISTS`... — Wrikken, May 15 '13 at 00:18
Seems to be an issue on windows machines: http://ledyardconsulting.blogspot.co.at/2011/03/mysql-error-1050-table-already-exists.html — Philipp, May 15 '13 at 00:20
Schroedinger's table... :-) http://stackoverflow.com/questions/3302476/mysql-1050-error-table-already-exists-when-in-fact-it-does-not — Philipp, May 15 '13 at 00:22

score 26 · Accepted Answer · edited Jun 13 '20 at 10:48

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)";

I'd use `SHOW TABLES LIKE 'USERS'` myself. Bear in mind this is case sensitive. — But those new buttons though.., Aug 06 '15 at 20:22
I'm not exactly a MySQL expert, but won't this fail if the table exists but is empty? — Frank Schmitt, Apr 30 '18 at 14:23

score 4 · Answer 2 · edited Jun 13 '20 at 10:48

4

To avoid outputting anything, test for the table in your php before trying to create the table. For example,

$querycheck='SELECT 1 FROM `USERS`';

$query_result=$dbConnection->query($querycheck);

if ($query_result !== FALSE)
{
 // table exists
} else
{
// table does not exist, create here.
}

edited Jun 13 '20 at 10:48

Dharman

30,962
25
85
135

answered May 15 '13 at 00:14

Eduardo Vieira

89
5

2

What about if the table is created but doesn't have any records yet? – Andres Ramos Oct 20 '16 at 15:22

score 2 · Answer 3 · answered Jun 11 '20 at 10:53

This old post is just a showcase of bad practices and inconsistent answers. A pity, closing it as a dupe won't close it from the public and it will keep on with its disinformation mission.

A short fact-check:

if your system is okay, then CREATE TABLE IF NOT EXISTS should work.
in case there is a ghost table, refer to this post for the remedy
to check whether a table exists, never select anything from the table, it will cause an error
- (however, contrary to a wrong notion expressed in many comments, an empty table won't return an empty result. An empty result means an error. To check whether your query returned any rows you have to fetch them)
instead, run a SHOW TABLES LIKE 'Users' query, fetch the row into a variable and see whether it's empty or not.

score -1 · Answer 4 · answered Jun 11 '20 at 10:33

This one will connect to mysql check if DB exists. If yes it will check if table exists. if none of them exists it will create it automatically.

$servername = "hostname";
    $username = "username";
    $password = "password";
    $dbname = "database_name";
    // Create connection
    $conn = new mysqli($servername, $username, $password);
    // Check connection
    if ($conn->connect_error) {
      die("Connection failed: " . $conn->connect_error);
    }

// Create database
$sql = "CREATE DATABASE IF NOT EXISTS database_name";
if ($conn->query($sql) === TRUE) {
  $conn = new mysqli($servername, $username, $password, $dbname);
  // Check connection
  if ($conn->connect_error) {
    die("Connection failed: " . $conn->connect_error);
  }
  $sql1 = "CREATE TABLE IF NOT EXISTS 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))";
  if($conn->query($sql1) === TRUE) {
    echo "Database and Table Online";
  }else{
    echo "Database and Table Offline" . $conn->error;
  }
} else {
  echo "Error creating database: " . $conn->error;
}

$conn->close();

He is using PHP procedural, and probably in other files, he is mixing those with OOP. probably that's the issue, not the code itself. — Michal Tymburski, Jul 09 '20 at 08:00
No, it is never an issue, technically you can mix the styles freely, although it's a bad style of course — Your Common Sense, Jul 09 '20 at 08:06

score -2 · Answer 5 · answered Nov 02 '16 at 14:28

-2

How about you only show the error if the error number is not 1050?

if(!$dbConnection->query($queryCreateUsersTable)){
  if($dbConnection->errno != 1050){
    echo "Table creation failed: (" . $dbConnection->errno . ") " . $dbConnection->error;
  }
}

answered Nov 02 '16 at 14:28

Aminah Nuraini

18,120
8
90
108

CREATE TABLE IF NOT EXISTS fails with table already exists

5 Answers5

Linked

Related