How to Create TABLE with MySQL Async

Hey, I’m getting this error when I try to make a table with MySQL.Async

MySQL.Async.execute([[
CREATE TABLE IF NOT EXISTS vrp_users(
  id INTEGER AUTO_INCREMENT,
  last_login VARCHAR(255),
  whitelisted BOOLEAN,
  banned BOOLEAN,
  CONSTRAINT pk_user PRIMARY KEY(id)
);
CREATE TABLE IF NOT EXISTS vrp_user_ids(
  identifier VARCHAR(255),
  user_id INTEGER,
  CONSTRAINT pk_user_ids PRIMARY KEY(identifier),
  CONSTRAINT fk_user_ids_users FOREIGN KEY(user_id) REFERENCES vrp_users(id) ON DELETE CASCADE
);
CREATE TABLE IF NOT EXISTS vrp_user_data(
  user_id INTEGER,
  dkey VARCHAR(255),
  dvalue TEXT,
  CONSTRAINT pk_user_data PRIMARY KEY(user_id,dkey),
  CONSTRAINT fk_user_data_users FOREIGN KEY(user_id) REFERENCES vrp_users(id) ON DELETE CASCADE
);
CREATE TABLE IF NOT EXISTS vrp_srv_data(
  dkey VARCHAR(255),
  dvalue TEXT,
  CONSTRAINT pk_srv_data PRIMARY KEY(dkey)
);
]])

How do I fix it?

Hey :slightly_smiling_face:
Seems like maybe there is an issue in the synthax you are using, in the mysql-async doc they recommend the following :

MySQL.Async.execute("CREATE TABLE IF NOT EXISTS vrp_users(
  id INTEGER AUTO_INCREMENT,
  last_login VARCHAR(255),
  whitelisted BOOLEAN,
  banned BOOLEAN,
  CONSTRAINT pk_user PRIMARY KEY(id)
);", {}, function(result) print(result) end)

You should maybe try the following synthax and call the ‘execute()’ function for each query, then if it worked you can try to call the ‘execute()’ function only once for all the queries (and see if it works this way)

Thanks for the Answer, its work. But I can get this to work

