Learn about Centmin Mod LEMP Stack today
Register Now

Xenforo Lost connection to MySQL server during query when executing 'OPTIMIZE TABLE ... '

Discussion in 'Forum software usage' started by RoldanLT, Sep 20, 2016.

  1. RoldanLT

    RoldanLT Well-Known Member

    3,822
    928
    113
    May 25, 2014
    Phillipines
    Ratings:
    +1,256
    Local Time:
    11:26 PM
    1.11
    10.2
    I always got this error:
    Code:
    Lost connection to MySQL server during query when executing 'OPTIMIZE TABLE ... '
    When optimizing database table using this command:
    Code:
    mysqlcheck -u root -p --auto-repair --optimize --all-databases
    Error always show after this table:
    Code:
    xenforo_database.xf_search OK
    With all XenForo Forum DB I tried, it always fail.
    Any idea? :/
     
  2. RoldanLT

    RoldanLT Well-Known Member

    3,822
    928
    113
    May 25, 2014
    Phillipines
    Ratings:
    +1,256
    Local Time:
    11:26 PM
    1.11
    10.2
    Someone with XF forums can also try?
    Running this:
    Code:
    mysqlcheck -u root -p --auto-repair --optimize --all-databases
     
  3. Sunka

    Sunka Active Member

    888
    230
    43
    Oct 31, 2015
    Rijeka, Croatia
    Ratings:
    +376
    Local Time:
    5:26 PM
    Nginx 1.13.3
    MariaDB 10.1.24
    Net is full of that.
    try this:
     
  4. eva2000

    eva2000 Administrator Staff Member

    28,935
    6,568
    113
    May 24, 2014
    Brisbane, Australia
    Ratings:
    +9,748
    Local Time:
    1:26 AM
    Nginx 1.13.x
    MariaDB 5.5
    no DO NOT DO that !
     
    • Winner Winner x 1
    • Informative Informative x 1
  5. eva2000

    eva2000 Administrator Staff Member

    28,935
    6,568
    113
    May 24, 2014
    Brisbane, Australia
    Ratings:
    +9,748
    Local Time:
    1:26 AM
    Nginx 1.13.x
    MariaDB 5.5
    OPTIMIZE command, read up on MySQL official documentation on what the OPTIMIZE command does and how it can be bad for InnoDB tables especially on large table sizes due to InnoDB not supporting OPTIMIZE command the way it does for MyISAM tables if InnoDB is not configured in a specific way ;)

    same with mysqlcheck and InnoDB vs MyISAM

    Basically, understand what OPTIMIZE does exactly and when to use it.
     
    Last edited: Sep 20, 2016
    • Like Like x 1
  6. Sunka

    Sunka Active Member

    888
    230
    43
    Oct 31, 2015
    Rijeka, Croatia
    Ratings:
    +376
    Local Time:
    5:26 PM
    Nginx 1.13.3
    MariaDB 10.1.24
    Sorry, I just google it and found several same questions like Roldan wrote.
    Every each of that suggest what I quote.

    Sorry if I make you Roldan problem with my answer.
    I am sorry
     
  7. eva2000

    eva2000 Administrator Staff Member

    28,935
    6,568
    113
    May 24, 2014
    Brisbane, Australia
    Ratings:
    +9,748
    Local Time:
    1:26 AM
    Nginx 1.13.x
    MariaDB 5.5
    very dangerous using level 4 see MySQL :: MySQL 5.6 Reference Manual :: 14.21.2 Forcing InnoDB Recovery

    always read official MySQL documentation first MySQL :: MySQL Documentation

     
    Last edited: Sep 20, 2016
  8. RoldanLT

    RoldanLT Well-Known Member

    3,822
    928
    113
    May 25, 2014
    Phillipines
    Ratings:
    +1,256
    Local Time:
    11:26 PM
    1.11
    10.2
    So is this the answer of my question? :|
     
  9. eva2000

    eva2000 Administrator Staff Member

    28,935
    6,568
    113
    May 24, 2014
    Brisbane, Australia
    Ratings:
    +9,748
    Local Time:
    1:26 AM
    Nginx 1.13.x
    MariaDB 5.5
    nope totally unrelated to you lost connections to mysql during OPTIMIZE