MySQLOO help. Crashing SRCDS

[NintendoEKS]

Why does this query crash my SRCDS.exe? I can not get a simple retrieval of data (and if not existing, inserting of data) to work...
I've tried using everyones different methods but to no success.

[lua]
require("mysqloo")

db = mysqloo.connect("", "", "", "")

function db:onConnected()
print("---------------------------------")
print("Connected to database!")
print("---------------------------------")
end

function db:onConnectionFailed(err)
print("---------------------------------")
print("Connection failed!")
print("Error:", err)
print("---------------------------------")
end

function checkQuery(query)
local playerInfo = query:getData()
if playerInfo[1] ~= nil then
return true
else
return false
end
end

function newPlayerData(ply)
local query1 = db:query("SELECT money FROM Users WHERE steamID='"..ply:SteamID().."';")

query1.onSuccess = function()
print("Player has money!");
end

query1.onError = function(err)
print("Money retrieval failed: ", err)
end

query1:start()
end
hook.Add("PlayerInitialSpawn", "PlayerInitialSpawn", newPlayerData)
[/lua]

Now it's saying 'attempt to index local query1 <a nil value>.

[Drakehawke]

You aren't calling db:connect() anywhere?

[helifreak]

[QUOTE=Drakehawke;39276529]You aren't calling db:connect() anywhere?[/QUOTE]

Line 4?

[Drakehawke]

mysqloo.connect just initializes the object, to actually connect to the database you need to call db:connect()

[NintendoEKS]

Rookie mistake sorry, added the db:connect() now still facing the original problems I had earlier.

[lua]require("mysqloo")

db = mysqloo.connect("", "", "", "")

function db:onConnected()
print("---------------------------------")
print("Connected to database!")
print("---------------------------------")
end

function db:onConnectionFailed(err)
print("---------------------------------")
print("Connection failed!")
print("Error:", err)
print("---------------------------------")
end

function checkQuery(query)
local playerInfo = query:getData()
if playerInfo[1] ~= nil then
return true
else
return false
end
end

function newPlayerData(ply)
local query1 = db:query("SELECT money FROM Users WHERE steamID='"..ply:SteamID().."';")

query1.onSuccess = function(q)
if checkQuery(q) then
print("Loaded Player!")

local data = query1:getData()
local row = data[1]

ply:SetNWInt("Money", tonumber(row['money']))
else
local query2 = db:query("INSERT INTO Users(steamID, username, money) VALUES ('" .. ply:SteamID() .. "', " .. ply:Name() .. "', " .. 500 .. ")")
query2.onSuccess = function(q) print("Created user!") ply:AddMoney(500) end
query2.onError = function(q,e) print("Problem creating user!") end
query2:start()
end
end

query1.onError = function(err)
print("Money retrieval failed: ", err)
end

query1:start()
end
hook.Add("PlayerInitialSpawn", "PlayerInitialSpawn", newPlayerData)

db:connect()[/lua]

My database information:
Table: Users
Contains: id, steamID, username, money
id = int(11) Primary, Auto increments
steamID = varchar(55)
username = varchar(55)
money = int(55)

[Drakehawke]

Is the database connecting properly? Does your print in onConnected show up?

[NintendoEKS]

It connects properly, before when I manually added the user to the database, then joined the server it successfully set my money accordingly.
How ever I can't get it to INSERT the user into database if he doesn't exist, just problem after a problem.

[zzaacckk]

[QUOTE=NintendoEKS;39284524]It connects properly, before when I manually added the user to the database, then joined the server it successfully set my money accordingly.
How ever I can't get it to INSERT the user into database if he doesn't exist, just problem after a problem.[/QUOTE]

What do you mean you can't insert into the database if he dosent exist?

Try printing the query, and seeing if the query is in correct format.

How many players are on the server when the issue occurs?

[NintendoEKS]

I mean like, say the table is empty, 0 rows 0 everything.
From my query above, what I want to happen is it'll add the user to the database accordingly.

Print the query?

Also it's just myself as I'm testing locally.

[zzaacckk]

Run query:wait() right after you run query:start() [B]only of no players are on the server[/B]

If no players are on the server, the main thread goes into a sleep mode to free up system resources. Running QueryObject:wait() makes it so everything is ran in the main thread, so that queries will go through regardless of if the main thread is sleeping.

I am assuming that the mysql query isn't causing errors/you are doing everything else correctly. If this dosen't work then print the query being executed and any potential query errors and we can look at it from there and try to understand what is going on.

