Announcement

Collapse
No announcement yet.

Database Error

Collapse
X
 
  • Filter
  • Time
  • Show
Clear All
new posts

  • Database Error

    Every day when I check my forums during the late night or early morning, it's always down because of a database error. Here is the error I receive in my e-mail:

    Database error in vBulletin:

    Invalid SQL:
    SELECT title, data
    FROM datastore
    WHERE title IN ('options', 'cron', 'forumcache', 'usergroupcache', 'stylecache')

    mysql error: User 'username' has exceeded the 'max_questions' resource (current value: 50000)

    mysql error number: 1226

    I've contacted my host (iPowerWeb) and they said they will not increase the 'max_questions' current value. Is there anything I can do to stop receiving this error? The only thing I can do get my forums back online is to add a new user to the database, then I delete that user and do the same thing the next day.

  • #2
    A similar user needed to avoid this problem. What he did was create three or four users that all had the same password and assigned them to the vBulletin database. Then he made this modification to includes/config.php:

    Open includes/config.php and find:

    PHP Code:
    $dbusername '<username>'
    Before that, add:

    PHP Code:
    $users[] = 'user1';
    $users[] = 'user2';
    $users[] = 'user3';
    $users[] = 'user4'
    ... replacing userX with the name of the users you added to the database.

    Then change the line:

    PHP Code:
    $dbusername '<current user>'
    To:

    PHP Code:
    $dbusername $usersrand(0, (count($users) - 1)) ]; 
    ... this modification will randomly select a user from the list of users that you gave it; it will then connect with alternating users, which should limit this problem.
    Bugdar: PHP bug tracking software that is beautiful, fast, and robust.

    Comment


    • #3
      Thanks for responding daemon! I followed your instructions. I'll let you know if it works in a few days. I hope I can report good news.

      Comment


      • #4
        Originally posted by daemon
        A similar user needed to avoid this problem. What he did was create three or four users that all had the same password and assigned them to the vBulletin database. Then he made this modification to includes/config.php:

        Open includes/config.php and find:

        PHP Code:
        $dbusername '<username>'
        Before that, add:

        PHP Code:
        $users[] = 'user1';
        $users[] = 'user2';
        $users[] = 'user3';
        $users[] = 'user4'
        ... replacing userX with the name of the users you added to the database.

        Then change the line:

        PHP Code:
        $dbusername '<current user>'
        To:

        PHP Code:
        $dbusername $usersrand(0, (count($users) - 1)) ]; 
        ... this modification will randomly select a user from the list of users that you gave it; it will then connect with alternating users, which should limit this problem.
        neat trick there
        :: Always Back Up Forum Database + Attachments BEFORE upgrading !
        :: Nginx SPDY SSL - World Flags Demo [video results]
        :: vBulletin hacked forums: Clean Up Guide for VPS/Dedicated hosting users [ vbulletin.com blog summary ]

        Comment


        • #5
          Originally posted by daemon
          A similar user needed to avoid this problem. What he did was create three or four users that all had the same password and assigned them to the vBulletin database. Then he made this modification to includes/config.php
          It'll probably be a really silly question... but how can I create/add users to the database?

          Comment


          • #6
            mysql.com documentation has all you need http://dev.mysql.com/doc/mysql/en/us...anagement.html

            http://dev.mysql.com/doc/mysql/en/adding-users.html
            :: Always Back Up Forum Database + Attachments BEFORE upgrading !
            :: Nginx SPDY SSL - World Flags Demo [video results]
            :: vBulletin hacked forums: Clean Up Guide for VPS/Dedicated hosting users [ vbulletin.com blog summary ]

            Comment


            • #7
              Hello, I was hoping to implement this fix to my forum since I am having the same problem. It seems though since this post was made the config code has changed. Could anyone please help me by explaining this using the present config file? Thanks in advance!

              Comment


              • #8
                Sure, everything remains the same for vB 3.5 and 3.6 except the last instruction regarding replacing that one line. Instead, replace

                PHP Code:
                $config['Database']['dbname'] = '<current user>'
                With:

                PHP Code:
                $config['Database']['dbname'] = $usersrand(0, (count($users) - 1)) ]; 
                Bugdar: PHP bug tracking software that is beautiful, fast, and robust.

                Comment


                • #9
                  Originally posted by daemon View Post
                  Sure, everything remains the same for vB 3.5 and 3.6 except the last instruction regarding replacing that one line. Instead, replace

                  PHP Code:
                  $config['Database']['dbname'] = '<current user>'
                  With:

                  PHP Code:
                  $config['Database']['dbname'] = $usersrand(0, (count($users) - 1)) ]; 
                  If it says "dbname" why would you list the database users here? I'm confused on this part of the changes.

                  Comment


                  • #10
                    Config.php Request

                    I could not find the dbuser area daemon's post indicates in my config.php file. This is probably because VB has been updated since his post.

                    I'm running VB 3.6.8 Patch Level 2. I also have VB Advanced installed. Below is what my config.php looked like when I started with just one db user:


                    // ****** MASTER DATABASE USERNAME & PASSWORD ******
                    // This is the username and password you use to access MySQL.
                    // These must be obtained through your webhost.

                    $config['MasterServer']['username'] = 'boomgami';
                    $config['MasterServer']['password'] = 'xxxxxxxxx';


                    I just updated it to the below and it seems to be working with the rotating users and no longer giving me DB Error 1226 every day. Note their is only one password line. I made all the users have the same password so I only needed one shared password entry in the config.php file for all the users. I then set the users up on the MySQL server to use the same password.


                    // ****** MASTER DATABASE USERNAME & PASSWORD ******
                    // This is the username and password you use to access MySQL.
                    // These must be obtained through your webhost.

                    //$config['MasterServer']['username'] = 'boomgami_root'; OLD LINE NO LONGER USED

                    $config['MasterServer']['username'] = $users[ rand(0, (count($users) - 1)) ]; //NEW LINE

                    $config['MasterServer']['password'] = 'password';
                    $users[] = 'boomgami_user1';
                    $users[] = 'boomgami_user2';
                    $users[] = 'boomgami_user3';
                    $users[] = 'boomgami_user4';
                    $users[] = 'boomgami_user5';
                    $users[] = 'boomgami_user6';
                    $users[] = 'boomgami_user7';
                    $users[] = 'boomgami_user8';
                    $users[] = 'boomgami_user9';
                    $users[] = 'boomgami_user10';

                    This has resolved the problem for me perfectly.

                    Mike Vail
                    BOOM! Gaming Inc.
                    www.boomgaming.net
                    Last edited by scso1502; Sat 19 Jan '08, 9:07am.

                    Comment

                    widgetinstance 262 (Related Topics) skipped due to lack of content & hide_module_if_empty option.
                    Working...
                    X