[CalendarServer-users] Connection aborted - took too long to close
Jacques Distler
distler at golem.ph.utexas.edu
Thu Feb 5 13:42:42 PST 2015
> On Feb 5, 2015, at 12:19 PM, Andre LaBranche <dre at apple.com> wrote:
>
>
>> On Feb 5, 2015, at 8:51 AM, Jacques Distler <distler at golem.ph.utexas.edu> 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
-------------- next part --------------
A non-text attachment was scrubbed...
Name: signature.asc
Type: application/pgp-signature
Size: 230 bytes
Desc: Message signed with OpenPGP using GPGMail
URL: <https://lists.macosforge.org/pipermail/calendarserver-users/attachments/20150205/7305c939/attachment.sig>
More information about the calendarserver-users
mailing list