Announcement

Collapse
No announcement yet.

Search function only patially delivering even after search indexing and updating to 5.5.2

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

  • Search function only patially delivering even after search indexing and updating to 5.5.2

    I am having the same problem as described in this thread: https://www.vbulletin.com/forum/foru...lly-delivering
    I was using vbulletin 3.8 and was upgrading to 5.5.1, a few days later users were reporting that search results were missing. Then i searched in this forum for solutions and found this thread. Version 5.5.2 was already released, so i was updating to that version. I was deleting the search index and started reindexing. But the search results are still not right. I don't know what else to do.
    I'm using the standard DB search. No Addons installed, default style with minimal adjustments, Deutsch (Du) language used,

    vBulletin Version: 5.5.2
    PHP Version: 7.2.17-0ubuntu0.18.04.1
    MySQL Version: 5.7.25-0ubuntu0.18.04.2

  • #2
    More information is required. What specific terms are not working? Is it a specific character like an umlaut? What size is your database? Is the search timing out?

    Comment


    • #3
      Searching for "china" in old forum returns 6 results ranging from last post 07-18-2006 - 08-11-2017.
      Searching for "china" in new forum returns 1 result from last post 03-25-2009.
      Database size: 605 MB
      Search is not timing out, the result is there in about 0,0025 seconds.

      In the new forum i created a new thread with china in the text and this will return in the search results but search words with an umlaut like höher are not working.

      Comment


      • #4
        but search words with an umlaut like höher are not working.
        This is due to how the language is stored in the database. Older versions of vBulletin do some weird things to UTF-8 characters to work around a lack of support for UTF-8. We've been working to reverse this so we can get all databases working in a UTF-8 format but some sites such as your own get into a weird middle state.

        What is the character set that your language (within vBulletin) is currently using? It should be UTF-8.
        Translations provided by Google.

        Wayne Luke
        The Rabid Badger - a vBulletin Cloud customization and demonstration site.
        vBulletin 5 Documentation - Updated every Friday. Report issues here.
        vBulletin 5 API - Full / Mobile
        I am not currently available for vB Messenger Chats.

        Comment


        • #5
          ISO88591, i changed it to UTF-8 but then the "umlaute" are not correctly displayed.

          Comment


          • #6
            That is because ISO-8859-1 is only a subset of UTF-8 and characters like the umlaut will be stored as HTML entities not actual characters.

            With your vBulletin database selected as the active database, what are the results of these two queries?

            Code:
            show variables like 'character_set%'
            And:
            Code:
            show variables like 'collation_%'
            You can also look these values up manually under Maintenance -> Diagnostics -> Server Information. Make sure the drop down is set to MySQL Variables and then submit the form. In the list of resulting variables, look for the results of the following variables:

            "character_set_client"
            "character_set_connection"
            "character_set_database"
            "character_set_filesystem"
            "character_set_results"
            "character_set_server"
            "character_set_system"
            "character_sets_dir"

            "collation_connection"
            "collation_database"
            "collation_server"
            Translations provided by Google.

            Wayne Luke
            The Rabid Badger - a vBulletin Cloud customization and demonstration site.
            vBulletin 5 Documentation - Updated every Friday. Report issues here.
            vBulletin 5 API - Full / Mobile
            I am not currently available for vB Messenger Chats.

            Comment


            • #7
              Here are the results:
              collation_connection latin1_swedish_ci
              collation_database latin1_swedish_ci
              collation_server latin1_swedish_ci





              character_set_client latin1
              character_set_connection latin1
              character_set_database latin1
              character_set_filesystem binary
              character_set_results latin1
              character_set_server latin1
              character_set_system utf8
              character_sets_dir /usr/share/mysql/charsets/
              Last edited by schneider_s; Thu 6th Jun '19, 3:09am.

              Comment


              • #8
                You won't be able to index UTF-8 characters using those settings. This causes a problem because most keyboards and fonts use UTF-8 characters for items like umlauts even when there is an ASCII equivalent. This is more prominent on mobile devices (tablets, phones, two-in-ones) and alternative OSes (i.e. ChromeOS and Windows on ARM).

                Ideally, you would be using utf8mb4_general_ci or utf8mb4_unicode_ci for the collation. utf8mb4_unicode_ci can possibly give you better alphabetic sorting. The character set of the database and fields should be utf8mb4.

                You can see an outline on how to change these here: https://coderwall.com/p/gjyuwg/mysql...t-garbled-data

                I have attached some beta scripts that have been successful in converting some vBulletin Cloud databases to utf8mb4. We're still having problems with some Middle Eastern and Asian characters. There is a readme.md file in the file that explains the process.
                Attached Files
                Translations provided by Google.

                Wayne Luke
                The Rabid Badger - a vBulletin Cloud customization and demonstration site.
                vBulletin 5 Documentation - Updated every Friday. Report issues here.
                vBulletin 5 API - Full / Mobile
                I am not currently available for vB Messenger Chats.

                Comment


                • #9
                  First i configured a test system so i don't destroy the running forum. I went through the instructions from the readme.md file and the following results came up:

                  php myisamfix.phar -dofix :
                  PHP Fatal error: Uncaught PDOException: SQLSTATE[22007]: Invalid datetime format: 1292 Incorrect date value: '0000-00-00' for column 'birthday_search' at row 1 in phar:///var/www/html/forums/myisamfix.phar/myisamfix.php:42
                  Stack trace:
                  #0 phar:///var/www/html/forums/myisamfix.phar/myisamfix.php(42): PDO->query('ALTER TABLE use...')
                  #1 /var/www/html/forums/myisamfix.phar(4): require_once('phar:///var/www...')
                  #2 {main}
                  thrown in phar:///var/www/html/forums/myisamfix.phar/myisamfix.php on line 42


                  php utf8tablefix.phar -dofix
                  DONE
                  updating collation for user ...PHP Warning: count(): Parameter must be an array or an object that implements Countable in phar:///var/www/html/forums/utf8tablefix.phar/includes/dbtools.php on line 175

                  PHP Fatal error: Uncaught PDOException: SQLSTATE[22007]: Invalid datetime format: 1292 Incorrect date value: '0000-00-00' for column 'birthday_search' at row 1 in phar:///var/www/html/forums/utf8tablefix.phar/includes/dbtools.php:195
                  Stack trace:
                  #0 phar:///var/www/html/forums/utf8tablefix.phar/includes/dbtools.php(195): PDO->exec('ALTER TABLE use...')
                  #1 phar:///var/www/html/forums/utf8tablefix.phar/includes/dbtools.php(177): vB_Db_Mysql_Tools->setTableCollation('user', 'utf8mb4', 'utf8mb4_general...')
                  #2 phar:///var/www/html/forums/utf8tablefix.phar/utf8tablefix.php(35): vB_Db_Mysql_Tools->setCharset('user', 'utf8mb4', 'utf8mb4_general...', 'utf8mb4_bin')
                  #3 /var/www/html/forums/utf8tablefix.phar(4): require_once('phar:///var/www...')
                  #4 {main}
                  thrown in phar:///var/www/html/forums/utf8tablefix.phar/includes/dbtools.php on line 195


                  This PHP warning on line 175 came up for every tablename so i only posted the last line.

                  serializefix.phar => everything is good

                  $config['Mysqli']['charset'] = 'utf8'; => is commented out:

                  Click image for larger version

