ID:97217
 
So I'm playing around with spies, trying to eliminate as much 'lag' as possible; everything is going fine. Then I try to upload a record into the network...




Is MySQL on BYOND this laggy or am I doing it wrong? I am going to be relying on a lot of MySql commands to manipulate databases within the game, and if its going to lag that much I might as well stick with a BYOND save file....

EDIT: Here is the code I'm using;
var/mysql/server    //contains all network databases
proc
initialize_sql_database()

server = new (/*data here*/)

mob/verb/add_record()

server.Add_Record(3, src.real_name, md5(ckey(src.real_name) + "encoder"), 1, src.ckey)


mysql




var
DBConnection/dbcon
ready = 0

New(sql_host, sql_database, sql_user, sql_password)
. = ..()

dbcon = new /DBConnection()

var/connected = dbcon.Connect("dbi:mysql:[sql_database]:[sql_host]", sql_user, sql_password)
if(!connected || !dbcon.IsConnected())
world.log << "Database Connection error: [dbcon.ErrorMsg()]"
ready = 0
else
ready = 1

proc
Quote(text)
if(isnull(text))
text="NULL"
else
text="[text]"

return dbcon.Quote(text)


GetRecordNames()
if(!dbcon.IsConnected())
world << "Database Disconnected: [dbcon.ErrorMsg()]"
return 0
var
query_sql = "SELECT * FROM `records` WHERE `id` = 1"
DBQuery/query = dbcon.NewQuery(query_sql)

var/list/chars = new

if(!query.Execute())
world << "GetRecordNames failed:\n\t[query_sql]\n\t[query.ErrorMsg()]"
return 0

while(query.NextRow())

var/list/columns = query.GetRowData()

chars += columns["name"]

return chars

RecordNameUsed(name)

if(!dbcon.IsConnected())
world.log << "Database Disconnected: [dbcon.ErrorMsg()]"
return 0

var
query_sql = "SELECT COUNT(*) FROM `records` WHERE `name` = [Quote(name)]"
DBQuery/query = dbcon.NewQuery(query_sql)

if(!query.Execute())
world.log << "RecordNameUsed failed:\n\t[query_sql]\n\t[query.ErrorMsg()]"
return 1

while(query.NextRow())
if(text2num("[query.item[1]]") > 0)
return 1

return 0

GetRecord(name, agency)
if(!dbcon.IsConnected())
world << "Database Disconnected: [dbcon.ErrorMsg()]"
return 0

var
query_sql = "SELECT * FROM `records` WHERE `name` = [Quote(name)] OR `agency` = [Quote(agency)]"
DBQuery/query = dbcon.NewQuery(query_sql)

if(!query.Execute())
world << "GetRecord failed:\n\t[query_sql]\n\t[query.ErrorMsg()]"
return 0

if(query.NextRow())
//var/list/columns = query.GetRowData()

//world<<columns["name"]

return 1


Add_Record(agency, name, fingerprint, version, ckey)

name = ckey(name)
ckey = ckey(ckey)


if(!dbcon.IsConnected())
world.log << "Database Disconnected: [dbcon.ErrorMsg()]"
return 0
var/query_sql
var/upd = {"
agency =
[Quote(agency)],
name =
[Quote(name)],
fingerprint =
[Quote(fingerprint)],
version =
[Quote(version)],
ckey =
[Quote(ckey)]
"}


if(GetRecord(name)) ///it exists, change query

query_sql = "UPDATE `records` SET [upd] WHERE name = [Quote(name)]"

else

query_sql = "INSERT INTO `records` SET [upd]"//(`agency`, `name`, `fingerprint`, `version`, `ckey`) VALUES ([Quote(agency)], [Quote(name)],[Quote(fingerprint)],[Quote(version)],[Quote(ckey)])"

var
DBQuery/query = dbcon.NewQuery(query_sql)

if(!query.Execute())
world << "Createrecord failed:\n\t[query_sql]\n\t[query.ErrorMsg()]"
return 0

return 1





Delete_Record(name)

if(!name)
return 0

name = ckey(name)

if(!dbcon.IsConnected())
world << "Database Disconnected: [dbcon.ErrorMsg()]"
return 0

var
query_sql = "DELETE FROM `records` WHERE `name` = [Quote(name)]"
DBQuery/query = dbcon.NewQuery(query_sql)

if(!query.Execute())
world << "Deleterecord failed:\n\t[query_sql]\n\t[query.ErrorMsg()]"
return 0

return 1



SaveItems(key,item[])
SaveCharacter(key)
I have zero speed issues with the pretty heavy MySQL usage I have in my projects.
You should show some code.

P.S: And get a CSS that isn't completely unreadable in IE. :)
Alrighty, could I take a look at some of your code?
Show us the database table structure you're using, and the queries you are doing.
Here is my structure,

http://i49.tinypic.com/zv9ai0.png

The above code is my ENTIRE MySQL code, other than Dantom.DB which is being used stock.
Surely names must be unique? If so, you don't need an id column, name will just do, and set the primary key and unique flags (on the right) for name.
Thanks for pointing that out.

Now, would you have any insight as to why this lags as much as it does?
Nothing stands out to me as being terribly wrong in your DM and SQL.

Do you use this MySQL server elsewhere? Does it perform reliably there?
Run some queries in phpMyAdmin's SQL console and take note of the execution time.

Perhaps there is a problem with your mysql client DLL? Just grabbing at straws here.
AH Murrawhip, your awesome!

So my queries were being executed within fractions of a second, this was okay. Then I decided to re-download libmysql.dll for the hell of it. I go to replace the files and my original was 1.44 Mb while the new one was around 288 Kb.... odd
I replaced the .dll and it runs as smooth as my other processes, 0.00 cpu time! That's what I like to see

Quick question, only the computer that the game is being hosted on needs this .dll correct? Players don't need to have this .dll located on their own systems?
Yes, Shifter, the players don't need the .dll
Do you host MySQL or use external? Connection lag will hit execution time too, because DM waits for respond, which doesn't arrive instantly.
Thanks D4RK3
Now I recently purchased a shell server to host the game, and now my mysql doesn't work. I put in libmysql.dll into the folder where the .dmb is located, yet that doesn't work. Does anybody know how to get this working?
Suicide Shifter wrote:
Thanks D4RK3
Now I recently purchased a shell server to host the game, and now my mysql doesn't work. I put in libmysql.dll into the folder where the .dmb is located, yet that doesn't work. Does anybody know how to get this working?

Any error message?
Is this 'shell server' windows-based or not?
You can't use the DLL for nix-based stuff.

Database Disconnected: libmysqlclient.so: cannot open shared object file: No such file or directory

Its Zen hosting, I'm not entirely sure
Murrawhip wrote:
Is this 'shell server' windows-based or not?
You can't use the DLL for nix-based stuff.

If that is the case, is there any possible way to get it working? Or should I be deleting my mysql code...?
Suicide Shifter wrote:
If that is the case, is there any possible way to get it working? Or should I be deleting my mysql code...?

Download MySQL for unix, and copy/paste libmysqlclient.so instead of .dll
I dunno the guys at Zen Hosting, but send them an email/post on their forums to see if they can get that shiz working for you.
It involves installing mysql-client.
Database Disconnected: /home/penkovs/byond/bin/libmysqlclient.so: file too short