Problems importing emails using IMAP and Zimbra server 8.8.7

[Jorge Gomes]

Hi Janos, Server is a Centos 7, updated and with PHP 7.2

Piler is 1.3.4 .upgraded from 1.3.1
MySQL is :: mysql  Ver 15.1 Distrib 10.2.16-MariaDB, for Linux (x86_64) using readline 5.1

Yesterday I upgraded Piler to version 1.3.4 from version 1.3.1. I have this server running from the last 3 months but never tried to import anything to it, but since I want to free some space on the Zimbra server, I tried it yesterday, so I'm seeing 2 things happening::

1 - Error messages on maillog; 2 - I cant import mailboxes, they get stuck, and yes I read the posts I could find, but with no help so far;

So, messages on maillog are::

Aug  3 07:49:24 mailarchive pilerimport[53043]: error: helper: execl
Aug  3 07:49:28 mailarchive pilerimport[53039]: ERROR: 400000005b63fb013a56130c0065304be08f: mysql_stmt_execute() 'Data too long for column 'body' at row 1' (errno: 1406)
Aug  3 07:49:28 mailarchive pilerimport[53039]: ERROR: 400000005b63fb013a56130c0065304be08f failed to store index data for id=72433, sql_errno=1406
Aug  3 07:49:28 mailarchive pilerimport[53039]: ERROR: 400000005b63fb013a56130c0065304be08f: rollback sql stmt=DELETE FROM sph_index WHERE id=72433
Aug  3 07:49:28 mailarchive pilerimport[53039]: ERROR: 400000005b63fb013a56130c0065304be08f: rollback sql stmt=DELETE FROM rcpt WHERE id=72433
Aug  3 07:49:28 mailarchive pilerimport[53039]: ERROR: 400000005b63fb013a56130c0065304be08f: rollback sql stmt=DELETE FROM metadata WHERE id=72433
Aug  3 07:49:28 mailarchive pilerimport[53039]: ERROR: 400000005b63fb013a56130c0065304be08f: rollback sql stmt=DELETE FROM attachment WHERE piler_id='400000005b63fb013a56130c0065304be08f'
Aug  3 07:49:33 mailarchive pilerimport[53039]: ERROR: 400000005b63fb0730f25f64005cd58d53f9: mysql_stmt_execute() 'Data too long for column 'body' at row 1' (errno: 1406)
Aug  3 07:49:33 mailarchive pilerimport[53039]: ERROR: 400000005b63fb0730f25f64005cd58d53f9 failed to store index data for id=72434, sql_errno=1406
Aug  3 07:49:33 mailarchive pilerimport[53039]: ERROR: 400000005b63fb0730f25f64005cd58d53f9: rollback sql stmt=DELETE FROM sph_index WHERE id=72434
Aug  3 07:49:33 mailarchive pilerimport[53039]: ERROR: 400000005b63fb0730f25f64005cd58d53f9: rollback sql stmt=DELETE FROM rcpt WHERE id=72434
Aug  3 07:49:34 mailarchive pilerimport[53039]: ERROR: 400000005b63fb0730f25f64005cd58d53f9: rollback sql stmt=DELETE FROM metadata WHERE id=72434
Aug  3 07:49:34 mailarchive pilerimport[53039]: ERROR: 400000005b63fb0730f25f64005cd58d53f9: rollback sql stmt=DELETE FROM attachment WHERE piler_id='400000005b63fb0730f25f64005cd58d53f9'
Aug  3 07:49:39 mailarchive pilerimport[53039]: ERROR: 400000005b63fb0d05e9447c00eb63a641ec: mysql_stmt_execute() 'Data too long for column 'body' at row 1' (errno: 1406)
Aug  3 07:49:39 mailarchive pilerimport[53039]: ERROR: 400000005b63fb0d05e9447c00eb63a641ec failed to store index data for id=72435, sql_errno=1406
Aug  3 07:49:39 mailarchive pilerimport[53039]: ERROR: 400000005b63fb0d05e9447c00eb63a641ec: rollback sql stmt=DELETE FROM sph_index WHERE id=72435
Aug  3 07:49:39 mailarchive pilerimport[53039]: ERROR: 400000005b63fb0d05e9447c00eb63a641ec: rollback sql stmt=DELETE FROM rcpt WHERE id=72435
Aug  3 07:49:39 mailarchive pilerimport[53039]: ERROR: 400000005b63fb0d05e9447c00eb63a641ec: rollback sql stmt=DELETE FROM metadata WHERE id=72435
Aug  3 07:49:39 mailarchive pilerimport[53039]: ERROR: 400000005b63fb0d05e9447c00eb63a641ec: rollback sql stmt=DELETE FROM attachment WHERE piler_id='400000005b63fb0d05e9447c00eb63a641ec'
Aug  3 07:49:39 mailarchive pilerimport[53039]: ERROR: 400000005b63fb0d202491ac00445a289ae6: mysql_stmt_execute() 'Data too long for column 'body' at row 1' (errno: 1406)
Aug  3 07:49:39 mailarchive pilerimport[53039]: ERROR: 400000005b63fb0d202491ac00445a289ae6 failed to store index data for id=72436, sql_errno=1406
Aug  3 07:49:39 mailarchive pilerimport[53039]: ERROR: 400000005b63fb0d202491ac00445a289ae6: rollback sql stmt=DELETE FROM sph_index WHERE id=72436
Aug  3 07:49:39 mailarchive pilerimport[53039]: ERROR: 400000005b63fb0d202491ac00445a289ae6: rollback sql stmt=DELETE FROM rcpt WHERE id=72436
Aug  3 07:49:39 mailarchive pilerimport[53039]: ERROR: 400000005b63fb0d202491ac00445a289ae6: rollback sql stmt=DELETE FROM metadata WHERE id=72436

