Canalplan Bug Tracker

Anonymous Login
2019-07-21 13:51 BST

View Issue Details Jump to Notes ]
IDProjectCategoryView StatusLast Update
0000539Canalplan [All Projects] Internal Improvementspublic2019-07-18 20:29
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




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.


Stephen Atty (administrator)

Are we happy for me to put this into the crontab? Maybe run it at say 4am every saturday morning?


Nick Atty (administrator)

Why not run it at that time every morning to start with? That way we can find out if it causes any problems quicker, and revert to weekly once happy.


Stephen Atty (administrator)

Last edited: 2019-07-14 11:17

View 2 revisions

I just ran it and it seems to have killed beta.

Error: Syntax error in 729 of /webstuff/canalplan-beta/cgi-bin/../cgi-bin/showstuff.can
database input system.config.main_data
sql.c:1317::SQL error: no such column: override_flow when loading waterway ends


Nick Atty (administrator)

No, that was me forgetting that I'd not updated the beta database and I'd just added that row to the table to deal with another issue.

I've just copied the main database to beta which should fix it.


Stephen Atty (administrator)

OK we'll see how it goes over night on Beta and if that's OK I'll update the cron tomorrow morning.


Stephen Atty (administrator)

OK beta seemed to work OK so I've flicked the code over to do it with live. Will check it tomorrow morning.


Stephen Atty (administrator)

OK it ran on live OK.

Will monitor it for a couple of days and then make it weekly.


Stephen Atty (administrator)

All working OK. Set to run at 04:30 every saturday morning.

-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
2019-07-14 10:23 Stephen Atty Note Added: 0002652
2019-07-14 11:04 Nick Atty Note Added: 0002656
2019-07-14 11:14 Stephen Atty Note Added: 0002658
2019-07-14 11:17 Stephen Atty Note Edited: 0002658 View Revisions
2019-07-14 15:08 Nick Atty Note Added: 0002659
2019-07-14 15:13 Stephen Atty Note Added: 0002660
2019-07-15 19:16 Stephen Atty Note Added: 0002661
2019-07-16 06:55 Stephen Atty Note Added: 0002662
2019-07-18 20:29 Stephen Atty Status assigned => resolved
2019-07-18 20:29 Stephen Atty Resolution open => fixed
2019-07-18 20:29 Stephen Atty Note Added: 0002666
+Issue History