Script not connecting to MySQL Database

Hello guys, I have a Lua script I downloaded which will enable logging to a MySQL database. I’ve downloaded the module “mysqloo” and it now works properly. (It is needed for this script).

I don’t know what the problem is but here’s the code for the logging system when it sends it to the database
And yes, I have created a database and a user to it. I have entered valid information into the script cuz I get a connected message but the a error message saying it is not connected anymore:


// SLA - Simple Logging Addon - Seth

SLA.DataTypes.MySQL = {
	Enabled = true,
	
	Host = 'HOST',
	Db = 'DATABASE',
	User = 'USERNAME',
	Pass = 'PASSWORD',
	Port = 3306
};

if(!SLA.DataTypes.MySQL.Enabled) then return; end

if(!require('mysqloo')) then
	SLA.DataTypes.MySQL.Enabled = false;
	print('SLA - Please install mysqloo module correctly!');
	return;
end

SLA.DB = mysqloo.connect(SLA.DataTypes.MySQL.Host, SLA.DataTypes.MySQL.User, SLA.DataTypes.MySQL.Pass, SLA.DataTypes.MySQL.Db, SLA.DataTypes.MySQL.Port);

SLA.DB.onConnected = function(self)
	print('SLA - Connected to MySQL!');
	
	local qy = SLA.DB:query([[
	CREATE TABLE IF NOT EXISTS `sla_logs` (
	`unixtime` INT NULL DEFAULT '0',
	`nicetime` VARCHAR(50) NULL DEFAULT '0',
	`msg` VARCHAR(1000) NULL DEFAULT '0'
	)]]);
	
	function qy.onError(self, err)
		print('SLA - Query failed:', err);
	end
	
	qy:start();
end

SLA.DB.onConnectionFailed = function(self, err)
	print('SLA - MySQL connection failed:', err);
end


function SLA.LogToMySQL(msg)
	if(SLA.DataTypes.MySQL.Enabled) then
		if(SLA.DB:status() != mysqloo.DATABASE_CONNECTED) then
			print('SLA - Attempted to run MySQL query with no database connection!');
			return;
		end
		
		local qy = SLA.DB:query(string.format("INSERT INTO `sla_logs`(`unixtime`, `nicetime`, `msg`) VALUES('%s', '%s', '%s')", os.time(), os.date('%m/%d/%y %H:%M:%S'), SLA.DB:escape(msg)));
		
		function qy.onError(self, err)
			print('SLA - Query failed:', err);
		end
		
		qy:start();
	end
end

SLA.DB:connect();

I get these messages in the console when I restart the server:


SLA - Connected to MySQL!

and


SLA - Query failed:	MySQL server has gone away

and when it tries to log something to the database, it says this:


SLA - Attempted to run MySQL query with no database connection!

Any clues?

You need to run SLA.DB:connect() when the [lua]print(‘SLA - Attempted to run MySQL query with no database connection!’);
return;[/lua] code is run. MySQL servers close the connection after a certain time of not receiving any data.

leiftiger, Like this:


// SLA - Simple Logging Addon - Seth

SLA.DataTypes.MySQL = {
	Enabled = true,
	
	Host = 'HOST',
	Db = 'DB',
	User = 'USER',
	Pass = 'PASS',
	Port = 3306
};

if(!SLA.DataTypes.MySQL.Enabled) then return; end

if(!require('mysqloo')) then
	SLA.DataTypes.MySQL.Enabled = false;
	print('SLA - Please install mysqloo module correctly!');
	return;
end

SLA.DB = mysqloo.connect(SLA.DataTypes.MySQL.Host, SLA.DataTypes.MySQL.User, SLA.DataTypes.MySQL.Pass, SLA.DataTypes.MySQL.Db, SLA.DataTypes.MySQL.Port);

SLA.DB.onConnected = function(self)
	print('SLA - Connected to MySQL!');
	
	local qy = SLA.DB:query([[
	CREATE TABLE IF NOT EXISTS `sla_logs` (
	`unixtime` INT NULL DEFAULT '0',
	`nicetime` VARCHAR(50) NULL DEFAULT '0',
	`msg` VARCHAR(1000) NULL DEFAULT '0'
	)]]);
	
	function qy.onError(self, err)
		print('SLA - Query failed:', err);
	end
	
	qy:start();
end

SLA.DB.onConnectionFailed = function(self, err)
	print('SLA - MySQL connection failed:', err);
end


function SLA.LogToMySQL(msg)
	if(SLA.DataTypes.MySQL.Enabled) then
		SLA.DB:connect();
		if(SLA.DB:status() != mysqloo.DATABASE_CONNECTED) then
			print('SLA - Attempted to run MySQL query with no database connection!');
			return;
		end
		
		local qy = SLA.DB:query(string.format("INSERT INTO `sla_logs`(`unixtime`, `nicetime`, `msg`) VALUES('%s', '%s', '%s')", os.time(), os.date('%m/%d/%y %H:%M:%S'), SLA.DB:escape(msg)));
		
		function qy.onError(self, err)
			print('SLA - Query failed:', err);
		end
		
		qy:start();
	end
end

SLA.DB:connect();

No, it should be inside the if case, perhaps also remove the return to continue the logging since the mysql database should connect. It’s not really good to use the mysqloo module like this. In my gamemode I created my own mysqloo function where all queries are queued and when the queue is empty the server disconnects and when it’s not empty it reconnects again thus going around the problem that the mysql database closes the connection.

You don’t need SLA.DB:connect( ), it does it for you. The issue is that your MySQL server is timing out

I will post my code here used for connecting to a database and authing players if you want, but it has alot of tables and I am afraid it could be confusing.

Your issue is that you aren’t utilizing callbacks for when you connect as AFAIK mysqloo is threaded so before the query responds you are trying to use its data.

Check out the documentation for how to do it, there should be some good examples there.