You are viewing a plain text version of this content. The canonical link for it is here.
Posted to common-issues@hadoop.apache.org by "slfan1989 (via GitHub)" <gi...@apache.org> on 2023/02/13 02:36:36 UTC
[GitHub] [hadoop] slfan1989 commented on pull request #5244: YARN-11349. [Federation] Router Support DelegationToken With SQL.
slfan1989 commented on PR #5244:
URL: https://github.com/apache/hadoop/pull/5244#issuecomment-1427244187
### SQL Server 2008 R2 Enterprise
> select @@version
```
Microsoft SQL Server 2008 R2 (SP3) - 10.50.6000.34 (X64)
Aug 19 2014 12:21:34
Copyright (c) Microsoft Corporation
Enterprise Edition (64-bit) on Windows NT 6.3 <X64> (Build 9600: )
```
> FederationStateStoreDatabase.sql
```
master> IF DB_ID ( '[FederationStateStore]') IS NOT NULL
DROP DATABASE [FederationStateStore]
[2023-01-25 17:22:05] completed in 41 ms
master> CREATE database FederationStateStore
[2023-01-25 17:22:05] completed in 106 ms
```
> FederationStateStoreUser.sql
```
master> USE [FederationStateStore]
[2023-01-25 17:23:20] [S0001][5701] Changed database context to 'FederationStateStore'.
[2023-01-25 17:23:20] completed in 41 ms
master> CREATE LOGIN FederationUser with password = 'Federation@Password', default_database=[FederationStateStore]
[2023-01-25 17:23:21] completed in 51 ms
master> CREATE USER FederationUser FOR LOGIN FederationUser WITH default_schema=dbo
[2023-01-25 17:23:21] completed in 41 ms
master> EXEC sp_addrolemember 'db_owner', 'FederationUser'
[2023-01-25 17:23:21] completed in 41 ms
```
> FederationStateStoreTables.sql
```
....
Table delegationTokens exists, no operation required...
[2023-01-25 17:24:34] completed in 44 ms
FederationStateStore> IF NOT EXISTS ( SELECT * FROM [FederationStateStore].sys.tables
WHERE name = 'sequenceTable'
AND schema_id = SCHEMA_ID('dbo'))
BEGIN
PRINT 'Table sequenceTable does not exist, create it...'
SET ANSI_NULLS ON
SET QUOTED_IDENTIFIER ON
SET ANSI_PADDING ON
CREATE TABLE [dbo].[sequenceTable](
sequenceName VARCHAR(255) NOT NULL,
nextVal bigint NOT NULL
CONSTRAINT [pk_sequenceName] PRIMARY KEY
(
[sequenceName]
)
)
SET ANSI_PADDING OFF
PRINT 'Table sequenceTable created.'
END
ELSE
PRINT 'Table sequenceTable exists, no operation required...'
Table sequenceTable does not exist, create it...
Table sequenceTable created.
```
```
USE [FederationStateStore]
GO
SELECT Name FROM SysObjects Where XType='U' ORDER BY Name
applicationsHomeSubCluster
delegationTokens
masterKeys
membership
policies
reservationsHomeSubCluster
sequenceTable
```
> FederationStateStoreStoredProcs.sql
```
[2023-01-25 17:53:48] completed in 39 ms
FederationStateStore> CREATE PROCEDURE [dbo].[sp_deleteDelegationToken]
@sequenceNum_IN bigint,
@rowCount_OUT int OUTPUT
AS BEGIN
DECLARE @errorMessage nvarchar(4000)
BEGIN TRY
BEGIN TRAN
DELETE FROM [dbo].[delegationTokens]
WHERE [sequenceNum] = @sequenceNum_IN;
SELECT @rowCount_OUT = @@ROWCOUNT;
COMMIT TRAN
END TRY
BEGIN CATCH
ROLLBACK TRAN
SET @errorMessage = dbo.func_FormatErrorMessage(ERROR_MESSAGE(), ERROR_LINE())
/* raise error and terminate the execution */
RAISERROR(@errorMessage, --- Error Message
1, -- Severity
-1 -- State
) WITH log
END CATCH
END;
[2023-01-25 17:53:48] completed in 44 ms
```
```
USE [FederationStateStore]
GO
SELECT Name FROM SysObjects Where XType='P' ORDER BY Name
sp_addApplicationHomeSubCluster
sp_addDelegationToken
sp_addMasterKey
sp_addReservationHomeSubCluster
sp_deleteApplicationHomeSubCluster
sp_deleteDelegationToken
sp_deleteMasterKey
sp_deleteReservationHomeSubCluster
sp_deregisterSubCluster
sp_getApplicationHomeSubCluster
sp_getApplicationsHomeSubCluster
sp_getDelegationToken
sp_getMasterKey
sp_getPoliciesConfigurations
sp_getPolicyConfiguration
sp_getReservationHomeSubCluster
sp_getReservationsHomeSubCluster
sp_getSubCluster
sp_getSubClusters
sp_registerSubCluster
sp_setPolicyConfiguration
sp_subClusterHeartbeat
sp_updateApplicationHomeSubCluster
sp_updateDelegationToken
sp_updateReservationHomeSubCluster
```
> dropStoreProcedures.sql
```
...
FederationStateStore> IF OBJECT_ID ('[sp_updateDelegationToken]', 'P') IS NOT NULL
DROP PROCEDURE [sp_updateDelegationToken];
[2023-01-25 18:05:27] completed in 46 ms
FederationStateStore> IF OBJECT_ID ('[sp_deleteDelegationToken]', 'P') IS NOT NULL
DROP PROCEDURE [sp_deleteDelegationToken];
[2023-01-25 18:05:27] completed in 40 ms
FederationStateStore> SELECT Name FROM SysObjects Where XType='P' ORDER BY Name
[2023-01-25 18:06:12] 0 rows retrieved in 53 ms (execution: 41 ms, fetching: 12 ms)
```
> dropTables.sql
```
.....
[2023-01-25 18:08:47] completed in 43 ms
FederationStateStore> IF OBJECT_ID ( '[masterKeys]', 'U' ) IS NOT NULL
DROP TABLE [masterKeys];
[2023-01-25 18:08:47] completed in 41 ms
FederationStateStore> IF OBJECT_ID ( '[delegationTokens]', 'U' ) IS NOT NULL
DROP TABLE [delegationTokens];
[2023-01-25 18:08:47] completed in 136 ms
FederationStateStore> IF OBJECT_ID ( '[sequenceTable]', 'U' ) IS NOT NULL
DROP TABLE [sequenceTable];
[2023-01-25 18:08:47] completed in 41 ms
FederationStateStore> SELECT Name FROM SysObjects Where XType='U' ORDER BY Name
[2023-01-25 18:10:04] 0 rows retrieved in 52 ms (execution: 42 ms, fetching: 10 ms)
```
> dropDatabase.sql
```
FederationStateStore> IF DB_ID ( '[FederationStateStore]') IS NOT NULL
DROP DATABASE [FederationStateStore]
[2023-01-25 18:14:06] completed in 39 ms
```
> dropUser.sql
```
FederationStateStore> DROP USER FederationUser
[2023-01-25 18:15:21] completed in 40 ms
FederationStateStore> DROP LOGIN FederationUser
[2023-01-25 18:15:21] completed in 65 ms
```
### SQL Server 2012 Enterprise
> select @@Version
```
Microsoft SQL Server 2012 (SP4) (KB4018073) - 11.0.7001.0 (X64)
Aug 15 2017 10:23:29
Copyright (c) Microsoft Corporation
Enterprise Edition: Core-based Licensing (64-bit) on Windows NT 6.3 <X64> (Build 9600: ) (Hypervisor)
```
> FederationStateStoreDatabase.sql
```
master> IF DB_ID ( '[FederationStateStore]') IS NOT NULL
DROP DATABASE [FederationStateStore]
[2023-02-12 22:50:39] completed in 47 ms
master> CREATE database FederationStateStore
[2023-02-12 22:50:40] completed in 262 ms
```
> FederationStateStoreUser.sql
```
master> USE [FederationStateStore]
[2023-02-12 22:51:21] [S0001][5701] Changed database context to 'FederationStateStore'.
[2023-02-12 22:51:21] completed in 56 ms
master> CREATE LOGIN FederationUser with password = 'Federation@Password', default_database=[FederationStateStore]
[2023-02-12 22:51:21] completed in 49 ms
master> CREATE USER FederationUser FOR LOGIN FederationUser WITH default_schema=dbo
[2023-02-12 22:51:22] completed in 49 ms
master> EXEC sp_addrolemember 'db_owner', 'FederationUser'
[2023-02-12 22:51:22] completed in 48 ms
```
> FederationStateStoreTables.sql
```
.....
[2023-02-12 22:52:21] completed in 56 ms
FederationStateStore> IF NOT EXISTS ( SELECT * FROM [FederationStateStore].sys.tables
WHERE name = 'sequenceTable'
AND schema_id = SCHEMA_ID('dbo'))
BEGIN
PRINT 'Table sequenceTable does not exist, create it...'
SET ANSI_NULLS ON
SET QUOTED_IDENTIFIER ON
SET ANSI_PADDING ON
CREATE TABLE [dbo].[sequenceTable](
sequenceName VARCHAR(255) NOT NULL,
nextVal bigint NOT NULL
CONSTRAINT [pk_sequenceName] PRIMARY KEY
(
[sequenceName]
)
)
SET ANSI_PADDING OFF
PRINT 'Table sequenceTable created.'
END
ELSE
PRINT 'Table sequenceTable exists, no operation required...'
Table sequenceTable does not exist, create it...
Table sequenceTable created.
USE [FederationStateStore]
GO
SELECT Name FROM SysObjects Where XType='U' ORDER BY Name
FederationStateStore> SELECT Name FROM SysObjects Where XType='U' ORDER BY Name
[2023-02-12 22:53:09] 7 rows retrieved starting from 1 in 72 ms (execution: 54 ms, fetching: 18 ms)
applicationsHomeSubCluster
delegationTokens
masterKeys
membership
policies
reservationsHomeSubCluster
sequenceTable
```
> FederationStateStoreStoredProcs.sql
```
FederationStateStore> CREATE PROCEDURE [dbo].[sp_deleteDelegationToken]
@sequenceNum_IN bigint,
@rowCount_OUT int OUTPUT
AS BEGIN
DECLARE @errorMessage nvarchar(4000)
BEGIN TRY
BEGIN TRAN
DELETE FROM [dbo].[delegationTokens]
WHERE [sequenceNum] = @sequenceNum_IN;
SELECT @rowCount_OUT = @@ROWCOUNT;
COMMIT TRAN
END TRY
BEGIN CATCH
ROLLBACK TRAN
SET @errorMessage = dbo.func_FormatErrorMessage(ERROR_MESSAGE(), ERROR_LINE())
/* raise error and terminate the execution */
RAISERROR(@errorMessage, --- Error Message
1, -- Severity
-1 -- State
) WITH log
END CATCH
END;
[2023-02-12 22:54:12] completed in 48 ms
USE [FederationStateStore]
GO
SELECT Name FROM SysObjects Where XType='P' ORDER BY Name
FederationStateStore> SELECT Name FROM SysObjects Where XType='P' ORDER BY Name
[2023-02-12 22:54:56] 25 rows retrieved starting from 1 in 70 ms (execution: 52 ms, fetching: 18 ms)
sp_addApplicationHomeSubCluster
sp_addDelegationToken
sp_addMasterKey
sp_addReservationHomeSubCluster
sp_deleteApplicationHomeSubCluster
sp_deleteDelegationToken
sp_deleteMasterKey
sp_deleteReservationHomeSubCluster
sp_deregisterSubCluster
sp_getApplicationHomeSubCluster
sp_getApplicationsHomeSubCluster
sp_getDelegationToken
sp_getMasterKey
sp_getPoliciesConfigurations
sp_getPolicyConfiguration
sp_getReservationHomeSubCluster
sp_getReservationsHomeSubCluster
sp_getSubCluster
sp_getSubClusters
sp_registerSubCluster
sp_setPolicyConfiguration
sp_subClusterHeartbeat
sp_updateApplicationHomeSubCluster
sp_updateDelegationToken
sp_updateReservationHomeSubCluster
```
> dropStoreProcedures.sql
```
[2023-02-12 22:56:27] completed in 48 ms
FederationStateStore> IF OBJECT_ID ('[sp_getDelegationToken]', 'P') IS NOT NULL
DROP PROCEDURE [sp_getDelegationToken];
[2023-02-12 22:56:28] completed in 48 ms
FederationStateStore> IF OBJECT_ID ('[sp_updateDelegationToken]', 'P') IS NOT NULL
DROP PROCEDURE [sp_updateDelegationToken];
[2023-02-12 22:56:28] completed in 47 ms
FederationStateStore> IF OBJECT_ID ('[sp_deleteDelegationToken]', 'P') IS NOT NULL
DROP PROCEDURE [sp_deleteDelegationToken];
[2023-02-12 22:56:28] completed in 48 ms
FederationStateStore> SELECT Name FROM SysObjects Where XType='P' ORDER BY Name
[2023-02-12 22:57:02] 0 rows retrieved in 63 ms (execution: 51 ms, fetching: 12 ms)
```
> dropTables.sql
```
[2023-02-12 22:57:34] completed in 56 ms
FederationStateStore> IF OBJECT_ID ( '[masterKeys]', 'U' ) IS NOT NULL
DROP TABLE [masterKeys];
[2023-02-12 22:57:35] completed in 48 ms
FederationStateStore> IF OBJECT_ID ( '[delegationTokens]', 'U' ) IS NOT NULL
DROP TABLE [delegationTokens];
[2023-02-12 22:57:35] completed in 49 ms
FederationStateStore> IF OBJECT_ID ( '[sequenceTable]', 'U' ) IS NOT NULL
DROP TABLE [sequenceTable];
[2023-02-12 22:57:35] completed in 47 ms
FederationStateStore> SELECT Name FROM SysObjects Where XType='U' ORDER BY Name
[2023-02-12 22:58:04] 0 rows retrieved in 147 ms (execution: 80 ms, fetching: 67 ms)
```
> dropDatabase.sql
```
FederationStateStore> IF DB_ID ( '[FederationStateStore]') IS NOT NULL
DROP DATABASE [FederationStateStore]
[2023-02-12 22:58:47] completed in 47 ms
```
> dropUser.sql
```
FederationStateStore> USE [FederationStateStore]
[2023-02-12 22:59:04] [S0001][5701] Changed database context to 'FederationStateStore'.
[2023-02-12 22:59:04] completed in 52 ms
FederationStateStore> DROP USER FederationUser
[2023-02-12 22:59:08] completed in 50 ms
FederationStateStore> DROP LOGIN FederationUser
[2023-02-12 22:59:09] completed in 57 ms
```
### SQL Server 2014 Enterprise
> select @@Version
```
Microsoft SQL Server 2014 (SP2) (KB3171021) - 12.0.5000.0 (X64)
Jun 17 2016 19:14:09
Copyright (c) Microsoft Corporation
Enterprise Edition (64-bit) on Windows NT 6.3 <X64> (Build 9600: ) (Hypervisor)
```
> FederationStateStoreDatabase.sql
```
master> IF DB_ID ( '[FederationStateStore]') IS NOT NULL
DROP DATABASE [FederationStateStore]
[2023-02-12 23:20:36] completed in 45 ms
master> CREATE database FederationStateStore
[2023-02-12 23:20:40] completed in 292 ms
```
> FederationStateStoreUser.sql
```
FederationStateStore> CREATE LOGIN FederationUser with password = 'Federation@Password', default_database=[FederationStateStore]
[2023-02-12 23:21:12] completed in 46 ms
FederationStateStore> CREATE USER FederationUser FOR LOGIN FederationUser WITH default_schema=dbo
[2023-02-12 23:21:13] completed in 45 ms
FederationStateStore> EXEC sp_addrolemember 'db_owner', 'FederationUser'
[2023-02-12 23:21:13] completed in 48 ms
```
> FederationStateStoreTables.sql
```
[2023-02-12 23:21:50] completed in 49 ms
FederationStateStore> IF NOT EXISTS ( SELECT * FROM [FederationStateStore].sys.tables
WHERE name = 'sequenceTable'
AND schema_id = SCHEMA_ID('dbo'))
BEGIN
PRINT 'Table sequenceTable does not exist, create it...'
SET ANSI_NULLS ON
SET QUOTED_IDENTIFIER ON
SET ANSI_PADDING ON
CREATE TABLE [dbo].[sequenceTable](
sequenceName VARCHAR(255) NOT NULL,
nextVal bigint NOT NULL
CONSTRAINT [pk_sequenceName] PRIMARY KEY
(
[sequenceName]
)
)
SET ANSI_PADDING OFF
PRINT 'Table sequenceTable created.'
END
ELSE
PRINT 'Table sequenceTable exists, no operation required...'
Table sequenceTable does not exist, create it...
Table sequenceTable created.
[2023-02-12 23:21:50] completed in 52 ms
USE [FederationStateStore]
GO
SELECT Name FROM SysObjects Where XType='U' ORDER BY Name
FederationStateStore> SELECT Name FROM SysObjects Where XType='U' ORDER BY Name
[2023-02-12 23:22:27] 7 rows retrieved starting from 1 in 77 ms (execution: 57 ms, fetching: 20 ms)
applicationsHomeSubCluster
delegationTokens
masterKeys
membership
policies
reservationsHomeSubCluster
sequenceTable
```
> FederationStateStoreStoredProcs.sql
```
[2023-02-12 23:24:03] completed in 44 ms
FederationStateStore> CREATE PROCEDURE [dbo].[sp_deleteDelegationToken]
@sequenceNum_IN bigint,
@rowCount_OUT int OUTPUT
AS BEGIN
DECLARE @errorMessage nvarchar(4000)
BEGIN TRY
BEGIN TRAN
DELETE FROM [dbo].[delegationTokens]
WHERE [sequenceNum] = @sequenceNum_IN;
SELECT @rowCount_OUT = @@ROWCOUNT;
COMMIT TRAN
END TRY
BEGIN CATCH
ROLLBACK TRAN
SET @errorMessage = dbo.func_FormatErrorMessage(ERROR_MESSAGE(), ERROR_LINE())
/* raise error and terminate the execution */
RAISERROR(@errorMessage, --- Error Message
1, -- Severity
-1 -- State
) WITH log
END CATCH
END;
[2023-02-12 23:24:03] completed in 46 ms
USE [FederationStateStore]
GO
SELECT Name FROM SysObjects Where XType='P' ORDER BY Name
FederationStateStore> SELECT Name FROM SysObjects Where XType='P' ORDER BY Name
[2023-02-12 23:24:53] 25 rows retrieved starting from 1 in 90 ms (execution: 49 ms, fetching: 41 ms)
sp_addApplicationHomeSubCluster
sp_addDelegationToken
sp_addMasterKey
sp_addReservationHomeSubCluster
sp_deleteApplicationHomeSubCluster
sp_deleteDelegationToken
sp_deleteMasterKey
sp_deleteReservationHomeSubCluster
sp_deregisterSubCluster
sp_getApplicationHomeSubCluster
sp_getApplicationsHomeSubCluster
sp_getDelegationToken
sp_getMasterKey
sp_getPoliciesConfigurations
sp_getPolicyConfiguration
sp_getReservationHomeSubCluster
sp_getReservationsHomeSubCluster
sp_getSubCluster
sp_getSubClusters
sp_registerSubCluster
sp_setPolicyConfiguration
sp_subClusterHeartbeat
sp_updateApplicationHomeSubCluster
sp_updateDelegationToken
sp_updateReservationHomeSubCluster
```
> dropStoreProcedures.sql
```
[2023-02-12 23:25:55] completed in 44 ms
FederationStateStore> IF OBJECT_ID ('[sp_updateDelegationToken]', 'P') IS NOT NULL
DROP PROCEDURE [sp_updateDelegationToken];
[2023-02-12 23:25:55] completed in 45 ms
FederationStateStore> IF OBJECT_ID ('[sp_deleteDelegationToken]', 'P') IS NOT NULL
DROP PROCEDURE [sp_deleteDelegationToken];
FederationStateStore> SELECT Name FROM SysObjects Where XType='P' ORDER BY Name
[2023-02-12 23:26:53] 0 rows retrieved in 59 ms (execution: 47 ms, fetching: 12 ms)
```
> dropTables.sql
```
FederationStateStore> USE [FederationStateStore]
[2023-02-12 23:27:20] [S0001][5701] Changed database context to 'FederationStateStore'.
[2023-02-12 23:27:20] completed in 44 ms
FederationStateStore> IF OBJECT_ID ( '[sp_deregisterSubCluster]', 'U' ) IS NOT NULL
DROP TABLE [sp_deregisterSubCluster];
[2023-02-12 23:27:25] completed in 44 ms
FederationStateStore> IF OBJECT_ID ( '[membership]', 'U' ) IS NOT NULL
DROP TABLE [membership];
[2023-02-12 23:27:25] completed in 46 ms
FederationStateStore> IF OBJECT_ID ( '[policies]', 'U' ) IS NOT NULL
DROP TABLE [policies];
[2023-02-12 23:27:25] completed in 46 ms
FederationStateStore> IF OBJECT_ID ( '[applicationsHomeSubCluster]', 'U' ) IS NOT NULL
DROP TABLE [applicationsHomeSubCluster];
[2023-02-12 23:27:25] completed in 45 ms
FederationStateStore> IF OBJECT_ID ( '[reservationsHomeSubCluster]', 'U' ) IS NOT NULL
DROP TABLE [reservationsHomeSubCluster];
[2023-02-12 23:27:25] completed in 56 ms
FederationStateStore> IF OBJECT_ID ( '[masterKeys]', 'U' ) IS NOT NULL
DROP TABLE [masterKeys];
[2023-02-12 23:27:26] completed in 52 ms
FederationStateStore> IF OBJECT_ID ( '[delegationTokens]', 'U' ) IS NOT NULL
DROP TABLE [delegationTokens];
[2023-02-12 23:27:26] completed in 46 ms
FederationStateStore> IF OBJECT_ID ( '[sequenceTable]', 'U' ) IS NOT NULL
DROP TABLE [sequenceTable];
[2023-02-12 23:27:26] completed in 46 ms
FederationStateStore> SELECT Name FROM SysObjects Where XType='U' ORDER BY Name
[2023-02-12 23:28:12] 0 rows retrieved in 90 ms (execution: 63 ms, fetching: 27 ms)
```
> dropDatabase.sql
```
FederationStateStore> use FederationStateStore
[2023-02-12 23:28:44] [S0001][5701] Changed database context to 'FederationStateStore'.
[2023-02-12 23:28:44] completed in 48 ms
FederationStateStore> IF DB_ID ( '[FederationStateStore]') IS NOT NULL
DROP DATABASE [FederationStateStore]
[2023-02-12 23:28:44] completed in 42 ms
```
> dropUser.sql
```
FederationStateStore> USE [FederationStateStore]
[2023-02-12 23:29:10] [S0001][5701] Changed database context to 'FederationStateStore'.
[2023-02-12 23:29:10] completed in 43 ms
FederationStateStore> DROP USER FederationUser
[2023-02-12 23:29:14] completed in 46 ms
FederationStateStore> DROP LOGIN FederationUser
[2023-02-12 23:29:14] completed in 55 ms
```
### SQL Server 2016 Enterprise
> select @@Version
```
Microsoft SQL Server 2016 (SP2) (KB4052908) - 13.0.5026.0 (X64)
Mar 18 2018 09:11:49
Copyright (c) Microsoft Corporation
Enterprise Edition: Core-based Licensing (64-bit) on Windows Server 2012 R2 Standard 6.3 <X64> (Build 9600: ) (Hypervisor)
```
> FederationStateStoreDatabase.sql
```
[2023-02-12 23:55:21] completed in 55 ms
master> IF DB_ID ( '[FederationStateStore]') IS NOT NULL
DROP DATABASE [FederationStateStore]
[2023-02-12 23:55:21] completed in 43 ms
master> CREATE database FederationStateStore
[2023-02-12 23:55:22] completed in 474 ms
```
> FederationStateStoreUser.sql
```
FederationStateStore> CREATE LOGIN FederationUser with password = 'Federation@Password', default_database=[FederationStateStore]
[2023-02-12 23:55:52] completed in 48 ms
FederationStateStore> CREATE USER FederationUser FOR LOGIN FederationUser WITH default_schema=dbo
[2023-02-12 23:55:52] completed in 47 ms
FederationStateStore> EXEC sp_addrolemember 'db_owner', 'FederationUser'
[2023-02-12 23:55:53] completed in 47 ms
```
> FederationStateStoreTables.sql
```
.....
FederationStateStore> IF NOT EXISTS ( SELECT * FROM [FederationStateStore].sys.tables
WHERE name = 'delegationTokens'
AND schema_id = SCHEMA_ID('dbo'))
BEGIN
PRINT 'Table delegationTokens does not exist, create it...'
SET ANSI_NULLS ON
SET QUOTED_IDENTIFIER ON
SET ANSI_PADDING ON
CREATE TABLE [dbo].[delegationTokens](
sequenceNum BIGINT NOT NULL,
tokenIdent VARCHAR(1024) NOT NULL,
token VARCHAR(1024) NOT NULL,
renewDate BIGINT NOT NULL,
CONSTRAINT [pk_sequenceNum] PRIMARY KEY
(
[sequenceNum]
)
)
SET ANSI_PADDING OFF
PRINT 'Table delegationTokens created.'
END
ELSE
PRINT 'Table delegationTokens exists, no operation required...'
Table delegationTokens exists, no operation required...
[2023-02-12 23:56:27] completed in 56 ms
FederationStateStore> IF NOT EXISTS ( SELECT * FROM [FederationStateStore].sys.tables
WHERE name = 'sequenceTable'
AND schema_id = SCHEMA_ID('dbo'))
BEGIN
PRINT 'Table sequenceTable does not exist, create it...'
SET ANSI_NULLS ON
SET QUOTED_IDENTIFIER ON
SET ANSI_PADDING ON
CREATE TABLE [dbo].[sequenceTable](
sequenceName VARCHAR(255) NOT NULL,
nextVal bigint NOT NULL
CONSTRAINT [pk_sequenceName] PRIMARY KEY
(
[sequenceName]
)
)
SET ANSI_PADDING OFF
PRINT 'Table sequenceTable created.'
END
ELSE
PRINT 'Table sequenceTable exists, no operation required...'
Table sequenceTable does not exist, create it...
Table sequenceTable created.
[2023-02-12 23:56:28] completed in 60 ms
USE [FederationStateStore]
GO
SELECT Name FROM SysObjects Where XType='U' ORDER BY Name
FederationStateStore> SELECT Name FROM SysObjects Where XType='U' ORDER BY Name
[2023-02-12 23:57:23] 7 rows retrieved starting from 1 in 75 ms (execution: 61 ms, fetching: 14 ms)
applicationsHomeSubCluster
delegationTokens
masterKeys
membership
policies
reservationsHomeSubCluster
sequenceTable
```
> FederationStateStoreStoredProcs.sql
```
.....
[2023-02-12 23:58:20] completed in 43 ms
FederationStateStore> CREATE PROCEDURE [dbo].[sp_deleteDelegationToken]
@sequenceNum_IN bigint,
@rowCount_OUT int OUTPUT
AS BEGIN
DECLARE @errorMessage nvarchar(4000)
BEGIN TRY
BEGIN TRAN
DELETE FROM [dbo].[delegationTokens]
WHERE [sequenceNum] = @sequenceNum_IN;
SELECT @rowCount_OUT = @@ROWCOUNT;
COMMIT TRAN
END TRY
BEGIN CATCH
ROLLBACK TRAN
SET @errorMessage = dbo.func_FormatErrorMessage(ERROR_MESSAGE(), ERROR_LINE())
/* raise error and terminate the execution */
RAISERROR(@errorMessage, --- Error Message
1, -- Severity
-1 -- State
) WITH log
END CATCH
END;
[2023-02-12 23:58:20] completed in 44 ms
USE [FederationStateStore]
GO
SELECT Name FROM SysObjects Where XType='P' ORDER BY Name
FederationStateStore> SELECT Name FROM SysObjects Where XType='P' ORDER BY Name
[2023-02-12 23:59:19] 25 rows retrieved starting from 1 in 70 ms (execution: 51 ms, fetching: 19 ms)
sp_addApplicationHomeSubCluster
sp_addDelegationToken
sp_addMasterKey
sp_addReservationHomeSubCluster
sp_deleteApplicationHomeSubCluster
sp_deleteDelegationToken
sp_deleteMasterKey
sp_deleteReservationHomeSubCluster
sp_deregisterSubCluster
sp_getApplicationHomeSubCluster
sp_getApplicationsHomeSubCluster
sp_getDelegationToken
sp_getMasterKey
sp_getPoliciesConfigurations
sp_getPolicyConfiguration
sp_getReservationHomeSubCluster
sp_getReservationsHomeSubCluster
sp_getSubCluster
sp_getSubClusters
sp_registerSubCluster
sp_setPolicyConfiguration
sp_subClusterHeartbeat
sp_updateApplicationHomeSubCluster
sp_updateDelegationToken
sp_updateReservationHomeSubCluster
```
> dropStoreProcedures.sql
```
.....
[2023-02-13 00:00:02] completed in 44 ms
FederationStateStore> IF OBJECT_ID ('[sp_getDelegationToken]', 'P') IS NOT NULL
DROP PROCEDURE [sp_getDelegationToken];
[2023-02-13 00:00:03] completed in 45 ms
FederationStateStore> IF OBJECT_ID ('[sp_updateDelegationToken]', 'P') IS NOT NULL
DROP PROCEDURE [sp_updateDelegationToken];
[2023-02-13 00:00:03] completed in 46 ms
FederationStateStore> IF OBJECT_ID ('[sp_deleteDelegationToken]', 'P') IS NOT NULL
DROP PROCEDURE [sp_deleteDelegationToken];
[2023-02-13 00:00:03] completed in 52 ms
FederationStateStore> SELECT Name FROM SysObjects Where XType='P' ORDER BY Name
[2023-02-13 00:00:55] 0 rows retrieved in 58 ms (execution: 47 ms, fetching: 11 ms)
```
> dropTables.sql
```
...
[2023-02-13 00:01:46] completed in 45 ms
FederationStateStore> IF OBJECT_ID ( '[masterKeys]', 'U' ) IS NOT NULL
DROP TABLE [masterKeys];
[2023-02-13 00:01:47] completed in 46 ms
FederationStateStore> IF OBJECT_ID ( '[delegationTokens]', 'U' ) IS NOT NULL
DROP TABLE [delegationTokens];
[2023-02-13 00:01:47] completed in 45 ms
FederationStateStore> IF OBJECT_ID ( '[sequenceTable]', 'U' ) IS NOT NULL
DROP TABLE [sequenceTable];
[2023-02-13 00:01:47] completed in 45 ms
FederationStateStore> SELECT Name FROM SysObjects Where XType='U' ORDER BY Name
[2023-02-13 00:02:16] 0 rows retrieved in 60 ms (execution: 49 ms, fetching: 11 ms)
```
> dropDatabase.sql
```
FederationStateStore> IF DB_ID ( '[FederationStateStore]') IS NOT NULL
DROP DATABASE [FederationStateStore]
[2023-02-13 00:02:58] completed in 43 ms
```
> dropUser.sql
```
FederationStateStore> DROP USER FederationUser
[2023-02-13 00:03:17] completed in 44 ms
FederationStateStore> DROP LOGIN FederationUser
[2023-02-13 00:03:18] completed in 57 ms
```
### SQL Server 2017 Enterprise
> select @@Version
```
Microsoft SQL Server 2017 (RTM-CU14) (KB4484710) - 14.0.3076.1 (X64)
Mar 12 2019 19:29:19
Copyright (C) 2017 Microsoft Corporation
Enterprise Edition: Core-based Licensing (64-bit) on Windows Server 2012 R2 Standard 6.3 <X64> (Build 9600: ) (Hypervisor)
```
> FederationStateStoreDatabase.sql
```
[2023-02-13 00:17:31] completed in 59 ms
master> IF DB_ID ( '[FederationStateStore]') IS NOT NULL
DROP DATABASE [FederationStateStore]
[2023-02-13 00:17:31] completed in 45 ms
master> CREATE database FederationStateStore
[2023-02-13 00:17:33] completed in 1 s 905 ms
```
> FederationStateStoreUser.sql
```
FederationStateStore> CREATE LOGIN FederationUser with password = 'Federation@Password', default_database=[FederationStateStore]
[2023-02-13 00:18:12] completed in 46 ms
FederationStateStore> CREATE USER FederationUser FOR LOGIN FederationUser WITH default_schema=dbo
[2023-02-13 00:18:12] completed in 46 ms
FederationStateStore> EXEC sp_addrolemember 'db_owner', 'FederationUser'
[2023-02-13 00:18:12] completed in 48 ms
```
> FederationStateStoreTables.sql
```
FederationStateStore> IF NOT EXISTS ( SELECT * FROM [FederationStateStore].sys.tables
WHERE name = 'sequenceTable'
AND schema_id = SCHEMA_ID('dbo'))
BEGIN
PRINT 'Table sequenceTable does not exist, create it...'
SET ANSI_NULLS ON
SET QUOTED_IDENTIFIER ON
SET ANSI_PADDING ON
CREATE TABLE [dbo].[sequenceTable](
sequenceName VARCHAR(255) NOT NULL,
nextVal bigint NOT NULL
CONSTRAINT [pk_sequenceName] PRIMARY KEY
(
[sequenceName]
)
)
SET ANSI_PADDING OFF
PRINT 'Table sequenceTable created.'
END
ELSE
PRINT 'Table sequenceTable exists, no operation required...'
Table sequenceTable does not exist, create it...
Table sequenceTable created.
[2023-02-13 00:18:46] completed in 56 ms
USE [FederationStateStore]
GO
SELECT Name FROM SysObjects Where XType='U' ORDER BY Name
FederationStateStore> SELECT Name FROM SysObjects Where XType='U' ORDER BY Name
[2023-02-13 00:19:34] 7 rows retrieved starting from 1 in 80 ms (execution: 56 ms, fetching: 24 ms)
applicationsHomeSubCluster
delegationTokens
masterKeys
membership
policies
reservationsHomeSubCluster
sequenceTable
```
> FederationStateStoreStoredProcs.sql
```
[2023-02-13 00:20:33] completed in 46 ms
FederationStateStore> CREATE PROCEDURE [dbo].[sp_deleteDelegationToken]
@sequenceNum_IN bigint,
@rowCount_OUT int OUTPUT
AS BEGIN
DECLARE @errorMessage nvarchar(4000)
BEGIN TRY
BEGIN TRAN
DELETE FROM [dbo].[delegationTokens]
WHERE [sequenceNum] = @sequenceNum_IN;
SELECT @rowCount_OUT = @@ROWCOUNT;
COMMIT TRAN
END TRY
BEGIN CATCH
ROLLBACK TRAN
SET @errorMessage = dbo.func_FormatErrorMessage(ERROR_MESSAGE(), ERROR_LINE())
/* raise error and terminate the execution */
RAISERROR(@errorMessage, --- Error Message
1, -- Severity
-1 -- State
) WITH log
END CATCH
END;
[2023-02-13 00:20:34] completed in 47 ms
FederationStateStore> SELECT Name FROM SysObjects Where XType='P' ORDER BY Name
[2023-02-13 00:21:30] 25 rows retrieved starting from 1 in 124 ms (execution: 66 ms, fetching: 58 ms)
sp_addApplicationHomeSubCluster
sp_addDelegationToken
sp_addMasterKey
sp_addReservationHomeSubCluster
sp_deleteApplicationHomeSubCluster
sp_deleteDelegationToken
sp_deleteMasterKey
sp_deleteReservationHomeSubCluster
sp_deregisterSubCluster
sp_getApplicationHomeSubCluster
sp_getApplicationsHomeSubCluster
sp_getDelegationToken
sp_getMasterKey
sp_getPoliciesConfigurations
sp_getPolicyConfiguration
sp_getReservationHomeSubCluster
sp_getReservationsHomeSubCluster
sp_getSubCluster
sp_getSubClusters
sp_registerSubCluster
sp_setPolicyConfiguration
sp_subClusterHeartbeat
sp_updateApplicationHomeSubCluster
sp_updateDelegationToken
sp_updateReservationHomeSubCluster
```
> dropStoreProcedures.sql
```
FederationStateStore> IF OBJECT_ID ('[sp_getDelegationToken]', 'P') IS NOT NULL
DROP PROCEDURE [sp_getDelegationToken];
[2023-02-13 00:22:24] completed in 46 ms
FederationStateStore> IF OBJECT_ID ('[sp_updateDelegationToken]', 'P') IS NOT NULL
DROP PROCEDURE [sp_updateDelegationToken];
[2023-02-13 00:22:24] completed in 46 ms
FederationStateStore> IF OBJECT_ID ('[sp_deleteDelegationToken]', 'P') IS NOT NULL
DROP PROCEDURE [sp_deleteDelegationToken];
[2023-02-13 00:22:24] completed in 46 ms
FederationStateStore> SELECT Name FROM SysObjects Where XType='P' ORDER BY Name
[2023-02-13 00:23:03] 0 rows retrieved in 58 ms (execution: 47 ms, fetching: 11 ms)
```
> dropTables.sql
```
FederationStateStore> USE [FederationStateStore]
[2023-02-13 00:23:38] [S0001][5701] Changed database context to 'FederationStateStore'.
[2023-02-13 00:23:38] completed in 46 ms
FederationStateStore> IF OBJECT_ID ( '[sequenceTable]', 'U' ) IS NOT NULL
DROP TABLE [sequenceTable];
[2023-02-13 00:23:57] completed in 48 ms
FederationStateStore> SELECT Name FROM SysObjects Where XType='U' ORDER BY Name
[2023-02-13 00:24:35] 0 rows retrieved in 58 ms (execution: 47 ms, fetching: 11 ms)
```
> dropDatabase.sql
```
FederationStateStore> use FederationStateStore
[2023-02-13 00:25:02] [S0001][5701] Changed database context to 'FederationStateStore'.
[2023-02-13 00:25:02] completed in 48 ms
FederationStateStore> IF DB_ID ( '[FederationStateStore]') IS NOT NULL
DROP DATABASE [FederationStateStore]
[2023-02-13 00:25:03] completed in 45 ms
```
> dropUser.sql
```
FederationStateStore> USE [FederationStateStore]
[2023-02-13 00:25:28] [S0001][5701] Changed database context to 'FederationStateStore'.
[2023-02-13 00:25:28] completed in 46 ms
FederationStateStore> DROP USER FederationUser
[2023-02-13 00:25:31] completed in 49 ms
FederationStateStore> DROP LOGIN FederationUser
[2023-02-13 00:25:31] completed in 60 ms
```
### SQL Server 2019 Enterprise
> select @@Version
```
Microsoft SQL Server 2019 (RTM-CU10) (KB5001090) - 15.0.4123.1 (X64)
Mar 22 2021 18:10:24
Copyright (C) 2019 Microsoft Corporation
Enterprise Edition: Core-based Licensing (64-bit) on Windows Server 2016 Datacenter 10.0 <X64> (Build 14393: ) (Hypervisor)
```
> FederationStateStoreDatabase.sql
```
[2023-02-13 09:32:28] completed in 57 ms
master> IF DB_ID ( '[FederationStateStore]') IS NOT NULL
DROP DATABASE [FederationStateStore]
[2023-02-13 09:32:28] completed in 45 ms
master> CREATE database FederationStateStore
[2023-02-13 09:32:28] completed in 369 ms
```
> FederationStateStoreUser.sql
```
master> CREATE LOGIN FederationUser with password = 'Federation@Password', default_database=[FederationStateStore]
[2023-02-13 09:33:19] completed in 49 ms
master> CREATE USER FederationUser FOR LOGIN FederationUser WITH default_schema=dbo
[2023-02-13 09:33:20] completed in 46 ms
master> EXEC sp_addrolemember 'db_owner', 'FederationUser'
[2023-02-13 09:33:20] completed in 47 ms
```
> FederationStateStoreTables.sql
```
[2023-02-13 09:35:12] completed in 55 ms
FederationStateStore> IF NOT EXISTS ( SELECT * FROM [FederationStateStore].sys.tables
WHERE name = 'sequenceTable'
AND schema_id = SCHEMA_ID('dbo'))
BEGIN
PRINT 'Table sequenceTable does not exist, create it...'
SET ANSI_NULLS ON
SET QUOTED_IDENTIFIER ON
SET ANSI_PADDING ON
CREATE TABLE [dbo].[sequenceTable](
sequenceName VARCHAR(255) NOT NULL,
nextVal bigint NOT NULL
CONSTRAINT [pk_sequenceName] PRIMARY KEY
(
[sequenceName]
)
)
SET ANSI_PADDING OFF
PRINT 'Table sequenceTable created.'
END
ELSE
PRINT 'Table sequenceTable exists, no operation required...'
Table sequenceTable does not exist, create it...
Table sequenceTable created.
[2023-02-13 09:35:12] completed in 58 ms
USE [FederationStateStore]
GO
SELECT Name FROM SysObjects Where XType='U' ORDER BY Name
master> USE [FederationStateStore]
[2023-02-13 09:36:36] [S0001][5701] Changed database context to 'FederationStateStore'.
[2023-02-13 09:36:36] completed in 45 ms
FederationStateStore> SELECT Name FROM SysObjects Where XType='U' ORDER BY Name
[2023-02-13 09:36:37] 7 rows retrieved starting from 1 in 73 ms (execution: 60 ms, fetching: 13 ms)
applicationsHomeSubCluster
delegationTokens
masterKeys
membership
policies
reservationsHomeSubCluster
sequenceTable
```
> FederationStateStoreStoredProcs.sql
```
[2023-02-13 09:38:05] completed in 44 ms
FederationStateStore> CREATE PROCEDURE [dbo].[sp_deleteDelegationToken]
@sequenceNum_IN bigint,
@rowCount_OUT int OUTPUT
AS BEGIN
DECLARE @errorMessage nvarchar(4000)
BEGIN TRY
BEGIN TRAN
DELETE FROM [dbo].[delegationTokens]
WHERE [sequenceNum] = @sequenceNum_IN;
SELECT @rowCount_OUT = @@ROWCOUNT;
COMMIT TRAN
END TRY
BEGIN CATCH
ROLLBACK TRAN
SET @errorMessage = dbo.func_FormatErrorMessage(ERROR_MESSAGE(), ERROR_LINE())
/* raise error and terminate the execution */
RAISERROR(@errorMessage, --- Error Message
1, -- Severity
-1 -- State
) WITH log
END CATCH
END;
[2023-02-13 09:38:05] completed in 48 ms
USE [FederationStateStore]
GO
SELECT Name FROM SysObjects Where XType='P' ORDER BY Name
FederationStateStore> SELECT Name FROM SysObjects Where XType='P' ORDER BY Name
[2023-02-13 09:48:07] 25 rows retrieved starting from 1 in 105 ms (execution: 51 ms, fetching: 54 ms)
sp_addApplicationHomeSubCluster
sp_addDelegationToken
sp_addMasterKey
sp_addReservationHomeSubCluster
sp_deleteApplicationHomeSubCluster
sp_deleteDelegationToken
sp_deleteMasterKey
sp_deleteReservationHomeSubCluster
sp_deregisterSubCluster
sp_getApplicationHomeSubCluster
sp_getApplicationsHomeSubCluster
sp_getDelegationToken
sp_getMasterKey
sp_getPoliciesConfigurations
sp_getPolicyConfiguration
sp_getReservationHomeSubCluster
sp_getReservationsHomeSubCluster
sp_getSubCluster
sp_getSubClusters
sp_registerSubCluster
sp_setPolicyConfiguration
sp_subClusterHeartbeat
sp_updateApplicationHomeSubCluster
sp_updateDelegationToken
sp_updateReservationHomeSubCluster
```
> dropStoreProcedures.sql
```
[2023-02-13 09:53:06] completed in 49 ms
FederationStateStore> IF OBJECT_ID ('[sp_getDelegationToken]', 'P') IS NOT NULL
DROP PROCEDURE [sp_getDelegationToken];
[2023-02-13 09:53:06] completed in 46 ms
FederationStateStore> IF OBJECT_ID ('[sp_updateDelegationToken]', 'P') IS NOT NULL
DROP PROCEDURE [sp_updateDelegationToken];
[2023-02-13 09:53:07] completed in 46 ms
FederationStateStore> IF OBJECT_ID ('[sp_deleteDelegationToken]', 'P') IS NOT NULL
DROP PROCEDURE [sp_deleteDelegationToken];
FederationStateStore> SELECT Name FROM SysObjects Where XType='P' ORDER BY Name
[2023-02-13 09:53:25] 0 rows retrieved in 60 ms (execution: 49 ms, fetching: 11 ms)
```
> dropTables.sql
```
...
[2023-02-13 09:54:17] completed in 47 ms
FederationStateStore> IF OBJECT_ID ( '[masterKeys]', 'U' ) IS NOT NULL
DROP TABLE [masterKeys];
[2023-02-13 09:54:17] completed in 46 ms
FederationStateStore> IF OBJECT_ID ( '[delegationTokens]', 'U' ) IS NOT NULL
DROP TABLE [delegationTokens];
[2023-02-13 09:54:18] completed in 47 ms
FederationStateStore> IF OBJECT_ID ( '[sequenceTable]', 'U' ) IS NOT NULL
DROP TABLE [sequenceTable];
FederationStateStore> SELECT Name FROM SysObjects Where XType='U' ORDER BY Name
[2023-02-13 09:54:41] 0 rows retrieved in 60 ms (execution: 48 ms, fetching: 12 ms)
```
> dropDatabase.sql
```
FederationStateStore> use FederationStateStore
[2023-02-13 09:55:41] [S0001][5701] Changed database context to 'FederationStateStore'.
[2023-02-13 09:55:41] completed in 49 ms
FederationStateStore> IF DB_ID ( '[FederationStateStore]') IS NOT NULL
DROP DATABASE [FederationStateStore]
[2023-02-13 09:55:41] completed in 44 ms
```
> dropUser.sql
```
FederationStateStore> DROP LOGIN FederationUser
[2023-02-13 09:59:06] completed in 61 ms
```
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: common-issues-unsubscribe@hadoop.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: common-issues-unsubscribe@hadoop.apache.org
For additional commands, e-mail: common-issues-help@hadoop.apache.org