Name:	2019-06-12 11_38_02-Dokument1 - Microsoft Word.png
Views:	88
Size:	52.8 KB
ID:	4416696
                  Forum, Language Manager, HTML Character Set to UTF-8, Umlauts are not displayed correctly:
                  Click image for larger version

Name:	2019-06-12 11_38_08-Dokument1 - Microsoft Word.png
Views:	71
Size:	26.0 KB
ID:	4416697
                  Reindexing Searchindex
                  Searching for „china“ returns 8 results, 2 more that the old forum ;-), but i’m still not able to search for words with umlauts.

                  $config['Mysqli']['charset'] = 'utf8'; => no comment:
                  Click image for larger version

Name:	2019-06-12 11_38_14-Dokument1 - Microsoft Word.png
Views:	70
Size:	55.4 KB
ID:	4416698

                  The forum and the admincp isn’t working properly with this setting:
                  Click image for larger version

Name:	2019-06-12 11_38_19-Dokument1 - Microsoft Word.png
Views:	70
Size:	36.5 KB
ID:	4416699
                  The logo is not shown and no forums or channels are shown. Search isn't working either.
                  The admincp is looking like that:
                  Click image for larger version

Name:	2019-06-12 11_38_31-Dokument1 - Microsoft Word.png
Views:	72
Size:	46.2 KB
ID:	4416700

                  Comment


                  • #10
                    The issue with dates is actually a strict mode requirement of MySQL but you can tell MySQL to ignore it.

                    Code:
                     SET SQL_MODE='TRADITIONAL,ALLOW_INVALID_DATES';
                    I have an open bug report to change these defaults. Otherwise, you would need to update those fields so the default date is 1900-01-01. Then update the fields in those records to match if they are 0000-00-00.

                    Will look into the other issues.
                    Translations provided by Google.

                    Wayne Luke
                    The Rabid Badger - a vBulletin Cloud customization and demonstration site.
                    vBulletin 5 Documentation - Updated every Friday. Report issues here.
                    vBulletin 5 API - Full / Mobile
                    I am not currently available for vB Messenger Chats.

                    Comment


                    • #11
                      Were you able to look after the other issues?

                      Comment


                      • #12
                        Reindexing isn't going to work unless you convert the database to UTF-8. Changing the value in the core/includes/config.php can actually break data on your site. Converting your database to UTF-8 is a complicated task.

                        Please look at the attached ZIP file. The instructions are in the readme.md file.
                        Attached Files
                        Translations provided by Google.

                        Wayne Luke
                        The Rabid Badger - a vBulletin Cloud customization and demonstration site.
                        vBulletin 5 Documentation - Updated every Friday. Report issues here.
                        vBulletin 5 API - Full / Mobile
                        I am not currently available for vB Messenger Chats.

                        Comment


                        • #13
                          OK, i did this:
                          Code:
                           
                           SET SQL_MODE='TRADITIONAL,ALLOW_INVALID_DATES';
                          Then i restored the database in the original state and did all the steps as described in the readme.md from utf8_db_tools.zip.
                          php myisamfix.phar -dofix shows no errors.

                          php utf8tablefix.phar -dofix brings up the following error for every table:
                          Code:
                          updating collation for ad ...PHP Warning:  count(): Parameter must be an array or an object that implements Countable in phar:///var/www/html/forums/utf8tablefix.phar/includes/dbtools.php on line 175
                           DONE
                          Is this a problem or can it be ignored?

                          serializefix.phar => everything is good

                          $config['Mysqli']['charset'] = 'utf8'; => is commented out by default.
                          Click image for larger version

