Help with connect to mysql

[lgeri000]

Hey guys,

i tried to write an account system for the first time with the help of a youtube video. The problem is that somewhy i cant connect to my mysql.

This is my code which connect to the database, and its writing in the chatbox that mysql connection successful.

local dbname = "lp";
local host = "127.0.0.1";
local username = "root";
local password = "";

local con;
local connectionAttempts = 1;

function connectMysql()
	con = dbConnect( "mysql", "dbname="..dbname..";host="..host..";charset=utf8", username, password, "share=1" );

	if (con) then
		outputChatBox("#FFFFFFMySQL connection#00AA00 successful#FFFFFF!", root, 0, 0, 0, true);
		connectionAttempts = 1;
	else
		outputChatBox("#FFFFFFMySQL connection#AA0000 failed#FFFFFF! trying again("..connectionAttempts.."/3) in 10 sec.", root, 0, 0, 0, true);
		if (connectionAttempts < 3) then
			connectionAttempts = connectionAttempts+1;
			setTimer(connectMysql, 10000, 1);
		else
			outputChatBox("#FF0000MySQL connection failed! The server will shut down in 30 sec.", root, 0, 0, 0, true);
			setTimer(function()
				shutdown("Unable to connect to MySQL database");
			end, 30000, 1);
		end
	end
end

connectMysql();

function getConnection()
	if (con) then
		return con;
	end
end

its meta file:

<meta>
	<info author="lgeri000" name="MySQL" version="alpha" type="script" />

	<script src="server.lua" type="server" />

	<export function="getConnection" type="server" />

</meta>

and than i export the getConnection function in an other script:

local con = exports.mysql:getConnection()

and from this point it doesnt work.

The consoles message is:

ERROR: Account\server.lua:1: exports: Call to non-running server resource (mysql) [string "?"]

if i restart the mysql script (the first code) and the accountsystem script i can register, and its appear in the database, but i cant log in.

i also tried to use the code of the youtuber but it didnt work either

thank you for the help in advance

Edited July 28, 2020 by lgeri000

[IIYAMA]

 

hmmm, not sure. Maybe your timing is mismatched?

 

setTimer(function () 
	local mysqlResource = getResourceFromName ( "mysql" )
	if getResourceState(mysqlResource) == "running" then
		local con = call ( mysqlResource, "getConnection")
		iprint("connection", con)
	else
		iprint("running state", getResourceState(mysqlResource))
 	end
end, 1000, 1)

 

 

This is how I handle MySQL failures:

function connectMysql()
	con = dbConnect( "mysql", "dbname="..dbname..";host="..host..";charset=utf8", username, password, "share=1" );

	if (con) then
		outputChatBox("#FFFFFFMySQL connection#00AA00 successful#FFFFFF!", root, 0, 0, 0, true);
		return true
	end
	return false
end

addEventHandler("onResourceStart", resourceRoot, 
function () 
	if not connectMysql() then
		cancelEvent (true, "Can't connect to MySQL server" )   
	end
end)

 

Edited July 28, 2020 by IIYAMA

[lgeri000]

its still not working, but now the console writes:

INFO: "connection" elem:db-connection00000049

this is the part of the script where it tries to log in maybe it helps

addEvent("attemptLogin",true)
addEventHandler("attemptLogin", resourceRoot,
	function(un,pass)
		
		if not(isPlayerBanned(client)) then
			local qh = dbQuery(con, "SELECT * FROM accounts WHERE username=?",un)
			local user dbPoll(qh,500)
			if (user) then
				if(#user > 0) then
					if(user[1]["password"] == pass) then
						setElementData(client, "acc:id", user[1]["id"])
						triggerClientEvent(client,"accountServerResponse", resourceRoot, "loginSuccess")
						spawnPlayer(client,0,0,4,29)
						setCameraTarget(client,client)
						fadeCamera(client,true)
					else
						triggerClientEvent(client,"accountServerResponse", resourceRoot, "wrongPass")
					end
				else
					triggerClientEvent(client, "accountServerResponse", resourceRoot, "noAccount")
				end
			else
				triggerClientEvent(client,"accountServerResponse",resourceRoot,"unknownError")
			end
		else
			triggerClientEvent(client, "accountServerResponse",resourceRoot,"banned")
		end
	end
)

it returns the unknownError every times when i try to log in or register

[IIYAMA]

4 minutes ago, lgeri000 said:

it returns the unknownError every times when i try to log in or register

 

local user = dbPoll(qh,500)

"=" was missing.

 

---

 

And you might also want wait for it a little bit longer, maybe your database is slow.

local user = dbPoll(qh, -1)

 

---

 

"SELECT * FROM accounts WHERE username=?"

Add a limit to speed up your queries:

 

"SELECT * FROM accounts WHERE username=? LIMIT 1"

 

---

 

Or your username is incorrect.

iprint("username", un)

 

[lgeri000]

Thanks for your help finally it works