API Portal not starting within local Mysql 5.7 database
Article ID: 274019
Updated On:
Products
Issue/Introduction
I have attempted multiple different attempts to get a customers portal upgraded to the latest version 5.2 from 4.5.
Portal version 4.5
MySQL user – ‘portal’@’%’
Password policy is LOW
MySQL version - Server version: 5.7.43 MySQL Community Server
My.cnf
$ sudo vi /etc/my.cnf
# For advice on how to change settings please see
# http://dev.mysql.com/doc/refman/5.7/en/server-configuration-defaults.html
[mysqld]
#
# Remove leading # and set to the amount of RAM for the most important data
# cache in MySQL. Start at 70% of total RAM for dedicated server, else 10%.
# innodb_buffer_pool_size = 128M
#
# Remove leading # to turn on a very important data integrity option: logging
# changes to the binary log between backups.
# log_bin
#
# Remove leading # to set options mainly useful for reporting servers.
# The server defaults are faster for transactions and fast SELECTs.
# Adjust sizes as needed, experiment to find the optimal values.
# join_buffer_size = 128M
# sort_buffer_size = 2M
# read_rnd_buffer_size = 2M
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
# Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links=0
log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid
[client]
default-character-set=utf8mb4
[mysqld]
character-set-server=utf8mb4
innodb_log_buffer_size=32M
innodb_log_file_size=80M
max_allowed_packet=8M
sql_mode = "STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION"
default-authentication-plugin=mysql_native_password
validate_password_policy=LOW
#connect_time=100
#wait_timeout=28800
#interactive_timeout=28800
bind-address=0.0.0.0
mysql> select user,host from mysql.user;
+---------------+-----------+
| user | host |
+---------------+-----------+
| portal | % |
| mysql.session | localhost |
| mysql.sys | localhost |
| root | localhost |
+---------------+-----------+
4 rows in set (0.01 sec)
mysql> select user, plugin from mysql.user;
+---------------+-----------------------+
| user | plugin |
+---------------+-----------------------+
| root | mysql_native_password |
| mysql.session | mysql_native_password |
| mysql.sys | mysql_native_password |
| portal | mysql_native_password |
+---------------+-----------------------+
4 rows in set (0.01 sec)
- I have created a mysql dump from each database and created a local copy of a mysql 5.7 database to point to within my portal.conf file. Each time I have the same error message:
- I wiped my portal and rebuilt it on a new box. 4.5 is working with mysql 5.7 and then when I upgrade to version 5.0.0.1 and 5.0.28
I get the following:
Reason: liquibase.exception.DatabaseException: Table 'GATEWAY_BUNDLE_TENANT_GATEWAY' already exists [Failed SQL: CREATE TABLE portal.GATEWAY_BUNDLE_TENANT_GATEWAY (UUID VARCHAR(36) NOT NULL, TENANT_ID VARCHAR(255) DEFAULT '' NOT NULL, TENANT_GATEWAY_UUID VARCHAR(255) NOT NULL, GATEWAY_BUNDLE_UUID VARCHAR(255) NOT NULL, SYNC_TIME BIGINT DEFAULT 0 NOT NULL, SYNC_LOG TEXT NULL, STATUS VARCHAR(80) NULL)]
at liquibase.changelog.ChangeSet.execute(ChangeSet.java:605)
at liquibase.changelog.visitor.UpdateVisitor.visit(UpdateVisitor.java:51)
at liquibase.changelog.ChangeLogIterator.run(ChangeLogIterator.java:79)
at liquibase.Liquibase.update(Liquibase.java:214)
at liquibase.Liquibase.update(Liquibase.java:192)
at liquibase.integration.commandline.Main.doMigration(Main.java:1126)
at liquibase.integration.commandline.Main.run(Main.java:184)
at liquibase.integration.commandline.Main.main(Main.java:103)
Caused by: liquibase.exception.DatabaseException: Table 'GATEWAY_BUNDLE_TENANT_GATEWAY' already exists [Failed SQL: CREATE TABLE portal.GATEWAY_BUNDLE_TENANT_GATEWAY (UUID VARCHAR(36) NOT NULL, TENANT_ID VARCHAR(255) DEFAULT '' NOT NULL, TENANT_GATEWAY_UUID VARCHAR(255) NOT NULL, GATEWAY_BUNDLE_UUID VARCHAR(255) NOT NULL, SYNC_TIME BIGINT DEFAULT 0 NOT NULL, SYNC_LOG TEXT NULL, STATUS VARCHAR(80) NULL)]
at liquibase.executor.jvm.JdbcExecutor$ExecuteStatementCallback.doInStatement(JdbcExecutor.java:301)
at liquibase.executor.jvm.JdbcExecutor.execute(JdbcExecutor.java:55)
at liquibase.executor.jvm.JdbcExecutor.execute(JdbcExecutor.java:107)
at liquibase.database.AbstractJdbcDatabase.execute(AbstractJdbcDatabase.java:1273)
at liquibase.database.AbstractJdbcDatabase.executeStatements(AbstractJdbcDatabase.java:1255)
at liquibase.changelog.ChangeSet.execute(ChangeSet.java:568)
... 7 more
Caused by: java.sql.SQLSyntaxErrorException: Table 'GATEWAY_BUNDLE_TENANT_GATEWAY' already exists
at com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:120)
at com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:97)
at com.mysql.cj.jdbc.exceptions.SQLExceptionsMapping.translateException(SQLExceptionsMapping.java:122)
at com.mysql.cj.jdbc.StatementImpl.executeInternal(StatementImpl.java:764)
at com.mysql.cj.jdbc.StatementImpl.execute(StatementImpl.java:648)
at liquibase.executor.jvm.JdbcExecutor$ExecuteStatementCallback.doInStatement(JdbcExecutor.java:299)
... 12 m
Environment
Release : 4.5 , 5.0 , 5.2
Cause
- Customer didn’t take a snapshot of the apim-portal folder from the original portal.
Resolution
1) We brought over the folder /conf and /certs. For the older version of the portal, it had a tenant.json file that was needed and referenced in the portal startup.
2) Compress (tar) this folder and brought it over to the new portal folder. Started the portal up and it came up right away.
below the steps followed to achieve the upgrade success
1. Deploy Portal 4.5.0.1 with MySQL 5.7.27 in the Swarm environment and confirm the Portal is up and functioning.
2. Stop Portal (sudo docker stack rm portal)
3. Export portal databases (see 1))
4. Prepare a mysql 8.0.22 DB server (see B) for /etc/my.cnf config)
5. Imports dump files from 3. to mysql 8.0.22 (see A))
6. Modify conf/portal.conf to point the PORTAL_DATABASE_HOST to mysql 8.0.22
7. start Portal (sudo ./portal.sh) and confirm the Portal 4.5.0.1 works for MySQL 8.0.22
8. Stop Portal
9. Download the Portal 5.0 CR1 tarball and extract it to the Portal installed location (e.g, /opt/apim-portal-4.5.0.1-final)
10. Start the Portal and confirm the Portal is successfully upgraded to Portal 5.0CR1
A) Export and Import command detailed
mysqldump -h localhost -u root -p7layer -f --routines --add-drop-database --databases analytics > analytics.sql
mysqldump -h localhost -u root -p7layer -f --routines --add-drop-database --databases apim_otk_db > apim_otk_db.sql
mysqldump -h localhost -u root -p7layer -f --routines --add-drop-database --databases druid > druid.sql
mysqldump -h localhost -u root -p7layer -f --routines --add-drop-database --databases integration_core > integration_core.sql
mysqldump -h localhost -u root -p7layer -f --routines --add-drop-database --databases integration_runscope > integration_runscope.sql
mysqldump -h localhost -u root -p7layer -f --routines --add-drop-database --databases portal > portal.sql
mysqldump -h localhost -u root -p7layer -f --routines --add-drop-database --databases rbac > rbac.sql
mysqldump -h localhost -u root -p7layer -f --routines --add-drop-database --databases tenant_provisioning > tenant_provisioning.sql
--
mysql -h localhost -u root -p7layer < apim_otk_db.sql;
mysql -h localhost -u root -p7layer < analytics.sql
mysql -h localhost -u root -p7layer < druid.sql
mysql -h localhost -u root -p7layer < integration_core.sql
mysql -h localhost -u root -p7layer < integration_runscope.sql
mysql -h localhost -u root -p7layer < portal.sql
mysql -h localhost -u root -p7layer < tenant_provisioning.sql
mysql -h localhost -u root -p7layer < rbac.sql
B) sample of /etc/my.cnf for mysql8.0.22
[client]
default-character-set=utf8
[mysqld]
default-storage-engine = innodb
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid
character-set-server=utf8mb4
innodb_log_buffer_size=32M
innodb_log_file_size=80M
max_allowed_packet=8M
sql_mode="STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION"
group_concat_max_len=512000
ssl_ca=/opt/mysql-ssl/ca.pem
ssl_cert=/opt/mysql-ssl/server-cert.pem
ssl_key=/opt/mysql-ssl/server-key.pem
Additional Information
To continue the upgrade
Feedback
