SQL syntax Error

[MDOB93]

hello i just set up a new server with a mariaDB as DB server i get the following error when i first start the server:

You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'CREATE TABLE IF NOT EXISTS vrp_user_ids(

I have looked in the vRP/vrp/base.lua but found no particular discrepancies, well I don't know much about DB either. I use a Debian 11 and have already considered switching to a mysql or postgresql.
would this be a solution or is it just a syntax error?

[Sharky521]

Do you still have issues with this?

[ahmed608]

-- --------------------------------------------------------
-- Host:                         127.0.0.1
-- Server version:               10.4.21-MariaDB - mariadb.org binary distribution
-- Server OS:                    Win64
-- HeidiSQL Version:             12.1.0.6537
-- --------------------------------------------------------

/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET NAMES utf8 */;
/*!50503 SET NAMES utf8mb4 */;
/*!40103 SET @OLD_TIME_ZONE=@@TIME_ZONE */;
/*!40103 SET TIME_ZONE='+00:00' */;
/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */;
/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */;
/*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */;


-- Dumping database structure for vrp
CREATE DATABASE IF NOT EXISTS `vrp` /*!40100 DEFAULT CHARACTER SET utf8mb4 */;
USE `vrp`;

-- Dumping structure for table vrp.vrp_characters
CREATE TABLE IF NOT EXISTS `vrp_characters` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `user_id` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `fk_characters_users` (`user_id`),
  CONSTRAINT `fk_characters_users` FOREIGN KEY (`user_id`) REFERENCES `vrp_users` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Dumping data for table vrp.vrp_characters: ~0 rows (approximately)
DELETE FROM `vrp_characters`;

