Primary Key must be unique error

[jaredmauch]

trafficstars

General informations

• system/distribution (with version): centos 7.0
• offlineimap version (offlineimap -V): 7.0.5 + 7.0.6
• Python version: 2.7.5
• server name or domain:
• CLI options: n/a

Configuration file offlineimaprc

[general]

metadata = ~/.offlineimap

accounts = NTT

maxsyncaccounts = 1

socktimeout = 30

[mbnames]

enabled = no
filename = ~/Mutt/muttrc.mailboxes
header = "mailboxes "
peritem = "+%(accountname)s/%(foldername)s"
sep = " "
footer = "\n"

[Account NTT]

localrepository = LocalNTT
remoterepository = RemoteNTT

autorefresh = 4

quick = 10

[Repository LocalNTT]

type = Maildir

localfolders = ~/NTT

[Repository RemoteNTT]

type = IMAP

remotehost = mail.us.ntt.net

ssl = yes

cert_fingerprint = 86919ab4738503b01a6ab946748a07814c4592fc

remoteuser = [email protected]

idlefolders = ['INBOX']

maxconnections = 4

holdconnectionopen = yes

folderfilter = lambda foldername: not re.search('(^Trash$|5YearsAndBeyond)', foldername)

[Repository GmailExample]

type = Gmail

remoteuser = [email protected]

pythonfile (if any)

n/a

Logs, error

Traceback:
  File "/usr/lib/python2.7/site-packages/offlineimap/folder/Base.py", line 814, in copymessageto
    message = self.getmessage(uid)
  File "/usr/lib/python2.7/site-packages/offlineimap/folder/IMAP.py", line 297, in getmessage
    data = self._fetch_from_imap(str(uid), 2)
  File "/usr/lib/python2.7/site-packages/offlineimap/folder/IMAP.py", line 756, in _fetch_from_imap
    raise OfflineImapError(reason, severity)

ERROR: IMAP server 'RemoteNTT' does not have a message with UID '288709'

Traceback:
  File "/usr/lib/python2.7/site-packages/offlineimap/folder/Base.py", line 814, in copymessageto
    message = self.getmessage(uid)
  File "/usr/lib/python2.7/site-packages/offlineimap/folder/IMAP.py", line 297, in getmessage
    data = self._fetch_from_imap(str(uid), 2)
  File "/usr/lib/python2.7/site-packages/offlineimap/folder/IMAP.py", line 756, in _fetch_from_imap
    raise OfflineImapError(reason, severity)

ERROR: while syncing INBOX [account NTT]
  PRIMARY KEY must be unique

Traceback:
  File "/usr/lib/python2.7/site-packages/offlineimap/folder/Base.py", line 1090, in syncmessagesto
    action(dstfolder, statusfolder)
  File "/usr/lib/python2.7/site-packages/offlineimap/folder/Base.py", line 902, in __syncmessagesto_copy
    statusfolder.savemessage(uid, None, flags, rtime)
  File "/usr/lib/python2.7/site-packages/offlineimap/folder/LocalStatusSQLite.py", line 341, in savemessage
    (uid,flags,mtime,labels))
  File "/usr/lib/python2.7/site-packages/offlineimap/folder/LocalStatusSQLite.py", line 170, in __sql_write
    self.connection.execute(sql, args)

ERROR: ERROR in syncfolder for NTT folder INBOX: Traceback (most recent call last):
  File "/usr/lib/python2.7/site-packages/offlineimap/accounts.py", line 584, in syncfolder
    remotefolder.syncmessagesto(localfolder, statusfolder)
  File "/usr/lib/python2.7/site-packages/offlineimap/folder/Base.py", line 1090, in syncmessagesto
    action(dstfolder, statusfolder)
  File "/usr/lib/python2.7/site-packages/offlineimap/folder/Base.py", line 902, in __syncmessagesto_copy
    statusfolder.savemessage(uid, None, flags, rtime)
  File "/usr/lib/python2.7/site-packages/offlineimap/folder/LocalStatusSQLite.py", line 341, in savemessage
    (uid,flags,mtime,labels))
  File "/usr/lib/python2.7/site-packages/offlineimap/folder/LocalStatusSQLite.py", line 170, in __sql_write
    self.connection.execute(sql, args)
IntegrityError: PRIMARY KEY must be unique

  PRIMARY KEY must be unique
ERROR: while syncing INBOX [account NTT]
  PRIMARY KEY must be unique

