Announcement

Collapse
No announcement yet.

DB error with USING clause

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

  • DB error with USING clause

    I got a db error with the Post Thanks Hack, but since I know that's not supported here (or at vb.org), I didn't post a help request here yet.

    I just tried to merge two users and got an error:
    MySQL Error : SELECT command denied to user 'UserName'@'999.999.999.999' for column 'userid' in table 'user'
    Error Number : 1143

    SELECT *
    FROM user AS user
    LEFT JOIN usertextfield AS usertextfield USING(userid)
    WHERE user.userid = 139;

    This is similar to my error with Post Thanks in that they both have the USING clause in it.

    My host is trying to find the problem, but I was wondering if there is a way to determine what caused the error?

    Thanks
    Cliff
    PathLabTalk
    Square Wheels Cycling

  • #2
    That looks like a MySQL permission problem. Make sure the MySQL user has full access to your forum database.

    Comment


    • #3
      I rechecked with my host and they confirmed that the user has permission.

      It looks like a MySQL bug. http://bugs.mysql.com/bug.php?id=36811
      It's not the USING clause as I suspected, it appears to be an issue with using SELECT and *.

      I am on MySQL 5.0.51a.
      Am I the only person who has reported this issue?
      Cliff
      PathLabTalk
      Square Wheels Cycling

      Comment


      • #4
        It is a permissions issue. The error states that the database user does not have permission to use the SELECT command in the database when associated with the userid field on the user table. You would need to grant that permission.
        Translations provided by Google.

        Wayne Luke
        The Rabid Badger - a vBulletin Cloud demonstration site.
        vBulletin 5 API

        Comment


        • #5
          If that were true, how does my site run? I'm sure there are plenty of SELECT queries running on all of the forum pages. I'm not trying to argue, just trying to learn.

          If I run this I get all the results expected:
          SELECT username
          FROM user AS user
          LEFT JOIN usertextfield AS usertextfield USING(userid)
          WHERE user.userid = 139;

          But when I switch back to the real query vb is trying to run, I get the permission error:
          SELECT *
          FROM user AS user
          LEFT JOIN usertextfield AS usertextfield USING(userid)
          WHERE user.userid = 139;

          It's the same db with the same db user, one time I get a permission error (while using *), one time it runs fine. It will also run fine if I select all the cols by listing them individually. So it supports the theory that this is a MySQL bug.
          Cliff
          PathLabTalk
          Square Wheels Cycling

          Comment


          • #6
            That bug does appear to be exactly what you are experiencing. I know this is the first time I've seen someone post about this (however, I don't read all the posts). Can you point that bug out to your host and ask them to upgrade your mysql?

            Please don't PM or VM me for support - I only help out in the threads.
            vBulletin Manual & vBulletin 4.0 Code Documentation (API)
            Want help modifying your vbulletin forum? Head on over to vbulletin.org
            If I post CSS and you don't know where it goes, throw it into the additional.css template.

            W3Schools <- awesome site for html/css help

            Comment


            • #7
              Originally posted by Lynne View Post
              That bug does appear to be exactly what you are experiencing. I know this is the first time I've seen someone post about this (however, I don't read all the posts). Can you point that bug out to your host and ask them to upgrade your mysql?
              I just asked to be upgraded, I'm keeping my fingers crossed.
              Cliff
              PathLabTalk
              Square Wheels Cycling

              Comment


              • #8
                As you posted in your ticket, this is a bug in MySQL -

                http://bugs.mysql.com/bug.php?id=36811

                If you switch the query from the USING clause to an ON clause it works. If you remove the "--skip-grant-tables" option while starting MySQL it works. You can log it as a bug for 3.8.5 as I don't think changing the JOIN clauses would hurt it but I can't guarantee it will be changed.
                Translations provided by Google.

                Wayne Luke
                The Rabid Badger - a vBulletin Cloud demonstration site.
                vBulletin 5 API

                Comment


                • #9
                  Thank you for the help Wayne, I posted the bug.
                  For now I'll try to hunt these queries down and edit them as you pointed out in the support ticket. I appreciate your help!
                  Cliff
                  PathLabTalk
                  Square Wheels Cycling

                  Comment

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