-- Dumping structure for table vrp.vrp_character_business
CREATE TABLE IF NOT EXISTS `vrp_character_business` (
  `character_id` int(11) NOT NULL,
  `name` varchar(30) DEFAULT NULL,
  `description` text DEFAULT NULL,
  `capital` int(11) DEFAULT NULL,
  `laundered` int(11) DEFAULT NULL,
  `reset_timestamp` int(11) DEFAULT NULL,
  PRIMARY KEY (`character_id`),
  CONSTRAINT `fk_character_business_characters` FOREIGN KEY (`character_id`) REFERENCES `vrp_characters` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Dumping data for table vrp.vrp_character_business: ~0 rows (approximately)
DELETE FROM `vrp_character_business`;

-- Dumping structure for table vrp.vrp_character_data
CREATE TABLE IF NOT EXISTS `vrp_character_data` (
  `character_id` int(11) NOT NULL,
  `dkey` varchar(100) NOT NULL,
  `dvalue` blob DEFAULT NULL,
  PRIMARY KEY (`character_id`,`dkey`),
  CONSTRAINT `fk_character_data_characters` FOREIGN KEY (`character_id`) REFERENCES `vrp_characters` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Dumping data for table vrp.vrp_character_data: ~0 rows (approximately)
DELETE FROM `vrp_character_data`;

-- Dumping structure for table vrp.vrp_character_homes
CREATE TABLE IF NOT EXISTS `vrp_character_homes` (
  `character_id` int(11) NOT NULL,
  `home` varchar(100) DEFAULT NULL,
  `number` int(11) DEFAULT NULL,
  PRIMARY KEY (`character_id`),
  UNIQUE KEY `home` (`home`,`number`),
  CONSTRAINT `fk_character_homes_characters` FOREIGN KEY (`character_id`) REFERENCES `vrp_characters` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Dumping data for table vrp.vrp_character_homes: ~0 rows (approximately)
DELETE FROM `vrp_character_homes`;

-- Dumping structure for table vrp.vrp_character_identities
CREATE TABLE IF NOT EXISTS `vrp_character_identities` (
  `character_id` int(11) NOT NULL,
  `registration` varchar(20) DEFAULT NULL,
  `phone` varchar(20) DEFAULT NULL,
  `firstname` varchar(50) DEFAULT NULL,
  `name` varchar(50) DEFAULT NULL,
  `age` int(11) DEFAULT NULL,
  PRIMARY KEY (`character_id`),
  KEY `registration` (`registration`),
  KEY `phone` (`phone`),
  CONSTRAINT `fk_character_identities_characters` FOREIGN KEY (`character_id`) REFERENCES `vrp_characters` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Dumping data for table vrp.vrp_character_identities: ~0 rows (approximately)
DELETE FROM `vrp_character_identities`;

-- Dumping structure for table vrp.vrp_global_data
CREATE TABLE IF NOT EXISTS `vrp_global_data` (
  `dkey` varchar(100) NOT NULL,
  `dvalue` blob DEFAULT NULL,
  PRIMARY KEY (`dkey`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Dumping data for table vrp.vrp_global_data: ~0 rows (approximately)
DELETE FROM `vrp_global_data`;

-- Dumping structure for table vrp.vrp_login_users
CREATE TABLE IF NOT EXISTS `vrp_login_users` (
  `user_id` int(11) NOT NULL,
  `whitelisted` tinyint(1) DEFAULT NULL,
  `ban_end` int(11) DEFAULT NULL,
  `ban_reason` text DEFAULT NULL,
  PRIMARY KEY (`user_id`),
  CONSTRAINT `fk_login_users_vrp` FOREIGN KEY (`user_id`) REFERENCES `vrp_users` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Dumping data for table vrp.vrp_login_users: ~0 rows (approximately)
DELETE FROM `vrp_login_users`;

-- Dumping structure for table vrp.vrp_server_data
CREATE TABLE IF NOT EXISTS `vrp_server_data` (
  `id` varchar(100) NOT NULL,
  `dkey` varchar(100) NOT NULL,
  `dvalue` blob DEFAULT NULL,
  PRIMARY KEY (`id`,`dkey`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Dumping data for table vrp.vrp_server_data: ~1 rows (approximately)
DELETE FROM `vrp_server_data`;
INSERT INTO `vrp_server_data` (`id`, `dkey`, `dvalue`) VALUES
	('main', 'vRP:hidden_transformers', _binary 0x83aa57656564206669656c6482a974696d657374616d70ce65be6a1ba8706f736974696f6e93cbc08f9e7d6000000bcb4090670f1ffffff5cb40649de01ffffffeaf576565642070726f63657373696e6782a974696d657374616d70ce65be6a1ba8706f736974696f6e93cb40a0d5b400000000cb40aa74cf80000000cb4046c8febffffffaab5765656420726573616c6582a974696d657374616d70ce65be6a1ba8706f736974696f6e93cbc0824988e0000002cbc0991b5880000000cb403b02c4bffffff3);

-- Dumping structure for table vrp.vrp_users
CREATE TABLE IF NOT EXISTS `vrp_users` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `username` varchar(100) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Dumping data for table vrp.vrp_users: ~0 rows (approximately)
DELETE FROM `vrp_users`;

-- Dumping structure for table vrp.vrp_user_data
CREATE TABLE IF NOT EXISTS `vrp_user_data` (
  `user_id` int(11) NOT NULL,
  `dkey` varchar(100) NOT NULL,
  `dvalue` blob DEFAULT NULL,
  PRIMARY KEY (`user_id`,`dkey`),
  CONSTRAINT `fk_user_data_users` FOREIGN KEY (`user_id`) REFERENCES `vrp_users` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Dumping data for table vrp.vrp_user_data: ~0 rows (approximately)
DELETE FROM `vrp_user_data`;

-- Dumping structure for table vrp.vrp_user_ids
CREATE TABLE IF NOT EXISTS `vrp_user_ids` (
  `identifier` varchar(100) NOT NULL,
  `user_id` int(11) NOT NULL,
  PRIMARY KEY (`identifier`,`user_id`),
  KEY `fk_user_ids_users` (`user_id`),
  CONSTRAINT `fk_user_ids_users` FOREIGN KEY (`user_id`) REFERENCES `vrp_users` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Dumping data for table vrp.vrp_user_ids: ~0 rows (approximately)
DELETE FROM `vrp_user_ids`;

/*!40103 SET TIME_ZONE=IFNULL(@OLD_TIME_ZONE, 'system') */;
/*!40101 SET SQL_MODE=IFNULL(@OLD_SQL_MODE, '') */;
/*!40014 SET FOREIGN_KEY_CHECKS=IFNULL(@OLD_FOREIGN_KEY_CHECKS, 1) */;
/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;
/*!40111 SET SQL_NOTES=IFNULL(@OLD_SQL_NOTES, 1) */;

[ahmed608]

INSERT INTO vrp_characters(user_id) VALUES(@user_id);
SELECT LAST_INSERT_ID() AS id;

DELETE FROM vrp_characters WHERE id = @id AND user_id = @user_id;

SELECT id FROM vrp_characters WHERE user_id = @user_id;
SELECT id FROM vrp_characters WHERE id = @id AND user_id = @user_id;

INSERT INTO vrp_user_ids(identifier,user_id) VALUES(@identifier,@user_id);
SELECT user_id FROM vrp_user_ids WHERE identifier = @identifier;

REPLACE INTO vrp_user_data(user_id,dkey,dvalue) VALUES(@user_id,@key,UNHEX(@VALUE));
SELECT dvalue FROM vrp_user_data WHERE user_id = @user_id AND dkey = @KEY;

REPLACE INTO vrp_character_data(character_id,dkey,dvalue) VALUES(@character_id,@key,UNHEX(@VALUE));
SELECT dvalue FROM vrp_character_data WHERE character_id = @character_id AND dkey = @KEY;

REPLACE INTO vrp_server_data(id,dkey,dvalue) VALUES(@id,@key,UNHEX(@VALUE));
SELECT dvalue FROM vrp_server_data WHERE id = @id AND dkey = @KEY;

REPLACE INTO vrp_global_data(dkey,dvalue) VALUES(@key,UNHEX(@VALUE));
SELECT dvalue FROM vrp_global_data WHERE dkey = @key;

[Noah-Wilderom]

Do you still have issues with this?

I still have issues with a fresh installation. I'am using MySql v8.0.36

When starting the server:

[      script:oxmysql] Error: vrp was unable to execute a query!
[      script:oxmysql] Query: CREATE TABLE IF NOT EXISTS vrp_users(
[      script:oxmysql]   id INTEGER AUTO_INCREMENT,
[      script:oxmysql]   CONSTRAINT pk_user PRIMARY KEY(id)
[      script:oxmysql] );
[      script:oxmysql] 
[      script:oxmysql] CREATE TABLE IF NOT EXISTS vrp_user_ids(
[      script:oxmysql]   identifier VARCHAR(100),
[      script:oxmysql]   user_id INTEGER,
[      script:oxmysql]   CONSTRAINT pk_user_ids PRIMARY KEY(identifier),
[      script:oxmysql]   CONSTRAINT fk_user_ids_users FOREIGN KEY(user_id) REFERENCES vrp_users(id) ON DELETE CASCADE
[      script:oxmysql] );
[      script:oxmysql] 
[      script:oxmysql] CREATE TABLE IF NOT EXISTS vrp_characters(
[      script:oxmysql]   id INTEGER AUTO_INCREMENT,
[      script:oxmysql]   user_id INTEGER,
[      script:oxmysql]   CONSTRAINT pk_characters PRIMARY KEY(id),
[      script:oxmysql]   CONSTRAINT fk_characters_users FOREIGN KEY(user_id) REFERENCES vrp_users(id) ON DELETE CASCADE
[      script:oxmysql] );
[      script:oxmysql] 
[      script:oxmysql] CREATE TABLE IF NOT EXISTS vrp_user_data(
[      script:oxmysql]   user_id INTEGER,
[      script:oxmysql]   dkey VARCHAR(100),
[      script:oxmysql]   dvalue BLOB,
[      script:oxmysql]   CONSTRAINT pk_user_data PRIMARY KEY(user_id,dkey),
[      script:oxmysql]   CONSTRAINT fk_user_data_users FOREIGN KEY(user_id) REFERENCES vrp_users(id) ON DELETE CASCADE
[      script:oxmysql] );
[      script:oxmysql] 
[      script:oxmysql] CREATE TABLE IF NOT EXISTS vrp_character_data(
[      script:oxmysql]   character_id INTEGER,
[      script:oxmysql]   dkey VARCHAR(100),
[      script:oxmysql]   dvalue BLOB,
[      script:oxmysql]   CONSTRAINT pk_character_data PRIMARY KEY(character_id,dkey),
[      script:oxmysql]   CONSTRAINT fk_character_data_characters FOREIGN KEY(character_id) REFERENCES vrp_characters(id) ON DELETE CASCADE
[      script:oxmysql] );
[      script:oxmysql] 
[      script:oxmysql] CREATE TABLE IF NOT EXISTS vrp_server_data(
[      script:oxmysql]   id VARCHAR(100),
[      script:oxmysql]   dkey VARCHAR(100),
[      script:oxmysql]   dvalue BLOB,
[      script:oxmysql]   CONSTRAINT pk_server_data PRIMARY KEY(id, dkey)
[      script:oxmysql] );
[      script:oxmysql] 
[      script:oxmysql] CREATE TABLE IF NOT EXISTS vrp_global_data(
[      script:oxmysql]   dkey VARCHAR(100),
[      script:oxmysql]   dvalue BLOB,
[      script:oxmysql]   CONSTRAINT pk_global_data PRIMARY KEY(dkey)
[      script:oxmysql] );
[      script:oxmysql] 
[      script:oxmysql] []
[      script:oxmysql] You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'CREATE TABLE IF NOT EXISTS vrp_user_ids(
[      script:oxmysql]   identifier VARCHAR(100),
[      script:oxmysql]   user_id IN' at line 6

When i'am joining the server:

[      script:oxmysql] Error: vrp was unable to execute a query!
[      script:oxmysql] Query: INSERT INTO vrp_users(id) VALUES(DEFAULT); SELECT LAST_INSERT_ID() AS id
[      script:oxmysql] []
[      script:oxmysql] You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'SELECT LAST_INSERT_ID() AS id' at line 1

[Sharky521]

Do you still have issues with this?

I still have issues with a fresh installation. I'am using MySql v8.0.36

When starting the server:

[      script:oxmysql] Error: vrp was unable to execute a query!
[      script:oxmysql] Query: CREATE TABLE IF NOT EXISTS vrp_users(
[      script:oxmysql]   id INTEGER AUTO_INCREMENT,
[      script:oxmysql]   CONSTRAINT pk_user PRIMARY KEY(id)
[      script:oxmysql] );
[      script:oxmysql] 
[      script:oxmysql] CREATE TABLE IF NOT EXISTS vrp_user_ids(
[      script:oxmysql]   identifier VARCHAR(100),
[      script:oxmysql]   user_id INTEGER,
[      script:oxmysql]   CONSTRAINT pk_user_ids PRIMARY KEY(identifier),
[      script:oxmysql]   CONSTRAINT fk_user_ids_users FOREIGN KEY(user_id) REFERENCES vrp_users(id) ON DELETE CASCADE
[      script:oxmysql] );
[      script:oxmysql] 
[      script:oxmysql] CREATE TABLE IF NOT EXISTS vrp_characters(
[      script:oxmysql]   id INTEGER AUTO_INCREMENT,
[      script:oxmysql]   user_id INTEGER,
[      script:oxmysql]   CONSTRAINT pk_characters PRIMARY KEY(id),
[      script:oxmysql]   CONSTRAINT fk_characters_users FOREIGN KEY(user_id) REFERENCES vrp_users(id) ON DELETE CASCADE
[      script:oxmysql] );
[      script:oxmysql] 
[      script:oxmysql] CREATE TABLE IF NOT EXISTS vrp_user_data(
[      script:oxmysql]   user_id INTEGER,
[      script:oxmysql]   dkey VARCHAR(100),
[      script:oxmysql]   dvalue BLOB,
[      script:oxmysql]   CONSTRAINT pk_user_data PRIMARY KEY(user_id,dkey),
[      script:oxmysql]   CONSTRAINT fk_user_data_users FOREIGN KEY(user_id) REFERENCES vrp_users(id) ON DELETE CASCADE
[      script:oxmysql] );
[      script:oxmysql] 
[      script:oxmysql] CREATE TABLE IF NOT EXISTS vrp_character_data(
[      script:oxmysql]   character_id INTEGER,
[      script:oxmysql]   dkey VARCHAR(100),
[      script:oxmysql]   dvalue BLOB,
[      script:oxmysql]   CONSTRAINT pk_character_data PRIMARY KEY(character_id,dkey),
[      script:oxmysql]   CONSTRAINT fk_character_data_characters FOREIGN KEY(character_id) REFERENCES vrp_characters(id) ON DELETE CASCADE
[      script:oxmysql] );
[      script:oxmysql] 
[      script:oxmysql] CREATE TABLE IF NOT EXISTS vrp_server_data(
[      script:oxmysql]   id VARCHAR(100),
[      script:oxmysql]   dkey VARCHAR(100),
[      script:oxmysql]   dvalue BLOB,
[      script:oxmysql]   CONSTRAINT pk_server_data PRIMARY KEY(id, dkey)
[      script:oxmysql] );
[      script:oxmysql] 
[      script:oxmysql] CREATE TABLE IF NOT EXISTS vrp_global_data(
[      script:oxmysql]   dkey VARCHAR(100),
[      script:oxmysql]   dvalue BLOB,
[      script:oxmysql]   CONSTRAINT pk_global_data PRIMARY KEY(dkey)
[      script:oxmysql] );
[      script:oxmysql] 
[      script:oxmysql] []
[      script:oxmysql] You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'CREATE TABLE IF NOT EXISTS vrp_user_ids(
[      script:oxmysql]   identifier VARCHAR(100),
[      script:oxmysql]   user_id IN' at line 6

When i'am joining the server:

[      script:oxmysql] Error: vrp was unable to execute a query!
[      script:oxmysql] Query: INSERT INTO vrp_users(id) VALUES(DEFAULT); SELECT LAST_INSERT_ID() AS id
[      script:oxmysql] []
[      script:oxmysql] You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'SELECT LAST_INSERT_ID() AS id' at line 1

What db driver are you using?