Mailing List Archive: Upgrade davical on FreeBSD

Upgrade davical on FreeBSD

Jan 27, 2010, 6:23 AM

Post #1 of 9 (1885 views)

Hi Andrew,

I started now to upgrade my davical installation to get it running with
an iPhone to version 0.9.8.

At first I installed awl-0.39 to /usr/local/share/awl-0.39.orig and
created a symlink (in /usr/local/share)
1 lrwxr-xr-x 1 root wheel 13 Jan 9 15:12 awl -> awl-0.39.orig

Then I upgraded postgres to 8.4.2 delete the old davical database,
recreated the database (with the script in dba) and loaded my data from
a backup to the database.
At least the update script says now:
The database is version 8.4 currently at revision 1.2.7.
No patches were applied.
Supported locales updated.
Updated view: dav_principal.sql applied.
CalDAV functions updated.
RRULE functions updated.
Database permissions updated.

so I think the database is now fine.

In FreeBSD is a strict seperation between config files for the system
and installed software.
So I have a my configfile in /usr/local/etc/davical/
I attached a patch you maybe want to add to davical in a future version.

My configfile has the following entries:
<?php
$c->domainname = "<removed it>";
$c->sysabbr = 'rscds';
$c->admin_email = 'idefix at fechner.net';
$c->system_name = "Really Simple CalDAV Store";
$c->collections_always_exist = false;
$c->default_locale = de_DE.UTF-8;

$c->pg_connect[] = 'dbname=davical port=5432 user=davical_app';
//$c->pg_connect[] = 'dbname=davical port=5432 user=root';

?>

If I try now to login via the webinterface the system does not let me in
(maybe I forgot to adapt a path in one of the files).
If I take my old version 0.9.6.2 with the same database and config file
it works perfectly.

Do you have an idea where the problem can be?
(i got no error message in the apache log file)

If everything work fine with FreeBSD my next plan it to make a FreeBSD
port for awl and for davical to simplify installation in FreeBSD.

Thanks,
Matthias

--
"Programming today is a race between software engineers striving to build bigger and better idiot-proof programs, and the universe trying to produce bigger and better idiots. So far, the universe is winning." -- Rich Cook

-------------- next part --------------
An embedded and charset-unspecified text was scrubbed...
Name: davical-0.9.8-path.patch
URL: <http://lists.morphoss.com/pipermail/davical-users/attachments/20100127/16591a61/attachment.txt>
-------------- next part --------------

Upgrade davical on FreeBSD [ In reply to ]

andrew at morphoss

Jan 27, 2010, 1:51 PM

Post #2 of 9 (1854 views)

Permalink

On Wed, 2010-01-27 at 14:23 +0100, Matthias Fechner wrote:
> Hi Andrew,
>
> I started now to upgrade my davical installation to get it running with
> an iPhone to version 0.9.8.

> so I think the database is now fine.

It seems good.

> In FreeBSD is a strict seperation between config files for the system
> and installed software.
> So I have a my configfile in /usr/local/etc/davical/
> I attached a patch you maybe want to add to davical in a future version.

I've applied that now - thanks.

> If I try now to login via the webinterface the system does not let me in
> (maybe I forgot to adapt a path in one of the files).
> If I take my old version 0.9.6.2 with the same database and config file
> it works perfectly.
>
> Do you have an idea where the problem can be?
> (i got no error message in the apache log file)

It's difficult for me to guess why not from this information. Is it
possible that your PHP does not have PDO and/or pdo_pgsql built in?
This is an additional requirement in 0.9.8 which some others have
discovered.

> If everything work fine with FreeBSD my next plan it to make a FreeBSD
> port for awl and for davical to simplify installation in FreeBSD.

Thanks. There is already a FreeBSD port for DAViCal (or so I
understand) but it is perhaps a little out of date, and doesn't include
the dependency on PDO. It might be a good starting point though.

Mind you, I haven't released 0.9.8 too widely because it is a
significant departure from the previous 0.9.7.x series and not
completely polished as a result. Much of that should be resolved with
0.9.8.1 in a few days, which I intend to release much more widely.

Cheers,
Andrew.

------------------------------------------------------------------------
andrew (AT) morphoss (DOT) com +64(272)DEBIAN
Powering the .NZ namespace with Open Source Software
------------------------------------------------------------------------

-------------- next part --------------
A non-text attachment was scrubbed...
Name: not available
Type: application/pgp-signature
Size: 198 bytes
Desc: This is a digitally signed message part
URL: <http://lists.morphoss.com/pipermail/davical-users/attachments/20100128/2be1402e/attachment.pgp>
-------------- next part --------------

Upgrade davical on FreeBSD [ In reply to ]

