Mysql setup job fails if require_secure_transport is set to ON on mysql server

[atul-chegg]

We are using RDS Aurora for mysql backend. Our server parameter has require_secure_transport set to ON. Mysql setup job fails with the following error

atul.atri@C02FD3A3MD6M iac-datahub-db % kubectl logs  datahub-mysql-setup-job-9d7fl -n datahub
2022/08/30 12:51:12 Waiting for: tcp://<redacted>:3306
2022/08/30 12:51:12 Connected to tcp://<redacted>:3306
-- create datahub database
CREATE DATABASE IF NOT EXISTS datahub CHARACTER SET utf8mb4 COLLATE utf8mb4_bin;
USE datahub;

-- create metadata aspect table
create table if not exists metadata_aspect_v2 (
  urn                           varchar(500) not null,
  aspect                        varchar(200) not null,
  version                       bigint(20) not null,
  metadata                      longtext not null,
  systemmetadata                lo

ngtext,
  createdon                     datetime(6) not null,
  createdby                     varchar(255) not null,
  createdfor                    varchar(255),
  constraint pk_metadata_aspect_v2 primary key (urn,aspect,version)
);

-- create default records for datahub user if not exists
DROP TABLE if exists temp_metadata_aspect_v2;
CREATE TABLE temp_metadata_aspect_v2 LIKE metadata_aspect_v2;
INSERT INTO temp_metadata_aspect_v2 (urn, aspect, version, metadata, createdon, createdby) VALUES(
  'urn:li:corpuser:datahub',
  'corpUserInfo',
  0,
  '{"displayName":"Data Hub","active":true,"fullName":"Data Hub","email":"[email protected]"}',
  now(),
  'urn:li:corpuser:__datahub_system'
), (
  'urn:li:corpuser:datahub',
  'corpUserEditableInfo',
  0,
  '{"skills":[],"teams":[],"pictureLink":"https://raw.githubusercontent.com/datahub-project/datahub/master/datahub-web-react/src/images/default_avatar.png"}',
  now(),
  'urn:li:corpuser:__datahub_system'
);
-- only add default records if metadata_aspect is empty
INSERT INTO metadata_aspect_v2
SELECT * FROM temp_metadata_aspect_v2
WHERE NOT EXISTS (SELECT * from metadata_aspect_v2);
DROP TABLE temp_metadata_aspect_v2;

-- create metadata index table
CREATE TABLE IF NOT EXISTS metadata_index (
 `id` BIGINT NOT NULL AUTO_INCREMENT,
 `urn` VARCHAR(200) NOT NULL,
 `aspect` VARCHAR(150) NOT NULL,
 `path` VARCHAR(150) NOT NULL,
 `longVal` BIGINT,
 `stringVal` VARCHAR(200),
 `doubleVal` DOUBLE,
 CONSTRAINT id_pk PRIMARY KEY (id),
 INDEX longIndex (`urn`,`aspect`,`path`,`longVal`),
 INDEX stringIndex (`urn`,`aspect`,`path`,`stringVal`),
 INDEX doubleIndex (`urn`,`aspect`,`path`,`doubleVal`)
);
ERROR 3159 (HY000): Connections using insecure transport are prohibited while --require_secure_transport=ON.
2022/08/30 12:51:12 Command exited with error: exit status 1

Mysql setup job was successful after I set require_secure_transport to OFF.

[atul-chegg]

I solved it the following way.

1. Create a mysql client conf file

atul.atri@C02FD3A3MD6M iac-datahub % cat conf/my.cnf 
[client]
ssl=true

2. Create a config map. I am using terraform so I used this resource

resource "kubernetes_config_map" "mysql_client_config_file" {
  metadata {
    name      = "mysql-client-config-file"
    namespace = "datahub"
  }

  data = {
    "my.cnf" = "${file("${path.module}/conf/my.cnf")}"
  }
}

3. Now mount this config map to /etc/mysql/my.cnf in mysql setup job pod. My Values.yml for mysql setup job looks like the following

mysqlSetupJob:
  enabled: true
  image:
    repository: acryldata/datahub-mysql-setup
    tag: "v0.8.43"
  podSecurityContext:
    fsGroup: 1000
  securityContext:
    runAsUser: 1000
  extraVolumes:
    - name: secrets-store-inline
      csi:
        driver: secrets-store.csi.k8s.io
        readOnly: true
        volumeAttributes:
          secretProviderClass: datahub-secrets
    - name: mysql-client-config-file
      configMap:
        name: mysql-client-config-file
  extraVolumeMounts:
    - name: secrets-store-inline
      mountPath: "/mnt/secrets-store"
      readOnly: true
    - name: mysql-client-config-file
      mountPath: "/etc/mysql"
      readOnly: true
  serviceAccount: datahub-mysql-setup-job

[atul-chegg]

This is probably because mysql-setuo-job does not use same ENV variables used by GMS service. It should also use same env variables as GMS. mysql-setuo-job uses the following env variables

MYSQL_USERNAME
MYSQL_PASSWORD
MYSQL_HOST
MYSQL_PORT

While GMS service uses

EBEAN_DATASOURCE_USERNAME
EBEAN_DATASOURCE_PASSWORD
EBEAN_DATASOURCE_HOST
EBEAN_DATASOURCE_URL
EBEAN_DATASOURCE_DRIVER

It should also use URL and Driver that will give us more flexibility to modify database connection properties. Driver is also important because we use aws-secrets manager-jdbc driver.

[github-actions[bot]]

This issue is stale because it has been open for 30 days with no activity. If you believe this is still an issue on the latest DataHub release please leave a comment with the version that you tested it with. If this is a question/discussion please head to https://slack.datahubproject.io. For feature requests please use https://feature-requests.datahubproject.io

[github-actions[bot]]

This issue was closed because it has been inactive for 30 days since being marked as stale.