MySQL issues with Install Script

[tallwireless]

ISSUE TYPE

Bug Report

OS

charlesr@portal:/srv/ixpmanager$ cat /etc/lsb-release
DISTRIB_ID=Ubuntu
DISTRIB_RELEASE=20.04
DISTRIB_CODENAME=focal
DISTRIB_DESCRIPTION="Ubuntu 20.04.6 LTS"

VERSION

charlesr@portal:/srv/ixpmanager$ cat version.php | grep APPLICATION
define( 'APPLICATION_VERSION', '6.4.0' );
define( 'APPLICATION_VERDATE', '2024052400' );

ENVIRONMENT

charlesr@portal:/srv/ixpmanager$ php -v
PHP 8.0.30 (cli) (built: May 23 2024 12:31:02) ( NTS )
Copyright (c) The PHP Group
Zend Engine v4.0.30, Copyright (c) Zend Technologies
    with Zend OPcache v8.0.30, Copyright (c), by Zend Technologies
charlesr@portal:/srv/ixpmanager$ dpkg -l | grep php
ii  libapache2-mod-php8.0                 1:8.0.30-6+ubuntu20.04.1+deb.sury.org+1      amd64        server-side, HTML-embedded scripting language (Apache 2 module)
ii  php-common                            2:94+ubuntu20.04.1+deb.sury.org+2            all          Common files for PHP packages
ii  php8.0                                1:8.0.30-6+ubuntu20.04.1+deb.sury.org+1      all          server-side, HTML-embedded scripting language (metapackage)
ii  php8.0-bcmath                         1:8.0.30-6+ubuntu20.04.1+deb.sury.org+1      amd64        Bcmath module for PHP
ii  php8.0-cgi                            1:8.0.30-6+ubuntu20.04.1+deb.sury.org+1      amd64        server-side, HTML-embedded scripting language (CGI binary)
ii  php8.0-cli                            1:8.0.30-6+ubuntu20.04.1+deb.sury.org+1      amd64        command-line interpreter for the PHP scripting language
ii  php8.0-common                         1:8.0.30-6+ubuntu20.04.1+deb.sury.org+1      amd64        documentation, examples and common module for PHP
ii  php8.0-curl                           1:8.0.30-6+ubuntu20.04.1+deb.sury.org+1      amd64        CURL module for PHP
ii  php8.0-ds                             1.5.0-1+ubuntu20.04.1+deb.sury.org+1         amd64        PHP extension providing efficient data structures for PHP 7
ii  php8.0-gd                             1:8.0.30-6+ubuntu20.04.1+deb.sury.org+1      amd64        GD module for PHP
ii  php8.0-igbinary                       3.2.15-1+ubuntu20.04.1+deb.sury.org+1        amd64        igbinary PHP serializer
ii  php8.0-intl                           1:8.0.30-6+ubuntu20.04.1+deb.sury.org+1      amd64        Internationalisation module for PHP
ii  php8.0-mbstring                       1:8.0.30-6+ubuntu20.04.1+deb.sury.org+1      amd64        MBSTRING module for PHP
ii  php8.0-memcache                       8.2-2+ubuntu20.04.1+deb.sury.org+2           amd64        memcache extension module for PHP
ii  php8.0-memcached                      3.2.0++-1+ubuntu20.04.1+deb.sury.org+2       amd64        memcached extension module for PHP, uses libmemcached
ii  php8.0-msgpack                        1:2.1.2-1+ubuntu20.04.1+deb.sury.org+1       amd64        PHP extension for interfacing with MessagePack
ii  php8.0-mysql                          1:8.0.30-6+ubuntu20.04.1+deb.sury.org+1      amd64        MySQL module for PHP
ii  php8.0-opcache                        1:8.0.30-6+ubuntu20.04.1+deb.sury.org+1      amd64        Zend OpCache module for PHP
ii  php8.0-readline                       1:8.0.30-6+ubuntu20.04.1+deb.sury.org+1      amd64        readline module for PHP
ii  php8.0-rrd                            2.0.3++-1+ubuntu20.04.1+deb.sury.org+2       amd64        PHP bindings to rrd tool system
ii  php8.0-snmp                           1:8.0.30-6+ubuntu20.04.1+deb.sury.org+1      amd64        SNMP module for PHP
ii  php8.0-xml                            1:8.0.30-6+ubuntu20.04.1+deb.sury.org+1      amd64        DOM, SimpleXML, XML, and XSL module for PHP
ii  php8.0-yaml                           2.2.3-2+ubuntu20.04.1+deb.sury.org+2         amd64        YAML-1.1 parser and emitter for PHP
ii  php8.0-zip                            1:8.0.30-6+ubuntu20.04.1+deb.sury.org+1      amd64        Zip module for PHP
charlesr@portal:/srv/ixpmanager$ 

