Uploaded image for project: 'Artifactory Binary Repository'
  1. Artifactory Binary Repository
  2. RTFACT-27454

Artifactory cannot create tables when MySQL 8 variable sql_require_primary_key=true

    XMLWordPrintable

    Details

    • Type: Bug
    • Status: Open
    • Resolution: Unresolved
    • Affects Version/s: 7.12.8, 7.41.6
    • Fix Version/s: None
    • Component/s: Database
    • Labels:
      None
    • Environment:

      Artifactory Versions tested: 7.41.7, 7.12.8

      Database: MySQL 8.0.30

      OS: Ubuntu 18.04

      Is this HA? No, single Artifactory with local MySQL. Also reproduced on external MySQL

      On-Prem or SaaS? On-Prem

      Description

      Problem Description:

      If a MySQL database has a flag enabled, Artifactory will fail to create DB tables as some of the SQL lacks a now necessary Primary key. The flag is the sql_require_primary_key , it's disabled by default. 

      Some users have to enable this flag to meet security requirements. 

      When this flag is enabled, Artifactory will fail to create its initial database tables. Some of the SQL statements lack the "PRIMARY KEY" SQL statement, so the MySQL DB fails the statement and the whole setup fails.

      During some upgrades, such as between 7.12 and 7.41, the new tables created also lack a Primary Key, so an upgrade can also fail unexpectedly.

      Impact:

      Some users have to enable this flag to meet security requirements and cannot turn it off. This blocks the only known workaround: To work around the issue you need to temporarily disable the flag so Artifactory can run all its SQL statements. 

      What is the expected behavior?

      The Artifactory Release Notes mentions this flag in 7.29, yet upgrades and installations past 7.29 fail when it's enabled. Expected behavior would be that Artifactory can create its database tables whether or not the Require Primary Key variable is enabled or not.

      Steps to Reproduce:

      1] Install MySQL 8 and in its mysql.cnf file add the variable:

      [Ubuntu 18.04 uses */etc/mysql/mysql.cnf*] 

      [mysqld]
      sql_require_primary_key=true 

      Don't forget to reboot MySQL after setting this variable. To test the variable being enabled, create a test database and try to create a table without a primary key:

      CREATE DATABASE test CHARACTER SET utf8 COLLATE utf8_bin;
      use test;
      CREATE TABLE test (test_column_id BIGINT NOT NULL, test_column_value BIGINT NOT NULL);
      
      ##Means the flag is enabled##
      ERROR 3750 (HY000): Unable to create or change a table without a primary key, when the system variable 'sql_require_primary_key' is set.[...]
      

      2] Create the Artifactory MySQL database following the steps on our wiki.

      3] Link Artifactory to the MySQL DB, add the JDBC driver as normal. On startup, during the DB Schema creation steps, an error happens on one of the tables:

      2022-09-26T20:13:02.962Z [jfrt ] [INFO ] [d38322e379370d17] [.BasicConfigurationManager:186] [Catalina-utility-3  ] - Artifactory (jfrt) service initialization started. Version: 7.41.10 Revision: 74110900 PID: 12062 Home: /opt/jfrog/artifactory
      [...]
      
      2022-09-26T20:13:03.886Z [jfrt ] [INFO ] [d38322e379370d17] [d.i.DbInitializationManager:69] [Catalina-utility-3  ] - ***Creating database schema***
      
      2022-09-26T20:13:04.849Z [jfrt ] [WARN ] [d38322e379370d17] [o.j.s.u.DbStatementUtils:62   ] [Catalina-utility-3  ] - Failed to execute query: Unable to create or change a table without a primary key, when the system variable 'sql_require_primary_key' is set. Add a primary key to the table or unset this variable to avoid this message. Note that tables without a primary key can cause performance problems in row-based replication, so please consult your DBA before changing this setting.:
      CREATE TABLE node_events_tmp (
           tmp_event_id   BIGINT    NOT NULL,
           timestamp  BIGINT        NOT NULL,
           event_type SMALLINT      NOT NULL,
           path       VARCHAR(1344) NOT NULL,
           hints      VARCHAR(4096)
      )
      
      2022-09-26T20:13:04.853Z [jfrt ] [ERROR] [d38322e379370d17] [d.i.DbInitializationManager:79] [Catalina-utility-3  ] - 
      java.sql.SQLException: Unable to create or change a table without a primary key, when the system variable 'sql_require_primary_key' is set. Add a primary key to the table or unset this variable to avoid this message. Note that tables without a primary key can cause performance problems in row-based replication, so please consult your DBA before changing this setting.
      	at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:965)
      	at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3933)
      	at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3869)
      	at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:2524)
      	at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:2675)
      	[...] 

      This same error can happen if you upgrade Artifactory (Tested 7.12 -> 7.41) with this flag enabled. The node_events_tmp table SQL statement lacks a Primary Key.

      Workaround:

      Disable the flag in MySQL and then do the Artifactory setup. The Artifactory will create the tables as it normally does, and the flag can be re-enabled after it's finished. This isn't super workable:

      • It requires complete downtime as MySQL must be rebooted to change the variable
      • Upgrading Artifactory will likely result in a failure because of how the variable works unless you disable the flag for upgrades too

      Environment details:

      • Artifactory Versions tested: 7.41.7, 7.12.8
      • Database: MySQL 8.0.30
      • OS: Ubuntu 18.04
      • Is this HA? No, single Artifactory with local MySQL. Also reproduced on external MySQL
      • On-Prem or SaaS? On-Prem

        Attachments

          Activity

              People

              Assignee:
              Unassigned
              Reporter:
              patrickr Patrick Russell
              Votes:
              0 Vote for this issue
              Watchers:
              1 Start watching this issue

                Dates

                Created:
                Updated: