gmsv_mysqloo - Updated OO MySQL Module (multiple statements/stored procs!)

[Kingof🅱east]

I haven't had the time to go through the code and actually track down the issue. Thanks, Agent766, I merged your pull request!

[Arhenox]

I still haven't solved my problem.
Recently, I've changed my VPS, but still when I set the MySQL host different than 'localhost' in any addon's config, server loses connection with DB. Tried several MySQL servers, always the same thing.
Example PointShop error:

PointShop MySQL: Connected!
PointShop MySQL: Query Failed: Lost connection to MySQL server during query (    
SELECT *
FROM `pointshop_data`
WHERE uniqueid = '1047415149'
)

Example AwesomeStats error:

ServerLog: [Thu Aug 14 12:26:51 2014][Database stuff] [Awesome Stats]Something we
nt wrong
[Thu Aug 14 12:26:51 2014][Database stuff] [Awesome Stats]Something went wrong
[Thu Aug 14 12:26:51 2014][Database stuff] MySQL server has gone away[Thu Aug 14 
12:26:51 2014][Database stuff] MySQL server has gone away

It is definitely something with mysqloo, because my TF server uses SourceMod and I don't have any problems with it.

[Kingof🅱east]

Can you do other queries? Try a dummy query like

SHOW VARIABLES

to see if it loses connection there as well.

[adamski0811]

I am still getting issues with this. I have them installed int he correct location. The server works with mysqlite. I am using DarkRP so I get this following error

[ERROR] gamemodes/darkrp/gamemode/modules/base/sv_data.lua:195: attempt to index global 'MySQLite' (a nil value)
1. retrievePlayerData - gamemodes/darkrp/gamemode/modules/base/sv_data.lua:195
2. restorePlayerData - gamemodes/darkrp/gamemode/modules/base/sv_data.lua:263
3. unknown - gamemodes/darkrp/gamemode/modules/base/sv_gamemode_functions.lua:548

I am using Ubuntu 12.04 (Precise Pangolin). I have 23 bit libs installed aswell. Still no joy getting it to work.

FIXED - I am using Tcadmin as I am fairly lazy. Makes it easier with my community and new servers etc. I jsut had to change the file permissions to tcgame to have it working. I should know about this!

Thanks whoever read anyway

[Arhenox]

[QUOTE=KingofBeast;45690889]Can you do other queries? Try a dummy query like

SHOW VARIABLES

to see if it loses connection there as well.[/QUOTE]
I've moved my WWW and MySQL server to my VPS again, so problem is "solved".
I think it wouldn't work, because ALL of the addons were losing connection (and they use different queries, some of them were connecting to other DB).

[Bizzik]

RROR] addons/gmdonate/lua/gmdonate/kernel/action.lua:24: attempt to index field 'DB' (a nil value)
1. unknown - addons/gmdonate/lua/gmdonate/kernel/action.lua:24

Timer Failed! [Simple][@addons/gmdonate/lua/gmdonate/kernel/action.lua (line 23)]
What should i do?

I have windows 8.1
and i run my web files and database on x10hosting.com premium. raritygmod.com

I tried new files from new thread and import them but coming this err msg:

[ERROR] addons/gmdonate/lua/gmdonate/kernel/db.lua:16: attempt to index local 'queryObj' (a nil value)
1. QuickQuery - addons/gmdonate/lua/gmdonate/kernel/db.lua:16
2. unknown - addons/gmdonate/lua/gmdonate/kernel/action.lua:24

Timer Failed! [Simple][@addons/gmdonate/lua/gmdonate/kernel/action.lua (line 23)]

[DannyCore]

sorry to bump this, but I have a question and have had enough of trying to debug this myself.

i sometimes, very rarely, but sometimes, receive the MySQL server has gone away error, and there's an issue with whenever that happens. these are being tested with 2 separate mysql addons.

