Prepared queries fail and cause crashes

[Ethorbit]

I am using a 32bit SRCDS server that is up to date and when using the code below, it prints "It has worked!" about 2 or 3 times and then the server crashes, weirdly enough I find that most times when it printed "It has worked" it never actually saved anything to the database:

local preparedQuery = db:prepare("INSERT INTO chat_logs (`sender_id`, `msg`, `date`) VALUES(?, ?, ?)")

function preparedQuery:onSuccess(data)
    print("It has worked!")
end

function preparedQuery:onError(err)
    print("IT FAILED! " .. err)
end

local function PlayerChat(ply, msg, team)
    preparedQuery:setString(1, ply:SteamID())
    preparedQuery:setString(2, msg)
    preparedQuery:setNull(3)
    preparedQuery:start()
end
hook.Add("PlayerSay", "GMLogSaverPlySaidSomething", PlayerChat);

My apologies if the reason it is failing is because I am not using it correctly, I tried to follow the example as closely as possible.

[viral32111]

I can't see any errors with the code itself (aside from semicolon usage and backticks in strings, but that's all up to preference).

Since you haven't included it in the snippet, are you sure you've initalised your database (db) object properly in earlier code & given it time to fully connect to the MySQL server? (Example code below)

require( "mysqloo" )

local db = mysqloo.connect( "1.2.3.4", "username", "password", "database", 3306 ) -- Replace these with details

function db:onConnected()
    print( "Connected to the MySQL server!" )
end

function db:onConnectionFailed( errorMessage )
    print( "Failed to connect to the MySQL server: " .. errorMessage )
end

db:connect()

If it isn't an issue with initalisation, it might be an issue with your database table structure (trying to insert malformed or incorrect data types), although that usually would throw an error and call query's onError() method.

[Ethorbit]

I can't see any errors with the code itself (aside from semicolon usage and backticks in strings, but that's all up to preference).

Since you haven't included it in the snippet, are you sure you've initalised your database (db) object properly in earlier code & given it time to fully connect to the MySQL server? (Example code below)

When using db:query instead the same code works fine, and it is saved to the database as intended, the prepared statement fails with or without the semicolon I mistakingly included in the issue post or the backticks which I copied from one of the examples to see if what I did previously was wrong.

Here is the beginning of my script:

require("mysqloo");

local dbHost = "localhost"
local dbUser = "root"
local dbPass = ""
local dbName = "nzombies"
local dbPort = 3306
local db = mysqloo.connect(dbHost, dbUser, dbPass, dbName, dbPort)
local dbIsConnected = false
local dbError = ""

function db:onConnected()
    dbIsConnected = true
end

db:connect()

And here is the structure of the table:

• log_id INT AUTO_INCREMENT PRIMARY KEY
• sender_id VARCHAR(18)
• msg VARCHAR(251)
• date VARCHAR(11)

Also I think it's important to note that sometimes instead of lagging or crashing it just hangs indefinitely

[FoksVHox]

Well, I experienced it before, but never submitted an issue. But are you sure that date can be null?

[Ethorbit]

Well, I experienced it before, but never submitted an issue. But are you sure that date can be null?

It was meant to be DateTime and I only set null as a test/example, I used

local function GetDateTime()
    return os.date("%Y-%m-%d %H:%M:%S")
end

for actually setting the date

[viral32111]

I've tried to reproduce this using code similar to yours and a similar database structure, but with my setup I can't seem to get it to fail or crash.

Using this script, the values were successfully inserted into the database:

require("mysqloo")

local database = mysqloo.connect( "ip", "username", "password", "database", 3306 ) -- Obviously I changed these
local query = database:prepare("INSERT INTO Ethorbit (sender_id, msg, date) VALUES(?, ?, ?);")

function query:onSuccess( data )
    print( tostring( data ) )
    PrintTable( data )
end

function query:onError( errorMessage )
    ErrorNoHalt( errorMessage )
end

function database:onConnected()
    query:setString( 1, "STEAM_0:1:104283773" )
    query:setString( 2, "Testing 123" )
    query:setNull( 3 )
    query:start()
end

function database:onConnectionFailed( errorMessage )
    ErrorNoHalt( errorMessage )
end

database:connect()

As seen in phpMyAdmin:

Console output:

Table structure:

• log_id INT(11) AUTO_INCREMENT PRIMARY KEY
• sender_id VARCHAR(20) utf8mb4_general_ci
• msg VARCHAR(251) utf8mb4_general_ci
• date VARCHAR(11) utf8mb4_general_ci NULL

I did have to change the table structure you provided because the date field didn't have the NULL option set, and the sender_id wasn't long enough. SteamIDs aren't specifically 18, some are only 6 characters long while others can be 19, such as my one used in the example.

Setup:

• MySQL Server: 10.0.28-MariaDB-2+b1
• Module: MySQLOO 9.6.1
• SRCDS: 32-bit, public (default) branch.
• OS: Windows 10 19041.208

(Sorry for the late response, I've been having to deal with a few things IRL due to COVID, hope you can understand)

Also I think it's important to note that sometimes instead of lagging or crashing it just hangs indefinitely

I've had a similar issue to this before, but not related to executing queries (although I see no reason why it wouldn't apply). If you have a firewall on the server that's denying SQL traffic, instead of getting a response back and the fail/error callback being ran, it just hangs indefinitely doing nothing. My guess is it's silently retrying over and over.

[Ethorbit]

I've tried to reproduce this using code similar to yours and a similar database structure, but with my setup I can't seem to get it to fail or crash.

Thanks for taking the time to go over everything and see if you run into the same issues, due to our setups being identical I believe it's just issues on my side, maybe an addon on the server or maybe just the fact that sender_id had the incorrect length.

It's enough to show that this isn't actually a problem with mysqloo so I will close the issue now.