Announcement

Collapse
No announcement yet.

after upgrade database error and members cannot log-in

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

  • after upgrade database error and members cannot log-in

    hi after we successfully upgraded from 4. to latest vb5 connect .auth login not working,

    database error is show like this

    Database error in vBulletin 5.5.6:

    Invalid SQL:
    SELECT `email`,`username`,`userid`,`token`,`scheme` FROM user
    WHERE `username` = ''
    LIMIT 1;

    MySQL Error : Illegal mix of collations (latin1_swedish_ci,IMPLICIT) and (utf8_general_ci,COERCIBLE) for operation '='
    Error Number : 1267
    Request Date : Monday, February 3rd 2020 @ 07:29:11 AM
    Error Date : Monday, February 3rd 2020 @ 07:29:11 AM
    Script :
    Referrer :
    IP Address : 110.1177266
    Username : Arnotech
    Classname : vB_Database_MySQLi
    MySQL Version :


    login message There has been a database error, and the current page cannot be displayed. Site staff have been notified.

    and even in adminpanel
    shows
    No permissions on node id 1


  • #2
    It is because your database is using multiple character sets and collations. When you use multiple collations, MySQL does not know how to sort the data used in JOIN statements. You would have to update your database to use the same collation throughout all tables and fields in your database. We have provided scripts to help with this transition. You can find the instructions for them here:

    https://forum.vbulletin.com/articles...database-tools

    It is recommended to change your database to the UTF8MB4 character set and an appropriate utfmb4 collation such as utf8mb4_general_ci.
    Translations provided by Google.

    Wayne Luke
    The Rabid Badger - a vBulletin Cloud demonstration site.
    vBulletin 5 API - Full / Mobile
    Vote for your favorite feature requests and the bugs you want to see fixed.

    Comment


    • #3
      ok ive done all steps but i not able to finish .where is this file SerializeFix

      i cant find it in folder

      Comment


      • #4
        Sorry.

        An alternate way to rebuild the serialized data is to use tools.php. That process is outlined here: https://forum.vbulletin.com/articles...sing-tools-php. You would run everything listed under Datastore Tools.

        I'll update the other article as soon as possible.
        Translations provided by Google.

        Wayne Luke
        The Rabid Badger - a vBulletin Cloud demonstration site.
        vBulletin 5 API - Full / Mobile
        Vote for your favorite feature requests and the bugs you want to see fixed.

        Comment


        • #5
          thanks Wayne Luke after all now login is ok my admin panel ok but all function not working

          forum,private message,contact us,blog, header etc, always database error


          Database error in vBulletin 5.5.6:

          Invalid SQL:

          SELECT COUNT(*) AS count
          FROM notification AS notification
          WHERE notification.recipient = 1
          AND notification.lastsenttime > notification.lastreadtime;

          MySQL Error : Unknown column 'notification.recipient' in 'where clause'
          Error Number : 1054
          Request Date : Monday, February 3rd 2020 @ 11:06:09 PM
          Error Date : Monday, February 3rd 2020 @ 11:06:09 PM
          Script : http://www.gsm/forum/chat/loadheaderdata
          Referrer : http://www.gsm/forum/blogs
          IP Address : 54.131.172
          Username : teech
          Classname : vB_Database_MySQLi
          MySQL Version : 5.7.23-23


          Stack Trace:
          #0 vB_Database->getErrorData() called in [path]/vb/database.php on line 1207
          #1 vB_Database->halt() called in [path]/vb/database/mysqli.php on line 249
          #2 vB_Database_MySQLi->execute_query() called in [path]/vb/database.php on line 576
          #3 vB_Database->query_read() called in [path]/vb/db/result.php on line 144
          #4 vB_dB_Result->rewind() called in [path]/vb/db/result.php on line 68
          #5 vB_dB_Result->__construct() called in [path]/packages/vbforum/db/mysql/querydefs.php on line 5586
          #6 vBForum_dB_MYSQL_QueryDefs->fetchNotificationCount() called in [path]/vb/db/query/method.php on line 175
          #7 vB_dB_Query_Method->execSQL() called in [path]/vb/db/assertor.php on line 301
          #8 vB_dB_Assertor->assertQuery() called in [path]/vb/db/assertor.php on line 625
          #9 vB_dB_Assertor->getRow() called in [path]/vb/library/notification.php on line 368
          #10 vB_Library_Notification->fetchNotificationCountForUser() called in [path]/vb/api/content/privatemessage.php on line 1502
          #11 vB_Api_Content_Privatemessage->getHeaderCounts() called in [path]/vb/api/wrapper.php on line 201
          #12 vB_Api_Wrapper->__call() called in /gs/public_html/forum/includes/api/interface/collapsed.php on line 101
          #13 Api_Interface_Collapsed->callApi() called in /gs/public_html/forum/includes/vb5/frontend/controller/chat.php on line 645
          #14 vB5_Frontend_Controller_Chat->actionLoadHeaderData() called in //gs/public_html/forum/index.php on line 74

          Click image for larger version  Name:	vb.jpg Views:	0 Size:	79.3 KB ID:	4433514

          Comment


          • #6
            That is a standard field in vBulletin 5 and should have been created during the upgrade. Did you have any hiccups or errors while running the upgrade from your previous version?

            Make sure your notification table looks like this:
            Code:
            CREATE TABLE IF NOT EXISTS `notification` (
            `notificationid` int(10) unsigned NOT NULL AUTO_INCREMENT,
            `recipient` int(10) unsigned NOT NULL,
            `sender` int(10) unsigned DEFAULT NULL,
            `lookupid` varchar(150) DEFAULT NULL,
            `lookupid_hashed` char(32) DEFAULT NULL,
            `sentbynodeid` int(10) unsigned DEFAULT NULL,
            `customdata` mediumtext,
            `typeid` smallint(5) unsigned NOT NULL,
            `lastsenttime` int(10) unsigned NOT NULL DEFAULT '0',
            `lastreadtime` int(10) unsigned NOT NULL DEFAULT '0',
            PRIMARY KEY (`notificationid`),
            UNIQUE KEY `guid` (`recipient`,`lookupid_hashed`),
            KEY `recipient` (`recipient`),
            KEY `lookupid_hashed` (`lookupid_hashed`),
            KEY `lastsenttime` (`lastsenttime`),
            KEY `lastreadtime` (`lastreadtime`)
            ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;
            Your character set and collation may vary.
            Translations provided by Google.

            Wayne Luke
            The Rabid Badger - a vBulletin Cloud demonstration site.
            vBulletin 5 API - Full / Mobile
            Vote for your favorite feature requests and the bugs you want to see fixed.

            Comment


            • #7
              hi Wayne Luke ,

              so i perform a fresh upgrade again delete all datas restore old vb4 database to new then run upgrade script again and All went fine, theres no error.
              but same issue again cannot log in , so i convert database again using ssh and login issue was fixed..

              but i notice that there is something error below.

              Click image for larger version

Name:	error.jpg
Views:	47
Size:	102.8 KB
ID:	4433746


              now im still always getting error on database


              Database error in vBulletin 5.5.6:

              Invalid SQL:

              SELECT COUNT(*) AS count
              FROM notification AS notification
              WHERE notification.recipient = 1
              AND notification.lastsenttime > notification.lastreadtime;

              MySQL Error : Unknown column 'notification.recipient' in 'where clause'
              Error Number : 1054
              Request Date : Monday, February 3rd 2020 @ 11:06:09 PM
              Error Date : Monday, February 3rd 2020 @ 11:06:09 PM
              Script : http://www.gsm/forum/chat/loadheaderdata
              Referrer : http://www.gsm/forum/blogs
              IP Address : 54.131.172
              Username : teech
              Classname : vB_Database_MySQLi
              MySQL Version : 5.7.23-23


              Stack Trace:
              #0 vB_Database->getErrorData() called in [path]/vb/database.php on line 1207
              #1 vB_Database->halt() called in [path]/vb/database/mysqli.php on line 249
              #2 vB_Database_MySQLi->execute_query() called in [path]/vb/database.php on line 576
              #3 vB_Database->query_read() called in [path]/vb/db/result.php on line 144
              #4 vB_dB_Result->rewind() called in [path]/vb/db/result.php on line 68
              #5 vB_dB_Result->__construct() called in [path]/packages/vbforum/db/mysql/querydefs.php on line 5586
              #6 vBForum_dB_MYSQL_QueryDefs->fetchNotificationCount() called in [path]/vb/db/query/method.php on line 175
              #7 vB_dB_Query_Method->execSQL() called in [path]/vb/db/assertor.php on line 301
              #8 vB_dB_Assertor->assertQuery() called in [path]/vb/db/assertor.php on line 625
              #9 vB_dB_Assertor->getRow() called in [path]/vb/library/notification.php on line 368
              #10 vB_Library_Notification->fetchNotificationCountForUser() called in [path]/vb/api/content/privatemessage.php on line 1502
              #11 vB_Api_Content_Privatemessage->getHeaderCounts() called in [path]/vb/api/wrapper.php on line 201
              #12 vB_Api_Wrapper->__call() called in /gs/public_html/forum/includes/api/interface/collapsed.php on line 101
              #13 Api_Interface_Collapsed->callApi() called in /gs/public_html/forum/includes/vb5/frontend/controller/chat.php on line 645
              #14 vB5_Frontend_Controller_Chat->actionLoadHeaderData() called in //gs/public_html/forum/index.php on line 74

              all web page not working and shows this error

              that action could not be completed. Please try again, and if this occurs again please contact the system administrator and tell them how you got this message.


              could you please help me to figured this out ,im not expert in database,


              regards,


              Attached Files

              Comment


              • #8
                This is the same exact database error that you posted in the first message. I have no idea what the error is in your Putty Screenshot because you've cut off the actual error.

                The answer for the database error is the same. Make sure your notifications table looks like the table I posted above.
                Translations provided by Google.

                Wayne Luke
                The Rabid Badger - a vBulletin Cloud demonstration site.
                vBulletin 5 API - Full / Mobile
                Vote for your favorite feature requests and the bugs you want to see fixed.

                Comment


                • #9
                  Originally posted by Wayne Luke View Post
                  This is the same exact database error that you posted in the first message. I have no idea what the error is in your Putty Screenshot because you've cut off the actual error.

                  The answer for the database error is the same. Make sure your notifications table looks like the table I posted above.
                  hi where to check this notification table.?

                  Comment


                  • #10
                    You would use whatever tool your hosting provider has made available to access the database directly. The most common is a web app called phpMyAdmin. I use a Windows application called HeidiSQL. You would have to ask your hosting provider how to access the tool they provide. There are thousands of these companies and every one of them does things differently.
                    Translations provided by Google.

                    Wayne Luke
                    The Rabid Badger - a vBulletin Cloud demonstration site.
                    vBulletin 5 API - Full / Mobile
                    Vote for your favorite feature requests and the bugs you want to see fixed.

                    Comment


                    • #11
                      hi Wayne Luke yes i have access on phpadmin, i mean can u please be more specific where to check this notification tables because when i click on table of this database no code or similar that you posted above, can you please guide me how to look at it via phpadmin,

                      regards,

                      Comment


                      • #12
                        When looking at the database, scroll down to notification. Click the link labeled "Structure" in the same row.

                        If you clicked on the table name, you can click the structure tab across the top of the page to get to the same place.

                        Or you can drop the table and then use the query I posted above to recreate it. You'll need to change the character set and collation to match your database but the the query will create the table it in the proper format. You'll lose old notifications but new ones should work.
                        Translations provided by Google.

                        Wayne Luke
                        The Rabid Badger - a vBulletin Cloud demonstration site.
                        vBulletin 5 API - Full / Mobile
                        Vote for your favorite feature requests and the bugs you want to see fixed.

                        Comment


                        • #13
                          hi Wayne Luke already drop that notification table can u please guide me how to re create table and how to insert that code u posted above

                          regards,

                          Comment


                          • #14
                            As stated, you would run this query:

                            Code:
                            CREATE TABLE IF NOT EXISTS `notification` (
                            `notificationid` int(10) unsigned NOT NULL AUTO_INCREMENT,
                            `recipient` int(10) unsigned NOT NULL,
                            `sender` int(10) unsigned DEFAULT NULL,
                            `lookupid` varchar(150) DEFAULT NULL,
                            `lookupid_hashed` char(32) DEFAULT NULL,
                            `sentbynodeid` int(10) unsigned DEFAULT NULL,
                            `customdata` mediumtext,
                            `typeid` smallint(5) unsigned NOT NULL,
                            `lastsenttime` int(10) unsigned NOT NULL DEFAULT '0',
                            `lastreadtime` int(10) unsigned NOT NULL DEFAULT '0',
                            PRIMARY KEY (`notificationid`),
                            UNIQUE KEY `guid` (`recipient`,`lookupid_hashed`),
                            KEY `recipient` (`recipient`),
                            KEY `lookupid_hashed` (`lookupid_hashed`),
                            KEY `lastsenttime` (`lastsenttime`),
                            KEY `lastreadtime` (`lastreadtime`)
                            ) ENGINE=InnoDB
                            The entire purpose of this query is to create the notification table.
                            Translations provided by Google.

                            Wayne Luke
                            The Rabid Badger - a vBulletin Cloud demonstration site.
                            vBulletin 5 API - Full / Mobile
                            Vote for your favorite feature requests and the bugs you want to see fixed.

                            Comment


                            • #15
                              hi Wayne Luke ,

                              so i run the query but i got this results, aslo my forum now not working all members getting this error that action could not be completed. Please try again, and if this occurs again please contact the system administrator and tell them how you got this message. but for guest or not login users can view forum..

                              query results,

                              Click image for larger version

Name:	query.jpg
Views:	34
Size:	87.3 KB
ID:	4433944
                              Attached Files

                              Comment

                              Related Topics

                              Collapse

                              Working...
                              X