[horde] duplicate key value violates unique constraint "horde_activesync_state_pkey"

[Sebastian Arcus]

On 15/05/18 10:55, Sebastian Arcus wrote:
> 
> On 14/05/18 13:52, Michael J Rubinsky wrote:
>>
>> Quoting Sebastian Arcus <s.arcus at open-t.co.uk>:
>>
>>> I'm on Horde 5.2.17 with Lighttpd 1.4.49, PHP 5.6.35 and Postgresql 
>>> 9.2.24
>>>
>>> I get lots of the above errors in the logs and most of the 8 iPhones 
>>> can't sync or download emails for several hours at a time, then they 
>>> work again, they they stop again - and so on. The full error message is:
>>>
>>> 2018-05-14T09:11:11+01:00 ERR: HORDE SQL QUERY FAILED: 
>>> SQLSTATE[23505]: Unique violation: 7 ERROR:  duplicate key value 
>>> violates unique constraint "horde_activesync_state_pkey"
>>> DETAIL:  Key (sync_key)=({5acb7873-baa4-4ea3-8f69-3d9bc0a83c02}1527) 
>>> already exists.
>>>     INSERT INTO "horde_activesync_state" ("sync_key", "sync_data",
>>>       "sync_devid", "sync_mod", "sync_folderid", "sync_user",
>>>       "sync_pending", "sync_timestamp") VALUES (?, :binary0, ?, ?, ?, 
>>> ?, ?,
>>>       ?) [pid 11736 on line 282 of 
>>> "/usr/lib/php/Horde/Db/Adapter/Pdo/Base.php"]
>>>
>>> I would really appreciate some guidance on this please - as the mail 
>>> downloading on the iPhones is really erratic and it drives users 
>>> bonkers - and in turn they are driving me up the wall :)
>>
>> The unique violation is explained in the sync log, it happens when the 
>> client either does not receive or does not understand the response it 
>> was sent, so it issues the same sync request again. This can happen 
>> from time to time under normal circumstances, but should not happen as 
>> often as you state or prevent sync for hours at a time unless there is 
>> something else going on.
>>
>> I would need to see the sync log for a device that is experiencing 
>> this behavior to help you debug the issue further.
> 
> Thank you Michael. I will keep an eye on the logs over the next few 
> days, as I have the feeling that error might not be the real cause of 
> why the phones stop syncing for hours at a time. I will post back here 
> in a few days.

Further to my previous messages - it would seem that the "duplicate key 
violates unique constraint error" had nothing to do with emails being 
very slow to download or sometimes delayed for hours on iPhones over 
ActiveSync. It now seems that the server was starved of php-cgi 
processes - as I've left the limit too low in Lighttpd configs without 
realising. Once I raised the number of max php-cgi children high enough, 
everything has been working much faster and emails downloading within a 
few seconds max. I've kept an eye on things for a week and it seems to 
be solved.

I don't know if it would be possible for Horde to detect such a 
condition - of the webserver not responding for long times - and output 
something helpful to the Horde logs? But maybe that is not possible and 
it is entirely down to the webserver setup. Anyway, I thought I would 
post here in case anybody else stumbles over the same problem.

Thank you for the previous hints and suggestions.