Error connecting to Gateway SSG Database using Jdbc connection in Policy Manager

[Muthugomu Rathnamswamy]

Hello, 

I am using Docker Container gateway 10.1 that use a Docker Container mysql 8.0 as well  for Gateway Database.

However, when I try to configure JDBC connection of the same exact mysql server, i am getting the attached error.

Has anyone able to configure gateway db successfully using Policy manager 10.1 on Docker Gateway?  If yes, would appreciate if you could share your inputs on what I may be missing.

[Barry Stern]

Muthu,

Can you provide your database configuration your using when deploying gateway chart? 

Check that the gateway user has pluggin for mysql_native_password

select user,authentication_string,plugin from mysql.user where user = 'gateway';

check to see  if the gateway user has caching_sha2_password for the plugin if so you can alter the user to use mysql_native_password

ALTER USER 'gateway'@'%' IDENTIFIED WITH mysql_native_password BY 'password';

or create another user with mysql_native_password

[Muthugomu Rathnamswamy]

Thanks a lot Barry for your quick response.  Please find my docker-compose below attached.  

ersion: "3.8"

services:

layer7:

image: caapim/gateway

mem_limit: 4g

cpus: 2

volumes:

- ./license.xml:/opt/SecureSpan/Gateway/node/default/etc/bootstrap/license/license.xml

expose:

- "8777"

- "2142"

container_name: layer7-cigna

ports:

- "8080:8080"

- "7443:8443"

- "9443:9443"

environment:

ACCEPT_LICENSE: "true"

SSG_CLUSTER_COMMAND: "create"

SSG_CLUSTER_HOST: "ssg101.l7tech.com"

SSG_CLUSTER_PASSWORD: "7layer"

SSG_DATABASE_TYPE: "mysql"

SSG_DATABASE_HOST: "l7mysql"

SSG_DATABASE_PORT: "3306"

SSG_DATABASE_NAME: "ssg"

SSG_DATABASE_USER: "gateway"

SSG_DATABASE_PASSWORD: "7layer"

SSG_DATABASE_JDBC_URL: "jdbc:mysql://l7mysql:3306/ssg?useSSL=false"

SSG_DATABASE_WAIT_TIMEOUT: "3600"

SSG_DATABASE_ADMIN_USER: "root"

SSG_DATABASE_ADMIN_PASS: "7layer"

SSG_ADMIN_USERNAME: "admin"

SSG_ADMIN_PASSWORD: "7layer"

SSG_INTERNAL_SERVICES: "restman"

links:

- l7mysql

l7mysql:

image: mysql:8.0.29

mem_limit: 2g

container_name: l7mysql

ports:

- "3306:3306"

environment:

- MYSQL_ROOT_PASSWORD=7layer

- MYSQL_USER=gateway

- MYSQL_PASSWORD=7layer

- MYSQL_DATABASE=ssg

command:

- "--character-set-server=utf8mb3"

- "--innodb_log_buffer_size=32M"

- "--innodb_log_file_size=80M"

- "--max_allowed_packet=20M"

- "--log_bin_trust_function_creators=1"

- "--default-authentication-plugin=mysql_native_password"

I have the latest Gateway Image and now it says "Connections to MYSQL Community servers are not supported.  Please contact MYSQL to obtain Enterprise or Commercial Version" irrespective of what mysql driver I choose.  Please find the screen shot attched.  Basically I am trying to connect to ssg db only using jdbc connection from policy manager.

[Barry Stern]

Muthu,

There were updates to mysql drivers and with 10.1 cr2. to use Mysql community instead of enterprise you need to change the driver class. Thesis in the release notes.

Try using com.mysql.cj.jdbc.Driver 

MySQL Connector Java Library Upgrade

The mysql-connector-java library is upgraded from 5.1.46 to 8.0.26 version. The driver class, com.mysql.cj.jdbc.Driver , is added to the supported JDBC driver classes list. For more information, see JDBC Cluster Properties and Set JDBC Connection Properties.

[Muthugomu Rathnamswamy]

Thanks a lot Barry for your quick response.  As you can see from the above screen shots, I have tried with all the mysql jdbc driver, none of them worked.

Also note that I am using Docker container gateway, meaning the image should have everything, not sure how it got missed.

I will review the above documentation and explore the same to see if any of those tweaks works.

If you know of any specific Docker Container Gateway version that works with the jdbc connection, then please let me know, I can pull that specific version to play around.

[Muthugomu Rathnamswamy]

I also tried running this SQL as per your suggestion and it just cannot connect.

ALTER USER 'gateway'@'%' IDENTIFIED WITH mysql_native_password BY 'password';

Appreciate if you can help with a solution to connect to ssg db from policy manager using jdbc connection.

[Muthugomu Rathnamswamy]

Finally it worked after changing the jdbc connection string for community edition, please find the screen shot attached.