How to Create TABLE with MySQL Async

HonningBi · February 15, 2022, 8:35pm

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?

thibaultD · February 16, 2022, 12:00pm

Hey
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)

HonningBi · February 16, 2022, 12:40pm

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?

thibaultD · February 16, 2022, 12:45pm

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.

HonningBi · February 16, 2022, 12:48pm

What do you mean with (back to back)?

thibaultD · February 16, 2022, 12:53pm

As I said : “on the same line”

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

Instead of :

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

HonningBi · February 16, 2022, 12:53pm

So all ALTER TABLE needs to be on one line?

HonningBi · February 16, 2022, 12:59pm

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

thibaultD · February 16, 2022, 1:10pm

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) …

HonningBi · February 16, 2022, 1:17pm

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.