Some user story are disapearing when closing a sprint

[Kelvin-SG]

Hello!

We are running IceScrum R6#14.8 Pro Standalone since a few months and I have to say that it had a very great success within the team.

Unfortunately, in one of our project, we are encountering an issue on certain user stories that are disapearing from the application when closing the sprint.
We are now on sprint 4 and it happened on sprint 1 and then again when closing sprint 2 & 3.

Being a former tech guy, I have checked in the database and I can see that those Stories are still there in the icescrum2_story tables. It is just that they are in status 5 (I guess in progress) and without any sprint attached to them (parent sprint id is null).

It looks like they were « forgotten » by the close sprint process (they were not updated to « done » or moved to the next sprint).

Is this a known issue?
I am now thinking about updating the corresponding entries in the story DB table but I don’t want to crash my entire project. At the same time we do love Icescrum and want to continue to use it for this project!

Any help or suggestion would be appreciated!

Thanks and regards,

Kelvin

[Nicolas Noullet]

Hello Kelvin,

First, I would recommend to upgrade to the latest version: R6#14.11 in order to benefit from the latest features and bug fixes. Here is how to do so: https://www.icescrum.com/documentation/upgrade-guide/.

I am glad that your team enjoy using our tool and I am sorry for the inconvenience caused by this issue.

The expected behavior is that the sprint closing is atomic: it should either work entirely or fail without updating the data. Here I think that something is failing but the error remains « silent », causing some stories to be « half-processed ».

There are three things to think about:

1. Understand the issue: we need to know more about the stories. Do they have tasks and acceptance tests? Do you have the logs (icescrum.log / catalina.out) for the moment when the sprint was closed?
2. Fix the existing data: you analysis is correct! Manual DB update come with great risks, that’s why we usually strongly advise against them. If you really want these stories back in the UI then we can update the data for you if you send us a dump of your DB.
3. Avoid the problem in future sprints: if step 1 leads to the fix of a bug then this will do it. In the meantime, a way to avoid the issue would consist in removing as much work as possible from the sprint closing: that means passing manually every done task to done, then every done acceptance test to done, then every done story to done, then switching to the next sprint any not done story, and finally closing the sprint

Nicolas

[Kelvin-SG]

Hello Nicolas,

Thank you very much for your reply.
I will try to answer you point by point.

1. Understanding the issue. I have checked in my tomcat folder in logs directory. The icescrum.log file has not been updated since the installation of Icescrum and the catalina.out or localhost do not contains any logs on the dates of the sprint closures (it contains other errors from time to time anyway).
Is there another directory I should check to find logs?

In terms of stories themselves, the only specific details I could think about was that they were initially having some dependencies defined. We had an error related to the depencies when closing the sprint 1 so we removed all depencies before closing it again.

2. Fix the existing data. What we have done up to now was to recreate the missing stories (and task) that were needed to be switched to next sprint. But I have to admit that I have also tried manual DB update of some done user stories in order to have them displayed in the release plan.
For example, I used such type of queries :

update [ICESCRUM].[dbo].[icescrum2_story]
set state = 7, rank = 1,parent_sprint_id= 60,done_date = ‘2016-08-05 06:00:00.000’
where id = 74 and backlog_id = 57

It works well to have the story appearing again in the release plan, but unfortunately (but as exepected), this did not refresh the sprint completed points, the related graphs and the taks that were contained within those stories.
Do you think that I should revert those updates?

To me it is important that the release plan reflects the actual progress on the project as it is the main view which we use in the project steering commitees.

Avoid the problem in future sprints Thank you for those advices, we will apply them for sure. Our next sprint closure is on friday. So we’ll see how it goes. I’ll update this thread afterwards.
If we encounter again those issue on friday, would you be interested to have a look at the db dump?

Thanks again for your support.

Kelvin

[Kelvin-SG]

Hi Again,

