MySQL error on subscription functions.

Collapse
X
 
  • Time
  • Show
Clear All
new posts
  • plarkin
    New Member
    • Feb 2004
    • 24
    • 3.5.x

    MySQL error on subscription functions.

    I have been having an issue with MySQL errors during various subscription functions. The Subscriptions scheduled task no longer runs and I am also unable to deactivate/modify any existing subscription.

    The following is the error I get running the Subscriptions task.

    Code:
     
    Database error in vBulletin 3.5.2:
    Invalid SQL:
         DELETE FROM access
         WHERE forumid IN (0,) AND
          userid = 4160;
    MySQL Error  : You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ') AND
          userid = 4160' at line 2
    Error Number : 1064
    Date         : Friday, December 9th 2005 @ 06:56:14 PM
    Script       : [URL="http://www.tacticalgamer.com/admincp/cronadmin.php?do=runcron&cronid=7"]http://www.tacticalgamer.com/admincp/cronadmin.php?do=runcron&cronid=7[/URL]
    Referrer     : [URL="http://www.tacticalgamer.com/admincp/"]http://www.tacticalgamer.com/admincp/[/URL]
    IP Address   : <removed>
    Username     : <removed>
    Classname    : vb_database
    I have disabled ALL installed products and this error remains.

    I tried executing the MySQL code directly in the MySQL engine and also get an error message about the code being invalid:

    DELETE FROM access
    WHERE forumid IN (0,) AND
    userid = 4160;

    Any ideas/help desperately needed.
  • plarkin
    New Member
    • Feb 2004
    • 24
    • 3.5.x

    #2
    Resolution:

    I use the Subscription system and provide access to various forums based on a user being part of my Subscribed usergroup. I do not use any sort of access masks on the forums directly allowing access to a forum as part of the subscription. (This is done by checking off the box next to the forum names when managing your subscriptions).

    After adding the subscription-based forum access masks, vBulletin now was able to correctly obtain the $key value it needed when running the /includes/cron/subscriptions.php task and everything started working properly.

    I believe this is a vBulletin bug with 3.5.2 that only affects users who use usergroup based permissions with their subscriptions rather than access-mask based permissions.

    Comment

    • Steve Machol
      Former Customer Support Manager
      • Jul 2000
      • 154488

      #3
      I responded to your support ticket.
      Steve Machol, former vBulletin Customer Support Manager (and NOT retired!)
      Change CKEditor Colors to Match Style (for 4.1.4 and above)

      Steve Machol Photography


      Mankind is the only creature smart enough to know its own history, and dumb enough to ignore it.


      Comment

      • plarkin
        New Member
        • Feb 2004
        • 24
        • 3.5.x

        #4
        Originally posted by Steve Machol
        I responded to your support ticket.
        I got the response. I did a clean install of vBulletin and re-created the same subscription error by using usergroup-based permissions instead of access-mask based permissions with no third party plugins or modifications whatsoever.

        In the end, I've got it all working, but I'm forced to use the access-mask permissions for the subscriptions rather than relying on the usergroup permissions.

        Comment

        • wdwmagic
          New Member
          • May 2001
          • 23
          • 3.0.1

          #5
          I am getting the same error, using usergroup permissions. This problem hasonly appeared since 3.5.2.

          Comment

          • Andreas
            Senior Member
            • Feb 2004
            • 2323

            #6
            This seems to be a bug.

            In class_paid_subscription.php
            SEARCH
            PHP Code:
            $subscription_forums explode(','$sub['forums']);
             
            if (
            is_array($subscription_forums) AND !empty($subscription_forums)) 
            REPLACE
            PHP Code:
            $subscription_forums preg_split('#,#'$sub['forums'], -1PREG_SPLIT_NO_EMPTY);

            if (
            is_array($subscription_forums) AND !empty($subscription_forums)) 
            Last edited by Andreas; Mon 12 Dec '05, 9:44am.

            Comment

            • plarkin
              New Member
              • Feb 2004
              • 24
              • 3.5.x

              #7
              Originally posted by Andreas
              This seems to be a bug.
              I knew I wasn't crazy! I just applied your codefix, should I be able to safely turn off the access-mask based permissions and go back to usergroup based permissions without a problem now?

              Cheers,

              Comment

              • plarkin
                New Member
                • Feb 2004
                • 24
                • 3.5.x

                #8
                I think there might still be a problem.

                This fix works fine for normal monthly recurring subscriptions. But I also have non-recurring yearly subscriptions that produce a slightly different error when trying to deactivate or when the Subscription task is run.

                Code:
                Database error in vBulletin 3.5.2:
                Invalid SQL:
                     DELETE FROM access
                     WHERE forumid IN (0,N;) AND
                      userid = 6207;
                MySQL Error  : You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ';) AND
                      userid = 6207' at line 2
                Error Number : 1064
                The only visible difference I see in the error is the "WHERE forumid IN (0,N" rather than "(0,)".

                For the purposes of verifying that it wasn't a result of the fix that was just posted, I reverted to the original 3.5.2 code and got the same error.

                Comment

                • coloradok5
                  Senior Member
                  • Sep 2004
                  • 196
                  • 3.0.3

                  #9
                  Originally posted by Andreas
                  This seems to be a bug.

                  In class_paid_subscription.php
                  SEARCH
                  PHP Code:
                  if (is_array($subscription_forums) AND !empty($subscription_forums)) 
                  REPLACE
                  PHP Code:
                  if ($subscription_forums !== false
                  That code is in there twice, replace both?
                  Do not click this link... you may save hundreds on your auto insurance.

                  Comment

                  • plarkin
                    New Member
                    • Feb 2004
                    • 24
                    • 3.5.x

                    #10
                    Originally posted by coloradok5
                    That code is in there twice, replace both?
                    That's a good question. I only replaced the first occurance, it might be part of the problem.

                    Comment

                    • plarkin
                      New Member
                      • Feb 2004
                      • 24
                      • 3.5.x

                      #11
                      I have a staging site up and I just tried replacing BOTH occurances of that line and it did indeed correct the problem with my non-recurring subscriptions.

                      Use that for what it's worth, but it did fix my problem.

                      Comment

                      • coloradok5
                        Senior Member
                        • Sep 2004
                        • 196
                        • 3.0.3

                        #12
                        It didn't do anything for me at all, but I am still using usergroups to control access as I have for the last year and do not want the work around of changing to access masks, THIS WORKED IN 3.0.7
                        Do not click this link... you may save hundreds on your auto insurance.

                        Comment

                        • wajones
                          Senior Member
                          • Jul 2000
                          • 458

                          #13
                          This did not work for me?

                          Comment

                          • plarkin
                            New Member
                            • Feb 2004
                            • 24
                            • 3.5.x

                            #14
                            Originally posted by coloradok5
                            It didn't do anything for me at all, but I am still using usergroups to control access as I have for the last year and do not want the work around of changing to access masks, THIS WORKED IN 3.0.7
                            You don't really need to do much. In fact, you can still use the usergroup based permissions for your "real" access control. Just go into your Subscription manager and check off a box next to one of the subscription-based forums. As long as one of them is checked, everything will start working again normally.

                            It should take about 30 seconds to take care of. I do admit, it is a workaround, but you've gotta do what you've gotta do until they fix it.

                            Comment

                            • Freddie Bingham
                              Former vBulletin Developer
                              • May 2000
                              • 14057
                              • 1.1.x

                              #15
                              An official patch for this problem will be released shortly.

                              Comment

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