• Advertisement
Sign in to follow this  

LibMySQL: Lost connection to MySQL server during query

This topic is 4730 days old which is more than the 365 day threshold we allow for new replies. Please post a new topic.

If you intended to correct an error in the post then please contact us.

Recommended Posts

Hey all, I have a question for anyone with any libMySQL experience. I finally got a working version of my MUD running overnight last night, and I decided to have a look through the log this afternoon, and I found this:
Quote:
[14/03/2005 17:02:53] WARNING: Failed to insert user to the database! Error: Lost connection to MySQL server during query
That caused the user to get disconnected, and they reconnected a few seconds later, and managed to register fine (I.e. the insert worked fine the second time). Does anyone know what cuased this? Was it just a timeout? It looks like it was about 14 hours since the database was last accessed before that call, so that seems likely. Secondly, does anyone know how to prevent this (Other than just trying again if it fails)? Here's the relevant source code:
bool CDatabase::InsertUser(CProfile* pUser)
{
char szBuff[4096];

   // Get user ID //
   pUser->m_dwUserID = GetNextUserID();
   if(!pUser->m_dwUserID)
      return false;

   // Allocate user for internal list //
   CProfile* pInternalUser = new CProfile;
   if(!pInternalUser)
   {
      Thread_SetError("Out of Memory!");
      return false;
   }
   memcpy(pInternalUser,pUser,sizeof(CProfile));

   // Compose query //
   sprintf(szBuff,"INSERT INTO `users` (`userid`, `username`, "
      "`password`, `sex`, `class`, `showname`, `desc`, `email`, "
      "`website`, `hp`, `max_hp`, `mp`, `max_mp`, `stamina`, `max_stamina`, "
      "`firstip`, `firstlogin`, `lastip`, `lastlogin`, `lastloginlen`, "
      "`onlinetime`, `cash`, `balance`, `roomid`, `status`, `statustext`, "
      "`respawntime`, `permissions`, `kickcount`, `bancount`, `deathcount`, "
      "`logincount`, `experience`, `next_xp`, `level`, `avatarurl`, "
      "`avatartime`, `idletimeout`, `floodreduce`, `floodmax`, `maxbuffer`, "
      "`strength`, `intelligence`, `accuracy`, `avoid`, `melee`, `archery`, "
      "`cast`, `pick_lock`, `spot_trap`, `disarm_trap` "
      ") VALUES "
      "('%lu', '%s', '%s', '%lu', '%lu','%s', '', '', '', '%lu', '%lu', '%lu', '%lu', "
      "'%lu', '%lu', '%lu', '%lu', '%lu','%lu', '0', '0', '0', '0', '0', '0', '', '0', "
      "'0', '0', '0','0', '1', '%lu', '%lu', '1', '', '0', '%lu', '%lu', '%lu', '%lu', "
      "'%lu', '%lu', '%lu', '%lu', '%lu', '%lu', '%lu', '%lu', '%lu', '%lu');",
      pUser->m_dwUserID,pUser->m_szUsername,pUser->m_szPassword,(int)pUser->m_bySex,
      pUser->m_dwClass,pUser->m_szShowname,pUser->m_dwHP,pUser->m_dwMaxHP,pUser->m_dwMP,pUser->m_dwMaxMP,
      pUser->m_dwStamina,pUser->m_dwMaxStamina,pUser->m_dwFirstIP,pUser->m_dwFirstLogin,
      pUser->m_dwFirstIP,pUser->m_dwFirstLogin,pUser->m_dwExperience,pUser->m_dwNextExperience,
      pUser->m_dwIdleTimeout,pUser->m_dwFloodReduce,pUser->m_dwFloodMax,pUser->m_dwMaxBufferLen,
      pUser->m_dwStrength,pUser->m_dwIntelligence,pUser->m_dwAccuracy,pUser->m_dwAvoid,
      pUser->m_dwMelee,pUser->m_dwArchery,pUser->m_dwCast,pUser->m_dwPickLock,pUser->m_dwSpotTrap,
      pUser->m_dwDisarmTrap);

   // Do the query //
   if(mysql_query(m_pSQL,szBuff) != 0)
   {
      Thread_SetError(mysql_error(m_pSQL));
      return false;
   }

   // Add to internal user list and return //
   EnterCriticalSection(&m_cs);
   m_mapUsers[pInternalUser->m_szUsername] = pInternalUser;
   LeaveCriticalSection(&m_cs);
   return true;
}

You can probably just ignore the vast INSERT statement :P And before anyone says anything, Yes, I know I should be using prepared statements. But the version of MySQL that I have doesn't support them, and I can't get PHP working with the newer version.

Share this post


Link to post
Share on other sites
Advertisement
Sounds like a connection timeout issue. I had it happen with MySQL when there was no activity for a long time. The way to solve this is to have activity. Find out what your connection timeout is set, then query the server every once in a while with some trivial request (a heartbeat of sorts).

With a MUD that is relatively unpopulated you may have no activity for a while so the best thing that I have added to my MUD is a dynamic connection pool that establishes a connection to the DB if there are none during login process and keeps the connection floating around for 30 minutes (reusing them as needed), if inactive for over 30 mins it closes the connection.

Share this post


Link to post
Share on other sites
im not sure about LibMySQL, since i use MySQL++, however there should be some sort of ping() function. i call it once an hour just to be safe, but thats only because i dont know what the timeout value really is. if you find out, let me know [grin].

Share this post


Link to post
Share on other sites
Sign in to follow this  

  • Advertisement