Checking the groovy.config file, I found the log directory for IceScrum.
Now I am able to find some errors on the sprints closure dates.
For example :
org.springframework.dao.DataIntegrityViolationException: could not update: [org.icescrum.core.domain.Task#359]; SQL [update icescrum2_task…

Caused by: com.microsoft.sqlserver.jdbc.SQLServerException: Violation of UNIQUE KEY constraint ‘UQ__icescrum__177B8D8492ECA575’. Cannot insert duplicate key in object ‘dbo.icescrum2_task’. The duplic

the log is pretty extensive. would it be helpfull to copy paste it over here?

Thanks and regards,

Kelvin

[Nicolas Noullet]

1. The message in your logs indicates an issue when creating tasks. The task name uniqueness constraint is actually enforced way too rigorously on SQL Server (because it deviates from the SQL standard regarding how nulls are managed for such constraints, I can tell you more about that if you want and even provide a workaround). Unfortunately, I think that task creation does not occur at sprint closing but probably rather comes from the activation of the next sprint afterward.

2. It’s hard to refrain from manual data tweaking, but it’s very hazardous! If you don’t come across new weird issues then I suggest not removing the « fake » stories for the moment to prevent any more significant change. However, you may update the sprint velocity manually (a simple field in the icescrum2_sprint table) after a DB backup, which should be enough to make your release plan look as it should.

3. Sure, but I hope it will go better this time!

Nicolas

[Kelvin-SG]

Hello again,

As mentionned previously, we had our sprint closure this morning.
Prior to that, we have upgraded our IceScrum to R6#14.11 and I have applied your advices of managing tasks and stories one by one before closing the sprint.

While doing so, we were able to found one problematic task and analyze the issue.
In fact, the problem is encountered when we are moving some completed task of an incomplete user story to the completed urgent tasks block (as Icescrum is doing when shifting uncomplete User story).

For one task, this was not successfull because of the same exeception :
Violation of UNIQUE KEY constraint ‘UQ__icescrum__177B8D8492ECA575’. Cannot insert duplicate key in object ‘dbo.icescrum2_task’.

Analyzing this DB constraint, we found that we already had in some previous sprint another urgent task with the same name. So this led to have 2 tasks with name and parent_story_id = null (as for all urgent tasks). In such case, the exception got triggered.

In our case, those tasks corresponds to generic tasks that we have in many user stories such as « Test Case creation », « Test Execution » or « SFD analysis ».
That’s why we are encountering this issue when the user stories are not all completed.

In your previous message, you’ve mentionned that this is linked to the way SQL server manages the ‘Null’ value within a constraint. I think that you also mentioned a possible workaround.
Would it possible for you to give more explanation on this workaround?

For our case today, we were finaly able to close our sprint after having updated the tasks name to make it unique. But it would be very helpfull to prevent this case for our next sprints.

thanks and regards,

Kelvin

[Nicolas Noullet]

Kelvin,

I was wrong regarding the link between the unique constraint error and the sprint closing, and I’m glad I was because we now know the cause!

Here is the expected behavior regarding name uniqueness: if a task has a parent story then the task name must be unique in the context of the story, otherwise (urgent or recurrent task) there is no constraint. As you aptly remarked, the « otherwise » case is materialized by a NULL parent_story_id in the icescrum2_task table.

In the ANSI SQL standard, the unique constraint does what we want out of the box: every pair (name, parent_story_id) with a same name and a NULL parent_story_id, such as (« someTaskName », NULL), is considered to be different thus avoiding unique constraint violation. Unfortunately, SQL Server treats NULL as any regular value so two pairs (« someTaskName », NULL) are considered the same and any attempt to create the second pair raises a constraint violation.

The problem is that we use high level abstractions to support a wide range of DBMS and they don’t take into account this peculiarity.

In fact, this leads to the requirement that every urgent / recurrent task name is unique not only at the project level but at the whole server level! 🙁

The way to fix that consists in identifying and removing the existing uniqueness constraint on task name and replacing it by a unique index on the name and parent_story_id columns with a where clause such as WHERE parent_story_id IS NOT NULL. Of course, before doing that, I advise that you back up your DB.

Here is some related reading: http://sqlmag.com/sql-server-2008/unique-constraint-multiple-nulls. By the way, I recently discovered that Oracle behaves like SQL Server…

If you can provide the working SQL queries, we will add them to our documentation!

[Kelvin-SG]

thank you very much for your reply and clear explanation.
We will check the solution you provided and let you know ! By the way, we are using SQL Server 2008 R2, I don’t know if this behaviour would happen in more recent versions of it.

In the meantime, what we have done is to have a SQL select query that is checking if potential duplicated tasks names exists between active stories and existing urgent or recurrent tasks.
If so, then we update manually the name of the tasks in IceScrum.
This is manageable as we currently have only 2 projects in parralel. But as you mentioned, this test needs to be done for the server level so it may become more difficult to manage in the case of many on going projects.

Thanks again. We really appreciated the support you provided.

Regards,

Kelvin

[Kelvin-SG]

We have applied the suggested fix.
A few tests were done and it seems to work perfectly 🙂
The new index is preventing the creation of two tasks with the same name within the same user story and it is now possible to move two tasks with the same name in the urgent/recurrent block. So this should be ok for our next sprint closure!

For your information, please find below the SQL queries that were used:

USE [ICESCRUM]
GO

/****** Object: Index [UQ__icescrum__177B8D8492ECA575] Script Date: 05/09/2016 14:10:27 ******/
ALTER TABLE [dbo].[icescrum2_task] DROP CONSTRAINT [UQ__icescrum__177B8D8492ECA575]
GO

— creation index
CREATE UNIQUE NONCLUSTERED INDEX UQ__icescrum_TaskName_Fix
ON [ICESCRUM].[dbo].[icescrum2_task]([parent_story_id],[name])
WHERE [parent_story_id] IS NOT NULL;

commit;

Thanks again !

Kelvin

[Nicolas Noullet]

Kelvin,

Thanks for your detailed feedback and for the SQL queries! I am glad that this solves the task name issue, hopefully it will solve the spring closing issue and the « ghost » stories 🙂

[Auteur]

Messages