[imp] Problems with attachments in current imp

[Martin Tessun]

Quoting Michael M Slusarz <slusarz at horde.org>:

> Quoting Martin Tessun <martin.tessun at die-tessuns.de>:
>
>> Hi all,
>>
>> as I believe it just works usually and this is only my problem, I  
>> just wanted to ask if someone has an idea, what can cause the  
>> following:
>>
>> 1. Create a new mail, add attachment (e.g. PDF file) send this mail  
>> ==> attachment is too small and corrupted
>> 2. Forward a mail with an attachment (e.g. PDF file) ==> attachment  
>> gets smaller than from the original mail and is corrupted.
>
> My guess is that you are using SQL VFS backend and haven't increased  
> the maximum size of BLOB data.  There is a bunch of posts on this in  
> the mailing list archive.

Hm, the blob size is 50 MB:

MariaDB [(none)]> show global variables like 'max_allowed_packet';
+--------------------+----------+
| Variable_name      | Value    |
+--------------------+----------+
| max_allowed_packet | 52428800 |
+--------------------+----------+
1 row in set (0.00 sec)

Nevertheless every mail is at a maximum 66 kB.
So I checked  horde_vfs:

MariaDB [horde]> show table status like 'horde_vfs';
+-----------+--------+---------+------------+------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+-------------+------------+-----------------+----------+----------------+---------+
| Name      | Engine | Version | Row_format | Rows | Avg_row_length |  
Data_length | Max_data_length | Index_length | Data_free |  
Auto_increment | Create_time         | Update_time | Check_time |  
Collation       | Checksum | Create_options | Comment |
+-----------+--------+---------+------------+------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+-------------+------------+-----------------+----------+----------------+---------+
| horde_vfs | InnoDB |      10 | Compact    |    6 |          16384 |   
      98304 |               0 |        32768 | 612368384 |              
15 | 2013-06-02 00:02:15 | NULL        | NULL       | utf8_general_ci  
|     NULL |                |         |
+-----------+--------+---------+------------+------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+-------------+------------+-----------------+----------+----------------+---------+
1 row in set (0.06 sec)


So this is InnoDB.

Checking the size of the blobs:

MariaDB [horde]> SELECT OCTET_LENGTH(vfs_data) from horde_vfs;
+------------------------+
| OCTET_LENGTH(vfs_data) |
+------------------------+
|                   NULL |
|                   NULL |
|                   NULL |
|                  65535 |
|                   NULL |
|                   NULL |
|                  10130 |
+------------------------+
7 rows in set (0.09 sec)

Shows the 65535 as a maximum. Now I tried to insert some date to it  
and it showed again only 65535 bytes for the BLOB.
MariaDB [horde]> SELECT OCTET_LENGTH(vfs_data) from horde_vfs;
+------------------------+
| OCTET_LENGTH(vfs_data) |
+------------------------+
|                   NULL |
|                   NULL |
|                   NULL |
|                  65535 |
|                   NULL |
|                   NULL |
|                  10130 |
|                  65535 |
+------------------------+
8 rows in set (0.00 sec)

MariaDB [horde]> delete from horde_vfs where vfs_owner='tester';
Query OK, 1 row affected (0.05 sec)

Here my script (The file is about 200 kB)
<?php
$link = mysql_connect('localhost', 'user', 'password');
if (!$link) {
     die('Verbindung schlug fehl: ' . mysql_error());
}
echo "Erfolgreich verbunden\n";
$sql = "USE horde";
$res = mysql_query($sql, $link);
if (!$res) {
     die($sql . ' schlug fehl: ' . mysql_error() . "\n");
}

$filename = "test.pdf";
$filecontent = file_get_contents($filename);
$filecontent_escaped = mysql_real_escape_string($filecontent);

$sql = "INSERT INTO horde_vfs (vfs_type, vfs_path, vfs_name,  
vfs_modified, vfs_owner, vfs_data) " .
        "VALUES(1, '.', '$filename', 20130610, 'tester',  
'$filecontent_escaped')";
$res = mysql_query($sql, $link);
if (!$res) {
     die($sql . ' schlug fehl: ' . mysql_error() . "\n");
}
mysql_close($link);
?>

Then I found this one:

||| The default field type in mysql is 'BLOB'. And this fieldtype is  
restricted to a maximum size of 64KB.

Some more information:
MySQL defines 4 types of BLOB.

type             max size
------------  -------------
TINYBLOB                255
BLOB                 65_535
MEDIUMBLOB       16_777_215
LONGBLOB      4_294_967_295

So I changed horde_vfs to largeblob (storing up to 4GB)
MariaDB [horde]> alter table horde_vfs change vfs_data vfs_data LONGBLOB;

Did run the test again:
MariaDB [horde]> SELECT OCTET_LENGTH(vfs_data) from horde_vfs;
+------------------------+
| OCTET_LENGTH(vfs_data) |
+------------------------+
|                   NULL |
|                   NULL |
|                   NULL |
|                  65535 |
|                   NULL |
|                   NULL |
|                  10130 |
|                 260627 |
+------------------------+
8 rows in set (0.00 sec)

And it worked. So why not just change the vfs_data to longblob by default?

Thanks Michael for stubbing my nose to the right place.

Cheers,
Martin