so it seems that whenever the MySQL server has gone away error comes during a query or when the status() timer on the script comes, the server experiences lag for 8-15 seconds, one of those numbers. it hogs the CPU for that small time period, and then when it's done, everything returns to normal and it states that it lost connection etc.

what I don't understand is why it hogs the cpu for 8-15 seconds, and returns to normal? am I or the author of the original script doing something wrong here? take a look at the snippets below.

[I]SourceBans addon piece[/I]

	function SBAN_MYSQL.Connect( first )
		if first then
			print("[SBAN_ULX][MYSQL] Connecting Database")
			sban_db:connect()
			--sban_db:wait()	//Forces the server to wait for the DB connection
		end

		local dbstatus = sban_db:status()
		if dbstatus != mysqloo.DATABASE_CONNECTED && dbstatus != mysqloo.DATABASE_CONNECTING then
			print("[SBAN_ULX][MYSQL] Connection was lost, Trying to reconnect")
			sban_db:connect()
		else
			if first then
				//Creates WatchDog for the database
				if(timer.Exists("sbanulx_database_check")) then timer.Destroy("sbanulx_database_check")end
				timer.Create("sbanulx_database_check", 69,0, function() SBAN_MYSQL.Connect(false) print("[SBAN][MYSQL] Checking database connectivity") end)
			end
		end
	end

[I]PointShop Modified Mysql[/I]

snippet of a query part that would cause hog:

function PROVIDER:SetPoints(ply, points)
	if MYSQL_HALT_DISCONNECT then return end
	
    local qs = [[
    INSERT INTO `pointshop_data` (uniqueid, points, items)
    VALUES ('%s', '%s', '[]')
    ON DUPLICATE KEY UPDATE 
        points = VALUES(points)
    ]]
    qs = string.format(qs, ply:UniqueID(), points or 0)
    local q = db:query(qs)
	
	if not q then return end
     
    function q:onError(err, sql)
        if db:status() ~= mysqloo.DATABASE_CONNECTED then
			MYSQL_HALT_DISCONNECT = true
            print("Pointshop MySQL: No connection to the database!")
            return
        end
        MsgN('PointShop MySQL: Query Failed: ' .. err .. ' (' .. sql .. ')')
        q:start()
    end
     
    q:start()
end

[I]snippet of section where it connects and all[/I]

function db:onConnected()
    MsgN('PointShop MySQL: Connected!')
	MYSQL_HALT_DISCONNECT = false
	
	timer.Simple(1, function()
		for k, v in pairs(player.GetHumans()) do
			if not IsValid(v) or v.PS_FirstLoadCompleted then continue end
			
			v:PS_LoadData()
			v:PS_SendClientsideModels()
			
			v:PS_Notify("Your data has successfully been loaded!")
		end
	end)
end

function db:onConnectionFailed(err)
    MsgN('PointShop MySQL: Connection Failed, please check your settings: ' .. err)
	MYSQL_HALT_DISCONNECT = true
end

timer.Create("provide_mysql_pickmeup", 30, 0, function()
	if db:status() ~= mysqloo.DATABASE_CONNECTED then
		MYSQL_HALT_DISCONNECT = true
		PS:LoadDataProvider()
	else
		MYSQL_HALT_DISCONNECT = false
	end
end)

db:connect()

i don't see what's being done wrong there?

I am testing this by shutting down the VDS hosting the mysql test server, not the mysql test server by itself. Shutting down the VDS = CPU hog, shutting down mysql server = the normal message that should appear, "Can't connect to MySQL server on" i literally don't see what's going on here. can anyone spare some help?

edit: it seems to sometimes just continue hogging cpu and crashing the server instead

[cm22]

[QUOTE=DannyCore;46669789]sorry to bump this, but I have a question and have had enough of trying to debug this myself.
-snip-
[/QUOTE]

I'm having the same issue! My server's been restart multiple times because of this. Hopefully someone knows the issue.

EDIT:

