ssg database creation fails during retry

[Laurent LANDREAU]

Hello,
I am configuring API layer7 v10.1 (software gateway). After a first setup.sh that has failed half way, I have ran the following commands in MySQL: 
drop database ssg ;
drop user 'gateway' ;
drop user 'gateway'@'localhost';
drop user 'gateway'@'localhost6';
drop user 'gateway'@'localhost6.localdomain6';
drop user 'gateway'@'localhost.localdomain';

I have check that there is no left over
select host,user from mysql.user;
+-----------+------------------+
| host        | user                 |
+-----------+------------------+
| localhost | mysql.infoschema |
| localhost | mysql.session |
| localhost | mysql.sys |
| localhost | root |
+-----------+------------------+


And then re-ran setup.sh with a valid root user and password, I get the following.

---------------------
Configuration Results
---------------------
An error occurred during configuration.
Error creating database when saving configuration 'Unable to create database'


in mysqld.log, i see the following
2021-12-06T07:02:12.624191Z 0 [System] [MY-010910] [Server] /usr/sbin/mysqld: Shutdown complete (mysqld 8.0.26) MySQL Community Server - GPL.
2021-12-06T07:02:51.853294Z 0 [Warning] [MY-010139] [Server] Changed limits: max_open_files: 10000 (requested 13125)
2021-12-06T07:02:51.853310Z 0 [Warning] [MY-010142] [Server] Changed limits: table_open_cache: 3682 (requested 4000)
2021-12-06T07:02:52.092231Z 0 [System] [MY-010116] [Server] /usr/sbin/mysqld (mysqld 8.0.26) starting as process 27521
2021-12-06T07:02:52.106219Z 1 [System] [MY-013576] [InnoDB] InnoDB initialization has started.
2021-12-06T07:02:52.316110Z 1 [System] [MY-013577] [InnoDB] InnoDB initialization has ended.
2021-12-06T07:02:52.546726Z 0 [Warning] [MY-013746] [Server] A deprecated TLS version TLSv1 is enabled for channel mysql_main
2021-12-06T07:02:52.546938Z 0 [Warning] [MY-013746] [Server] A deprecated TLS version TLSv1.1 is enabled for channel mysql_main
2021-12-06T07:02:52.548654Z 0 [Warning] [MY-010068] [Server] CA certificate ca.pem is self signed.
2021-12-06T07:02:52.548953Z 0 [System] [MY-013602] [Server] Channel mysql_main configured to support TLS. Encrypted connections are now supported for this channel.
2021-12-06T07:02:52.714255Z 0 [System] [MY-010931] [Server] /usr/sbin/mysqld: ready for connections. Version: '8.0.26' socket: '/var/lib/mysql/mysql.sock' port: 3306 MySQL Community Server - GPL.
2021-12-06T07:02:52.714302Z 0 [System] [MY-011323] [Server] X Plugin ready for connections. Bind-address: '0.0.0.0' port: 33060, socket: /var/run/mysqld/mysqlx.sock
2021-12-06T07:04:35.948923Z 10 [Warning] [MY-010236] [Server] Following users were specified in ALTER USER IF EXISTS but they do not exist. Corresponding entry in binary log used default authentication plugin 'caching_sha2_password' to rewrite authentication information (if any) for them: 'gateway'@'%'
2021-12-06T07:04:35.952509Z 10 [Warning] [MY-010236] [Server] Following users were specified in ALTER USER IF EXISTS but they do not exist. Corresponding entry in binary log used default authentication plugin 'caching_sha2_password' to rewrite authentication information (if any) for them: 'gateway'@'localhost'
2021-12-06T07:04:35.955991Z 10 [Warning] [MY-010236] [Server] Following users were specified in ALTER USER IF EXISTS but they do not exist. Corresponding entry in binary log used default authentication plugin 'caching_sha2_password' to rewrite authentication information (if any) for them: 'gateway'@'localhost6'
2021-12-06T07:04:35.959447Z 10 [Warning] [MY-010236] [Server] Following users were specified in ALTER USER IF EXISTS but they do not exist. Corresponding entry in binary log used default authentication plugin 'caching_sha2_password' to rewrite authentication information (if any) for them: 'gateway'@'localhost.localdomain'
2021-12-06T07:04:35.962886Z 10 [Warning] [MY-010236] [Server] Following users were specified in ALTER USER IF EXISTS but they do not exist. Corresponding entry in binary log used default authentication plugin 'caching_sha2_password' to rewrite authentication information (if any) for them: 'gateway'@'localhost6.localdomain6'

--------------------------------------------------------- Questions
1 How can I check what is the error?
2) Or is there anything else I need to delete to allow a rerun? 


This is the config recap:

Java VM
Java VM Path = /usr/lib/jvm/java-11-openjdk-11.0.12.0.7-0.el8_4.x86_64
Java VM Memory Allocation = 8192

Database Connection
Database Host = localhost
Database Port = 3306
Database Name = ssg
Database Username = gateway
Administrative Database Username = root

Layer7 API Gateway Policy Manager Administrative Account
Policy Manager Username = pmadmin

Cluster Configuration
Cluster Host = NFUJSIT120.corporate.urbanutilities.internal


Thank you
Laurent LANDREAU

[Enzo Fusca]

Hi Laurent,

I have the same problem and I did not found any solution.

Did you solve this issue?

Regards,

Enzo

[Ben Urbanski]

Hello @Enzo Fusca,

I recommend opening a support case to get help with this problem.

Regards,

------------------------------
Ben Urbanski
Product Manager, API Gateway
Layer7 API Management
------------------------------

[Laurent LANDREAU]

Hello - I did not resolve the issue.

In that environment, I have created a database with a different name (ssg10).

Now that I have upgraded to API v11, i have backed up the ssg10 db, created the ssg database, and imported the content of ssg10 into ssg.

Regards, Laurent

Regards, Laurent

[Maurizio Garzelli]

Hello @Enzo Fusca and @Laurent LANDREAU,

the reason that you are having this issue is that you have deleted the Gateway user, and the gateway knows that there should be that user registered. As you can see from the logs, it tries to ALTER the user so that it will use the correct authentication system, but it cannot find it so it fails. 

The ssg database needs to have a user (usually Gateway) for accessing it. but I can see from your note at the top, you have deleted those users

What was the reason for deleting that user?

thanks

------------------------------
Maurizio Garzelli
APIIDA
APIIDA Chief Technology Advisor APIM
[e-mail]
https://apiida.com
------------------------------

[Enzo Fusca]

Hi @Maurizio Garzelli,

in my case the problem was due to the MySQL default authentication plugin.

I have solved by modifying the MySQL users used by Layer7 (gateway@localhost, gateway@'%', ...)authentication plugin from "mysql_native_password" to "caching_sha2_password".

Example

ALTER USER 'gateway'@'localhost' IDENTIFIED WITH caching_sha2_password BY '7layer';

And restarting mysql service.

Regards,

Enzo

[Maurizio Garzelli]

Hi @Enzo Fusca

indeed, the mysql_native_password is deprecated and in 11 we started seeing a massive amount of errors in the logs, 

------------------------------
Maurizio Garzelli
APIIDA
APIIDA Chief Technology Advisor APIM
[e-mail]
https://apiida.com
------------------------------