How to load a schema into Node-MySQL ClearDB Azure?

Question

I've got a schema that I want to load into attached to my Azure/Node server:

USE mydatabase;

CREATE TABLE users(
    id BIGINT NOT NULL AUTO_INCREMENT,
    first_name VARCHAR(40),
    last_name VARCHAR(40)
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP NOT NULL,
    PRIMARY KEY(user_id)
)

When the server connects using node-mysql, it says ER_NO_SUCH_TABLE.

Any suggestions on how to proceed?

EDIT: Here's the node code:

const mysql = require("mysql")

function connectToDB () {
  const connection_params = {
    host: process.env.db_host || "localhost",
    user: process.env.db_username || "root",
    password: process.env.db_password || "",
    database: process.env.db_database || "test_database"
  }
  const connection = mysql.createConnection(connection_params)
  connection.connect(console.log)
  return connection
}

module.exports = connectToDB

Do you use any ORM framework like sequelize? Please paste more details: codes, etc. — Peter Pan, Nov 10 '15 at 08:25
Using node-mysql, which negotiates connections between MySQL and Node, against which I can write SQL queries. So it's not an ORM. Any other details you think I should add? — mLuby, Nov 11 '15 at 01:44
I knew node-mysql is a mysql driver for nodejs. Could you supply your node code to help testing the issue? — Peter Pan, Nov 11 '15 at 01:55
I tried to test your code on my local mysql or azure cleardb, but didn't get any errors both. My steps:1.create a dir & npm install mysql in it; 2. remove the define header of function `connectToDB` & replace with my connect info & run it. Any more details can help reproducing your issue? — Peter Pan, Nov 11 '15 at 09:35

score 1 · Answer 1 · answered Nov 13 '15 at 04:18

I tried to reproduce the issue, but failed to get any errors when the server connects to local mysql and Azure ClearDB using node-mysql. I suggest you to check the mysql carefully by using some third-party GUI admin tools like HeidiSQL for Windows or Dbeaver for any platform support Java.

You can download them at http://www.heidisql.com/ and http://dbeaver.jkiss.org/

I think you need to add symbol ; for each JS sentences and use var instead of const for declaring a varibale because of following the JavaScript Specification and the node-mysql doc, although it works fine without symbol ; on NodeJS.

Please refer to these docs below:

For semiconlon ;: https://developer.mozilla.org/en-US/docs/Web/JavaScript/Reference/Statements
For var & const: https://developer.mozilla.org/en-US/docs/Web/JavaScript/Reference/Statements/var and https://developer.mozilla.org/en-US/docs/Web/JavaScript/Reference/Statements/const
For node-mysql: https://www.npmjs.com/package/mysql

When I connected MySQL and loaded table schema to create a table, I found that your SQL DDL sentence is not correct. Please refer to the mysql offical docs http://dev.mysql.com/doc/refman/5.6/en/create-table.html and http://dev.mysql.com/doc/refman/5.6/en/timestamp-initialization.html. Note: For date type DATETIME & TIMESTAMPE, the mysql offical docs says:

DEFAULT CURRENT_TIMESTAMP and ON UPDATE CURRENT_TIMESTAMP cannot be used with DATETIME columns.

So Here are the table ddl sql and code updated.

Table DDL:

CREATE TABLE users (
    id BIGINT NOT NULL AUTO_INCREMENT,
    first_name VARCHAR(40),
    last_name VARCHAR(40),
    created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY(id)
)

Code:

var mysql = require('mysql');

var connection_parames = "mysql://user:password@host/test_database";

var connection = mysql.createConnection(connection_parames);

var table_ddl = "CREATE TABLE users ("+
    "id BIGINT NOT NULL AUTO_INCREMENT,"+
    "first_name VARCHAR(40),"+
    "last_name VARCHAR(40),"+
    "created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,"+
    "PRIMARY KEY(id)"+
")";

connection.query(table_ddl, function(err, result) {
    if (err) {
        console.log(err);
    }
    console.log(result);
});

How to load a schema into Node-MySQL ClearDB Azure?

1 Answers1