Regarding the import, how can I see any log? since maillog is not showing any.

[Janos SUTO]

Check the sphx_index table if its schema matches the db-mysql.sql file from 1.3.4. If not, then fix it.

[Jorge Gomes]

Thanks for the reply.

Can you kindly provide a simple how-to for that process? not sure how to do it myself

Regards JG

[Jorge Gomes]

Wel, google is your friend

So, I have imported the db-mysql.sql into a new DB and there is some differences ...

This is the NEW DB -- empty -- and imported from the db-mysql.sql file

MariaDB [pilerdb]> desc sph_index;

+------------------+------------------+------+-----+---------+-------+
| Field            | Type             | Null | Key | Default | Extra |
+------------------+------------------+------+-----+---------+-------+
| id               | bigint(20)       | NO   | PRI | NULL    |       |
| from             | tinyblob         | YES  |     | NULL    |       |
| to               | text             | YES  |     | NULL    |       |
| fromdomain       | char(255)        | YES  |     | NULL    |       |
| todomain         | text             | YES  |     | NULL    |       |
| subject          | blob             | YES  |     | NULL    |       |
| arrived          | int(10) unsigned | NO   |     | NULL    |       |
| sent             | int(10) unsigned | NO   |     | NULL    |       |
| body             | mediumblob       | YES  |     | NULL    |       |
| size             | int(11)          | YES  |     | 0       |       |
| direction        | int(11)          | YES  |     | 0       |       |
| folder           | int(11)          | YES  |     | 0       |       |
| attachments      | int(11)          | YES  |     | 0       |       |
| attachment_types | text             | YES  |     | NULL    |       |
+------------------+------------------+------+-----+---------+-------+"
14 rows in set (0.00 sec)

!! This is the PILER DB !!!

MariaDB [piler]> desc sph_index;
+------------------+------------------+------+-----+---------+-------+
| Field            | Type             | Null | Key | Default | Extra |
+------------------+------------------+------+-----+---------+-------+
| id               | bigint(20)       | NO   | PRI | NULL    |       |
| from             | tinyblob         | YES  |     | NULL    |       |
| to               | text             | YES  |     | NULL    |       |
| fromdomain       | char(255)        | YES  |     | NULL    |       |
| todomain         | text             | YES  |     | NULL    |       |
| subject          | text             | YES  |     | NULL    |       |
| arrived          | int(10) unsigned | NO   |     | NULL    |       |
| sent             | int(10) unsigned | NO   |     | NULL    |       |
| body             | text             | YES  |     | NULL    |       |
| size             | int(11)          | YES  |     | 0       |       |
| direction        | int(11)          | YES  |     | 0       |       |
| folder           | int(11)          | YES  |     | 0       |       |
| attachments      | int(11)          | YES  |     | 0       |       |
| attachment_types | text             | YES  |     | NULL    |       |
+------------------+------------------+------+-----+---------+-------+
14 rows in set (0.00 sec)

There are two rows different !!!

On pilerdb -imported

| subject          | blob             | YES  |     | NULL    |       |
| body             | mediumblob       | YES  |     | NULL    |       |

On PILER

| subject          | text             | YES  |     | NULL    |       |
| body             | text             | YES  |     | NULL    |       |

Is this what needs to me modified??

[Jorge Gomes]

So I did this :::

MariaDB [piler]> alter table sph_index change column `body` `body` mediumblob default null;
MariaDB [piler]> alter table sph_index change column `subject` `subject` blob default null;

as per your recommendation on https://bitbucket.org/jsuto/piler/issues/890/how-to-update

I had run this before::

alter table sph_index change column `from` `from` tinyblob default null;
alter table metadata change column `from` `from` varchar(255) not null;
alter table metadata change column `fromdomain` `fromdomain` varchar(255) not null;
alter table metadata change column `message_id` `message_id` varchar(255) not null;
alter table attachment change column `name` `name` tinyblob default null;

Correct action ??

[Jorge Gomes]

now getting only this errors ::

Aug  3 18:24:08 mailarchive piler-smtp[23203]: reloaded config: /usr/local/etc/piler/piler.conf
Aug  3 18:24:08 mailarchive piler-smtp[23203]: piler-smtp 1.3.4, build 994 starting
Aug  3 18:24:08 mailarchive piler[23205]: reloaded config: /usr/local/etc/piler/piler.conf
Aug  3 18:24:08 mailarchive piler[23205]: piler 1.3.4, build 994 starting
Aug  3 18:24:16 mailarchive pilerimport[23232]: error: helper: execl
Aug  3 18:24:17 mailarchive pilerimport[23235]: error: helper: execl
Aug  3 18:24:17 mailarchive pilerimport[23236]: error: helper: execl

Please advice.

Regards JG

[Janos SUTO]

At a quick glance, the sql statements look fine. The helper: execl errors may be normal. Can you continue the import or is it still stuck?

[Jorge Gomes]

Hi, apart from that message, the import went ok, or it seems. Now showing only duplicate: 23230-imap-26345.txt (duplicate id: 72364)

Need to do a quick check, but all seems ok ... You might want to include those MySQL lines on the wiki or so, since this was an upgrade from 1.3.1. to 1.3.4, and only found the others and not those two extra for sph_index table.

Again thanks for the support. Regards. JG

[Janos SUTO]

I've added these steps to the upgrade docs in the wiki.