Right, anyone had this DB related error (hstmt)?

[lifestream]

DB error (hstmt)/broken guilds

321 08/08/12 06:40:51 : Query : select MemberCount from tbl_guild where serial = 6
322 08/08/12 06:40:51 : SqlState:HY000, NativeError:0 Msg:[Microsoft][ODBC SQL Server Driver]Connection is busy with results for another hstmt
323 08/08/12 06:40:51 : SQLExecDirect Returns : SQL_ERROR

Whats weird about it is that is only for guild table. If the connection really were busy, it would apply to other tables aswell.

As a result guild creation and accepting members wont work. And with enough errors the zone gets disconnected from database.

I have used different, clean server files, remade odbc-s.
PS! wipe is not an option, so i cant use completely clean, new DB-s.


If anyone helps me to get this issue resolved i can throw bunch of rf related inf o up here i normally wouldn't share.

I run a test with clean database on same setup and it worked. Its like some index is wrong and query for something wont close or something like that.

There was like few more 216 with similar error.

 

[jbrannon2]

Seems to point to rowset returns being too 'small' and not completing the query before the query is executed again.

i forget on 2005, but when you Right-click a table does it show "Select Top ### rows" ?
or just Select all?

I know how to change that much, but will search for ODBC connections if there's something different



ADDED: may want to double check this section as well




Not saying that is the problem, but might as well rule it out Ironically, it's backwards from the return results. In theory if you have Rowcount above 0 in the screen shot, then SQL will not stop running the first query until that many rows comes up. setting it to 0 allows the query to stop even if no guilds exists in that case

 

[lifestream]

run a test today, same setup with clean db-s all worked. so odbc can be ruled out, its a database issue unfortunately.
and the select is ok, iv set it t o0 quite a long time ago.
Visually all tables check out, triggers are there, master keys are there, users and permissions, collation etc seems ok.

Im not sure where its broken.

These errors are zoneserver ones, dblog. Nothign to do with SSMS.

 

[jbrannon2]

mm, out of curiosity then, does the error always come from guild serial 6 ?
or was it just that one in the particular instantce you made note of the error?

could be something on that row causing issues.

especially if you set it up with clean DBs and it worked just fine

 

[lifestream]

its for all serials, essentially all guilds had to manually create - it comes up mainly when people apply for guild.
Since the same problem is linked to guilds being unable to accept people (cuz the membercount wont update until zone reboot - the query returns error above), same problem kind of stops the guild creation (noone gets assigned to guild).

Its all more or less guild related.


I cant think of where or what could possibly go wrong. Some hidden settings, logs, connections that don't match up?.
Iv dropped and recreated all guild related tables, still nothing.
Visually looking through, comparing options etc there seems to be 100% match, yet it works on clean db and not with this one.

I wish it woudl say what query is keeping it busy...

So as i said, its far from simple issue :/ I cant just wipe the db-s cuz that would be basically same as closing down the server, noone likes wipes.

 

[lifestream]

im gonna give up on this soon... nothing works... its like some magic sql fairy lost a wing and now its not working.

 

[lifestream]

need to now come back to the issue since got the content etc patches done for now.

This one needs some serious magic to make it work.

 

[Ron]

Someone on skype said the fix was the server time; he rolled back the date on the server and the issue went away.

 

[lifestream]

i guessed as much, but i cant really do that - gamecp is on the same box and rolling back time has impact on users CPT (the 30 days not logged in crap will give serious -cpt).

Been looking for diff time related settings, but the create date on guild table is not it - u could manually adjust it to older date and it wont work. Need to look more for date related columns then.

 

[lifestream]

UPDATE:
I run a complete trace of SQL actions during the time when a member is accepted.
Results:
Everything seems to work perfectly.
Selects the membercount following with user guild data update (executes the stored procedure and completes it, updating user guild serial, status etc in general) that finishes successfully.

Everything matches the working database race (same actions).
The fact that on server reboot people are in the guild proves that its all ok.

The problem is with membercount. Its selected into memory (zoneserver) so ingame they get accepted and database membercount updates later (server reboot or some fixed time?).

So thats where the problem is located - somehow, the membercount value never reaches the zone and wont be updated.
However,, in that case, you could suspect its a odbc, server file etc issue but its not. A clean copy of database seems to magically work again.

 

[jbrannon2]

you have a dev server on same box?

could be a broken relationship somewhere between the tables.

Didn't know if there was any mileage in backup full world, restore as DEV, try cleaning the Guild tables only, see if it worked on the Dev server. If it ends up working, do the same for live server. Players may not like rebuilding guilds, but at least they don't lose everything else to a wipe.

I had to do something similar with mine back when race candidate table was screwing up a few months ago.

 

[lifestream]

dev server is on a separate box.
I have already cleaned guilds, even remade the table.

Manual queries work fine, i can select use procedures to change/update etc
Where it goes wrong is the membercount part - it selects it and completes just fine - but somehow it never reaches the zoneserver.

So zone reports the busy hstmt error for the select statement (dblog) - as if it timed out waiting for results.

But amazingly without changing any of the files or odbc with just a new database from backup (rename the bugged ones) - and it works.

IT actually started just randomly also, out of the blue. No updates, crashes, downtimes - and it just stopped working.

 

[jbrannon2]

thought about just changing the query in zone to stop it?
unless you actually use those logs for a reason, there's really no need to have them anyway.

And if it's just messing it up for a log that you rarely if ever use, it's just getting in the way

 

[lifestream]

its not just the log lol the guild itself fails ingame cuz of that (cant create and accept members since the membercount doesn't update)

 

[jbrannon2]

its not just the log lol the guild itself fails ingame cuz of that (cant create and accept members since the membercount doesn't update)

Apologies, just re-read everything to refresh what was going on.

I did test this part

The problem is with membercount. Its selected into memory (zoneserver) so ingame they get accepted and database membercount updates later (server reboot or some fixed time?).

on my server and my tbl_Guild updated immediately. I logged in a normal character left a guild, ran query it updated, then rejoined guild ran the query it updated again instantly.

So is it supposed to update. looking at the properties the column isn't computed, so not exactly sure where it's breaking down for you unless the zone actually takes the info, adds +1 to it, and returns the update

 

[lifestream]

i assume you are not running 216? 216 is famous for keeping too much stuff in memory (therefor the rollback issues).

 

[jbrannon2]

i assume you are not running 216? 216 is famous for keeping too much stuff in memory (therefor the rollback issues).

oh no, figured Fasty told you, I graduated to 2.2.3.2. though if need be i can throw up a 216 server on my machine right here in about 30 minutes.

However, I am trying to help out where i can, as I believe the issue helping here, may lead me to some insight on fixing the honor guilds on 2.2.3.2

 

[lifestream]

yeah haven't run 2232 so not familiar with honor guild issue, except it could be simply stored procedure problem. run server profile to find out what procedure it uses. It could be as simple as "set quoted Identifier" is set to OFF.


But as for the guild issue - if id eliminate odbc and zone (works with clean database) and yet the select works fine if i use it through php etc - then what else is there after SQL selects it and before ODBC forwards it to zone Oo

 

[efraim14]

Log of My Guild Error (Maybe you know this log?)

I was testing on my test server, creating the guild manual and try to accept my friend that connecting to the test server. When im trying to Confirm his request to join my guild, i got this log directly in my server.

0 09/23/12 11:15:49 : Query : { CALL pUpdate_UserGuildData_20070614( 565586, 1, 0 ) }
1 09/23/12 11:15:49 : SQLExecDirect Returns : SQL_NO_DATA
2 09/23/12 11:16:03 : Query : select MemberCount from tbl_guild where serial = 1
3 09/23/12 11:16:03 : SqlState:HY000, NativeError:0  Msg:[Microsoft][ODBC SQL Server Driver]Connection is busy with results for another hstmt 
4 09/23/12 11:16:03 : SQLExecDirect Returns : SQL_ERROR
5 09/23/12 11:16:14 : Query : select MemberCount from tbl_guild where serial = 1
6 09/23/12 11:16:14 : SqlState:HY000, NativeError:0  Msg:[Microsoft][ODBC SQL Server Driver]Connection is busy with results for another hstmt 
7 09/23/12 11:16:14 : SQLExecDirect Returns : SQL_ERROR

Anyone got a clue what is these things?

Anyway i just fixed Deposit Guild Funds, Leave Guild, Senate Guild. Now they aren't crash anymore. But still joining guild is the main problem for me because those error logs.

 

[Busman]

Re: Log of My Guild Error (Maybe you know this log?)

try changing your server box date to 2008 and restart the zone then try normally if you can accept people and stuff.