+- SphereCommunity (https://forum.spherecommunity.net)
+-- Forum: Sphere 0.56d (/Forum-Sphere-0-56d)
+--- Forum: General Help (/Forum-General-Help)
+--- Thread: Problem with MySQL (/Thread-Problem-with-MySQL)
Problem with MySQL - DartHVirS - 01-13-2018 03:11 AM
Hello,
I have the following code that works good, it updates the table successfully, but there are errors in the console.
The code is:
Code:
[FUNCTION f_estadisticasDB]
if ( <DB.connected> )
DB.QUERY "SELECT account, player, uid, oro, auocoins, puntos, karma, fama, kills, killsnpc, deaths, str, dex, inte FROM estadisticas WHERE uid='<src.uid>'"
if (<DB.ROW.NUMROWS> > 0)
DB.EXECUTE "UPDATE estadisticas SET oro='<src.gold>', auocoins='<eval <src.account.tag.auocoins>>', puntos='<eval <src.tag.puntos>>', karma='<src.karma>', fama='<src.fame>', kills='<src.kills>', killsnpc='<eval <src.tag.killsnpc>>', deaths='<src.deaths>', str='<src.str>', dex='<src.dex>', inte='<src.int>' WHERE uid = '<src.uid>'"
else
DB.EXECUTE "INSERT INTO estadisticas (account, player, uid, oro, auocoins, puntos, karma, fama, kills, killsnpc, deaths, str, dex, inte) VALUES('<src.account.name>', '<src.name>', '<src.uid>', '<src.gold>', '<eval <src.account.tag.auocoins>>', '<eval <src.tag.puntos>>', '<src.karma>', '<src.fame>', '<src.kills>', '<eval <src.tag.killsnpc>>', '<src.deaths>', '<src.str>', '<src.dex>', '<src.int>')"
endif
endifThe error messages on sphere console are:
05:03:CRITICAL:(estadisticasDB.scp,12)"Access Violation" (0x103a14), in CScriptObj::Verb()
05:03:DEBUG:command 'DB.QUERY' args '"SELECT account, player, uid, oro, auocoins, puntos, karma, fama, kills, killsnpc, deaths, str, dex, inte FROM estadisticas WHERE uid='0153e4'' [0C9FD5BC]
05:03:DEBUG:__ thread (1864) __ | # | _____________ function _____________ | __ ticks passed from previous function start __
05:03:DEBUG:>> 1864 | 0 | CWorld::OnTick | +0
05:03:DEBUG:>> 1864 | 1 | CGObList::DeleteAll | +16
05:03:DEBUG:>> 1864 | 2 | CClient::CharDisconnect | +0
05:03:DEBUG:>> 1864 | 3 | CChar::OnTrigger | +0
05:03:DEBUG:>> 1864 | 4 | CScriptObj::OnTriggerScript | +0
05:03:DEBUG:>> 1864 | 5 | CScriptObj::OnTriggerRunVal | +0
05:03:DEBUG:>> 1864 | 6 | CScriptObj::OnTriggerRun | +0
05:03:DEBUG:>> 1864 | 7 | CChar::r_Verb | +0
05:03:DEBUG:>> 1864 | 8 | CClient::r_Verb | +0
05:03:DEBUG:>> 1864 | 9 | CScriptObj::r_Verb | +0
05:03:DEBUG:>> 1864 | 10 | CChar::r_Verb | +0
05:03:DEBUG:>> 1864 | 11 | CObjBase::r_Verb | +0
05:03:DEBUG:>> 1864 | 12 | CScriptObj::r_Call | +0
05:03:DEBUG:>> 1864 | 13 | CScriptObj::OnTriggerRun | +0
05:03:DEBUG:>> 1864 | 14 | CScriptObj::OnTriggerRun | +0
05:03:DEBUG:>> 1864 | 15 | CChar::r_Verb | +0
05:03:DEBUG:>> 1864 | 16 | CObjBase::r_Verb | +31
05:03:DEBUG:>> 1864 | 17 | CScriptObj::r_Verb | +0 <-- exception catch point (below is guessed and could be incorrect!)
05:03:DEBUG:>> 1864 | 18 | CGFile::Close | +0
05:03:DEBUG:command 'DB.QUERY' args '"SELECT account, player, uid, oro, auocoins, puntos, karma, fama, kills, killsnpc, deaths, str, dex, inte FROM estadisticas WHERE uid='0153e4'' [0C9FD5BC]
Anybody has a clue what is happening here?
THank you.
RE: Problem with MySQL - darksun84 - 01-13-2018 03:25 AM
Is the function called when the player disconnect/deleted?
RE: Problem with MySQL - DartHVirS - 01-13-2018 03:52 AM
(01-13-2018 03:25 AM)darksun84 Wrote: Is the function called when the player disconnect/deleted?
Yes, every time they log in, and log out, but also happens when it is triggered in other situations.
RE: Problem with MySQL - DartHVirS - 01-13-2018 02:16 PM
FIXED!, well not really fixed because is something that should be fixed on spheresrv.exe file I guess.
The thing is that the SELECT does not handle more than 8 columns at once, so my temporary fix was to split the query.
Coruja, maybe this is something you can take a look.
Thank you!
RE: Problem with MySQL - Coruja - 01-15-2018 06:02 PM
not sure if this is really the problem, but the function DB.QUERY "SELECT ..." have an buffer of max 12 args, and your function have 14
I don't have any MySQL server here to test, but try check on your server the exact max arguments that it accepts, if we find the value it will be easy to fix
RE: Problem with MySQL - Kanibal - 01-16-2018 06:46 AM
Fixed
Code:
[FUNCTION f_estadisticasDB]
if ( <DB.connected> )
DB.EXECUTE "DELETE FROM estadisticas WHERE uid='<src.uid>'"
DB.EXECUTE "INSERT INTO estadisticas VALUES('<src.account.name>', '<src.name>', '<src.uid>', '<src.gold>', '<eval <src.account.tag.auocoins>>', '<eval <src.tag.puntos>>', '<src.karma>', '<src.fame>', '<src.kills>', '<eval <src.tag.killsnpc>>', '<src.deaths>', '<src.str>', '<src.dex>', '<src.int>')"
endifRE: Problem with MySQL - Coruja - 01-19-2018 06:06 AM
try using latest sphere build, I optimized the entire MySQL engine and now DB.QUERY function can handle "SELECT ..." commands with up to 64 columns
PS: and also don't forget to use "LIMIT 1" on SELECT or UPDATE commands, this will avoid SQL server to waste performance looping through all rows of the table even when the wanted row is already found
RE: Problem with MySQL - DartHVirS - 01-19-2018 07:49 AM
(01-19-2018 06:06 AM)Coruja Wrote: try using latest sphere build, I optimized the entire MySQL engine and now DB.QUERY function can handle "SELECT ..." commands with up to 64 columns
PS: and also don't forget to use "LIMIT 1" on SELECT or UPDATE commands, this will avoid SQL server to waste performance looping through all rows of the table even when the wanted row is already found
thank you!!
RE: Problem with MySQL - invernizzi - 01-20-2018 10:15 PM
Thx for help and ur "PS" Coruja, this will help me a lot to optimize my SQL commands