Error: ER_TABLE_EXISTS_ERROR: when connecting with mysql using node.js

Question

I'm getting Error: ER_TABLE_EXISTS_ERROR: when trying to connect with mysql using node.js. I'm totally beginner in using sql database with node. all tables successfully created. but I'm still getting this error. I cannot find what makes this error. how can I resolve this. thanks!

 const con = mysql.createConnection({
  host: "localhost",
  user: "root",
  password: "",
  database: "discussions",
});

con.connect((err) => {
  if (err) {
    throw err;
  }

  let usql =
    "CREATE TABLE users (id INT AUTO_INCREMENT PRIMARY KEY, name TEXT, profile TEXT)";
  con.query(usql, (err, result) => {
    if (err) {
      throw err;
    }
    console.log("User Table created");
  });

  let csql =
    "CREATE TABLE comments (id INT AUTO_INCREMENT PRIMARY KEY, comment TEXT, upvotes INT)";
  con.query(csql, (err, result) => {
    if (err) {
      throw err;
    }
    console.log("Comments Table created");
  });

  let rsql =
    "CREATE TABLE replies (id INT AUTO_INCREMENT PRIMARY KEY, reply TEXT, upvotes INT)";
  con.query(rsql, (err, result) => {
    if (err) {
      throw err;
    }

    console.log("Replies Table created");
  });

  let sql =
    "SELECT users.name AS user, comments.id AS comments FROM users JOIN comments ON users.comment_id = comments.id";
  con.query(sql, (err, result) => {
    if (err) {
      throw err;
    }

    console.log(result);
  });

  let msql =
    "SELECT comments.id AS comment, replies.id AS replyies FROM comments JOIN replies ON comment_id.reply_id = replies.id";
  con.query(msql, (err, result) => {
    if (err) {
      throw er;
    }
    console.log(result);
  });
});

The `CREATE TABLE` statements are executed each time you _connect_ to the database, but they must be executed only once in the lifetime of your database. If you don't know a better place where to put these statements, use [`CREATE TABLE IF NOT EXISTS`](https://dev.mysql.com/doc/refman/8.0/en/create-table.html). — Heiko Theißen, Sep 18 '22 at 10:06

score 0 · Answer 1 · answered Sep 19 '22 at 03:52

As @Heiko said in the comment section and according to your code. each time you connect to the database your code is going to create a table. in the first time it would be really fine and it will create tables for you. But after that when you run the code it tries to create those tables agin but the tables are already created that what the error says.

so please try using the following statement

CREATE TABLE IF NOT EXISTS

therefore your code should looks like this

 const con = mysql.createConnection({
  host: "localhost",
  user: "root",
  password: "",
  database: "discussions",
});

con.connect((err) => {
  if (err) {
    throw err;
  }

  let usql =
    "CREATE TABLE IF NOT EXISTS users (id INT AUTO_INCREMENT PRIMARY KEY, name TEXT, profile TEXT)";
  con.query(usql, (err, result) => {
    if (err) {
      throw err;
    }
    console.log("User Table created");
  });

  let csql =
    "CREATE TABLE IF NOT EXISTS comments (id INT AUTO_INCREMENT PRIMARY KEY, comment TEXT, upvotes INT)";
  con.query(csql, (err, result) => {
    if (err) {
      throw err;
    }
    console.log("Comments Table created");
  });

  let rsql =
    "CREATE TABLE IF NOT EXISTS replies (id INT AUTO_INCREMENT PRIMARY KEY, reply TEXT, upvotes INT)";
  con.query(rsql, (err, result) => {
    if (err) {
      throw err;
    }

    console.log("Replies Table created");
  });

  let sql =
    "SELECT users.name AS user, comments.id AS comments FROM users JOIN comments ON users.comment_id = comments.id";
  con.query(sql, (err, result) => {
    if (err) {
      throw err;
    }

    console.log(result);
  });

  let msql =
    "SELECT comments.id AS comment, replies.id AS replyies FROM comments JOIN replies ON comment_id.reply_id = replies.id";
  con.query(msql, (err, result) => {
    if (err) {
      throw er;
    }
    console.log(result);
  });
});

Error: ER_TABLE_EXISTS_ERROR: when connecting with mysql using node.js

1 Answers1