Alot of people add a bot to their server whenever noone is on, and kick it once someone connects. This is an alternative method you can use, but this method discouraged.

[Ruzza]

I've been seeing that mysqloo is causing memleaks, have you tried tmysql?

[zzaacckk]

[QUOTE=Ruzza;39285170]I've been seeing that mysqloo is causing memleaks, have you tried tmysql?[/QUOTE]

I don't think the issue(s) he is experiencing is directly caused from a memory leak associated with mysqloo.

[donovan]

Line 39. You're missing the opening single quote for ply:Name().

[Ruzza]

[QUOTE=donovan;39285324]Line 39. You're missing the opening single quote for ply:Name().[/QUOTE]

He's right, you're missing a single quote.

[NintendoEKS]

I'm having 'some success', when I manually add myself to the database, I connect and it sets my money accordingly to the databases amount.
It's just that 'else create user' part that isn't not so successful.

EDIT: I've had it create myself successfully, how ever when I reconnect it throws; "Money retrieval failed: [Query:1B96CA00]"

(Also added that single quotation, thank you.)

EDIT2: Added a ; to the end of the following line;

[lua]local query1 = db:query("SELECT money FROM Users WHERE steamID='"..ply:SteamID().."';");[/lua]

It seems to throw errors randomly... I'll reconnect 'LOADED PLAYER!: $amounthere", reconnect again "Money retrieval failed".
What's with the inconsistency.

[zzaacckk]

Print the query that is erroring, and the error message returned from the mysql server.

[NintendoEKS]

At quite a risk of sounding ridiculously stupid, how do I print a query?

[Pandaman09]

print(query1) lol :3

[NintendoEKS]

print(query1) returns;

[Query:1B96D100]

[Pandaman09]

yeah that's not right... try just printing the string without db: query ()
edit: also I've never seen mySQL crash srcds even when i made a simple derp. out only caused lag

[MIKEYMASTALSKi]

[lua]query.onError = function(q, err, sql)
print(err)
end[/lua]

[NintendoEKS]

Without db:query it's just a string and nothing more, thus it wouldn't relate to any of my problems?

EDIT: Okay, I join the server for the first time it throws the following;

"Money retrieval failed: MySQL server has gone away

I rejoin, it throws;

Connection failed!

I rejoin again;

Loaded Player!

[Pandaman09]

it would show us how the query was formatted, to see if mySQL would accept it

[editline]20th January 2013[/editline]

if you're on local host aka listen server or a multiplayer sever with no one else in it, mySQL will not work properly because the think hook its not called until a player is in the sever, so if its multiplayer spawn a boy then join.

[editline]20th January 2013[/editline]

you might also want to check to see if the database is connected every few minutes

[NintendoEKS]

What I've done is run a check underneath where I call 'local query1 = db:query()' like so;

[lua]
if stat ~= mysqloo.DATABSE_CONNECTED then
if stat == mysqloo.DATABASE_NOT_CONNECTED then
db:connect();
query1:start();
end
end
[/lua]

This seems to have fixed all issues and creates myself successfully, loads my player data so on so forth.

I'm sure I'll be coming back to this thread with problems heh.
Appreciate the help given so far.

[TheDivinity]

[QUOTE=NintendoEKS;39287547]What I've done is run a check underneath where I call 'local query1 = db:query()' like so;

[lua]
if stat ~= mysqloo.DATABSE_CONNECTED then
if stat == mysqloo.DATABASE_NOT_CONNECTED then
db:connect();
query1:start();
end
end
[/lua]

This seems to have fixed all issues and creates myself successfully, loads my player data so on so forth.

I'm sure I'll be coming back to this thread with problems heh.
Appreciate the help given so far.[/QUOTE]

From what I heard, calling Status with mysqloo is bad performance wise. Another solution, make a timer that runs every so often, so that the connection isn't lost.

timer.Create("DB.StayAlive", 30, 0, function()
	local query = db:query("Show variables like 'KEEPALIVE'")
	query:start()
end)

Works fine for me.

[MIKEYMASTALSKi]

[QUOTE=TheDivinity;39287796]From what I heard, calling Status with mysqloo is bad performance wise. Another solution, make a timer that runs every so often, so that the connection isn't lost.

timer.Create("DB.StayAlive", 30, 0, function()
	local query = db:query("Show variables like 'KEEPALIVE'")
	query:start()
end)

Works fine for me.[/QUOTE]Or he can just do it in the onError function of his query... and restart the query after he reconnects..

[TheDivinity]

[QUOTE=Lerpaderp;39287803]Or he can just do it in the onError function of his query... and restart the query after he reconnects..[/QUOTE]

