/ Register

Learn about Centmin Mod LEMP Stack today
Become a Member

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

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

Previous Thread Next Thread
Loading...
  1. Sep 20, 2016 #1
    rdan

    rdan Well-Known Member

    5,447
    1,408
    113
    May 25, 2014
    Ratings:
    +2,201
    Local Time:
    3:01 AM
    Mainline
    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. Sep 20, 2016 #2
    rdan

    rdan Well-Known Member

    5,447
    1,408
    113
    May 25, 2014
    Ratings:
    +2,201
    Local Time:
    3:01 AM
    Mainline
    10.2
    Someone with XF forums can also try?
    Running this:
    Code:
    mysqlcheck -u root -p --auto-repair --optimize --all-databases
     
  3. Sep 20, 2016 #3
    Sunka

    Sunka Well-Known Member

    1,150
    325
    83
    Oct 31, 2015
    Pula, Croatia
    Ratings:
    +525
    Local Time:
    8:01 PM
    Nginx 1.17.9
    MariaDB 10.3.22
    Net is full of that.
    try this:
     
  4. Sep 20, 2016 #4
    eva2000

    eva2000 Administrator Staff Member

    54,864
    12,239
    113
    May 24, 2014
    Brisbane, Australia
    Ratings:
    +18,810
    Local Time:
    5:01 AM
    Nginx 1.27.x
    MariaDB 10.x/11.4+
    no DO NOT DO that !
     
  5. Sep 20, 2016 #5
    eva2000

    eva2000 Administrator Staff Member

    54,864
    12,239
    113
    May 24, 2014
    Brisbane, Australia
    Ratings:
    +18,810
    Local Time:
    5:01 AM
    Nginx 1.27.x
    MariaDB 10.x/11.4+
    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
  6. Sep 20, 2016 #6
    Sunka

    Sunka Well-Known Member

    1,150
    325
    83
    Oct 31, 2015
    Pula, Croatia
    Ratings:
    +525
    Local Time:
    8:01 PM
    Nginx 1.17.9
    MariaDB 10.3.22
    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. Sep 20, 2016 #7
    eva2000

    eva2000 Administrator Staff Member

    54,864
    12,239
    113
    May 24, 2014
    Brisbane, Australia
    Ratings:
    +18,810
    Local Time:
    5:01 AM
    Nginx 1.27.x
    MariaDB 10.x/11.4+
    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. Sep 21, 2016 #8
    rdan

    rdan Well-Known Member

    5,447
    1,408
    113
    May 25, 2014
    Ratings:
    +2,201
    Local Time:
    3:01 AM
    Mainline
    10.2
    So is this the answer of my question? :|
     
  9. Sep 21, 2016 #9
    eva2000

    eva2000 Administrator Staff Member

    54,864
    12,239
    113
    May 24, 2014
    Brisbane, Australia
    Ratings:
    +18,810
    Local Time:
    5:01 AM
    Nginx 1.27.x
    MariaDB 10.x/11.4+
    nope totally unrelated to you lost connections to mysql during OPTIMIZE
     
Previous Thread Next Thread
Loading...

.