Traceback:
  File "/usr/lib/python2.7/site-packages/offlineimap/folder/Base.py", line 1090, in syncmessagesto
    action(dstfolder, statusfolder)
  File "/usr/lib/python2.7/site-packages/offlineimap/folder/Base.py", line 902, in __syncmessagesto_copy
    statusfolder.savemessage(uid, None, flags, rtime)
  File "/usr/lib/python2.7/site-packages/offlineimap/folder/LocalStatusSQLite.py", line 341, in savemessage
    (uid,flags,mtime,labels))
  File "/usr/lib/python2.7/site-packages/offlineimap/folder/LocalStatusSQLite.py", line 170, in __sql_write
    self.connection.execute(sql, args)

ERROR: ERROR in syncfolder for NTT folder INBOX: Traceback (most recent call last):
  File "/usr/lib/python2.7/site-packages/offlineimap/accounts.py", line 584, in syncfolder
    remotefolder.syncmessagesto(localfolder, statusfolder)
  File "/usr/lib/python2.7/site-packages/offlineimap/folder/Base.py", line 1090, in syncmessagesto
    action(dstfolder, statusfolder)
  File "/usr/lib/python2.7/site-packages/offlineimap/folder/Base.py", line 902, in __syncmessagesto_copy
    statusfolder.savemessage(uid, None, flags, rtime)
  File "/usr/lib/python2.7/site-packages/offlineimap/folder/LocalStatusSQLite.py", line 341, in savemessage
    (uid,flags,mtime,labels))
  File "/usr/lib/python2.7/site-packages/offlineimap/folder/LocalStatusSQLite.py", line 170, in __sql_write
    self.connection.execute(sql, args)
IntegrityError: PRIMARY KEY must be unique

Steps to reproduce the error

• Launch offlineimap
• Let it run for some time, this error is triggered.
• I suspect this is related to the numerous IMAP clients attached to my account, eg: Phone, Mail.app, offlineimap, etc. and some race condition triggered when processing the results.

[nicolas33]

Is this error reproducible on next run for the exact same UID?

[jaredmauch]

if I restart it, it recovers ok. I run offlineimap detached in a screen so don't notice when it fails immediately.

[nicolas33]

I wonder the error stands because of maxconnections = 4. You only have one idle folder, so I'd try to reduce this value to 2 (or even one if all the emails are downloaded by offlineimap are from INBOX).

I'm pretty sure that's a IDLE issue where offlineimap does the same job twice: trying to save the same UID twice in the database.

[jaredmauch]

is there a good trace i can enable for this? my mailbox is quite large on the backside (~1.37m messages). I can trigger this usually 1-3x a day, so good debugs should be easy to come by.

[nicolas33]

IntegrityError: PRIMARY KEY must be unique means offlineimap tries to create the same UID twice while this should not happen. The root cause is likely that offlineimap downloads the same UID concurrently. This should never happen. Tracking down issues about concurrency is not easy. I don't think the debug logs will help much.

You might keep maxconnections set to 4 and try v7.0.6 with singlethreadperfolder enabled (new feature). This might help.

[nicolas33]

I think this issue and #174 are the same. I bet the printed error is different because of the database backend. SQLite is more strict than legacy text.

[jaredmauch]

Ok, have to build a new package and hand to our OS team. I didn't poke at the code, you don't lock the UID to prevent multiple threads from accessing at once?

[nicolas33]

There's no "lock per UID" concept in the code.

[nicolas33]

It's possible to install offlineimap as a user: remove the currently installed offlineimap and download the zip (or git clone the repository). Then ./offlineimap.py [options].

[jaredmauch]

yeah, i'd rather the other users pick up 7.0.6 as well. let me do my thing and i'll get back in the next ~24h

[jaredmauch]

Apologies for the late reply, I'm still seeing the issue with 7.0.6:

prior 2 cycles:

Syncing IP-ENG-OPS: IMAP -> Maildir
 Copy message UID 615517 (1/3) RemoteNTT:IP-ENG-OPS -> LocalNTT
 Copy message UID 615518 (2/3) RemoteNTT:IP-ENG-OPS -> LocalNTT
 Copy message UID 615519 (3/3) RemoteNTT:IP-ENG-OPS -> LocalNTT
Account sync NTT:
 *** Finished account 'NTT' in 0:42
 Next refresh in 4.0 minutes  
 Next refresh in 3.0 minutes  
Thread-73:
 Syncing INBOX: IMAP -> Maildir
 Copy message UID 289029 (1/1) RemoteNTT:INBOX -> LocalNTT

Folder IP-ENG-OPS [acc: NTT]: 
 Syncing IP-ENG-OPS: IMAP -> Maildir
 Copy message UID 615520 (1/3) RemoteNTT:IP-ENG-OPS -> LocalNTT
 Copy message UID 615521 (2/3) RemoteNTT:IP-ENG-OPS -> LocalNTT
 Copy message UID 615522 (3/3) RemoteNTT:IP-ENG-OPS -> LocalNTT
