911 Timeout while creating Custom Tags (or Publishing Panel) on big collections

[exterminador]

Hi!

 

When trying to create a Custom Tag (or the Publishing Panel) I get the timeout error bellow. It probably happens only with large collections or very slow computers, as the "INSERT INTO CustomNamespaceValues" query can take more than the default timeout value for Daminion (probably just for a few seconds), in my case the insert took 36s while testing it directly on the database.

 

For this scenarios would it be feasible to (temporarily) adjust the timeout value for database connections on the server's config file or something like that? I'm getting pretty good response times for every other operation on Daminion so I guess it only makes sense on this "big" transactions.

 

(I was using build 911 when tested this).

 

Thanks!

 

2013-12-08 17:20:11,605 [14] DEBUG PicaJet.Daminion.Service.Utils.CreateCustomNamespaceCatalogItemsCommand [(null)] - Start: (46)
2013-12-08 17:20:11,614 [14] DEBUG SQL [(null)] - select ID, Guid, Name, Description, TableName from Namespaces where Guid = @Guid (@Guid=6f7e59ef-3fc0-430f-ae50-a1eb36e017fb)
2013-12-08 17:20:11,634 [14] DEBUG SQL [(null)] - insert into Namespaces (Guid, Name, Description, TableName) values (@Guid, @Name, @Description, @TableName); select lastval(); (@Guid=6f7e59ef-3fc0-430f-ae50-a1eb36e017fb, @Name=custom, @Description=custom namespace, @TableName=CustomNamespaceValues)
2013-12-08 17:20:11,642 [14] DEBUG SQL [(null)] - insert into NamespaceItems (Guid, Name, Description, FieldName, DataType, ID_NameSpace, ReadOnly, GuiType, Units, Multiplier, ExcludeFromUI, PropertyInspectorTitleAlign, TagGroupGuid) values (@Guid, @Name, @Description, @FieldName, @DataType, @ID_NameSpace, @ReadOnly, @GuiType, @Units, @Multiplier, @ExcludeFromUI, @PropertyInspectorTitleAlign, @TagGroupGuid); select lastval(); (@Guid=b9fcd9d6-0b5c-40eb-86fc-f29eae8e03db, @Name=Fixed Filename, @Description=Fixed Filename, @FieldName=Custom_35d34ffd_b999_4577_a410_39b7d031ab3e, @DataType=System.String, @ID_NameSpace=9, @ReadOnly=False, @GuiType=Text, @Units=, @Multiplier=1, @ExcludeFromUI=False, @PropertyInspectorTitleAlign=Left, @TagGroupGuid=6f7e59ef-3fc0-430f-ae50-a1eb36e017fb)
2013-12-08 17:20:11,646 [14] DEBUG SQL [(null)] - select 1 from information_schema.tables where table_schema = 'public' and Upper(table_name) = 'CUSTOMNAMESPACEVALUES' ()
2013-12-08 17:20:11,661 [14] DEBUG SQL [(null)] - CREATE TABLE CustomNamespaceValues (
ID_MediaItem bigserial NOT NULL PRIMARY KEY,
Custom_35d34ffd_b999_4577_a410_39b7d031ab3e text);

