Search not indexing after upgrade from v5.5.0 to v5.5.2

Collapse
X
 
  • Time
  • Show
Clear All
new posts
  • Wayne Luke
    vBulletin Technical Support Lead
    • Aug 2000
    • 73979

    #16
    Can you also provide the output for this query?

    Code:
     
     SHOW VARIABLES LIKE 'character\_set\_%';
    Translations provided by Google.

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

    Comment

    • mark.hs
      Senior Member
      • Dec 2016
      • 197
      • 5.7.0

      #17
      Originally posted by Wayne Luke
      We think we know where the query is failing. We don't know why the query is failing or how to fix it immediately. The queries work on my implementation of MySQL but failed on a developer's implementation.

      In order to determine the why, we are going to need a backup copy of your database. This is so we can troubleshoot and the developers can add additional debugging tools as needed to determine the problem. If you can make a copy available for us, it would reduce the amount of time we need to invest in fixing the issue and we can provide a solution quicker.
      I will send you a link to the backup via PM
      Admin for hobbysquawk.com
      VB 5.74
      PHP 8.2
      Maria DB 10.6

      Comment


      • Wayne Luke
        Wayne Luke commented
        Editing a comment
        Received. Thanks.
    • mark.hs
      Senior Member
      • Dec 2016
      • 197
      • 5.7.0

      #18
      Originally posted by Wayne Luke
      Can you also provide the output for this query?

      Code:
      SHOW VARIABLES LIKE 'character\_set\_%';
      Here is the output:

      character_set_client utf8
      character_set_connection utf8
      character_set_database utf8
      character_set_filesystem binary
      character_set_results utf8
      character_set_server latin1
      character_set_system utf8
      Admin for hobbysquawk.com
      VB 5.74
      PHP 8.2
      Maria DB 10.6

      Comment


      • Wayne Luke
        Wayne Luke commented
        Editing a comment
        okay. this is pretty close to mine. I just have utf8mb4 where you have utf8.
    • Wayne Luke
      vBulletin Technical Support Lead
      • Aug 2000
      • 73979

      #19
      https://tracker.vbulletin.com/vbulle...sues/VBV-19391 - Will have someone look at your database as soon as possible.
      Translations provided by Google.

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

      Comment

      • glennrocksvb
        Former vBulletin Developer
        • Mar 2011
        • 4011
        • 5.7.X

        #20
        As a temporary solution while waiting for the official fix, why not just replace those curly quotes on the database with the corresponding straight ones?

        Flag Icon Postbit Insert GIPHY Impersonate User BETTER INITIALS AVATAR Better Name Card Quote Selected Text Bookmark Posts Post Footer Translate Stop Links in Posts +MORE!

        Comment

        • Wayne Luke
          vBulletin Technical Support Lead
          • Aug 2000
          • 73979

          #21
          Please update your config.php file to read:

          Code:
          $config['Mysqli']['charset'] = 'utf8mb4';
          Rebuild your search index.
          Translations provided by Google.

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

          Comment

          • mark.hs
            Senior Member
            • Dec 2016
            • 197
            • 5.7.0

            #22
            Originally posted by Glenn Vergara
            As a temporary solution while waiting for the official fix, why not just replace those curly quotes on the database with the corresponding straight ones?
            It's an interesting idea. I'm not sure if it would screw up anything else down the line. I'm assuming I can do this via Phpmyadmin with a sql search and replace function. What tables would I need to search and replace the curly quotes? I'm assuming:
            cache
            text
            words
            node

            Is that correct?
            Admin for hobbysquawk.com
            VB 5.74
            PHP 8.2
            Maria DB 10.6

            Comment


            • glennrocksvb
              glennrocksvb commented
              Editing a comment
              I don't know all the tables off the top of my head. To prevent the curly quotes from being saved in the posts in the future, you'd have to have a mod that does that. I have that mod but you won't need it once this issue is fixed.
          • mark.hs
            Senior Member
            • Dec 2016
            • 197
            • 5.7.0

            #23
            Originally posted by Wayne Luke
            Please update your config.php file to read:

            Code:
            $config['Mysqli']['charset'] = 'utf8mb4';
            Rebuild your search index.
            This fixed the issue.. Thank you Wayne
            Admin for hobbysquawk.com
            VB 5.74
            PHP 8.2
            Maria DB 10.6

            Comment

            • artlserver
              New Member
              • Aug 2019
              • 13
              • 4.2.x

              #24
              I also had the error, "invalid SQL," on building the search index. It occurs with both the command line and web method.

              The fix

              $config['Mysqli']['charset'] = 'utf8mb4'; did not work.

              I ran this query below:

              SHOW VARIABLES LIKE 'character\_set\_%';

              wit this result:

              Variable_name Value

              character_set_client utf8mb4
              character_set_connection utf8mb4
              character_set_database utf8mb4
              character_set_filesystem binary
              character_set_results utf8mb4
              character_set_server utf8mb4
              character_set_system utf8

              I copied the failing SQL query, and it worked in phpMyAdmin on the same server, the same database!

              This was an upgrade from 4.5.2 to 5.5.4, but the failing query was the same as in this post - different words of course, but it started out as below:

              Error Invalid SQL: SELECT * FROM words WHERE `word` IN ('woodshed','reported','posts', ...

              Server info is below. Are there any suggestions on getting this to work?

              Click image for larger version  Name:	server48570.png Views:	0 Size:	56.5 KB ID:	4422547
              Last edited by artlserver; Mon 23 Sep '19, 1:18pm.

              Comment

              • Wayne Luke
                vBulletin Technical Support Lead
                • Aug 2000
                • 73979

                #25
                Originally posted by artlserver
                Are there any suggestions on getting this to work?
                You will need to open your own topic to receive support. Piggybacking off of someone else's issue will rarely end satisfactorily even if you think you have the exact same problem. We will need to see the entire query and error message returned by MySQL in your topic.

                Translations provided by Google.

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

                Comment

                • artlserver
                  New Member
                  • Aug 2019
                  • 13
                  • 4.2.x

                  #26
                  Originally posted by Wayne Luke

                  You will need to open your own topic to receive support. Piggybacking off of someone else's issue will rarely end satisfactorily even if you think you have the exact same problem. We will need to see the entire query and error message returned by MySQL in your topic.
                  Ok, I opened a topic at https://forum.vbulletin.com/forum/vb...-2-5-to-v5-5-4

                  I added the entire query. Let me know if more info is needed.

                  Thanks for looking into this.

                  Comment

                  Related Topics

                  Collapse

                  Working...