Thread 'Copy message from RemoteNTT:IP-ENG-OPS' terminated with exception:
Traceback (most recent call last):
  File "/home/jared/offlineimap/offlineimap/threadutil.py", line 163, in run
    Thread.run(self)
  File "/usr/lib64/python2.7/threading.py", line 764, in run
    self.__target(*self.__args, **self.__kwargs)
  File "/home/jared/offlineimap/offlineimap/folder/Base.py", line 814, in copymessageto
    message = self.getmessage(uid)
  File "/home/jared/offlineimap/offlineimap/folder/IMAP.py", line 302, in getmessage
    data = self._fetch_from_imap(str(uid), 2)
  File "/home/jared/offlineimap/offlineimap/folder/IMAP.py", line 720, in _fetch_from_imap
    imapobj.select(self.getfullname(), readonly=True)
  File "/home/jared/offlineimap/offlineimap/imaplibutil.py", line 64, in select
    exc_info()[2])
  File "/home/jared/offlineimap/offlineimap/imaplibutil.py", line 53, in select
    result = super(UsefulIMAPMixIn, self).select(mailbox, readonly)
  File "/home/jared/offlineimap/offlineimap/bundled_imaplib2.py", line 1053, in select
    typ, dat = rqb.get_response('command: %s => %%s' % rqb.name)
  File "/home/jared/offlineimap/offlineimap/bundled_imaplib2.py", line 200, in get_response
    raise typ(exc_fmt % str(val))
OfflineImapError: Server 'mail.us.ntt.net' closed connection, error on SELECT 'IP-ENG-OPS'. Server said: command: EXAMINE => no response after 30.0 secs
Last 1 debug messages logged for Copy message from RemoteNTT:IP-ENG-OPS prior to exception:
thread: Register new thread 'Copy message from RemoteNTT:IP-ENG-OPS' (account 'NTT')
ERROR: Exceptions occurred during the run!
ERROR: IMAP server 'RemoteNTT' does not have a message with UID '289023'
Traceback:
  File "/home/jared/offlineimap/offlineimap/folder/Base.py", line 814, in copymessageto
    message = self.getmessage(uid)
  File "/home/jared/offlineimap/offlineimap/folder/IMAP.py", line 302, in getmessage
    data = self._fetch_from_imap(str(uid), 2)
  File "/home/jared/offlineimap/offlineimap/folder/IMAP.py", line 761, in _fetch_from_imap
    raise OfflineImapError(reason, severity)
ERROR: IMAP server 'RemoteNTT' does not have a message with UID '289027'
Traceback:
  File "/home/jared/offlineimap/offlineimap/folder/Base.py", line 814, in copymessageto
    message = self.getmessage(uid)
  File "/home/jared/offlineimap/offlineimap/folder/IMAP.py", line 302, in getmessage
    data = self._fetch_from_imap(str(uid), 2)
  File "/home/jared/offlineimap/offlineimap/folder/IMAP.py", line 761, in _fetch_from_imap
    raise OfflineImapError(reason, severity)

[nicolas33]

And does the server has UIDs 289023 and 289027 in the RemoteNTT folder?

[jaredmauch]

Seems so for 23, not for 27.

A100 UID FETCH 289023:289030 FLAGS
* 52454 FETCH (UID 289023 FLAGS (\Seen $NotJunk NotJunk))
* 52455 FETCH (UID 289024 FLAGS (\Seen $NotJunk NotJunk))
* 52456 FETCH (UID 289025 FLAGS (\Seen $NotJunk NotJunk))
* 52457 FETCH (UID 289026 FLAGS (\Seen $NotJunk NotJunk))
* 52458 FETCH (UID 289028 FLAGS (\Seen $NotJunk NotJunk))
* 52459 FETCH (UID 289029 FLAGS ($NotJunk NotJunk))
* 52460 FETCH (UID 289030 FLAGS (\Seen $NotJunk NotJunk))
* 52499 EXISTS
A100 OK Fetch completed.

[nicolas33]

Weird. Does the next run fail on those UIDs (with and without a full restart)?

[jaredmauch]

due to the error offlineimap stops and i must manually restart it. (i leave it in detached screen and require my password as interactive to launch).

the error does not exist after restart and it proceeds as fine.. it seems if i were to:

while true do; 
    offlineimap;
done

with my password in the rc file it would recover without issue. I have other colleagues who use this as a workaround/solution.