MySQL.Async.execute("ALTER TABLE vrp_user_vehicles ADD IF NOT EXISTS veh_type varchar(255) NOT NULL DEFAULT 'default';
ALTER TABLE vrp_user_vehicles ADD IF NOT EXISTS vehicle_plate varchar(255) NOT NULL;
ALTER TABLE vrp_user_vehicles ADD IF NOT EXISTS vehicle_colorprimary varchar(255) DEFAULT NULL;
ALTER TABLE vrp_user_vehicles ADD IF NOT EXISTS vehicle_colorsecondary varchar(255) DEFAULT NULL;
ALTER TABLE vrp_user_vehicles ADD IF NOT EXISTS vehicle_pearlescentcolor varchar(255) DEFAULT NULL;
ALTER TABLE vrp_user_vehicles ADD IF NOT EXISTS vehicle_wheelcolor varchar(255) DEFAULT NULL;
ALTER TABLE vrp_user_vehicles ADD IF NOT EXISTS vehicle_plateindex varchar(255) DEFAULT NULL;
ALTER TABLE vrp_user_vehicles ADD IF NOT EXISTS vehicle_neoncolor1 varchar(255) DEFAULT NULL;
ALTER TABLE vrp_user_vehicles ADD IF NOT EXISTS vehicle_neoncolor2 varchar(255) DEFAULT NULL;
ALTER TABLE vrp_user_vehicles ADD IF NOT EXISTS vehicle_neoncolor3 varchar(255) DEFAULT NULL;
ALTER TABLE vrp_user_vehicles ADD IF NOT EXISTS vehicle_windowtint varchar(255) DEFAULT NULL;
ALTER TABLE vrp_user_vehicles ADD IF NOT EXISTS vehicle_wheeltype varchar(255) DEFAULT NULL;
ALTER TABLE vrp_user_vehicles ADD IF NOT EXISTS vehicle_mods0 varchar(255) DEFAULT NULL;
ALTER TABLE vrp_user_vehicles ADD IF NOT EXISTS vehicle_mods1 varchar(255) DEFAULT NULL;
ALTER TABLE vrp_user_vehicles ADD IF NOT EXISTS vehicle_mods2 varchar(255) DEFAULT NULL;
ALTER TABLE vrp_user_vehicles ADD IF NOT EXISTS vehicle_mods3 varchar(255) DEFAULT NULL;
ALTER TABLE vrp_user_vehicles ADD IF NOT EXISTS vehicle_mods4 varchar(255) DEFAULT NULL;
ALTER TABLE vrp_user_vehicles ADD IF NOT EXISTS vehicle_mods5 varchar(255) DEFAULT NULL;
ALTER TABLE vrp_user_vehicles ADD IF NOT EXISTS vehicle_mods6 varchar(255) DEFAULT NULL;
ALTER TABLE vrp_user_vehicles ADD IF NOT EXISTS vehicle_mods7 varchar(255) DEFAULT NULL;
ALTER TABLE vrp_user_vehicles ADD IF NOT EXISTS vehicle_mods8 varchar(255) DEFAULT NULL;
ALTER TABLE vrp_user_vehicles ADD IF NOT EXISTS vehicle_mods9 varchar(255) DEFAULT NULL;
ALTER TABLE vrp_user_vehicles ADD IF NOT EXISTS vehicle_mods10 varchar(255) DEFAULT NULL;
ALTER TABLE vrp_user_vehicles ADD IF NOT EXISTS vehicle_mods11 varchar(255) DEFAULT NULL;
ALTER TABLE vrp_user_vehicles ADD IF NOT EXISTS vehicle_mods12 varchar(255) DEFAULT NULL;
ALTER TABLE vrp_user_vehicles ADD IF NOT EXISTS vehicle_mods13 varchar(255) DEFAULT NULL;
ALTER TABLE vrp_user_vehicles ADD IF NOT EXISTS vehicle_mods14 varchar(255) DEFAULT NULL;
ALTER TABLE vrp_user_vehicles ADD IF NOT EXISTS vehicle_mods15 varchar(255) DEFAULT NULL;
ALTER TABLE vrp_user_vehicles ADD IF NOT EXISTS vehicle_mods16 varchar(255) DEFAULT NULL;
ALTER TABLE vrp_user_vehicles ADD IF NOT EXISTS vehicle_turbo varchar(255) NOT NULL DEFAULT 'off';
ALTER TABLE vrp_user_vehicles ADD IF NOT EXISTS vehicle_tiresmoke varchar(255) NOT NULL DEFAULT 'off';
ALTER TABLE vrp_user_vehicles ADD IF NOT EXISTS vehicle_xenon varchar(255) NOT NULL DEFAULT 'off';
ALTER TABLE vrp_user_vehicles ADD IF NOT EXISTS vehicle_mods23 varchar(255) DEFAULT NULL;
ALTER TABLE vrp_user_vehicles ADD IF NOT EXISTS vehicle_mods24 varchar(255) DEFAULT NULL;
ALTER TABLE vrp_user_vehicles ADD IF NOT EXISTS vehicle_neon0 varchar(255) DEFAULT NULL;
ALTER TABLE vrp_user_vehicles ADD IF NOT EXISTS vehicle_neon1 varchar(255) DEFAULT NULL;
ALTER TABLE vrp_user_vehicles ADD IF NOT EXISTS vehicle_neon2 varchar(255) DEFAULT NULL;
ALTER TABLE vrp_user_vehicles ADD IF NOT EXISTS vehicle_neon3 varchar(255) DEFAULT NULL;
ALTER TABLE vrp_user_vehicles ADD IF NOT EXISTS vehicle_bulletproof varchar(255) DEFAULT NULL;
ALTER TABLE vrp_user_vehicles ADD IF NOT EXISTS vehicle_smokecolor1 varchar(255) DEFAULT NULL;
ALTER TABLE vrp_user_vehicles ADD IF NOT EXISTS vehicle_smokecolor2 varchar(255) DEFAULT NULL;
ALTER TABLE vrp_user_vehicles ADD IF NOT EXISTS vehicle_smokecolor3 varchar(255) DEFAULT NULL;
ALTER TABLE vrp_user_vehicles ADD IF NOT EXISTS vehicle_modvariation varchar(255) NOT NULL DEFAULT 'off';")


What have I made wrong?

He doesn’t like the fact that you are adding a carriage return at the and of each query (it breaks the string), even if it’s not pretty put all the queries on the same line (back to back) for now.

What do you mean with (back to back)?

As I said : “on the same line”

("SELECT .... ; SELECT ....; UPDATE ...;")

Instead of :

("SELECT ...;
SELECT ...;
UPDATE ...;) 

So all ALTER TABLE needs to be on one line?

Now I get a new error message

MySQL.Async.execute("ALTER TABLE vrp_user_vehicles ADD IF NOT EXISTS veh_type varchar(255) NOT NULL DEFAULT 'default'; ALTER TABLE vrp_user_vehicles ADD IF NOT EXISTS vehicle_plate varchar(255) NOT NULL; ALTER TABLE vrp_user_vehicles ADD IF NOT EXISTS vehicle_colorprimary varchar(255) DEFAULT NULL; ALTER TABLE vrp_user_vehicles ADD IF NOT EXISTS vehicle_colorsecondary varchar(255) DEFAULT NULL; ALTER TABLE vrp_user_vehicles ADD IF NOT EXISTS vehicle_pearlescentcolor varchar(255) DEFAULT NULL; ALTER TABLE vrp_user_vehicles ADD IF NOT EXISTS vehicle_wheelcolor varchar(255) DEFAULT NULL; ALTER TABLE vrp_user_vehicles ADD IF NOT EXISTS vehicle_plateindex varchar(255) DEFAULT NULL; ALTER TABLE vrp_user_vehicles ADD IF NOT EXISTS vehicle_neoncolor1 varchar(255) DEFAULT NULL; ALTER TABLE vrp_user_vehicles ADD IF NOT EXISTS vehicle_neoncolor2 varchar(255) DEFAULT NULL; ALTER TABLE vrp_user_vehicles ADD IF NOT EXISTS vehicle_neoncolor3 varchar(255) DEFAULT NULL; ALTER TABLE vrp_user_vehicles ADD IF NOT EXISTS vehicle_windowtint varchar(255) DEFAULT NULL; ALTER TABLE vrp_user_vehicles ADD IF NOT EXISTS vehicle_wheeltype varchar(255) DEFAULT NULL; ALTER TABLE vrp_user_vehicles ADD IF NOT EXISTS vehicle_mods0 varchar(255) DEFAULT NULL; ALTER TABLE vrp_user_vehicles ADD IF NOT EXISTS vehicle_mods1 varchar(255) DEFAULT NULL; ALTER TABLE vrp_user_vehicles ADD IF NOT EXISTS vehicle_mods2 varchar(255) DEFAULT NULL; ALTER TABLE vrp_user_vehicles ADD IF NOT EXISTS vehicle_mods3 varchar(255) DEFAULT NULL; ALTER TABLE vrp_user_vehicles ADD IF NOT EXISTS vehicle_mods4 varchar(255) DEFAULT NULL; ALTER TABLE vrp_user_vehicles ADD IF NOT EXISTS vehicle_mods5 varchar(255) DEFAULT NULL; ALTER TABLE vrp_user_vehicles ADD IF NOT EXISTS vehicle_mods6 varchar(255) DEFAULT NULL; ALTER TABLE vrp_user_vehicles ADD IF NOT EXISTS vehicle_mods7 varchar(255) DEFAULT NULL; ALTER TABLE vrp_user_vehicles ADD IF NOT EXISTS vehicle_mods8 varchar(255) DEFAULT NULL; ALTER TABLE vrp_user_vehicles ADD IF NOT EXISTS vehicle_mods9 varchar(255) DEFAULT NULL; ALTER TABLE vrp_user_vehicles ADD IF NOT EXISTS vehicle_mods10 varchar(255) DEFAULT NULL; ALTER TABLE vrp_user_vehicles ADD IF NOT EXISTS vehicle_mods11 varchar(255) DEFAULT NULL; ALTER TABLE vrp_user_vehicles ADD IF NOT EXISTS vehicle_mods12 varchar(255) DEFAULT NULL; ALTER TABLE vrp_user_vehicles ADD IF NOT EXISTS vehicle_mods13 varchar(255) DEFAULT NULL; ALTER TABLE vrp_user_vehicles ADD IF NOT EXISTS vehicle_mods14 varchar(255) DEFAULT NULL; ALTER TABLE vrp_user_vehicles ADD IF NOT EXISTS vehicle_mods15 varchar(255) DEFAULT NULL; ALTER TABLE vrp_user_vehicles ADD IF NOT EXISTS vehicle_mods16 varchar(255) DEFAULT NULL; ALTER TABLE vrp_user_vehicles ADD IF NOT EXISTS vehicle_turbo varchar(255) NOT NULL DEFAULT 'off'; ALTER TABLE vrp_user_vehicles ADD IF NOT EXISTS vehicle_tiresmoke varchar(255) NOT NULL DEFAULT 'off'; ALTER TABLE vrp_user_vehicles ADD IF NOT EXISTS vehicle_xenon varchar(255) NOT NULL DEFAULT 'off'; ALTER TABLE vrp_user_vehicles ADD IF NOT EXISTS vehicle_mods23 varchar(255) DEFAULT NULL; ALTER TABLE vrp_user_vehicles ADD IF NOT EXISTS vehicle_mods24 varchar(255) DEFAULT NULL; ALTER TABLE vrp_user_vehicles ADD IF NOT EXISTS vehicle_neon0 varchar(255) DEFAULT NULL; ALTER TABLE vrp_user_vehicles ADD IF NOT EXISTS vehicle_neon1 varchar(255) DEFAULT NULL; ALTER TABLE vrp_user_vehicles ADD IF NOT EXISTS vehicle_neon2 varchar(255) DEFAULT NULL; ALTER TABLE vrp_user_vehicles ADD IF NOT EXISTS vehicle_neon3 varchar(255) DEFAULT NULL; ALTER TABLE vrp_user_vehicles ADD IF NOT EXISTS vehicle_bulletproof varchar(255) DEFAULT NULL; ALTER TABLE vrp_user_vehicles ADD IF NOT EXISTS vehicle_smokecolor1 varchar(255) DEFAULT NULL; ALTER TABLE vrp_user_vehicles ADD IF NOT EXISTS vehicle_smokecolor2 varchar(255) DEFAULT NULL; ALTER TABLE vrp_user_vehicles ADD IF NOT EXISTS vehicle_smokecolor3 varchar(255) DEFAULT NULL; ALTER TABLE vrp_user_vehicles ADD IF NOT EXISTS vehicle_modvariation varchar(255) NOT NULL DEFAULT 'off';")

Dude … This is the same error why you created this post in the first place, then I told you your synthax of the Mysql-Async ‘execute()’ function was wrong. You changed it and told me it worked, then you made a new set of queries with the same synthax issue and are asking the same question you were asking in the first place (question I already answered to) …

Okay. But now I can tell you that I made it perfectly the same as you said I should do in the first answer you came up with and it still does not work.