idefix at fechner

Jan 27, 2010, 2:32 PM

Post #3 of 9 (1893 views)

Permalink

Hi Andrew,

Am 27.01.2010 21:51, schrieb Andrew McMillan:
> It's difficult for me to guess why not from this information. Is it
> possible that your PHP does not have PDO and/or pdo_pgsql built in?
> This is an additional requirement in 0.9.8 which some others have
> discovered.

I installed now the port:
databases/php5-pdo_pgsql

And now I get errormessages into the error log from apache:
[Wed Jan 27 22:20:52 2010] [error] [client 192.168.0.151] rscds: LOG:
always: Query: SQ: Took: 0.414886 for SELECT schema_major, schema_minor,
schema_patch FROM awl_db_revision ORDER BY schema_id DESC LIMIT 1;
[Wed Jan 27 22:20:52 2010] [error] [client 192.168.0.151] rscds: LOG:
always: Query: SQ: Error in
'/usr/local/www/davical-0.9.8/inc/always.php' on line 180
[Wed Jan 27 22:20:52 2010] [error] [client 192.168.0.151] rscds: LOG:
always: Query: SQ: Took: 0.440168 for SELECT *, to_char(updated at time
zone 'GMT','Dy, DD Mon IYYY HH24:MI:SS "GMT"') AS modified, principal.*,
0::BIT(24) AS privileges FROM usr LEFT JOIN principal USING(user_no)
WHERE lower(username) = lower(E'idefix')

It seems to repeat always this line (it seems to come from thunderbird).

If I try to login via web I see now in the logfile:

[Wed Jan 27 22:29:47 2010] [error] [client 192.168.0.151] rscds: WARN:
Login ERR: Other than one session record matches. 2, referer: http://x/
[Wed Jan 27 22:29:47 2010] [error] [client 192.168.0.151] rscds: WARN:
Login ERR: Other than one session record matches. 2, referer: http://x/

(I replaced the hostname with a x)

> Thanks. There is already a FreeBSD port for DAViCal (or so I
> understand) but it is perhaps a little out of date, and doesn't include
> the dependency on PDO. It might be a good starting point though.

oh you are right, could be new, I havn't seen it yet (is version 0.9.7.6).

> Mind you, I haven't released 0.9.8 too widely because it is a
> significant departure from the previous 0.9.7.x series and not
> completely polished as a result. Much of that should be resolved with
> 0.9.8.1 in a few days, which I intend to release much more widely.

oh that are good news, I can test davical on FreeBSD if you like.
Have as client iMail, Thunderbird and an iPhone.

Bye
Matthias

--

"Programming today is a race between software engineers striving to
build bigger and better idiot-proof programs, and the universe trying to
produce bigger and better idiots. So far, the universe is winning." --
Rich Cook

Upgrade davical on FreeBSD [ In reply to ]

andrew at morphoss

Jan 27, 2010, 5:30 PM

Post #4 of 9 (1859 views)

Permalink

On Wed, 2010-01-27 at 22:32 +0100, Matthias Fechner wrote:
>
> And now I get errormessages into the error log from apache:
> [Wed Jan 27 22:20:52 2010] [error] [client 192.168.0.151] rscds: LOG:
> always: Query: SQ: Took: 0.414886 for SELECT schema_major, schema_minor,

'SQ' means 'Slow Query' in this case it's taking 0.4 seconds, which
seems excessively slow for something that should be practically
instantaneous.

You should try and do the following:

Stop postgresql
Start postgresql

connect as the database superuser and issue a:

TRUNCATE session;
VACUUM FULL ANALYZE;

It may be that autovacuum is not properly configured on your system,
which should clean this up on a regular basis otherwise.

> [Wed Jan 27 22:29:47 2010] [error] [client 192.168.0.151] rscds: WARN:
> Login ERR: Other than one session record matches. 2, referer: http://x/

I've never seen this problem before, but the 'truncate session' above
should resolve it.

> oh you are right, could be new, I havn't seen it yet (is version 0.9.7.6).

That's as up-to-date as is reasonable, then :-)

Cheers,
Andrew.

------------------------------------------------------------------------
andrew (AT) morphoss (DOT) com +64(272)DEBIAN
So you're back... about time...
------------------------------------------------------------------------

-------------- next part --------------
A non-text attachment was scrubbed...
Name: not available
Type: application/pgp-signature
Size: 198 bytes
Desc: This is a digitally signed message part
URL: <http://lists.morphoss.com/pipermail/davical-users/attachments/20100128/6be0604a/attachment.pgp>
-------------- next part --------------

Upgrade davical on FreeBSD [ In reply to ]

idefix at fechner

Jan 28, 2010, 1:16 AM

Post #5 of 9 (1842 views)

Permalink

Hi Andrew,

Andrew McMillan wrote:
> You should try and do the following:
> Stop postgresql
> Start postgresql
>
> connect as the database superuser and issue a:
>
> TRUNCATE session;
> VACUUM FULL ANALYZE;
>
>
ok, I executed the commands I checked the session table and it was empty
after it.
If I connect to the database via web I see in the errorlog:
[Thu Jan 28 08:45:15 2010] [error] [client 88.217.56.106] rscds: WARN:
Login ERR: Other than one session record matches. 0

Maybe I forgot something in the restore of data.
I execute the copy command on the following tables (in the order it is
shown here):
usr
caldav_data
calendar_item
principal
property
session

Maybe something in the database is broken?
If you like I can sent a dump of the database to you.

Thanks,
Matthias

--
"Programming today is a race between software engineers striving to build bigger and better idiot-proof programs, and the universe trying to produce bigger and better idiots. So far, the universe is winning." -- Rich Cook

Upgrade davical on FreeBSD [ In reply to ]

andrew at morphoss

Jan 28, 2010, 2:23 AM

Post #6 of 9 (1870 views)

Permalink

On Thu, 2010-01-28 at 09:16 +0100, Matthias Fechner wrote:
> Hi Andrew,
>
> Andrew McMillan wrote:
> > You should try and do the following:
> > Stop postgresql
> > Start postgresql
> >
> > connect as the database superuser and issue a:
> >
> > TRUNCATE session;
> > VACUUM FULL ANALYZE;
> >
> >
> ok, I executed the commands I checked the session table and it was empty
> after it.
> If I connect to the database via web I see in the errorlog:
> [Thu Jan 28 08:45:15 2010] [error] [client 88.217.56.106] rscds: WARN:
> Login ERR: Other than one session record matches. 0
>
> Maybe I forgot something in the restore of data.
> I execute the copy command on the following tables (in the order it is
> shown here):
> usr
> caldav_data
> calendar_item
> principal
> property
> session
>
> Maybe something in the database is broken?
> If you like I can sent a dump of the database to you.

There are lots more tables than that! You should restore the whole
database.

I usually back up my database with:

pg_dump -Fc davical >davical.pgdump

And then I can restore with:

createdb --encoding utf-8 --owner davical_dba davical
pg_restore -Fc -d davical davical.pgdump

If the database came from an older version of DAViCal, then you would
need to:

cd /usr/share/davical
dba/update-davical-database

in the normal manner.

After that you can truncate the session table and vacuum full analyze
the database as mentioned previously.

Cheers,
Andrew.

------------------------------------------------------------------------
andrew (AT) morphoss (DOT) com +64(272)DEBIAN
Necessity is the mother of documentation
------------------------------------------------------------------------

-------------- next part --------------
A non-text attachment was scrubbed...
Name: not available
Type: application/pgp-signature
Size: 198 bytes
Desc: This is a digitally signed message part
URL: <http://lists.morphoss.com/pipermail/davical-users/attachments/20100128/79c323e6/attachment.pgp>
-------------- next part --------------

Upgrade davical on FreeBSD [ In reply to ]

idefix at fechner

Jan 28, 2010, 5:26 AM

Post #7 of 9 (1860 views)

Permalink

Hi Andrew,

Andrew McMillan wrote:
> On Thu, 2010-01-28 at 09:16 +0100, Matthias Fechner wrote:
>
>> ok, I executed the commands I checked the session table and it was empty
>> after it.
>> If I connect to the database via web I see in the errorlog:
>> [Thu Jan 28 08:45:15 2010] [error] [client 88.217.56.106] rscds: WARN:
>> Login ERR: Other than one session record matches. 0
>>
>> Maybe I forgot something in the restore of data.
>> I execute the copy command on the following tables (in the order it is
>> shown here):
>> usr
>> caldav_data
>> calendar_item
>> principal
>> property
>> session
>>
>> Maybe something in the database is broken?
>> If you like I can sent a dump of the database to you.
>>
>
> There are lots more tables than that! You should restore the whole
> database.
>
>
sorry, I think you misunderstood me here, I will try to explain more in
detail, what I did:
1. Deleted the old database
2. Created the database again with the script: dba/create-database.sh
(version 0.9.8)
3. Loaded my old data (tables above) into my database, the lines look
like this one (created by pg_dump on the old database):
COPY usr (user_no, active, email_ok, joined, updated, last_used,
username, password, fullname, email, config_data, date_format_type,
locale) FROM stdin;
1001 t \N 2007-12-09 22:29:08.137328+01 \N
2009-12-29 23:48:01.048109+01 idefix **foobar Matthias Fechner
idefix at fechner.net \N E de_DE
\.

