> On Feb 5, 2015, at 12:19 PM, Andre LaBranche <[email protected]> wrote: > > >> On Feb 5, 2015, at 8:51 AM, Jacques Distler <[email protected]> >> wrote: >> >>> There is one known issue in 5.3 regarding how we handle updating calendar >>> event data when the format changes (as it has recently). In 5.3, the >>> approach is to leave the data in the old format until it is requested, and >>> then update it on the fly as it is vended to the client. This fails when >>> there are enough events being 'touched' by the request that the processing >>> time for doing the format upgrades exceeds the client's request timeout >>> threshold. This is fixed in 5.4-dev. >> >> Is there (say) a commandline utility to upgrade the data to the new format? > > We don’t have a single command line tool to do this, no... > >> Or some other way to address these (rather persistent) timeouts? > > You can find out how many resources are in the old format by running this SQL: > select count(*) from calendar_object where dataversion = 0;
Well, now you have me REALLY confused.
I did a calendarserver_upgrade when I upgraded to 5.3 (and, again, for good
measure when I upgraded to 5.4dev). So I THOUGHT I had the latest schema
version. But there's no "dataversion" column in the "calendar_object" table:
% psql -U caldav -d caldav -c "\d calendar_object;"
Table "public.calendar_object"
Column | Type |
Modifiers
----------------------+-----------------------------+-------------------------------------------------------
resource_id | integer | not null default
nextval('resource_id_seq'::regclass)
calendar_resource_id | integer | not null
resource_name | character varying(255) | not null
icalendar_text | text | not null
icalendar_uid | character varying(255) | not null
icalendar_type | character varying(255) | not null
attachments_mode | integer | not null default 0
dropbox_id | character varying(255) |
organizer | character varying(255) |
recurrance_min | date |
recurrance_max | date |
access | integer | not null default 0
schedule_object | boolean | default false
schedule_tag | character varying(36) | default NULL::character
varying
schedule_etags | text |
private_comments | boolean | not null default false
md5 | character(32) | not null
created | timestamp without time zone | default
timezone('UTC'::text, now())
modified | timestamp without time zone | default
timezone('UTC'::text, now())
Indexes:
"calendar_object_pkey" PRIMARY KEY, btree (resource_id)
"calendar_object_calendar_resource_id_resource_name_key" UNIQUE CONSTRAINT,
btree (calendar_resource_id, resource_name)
"calendar_object_calendar_resource_id_and_icalendar_uid" btree
(calendar_resource_id, icalendar_uid)
"calendar_object_calendar_resource_id_recurrance_max" btree
(calendar_resource_id, recurrance_max)
"calendar_object_dropbox_id" btree (dropbox_id)
"calendar_object_icalendar_uid" btree (icalendar_uid)
Foreign-key constraints:
"calendar_object_calendar_resource_id_fkey" FOREIGN KEY
(calendar_resource_id) REFERENCES calendar(resource_id) ON DELETE CASCADE
>
> If the timeouts are being caused by the data format upgrade taking too long
> (because it’s operating in batches that are too large), one way to work
> around it is to manually request all the calendar resources one by one,
> causing each one to be upgraded individually. I had to work around this
> problem on a live server without the benefit of any code fixes, and here’s
> what I ended up doing.
>
> There are two scripts: make-curl-command-file.sh and old_resources.sql; the
> contents of each are shown below, along with output from a run of
> make-curl-command-file.sh. To use this, you need to specify a principal in
> ReadPrincipals in caldavd.plist - preferably an admin user - to authorize
> that user to access all the resources. ReadPrincipals is an array, with
> string values specifying principal URLs, like the ones you see on a user’s
> principal page (at /principals/users/foo). Use the credentials for this user
> with the scripts below by creating a netrc file for curl. The script gives an
> example if netrc is missing. You may also need to customize SOCKETPATH, or
> perhaps switch to tcp/ip - depending on how your postgres service is deployed.
>
>
> (and note that although this script worked for me, it comes with no warranty)
>
>
> # begin make-curl-command-file.sh
> #!/bin/bash
>
> # * execute some SQL that emits URLs of all calendar events with an old data
> # format.
> # * put the results into a new file, trimming the leading and trailing lines
> # * emit the curl command required to process this file
> # * run that command
>
> SOCKETPATH="/var/run/caldavd/PostgresSocket"
> PGUSER="caldav"
> SQLFILE="old_resources.sql"
> CURLFILE="curl-commands.txt"
> NETRC="netrc.txt"
>
> rm ${CURLFILE}
> touch ${CURLFILE}
>
> echo "Getting total number of un-upgraded items: "
> sudo psql -h ${SOCKETPATH} -U ${PGUSER} -c "select count(*) from
> calendar_object where dataversion = 0;"
> echo ""
>
> echo "Gathering list of events that need to be upgraded in 1000 item
> chunks..."
> sudo psql -q -h ${SOCKETPATH} -U ${PGUSER} -f ${SQLFILE} | grep "url =" >
> ${CURLFILE}
> echo ""
>
> ITEMS=$(wc -l ${CURLFILE} | awk '{print $1}')
>
> if [ ${ITEMS} -eq 0 ]
> then echo "No more work to do!"
> exit 0
> fi
>
> echo -n "Generated curl command file with this many items: ${ITEMS}"
> echo ""
>
> if [ ! -f ${NETRC} ]
> then
> echo "Put the credentials of a ReadAdmin user into ${NETRC}, e.g. :"
> echo "machine example.com login Administrator password 12345"
> echo ""
> fi
>
> echo ""
> echo "now running:"
> echo "curl --netrc-file ${NETRC} -K ${CURLFILE} > /dev/null"
> echo ""
> curl --netrc-file ${NETRC} -K ${CURLFILE} > /dev/null
> echo "Re-run this script until it has no more work to do.”
> # end make-curl-command-file.sh
>
>
>
> # begin old_resources.sql
> SELECT 'url = "https://example.com:8443/calendars/__uids__/'
> || owner_uid
> || '/'
> || calendar_resource_name
> || '/'
> || resource_name
> || '"' as "curl command file contents"
> FROM calendar_bind
> JOIN calendar_home
> ON calendar_bind.calendar_home_resource_id = calendar_home.resource_id
> JOIN calendar_object
> ON calendar_bind.calendar_resource_id =
> calendar_object.calendar_resource_id
> WHERE calendar_bind.bind_mode = 0
> AND calendar_object.dataversion = 0
> LIMIT 1000;
> # end old_resources.sql
>
>
> server:bin administrator$ ./make-curl-command-file.sh
> Total number of un-upgraded items: count
> -------
> 48842
> (1 row)
>
>
> Gathering list of events that need to be upgraded in 1000 item chunks...
>
> Generated curl command file with this many items: 1000
>
> now running:
> curl --netrc-file netrc.txt -K curl-commands.txt > /dev/null
>
> % Total % Received % Xferd Average Speed Time Time Time Current
> Dload Upload Total Spent Left Speed
> 100 1514 100 1514 0 0 7656 0 --:--:-- --:--:-- --:--:-- 7685
> 100 803 100 803 0 0 5378 0 --:--:-- --:--:-- --:--:-- 784k
> 100 1140 100 1140 0 0 7580 0 --:--:-- --:--:-- --:--:-- 7580
> 100 707 100 707 0 0 4813 0 --:--:-- --:--:-- --:--:-- 4813
> 100 733 100 733 0 0 4982 0 --:--:-- --:--:-- --:--:-- 4982
> 100 1035 100 1035 0 0 6948 0 --:--:-- --:--:-- --:--:-- 6948
> 100 691 100 691 0 0 4690 0 --:--:-- --:--:-- --:--:-- 4690
> 100 1317 100 1317 0 0 8444 0 --:--:-- --:--:-- --:--:-- 1286k
> 100 748 100 748 0 0 5102 0 --:--:-- --:--:-- --:--:-- 5102
> 100 368 100 368 0 0 2507 0 --:--:-- --:--:-- --:--:-- 2507
> 100 725 100 725 0 0 4943 0 --:--:-- --:--:-- --:--:-- 4943
> 100 717 100 717 0 0 4773 0 --:--:-- --:--:-- --:--:-- 4773
> ...
>
>
>
>
>> FWIW, I ran calendarserver_verify_data and everything checked out OK.
>
> Good deal.
>
>
> -dre
signature.asc
Description: Message signed with OpenPGP using GPGMail
_______________________________________________ calendarserver-users mailing list [email protected] https://lists.macosforge.org/mailman/listinfo/calendarserver-users
