Error after Installation: 1215 Cannot add foreign key constraint

[megabert]

Error message in Browser:

SQLSTATE[HY000]: General error: 1215 Cannot add foreign key constraint

Steps to reproduce:

1. After Installation, the file ConfigUser.php(see below) is copied to /var/www/html/backend/config
2. (The database had been created and is empty at the moment)
3. Navigate with the browser to the ip of pdns-manager
4. I'm redirected to the ip-of-pdnsmanager/update - Location in the browser
5. (The database consists only of the the "remote" table so far. This table is empty.)
6. The Browser shows "Update PDNS Manager" and a Button to upgrade
7. When I click upgrade, at upgrade step 0/6 I get the error: SQLSTATE[HY000]: General error: 1215 Cannot add foreign key constraint

pdnsmanager-config:

<?PHP
# pdnsmanager user config
return [
    'db' => [
        'host' => 'pdnsmanager-mysql',
        'user' => 'powerdns_admin',
        'password' => 'slfjsldfj324r0s8dfdy_dsfj/df',
        'dbname' => 'powerdns_admin',
        'port' => 3306
    ],
    'logging' => [
        'level' => 'debug',
        'path' => '/tmp/pdnsmanager.log'
    ],
    'authentication' => [
        'foo' => [
            'plugin' => 'config',
            'prefix' => 'default',
            'config' => [
                'peter' => '$2y$10$WY2upwNQUrTburcGIfjJburThR9WjSCKzxXXh4NuWCrWdmqKcrC0C'
            ]
        ]
    ]
];

Log file (/tmp/pdnsmanager.log)

[2019-02-18 10:54:07] pdnsmanager.DEBUG: GET v1/update [] []
[2019-02-18 10:54:07] pdnsmanager.DEBUG: Database setup successfull [] []
[2019-02-18 10:54:09] pdnsmanager.DEBUG: POST v1/update [] []
[2019-02-18 10:54:09] pdnsmanager.DEBUG: Database setup successfull [] []
[2019-02-18 10:54:09] pdnsmanager.ERROR: Upgrade failed with: SQLSTATE[HY000]: General error: 1215 Cannot add foreign key constraint [] []

Environment: Docker

App-Container: Alpine Linux 3.9 pdnsmanager 2.0.1 (from tar.gz) Packages apache2 apache2-ctl php7-apache2 php7-common php7 php7-apache2 php7-json php7-openssl php7-mysqlnd php7-mysqli php7-pdo php7-pdo_mysql

phpinfo.html.txt

DB-Container: Oracle Linux Server 7.6 Packages mysql-community-server-minimal-5.7.25-1.el7.x86_64

[loewexy]

Did you run the setup process? Then in your point 2 the database should not be empty.

What do you want to do? This sounds like automating the installation?

[megabert]

I copied the config an individual file ConfigUser.php in the automated docker setup process. When I try to run the /setup url I get always redirected to /upgrade. I thought the setup process maybe called automatically.

I ran it without the copied ConfigUser.php and the setup works as normal. So: No Issue: User error.

Thanks for your help!

[IT-Experte]

Can you publish your "Dockerfile" to build your Docker image? I am also interested on a solution based on alpine.

Hint: It is not a good to publish your password.

[megabert]

@IT-Experte

The Dockerfile and docker-compose.yml is here: https://gist.github.com/megabert/9bbd6d41be5465f2d07953bb627ba8cb

Maybe it's useful to you.

[IT-Experte]

Thank you for you Dockerfile. I have created my Dockerfile, based on your and it is working with MySQL 8. I used the MySQL from Docker Hub, but it must work with MySQL 5.7 as well.

Her my hints:

1. ConfigUser.php 1.1. Don't create file config/ConfigUser.php. It will created by the setup process. 1.2. Or, If you have a pdns database, than you can copy your file "ConfigUser.php" into container. But then you call the update process http://pdnshost/update

2. MySQL 2.1 You must created a empty database (InnoDB) and your new database user. 2.2 Check the MySQL user security credentials. You can't working with 'user'@'localhost', becaue you use differend Docker container. You can use this 'user'@'172.17.0.%'. 2.3. Are you sure with the hostname from database "pdns manager-mysql" ? By me it is only "mysql". 2.4. Have you linked Docker container myql and pdnsmanager with option --link mysql1:mysql ?

3. Apache 3.1. Check your Apache config for your virtual host. The example is not working without "Require all granted".

  <Directory /var/www/localhost/frontend>
      Options FollowSymLinks
      AllowOverride all
      Require all granted
   </Directory>

[megabert]

@IT-Experte

At the moment I do not plan to use pdnsmanager, since in my understanding pdnsmanager wants to setup the pdns-database itself. I prefer that powerdns does the db provisioning for itself and no foreign application like pdnsmanager writes own data(pdnsmanager users,...) into the powerdns-database. (In case I got this wrong, anybody feel free to correct me.).

[megabert]

Ok. Since powerdns-admin isn't a usable option, I resume with this task. I'm creating a new docker setup now.

Or, If you have a pdns database, than you can copy your file "ConfigUser.php" into container. But then you call the update process http://pdnshost/update

I will not do this. I would like to do the schema update myself. But I think the programmer of pdnsmanager knows better to do that. So I initially extend the pdns schema to schema_setup version 6 (the current version) and pdnsmanager will prompt automatically if there's any new update and the user has to click "upgrade" once when a new schema should be available.

I'll publish the new work(dockerfiles, scripts) shortly.

See at #101 for the current docker scripts.