Project

General

Profile

Support #5391

message-archiving-schema-1.3.0 doesn't work for SQL Server

Added by Wojciech Kapcia TigaseTeam about 2 years ago. Updated almost 2 years ago.

Status:
Closed
Priority:
Normal
Target version:
-
Start date:
Due date:
2017-04-25
% Done:

100%

Estimated time:
Source Code Disclaimer:

Description

Relevant part:

2017-04-19 17:18:04.795 [main]             DBSchemaLoader.loadSchemaFile()         SEVERE:   Failed to execute query: IF NOT EXISTS(SELECT * FROM sys.indexes WHERE object_id = object_id('dbo.tig_ma_tags') AND NAME ='IX_tig_ma_tags_owner_id') CREATE INDEX IX_tig_ma_tags_owner_id ON [dbo].[tig_ma_tags] ([owner_id])
2017-04-19 17:18:04.796 [main]             DBSchemaLoader.loadSchemaFile()         WARNING:  Can't finalize: Cannot find the object "dbo.tig_ma_tags" because it does not exist or you do not have permissions.

Full log:

wojtek@atlantiscity.local ~/dev/tigase/tigase-server/server $ export DB_TYPE=sqlserver
wojtek@atlantiscity.local ~/dev/tigase/tigase-server/server $ java -cp "jars/*" tigase.db.util.DBSchemaLoader -T ${DB_TYPE} -U tigase72tts -P tigase72tts -D tigase72tts -L ALL -H sqlserverhost -R sa -A xyz -F database/${DB_TYPE}-message-archiving-schema-1.3.0.sql
Picked up JAVA_TOOL_OPTIONS: -Djava.awt.headless=true
properties: {dbHostname=sqlserverhost, logLevel=ALL, dbType=sqlserver, file=database/sqlserver-message-archiving-schema-1.3.0.sql, rootUser=sa, useSSL=false, dbPass=tigase72tts, schemaVersion=7-2, dbName=tigase72tts, rootPass=xyz, dbUser=tigase72tts, ignoreMissingFiles=false}
LogLevel: ALL
2017-04-19 17:17:58.428 [main]             DBSchemaLoader.<init>()                 CONFIG:   Properties: {dbHostname=sqlserverhost, logLevel=ALL, dbType=sqlserver, file=database/sqlserver-message-archiving-schema-1.3.0.sql, rootUser=sa, useSSL=false, dbPass=tigase72tts, schemaVersion=7-2, dbName=tigase72tts, rootPass=x, dbUser=tigase72tts, ignoreMissingFiles=false}
2017-04-19 17:17:58.441 [main]             DBSchemaLoader.validateDBConnection()   INFO:     Validating DBConnection, URI: jdbc:jtds:sqlserver://sqlserverhost;user=sa;password=x;schema=dbo;lastUpdateCount=false;cacheMetaData=false
2017-04-19 17:17:59.080 [main]             DBSchemaLoader.validateDBConnection()   CONFIG:   DriverManager (available drivers): [org.apache.derby.jdbc.AutoloadedDriver@7ab2bfe1, jTDS 1.3.1, com.mysql.jdbc.Driver@5e8c92f4, com.mysql.fabric.jdbc.FabricMySQLDriver@2957fcb0, org.postgresql.Driver@1a93a7ca]
2017-04-19 17:17:59.081 [main]             DBSchemaLoader.validateDBConnection()   INFO:     Connection OK
2017-04-19 17:17:59.082 [main]             DBSchemaLoader.validateDBExists()       INFO:     Validating whether DB Exists, URI: jdbc:jtds:sqlserver://sqlserverhost;databaseName=tigase72tts;user=tigase72tts;password=tigase72tts;schema=dbo;lastUpdateCount=false;cacheMetaData=false
2017-04-19 17:17:59.482 [main]             DBSchemaLoader.validateDBExists()       INFO:     Doesn't exist, creating...
2017-04-19 17:18:00.019 [main]             DBSchemaLoader.loadSQLQueries()         FINER:    Loading queries, resource: sqlserver-installer-create-db, res_prefix: sqlserver
2017-04-19 17:18:00.038 [main]             DBSchemaLoader.validateDBExists()       FINE:     Executing query: USE [master]
2017-04-19 17:18:00.232 [main]             DBSchemaLoader.validateDBExists()       FINE:     Executing query: CREATE DATABASE tigase72tts
2017-04-19 17:18:00.910 [main]             DBSchemaLoader.validateDBExists()       FINE:     Executing query: CREATE LOGIN [tigase72tts] WITH PASSWORD=N'tigase72tts', DEFAULT_DATABASE=[tigase72tts]
2017-04-19 17:18:01.084 [main]             DBSchemaLoader.validateDBExists()       WARNING:  Query failed: The server principal 'tigase72tts' already exists.
2017-04-19 17:18:01.085 [main]             DBSchemaLoader.validateDBExists()       FINE:     Executing query: IF NOT EXISTS (SELECT name FROM sys.filegroups WHERE is_default=1 AND name = N'PRIMARY') ALTER DATABASE [tigase72tts] MODIFY FILEGROUP [PRIMARY] DEFAULT
2017-04-19 17:18:01.287 [main]             DBSchemaLoader.validateDBExists()       FINE:     Executing query: USE [tigase72tts]
2017-04-19 17:18:01.524 [main]             DBSchemaLoader.validateDBExists()       FINE:     Executing query: CREATE USER [tigase72tts] FOR LOGIN [tigase72tts]
2017-04-19 17:18:01.699 [main]             DBSchemaLoader.validateDBExists()       FINE:     Executing query: ALTER USER [tigase72tts] WITH DEFAULT_SCHEMA=[dbo]
2017-04-19 17:18:01.872 [main]             DBSchemaLoader.validateDBExists()       FINE:     Executing query: ALTER ROLE [db_owner] ADD MEMBER [tigase72tts]
2017-04-19 17:18:02.047 [main]             DBSchemaLoader.validateDBExists()       INFO:      OK
2017-04-19 17:18:02.048 [main]             DBSchemaLoader.loadSchemaFile()         INFO:     Loading schema from file(s): [database/sqlserver-message-archiving-schema-1.3.0.sql], URI: jdbc:jtds:sqlserver://sqlserverhost;databaseName=tigase72tts;user=sa;password=x;schema=dbo;lastUpdateCount=false;cacheMetaData=false
2017-04-19 17:18:02.729 [main]             DBSchemaLoader.loadSQLQueries()         FINER:    Loading queries, resource: database/sqlserver-message-archiving-schema-1.3.0.sql, res_prefix: null
2017-04-19 17:18:02.741 [main]             DBSchemaLoader.loadSchemaFile()         FINEST:   Executing query: SET QUOTED_IDENTIFIER ON
2017-04-19 17:18:02.929 [main]             DBSchemaLoader.loadSchemaFile()         FINEST:   Executing query: IF NOT EXISTS (select * from sysobjects where name='tig_ma_jids' and xtype='U') CREATE  TABLE [dbo].[tig_ma_jids] ( [jid_id] [bigint] IDENTITY(1,1) NOT NULL, [jid] [nvarchar](2049) NOT NULL, [jid_sha1] [varbinary](20) NOT NULL, [jid_fragment] AS CAST( [jid] AS NVARCHAR(255)), PRIMARY KEY ( [jid_id] ), CONSTRAINT UQ_tig_ma_jids_jids_sha1 UNIQUE (jid_sha1) )
2017-04-19 17:18:03.174 [main]             DBSchemaLoader.loadSchemaFile()         FINEST:   Executing query: IF NOT EXISTS(SELECT * FROM sys.indexes WHERE object_id = object_id('dbo.tig_ma_jids') AND NAME ='IX_tig_ma_jids_jid') CREATE INDEX IX_tig_ma_jids_jid ON [dbo].[tig_ma_jids](jid_fragment)
2017-04-19 17:18:03.368 [main]             DBSchemaLoader.loadSchemaFile()         FINEST:   Executing query: IF NOT EXISTS (select * from sysobjects where name='tig_ma_msgs' and xtype='U') CREATE  TABLE [dbo].[tig_ma_msgs] ( [msg_id] [bigint] IDENTITY(1,1) NOT NULL, [owner_id] [bigint], [buddy_id] [bigint], [ts] [datetime], [direction] [smallint], [type] [nvarchar](20), [body] [nvarchar](max), [msg] [nvarchar](max), [stanza_hash] [nvarchar](50), PRIMARY KEY ( [msg_id] ), CONSTRAINT [FK_tig_ma_msgs_owner_id] FOREIGN KEY ([owner_id]) REFERENCES [dbo].[tig_ma_jids]([jid_id]), CONSTRAINT [FK_tig_ma_msgs_buddy_id] FOREIGN KEY ([buddy_id]) REFERENCES [dbo].[tig_ma_jids]([jid_id]) )
2017-04-19 17:18:03.559 [main]             DBSchemaLoader.loadSchemaFile()         FINEST:   Executing query: IF NOT EXISTS(SELECT * FROM sys.indexes WHERE object_id = object_id('dbo.tig_ma_msgs') AND NAME ='IX_tig_ma_msgs_owner_id_index') CREATE INDEX IX_tig_ma_msgs_owner_id_index ON [dbo].[tig_ma_msgs] ([owner_id])
2017-04-19 17:18:03.748 [main]             DBSchemaLoader.loadSchemaFile()         FINEST:   Executing query: IF NOT EXISTS(SELECT * FROM sys.indexes WHERE object_id = object_id('dbo.tig_ma_msgs') AND NAME ='IX_tig_ma_msgs_owner_id_buddy_id_index') CREATE INDEX IX_tig_ma_msgs_owner_id_buddy_id_index ON [dbo].[tig_ma_msgs] ([owner_id], [buddy_id])
2017-04-19 17:18:03.938 [main]             DBSchemaLoader.loadSchemaFile()         FINEST:   Executing query: IF NOT EXISTS(SELECT * FROM sys.indexes WHERE object_id = object_id('dbo.tig_ma_msgs') AND NAME ='IX_tig_ma_msgs_owner_id_buddy_id_ts_index') CREATE INDEX IX_tig_ma_msgs_owner_id_buddy_id_ts_index ON [dbo].[tig_ma_msgs] ([owner_id], [buddy_id], [ts])
2017-04-19 17:18:04.129 [main]             DBSchemaLoader.loadSchemaFile()         FINEST:   Executing query: IF EXISTS(SELECT * FROM sys.indexes WHERE object_id = object_id('dbo.tig_ma_msgs') AND NAME ='IX_tig_ma_msgs_owner_id_ts_buddy_id_stanza_hash_index') DROP INDEX IX_tig_ma_msgs_owner_id_ts_buddy_id_stanza_hash_index ON [dbo].[tig_ma_msgs]
2017-04-19 17:18:04.321 [main]             DBSchemaLoader.loadSchemaFile()         FINEST:   Executing query: IF NOT EXISTS(SELECT * FROM sys.indexes WHERE object_id = object_id('dbo.tig_ma_msgs') AND NAME ='IX_tig_ma_msgs_owner_id_buddy_id_stanza_hash_ts_index') CREATE INDEX IX_tig_ma_msgs_owner_id_buddy_id_stanza_hash_ts_index ON [dbo].[tig_ma_msgs] ([owner_id], [buddy_id], [stanza_hash], [ts])
2017-04-19 17:18:04.513 [main]             DBSchemaLoader.loadSchemaFile()         FINEST:   Executing query: IF NOT EXISTS(SELECT * FROM sys.indexes WHERE object_id = object_id('dbo.tig_ma_tags') AND NAME ='IX_tig_ma_tags_owner_id') CREATE INDEX IX_tig_ma_tags_owner_id ON [dbo].[tig_ma_tags] ([owner_id])
2017-04-19 17:18:04.795 [main]             DBSchemaLoader.loadSchemaFile()         SEVERE:   Failed to execute query: IF NOT EXISTS(SELECT * FROM sys.indexes WHERE object_id = object_id('dbo.tig_ma_tags') AND NAME ='IX_tig_ma_tags_owner_id') CREATE INDEX IX_tig_ma_tags_owner_id ON [dbo].[tig_ma_tags] ([owner_id])
2017-04-19 17:18:04.796 [main]             DBSchemaLoader.loadSchemaFile()         WARNING:  Can't finalize: Cannot find the object "dbo.tig_ma_tags" because it does not exist or you do not have permissions.
2017-04-19 17:18:04.797 [main]             DBSchemaLoader.printInfo()              WARNING:  Database schema is invalid

Associated revisions

Revision cb2e8860 (diff)
Added by Andrzej Wójcik IoT 1 CloudTigaseTeam about 2 years ago

#5391: added missing query statements blocks starts and ends

History

#1 Updated by Andrzej Wójcik IoT 1 CloudTigaseTeam about 2 years ago

  • Due date changed from 2017-04-21 to 2017-04-25
  • Status changed from New to In QA
  • Assignee changed from Andrzej Wójcik to Wojciech Kapcia
  • % Done changed from 0 to 100

Added missing starts and ends for query blocks

#2 Updated by Wojciech Kapcia TigaseTeam almost 2 years ago

  • Status changed from In QA to Closed

Works in @tigase-server-7.2.0-SNAPSHOT-b4787@c!

2017-04-24 18:48:43.025 [main]             DBSchemaLoader.loadSchemaFile()         INFO:      completed OK
2017-04-24 18:48:43.026 [main]             DBSchemaLoader.printInfo()              INFO:

Database init.properties configuration:

Also available in: Atom PDF