()
2013-12-08 17:20:11,796 [14] DEBUG SQL [(null)] - INSERT INTO CustomNamespaceValues (ID_MediaItem, Custom_35d34ffd_b999_4577_a410_39b7d031ab3e) SELECT ID, @Custom_35d34ffd_b999_4577_a410_39b7d031ab3e FROM MediaItems; (@Custom_35d34ffd_b999_4577_a410_39b7d031ab3e=)
2013-12-08 17:20:12,960 [sTP SmartThreadPool Thread #1] DEBUG PicaJet.Daminion.Service.Utils.GetSynchronizeStateCommand [(null)] - Start: (47)
2013-12-08 17:20:12,963 [sTP SmartThreadPool Thread #1] DEBUG SQL [(null)] - select count(*), sum(case when Offline or Processed then 0 else 1 end)
from (select distinct s.ID_MediaItem as ID_MediaItem, m.Deleted, f.Offline, s.Processed
   from Synchronize s 
       left join MediaItems m on (s.ID_MediaItem = m.Id) 
       left join Files f on (s.ID_File = f.Id)) vv ()
2013-12-08 17:20:12,965 [sTP SmartThreadPool Thread #1] DEBUG SQL [(null)] - delete from Synchronize where Id in (select s.Id from Synchronize s left join MediaItems m on s.Id_MediaItem = m.Id where m.Deleted) ()
2013-12-08 17:20:12,967 [sTP SmartThreadPool Thread #1] DEBUG PicaJet.Daminion.Service.Utils.GetSynchronizeStateCommand [(null)] - End: (47)
2013-12-08 17:20:23,101 [sTP SmartThreadPool Thread #1] DEBUG PicaJet.Daminion.Service.Utils.GetSynchronizeStateCommand [(null)] - Start: (48)
2013-12-08 17:20:23,114 [sTP SmartThreadPool Thread #1] DEBUG SQL [(null)] - select count(*), sum(case when Offline or Processed then 0 else 1 end)
from (select distinct s.ID_MediaItem as ID_MediaItem, m.Deleted, f.Offline, s.Processed
   from Synchronize s 
       left join MediaItems m on (s.ID_MediaItem = m.Id) 
       left join Files f on (s.ID_File = f.Id)) vv ()
2013-12-08 17:20:23,118 [sTP SmartThreadPool Thread #1] DEBUG SQL [(null)] - delete from Synchronize where Id in (select s.Id from Synchronize s left join MediaItems m on s.Id_MediaItem = m.Id where m.Deleted) ()
2013-12-08 17:20:23,119 [sTP SmartThreadPool Thread #1] DEBUG PicaJet.Daminion.Service.Utils.GetSynchronizeStateCommand [(null)] - End: (48)
2013-12-08 17:20:31,857 [14] ERROR PicaJet.Daminion.Service.Utils.CreateCustomNamespaceCatalogItemsCommand [(null)] - Error: (46)
Npgsql.NpgsqlException:
A timeout has occured. If you were establishing a connection, increase Timeout value in ConnectionString. If you were executing a command, increase the CommandTimeout value in ConnectionString or in your NpgsqlCommand object.
  at Npgsql.NpgsqlState.ProcessBackendResponsesEnum(NpgsqlConnector context) in C:\projects\Npgsql2\src\Npgsql\NpgsqlState.cs:line 382
  at Npgsql.NpgsqlReadyState.QueryEnum(NpgsqlConnector context, NpgsqlCommand command) in C:\projects\Npgsql2\src\Npgsql\NpgsqlReadyState.cs:line 67
  at Npgsql.NpgsqlConnector.QueryEnum(NpgsqlCommand queryCommand) in C:\projects\Npgsql2\src\Npgsql\NpgsqlConnector.cs:line 292
  at Npgsql.NpgsqlCommand.GetReader(CommandBehavior cb) in C:\projects\Npgsql2\src\Npgsql\NpgsqlCommand.cs:line 591
  at Npgsql.NpgsqlCommand.ExecuteNonQuery() in C:\projects\Npgsql2\src\Npgsql\NpgsqlCommand.cs:line 495
  at PicaJet.Daminion.Service.DbCommandWrapper.ExecuteNonQuery() in c:\DaminionAuto\Code\Service.DB\DbCommandWrapper.cs:line 41
  at PicaJet.Daminion.Service.Utils.CreateCustomNamespaceCatalogItemsCommand.CreateCustomValuesTable(DbConnection connection, NameSpaceInfo customNamespace, List`1 namespaceItems) in c:\DaminionAuto\Code\Service.Utils.Uni\Command\CreateCustomNamespaceCatalogItemsCommand.cs:line 140
  at PicaJet.Daminion.Service.Utils.CreateCustomNamespaceCatalogItemsCommand.Start(DbConnection connection) in c:\DaminionAuto\Code\Service.Utils.Uni\Command\CreateCustomNamespaceCatalogItemsCommand.cs:line 73
  at PicaJet.Daminion.Service.DbCommandExecutor.ExecWithTransaction(Execute execDelegate, IDatabaseEnvironment databaseEnvironment, DbConnection connection, Boolean processErrorHandler) in c:\DaminionAuto\Code\Service.DB\DbCommandExecutor.cs:line 55
  at PicaJet.Daminion.Service.DbCommandExecutor.ExecWithTransaction(Execute execDelegate, IDatabaseEnvironment databaseEnvironment, Boolean processErrorHandler) in c:\DaminionAuto\Code\Service.DB\DbCommandExecutor.cs:line 23
  at PicaJet.Daminion.Service.DbCommandExecutor.ExecWithTransaction(Execute execDelegate, IDatabaseEnvironment databaseEnvironment) in c:\DaminionAuto\Code\Service.DB\DbCommandExecutor.cs:line 14
  at PicaJet.Daminion.Service.Utils.DatabaseCommand.StartWithTransaction() in c:\DaminionAuto\Code\Service.Utils.Uni\DatabaseCommand.cs:line 111
  at PicaJet.Daminion.Service.Utils.DatabaseCommand.Process() in c:\DaminionAuto\Code\Service.Utils.Uni\DatabaseCommand.cs:line 40
2013-12-08 17:20:33,225 [sTP SmartThreadPool Thread #1] DEBUG PicaJet.Daminion.Service.Utils.GetSynchronizeStateCommand [(null)] - Start: (49)

[Murat]

Added to the bug-tracking system (#2947)

 

Thanks for the detailed info!