Canalplan Bug Tracker

Anonymous Login
2019-06-19 18:18 BST

View Issue Details Jump to Notes ]
IDProjectCategoryView StatusLast Update
0000438Canalplan [All Projects] Bugpublic2018-04-13 14:29
ReporterNick Atty 
Assigned ToNick Atty 
Product VersionProduct Build 
Target VersionFixed in Version 
Summary0000438: Need to avoid incompatible database edits - difficult problem
DescriptionIt's possible for the database to end up in a complete mess if two people are simultaneously trying to edit the same part of the network.

For example, person A starts to add a new branch from a place but before completing the task, person B deletes the place.

This is not due to sqlite shortcomings (which are probably responsible for the flurry of locked database errors that result) but will happen with any underlying database technology.
Additional InformationThe only way I can think of working around this is to keep a version counter in the database, and when someone starts an edit capture this in the page. When the edit is complete and ready to submit check that this has not changed. But what if it has? Most of the time things will be fine, but if they are not, how on earth do I test to see what might have changed? I don't want to force a complete re-entry, but how do I avoid this?
TagsNo tags attached.
Attach Tags (Separate by ",")
Attached Files


-Upload File
Select File
Maximum size: 5,000 KB
+Upload File



Stephen Atty (administrator)

If you had row level locking then you could basically lock the row (starting location) and a delete would fail as the row was locked.

But of course that doesn't help if we've both gone to location X and you've started an "Add place" or even a modify (but haven't actually commited) and I come in and do a delete before you've done anything You don't really want to take locks out on a place the minute a person starts an edit....

You could do it by checking that before you basically do an "Alter" (i.e. adding a link to an existing place that the place exists and if it doesn't then error out gracefully.

Its a problem found with all RESTFUL systems (which canalplan sort of is) and its compounded by having separate processes for each user which all attach to databases.


Shultzy (updater)

If a new waterway is created from an existing place is it possible to lock that waterway after checking that there is no activity on it, and the put an pop-up message to say the waterway is locked for administration purposes.

Isn't this the first time such a major mess has happened and it may be that the effort to stop it happening again is more than the gain.

Another option is to only allow trusted editors to complete sensitive actions. Users wanting to add a waterway could submit the information maybe to the forum. I would suspect that there would be very few instances of this.

The issue is why any of [T1], [Firhill Road Basin (north entrance)], [Firhill Road Basin moorings] or [Firhill Road Basin (south entrance)] cannot be deleted or split from each other in order to delete the rogue waterway.


Nick Atty (administrator)

As Steve says, the problem with taking out a lock is that it will hang around indefinitely. This is a known difficult problem.

Fixing the current mess is something else entirely! We are perilously close to having to wipe the system and backup by a couple of weeks, losing photos and thousands of Broads marker places and heaven knows what. To avoid ever having to do this again is worth *a lot* of work! It's not pressingly urgent, but can't be left like this if I'm going to make this into a system I can hand onto others at some stage.


Shultzy (updater)

I hadn't realised you needed to go back so far. Let me know if there is anything I can do. Copying the existing database to the Beta version including the [New Contributions] will help to recreate what is lost.


Stephen Atty (administrator)

We should put a proper backup of the sqlite databases in place. There is a backups folder on /webstuff2 which holds the mysql backups. We could put them in there into timestamped folders and housekeep them.


Nick Atty (administrator)

We should. We'll still perhaps end up with orphaned photo files, but that's about all.

-Add Note
View Status
Upload File
Maximum size: 5,000 KB
+Add Note

-Issue History
Date Modified Username Field Change
2018-04-13 11:10 Nick Atty New Issue
2018-04-13 11:10 Nick Atty Status new => assigned
2018-04-13 11:10 Nick Atty Assigned To => Nick Atty
2018-04-13 11:50 Stephen Atty Note Added: 0001698
2018-04-13 13:05 Shultzy Note Added: 0001701
2018-04-13 13:14 Nick Atty Note Added: 0001702
2018-04-13 13:21 Shultzy Note Added: 0001703
2018-04-13 13:31 Stephen Atty Note Added: 0001704
2018-04-13 14:29 Nick Atty Note Added: 0001707
+Issue History