6.0.0-git
2019-04-24

[#12979] IMAP Client issues / Wrong SQL statement?
Summary IMAP Client issues / Wrong SQL statement?
Queue Horde Framework Packages
Queue Version Git master
Type Bug
State No Feedback
Priority 2. Medium
Owners jan (at) horde (dot) org
Requester markus (at) mwagner (dot) info
Created 2014-02-18 (1891 days ago)
Due
Updated 2014-04-28 (1822 days ago)
Assigned 2014-02-19 (1890 days ago)
Resolved 2014-03-21 (1860 days ago)
Milestone
Patch No

History
2014-04-28 07:46:57 wojnas (at) gmail (dot) com Comment #11 Reply to this comment

[Show Quoted Text - 9 lines]
Hello,
i got the same problem, after upgrade horde 2 weeks ago by pear.
Here is my database structure (there is no autoincremet option i 
messageid column):

mysql> DESCRIBE horde_imap_client_data;
+-----------+------------------+------+-----+---------+-------+
| Field     | Type             | Null | Key | Default | Extra |
+-----------+------------------+------+-----+---------+-------+
| messageid | int(10) unsigned | NO   | PRI | 0       |       |
| hostspec  | varchar(255)     | NO   | MUL | NULL    |       |
| mailbox   | varchar(255)     | NO   |     | NULL    |       |
| modified  | bigint(20)       | YES  |     | NULL    |       |
| port      | int(11)          | NO   |     | NULL    |       |
| username  | varchar(255)     | NO   |     | NULL    |       |
+-----------+------------------+------+-----+---------+-------+
6 rows in set (0.00 sec)

New users can't log to webmail.
He is error from my log file

Apr 28 09:26:18 hostname HORDE: [imp] SQL QUERY FAILED: 
SQLSTATE[23000]: Integrity constraint violation: 1062 Duplicate entry 
'0' for key 'PRIMARY'  #012#011INSERT INTO horde_imap_client_data 
(hostspec, mailbox, port, username)#012#011  VALUES ('host', 'INBOX', 
'143', 'username@domain') [pid 9236 on line 204 of 
"/usr/share/pear/Horde/Db/Adapter/Pdo/Base.php"]

How can i fix the problem?

2014-03-21 12:16:53 Jan Schneider State ⇒ No Feedback
 
2014-02-19 13:28:31 markus (at) mwagner (dot) info Comment #10 Reply to this comment
Always via official debian repository (though sid/experimental).
(I used to have a PEAR installation way before...)
So, did you install the original horde_imap_client version through 
apt/dpkg and then upgraded via PEAR?
Do you remember the versions of that library before and after the upgrade?
It starts to sound like a botched upgrade rather than a design issue 
of the migration scripts.
2014-02-19 13:20:40 Jan Schneider Comment #9
Version ⇒ Git master
Reply to this comment
So, did you install the original horde_imap_client version through 
apt/dpkg and then upgraded via PEAR?
Do you remember the versions of that library before and after the upgrade?
It starts to sound like a botched upgrade rather than a design issue 
of the migration scripts.
2014-02-19 13:03:39 markus (at) mwagner (dot) info Comment #8 Reply to this comment
I don't know, if during migration the table has been altered to add 
the auto_increment feature.

I tried to do so manually and it caused a hick-up due to an entry with 
'0' as messageid, thus setting the auto-increment sequence to 1 and 
having a PRIMARY violation. Setting this record to anything > 0 will 
make the table alteration successful.

[Show Quoted Text - 26 lines]
2014-02-19 10:46:31 markus (at) mwagner (dot) info Comment #7 Reply to this comment
And are you really using FRAMEWORK_5_1 code?
I was assuming so, since Horde says in the upper left, that version is 
"5.1.5".

Unfortunately, checking for newer versions under adminstration does 
not work anymore... (but this ought to be another story)