If he's only having queries once or twice every minute, and it constantly loses connections, wouldn't it be better to just have it on the timer, so the connection is never dropped in the first place? I realize the performance difference between a query and the connection is miniscule, but was just going for the least performance hit. Also, your way of fixing his issue seems a little repetitive, since you'd have to start the query again.

[Ruzza]

[QUOTE=TheDivinity;39288010]If he's only having queries once or twice every minute, and it constantly loses connections, wouldn't it be better to just have it on the timer, so the connection is never dropped in the first place? I realize the performance difference between a query and the connection is miniscule, but was just going for the least performance hit. Also, your way of fixing his issue seems a little repetitive, since you'd have to start the query again.[/QUOTE]

This is how it is meant to be done, I had a timer run every 30 seconds to run queries "SHOW STATUS LIKE 'uptime';" and it always kept the mysql connection open.

[zzaacckk]

[QUOTE=TheDivinity;39287796]From what I heard, calling Status with mysqloo is bad performance wise. Another solution, make a timer that runs every so often, so that the connection isn't lost.

timer.Create("DB.StayAlive", 30, 0, function()
	local query = db:query("Show variables like 'KEEPALIVE'")
	query:start()
end)

Works fine for me.[/QUOTE]

If your not on a shared mysql server, you can set the wait_timeout and stop it from doing connections.

Here is a mysqloo interface I built awhile ago that may help you out. It has worked fine for my in its application, but you won't be able to directly port it without some work.

[lua]

local _Module = HEX_LINK._Modules.Base.create( "mysql" );

_Module:setValue { "Author", "" };
_Module:setValue { "Desc", "Allows connections to a database, required by alot of modules." };
_Module:setValue { "Website", "" };
_Module:setValue { "Initialized", false };
_Module:setValue { "Connecting", false };
_Module:setValue { "QueryStack", {} };

function _Module:PreLoad()
require "mysqloo";
end

function _Module:Connect( ... )
if( self:getValue "Initialized" == false )then
self:setValue { "DB", mysqloo.connect( unpack( HEX_LINK[ "_InternalData" ][ "MysqlConfig" ] ) ) };
self:setValue { "Initialized", true };
end

if( self:getValue "Connecting" == true )then
return;
end

local __ = self:getValue "DB";

__.onConnectionFailed = function( _DB, _Err )
print( "\n***** [CRITICAL] *****\n\tHex Link is unable to connect to the database!\t".._Err.."\n***** [CRITICAL] *****\n" );
end;

__.onConnected = function()
self:setValue { "Connecting", false };

local QueryStack = table.Copy( self:getValue "queryStack" );
self:setValue { "queryStack", {} };

for QID, QueryTable in pairs( QueryStack ) do
local Query = ( self:getValue "DB" ):query( QueryTable[1] );

Query.onError = function ( ... )
local e = { ... };
print( "---- [ Hex Query Error ] -----" );
PrintTable( e );
end

Query.onSuccess = function( _Data )
if( QueryTable[2] == nil )then return; end
return QueryTable[2]( _Data:getData() );
end

Query:start();
if( QueryTable[3] == true && ( 1 > #player.GetAll() ) )then Query:wait(); end
end
end;

if( __:status() != 0 && __:status() != 1 )then
self:setValue { "Connecting", true };
__:connect();
else
__.onConnected();
end
end

function _Module:Escape( str )
return ( self:getValue "DB" ):escape( str );
end

function _Module:Query( ... )
local args = { ... };
local Query, Callback, Sync = args[1], args[2], args[3];

if( Query == nil )then return; end

if( Sync != true )then Sync = false end

local QueryStack = self:getValue "queryStack";
QueryStack[ #QueryStack + 1 ] = { args[1], args[2], Sync };

self:Connect();
end

function _Module:GetLastID(callback, sync)
if( sync != true )then sync = false; end

self:Query( "SELECT LAST_INSERT_ID();", function( data )
return callback( data[1]["LAST_INSERT_ID()"] );
end, sync );
end
[/lua]

[Drakehawke]

Wrong way to do it, you're calling db:status() every time you run a query, this causes the main thread to wait until all running queries have finished before it pings the database.

In short, when a lot of queries are running it's basically making it not threaded at all (i.e. lag)

Also what the fuck

[lua]function _Module:Query( ... )
local args = { ... };
local Query, Callback, Sync = args[1], args[2], args[3];[/lua]

[lua]function _Module:Query( Query, Callback, Sync )[/lua]

?