A NOTE has been added to this issue.
======================================================================
http://www.dbmail.org/mantis/view.php?id=371
======================================================================
Reported By: blake
Assigned To: aaron
======================================================================
Project: DBMail
Issue ID: 371
Category: Database layer
Reproducibility: sometimes
Severity: major
Priority: normal
Status: resolved
target:
Resolution: fixed
Fixed in Version: SVN Trunk
======================================================================
Date Submitted: 22-Jun-06 22:43 CEST
Last Modified: 23-Jun-06 01:29 CEST
======================================================================
Summary: Some messages are truncated during insertion.
Description:
During insertion into the database, either via dbmail-smtp or IMAP append,
messages are being truncated at non ASCII characters. Looking at the
trace, the full message is in the insert query, but it doesn't all make it
into the databsae.
======================================================================
----------------------------------------------------------------------
blake - 22-Jun-06 22:44
----------------------------------------------------------------------
I am using MySQL on Gentoo, version 4.1.14-r1.
----------------------------------------------------------------------
blake - 22-Jun-06 23:04
----------------------------------------------------------------------
I've attached a level 5 log of both the message being inserted by
dbmail-smtp, and my MUA appending it to the "Sent" folder.
Here is the relavent section of the dbmail-smtp trace:
Jun 22 16:08:21 colo dbmail/smtp[28256]: Debug dbmysql.c,db_query: query
[INSERT INTO dbmail_physmessage (messagesize, internal_date) VALUES ('0',
CURRENT_TIMESTAMP)]
Jun 22 16:08:21 colo dbmail/smtp[28256]: Debug dbmysql.c,db_query: query
[INSERT INTO dbmail_messages(mailbox_idnr, physmessage_id,
unique_id,recent_flag, status) VALUES ('142', '817110',
'457daaa08c5f501c5dedd0faa2972375', '1', '5')]
Jun 22 16:08:21 colo dbmail/smtp[28256]: Debug dbmysql.c,db_query: query
[SELECT physmessage_id FROM dbmail_messages WHERE message_idnr =
'1346881']
Jun 22 16:08:21 colo dbmail/smtp[28256]: Debug dbmysql.c,db_query: query
[INSERT INTO dbmail_messageblks (is_header, messageblk,blocksize,
physmessage_id) VALUES ('1','Return-Path:
<[EMAIL PROTECTED]>\nDelivered-To:
[EMAIL PROTECTED]: from [192.168.1.89]
(ns2.marketmetrix.com [216.27.185.170])\n by travis.medleysoft.com
(Postfix) with ESMTP id 6573F1003A451\n for <[EMAIL PROTECTED]>; Thu,
22 Jun 2006 16:08:21 -0400 (EDT)\nMessage-ID:
<[EMAIL PROTECTED]>\nDate: Thu, 22 Jun 2006 13:08:17
-0700\nFrom: Blake Mitchell <[EMAIL PROTECTED]>\nUser-Agent:
Thunderbird 1.5.0.4 (Windows/20060516)\nTo: Blake Mitchell
<[EMAIL PROTECTED]>\nSubject: test message truncate
2\nX-DBMail-PhysMessage-ID: 817110\nMIME-Version: 1.0\nContent-Type:
text/plain; charset=ISO-8859-1; format=flowed\nContent-Transfer-Encoding:
8bit\n\n', '689', '817110')]
Jun 22 16:08:21 colo dbmail/smtp[28256]: Debug
dbmail-message.c,dbmail_message_store: allocating [524288] bytes of memory
for readblock
Jun 22 16:08:21 colo dbmail/smtp[28256]: Debug pipe.c,
store_message_in_blocks: inserting message: 1: Geçerli Degil 2: Geçerli
Degil 3: Geçerli Degil 4: Geçerli Degil 5: Geçerli Degil
Jun 22 16:08:21 colo dbmail/smtp[28256]: Debug dbmysql.c,db_query: query
[SELECT physmessage_id FROM dbmail_messages WHERE message_idnr =
'1346881']
Jun 22 16:08:21 colo dbmail/smtp[28256]: Debug dbmysql.c,db_query: query
[INSERT INTO dbmail_messageblks (is_header, messageblk,blocksize,
physmessage_id) VALUES ('0','1: Geçerli Degil\n2: Geçerli Degil\n3:
Geçerli Degil\n4: Geçerli Degil\n5: Geçerli Degil\n', '85', '817110')]
Jun 22 16:08:21 colo dbmail/smtp[28256]: Debug dbmysql.c,db_query: query
[UPDATE dbmail_messages SET unique_id =
'457daaa08c5f501c5dedd0faa2972375', status = '0' WHERE message_idnr =
'1346881']
Jun 22 16:08:21 colo dbmail/smtp[28256]: Debug dbmysql.c,db_query: query
[SELECT physmessage_id FROM dbmail_messages WHERE message_idnr =
'1346881']
Jun 22 16:08:21 colo dbmail/smtp[28256]: Debug dbmysql.c,db_query: query
[UPDATE dbmail_physmessage SET messagesize = '774', rfcsize = '795' WHERE
id = '817110']
----------------------------------------------------------------------
blake - 22-Jun-06 23:10
----------------------------------------------------------------------
if I try to look at the message from above in the databse, you can see that
the message is truncated at the first 'ç' character:
travis htdocs # mysql -u dbmail -p -e 'select * from dbmail_messageblks
where physmessage_id = 817110' dbmail
Enter password:
+-----------------+----------------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+-----------+-----------+
| messageblk_idnr | physmessage_id | messageblk
| blocksize | is_header |
+-----------------+----------------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+-----------+-----------+
| 1637260 | 817110 | Return-Path:
<[EMAIL PROTECTED]>
Delivered-To: [EMAIL PROTECTED]
Received: from [192.168.1.89] (ns2.marketmetrix.com [216.27.185.170])
by travis.medleysoft.com (Postfix) with ESMTP id 6573F1003A451
for <[EMAIL PROTECTED]>; Thu, 22 Jun 2006 16:08:21 -0400
(EDT)
Message-ID: <[EMAIL PROTECTED]>
Date: Thu, 22 Jun 2006 13:08:17 -0700
From: Blake Mitchell <[EMAIL PROTECTED]>
User-Agent: Thunderbird 1.5.0.4 (Windows/20060516)
To: Blake Mitchell <[EMAIL PROTECTED]>
Subject: test message truncate 2
X-DBMail-PhysMessage-ID: 817110
MIME-Version: 1.0
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 8bit
| 689 | 1 |
| 1637261 | 817110 | 1: Ge
| 85 | 0 |
+-----------------+----------------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+-----------+-----------+
----------------------------------------------------------------------
blake - 22-Jun-06 23:13
----------------------------------------------------------------------
I have had messages go through correctly that contain the 'ç' character,
and others not work that didn't containt that character. So it doesn't
seem specific to just that character.
----------------------------------------------------------------------
blake - 23-Jun-06 00:29
----------------------------------------------------------------------
I upgraded to MySQL version 4.1.20, no change in behavior.
----------------------------------------------------------------------
aaron - 23-Jun-06 00:51
----------------------------------------------------------------------
I've seen this personally, but have not pinpointed the problem with a
testcase :-\
----------------------------------------------------------------------
blake - 23-Jun-06 01:22
----------------------------------------------------------------------
I've found the problem, my database has messagblk as a longtext, but it
should be a longblob. Here's a simple test case:
[EMAIL PROTECTED] ~ $ cat dbmail_test_insert.sql
create table test_insert ( test_text longtext );
insert into test_insert (test_text) values ('1: Geçerli Degil\n2: Geçerli
Degil\n3: Geçerli Degil\n4: Geçerli Degil\n5: Geçerli Degil\n');
select * from test_insert;
drop table test_insert;
create table test_insert ( test_text longblob );
insert into test_insert (test_text) values ('1: Geçerli Degil\n2: Geçerli
Degil\n3: Geçerli Degil\n4: Geçerli Degil\n5: Geçerli Degil\n');
select * from test_insert;
drop table test_insert;
messageblk is a longblob in the current create_tables.mysql, but my
database has been upgraded from the pre 1.0 days, so I'm sure it's an
artifact. Once I find the correct alter syntax I'll post a patch for the
upgrade schema script.
----------------------------------------------------------------------
aaron - 23-Jun-06 01:27
----------------------------------------------------------------------
Added to sql/mysql/migrate_from_2.0_to_2.1.mysql:
alter table dbmail_messageblks change messageblk longblob;
Now in SVN.
----------------------------------------------------------------------
aaron - 23-Jun-06 01:29
----------------------------------------------------------------------
Sorry, it's MODIFY, not CHANGE, and confirmed that it fixed the problem.
Issue History
Date Modified Username Field Change
======================================================================
22-Jun-06 22:43 blake New Issue
22-Jun-06 22:44 blake Note Added: 0001270
22-Jun-06 22:53 blake File Added: maillog.txt.gz
22-Jun-06 23:04 blake Note Added: 0001271
22-Jun-06 23:10 blake Note Added: 0001272
22-Jun-06 23:13 blake Note Added: 0001273
23-Jun-06 00:29 blake Note Added: 0001274
23-Jun-06 00:51 aaron Note Added: 0001275
23-Jun-06 01:22 blake Note Added: 0001276
23-Jun-06 01:27 aaron Status new => resolved
23-Jun-06 01:27 aaron Fixed in Version => SVN Trunk
23-Jun-06 01:27 aaron Resolution open => fixed
23-Jun-06 01:27 aaron Assigned To => aaron
23-Jun-06 01:27 aaron Note Added: 0001277
23-Jun-06 01:29 aaron Note Added: 0001278
======================================================================
