Join the community today
Register Now

MariaDB How to remove completely a corrupted database?

Discussion in 'Nginx, PHP-FPM & MariaDB MySQL' started by rdan, Nov 22, 2018.

  1. rdan

    rdan Well-Known Member

    4,547
    1,089
    113
    May 25, 2014
    Ratings:
    +1,590
    Local Time:
    4:34 AM
    Mainline
    10.2
    Please fill in any relevant information that applies to you:
    • CentOS Version: CentOS 7 64bit
    • Centmin Mod Version Installed: 123.09beta01
    • PHP Version Installed: 7.2.12
    • MariaDB MySQL Version Installed: 10.2.19

    What happen was...
    I have a Test/Dev Site running XF 2.0.10, with 10M post, 400k threads & 1M member records.
    I tried upgrading it to XF 2.0.1 Beta 2 using CLI mode.

    If I remember correctly, it stuck on Step 18 for several minutes (20-30 minutes I guess).
    I thought my Terminal was just stuck ( i forgot to run it on screen mode).

    So I open xf.domain/install and it presented me upgrade process, so I run the php cmd.php xf:upgrade on new Terminal and it shows Step 17 is done.

    But then presented me this error:
    So I raise my.cnf/innodb_lock_wait_timeout from 50 to 120 and then restarted MYSQL.
    But mysql can't be restarted.

    Mysql is DOWN, I can't mysqlstart/mysqlstop/mysqlrestart.
    I rebooted CentOS and it makes mysql running fine again.

    Whenever I tried accessing the Test/DevSite Database via PHPMyAdmin, the whole MYSQL will stop responding.
    I need to reboot CentOS again to temporary fix it.

    I tried Deleting the Test/DevSite Database via PHPMyAdmin and it stuck up again and MYSQL is down. Rebooted CentOS to fix it.

    Now I want to completely remove this corrupt Database.
    On /var/lib/mysql/Test-DevSite-Database/ directory
    I have this single unknown left file:
    #sql-ib13716-684285700.ibd

    I think I cannot delete this manually right?
     
  2. rdan

    rdan Well-Known Member

    4,547
    1,089
    113
    May 25, 2014
    Ratings:
    +1,590
    Local Time:
    4:34 AM
    Mainline
    10.2
    # /root/tools/dbbackup.sh dbinfo
    Test-DevSite-Database [idx: 0.00 MB data: 0.00 MB]
     
  3. eva2000

    eva2000 Administrator Staff Member

    40,322
    8,930
    113
    May 24, 2014
    Brisbane, Australia
    Ratings:
    +13,753
    Local Time:
    6:34 AM
    Nginx 1.15.x
    MariaDB 5.5/10.x
    You need to optimise mysql configuration to better handle mysql upgrades for such large forums :) Difference between optimised verus non-optimised mysql config can be huge for large forum upgrades !!!!

    so you started on CLI upgrade, then started web browser /install upgrade ? did you check if mysql processes were running for previous CLI upgrade ? Have you confirmed with xenforo support if moving from CLI upgrade to browser will retain it's state/position and start off where CLI upgrade left off ??

    generally CLI upgrades will be faster than web browser, so I wouldn't use web browser upgrade and stick with CLI upgrade.

    Probably will corrupt some mysql data if mysql processes are still running

    probably corrupt innodb tables so you won't be able get that info

    Centmin Mod is provide as is, so short of scripted related bugs or issues, any further optimisation to the web stack components - nginx, php-fpm, mariadb mysql, csf firewall etc or web app specific configurations are left to the Centmin Mod user to deal with. So I do not provide any free support for such as MySQL database corruption, repair and recovery. You'd want to hire someone if you are not 100% sure how to fix MySQL database corruption or do repair and recovery.

    However, Centmin Mod users are free to help each other out and ask questions or give answers on this community forum. My hopes are that this community forum evolves so that more veteran long time Centmin Mod users help new Centmin Mod users out :)

    If you did mysql restart/reboot or MySQL crashed in the middle of mysql operation i.e. processing an alter table and basically corrupted the database table(s) - producing an intermediate orphaned innodb table(s).

    See MySQL :: MySQL 5.7 Reference Manual :: 14.21.3 Troubleshooting InnoDB Data Dictionary Operations. Example below but the rest of instructions and steps after are left to you to figure out. You'd want to hire someone if you are not 100% sure how to fix MySQL database corruption or do repair and recovery.

    You can check using this command for orphaned tables beginning with #sql in their names.
    Code (Text):
    mysql -e "SELECT * FROM INFORMATION_SCHEMA.INNODB_SYS_TABLES WHERE NAME LIKE '%#sql%';"
    

    Look for table names that begin with #sql. If the original table resides in a file-per-table tablespace (Centmin Mod MariaDB does use file-per-table), the tablespace file (the #sql-*.ibd file) for the orphan intermediate table should be visible in the database directory.

    To remove an orphan intermediate table, perform the following steps:
    1. In the database directory, rename the #sql-*.frm file to match the base name of the orphan intermediate table:

      Code (Text):
      mv #sql-36ab_2.frm #sql-ib87-856498050.frm


      Note
      If there is no .frm file, you can recreate it. The .frm file must have the same table schema as the orphan intermediate table (it must have the same columns and indexes) and must be placed in the database directory of the orphan intermediate table.

    2. Drop the orphan intermediate table by issuing a DROP TABLE statement, prefixing the name of the table with #mysql50# and enclosing the table name in backticks. For example:

      Code (Text):
      mysql > DROP TABLE `#mysql50##sql-ib87-856498050`;

      The #mysql50# prefix tells MySQL to ignore file name safe encoding introduced in MySQL 5.1. Enclosing the table name in backticks is required to perform SQL statements on table names with special characters such as “#”.
     
    • Like Like x 1
  4. rdan

    rdan Well-Known Member

    4,547
    1,089
    113
    May 25, 2014
    Ratings:
    +1,590
    Local Time:
    4:34 AM
    Mainline
    10.2
    Yes, but still run cli upgrade after that.

    No I haven't.

    Sadly No, I'm not sure if it's fine (mostly not).

    Nothing with that name.
     
  5. eva2000

    eva2000 Administrator Staff Member

    40,322
    8,930
    113
    May 24, 2014
    Brisbane, Australia
    Ratings:
    +13,753
    Local Time:
    6:34 AM
    Nginx 1.15.x
    MariaDB 5.5/10.x
    it would show in /var/lib/mysql/yourdbname/ directory too.
     
    • Like Like x 1
  6. rdan

    rdan Well-Known Member

    4,547
    1,089
    113
    May 25, 2014
    Ratings:
    +1,590
    Local Time:
    4:34 AM
    Mainline
    10.2
    But I only have this single file:
    sql-ib13716-684285700.ibd

    Not sure how to do this exactly?

    Thanks!
     
  7. eva2000

    eva2000 Administrator Staff Member

    40,322
    8,930
    113
    May 24, 2014
    Brisbane, Australia
    Ratings:
    +13,753
    Local Time:
    6:34 AM
    Nginx 1.15.x
    MariaDB 5.5/10.x
    Google for how to re-create frm files for innodb orphaned tables ;)

    or

     
  8. rdan

    rdan Well-Known Member

    4,547
    1,089
    113
    May 25, 2014
    Ratings:
    +1,590
    Local Time:
    4:34 AM
    Mainline
    10.2
    I didn't do anything and I just check this folder is now empty.

    :unsure:
     
  9. eva2000

    eva2000 Administrator Staff Member

    40,322
    8,930
    113
    May 24, 2014
    Brisbane, Australia
    Ratings:
    +13,753
    Local Time:
    6:34 AM
    Nginx 1.15.x
    MariaDB 5.5/10.x
    now that's weird indeed !
     
..