Name:	2019-06-12 11_38_02-Dokument1 - Microsoft Word.png
Views:	40
Size:	52.8 KB
ID:	4417609

                          Forum, Language Manager, HTML Character Set to UTF-8, Umlauts are not displayed correctly.
                          Click image for larger version

Name:	2019-06-12 11_38_08-Dokument1 - Microsoft Word.png
Views:	32
Size:	26.0 KB
ID:	4417610
                          Reindexing Searchindex
                          Searching for „china“ returns 8 results, 2 more that the old forum ;-), but i’m still not able to search for words with umlauts.

                          $config['Mysqli']['charset'] = 'utf8'; => no comment:
                          Click image for larger version

Name:	2019-06-12 11_38_14-Dokument1 - Microsoft Word.png
Views:	33
Size:	55.4 KB
ID:	4417612

                          The forum and the admincp isn’t working properly with this setting:
                          Click image for larger version

Name:	2019-06-12 11_38_19-Dokument1 - Microsoft Word.png
Views:	32
Size:	36.5 KB
ID:	4417613

                          The logo is not shown and no forums or channels are shown. Search isn't working either.
                          The admincp is looking like that:
                          Click image for larger version

Name:	2019-06-12 11_38_31-Dokument1 - Microsoft Word.png
Views:	32
Size:	46.2 KB
ID:	4417614

                          As far as i understood the database was converted to UTF-8 but why are the umlauts not showing correctly and why is the forum behaving weird when i set utf-8 in the config.php. I'm not a professional when i comes to mysql und converting databases, so i'm relying on your help.
                          Attached Files

                          Comment


                          • #14
                            Uncomment the line in the config.php. This should not be changed unless the upgrade.php script tells you to change it. It has no effect on how the browser display characters.

                            Change your language's HTML character set in the AdminCP to UTF-8. You may need to also specify a UTF-8 Locale for German in the same Language Settings page.

                            You will have to rebuild your search after converting.
                            Last edited by Wayne Luke; Fri 28th Jun '19, 9:21am.
                            Translations provided by Google.

                            Wayne Luke
                            The Rabid Badger - a vBulletin Cloud customization and demonstration site.
                            vBulletin 5 Documentation - Updated every Friday. Report issues here.
                            vBulletin 5 API - Full / Mobile
                            I am not currently available for vB Messenger Chats.

                            Comment


                            • #15
                              When i uncomment UTF-8 in the config.php i cannot use the admincp. You can see what the admincp looks like in my previous post. I didn't change the line in the config.php, it was commented by default.
                              HTML charset is UTF-8 and the UTF-8 locale is de_DE.
                              After converting i rebuilt the search index.

                              Comment

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