[Discovery] Error during publish. Can't create SnapShot

[Discovery] Error during publish. Can't create SnapShot

Forge Component
Published on 2016-08-03 by Francisco Menezes
36 votes
Published on 2016-08-03 by Francisco Menezes
Hi all,
When publishing this great application I get the following error message in Service Center:

Timer OnDeploy error (inside action 'OnDeploy'). Timer duration = 0 secs: Error in advanced query InsertModuleEffort in SnapShot_Create in SnapShot in DiscoveryProbe (INSERT INTO {ModuleEffort} ({ModuleEffort}.[SnapShotId],{ModuleEffort}.[ModuleDefId],{ModuleEffort}.[Effort])  select @SnapShotId ,moduledefid, SUM(hourtotal) effort   from   (  select instant, user_id, moduledefid, ops * 1.0 / sum(ops) over  (partition by user_id,@TruncateHourPrefix  instant @TruncateHourSuffix) hourtotal,   ops, sum(ops) over  (partition by user_id,@TruncateHourPrefix  instant @TruncateHourSuffix ) opsHour  from    (      SELECT [USER_ID],coalesce(esp.id,ext.id) moduledefid,COUNT(1) ops, @TruncateHourPrefix  [datetime] @TruncateHourSuffix   instant      FROM {Developer_Operation} op      LEFT JOIN {ModuleDef} esp ON esp.eSpaceId=op.ESPACE_ID      LEFT JOIN {ModuleDef} ext ON ext.ExtensionId=op.EXTENSION_ID            WHERE op.DATETIME    > @StartDate AND op.OPERATION_TYPE<5            group by USER_ID, coalesce(esp.id,ext.id),@TruncateHourPrefix  [datetime] @TruncateHourSuffix     ) t  ) t2  group by moduledefid    ): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '(partition by user_id,trunc(  instant ,'HH')) hourtotal,   ops, sum(ops) over  (' at line 1 [retry 3 of 3 scheduled]

We are using the Java stack with a MySQL database. Looks like this syntax isn't accepted by the MySQL database.

Result of this is that no SnapShot is created so the application gives a blank screen with the text LOADING.....
Hi Martijn,

Thanks for reporting it. I'll try to fix the query to work with MySQL in the next couple of days.

Hi Martijn,

If you don't mind loosing some effort distribution information, you can remove the " over (partition by user_id,@TruncateHourPrefix instant @TruncateHourSuffix ) " from the "InsertModuleEffort" query in the DiscoveryProbe.

The fix is not trivial for me to do in the next couple of days. 
Any volunteers to help me on this task?

Hi Lúcio,

I tried some things and got it to work. The TruncateHourPrefix and TruncateHourSuffix were also not correct in MySQL. I have attached my oml not totally sure if this version gives errors on Sql server or Oracle databases.

kind regards,

I also had the same error when using MySQL. The attached fixed version works. Thanks!