MySQL has Gone Away - Threaded Loading

[Jabantiz]

Should we actually do the proper fix though? If we have multiple zones running with multiple clients is it not possible that the crash errors I posted using just the original "database" could happen then as well? After all a zone is just a new thread and each zone can run database queries.

[John Adams]

I will always vote for a "proper fix", if there is one.

I thought Scat said we didn't need new DB constructors inside the threads, but saw his code fixes left them there. So I likely do not understand, again

[Jabantiz]

I will always vote for a "proper fix", if there is one.

I thought Scat said we didn't need new DB constructors inside the threads, but saw his code fixes left them there. So I likely do not understand, again

Using the original database declare like scat suggested produces the errors I posted, the proper solution is the link scat provided basically saying MySQL doesn't like threaded applications and tells you to link to a specific library and have a mutex on the connection for threaded applications.

The solution we went with fixes the immediate problem which was a copy & paste error on my part and world can now start up with no errors.

[Scatman]

Yep it's a proper fix. MySQL requires you call mysql_init() on each thread, which is called before the MySQL client establishes a TCP connection with the server. Each thread has some thread-specific variables that need to be initialized. When we commented out those lines, the MySQL object for that thread had never been inited(), causing unexpected behavior.

[John Adams]

When we commented out those lines, the MySQL object for that thread had never been inited(), causing unexpected behavior.

Well hang on a second now... that db.Init() was in there and threw errors on startup while Threaded. I'm the guy who commented it out, because after doing so I no longer got errors. Unless it was a total coincidence/timing. I do remember ThreadedMode = 0 worked 100% of the time, so this is why I blamed threading.

Which btw I miss because it was a hella lot faster to load the world

AND! This didn't happen on every machine I use. Only my Win7 local mysql server World connection, with my world using the Public Login/Patcher. Anyway, does this mean this fix is "bigger than a breadbox"? Or something we can implement relatively easy? For now, I will just switch the default rule behavior to non-threaded loading until we know this is fixed.

[Jabantiz]

John:
There should be a fix on svn currently for starting up the server. The issue was I defined the new database as db, I copied and pasted code into these threads and never changed database to db and that is what was causing the crashes at startup.

Scatman:
ZoneServer's are just new threads, we never call the init code on those threads so won't it be possible that the errors could show up down the road on them?

[Scatman]

It sure is. I need to do some more research on it. One of my co-workers at work has written their own MySQL database engine for stuff we do and also has submitted a few MySQL patches so he knows it very well. I will ask him exactly what we need to do in a multi-threaded environment, or if there's some multi-threaded MySQL library we can use.

To me, it appears this should be happening more often; given what was written in the MySQL documentation.

[John Adams]

This is troubling indeed. We've had multiple threads since the dawn of time, and only now this is coming up? I find it very hard to believe MySQL has been around this long, and cannot handle threads. Of course, likely do not understand the technical reasoning behind it (I'm a data guy, not an engine guy) but I'm sure you guys will let me know if some major overhaul needs to be done here. I sure hope not...

Question, how does every other emu out there handle this when they all use multiple threads and MySQL?

[Scatman]

I will be able to talk to my friend on Saturday. He'll tell us exactly what needs to be done.

[Jabantiz]

While not related to threaded loading I ran into the MySQL has gone away issue while working on instances, mainly logging into an instance. It seems that having database_new running on 2 threads at the same time causes this and my server can't seem to recover from it. I added mutex to DatabaseNew and added writelock in DatabaseNew::Query and DatabaseNew::Select and have not got the "gone away" issue sense.

Haven't committed this as it may be a temp solution, waiting for scat to approve this for commit or get a proper solution.

[Jabantiz]

This code snuck on to Dev SVN with my instance commit. Will leave it in place as if any one uses instances the "MySQL has gone away" issue can show up with out this code. Will be easy to remove this code for a proper fix later.