[jira] [Created] (YARN-11810) Fix SQL script in SQLServer/FederationStateStoreTables.sql

["Peter Szucs (Jira)" <ji...@apache.org>]

Peter Szucs created YARN-11810:
----------------------------------

             Summary: Fix SQL script in SQLServer/FederationStateStoreTables.sql
                 Key: YARN-11810
                 URL: https://issues.apache.org/jira/browse/YARN-11810
             Project: Hadoop YARN
          Issue Type: Bug
          Components: federation
    Affects Versions: 3.4.1
            Reporter: Peter Szucs
            Assignee: Peter Szucs


The SQLServer/FederationStateStoreTables.sql script contains duplicated create statements and SQL syntax errors which makes _TestFederationSQLServerScriptAccuracy#checkSqlServerScriptAccuracy_ unit test fail.

 

I see 2 issues which cause this test failure:
 # *Duplicated create statements*
 ** the test creates a SQLServerFederationStateStore class, which extracts and stores the "CREATE TABLE" statements from FederationStateStoreTables.sql with the [extractCreateTableSQL method|https://github.com/apache/hadoop/blob/trunk/hadoop-yarn-project/hadoop-yarn/hadoop-yarn-server/hadoop-yarn-server-common/src/test/java/org/apache/hadoop/yarn/server/federation/store/impl/SQLServerFederationStateStore.java#L43].
 ** then iterates through the collected statements and executes them as a prepared statement [here|https://github.com/apache/hadoop/blob/trunk/hadoop-yarn-project/hadoop-yarn/hadoop-yarn-server/hadoop-yarn-server-common/src/test/java/org/apache/hadoop/yarn/server/federation/store/sql/TestFederationSQLServerScriptAccuracy.java#L53]
 ** the create statements for _masterKeys_ and _delegationTokens_ tables are duplicated in the script, so these will be collected and attempted to be executed twice, which results in the following error during the test runs:

{code:java}
[ERROR] Tests run: 1, Failures: 0, Errors: 1, Skipped: 0, Time elapsed: 0.991 s <<< FAILURE! - in org.apache.hadoop.yarn.server.federation.store.sql.TestFederationSQLServerScriptAccuracy
[ERROR] checkSqlServerScriptAccuracy(org.apache.hadoop.yarn.server.federation.store.sql.TestFederationSQLServerScriptAccuracy)  Time elapsed: 0.91 s  <<< ERROR!
java.sql.SQLSyntaxErrorException: 
object name already exists: MASTERKEYS in statement [CREATE TABLE masterKeys(
            keyId BIGINT NOT NULL,
            masterKey VARCHAR(1024) NOT NULL,
            CONSTRAINT pk_keyId PRIMARY KEY
            (
                keyId
            )
        )]
    at org.hsqldb.jdbc.JDBCUtil.sqlException(Unknown Source)
    at org.hsqldb.jdbc.JDBCUtil.sqlException(Unknown Source)
    at org.hsqldb.jdbc.JDBCPreparedStatement.fetchResult(Unknown Source)
    at org.hsqldb.jdbc.JDBCPreparedStatement.execute(Unknown Source)
    at com.zaxxer.hikari.pool.ProxyPreparedStatement.execute(ProxyPreparedStatement.java:44){code}
**
 # *Syntax errors in the SQL script*
 ** the create statements for _sequenceTable_ and versions are missing a comma before the constraints are defined here:

{code:java}
CREATE TABLE [dbo].[sequenceTable](
    sequenceName VARCHAR(255) NOT NULL,
    nextVal bigint NOT NULL
    CONSTRAINT [pk_sequenceName] PRIMARY KEY
    (
        [sequenceName]
    )
){code}
and here:

{code:java}
CREATE TABLE [dbo].[versions](
    fedVersion VARBINARY(1024) NOT NULL,
    versionComment VARCHAR(255) NOT NULL
    CONSTRAINT [pk_fedVersion] PRIMARY KEY
    (
        [fedVersion]
    )
){code}



--
This message was sent by Atlassian Jira
(v8.20.10#820010)

---------------------------------------------------------------------
To unsubscribe, e-mail: yarn-issues-unsubscribe@hadoop.apache.org
For additional commands, e-mail: yarn-issues-help@hadoop.apache.org