Windows MySQL issue with regular queries

[dchampag]

mysql_real_query(): BEGIN - MYSQL ERROR: Lost connection to MySQL server during query (2013)

and

mysql_real_query(): SELECT `id`, `value`, `param`, `expires` FROM `bans` WHERE `type` = 1 AND `active` = 1 - MYSQL ERROR: Lost connection to MySQL server during query (2013)
WARNING: MYSQL Lost connection, attempting to reconnect...
WARNING: MYSQL Lost connection, attempting to reconnect...
WARNING: MYSQL Lost connection, attempting to reconnect...
WARNING: MYSQL Lost connection, attempting to reconnect...

Any ideas?

 

[Rexxar]

Is the MySQL server running on the same computer as the server?
What kind of security software do you have installed on your computer?
Is the MySQL service still running when you receive the error?

 

[dchampag]

yes
none
yes

I dont even need to reset the mysql server in order to get it working again, when this error appears the website and forums both are shut down also but as soon as i turn off the tibia server, the other things can connect

 

[Rexxar]

Try increasing the sqlKeepAlive setting in your config.lua to 10 or something.
However the fact that your website/forum stops working until the server is shut down puzzles me, does the website/forum show the same error (MySQL server has gone away)?

 

[dchampag]

It says that it cannot connect yes, but I have tryed changing both keep alive setting and timeout settings in different numbers for a couple weeks.

 

[Rexxar]

Try increasing the number of concurrent connections (max_connections) setting in the MySQL config file, cause it sort of sounds like your OT server is hogging all the connections.

 

[dchampag]

Already Tryed that

- - - Updated - - -

It just recently started with our current database size, I have Tryed adjusting buffers and what not but if someone could help me get a better understanding of what buffers and what not are better for it server that would help drastically

 

[Rexxar]

Jeez, I'm starting to run out of ideas here... Does your map have any really huge houses? Or ones with lots of items in it?
Because sometimes when the server tries send a BLOB of house items to the database, if the house is really large and/or has many items in it, the BLOB exceeds 1MB, effectively causing the "gone away..." error.

Or do you have any script or something which executes manual DB queries that are extremely long and data-heavy? Cause too large INSERT (as well as REPLACE) queries can also cause these kinds of issues.

---

As for your question about buffers, these links should help you;
MySQL - performance - How do I tune the MyISAM key buffer and InnoDB buffer pool in MySQL?
Are larger buffers always better ? - MySQL Performance Blog
As well as the official page on the matter: MySQL :: MySQL 3.23, 4.0, 4.1 Reference Manual :: 7.5 Buffering and Caching

 

[dchampag]

I do have large houses with lots of items. But other than that the server has no queries or anything that I've done that are data heavy, and the problem with innoDB is I get errors while players save... So I will have to somehow change my player table to fix the errors that should be fun...

 

[Rexxar]

Try increasing the "max_allowed_packet" to 10MB or something, it might help in case the issue is too big houseitems blobs; MySQL :: MySQL 5.0 Reference Manual :: 5.1.4 Server System Variables
Also what kind of errors do you get during player save?

 

[dchampag]

My max allowed packet is set for 16 mb already xD but I see other people posting this issue now? Maybe it's a bug someone found and is using it to crash servers?

- - - Updated - - -

Ughm ill have to run it and get the errors for ya sometime tomorrow

 

[Rexxar]

Try turning on maximal MySQL error/warning/debug logs. That should at least tell you MySQL's side of the story, why it's disconnecting from the OTserver/Websites.

 

[dchampag]

Well for sure it's the server not the sites because I gave them all seperate users have the sites a max connection limit of 10 each just to test and the server of 50 and the server didnt take any less time for the error to occur than with 100 connections but only the server was blocked site and forum both stayed online

 

[Rexxar]

Did the MySQL logs reveal anything? Such as a particular user (in this case the server) exhausting all available connections?

 

[dchampag]

Yeah, but it's not saying why just that it's the server opening too many connections.

 

[Rexxar]

Investigate the scripts you have, see if any of them uses the db.executeQuery() command. It might be that the server doesn't properly close those, or that the script is executed too fast and thus opens connections faster than the old ones can time out.

 

[dchampag]

Alright what function should I use in order for them to close? cause I do have a few scripts that use that function.

 

[Rexxar]

I don't think TFS has a LUA command for explicitly closing a connection. So I don't think that's possible unless you convert all scripts to use LUAsql instead of the TFS commands. :/
What connection limit did you put in your my.ini? And what connection limit did you give the user which is used by the server to connect to MySQL?

 

[dchampag]

well i switched it back to default because no sence in wasting resources if it doesn't help, but i tryed 150, 250, 350, and 500 without limits on the server at all, and only website and forum limited to a max of 10 connections.

but I dont mean a command that closes the connections but a command that maybe can execute a query differently?

But this issue only occured when I had save player on advance enabled and save players individually script now it's stopped.... but now if server crashes players lose things

 

[Rexxar]

Well increasing connections has nothing to do with wasting resources.
Try setting the connections limit in my.ini to 1000, and then increase the connection limit of the OT Server MySQL user to 990.