error database

Collapse
X
 
  • Time
  • Show
Clear All
new posts
  • domdom
    Member
    • Oct 2018
    • 98
    • 5.6.4

    error database

    Hello everybody,

    Since last night, I have been receiving error messages, whereas everything has been going well so far. I haven't changed anything to the settings in several months.
    I am under version 5.4.4.

    I have other topics with accented characters and I have no worries. This has only been happening in the links since last night.
    Obviously, this only happens with guests and not with registered users.

    Thank you for giving me a solution to solve this problem.

    Best Regards.

    ======================================================
    Database error in vBulletin 5.4.4: Invalid SQL: SELECT routenew.* FROM opiroutenew AS routenew WHERE routenew.prefix IN ('forum/eep/questions-g�n�rales/127-disparition-du-manipulateur-d-objet/favicon.ico','forum/eep/questions-g�n�rales/127-disparition-du-manipulateur-d-objet','forum/eep/questions-g�n�rales','forum/eep','forum') OR ishomeroute = 1 /**get_best_routes**/; MySQL Error : Illegal mix of collations for operation 'in' Error Number : 1271 Request Date : Sunday, December 2nd 2018 @ 04:33:18 PM Error Date : Sunday, December 2nd 2018 @ 04:33:18 PM Script : Referrer : IP Address : 84.99.159.245 Username : Invité Classname : vB_Database_MySQLi MySQL Version : Stack Trace: #0 vB_Database->getErrorData() called in [path]/vb/database.php on line 1188 #1 vB_Database->halt() called in [path]/vb/database/mysqli.php on line 201 #2 vB_Database_MySQLi->execute_query() called in [path]/vb/database.php on line 557 #3 vB_Database->query_read() called in [path]/vb/db/result.php on line 126 #4 vB_dB_Result->rewind() called in [path]/vb/db/result.php on line 63 #5 vB_dB_Result->__construct() called in [path]/vb/db/query/stored.php on line 104 #6 vB_dB_Query_Stored->execSQL() called in [path]/vb/db/assertor.php on line 301 #7 vB_dB_Assertor->assertQuery() called in [path]/vb/api/route.php on line 162 #8 vB_Api_Route->getRoute() called in [path]/vb/api/wrapper.php on line 199 #9 vB_Api_Wrapper->__call() called in /home/d456789/public_html/includes/api/interface/collapsed.php on line 101 #10 Api_Interface_Collapsed->callApi() called in /home/d456789/public_html/includes/vb5/frontend/routing.php on line 120 #11 vB5_Frontend_Routing->setRoutes() called in /home/d456789/public_html/includes/vb5/frontend/application.php on line 21 #12 vB5_Frontend_Application::init() called in /home/d456789/public_html/index.php on line 48 ================================================
  • Wayne Luke
    vBulletin Technical Support Lead
    • Aug 2000
    • 73976

    #2
    MySQL Error : Illegal mix of collations for operation 'in' Error Number
    You need to make sure that every table and every text/string field in your database has the same collation. The most likely cause of this is changing the default collation on the database without updating existing tables. When you run an upgrade, vBulletin will use the database default for any new tables or modifications to a table.
    Translations provided by Google.

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

    Comment

    • domdom
      Member
      • Oct 2018
      • 98
      • 5.6.4

      #3
      Hello,

      Thanks for the answer, but I don't know how to fix the database. I had to go back to version 5_4_4 of Vbulletin with a database backup made with this version. Version 5_4_5 froze all the buttons and I was unable to click on them.
      Last edited by domdom; Mon 3 Dec '18, 8:25pm.

      Comment

      • domdom
        Member
        • Oct 2018
        • 98
        • 5.6.4

        #4
        Hello,

        Can you tell me why I have a weird character (in the word December) in the widget on the right - Utilisateurs en ligne - ?
        when the home page of my site is displayed?
        my site : www.eep-france.net

        What is the solution to this?

        Thank you very much for your answer

        Regards

        Comment


        • Wayne Luke
          Wayne Luke commented
          Editing a comment
          It is because your site isn't using a UTF-8 character set. You may be able to simply change the Language's character set under Languages & Phrases -> Language Manager. However you might need to convert the database to a UTF-8 Character Set and Collation.
      • domdom
        Member
        • Oct 2018
        • 98
        • 5.6.4

        #5
        Hello,

        the configuration is well in utf-8 in vbulletin and the 206 tables of the database are also in : "DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;"

        And I still have these unwanted characters only for month display (user interface and administrator interface)

        I even enabled the parameter $config['Mysqli']['charset'] ='utf8'; in the config.php file and I still have the same problem.

        Thank you very much for your answer

        Regards

        Comment

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

          #6
          The character you're complaining about is a placeholder character when the character set doesn't have a proper character to show. In the vBulletin AdminCP, the character set must be UTF-8 (it is case sensitive). You may also need to specify a UTF-8 based locale for your language. Locales need to be configured on the server. You will need to discuss locale availability with your Server Administrator. The UTF-8 locale for French is fr_FR.UTF-8 or fr_CA.UTF-8 for French (Canada).

          Changing the communication protocol in the config.php file can mess up your language if you ran the site previous without this line uncommented.
          Translations provided by Google.

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

          Comment

          • domdom
            Member
            • Oct 2018
            • 98
            • 5.6.4

            #7
            Hi Luke,

            thank you for your answer, but the database server is well configured for the FR locale.

            In the vBulletin settings, I have UTF-8 which is declared correctly.
            In my sql backup, the 206 tables are declared as follows for encoding (here I took as an example the "sentence" table:

            CREATE TABLE `phrase` (
            `phraseid` int(10) UNSIGNED NOT NULL,
            `languageid` smallint(6) NOT NULL DEFAULT 0,
            `varname` varchar(191) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NOT NULL DEFAULT '',
            `fieldname` varchar(20) CHARACTER SET utf8mb4 NOT NULL DEFAULT '',
            `text` mediumtext CHARACTER SET utf8mb4 DEFAULT NULL,
            `product` varchar(25) CHARACTER SET utf8mb4 NOT NULL DEFAULT '',
            `username` varchar(100) CHARACTER SET utf8mb4 NOT NULL DEFAULT '',
            `dateline` int(10) UNSIGNED NOT NULL DEFAULT 0,
            `version` varchar(30) CHARACTER SET utf8mb4 NOT NULL DEFAULT ''
            ) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

            There are only the months of the year when I have this problem, all the other accented characters are displayed correctly!. In the back-end or front-end, as soon as the month of "Décembre", "Février", "Août" is displayed, I have this problem.

            In the "phrase" table, the keyword "Décembre" is displayed correctly in line 19342.

            Even if you change the collation, it doesn't change this problem. I don't know what to do about it anymore.

            Best regards.




            Comment

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

              #8
              The table structure has absolutely nothing to do with the LOCALE. The LOCALE has nothing to do with the DATABASE. It is a setting for how PHP displays your language. This is why is it is set within vBulletin's AdminCP in the Language Manager. All month strings within dates are DEFINED by the PHP LOCALE. Dates are not stored in the database as strings. They are predominantly stored as integers representing the number of seconds from Midnight January 1st, 1970.
              Translations provided by Google.

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

              Comment

              • domdom
                Member
                • Oct 2018
                • 98
                • 5.6.4

                #9
                Hi Luke,

                Here is a screenshot of the admin part in the language manager :


                Click image for larger version

Name:	Capture.PNG
Views:	152
Size:	34.4 KB
ID:	4403893

                Unless I'm mistaken, I don't see where I made a error.

                I no more know what to do to correct this problem... Thancks

                Regards.

                Comment

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

                  #10
                  fr-FR is not compatible with UTF-8... You need to use fr-FR.UTF-8 as I specified above.
                  Translations provided by Google.

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

                  Comment

                  • domdom
                    Member
                    • Oct 2018
                    • 98
                    • 5.6.4

                    #11
                    No, that I enter everything I want UTF-8, fr-FR, fr... etc... etc... nothing works. only the months are displayed incorrectly, if I change the encoding of my page with unicode, (firefox -> display -> text encoding) the months are displayed correctly but all other accented characters, are displayed more correctly. It's the other way around.

                    All I have to do now is pray...

                    Thank you for your answers.

                    Best Regards.

                    Domi

                    Comment

                    • domdom
                      Member
                      • Oct 2018
                      • 98
                      • 5.6.4

                      #12
                      Yes, okay, I entered fr_FR.UTF-8 directly into the locale, and everything works. Logical, I was looking for complicated when it was very simple.

                      Thank you for your patience.


                      Best Regards!

                      Domi

                      Comment

                      Related Topics

                      Collapse

                      Working...