Canalplan Bug Tracker



Anonymous Login
2019-04-21 11:12 BST

View Issue Details Jump to Notes ]
IDProjectCategoryView StatusLast Update
0000539Canalplan [All Projects] Internal Improvementspublic2019-01-23 19:21
ReporterNick Atty 
Assigned Touser17280 
PrioritynormalSeverityminorReproducibilityN/A
StatusassignedResolutionopen 
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

-Relationships
+Relationships

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

-Notes

~0002050

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?

~0002051

Stephen Atty (administrator)

OK I've scripted repack_weekly.sh 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.

~0002052

Stephen Atty (administrator)

OK its coded

run from canalplan user home as ./repack_weekly.sh sitename

so ./repack_weekly.sh canalplan-beta

repacks the database for canalplan-beta.

BTW beta seems to be broken.

~0002053

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.

~0002054

Stephen Atty (administrator)

Nope - its killed it again.

~0002055

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

~0002056

Stephen Atty (administrator)

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

~0002057

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.

~0002058

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.

~0002059

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.

~0002060

Stephen Atty (administrator)

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

 ./repack_weekly.sh canalplan-beta

~0002061

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).

~0002062

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.

~0002063

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

~0002064

Stephen Atty (administrator)

OK i'ts fixed now

 sg www-data ./'repack_weekly.sh 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
+Notes

-Add Note
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
+Issue History