I only execute the COPY commands for the tables I mentioned above.

If you see a better way to get my old data into a clear new database
please let me know.

If I take version 0.9.6.2 to access the new database it works.
If I take version 0.9.8 to access the new database it fails at the login.

Thanks,
Matthias

--
"Programming today is a race between software engineers striving to build bigger and better idiot-proof programs, and the universe trying to produce bigger and better idiots. So far, the universe is winning." -- Rich Cook

Upgrade davical on FreeBSD [ In reply to ]

andrew at morphoss

Jan 28, 2010, 6:06 AM

Post #8 of 9 (1877 views)

Permalink

On Thu, 2010-01-28 at 13:26 +0100, Matthias Fechner wrote:
> >
> > There are lots more tables than that! You should restore the whole
> > database.
> >
> >
> sorry, I think you misunderstood me here, I will try to explain more in
> detail, what I did:
> 1. Deleted the old database
> 2. Created the database again with the script: dba/create-database.sh
> (version 0.9.8)
> 3. Loaded my old data (tables above) into my database

No, that's exactly what I thought you had done, and I think it's the
wrong way to do it :-)

If it did work, it would only work for the same DAViCal version, so why
do it at all?

If you really want to go back to a re-created database and add to it,
then do a GET on each calendar and save it to an .ics file, then create
the new database, and the principals and so forth, and recreate each
collection from the .ics file you saved.

> If you see a better way to get my old data into a clear new database
> please let me know.
>
> If I take version 0.9.6.2 to access the new database it works.
> If I take version 0.9.8 to access the new database it fails at the login.

The process of converting a database from the structures present in
0.9.6.2 to the structures present in 0.9.8 is defined in a series of
patchfiles which are applied in sequence by update-davical-database.
That's the intended way of upgrading. There is no need to install
intermediate versions, either, since all database patchfiles back into
the prehistory of DAViCal are shipped with every version.

If that upgrade process doesn't work, then it is a bug, and I need to
know about it so I can fix it.

Cheers,
Andrew.

------------------------------------------------------------------------
http://andrew.mcmillan.net.nz/ Porirua, New Zealand
Twitter: _karora Phone: +64(272)DEBIAN
Facts do not cease to exist because they are ignored. -- Aldous Huxley,
"Proper Studies", 1927

------------------------------------------------------------------------

-------------- next part --------------
A non-text attachment was scrubbed...
Name: not available
Type: application/pgp-signature
Size: 198 bytes
Desc: This is a digitally signed message part
URL: <http://lists.morphoss.com/pipermail/davical-users/attachments/20100129/ce01032b/attachment.pgp>
-------------- next part --------------

Upgrade davical on FreeBSD [ In reply to ]

idefix at fechner

Jan 29, 2010, 10:30 AM

Post #9 of 9 (1854 views)

Permalink

Hi Andrew,

Am 28.01.2010 14:06, schrieb Andrew McMillan:
> No, that's exactly what I thought you had done, and I think it's the
> wrong way to do it :-)
>
> If it did work, it would only work for the same DAViCal version, so why
> do it at all?

the reason why I was forced to go that way was, I tried to upgrade my
database with the script dba/update-davical-database but it failed, I
sent there some time ago an email to the list or to your directly, I
cannot remember.

After that the database was broken completely and I had to use a backup.
But I created my backup only with pg_dump davical and there it seems
that some parts of the database were not dumped and I failed to restore it.

So I checked my backup file and found that it is filled by normal sql
commands (sorry I don't know postgres but I was able to load the data
into a freshly created database, is very similar to other databases).
I think I tried to restore the database with the psql command what I see
now was the wrong way :)

> If you really want to go back to a re-created database and add to it,
> then do a GET on each calendar and save it to an .ics file, then create
> the new database, and the principals and so forth, and recreate each
> collection from the .ics file you saved.

But I was now able to get the complete caldav server up again with all
information in it.
What I did was, started iCal and exported every calendar to an .ical file.

I droped the completed davical database and created it freshly.
Created a new user and configured iCal to connect with this user to the
database (wow a lot of changes on the webinterface). Create the
calendars with iCal and imported them.
And now it works with iCal, my iPhone and with Lightning, really great!

Thanks a lot for your help Andrew!

Sorry that I cannot help you further with the failed upgrade of my
database. The old database was removed from my backup rotation.

Thanks
Matthias

--

"Programming today is a race between software engineers striving to
build bigger and better idiot-proof programs, and the universe trying to
produce bigger and better idiots. So far, the universe is winning." --
Rich Cook