[jaredmauch]

Here's another related/similar error/exception:

 Syncing Sent Messages: IMAP -> Maildir
 Skipping Sent Messages (not changed)
Folder INBOX [acc: NTT]:
 Syncing INBOX: IMAP -> Maildir
Thread-109:
 Copy message UID 289063 (1/2) RemoteNTT:INBOX -> LocalNTT
 Copy message UID 289064 (2/2) RemoteNTT:INBOX -> LocalNTT
Folder stats [acc: NTT]:
 Syncing stats: IMAP -> Maildir
 Skipping stats (not changed)
Folder RANCID [acc: NTT]:
 Syncing RANCID: IMAP -> Maildir
 Skipping RANCID (not changed)
Folder IP-ENG-OPS [acc: NTT]:
 Syncing IP-ENG-OPS: IMAP -> Maildir
Folder INBOX [acc: NTT]:
 Copy message UID 401 (1/52494) RemoteNTT:INBOX -> LocalNTT
 Copy message UID 402 (2/52494) RemoteNTT:INBOX -> LocalNTT
 Copy message UID 404 (3/52494) RemoteNTT:INBOX -> LocalNTT
 Copy message UID 410 (4/52494) RemoteNTT:INBOX -> LocalNTT
 Copy message UID 411 (5/52494) RemoteNTT:INBOX -> LocalNTT
Copy message from RemoteNTT:INBOX:
 ERROR: Copying message 404 [acc: NTT]
  PRIMARY KEY must be unique
Folder INBOX [acc: NTT]:
 Copy message UID 412 (6/52494) RemoteNTT:INBOX -> LocalNTT
Thread 'Copy message from RemoteNTT:INBOX' terminated with exception:
Traceback (most recent call last):
  File "/home/jared/offlineimap/offlineimap/threadutil.py", line 163, in run
    Thread.run(self)
  File "/usr/lib64/python2.7/threading.py", line 764, in run
    self.__target(*self.__args, **self.__kwargs)
  File "/home/jared/offlineimap/offlineimap/folder/Base.py", line 827, in copymessageto
    statusfolder.savemessage(new_uid, message, flags, rtime)
  File "/home/jared/offlineimap/offlineimap/folder/LocalStatusSQLite.py", line 341, in savemessage
    (uid,flags,mtime,labels))
  File "/home/jared/offlineimap/offlineimap/folder/LocalStatusSQLite.py", line 170, in __sql_write
    self.connection.execute(sql, args)
IntegrityError: PRIMARY KEY must be unique
Last 4 debug messages logged for Copy message from RemoteNTT:INBOX prior to exception:
thread: Register new thread 'Copy message from RemoteNTT:INBOX' (account 'NTT')
imap: Returned object from fetching 404: 'Return-Path: 
Received: from mail1.us.ntt.net (mail1.us.ntt.net [129.250.38.32])
         (authenticated [email protected] b...> -b
> > > > >>>>>
> > > > >>>>
> > > > >>>
>=20
'
maildir: Write mail 'INBOX:404' with flags set(['S'])
maildir: savemessage: returning uid 404
ERROR: Exceptions occurred during the run!
ERROR: IMAP server 'RemoteNTT' does not have a message with UID '289046'
Traceback:
  File "/home/jared/offlineimap/offlineimap/folder/Base.py", line 814, in copymessageto
    message = self.getmessage(uid)
  File "/home/jared/offlineimap/offlineimap/folder/IMAP.py", line 302, in getmessage
    data = self._fetch_from_imap(str(uid), 2)  
  File "/home/jared/offlineimap/offlineimap/folder/IMAP.py", line 761, in _fetch_from_imap
    raise OfflineImapError(reason, severity)   
ERROR: IMAP server 'RemoteNTT' does not have a message with UID '289048'
Traceback:
  File "/home/jared/offlineimap/offlineimap/folder/Base.py", line 814, in copymessageto
    message = self.getmessage(uid)
  File "/home/jared/offlineimap/offlineimap/folder/IMAP.py", line 302, in getmessage
    data = self._fetch_from_imap(str(uid), 2)  
  File "/home/jared/offlineimap/offlineimap/folder/IMAP.py", line 761, in _fetch_from_imap
    raise OfflineImapError(reason, severity)   
ERROR: IMAP server 'RemoteNTT' does not have a message with UID '289050'
Traceback:
  File "/home/jared/offlineimap/offlineimap/folder/Base.py", line 814, in copymessageto
    message = self.getmessage(uid)
  File "/home/jared/offlineimap/offlineimap/folder/IMAP.py", line 302, in getmessage
    data = self._fetch_from_imap(str(uid), 2)  
  File "/home/jared/offlineimap/offlineimap/folder/IMAP.py", line 761, in _fetch_from_imap
    raise OfflineImapError(reason, severity)   
ERROR: IMAP server 'RemoteNTT' does not have a message with UID '289063'
Traceback:
  File "/home/jared/offlineimap/offlineimap/folder/Base.py", line 814, in copymessageto
    message = self.getmessage(uid)
  File "/home/jared/offlineimap/offlineimap/folder/IMAP.py", line 302, in getmessage
    data = self._fetch_from_imap(str(uid), 2)  
  File "/home/jared/offlineimap/offlineimap/folder/IMAP.py", line 761, in _fetch_from_imap
    raise OfflineImapError(reason, severity)   
ERROR: Copying message 404 [acc: NTT]
  PRIMARY KEY must be unique
Traceback:
  File "/home/jared/offlineimap/offlineimap/folder/Base.py", line 827, in copymessageto
    statusfolder.savemessage(new_uid, message, flags, rtime)
  File "/home/jared/offlineimap/offlineimap/folder/LocalStatusSQLite.py", line 341, in savemessage
    (uid,flags,mtime,labels))
  File "/home/jared/offlineimap/offlineimap/folder/LocalStatusSQLite.py", line 170, in __sql_write
    self.connection.execute(sql, args)