CONFIGURATION

APP_DEBUG=false
APP_URL="http://149.28.92.25"
APP_TIMEZONE="UTC"
APP_LOG="single"
IXP_RESELLER_ENABLED=false
IXP_AS112_UI_ACTIVE=false
IXP_FE_FRONTEND_DISABLED_LOGO=false
GRAPHER_BACKENDS="dummy"
GRAPHER_CACHE_ENABLED=true
GRAPHER_BACKEND_MRTG_DBTYPE="rrd"
IXP_API_JSONEXPORTSCHEMA_PUBLIC=true
CACHE_DRIVER=memcached
IXP_IRRDB_BGPQ3_PATH=/usr/bin/bgpq3

SUMMARY

Installer fails to complete. Appears to be an issue with the a difference between the database schema in the repo and the install script.

STEPS TO REPRODUCE

During the installation, the installer quits after attempting to add entries to the database:

...
Creating database and database users...mysql: [Warning] Using a password on the command line interface can be insecure.
[done]
Creating .env file...[done]
Installing composer for PHP dependencies... [done]
Running composer to install PHP dependencies (please be patient)... [done]
Generating application key... [done]
Setting up IXP Manager database... root@portal:~# 

Making some modifications to the script to get the MySQL commands, I was able to determine that there is a difference between the schema in the database, and the commands used by the installer:

mysql> INSERT INTO infrastructure ( name, shortname, isPrimary, created_at, updated_at )
    ->     VALUES ( 'Infrastructure #1', '#1', 1, NOW(), NOW() );
ERROR 1054 (42S22): Unknown column 'created_at' in 'field list'
mysql> INSERT INTO infrastructure ( name, shortname, isPrimary )     VALUES ( 'Infrastructure #1', '#1', 1 );
ERROR 1364 (HY000): Field 'ixp_id' doesn't have a default value
mysql> describe infrastructure;
+-----------------+--------------+------+-----+---------+----------------+
| Field           | Type         | Null | Key | Default | Extra          |
+-----------------+--------------+------+-----+---------+----------------+
| id              | int          | NO   | PRI | NULL    | auto_increment |
| ixp_id          | int          | NO   | MUL | NULL    |                |
| name            | varchar(255) | YES  |     | NULL    |                |
| shortname       | varchar(255) | YES  | MUL | NULL    |                |
| isPrimary       | tinyint(1)   | NO   |     | 0       |                |
| peeringdb_ix_id | bigint       | YES  |     | NULL    |                |
| ixf_ix_id       | bigint       | YES  |     | NULL    |                |
| country         | varchar(2)   | YES  |     | NULL    |                |
+-----------------+--------------+------+-----+---------+----------------+
8 rows in set (0.00 sec)

As I'm currently working on bringing up a new IX, I have installed the portal software several times in the last month for testing and development, and haven't run into any issues. I think this might be related to the new release of the software.

EXPECTED RESULTS

A successful installation of the portal software.

ACTUAL RESULTS

A non-successful installation of the portal software.

IMPORTANCE

This is of importance to us as we are currently setting up our production environment to go live in the next week or so.

RELEVANT LOGS

N/A

[WuShengHung]

I am encountering the same issue. The installation process gets stuck at "Setting up IXP Manager database...". I have found the following error message in the ixp-manager-install.log

In Connection.php line 760:
SQLSTATE[42S22]: Column not found: 1054 Unknown column 'ca.colocation' in 'field list' (SQL: -- Views and tri
ggers used on the IXP Manager database

It looks like the problem was related to a code change in PR #887. After I reversed that change, the installation worked fine.
But It still need to be confirm.

[barryo]

Thanks @tallwireless and @ShengHungWu - that has been fixed now.

The install script should work fine now as it pulls from release-v6 and the fix is in there.