+- SphereCommunity (https://forum.spherecommunity.net)
+-- Forum: Sphere 0.56d (/Forum-Sphere-0-56d)
+--- Forum: General Help (/Forum-General-Help)
+--- Thread: Strange MySQL Behavior (/Thread-Strange-MySQL-Behavior)
Strange MySQL Behavior - n1ghtwish - 02-08-2016 05:19 PM
First and foremost:
Windows 7 - .56c-Nightly Build 2681 2/6/2016
Okay, so I have TWO functions, whose names have been changed for simplicity... also, it is assumed that I have a working and established MySQL database connection with the following tables:
"testing" and "accounts"
The "accounts" table was created (last week) to mirror the accounts from Sphere and also keep an updated web-accessible record of last used IPs, last connect time, passwords, etc... it's also used as a web-based account registration system. PHP form --> MySQL table --> Sphere, via various scripts. This table currently has two rows, both containing data for the two accounts that I have in Sphere.
The "testing" table was created later (today) to debug the issue at hand. This table contains 9 rows of data, some empty entries included just to see if Sphere will handle it. The columns are named "data1" "data2" etc... and the rows have basic strings like "hello" and "fdhsjfa478343".
On to the functions:
Code:
[function function2]
db.query "select * from testing"
[function function1]
db.query "select * from accounts"function2 gives me no console activity, so I did this:
Code:
[function function2]
db.query "select * from testing"
for x 0 <eval <db.row.numrows> -1>
serv.log @TEST: Row # <dlocal.x> - data = <db.row.<dlocal.x>.2>
endforRunning the above command gives me a nice clean loop through the data in column 2, including some empties. No console errors.
Back to function1, still looking like this:
Code:
[function function1]
db.query "select * from accounts"and this is what I get....
Code:
23:01:DEBUG:__ thread (3772) __ | # | _____ function _____________ | ticks passed from previous function start ______
23:01:DEBUG:>> 3772 | 0 | CServer::OnTick | +0
23:01:DEBUG:>> 3772 | 1 | CServer::OnConsoleCmd | +0
23:01:DEBUG:>> 3772 | 2 | CServer::r_Verb | +0
23:01:DEBUG:>> 3772 | 3 | CScriptObj::r_Call | +0
23:01:DEBUG:>> 3772 | 4 | CScriptObj::OnTriggerRun | +0
23:01:DEBUG:>> 3772 | 5 | CServer::r_Verb | +0
23:01:DEBUG:>> 3772 | 6 | CScriptObj::r_Verb | +0 <-- exception catch point (below is guessed and could be incorrect!)
23:01:DEBUG:>> 3772 | 7 | CGFile::Close | +0
23:01:CRITICAL:(sphere_sql_functions.scp,47)"Access Violation" (0x16cf44), in CScriptObj::Verb()
23:01:DEBUG:command 'db.query' args '"select * from accounts' [0297B680]If I run the FOR loop on "accounts" table, I get a return on the FIRST row only, the above error still happens, and then my FOR loop is broken... even tried adding more rows to the mix, and the loop breaks after the first row is returned.
BOTH tables were created using PHPMyAdmin... I have renamed columns, tables, rows, even renamed data to take out all special characters and try to get the two similar in the sense that they both contain simple columns and rows of data... each and EVERY time I run these tests, each and every WAY I can possibly think of, I get the same results... Sphere hates the table I need to use, and likes the table that I don't.
Edit: I also forgot to mention that this does not happen with the pre-release executable.
-NW
RE: Strange MySQL Behavior - darksun84 - 02-09-2016 07:20 AM
Did you rename the accounts table as well ? Is possible that ACCOUNTS keyword is reserved ( i think that sphere parse everything)
RE: Strange MySQL Behavior - n1ghtwish - 02-09-2016 02:25 PM
(02-09-2016 07:20 AM)darksun84 Wrote: Did you rename the accounts table as well ? Is possible that ACCOUNTS keyword is reserved ( i think that sphere parse everything)
I thought of that too, and yep, renamed the table to "accts" "testing2" and even "blah" and still no dice.
I am going to wipe the DB and recreate it and see if that does the trick. Just find it odd that the pre-release didn't have any issues for me, yet no one has reported it as a bug and as far as I know, MySQL integration is pretty popular so you'd think someone would have caught it if it's an issue with the executable.
RE: Strange MySQL Behavior - Ben - 02-12-2016 03:24 AM
Since the error happens after the first row... It's possible the there is some data in the next row that sphere can't handle.
RE: Strange MySQL Behavior - n1ghtwish - 02-15-2016 04:23 AM
(02-12-2016 03:24 AM)Ben Wrote: Since the error happens after the first row... It's possible the there is some data in the next row that sphere can't handle.
Ben, thanks for the reply and that's a valid assumption. However, answer the following question regarding that...
why would the same debug message show up when I query the table with only 1 row, without the loop? simply a "db.query etc...." single line of code.
At this time, I have narrowed down the issue to ONLY MySQL queries (does not happen with execute). Also, the error only seems to appear while using the * operator inside the query... if I rewrite the function as follows, I do not get the debug errors at all and the command executes properly:
Code:
local.col = acctname
local.table = accounts
db.query "select <local.col> from <local.table>"
serv.log @Test: Querying column "<local.col>"
serv.log @Test: <eval <db.row.numrows>> rows returned.
for X 0 <eval <db.row.numrows> -1>
serv.log @Test: Row # <eval <dlocal.x> + 1> account name is <db.row.<dlocal.x>.acctname>
endfor�RE: Strange MySQL Behavior - n1ghtwish - 02-15-2016 04:01 PM
So, I've circumvented the error by specifying the columns I need to query, rather than using the * operator, which is probably better anyway, less unused data being parsed.
Code:
local.col = column1, column2, etc....
local.table = table
db.query "select <local.col> from <local.table> where blah = whatever"