Issue with dbconfig-common and autocreating databases for roundcube, phpmyadmin

Discussion in 'General' started by F4lcon, Jul 16, 2019.

  1. F4lcon

    F4lcon New Member

    Hi,
    following new tutorial installing ISPConfig on Debian Buster, I have a issue while installing roundcube and phpmyadmin/unstable (didn't try manual installation of current packages).
    I set up correct root password in debian.cnf, set 'yes' to the question Configure database for roundcube with dbconfig-common?
    Installer always ends with: mysql said: ERROR 1049 (42000): Unknown database 'roundcube' (or 'phpmyadmin' etc...)
    Tried to create db and user from shell, it is working fine. When running dpkg-reconfigure, it ends with the same error. Here is the output:
    ---
    Determining localhost credentials from /etc/mysql/debian.cnf: succeeded.
    dbconfig-common: writing config to /etc/dbconfig-common/roundcube.conf
    Replacing config file /etc/dbconfig-common/roundcube.conf with new version
    roundcube already exists and has privileges on roundcube.
    dbconfig-common: dumping mysql database roundcube to /var/tmp/roundcube.roundcube.2019-07-15-21.06.mysql.GABffs.
    dbconfig-common: dropping old mysql database roundcube.
    dropping database roundcube: success.
    verifying database roundcube was dropped: success.
    creating database roundcube: failed.
    error encountered creating database:
    mysql said: ERROR 1049 (42000): Unknown database 'roundcube'
    ---
    There is no special chars in mariadb root password. Any idea? Thanks
     
  2. till

    till Super Moderator Staff Member ISPConfig Developer

    Did you enable mysql root password with this command too:

    echo "update mysql.user set plugin = 'mysql_native_password' where user='root';" | mysql -u root

    and restarted mariadb as described in the tutorial? I'm working on the nginx version of the tutorial today, so I can use that to test it again.
     
  3. F4lcon

    F4lcon New Member

    Yes I run this command, just with -p parameter at the end to confirm root password - without the parameter it returned Access denied for user 'root'@'localhost' (using password: NO). After that I restarted mariadb service, still no luck...
     
  4. till

    till Super Moderator Staff Member ISPConfig Developer

    Hmm, ok. But it should not have asked for a password at that stage of the install as mariadb is configured to accept the shell root user Ubuntu as passwordless login, that's the Ubuntu default and gets just changed by the above command. So if you would rin the command again later, it would require the -p parameter but not at that install stage. What kind of base system do you use? I'm using the one that's described in the Debian 10 minimal guide, maybe you use a different kind of base setup where someone configured MariaDB already in a different way?
     
  5. till

    till Super Moderator Staff Member ISPConfig Developer

    Sorry, meant Debian, not Ubuntu. On Ubuntu 18.04 it's the same setup though :)
     
  6. F4lcon

    F4lcon New Member

    Maybe at the beginning of installation it didn't ask for a password, I am not sure now :) But I think it asked for a password even on Debian 9 installers I've tried (without dbconfig issues there). The base files are taken from OVH Debian 10 template (server hosted at OVH), but the installed system was clean, without preinstalled and preconfigured packages like mariadb/mysql etc.
     
  7. till

    till Super Moderator Staff Member ISPConfig Developer

    On Debian 9, it must ask for a password. So that#s ok.

    I was able to reproduce the problem now, the big question is, why it happens. Maybe a MariaDB update in the time since I wrote the guide. Will have to investigate that further.
     
  8. Taleman

    Taleman Well-Known Member HowtoForge Supporter

  9. till

    till Super Moderator Staff Member ISPConfig Developer

    @Taleman: that's what I thought too, but I was able to reproduce it here now. The question is just why this happens, seems like a bug in Debian to me. Here the mysql query log from roundcube installation:

    Code:
    190716 10:06:30     36 Connect  [email protected] as anonymous on
                        36 Connect  Access denied for user 'roundcube'@'localhost' (using password: YES)
                        37 Connect  [email protected] as anonymous on
                        37 Quit
                        38 Connect  [email protected] as anonymous on
                        38 Connect  Access denied for user 'roundcube'@'localhost' (using password: YES)
                        39 Connect  [email protected] as anonymous on
                        39 Query    SHOW GRANTS FOR 'roundcube'@'localhost'
                        39 Quit
                        40 Connect  [email protected] as anonymous on
                        40 Query    GRANT ALL PRIVILEGES ON `roundcube`.* TO `roundcube`@'localhost' IDENTIFIED BY 'YRvpj7SZEMO3'
                        40 Query    FLUSH PRIVILEGES
                        40 Quit
                        41 Connect  [email protected] as anonymous on
                        41 Query    SHOW GRANTS FOR 'roundcube'@'localhost'
                        41 Quit
                        42 Connect  [email protected] as anonymous on
                        42 Quit
                        43 Connect  [email protected] as anonymous on
                        43 Query    SHOW DATABASES
                        43 Quit
                        44 Connect  [email protected] as anonymous on roundcube
    As one can see, it does not even try to create a database. The roundcube mysql user is created successfully though, so there is no problem with the dbconfig common connection as I suspected first.
     
  10. till

    till Super Moderator Staff Member ISPConfig Developer

    I'll add this command to create the database manually upfront until Debian fixes the issue.

    Code:
    echo "CREATE DATABASE roundcube;" | mysql --defaults-file=/etc/mysql/debian.cnf
     
  11. F4lcon

    F4lcon New Member

    Thanks Till, it seems to be working now. I tried yesterday to manually create a database for roundcube from a mysql shell, but dbconfig dropped that database (as you can see from output in first post) - maybe there was a problem I had strong mariadb root password with a special chars.
     
  12. till

    till Super Moderator Staff Member ISPConfig Developer

    When special chars are used, the password needs to be enclosed in quotes or double quotes and it might be that no whitespace might be used after the '=', I just remember that I had some troubles in the past until I found that. I'll have to test that again what's needed exactly and add a note to the tutorial.
     
  13. F4lcon

    F4lcon New Member

    Ok thanks for your help. I can confirm phpmyadmin unstable (from Debian Sid) installed fine too after manually created database.
     
  14. till

    till Super Moderator Staff Member ISPConfig Developer

    You had to create the phpmyadmin database manually too? If yes, then there must be indeed a general bug in the dbconfig common tool from Debian. As the perfect server guide does not sue the sid package, I don't have to alter this part though.
     
  15. till

    till Super Moderator Staff Member ISPConfig Developer

    Or Debian dropped full support for the debian.cnf file. If that would be the case, then the MariaDB root user cannot be modified anymore to allow login by password which then means you can't use it anymore to manage databases in phpmyadmin, so we need another user with root privileges then or an additional root user with different auth scheme, but not sure if MariaDB supports that. And this might affect the creation of databases by ispconfig as well.
     
  16. F4lcon

    F4lcon New Member

    I've tried purge phpmyadmin now and install it again, without manually created database. I can confirm the same error:
    mysql said: ERROR 1049 (42000): Unknown database 'phpmyadmin'
    So it looks as a bug in dbconfig-common for me too...
     
  17. till

    till Super Moderator Staff Member ISPConfig Developer

    Thank you for testing this!
     
  18. Croydon

    Croydon ISPConfig Developer ISPConfig Developer

    All together: I further investigated this. It is indeed a bug in dbconfig-common that is VERY easy to fix:
    Code:
    --- /usr/share/dbconfig-common/internal/mysql.orig    2019-07-31 21:00:12.127398976 +0200
    +++ /usr/share/dbconfig-common/internal/mysql    2019-07-31 21:01:06.957160111 +0200
    @@ -195,7 +195,8 @@
             if [ "${dbc_mysql_createdb_encoding:-}" ]; then
                 extrasql=" CHARACTER SET '$dbc_mysql_createdb_encoding'";
             fi
    -        _dbc_nodb="yes" dbc_mysql_exec_command "CREATE DATABASE \`$dbc_dbname\`${extrasql:-}"
    +        _dbc_nodb="yes"
    +    dbc_mysql_exec_command "CREATE DATABASE \`$dbc_dbname\`${extrasql:-}"
             ret=$?
             _dbc_nodb=""
             if [ "$ret" = "0" ]; then
    @@ -314,7 +315,8 @@
     FLUSH PRIVILEGES;
     EOF
             l_dbname=$dbc_dbname
    -        _dbc_nodb="yes" dbc_mysql_exec_file "$l_sqlfile"
    +        _dbc_nodb="yes"
    +    dbc_mysql_exec_file "$l_sqlfile"
             l_ret=$?
             _dbc_nodb=""
    
    Store this in a file called e.g. /tmp/dbconfig-common.patch (or download the one from this thread attachment and upload/rename it).
    Then issue:
    Code:
    $> patch /usr/share/dbconfig-common/internal/mysql /tmp/dbconfig-common.patch
    Update: Even more easy:
    Code:
    sed -i -r 's/_dbc_nodb="yes" dbc_mysql_exec/_dbc_nodb="yes"; dbc_mysql_exec/g' /usr/share/dbconfig-common/internal/mysql
    After that you can use the apt-get install roundcube or phpmyadmin without problems.

    Short explanation:
    It seems that somewhen during the last updates of dbconfig-common there were 2 newlines that got lost. Without those newlines the variable _dbc_nodb is not correctly set inside the function that creates the sql command.
    This results in something like this (/tmp/xxx.sql to contain the CREATE DATABASE roundcube):
    Code:
    mysql --defaults-file=/tmp/xxxx roundcube < /tmp/xxxx.sql
    With the _dbc_nodb correctly set, the command would be:
    Code:
    mysql --defaults-file=/tmp/xxxx < /tmp/xxxx.sql
    As the database "roundcube" does not yet exist at that point the first command fails.
     

    Attached Files:

    Last edited: Jul 31, 2019
    NeilB, F4lcon, till and 2 others like this.
  19. Taleman

    Taleman Well-Known Member HowtoForge Supporter

    So the bug is in a file that comes from Debian package dbconfig-common?
    Was this fix and report submitted to bugs.debian.org? I tried to find it there but no luck.
     
  20. Croydon

    Croydon ISPConfig Developer ISPConfig Developer

    ahrasis likes this.

Share This Page