Cannot connect to MariaDB as root

Discussion in 'Installation/Configuration' started by Niels Terp, Jan 19, 2019.

  1. Niels Terp

    Niels Terp New Member

    Hi there,
    I'm setting up ISPConfig 3 on Debian 9, following the guide I have followed this guide before without any problems. This time however, I could not set up phpmyadmin with dbcommon - I could not connect to mysql as root. Same problem with Roundcube, wich I also had to abandon. Ind finaly, ISPConfig cannot be installed.

    In section 8, after I run mysql_secure_installation, I checked with mysql -u root -p, and I logged on without problems.
    Then I continued through the guide, checking after each point that I could still connect to mysql as root.
    The problem comes after I insert the lines
    mysql soft nofile 65535
    mysql hard nofile 65535
    From this point forward, I cannot connect as root, and hence the installation fails.
    I then tried to omit these two lines, but then the problem appears after I create limits.conf and restart mysql (wich, by the way, gives a warning, but I have seen that before but able to continue).

    So no matter what I do, I don't get past section 8. Realy hope you can help on this.

  2. kmchen

    kmchen Member

    Hi, what is your hoster ? On OVH you have that type of server debian9/ispconfig installed
  3. Taleman

    Taleman Well-Known Member HowtoForge Supporter

    Can you connect as some other user?
    Have you checked sql server is running? It looks like you problem starts after restart of mysql. Or you prevent logins from the IP you try to login from.
    I have had problems with special characters in password. I don't remember any more what it was, maybe ":" but anyway that password could be set but it did not work when trying to login to mysql.
  4. Niels Terp

    Niels Terp New Member

    Yes, the mysql server is running, and I can connect as other users, and my password is OK - no special charecters.
    I found some other people who has had problems with this, and one of them suggested to create another user (e.g. 'admin') with the same privs as root. That worked ! Even I didn't have to refer to that user, now I could log in as root throughout the rest of the guide !

    Very strange indeed. Now I will do the whole thing again, hopefully with more success.
  5. Niels Terp

    Niels Terp New Member

    Well, it seems to be a good idea not to follow instructions to much:
    In the guide, when setting the password authentication method in MariaDB with the statement
    echo "update mysql.user set plugin = 'mysql_native_password' where user='root';" | mysql -u root
    it should be obvious even to me, that a -p should be added. The problem here is, that if you do NOT add -p, MariaDB just accept that without complaining. Therefore the statement does not change anything, and therefore everything stops working after that slight mistake.
    So if you are sleepy and unconcentrated :), you should NOT just rely on cut and paste.
    Still, I think it should be added in the guide - people with less or no knowledge of MySql could get into trouble here.

  6. till

    till Super Moderator Staff Member ISPConfig Developer

    The -p is not added because it is not needed when executing this command as root, the root user is authenticated automatically and does not need a password. Probably your base setup differs or you installed MySQL instead of MariaDB?
    Niels Terp likes this.
  7. Niels Terp

    Niels Terp New Member

    Hi Till, you are of cause right. Thank you for refreshing my sql 101 :)
    I have tried to trace the error by going through section 8 of the guide step by step. After each step I restarted mysql, and checked that I could still log on as root - with or whitout password.
    After I run the statement
    echo "update mysql.user set plugin = 'mysql_native_password' where user='root';" | mysql -u root
    I could still log on, but after I restarted mysql, I could not !
    But interestingly I could in fact log on if I used -p and the password. And I WAS root.
    My best gues: A flaw in this version of MariaDB ?

Share This Page