Discover Centmin Mod today
Register Now

MariaDB Anyone using MariaDB 10.4.8 on live and busy site?

Discussion in 'Nginx, PHP-FPM & MariaDB MySQL' started by rdan, Sep 18, 2019.

  1. eva2000

    eva2000 Administrator Staff Member

    44,750
    10,205
    113
    May 24, 2014
    Brisbane, Australia
    Ratings:
    +15,816
    Local Time:
    11:04 PM
    Nginx 1.17.x
    MariaDB 5.5/10.x
    minor version downgrades are fine to do though depends on what updates were made in 10.4.10 versus 10.4.8. MariaDB 10.4.9/10.4.10 has security and bug fixes it MariaDB 10.4.10 Release Notes including a criticial InnoDB bug which can prevent MariaDB server from starting up if it has Full Text InnoDB tables with Foreign Keys. So you'd want to be on MariaDB 10.4.10 if that situation applies to you otherwise, MariaDB MySQL server will not restart/start up.
    10.4.9
     
  2. negative

    negative Active Member

    405
    49
    28
    Apr 11, 2015
    Ratings:
    +96
    Local Time:
    4:04 PM
    1.9.10
    10.1.11
    10.4.8 works fine without any problem now in realtime server/websites.
    Mysql server restarting or starting without any issue.
    It didn't effected for me.

    I will think switch to never version of 10.4 family if everything is okay again (related issues with LIMIT clauses)
     
  3. Xon

    Xon Active Member

    170
    61
    28
    Nov 16, 2015
    Ratings:
    +223
    Local Time:
    9:04 PM
    1.15.x
    MariaDB 10.3.x
    Yeah changing optimizer_use_condition_selectivity=1 looks to have fixed things. Kinda yikes it causes such performance issues :(
     
  4. eva2000

    eva2000 Administrator Staff Member

    44,750
    10,205
    113
    May 24, 2014
    Brisbane, Australia
    Ratings:
    +15,816
    Local Time:
    11:04 PM
    Nginx 1.17.x
    MariaDB 5.5/10.x
    That's a strange one if 10.4.8 works. Let us know how the next update goes for 10.4.11 :)

    Thanks for the confirmation :) I'd probably be waiting till 10.4.2x releases to revisit 10.4 myself :D
     
  5. negative

    negative Active Member

    405
    49
    28
    Apr 11, 2015
    Ratings:
    +96
    Local Time:
    4:04 PM
    1.9.10
    10.1.11
    These problems fixed in mariadb 10.5 ? Anyone experienced? especially @Xon

    Thanks
     
  6. Xon

    Xon Active Member

    170
    61
    28
    Nov 16, 2015
    Ratings:
    +223
    Local Time:
    9:04 PM
    1.15.x
    MariaDB 10.3.x
    While there is a MariaDB 10.4.12 which contains a bunch of optimizer bugfixes, I still need to set optimizer_use_condition_selectivity=1 for some of my add-ons

    :edit: still does it for Mariadb 10.5.1 :(
     
    Last edited: Feb 25, 2020
  7. eva2000

    eva2000 Administrator Staff Member

    44,750
    10,205
    113
    May 24, 2014
    Brisbane, Australia
    Ratings:
    +15,816
    Local Time:
    11:04 PM
    Nginx 1.17.x
    MariaDB 5.5/10.x
    Thanks for the heads up. Seems like this will take quite a long time to iron out the issues ! Wonder if MySQL 8.0 is also having similar issues ?
     
  8. Nuno

    Nuno New Member

    2
    0
    1
    May 13, 2020
    Ratings:
    +0
    Local Time:
    2:04 PM
    Seems this was my problem too.

    FORCE INDEX was being ignored on a complex query, making it doing a full table scan, which was causing 100% CPU usage (just for that query running) and never getting results.

    optimizer_use_condition_selectivity = 1 fixed the problem.

    How are they not aware of this and how have they not reverted this yet?!?!
     
  9. eva2000

    eva2000 Administrator Staff Member

    44,750
    10,205
    113
    May 24, 2014
    Brisbane, Australia
    Ratings:
    +15,816
    Local Time:
    11:04 PM
    Nginx 1.17.x
    MariaDB 5.5/10.x
    Yeah seems to be the case still. For Centmin Mod 123.09beta01 and higher at least for optional MariaDB 10.4 update, I have made optimizer_use_condition_selectivity = 1 the default for this anyway.
     
  10. rdan

    rdan Well-Known Member

    4,962
    1,183
    113
    May 25, 2014
    Ratings:
    +1,797
    Local Time:
    9:04 PM
    Mainline
    10.2
    I have new server install with this config, but seems mysql_secure_installation isn't run.
    I can't find it on the logs also.
     
  11. rdan

    rdan Well-Known Member

    4,962
    1,183
    113
    May 25, 2014
    Ratings:
    +1,797
    Local Time:
    9:04 PM
    Mainline
    10.2
    Why is this commented out by default @eva2000 ?
     
  12. Meirami

    Meirami Member

    146
    24
    18
    Dec 21, 2017
    Ratings:
    +57
    Local Time:
    4:04 PM
    Same here. I ran it manually.
     
  13. eva2000

    eva2000 Administrator Staff Member

    44,750
    10,205
    113
    May 24, 2014
    Brisbane, Australia
    Ratings:
    +15,816
    Local Time:
    11:04 PM
    Nginx 1.17.x
    MariaDB 5.5/10.x
    for mariadb 10.4 the routine is different but it should of run see code at centminmod/centminmod

    you can verify if it was run if there's 4 files generated
    • /root/centminlogs/mariadb10.4-check-users-before.log
    • /root/centminlogs/mariadb10.4-check-users-after.log
    • /root/.my.cnf would have been populated with mysql root password and /root/centminlogs/set-dot-my.cnf.log would of been created
    what did you run manually ? as mariadb 10.4 is different from other previous versions

    because optimizer_use_condition_selectivity=1 resolved most of the issues IIRC
     
  14. rdan

    rdan Well-Known Member

    4,962
    1,183
    113
    May 25, 2014
    Ratings:
    +1,797
    Local Time:
    9:04 PM
    Mainline
    10.2
    This is blank file on my server.

    Yes created fine.

    I think he mean: mysql_secure_installation

     
  15. Meirami

    Meirami Member

    146
    24
    18
    Dec 21, 2017
    Ratings:
    +57
    Local Time:
    4:04 PM
    Sorry. I mean that I ran mysql_secure_installation manually.
    I have empty mariadb10.4-check-users-after.log.
    Other files are created.

    I used mysql_secure_installation to remove anonymous user. And that's why I thought it didn't run, because of anon user.
    Anyway, maybe I misunderstood something...
     
  16. eva2000

    eva2000 Administrator Staff Member

    44,750
    10,205
    113
    May 24, 2014
    Brisbane, Australia
    Ratings:
    +15,816
    Local Time:
    11:04 PM
    Nginx 1.17.x
    MariaDB 5.5/10.x
    From my past tests MariaDB 10.4 doesn't create an anonymous user and is more secure and the MySQL user table has actually moved, so hence why I did MariaDB 10.4 securing differently at centminmod/centminmod. See Authentication in MariaDB 10.4 — Understanding the Changes - MariaDB.org

    This is example output for mariadb10.4-check-users-after.log but can be done when you run the command on MariaDB 10.4 server
    Code (Text):
    mysql -e "select concat(user, '@', host, ' => ', json_detailed(priv)) from mysql.global_priv;"

    Don't post your output publicly on the forums as it is your mysql root and mysql user's login details. Centmin Mod makes MariaDB 10.4 use old mysql_native_password for best compatibility (see MariaDB - Interesting look at what's new in MariaDB 10.4)
    Code (Text):
    mysql -e "select concat(user, '@', host, ' => ', json_detailed(priv)) from mysql.global_priv;"          
    +--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
    | concat(user, '@', host, ' => ', json_detailed(priv))                                                                                                                                                                                                                                                                                 |
    +--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
    | root@localhost => {
       "access": 1073741823,
       "plugin": "mysql_native_password",
       "authentication_string": "*A177D80C40E567CF2F14D9CD0F14E650BCF0091F",
       "auth_or":
       [
          
           {
           },
          
           {
               "plugin": "unix_socket"
           }
       ],
       "password_last_changed": 1568844540
    } |
    | mysql@localhost => {
       "access": 18446744073709551615,
       "plugin": "mysql_native_password",
       "authentication_string": "invalid",
       "auth_or":
       [
          
           {
           },
          
           {
               "plugin": "unix_socket"
           }
       ]
    }                                                                 |
    +--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
    

    See there is no anonymous user by default in MariaDB 10.4 to need removing. Only root user (which Centmin Mod enables with a root password you can see via authentication string hash of the root password) and the default mysql user (which is disabled with authentication string = invalid Authentication in MariaDB 10.4 — Understanding the Changes - MariaDB.org

    MariaDB 10.4 has drastic changes to way it usually handles authentication from previous versions etc so ensure you're familiar with them MariaDB - Interesting look at what's new in MariaDB 10.4. Otherwise, probably best to stick with MariaDB 10.3 if you're unsure.
     
  17. rdan

    rdan Well-Known Member

    4,962
    1,183
    113
    May 25, 2014
    Ratings:
    +1,797
    Local Time:
    9:04 PM
    Mainline
    10.2
    Not on my situation.
    My new installed server with 10.4 without it, CPU load always stays 40-45%.

    Now I apply that config (5 minutes ago before I take the screenshot), CPU load is almost nothing (just below 10%):
    upload_2020-5-21_1-12-37.png
     
  18. Recep Baltas

    Recep Baltas New Member

    5
    3
    3
    Dec 11, 2015
    Ratings:
    +3
    Local Time:
    4:04 PM
    Hi,

    anyone using the latest stable version?
     
  19. negative

    negative Active Member

    405
    49
    28
    Apr 11, 2015
    Ratings:
    +96
    Local Time:
    4:04 PM
    1.9.10
    10.1.11
    version 10.4.8 still perfect for me without any problem however i'm interesting that answer if our problems fixed in 10.5.x too.
     
  20. buik

    buik “It always seems impossible until it’s done.” Premium Member

    1,297
    350
    83
    Apr 29, 2016
    Ratings:
    +1,055
    Local Time:
    3:04 PM