[nicolas33]

The closed socket is another issue.

The primary key error is likely a bug in concurrency because we download the same UID more than once.

[nicolas33]

Forget to say that's related to IDLE, IMHO.

[nicolas33]

Did you enable singlethreadperfolder, BTW?

[jaredmauch]

No, but it's now set

[Repository RemoteNTT]
type = IMAP
singlethreadperfolder =yes
remotehost = mail.us.ntt.net
ssl = yes
cert_fingerprint = 86919ab4738503b01a6ab946748a07814c4592fc
remoteuser = [email protected]
idlefolders = ['INBOX']
maxconnections = 4
holdconnectionopen = yes
folderfilter = lambda foldername: not re.search('(^Trash$|5YearsAndBeyond)', foldername)

[nicolas33]

Well, I'm not sure this setting is honored in IDLE mode. I don't think this can harm, though. Thanks for your tests.

I've looked at the code about IDLE: that sucks.

[jaredmauch]

been running for almost 24h with singlethreadperfolder set.

[nicolas33]

Good to know! I'll wait for you to get more feedback after more time with this setup. Then, I'll add this information to the documentation.

[jaredmauch]

Been running for a week with no need to restart. perhaps this should just be the default option on the codebase, with setting it to no driving someone to debug it further?

[nicolas33]

I agree. Notice this feature is recent and still marked experimental. According to your positive feedbacks I think we can enable singlethreadperfolder by default for IDLE mode.

[nicolas33]

@jaredmauch Done. Could you try the current next branch with the singlethreadperfolder option disabled, please?

[nicolas33]

Please read unset instead of disabled. IOW, comment the singlethreadperfolder option. Explicitly enabling or disabling the option is still supported in IDLE mode.

[jaredmauch]

yeah, let me pull it onto that host.

[jaredmauch]

btw, when quitting i see this:

Account sync NTT:
 Next refresh in 3.0 minutes
^CTerminating NOW (this may take a few seconds)...
ERROR: Exceptions occurred during the run!
ERROR: ERROR in syncfolder for NTT folder INBOX: Traceback (most recent call last):
  File "/usr/lib/python2.7/site-packages/offlineimap/accounts.py", line 537, in syncfolder
    statusfolder.cachemessagelist()
  File "/usr/lib/python2.7/site-packages/offlineimap/folder/LocalStatusSQLite.py", line 234, in cach
emessagelist
    flags = set(row[1])
TypeError: 'NoneType' object is not iterable
  'NoneType' object is not iterable
ERROR: Copying message 82372 [acc: NTT]
  PRIMARY KEY must be unique
Traceback:
  File "/usr/lib/python2.7/site-packages/offlineimap/folder/Base.py", line 827, in copymessageto
    statusfolder.savemessage(new_uid, message, flags, rtime)
  File "/usr/lib/python2.7/site-packages/offlineimap/folder/LocalStatusSQLite.py", line 341, in save
message
    (uid,flags,mtime,labels))
  File "/usr/lib/python2.7/site-packages/offlineimap/folder/LocalStatusSQLite.py", line 170, in __sq
l_write
    self.connection.execute(sql, args)
ERROR: while syncing INBOX [account NTT]
  PRIMARY KEY must be unique

[nicolas33]

btw, when quitting i see this:

Which version?