Please whitelist cPanel in your adblocker so that you’re able to see our version release promotions, thanks!

The Community Forums

Interact with an entire community of cPanel & WHM users!

SOLVED No mysql for clients on MariaDB 10.2

Discussion in 'Database Discussion' started by Clud7, Jun 16, 2018.

  1. Clud7

    Clud7 Member

    Joined:
    Jun 16, 2018
    Messages:
    7
    Likes Received:
    1
    Trophy Points:
    3
    Location:
    GREECE
    cPanel Access Level:
    Root Administrator
    Hello! i just upgraded from mysql 5.6 --> mysql 5.7 --> MariaDB 10.2

    Everything works fine except mysql command in terminal for users.

    Code:
    [root@data ~]# mysql
    Welcome to the MariaDB monitor.  Commands end with ; or \g.
    Your MariaDB connection id is 18
    Server version: 10.2.15-MariaDB MariaDB Server
    
    Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.
    
    Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
    
    MariaDB [(none)]> exit
    Bye
    [root@data ~]# su veto
    [veto@data ~]$ mysql
    mysql: Can't read dir of '/etc/my.cnf.d' (Errcode: 2 "No such file or directory")
    Fatal error in defaults handling. Program aborted
    

    Also: /etc/my.cnf

    Code:
    #
    # This group is read both both by the client and the server
    # use it for options that affect everything
    #
    [client-server]
    
    #
    # include all files from the config directory
    #
    !includedir /etc/my.cnf.d
    
    [mysqld]
    log-error=/var/lib/mysql/data.lunaserver.gr.err
    performance-schema=0
    innodb_file_per_table=1
    default-storage-engine=MyISAM
    max_allowed_packet=268435456
    open_files_limit=14000
    2018-06-17 01_41_47-Window.png
    2018-06-17 01_44_56-Window.png
     
  2. 24x7server

    24x7server Well-Known Member

    Joined:
    Apr 17, 2013
    Messages:
    1,813
    Likes Received:
    84
    Trophy Points:
    78
    Location:
    India
    cPanel Access Level:
    Root Administrator
    Hi,
    I am not sure what you are doing but when you login into the user, you have to give the -u and -p paramter and enter the appropriate username and password to connect to the database..
    Also, from the error you reported, it appears that the user has no read access to the mentioned file to login. Check if it does..
     
    Stop hovering to collapse... Click to collapse... Hover to expand... Click to expand...
    Clud7 likes this.
  3. Clud7

    Clud7 Member

    Joined:
    Jun 16, 2018
    Messages:
    7
    Likes Received:
    1
    Trophy Points:
    3
    Location:
    GREECE
    cPanel Access Level:
    Root Administrator
    Hello and thank you for your prompt reply!

    In mysql 5.6 i was able to open the mysql terminal via root and user profile.
    Now after the upgrade, (MariaDB 10.2) i am able to open the mysql terminal only via root user.

    The result is:

    Code:
    [veto@data ~]$ mysql -u veto --password='PASSWORD' veto_products<mysqlupdate.sql
    mysql: Can't read dir of '/etc/my.cnf.d' (Errcode: 2 "No such file or directory")
    Fatal error in defaults handling. Program aborted
    
    With --no-defaults WORKS FINE!!!
    Code:
     mysql --no-defaults -u veto --password='PASSWORD' veto_products<mysqlupdate.sql
    Thinking that the problem is my.cnf file. Any idea how i should fix it to work correctly?

    I want to be able to run my mysql cron jobs again via clients cpanel.

    About the permissions:
    upload_2018-6-18_0-53-31.png

    upload_2018-6-18_0-53-50.png

    And this is my.cnf file:

    upload_2018-6-18_0-56-2.png

    upload_2018-6-18_0-56-24.png
     

    Attached Files:

    #3 Clud7, Jun 17, 2018
    Last edited: Jun 17, 2018
  4. 24x7server

    24x7server Well-Known Member

    Joined:
    Apr 17, 2013
    Messages:
    1,813
    Likes Received:
    84
    Trophy Points:
    78
    Location:
    India
    cPanel Access Level:
    Root Administrator
    Hi,

    Login to the user and perform below steps:
    cat /etc/my.cnf

    mysql -u <user> -p --> and then hit enter. Enter the password when prompted.
     
    Stop hovering to collapse... Click to collapse... Hover to expand... Click to expand...
    Clud7 likes this.
  5. Clud7

    Clud7 Member

    Joined:
    Jun 16, 2018
    Messages:
    7
    Likes Received:
    1
    Trophy Points:
    3
    Location:
    GREECE
    cPanel Access Level:
    Root Administrator
    upload_2018-6-18_19-28-34.png


    With --no-defaults the command works
    upload_2018-6-18_19-33-57.png

    Thanks
     
  6. Clud7

    Clud7 Member

    Joined:
    Jun 16, 2018
    Messages:
    7
    Likes Received:
    1
    Trophy Points:
    3
    Location:
    GREECE
    cPanel Access Level:
    Root Administrator
    The problem is the /etc/my.cnf file!

    After the Upgrade from Mysql to MariaDB you should change the
    upload_2018-6-18_19-55-20.png

    Steps:
    • I renamed the my.cnf to my.cnf.old
    • I restarted the Mysql
    • New my.cnf file re-created automatically
    • I edit the file with my personal mysqld configurations.

    Now it works.
     
  7. cPanelLauren

    cPanelLauren Forums Analyst
    Staff Member

    Joined:
    Nov 14, 2017
    Messages:
    1,835
    Likes Received:
    134
    Trophy Points:
    118
    Location:
    Houston
    cPanel Access Level:
    DataCenter Provider
    Hi @Clud7

    I'm happy to hear you found the solution to this issue though I wonder if there was some further customization done to your /etc/my.cnf file as I haven't needed to do this on a mysql -> MariaDB switch or upgrade.

    Thanks for letting us know what fixed the issue!
     
    Stop hovering to collapse... Click to collapse... Hover to expand... Click to expand...
    Clud7 likes this.
  8. Clud7

    Clud7 Member

    Joined:
    Jun 16, 2018
    Messages:
    7
    Likes Received:
    1
    Trophy Points:
    3
    Location:
    GREECE
    cPanel Access Level:
    Root Administrator
    Hello! My /etc/my.cnf file was the default one.
    upload_2018-6-18_20-26-55.png
     
  9. cPanelLauren

    cPanelLauren Forums Analyst
    Staff Member

    Joined:
    Nov 14, 2017
    Messages:
    1,835
    Likes Received:
    134
    Trophy Points:
    118
    Location:
    Houston
    cPanel Access Level:
    DataCenter Provider
    Hi @Clud7

    Thank you for that, what were the permissions and ownership of it?
    The default provided by cPanel looks like:
    Code:
    [root@server php-fpm]# cat /etc/my.cnf
    [mysqld]
    innodb_file_per_table=1
    default-storage-engine=MyISAM
    performance-schema=0
    max_allowed_packet=268435456
    open_files_limit=2048
    general-log
    general-log-file=queries.log
    log-output=file
    Not much involved nor does it specify the log dir. It does look like yours is also specifically including the dir you were experiencing issues with on line 10 which isn't present in the default my.cnf file.



    Thanks!
     
    Stop hovering to collapse... Click to collapse... Hover to expand... Click to expand...
    Clud7 likes this.
  10. Clud7

    Clud7 Member

    Joined:
    Jun 16, 2018
    Messages:
    7
    Likes Received:
    1
    Trophy Points:
    3
    Location:
    GREECE
    cPanel Access Level:
    Root Administrator
    Unfortunately i have the new /etc/my.cnf but i can confirm that the permissions and ownership for the old file my.cnf was the same as the new.
    We had to update the mysql 5.6 to MariaDB 10.2 because of the GDPR so the file was untouched from May 2017 till now

    upload_2018-6-19_2-43-51.png

    Also some info about our Server:
    upload_2018-6-19_2-49-52.png
     

    Attached Files:

  11. cPanelLauren

    cPanelLauren Forums Analyst
    Staff Member

    Joined:
    Nov 14, 2017
    Messages:
    1,835
    Likes Received:
    134
    Trophy Points:
    118
    Location:
    Houston
    cPanel Access Level:
    DataCenter Provider
    Hi @Clud7

    I believe the issue was due to the specified inclusion of the directory in the old my.cnf

    From my previous post:
    While that directory exists it's not specifically included in the my.cnf file on default installations. Is it present on the current new my.cnf file?

    Thanks!
     
    Stop hovering to collapse... Click to collapse... Hover to expand... Click to expand...
  12. Clud7

    Clud7 Member

    Joined:
    Jun 16, 2018
    Messages:
    7
    Likes Received:
    1
    Trophy Points:
    3
    Location:
    GREECE
    cPanel Access Level:
    Root Administrator
    The new auto-created my.cnf file was exactly like the image below:
    upload_2018-6-21_19-14-38.png

    After i added everything from my old my.cnf like below (except the !includedir /etc/my.cnf.d)
    upload_2018-6-21_19-15-44.png

    After that:
    All the cronjobs started and my users have full access to mysql command like:
    mysql -u USERNAME --password='PASSWORD'

    100% Success.
     
    cPanelLauren likes this.
  13. cPanelLauren

    cPanelLauren Forums Analyst
    Staff Member

    Joined:
    Nov 14, 2017
    Messages:
    1,835
    Likes Received:
    134
    Trophy Points:
    118
    Location:
    Houston
    cPanel Access Level:
    DataCenter Provider
    Hi @Clud7

    That's great, thank you for posting exactly what worked for you!
     
    Stop hovering to collapse... Click to collapse... Hover to expand... Click to expand...
Loading...

Share This Page

  1. This site uses cookies to help personalise content, tailor your experience and to keep you logged in if you register.
    By continuing to use this site, you are consenting to our use of cookies.
    Dismiss Notice