Announcement

Collapse
No announcement yet.

MySQL error code 145: Table was marked as crashed and should be repaired

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

  • MySQL error code 145: Table was marked as crashed and should be repaired

    Ver 5.6.2
    (but same in prior versions back to 4.x)

    ------
    Database error in vBulletin 5.6.2:
    Invalid SQL:
    REPLACE INTO searchlog
    (type,userid,ipaddress,searchhash,sortby,sortorder,searchtime,dateline,json,results,result s_count)
    VALUES (removed)
    MySQL Error : Table '.\forum\searchlog' is marked as crashed and should be repaired
    Error Number : 145
    ------

    This happens on a dirty shutdown of the server.

    It's happens to the following MYISAM tables:

    USER (always)
    SEARCHLOG (always)
    USERFIELD (sometimes, not always)

    ---------

    The manual shows: Common MySQL Error Messages

    MySQL error code 145: Table was marked as crashed and should be repaired

    These error codes all have the same solution. They specify that something has gone wrong with your data. You should run optimize & repair on your database tables. You can upload the tools.php file from the vbulletin .zip file do_not_upload/ folder into your forumdir/admincp/ directory and run it through the browser, there's a switch there to run optimize and repair. Run it a few times for best result.
    ----------

    To fix it manually I'll:

    CHECK TABLE `searchlog`, `user`, `userfield`

    and as needed

    REPAIR TABLE `searchlog`, `user`, `userfield`

    ---------

    While I fully understand that a dirty shutdown of the server should never happen, it does on rare occasions and the forum will remain broken until I find it and repair the 3 tables.


    What would be the best way to automate this so that it happens after the Windows server reboots?



  • #2
    It would be very bad practice to automate repairs of tables like that.

    Better to investigate why such shutdowns are repeatedly necessary, as they shouldn't be. That is outside the scope of our support however.
    MARK.B | vBULLETIN SUPPORT

    TalkNewsUK - My vBulletin 5.6.4 Demo
    AdminAmmo - My Cloud Demo

    Comment


    • #3
      Thanks Mark,
      I agree, the server restarting is a problem and we are well aware and will work toward a better solution (per my second last line in the post)

      ...but this was a request for MYSQL scripting ideas and directional help and I understand it's outside of the realm of normal VBull support even though vbull is the only thing that doesn't recover from a dirty shutdown on this box.

      This morning I woke to 3500+ emailed error messages in my inbox. If I had a scheduled script running every hour to check those 3 tables or a script that started 5 minutes after a reboot then maybe my site would have only had problems for minutes and my inbox would have been easier to clean-up!

      This forum is full of people with far better sql skills than I will ever have so I was really hoping for a reply to the question, not just a "it's not vbull problem" answer.

      Does anyone have experience with using a batch file to run check table commands and do a repair table if the result isn't OK?

      Comment


      • #4
        The problem is that table repairs is not routine maintenance. It should only be carried out if there is a specific problem pointing to it needing to be done. We actually removed the option to do this from the admincp for that very reason. You can cause damage to the database.
        MARK.B | vBULLETIN SUPPORT

        TalkNewsUK - My vBulletin 5.6.4 Demo
        AdminAmmo - My Cloud Demo

        Comment


        • #5
          You can repair MyISAM tables using the myisamchk command line utility provided with MySQL. There is more information about this here:
          https://dev.mysql.com/doc/refman/5.7...am-repair.html

          The underlying issue is that you're using MyISAM tables. We do not recommend this and do not create these tables as MyISAM in a default installation. These tables should be converted to INNODB. We provide a script to do this with /do_not_upload/dbtools/myisamfix.phar directory. This can only be run after you repair the tables using the instructions linked above.

          To use it:
          1. Rename vbutil_config.php.new to vbutil_config.php.
          2. Edit the vbutil_config.php file to point to your vBulletin database.
          3. Upload the myisamfix.phar and vbutil_config.php to your server. They do not need to be in the web directory, just on the server.
          4. Turn off your forums in the AdminCP.
          5. Run the myisamfix.phar with the command line: php myisamfix.phar
          More information on our database tools can be found here: https://forum.vbulletin.com/articles...database-tools

          After switching to INNODB tables, you shouldn't encounter crashed tables in the future. Though, you should look into whether you can shut down the MySQL service automatically using a batch file or group policies prior to reboot.
          Last edited by Wayne Luke; Fri 17 Jul '20, 8:15am.
          Translations provided by Google.

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

          Comment


          • #6
            Thanks for the excellent instructions Wayne.

            The underlying issue is that you're using MyISAM tables. We do not recommend this and do not create these tables as MyISAM in a default installation
            I certainly didn't create them so can only guess that these are something that migrated from VBull v4.x


            I added my DB details to util and ran: php myisamfix.phar
            ...but it returns to the command prompt without error or echo.

            Checked in PHP MyAdmin and the tables didn't convert.

            This is all the tables sorted by type:



            Click image for larger version

Name:	MyIsam.jpg
Views:	140
Size:	271.1 KB
ID:	4444337

            glowhostxxx - will be VB4 antispam add-on leftovers
            rbd_xxx - is prob also an old VB4 add-on

            Should I drop those?

            I assume the other MyISAM tables are all VB4 migration ones?

            I can manually change them in PHP MyAdmin, should I just do that?


            Comment


            • #7
              The addon tables can be dropped. They won't provide any useful data to the system.

              Not sure why the script didn't convert unless the user doesn't have all the proper permissions for some reason. You can manually change them. The script is just a way to do it quicker. Do not change the language, phrase, or usertextfield tables unless you're using MySQL 5.7 or higher.
              Translations provided by Google.

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

              Comment


              • #8
                Paranoid double checking...

                Do not change the language, phrase, or usertextfield tables unless you're using MySQL 5.7 or higher.
                I'm running 8.0.19 - MySQL Community Server

                So it's okay to change those tables too?

                Comment


                • #9
                  Done and the forum still works, woohoo

                  For those playing at home (or someone in the future that finds this while troubleshooting)
                  ...this was the SQL that I used.

                  ----- Display the tables using MyISAM ------

                  USE forum;
                  SELECT TABLE_NAME, ENGINE
                  FROM information_schema.TABLES
                  WHERE TABLE_SCHEMA = 'forum' and ENGINE = 'myISAM'


                  ----- Convert the MyISAM tables to InnoDB -----

                  USE forum;
                  ALTER TABLE contenttype ENGINE=InnoDB;
                  ALTER TABLE impexerror ENGINE=InnoDB;
                  ALTER TABLE language ENGINE=InnoDB;
                  ALTER TABLE phrase ENGINE=InnoDB;
                  ALTER TABLE searchlog ENGINE=InnoDB;
                  ALTER TABLE tag ENGINE=InnoDB;
                  ALTER TABLE user ENGINE=InnoDB;
                  ALTER TABLE userfield ENGINE=InnoDB;
                  ALTER TABLE usersave ENGINE=InnoDB;

                  --------------

                  Thanks for the help Wayne and Mark,
                  hopefully the next dirty shutdown never happens but if it does, then there is more chance those table will survive like the rest of the InnoDB ones do.




                  Comment

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