Fetch problem or installation problem

[Alejandro Díaz]

I installed Posterity from svn repository in a clean Ubuntu 7.10
installation.
I tried to fetch emails from a gmail account, but I receive this
message:

/usr/lib/python2.5/site-packages/SQLAlchemy-0.3.11-py2.5.egg/
sqlalchemy/databases/mysql.py:1042: Warning: Data truncated for column
'payload' at row 1
cursor.execute(statement, parameters)

I don't know if it is a bug or a installation problem.

Any help?

Best regards
Alejandro

[Jonas Borgström]

Hi Alejandro,

You've found a bug/limitation when using Posterity together with MySQL.
It appears that the standard MySQL BLOB data type only supports 2^16
bytes and the MySQL specific data type LONGBLOB has to be used to store
more data. The following SQL statement should make your database use the
LONGBLOB data type:

ALTER TABLE msg MODIFY COLUMN payload LONGBLOB;

I've created a new ticket to track this bug:

http://posterity.edgewall.org/ticket/36

Cheers,
Jonas

[Alejandro Díaz]

Thanks Jonas,

I tried your tip, and all work fine, I'm running now a Posterity
instance.

Alejandro Díaz

[henning....@gmail.com]

Hi!

On Nov 13, 10:14 pm, Jonas Borgström <jo...@edgewall.com> wrote:
> You've found a bug/limitation when using Posterity together with MySQL.
> It appears that the standard MySQL BLOB data type only supports 2^16
> bytes and the MySQL specific data type LONGBLOB has to be used to store
> more data. The following SQL statement should make your database use the
> LONGBLOB data type:
>
> ALTER TABLE msg MODIFY COLUMN payload LONGBLOB;

After reading this i looked at the database schema and wondered if it
is a good
idea to store the whole message in a blob?

Wouldn't it make sense to store each message part separately?
If I have a large mail with many pictures attached and I only want to
download one picture I would have to load the
complete message into memory and to decode it which then is also held
in memory.
This could cause problems if I want to provide Posterity to many users
on my server.

I searched for some alternative database schemas and found the
following links:
http://www.archiveopteryx.org/sql-schema.html#messages
http://svn.ic-s.nl/websvn/filedetails.php?repname=DBMail&path=%2Ftrunk%2Fdbmail%2Fsql%2Fmysql%2Fcreate_tables.mysql&rev=0&sc=0

Perhaps storing messages differently would it make easier to show a
attachment icon in the message list page.

Regards
Henning

[Jonas Borgström]

henning....@gmail.com wrote:
>
> Hi!
>
> On Nov 13, 10:14 pm, Jonas Borgström <jo...@edgewall.com> wrote:
>> You've found a bug/limitation when using Posterity together with MySQL.
>> It appears that the standard MySQL BLOB data type only supports 2^16
>> bytes and the MySQL specific data type LONGBLOB has to be used to store
>> more data. The following SQL statement should make your database use the
>> LONGBLOB data type:
>>
>> ALTER TABLE msg MODIFY COLUMN payload LONGBLOB;
> After reading this i looked at the database schema and wondered if it
> is a good
> idea to store the whole message in a blob?
>
> Wouldn't it make sense to store each message part separately?
> If I have a large mail with many pictures attached and I only want to
> download one picture I would have to load the
> complete message into memory and to decode it which then is also held
> in memory.
> This could cause problems if I want to provide Posterity to many users
> on my server.

The main reason the whole message is stored in the database unparsed is
as an extra guarantee against data loss. This way a bug in the message
parser or anywhere else can never cause any data loss since we can
always re-parse the message. Another reason is to reduse code
complexity, storing mime structures efficiently in a relational database
is a complex task.

Of course this approach has a couple of drawbacks as well as you
mentioned, mainly performance. (Re)parsing messages every time they are
viewed adds some overhead, but so far this hasn't really been an issue.
Especially since the current database schema already stores a parsed
copy of relevant messages headers so things like the message list can be
rendered without needing to parse any messages.

But anyway, I don't have any problem with switching to a more normalized
database schema if the benefits are strong enough.

> Perhaps storing messages differently would it make easier to show a
> attachment icon in the message list page.

I was actually planning to add a simple "has_attachments" boolean
attribute to the message table. This would be enough to add an icon
indicating that a message has attachments. Or do you want to be able to
see the actual filenames as well?

Cheers,
Jonas