Now actually thinking about the problem, the retry code is what's causing the issue. The simple fix would be to take out the code that retrys the queries and instead make them fail gracefully.

[DannyCore]

[QUOTE=cm22;46801953]I'm having the same issue! My server's been restart multiple times because of this. Hopefully someone knows the issue.

EDIT:

Now actually thinking about the problem, the retry code is what's causing the issue. The simple fix would be to take out the code that retrys the queries and instead make them fail gracefully.[/QUOTE]

yea, i switched everything to tmysql4 and that fixed the crash issues.

[Zalvious]

Please help me, I'm kind of new to this stuff and I'm not really sure how to fix this. I followed the installation instructions. :(

Couldn't include file 'includes/modules/mysqloo.lua' (File not found)

[MIKEYMASTALSKi]

[QUOTE=Zalvious;46852199]Please help me, I'm kind of new to this stuff and I'm not really sure how to fix this. I followed the installation instructions. :(

Couldn't include file 'includes/modules/mysqloo.lua' (File not found)[/QUOTE]

This module is pretty much obsolete now, you should switch to tmysql4 if your addon supports it.

[url]http://facepunch.com/showthread.php?t=1442438[/url]

[FPtje]

[QUOTE=Lerpaderp;46855369]This module is pretty much obsolete now, you should switch to tmysql4 if your addon supports it.

[url]http://facepunch.com/showthread.php?t=1442438[/url][/QUOTE]

It still works and does its thing fine. The guy didn't install the module correctly (you have to put it in lua/bin/). That's no reason to tell him to use tmysql4.
You can make the exact same mistake with tmysql4.

[MIKEYMASTALSKi]

[QUOTE=FPtje;46855562]It still works and does its thing fine. The guy didn't install the module correctly (you have to put it in lua/bin/). That's no reason to tell him to use tmysql4.
You can make the exact same mistake with tmysql4.[/QUOTE]

The module has been linked to numerous crashes, and has a pretty blatantly obvious memory leak...everybody should be moving away from it.

[Teddi]

On a small scale, the module works - where it fails is when you start scaling the operations to include more queries and the like.

Personally it's not something I'd use in production but for getting to grips with SQL and so on, it's fine really.

[killdro]

So im extremely new to gmod and server stuff so what exactly should I do after I have the files in there?

[Drakehawke]

I suspect the memory leak issue is to do with how people are using callback functions. For example:

[lua]
local query = db:query("whatever")
query.onSuccess = function( q, data )

-- insert any code involving the variable "query" here, eg
print( query )

end
[/lua]

You notice inside the callback function we refer to the query object as an upvalue instead of using the function argument.

Could those who have the memory leak bug try using the function argument instead of an upvalue?

e.g. the code above would become
[lua]
local query = db:query("whatever")
query.onSuccess = function( q, data )

print( q )

end
[/lua]

You will need to do this in every callback function you are using including onSuccess and onError.

I've conducted some preliminary tests and found that this seems to help a fair bit. Would appreciate it if people could test/verify this.

[NightlyDev]

Found a weird issue on Linux.

After running a lot of queries, the srcds_linux process have a high CPU usage (even if all queries are terminated.)

For example :

Sandbox - No addons
CPU Usage : 3%

After running 30.000 queries (and waited for them to finish)

CPU Usage : 6%

After running 90.000 queries

CPU Usage : 17%

And the CPU usage will remain at 17%.

The Lua script I used for testing :

require("mysqloo")
print("Starting...")
local db = mysqloo.connect("10.25.0.10", "devel_user", "XXXXXXXXXXXXXXX", "devel_db", 3306, "/var/lib/mysql/mysql.sock")

function db:onConnected()
    print( "Database has connected!" )
    for i=0, 30000 do
        local q = self:query( "SELECT @@VERSION;" )
        q:start()
        q:wait()
    end
    print("Done...")
end

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

db:connect()

I'm running CentOS 7.0

Cannot reproduce in Windows.

Any ideas ? Related to the memory leak issue ?

Thanks.

[nico64160]

I everybody ,

My VPS is running on Ubuntu 14.04 LTS 64Bits and i'm got the libmysqlclient.so.18 on Steam/steamapps/common/GarrysModDS/ HERE
and
gmsv_mysqloo_linux.dll on Steam/steamapps/common/GarrysModDS/garrysmod/lua/bin/ HERE
I think this is the good directory but im got errors like this :

[ULX GB] - Database Connection Restarted
[ULX GB] connectToDatabase
Lost connection to MySQL server at 'reading initial communication packet', system
error: 0
VXDonation lost connection! Reconnecting..
L 04/16/2015 - 13:48:21: Lua Error: VXDonation: MySQL connection failed! Error me
ssage:Lost connection to MySQL server at 'reading initial communication packet',
system error: 0

There are no lua errors so i'm lost :s

[lantos]

[QUOTE=nico64160;47538544]I everybody ,

My VPS is running on Ubuntu 14.04 LTS 64Bits and i'm got the libmysqlclient.so.18 on Steam/steamapps/common/GarrysModDS/ HERE
and
gmsv_mysqloo_linux.dll on Steam/steamapps/common/GarrysModDS/garrysmod/lua/bin/ HERE
I think this is the good directory but im got errors like this :

[ULX GB] - Database Connection Restarted
[ULX GB] connectToDatabase
Lost connection to MySQL server at 'reading initial communication packet', system
error: 0
VXDonation lost connection! Reconnecting..
L 04/16/2015 - 13:48:21: Lua Error: VXDonation: MySQL connection failed! Error me
ssage:Lost connection to MySQL server at 'reading initial communication packet',
system error: 0

There are no lua errors so i'm lost :s[/QUOTE]
running ubuntu 14.04 lts 64 bit
having similar issues. placed
//same place as srcds_run
libmysqlclient.so.18 -> /gmod/
gmsv_mysqloo_linux.dll -> /gmod/lua/bin

edit:
I ran ldd on gmsv_mysqloo_linux.dll and got this

$ldd   gmsv_mysqloo_linux.dll         
        linux-gate.so.1 =>  (0xf7758000)
        libmysqlclient.so.18 => not found
        libstdc++.so.6 => /usr/lib/i386-linux-gnu/libstdc++.so.6 (0xf7646000)
        libgcc_s.so.1 => /lib/i386-linux-gnu/libgcc_s.so.1 (0xf7628000)
        libc.so.6 => /lib/i386-linux-gnu/libc.so.6 (0xf747a000)
        libm.so.6 => /lib/i386-linux-gnu/libm.so.6 (0xf7434000)
        /lib/ld-linux.so.2 (0xf7759000)

it seems that the libmysqlclient.so.18 is not loading from the /gmod/ where the srcds_run is. Any ideas?

edit: solved

make a copy of libmysqlclient.so.18 to /usr/lib/

[Lebofly]

[QUOTE=lantos;47561946]running ubuntu 14.04 lts 64 bit
having similar issues. placed
//same place as srcds_run
libmysqlclient.so.18 -> /gmod/
gmsv_mysqloo_linux.dll -> /gmod/lua/bin

edit:
I ran ldd on gmsv_mysqloo_linux.dll and got this

$ldd   gmsv_mysqloo_linux.dll         
        linux-gate.so.1 =>  (0xf7758000)
        libmysqlclient.so.18 => not found
        libstdc++.so.6 => /usr/lib/i386-linux-gnu/libstdc++.so.6 (0xf7646000)
        libgcc_s.so.1 => /lib/i386-linux-gnu/libgcc_s.so.1 (0xf7628000)
        libc.so.6 => /lib/i386-linux-gnu/libc.so.6 (0xf747a000)
        libm.so.6 => /lib/i386-linux-gnu/libm.so.6 (0xf7434000)
        /lib/ld-linux.so.2 (0xf7759000)

it seems that the libmysqlclient.so.18 is not loading from the /gmod/ where the srcds_run is. Any ideas?

edit: solved

make a copy of libmysqlclient.so.18 to /usr/lib/[/QUOTE]

Thanks man.

[Cabillaud94]

Hello everybody !

I'm actually working on my own very first gamemode. I'm using MySL to organize my data, heritage from my PhP period. But i actually get this strange error :

[IMG]http://image.noelshack.com/fichiers/2015/17/1429719295-sans-titre.png[/IMG]

Here's is my code.

require( "mysqloo" )

GM.Database = {}
GM.Database.Errors = {}
GM.Database.Object = mysqloo.connect(GM.Config.Database.Host, GM.Config.Database.User, GM.Config.Database.Password, GM.Config.Database.DBName, GM.Config.Database.Port)
GM.Database.Object.onError = GM.Database.Object.onErrorDefault
GM.Database.Object:connect()

GM.Database.onErrorDefault = function (Q, E, R)
	if GM.Database.Object:status() == mysqloo.DATABASE_NOT_CONNECTED then
		GM.Database.Object:connect()
	end

	print("")
	print("SQL Error ! : \"" .. Q .. "\", \"" .. E .. "\", \"" .. R .. "\"")
	print("")

	table.insert(GM.Database.Errors, {Q,E,R})
	return
end

Could someone help fix this ?
If something is missing, ask me.

The error is pretty simple and not colored. Maybe it's an intern error ?

Thank you for help !

Cabillaud94

[Donkie]

[QUOTE=Cabillaud94;47581768]-snip-[/QUOTE]
Try [url]http://www.facepunch.com/showthread.php?t=1442438[/url] instead.

[dok441]

Connection failed! Can't connect to local MySQL server through socket '/tmp/mysql.sock' (2)
Debian 8
What's the problem ?

[Blasteh]

Any chance you could add support for escaping utf8mb4?
It errors on mysqloo but not tmysql4

[Willox]

[QUOTE=Blasteh;48893452]Any chance you could add support for escaping utf8mb4?
It errors on mysqloo but not tmysql4[/QUOTE]

Show an example of code that doesn't work

[Blasteh]

This gets spammed in console when it tries to insert some utf8 characters to the database.
[IMG]http://i.imgur.com/xXjES2n.png[/IMG]

[lua]
function PROVIDER:GivePoints(ply, points)
if ply:IsBot() then return end
if not shouldmysql then self:GetFallback():GivePoints(ply, points) end
local _name = db:escape(ply:Nick()) --name tracking in database
local _steamid = db:escape(ply:SteamID()) --steamid tracking
local _sid64 = db:escape(ply:SteamID64())
local qs = [[
INSERT INTO `pointshop_data` (sid64, name, steamid, points, items)
VALUES ('%s', '%s', '%s', '%s', '[]')
ON DUPLICATE KEY UPDATE
points = points + VALUES(points),
name = VALUES(name),
steamid = VALUES(steamid),
sid64 = VALUES(sid64)

]]
qs = string.format(qs, _sid64, _name, _steamid, points or 0)
local q = db:query(qs)

function q:onError(err, sql)
if db:status() ~= mysqloo.DATABASE_CONNECTED then
db:connect()
db:wait()
if db:status() ~= mysqloo.DATABASE_CONNECTED then
ErrorNoHalt("Re-connection to database server failed.")
return
end
end
MsgN('PointShop MySQL: Query Failed: ' .. err .. ' (' .. sql .. ')')
q:start()
end

q:start()
end
[/lua]

[Willox]

MySQLoo doesn't set or let users set the charset? Gross.

[BlackAwps]

The whole module is gross and old, the fact people still use it amazes me.

[Blasteh]

Pointshop still uses it -.-

[MyDimeIsUp]

I think people still use MySQLOO is because MySQLOO is really the only compatible module with most MySQL addons.

I'm no expert at modules or MySQL so correct me if im wrong :P