Canalplan Bug Tracker

Anonymous Login
2019-06-19 17:48 BST

View Issue Details Jump to Notes ]
IDProjectCategoryView StatusLast Update
0000539Canalplan [All Projects] Internal Improvementspublic2019-05-29 07:22
ReporterNick Atty 
Assigned ToStephen Atty 
PlatformGenericOSN/AOS VersionN/A
Product VersionProduct Build 
Target VersionFixed in Version 
Summary0000539: Repack the database automatically
DescriptionThe main database gets fragmented, and once that happens accesses get slow and locking errors start to proliferate.

There is a small script in the top-level Makefile that repacks the DB called "dump_and_reload_database". If we could run this once every couple of weeks at a quiet time (like three in the morning) I think it would improve reliability. Either calling the code in the makefile, or abstracting it to a separate script would work.
Additional InformationCode reproduced here for ease:

touch norun
echo '.dump ' | sqlite3 data/canal.sqlite > /tmp/canal_sqlite_dump
mv data/canal.sqlite data/bkup_canal.sqlite
sqlite3 data/canal.sqlite < /tmp/canal_sqlite_dump
chmod g+w data/canal.sqlite
rm norun

first we stop people using the site. Then we dump the database as SQL commands to a temporary file, get rid of the database entirely (as a backup - number these and put them elsewhere pehaps) then create a completely new database from the SQL command dump, ensure it is writable by the server and then make the site active again. It typically takes 20 seconds or so.
TagsNo tags attached.
Attach Tags (Separate by ",")
Attached Files


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



Stephen Atty (administrator)

We can do that - canalplan has its own crontab so we can put it in there to run once a week

I assume that we'd need a bit of a wait between touch norun and the next line?


Stephen Atty (administrator)

OK I've scripted and put it in canalplan's home dir.

It's set to run against beta so if it blows up its not catastrophic.

Will test it later.


Stephen Atty (administrator)

OK its coded

run from canalplan user home as ./ sitename

so ./ canalplan-beta

repacks the database for canalplan-beta.

BTW beta seems to be broken.


Nick Atty (administrator)

Fixed beta by copying across the database. It's about the only file owned by "canalplan" now so let's check that doesn't break anything.


Stephen Atty (administrator)

Nope - its killed it again.


Stephen Atty (administrator)

touching no run just makes it hang with a pile of processes:

www-data 21786 15164 0 21:44 ? 00:00:00 ./stable_canal /webstuff/canalplan-beta/cgi-bin/../cgi-bin/offline.can api.cgi
www-data 21802 15164 0 21:44 ? 00:00:00 ./stable_canal /webstuff/canalplan-beta/cgi-bin/../cgi-bin/offline.can api.cgi
www-data 21803 15164 0 21:44 ? 00:00:00 ./stable_canal /webstuff/canalplan-beta/cgi-bin/../cgi-bin/offline.can api.cgi
www-data 21804 15164 0 21:44 ? 00:00:00 ./stable_canal /webstuff/canalplan-beta/cgi-bin/../cgi-bin/offline.can api.cgi
www-data 21805 15164 0 21:44 ? 00:00:00 ./stable_canal /webstuff/canalplan-beta/cgi-bin/../cgi-bin/offline.can api.cgi
www-data 21829 15164 0 21:44 ? 00:00:00 ./stable_canal /webstuff/canalplan-beta/cgi-bin/../cgi-bin/offline.can canal.cgi


Stephen Atty (administrator)

But the script should work once we can work out what is making it break.


Nick Atty (administrator)

Those "offline.can" should be what runs when someone tries to continue working on the site while an upgrade is taking place ("norun" exists). But they ought to terminate.


Stephen Atty (administrator)

They just hung round for ages.

I left the BETA site with norun in place

If you go in and go to something like plan a journey the page just hangs.


Nick Atty (administrator)

To release a new version I need to be able to turn norun on and off in beta.

But beta is working fine for me - planning journeys nicely.


Stephen Atty (administrator)

OK so if that's working do you want to try running the script that I put in the canalplan home

 ./ canalplan-beta


Nick Atty (administrator)

It works fairly well but hits ownership issues - is canalplan a member of www-data? When I tried it couldn't overwrite the text file, and when that happens it continues and restores from the previous one.

We don't actually ever need to use it on beta, so we could simplify it (although beta obviously helps with the testing) because the beta database is just - at intervals - manually replaced (I quite often put silly experimental things into beta when testing data modification features).


Stephen Atty (administrator)

Not sure if canalplan is (should be) will check the groups when I get home and add it if needed

The text file in the backup directory should have been created by canalplan so it should be able to overwrite it.


Stephen Atty (administrator)

its in the groups and creates files OK

canalplan@Debussy:~$ touch /webstuff/canalplan-beta/testing.txt
canalplan@Debussy:~$ ls -ltr /webstuff/canalplan-beta/testing.txt
-rw-rw-r-- 1 canalplan canalplan 0 Jan 23 18:38 /webstuff/canalplan-beta/testing.txt


Stephen Atty (administrator)

OK i'ts fixed now

 sg www-data ./' canalplan-beta'

is the command to run it.

Give it a go and make sure it works - if it does then I'll crontab it to run in beta for a couple of days so we can be sure its working as expected


Stephen Atty (administrator)

Can you check that the script works?


Nick Atty (administrator)

It seems to! It could do with a message after the sleep though, as the backup takes a while with no status as to what is happening.

It might be nice to make the penultimate rm fail silently as well if there isn't an old backup to remove.


Stephen Atty (administrator)

OK I'll add a couple more messages and wrap the rm s in some logic.


Stephen Atty (administrator)

OK how does that look


Nick Atty (administrator)

Looks OK. I think 2 mins is very conservative, but the whole thing should result in one 5 min outage every week so not really a problem.

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

-Issue History
Date Modified Username Field Change
2019-01-20 16:49 Nick Atty New Issue
2019-01-20 16:49 Nick Atty Status new => assigned
2019-01-20 16:49 Nick Atty Assigned To => user17280
2019-01-20 16:55 Stephen Atty Note Added: 0002050
2019-01-20 17:27 Stephen Atty Note Added: 0002051
2019-01-20 18:54 Stephen Atty Note Added: 0002052
2019-01-20 21:11 Nick Atty Note Added: 0002053
2019-01-20 21:26 Stephen Atty Note Added: 0002054
2019-01-20 21:45 Stephen Atty Note Added: 0002055
2019-01-20 21:54 Stephen Atty Note Added: 0002056
2019-01-22 10:37 Nick Atty Note Added: 0002057
2019-01-22 11:57 Stephen Atty Note Added: 0002058
2019-01-22 13:12 Nick Atty Note Added: 0002059
2019-01-22 14:00 Stephen Atty Note Added: 0002060
2019-01-23 07:28 Nick Atty Note Added: 0002061
2019-01-23 09:54 Stephen Atty Note Added: 0002062
2019-01-23 18:39 Stephen Atty Note Added: 0002063
2019-01-23 19:21 Stephen Atty Note Added: 0002064
2019-04-21 20:09 Stephen Atty Assigned To user17280 => Stephen Atty
2019-04-21 20:09 Stephen Atty Note Added: 0002386
2019-05-28 13:59 Nick Atty Note Added: 0002506
2019-05-28 18:29 Stephen Atty Note Added: 0002515
2019-05-28 19:04 Stephen Atty Note Added: 0002516
2019-05-29 07:22 Nick Atty Note Added: 0002517
+Issue History