Thanks!
2014-02-19 10:44:20 markus (at) mwagner (dot) info Comment #6 Reply to this comment
I am using MySQL - and yes, I assume it has been migrated from an 
earlier version. (I am using Debian sid/experimental packages and at 
some point imap_client came into the game)

--
-- Table structure for table `horde_imap_client_data`
--
CREATE TABLE IF NOT EXISTS `horde_imap_client_data` (
   `messageid` int(10) unsigned NOT NULL DEFAULT '0',
   `hostspec` varchar(255) NOT NULL,
   `mailbox` varchar(255) NOT NULL,
   `modified` bigint(20) DEFAULT NULL,
   `port` int(11) NOT NULL,
   `username` varchar(255) NOT NULL,
   PRIMARY KEY (`messageid`),
   KEY 
`index_horde_imap_client_data_on_hostspec_and_mailbox_and_port_an` 
(`hostspec`,`mailbox`,`port`,`username`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

So I assume, the autoincrement got lost at some point...
Sounds like your messageid column is not an autoincrement column 
(anymore?). What database do you use, and has this been migrated 
from an earlier version of the Horde_Imap_Client database schema?
2014-02-19 10:36:35 Jan Schneider Comment #5 Reply to this comment
And are you really using FRAMEWORK_5_1 code?
2014-02-19 10:36:01 Jan Schneider Comment #4
State ⇒ Feedback
Reply to this comment
Sounds like your messageid column is not an autoincrement column 
(anymore?). What database do you use, and has this been migrated from 
an earlier version of the Horde_Imap_Client database schema?
2014-02-19 09:18:02 markus (at) mwagner (dot) info Comment #3 Reply to this comment
But I _do_ have the column 'messageid' and no column 'uid' in table 
'horde_imap_client_data' !?

Also, my running codebase shows in file 
1_horde_imap_client_base_tables.php 'messageid' as autoincrementKey.

BTW: I can move messages to older folders. But when creating a folder, 
it seems that the appropriate enrtry cannot be made into table 
'horde_imap_client_data'.

Further ideas to investigate on this one?

2014-02-19 06:56:43 Michael Slusarz Comment #2
Assigned to Jan Schneider
State ⇒ Assigned
Reply to this comment
This was changed by this commit:

commit f47da21798ffc4d1664e167a620ca846c2808ff6
Author: Jan Schneider <jan@horde.org>
Date:   Tue Feb 11 23:06:14 2014 +0100

     Rename uid column to messageid.

     uid is a reserved word on Oracle databases.
2014-02-18 12:33:07 markus (at) mwagner (dot) info Comment #1
Type ⇒ Bug
State ⇒ Unconfirmed
Priority ⇒ 2. Medium
Summary ⇒ IMAP Client issues / Wrong SQL statement?
Due ⇒ 2014-02-28
Queue ⇒ Horde Framework Packages
Milestone ⇒
Patch ⇒ No
New Attachment: horde5-imp-sql-error.png Download
Reply to this comment
When moving a message with IMP on my IMAP-Server from INBOX to 
Folder.Subfolder I receive the following error (attachment) and also 
the following log entry is issued.

2014-02-18T13:16:23+01:00 ERR: HORDE [imp] SQL QUERY FAILED: 
SQLSTATE[23000]: Integrity constraint violation: 1062 Duplicate entry 
'0' for key 'PRIMARY'
         INSERT INTO horde_imap_client_data (hostspec, mailbox, port, username)
           VALUES ('localhost', 'Folder.Subfolder', '143',
           'user@domain.com') [pid 16977 on line 553 of 
"/usr/share/php/Horde/Db/Adapter/Base.php"

There is a messageid field in the horde_imap_client_data table which 
is set to 0 already for "Folder". Other folders have other numbers.

Also, when looking at the folder (e.g. click to show content), I just 
see nothing/empty folder.
However, for other (older) IMAP-Folders everything works perfectely.

What could be the reason for this?! DB out of date/not properly updated?

Thanks for any pointers/fixes.


Saved Queries