View Issue Details

IDProjectCategoryView StatusLast Update
0035247mantisbtemailpublic2025-02-01 10:19
Reporterdregad Assigned To 
PrioritynormalSeverityminorReproducibilityalways
Status confirmedResolutionopen 
Summary0035247: Data too long for column 'body' when adding a note to an Issue
Description

When adding a bugnote to issue 0012830, MantisBT throws

APPLICATION ERROR 401
Database query failed. Error received from database was #1406: Data too long for column 'body' at row 1 for the query: INSERT INTO mantis_email_table
( email, subject, body, submitted, metadata)
VALUES
(?,?,?,?,?).
Please use the "Back" button in your web browser to return to the previous page. There you can correct whatever problems were identified in this error or select another action. You can also click an option from the menu bar to go directly to a new section.

TagsNo tags attached.

Relationships

related to 0012830 new new HTMLMail plugin v0.1 

Activities

dregad

dregad

2025-01-18 16:56

developer   ~0069718

Last edited: 2025-01-18 16:56

That error is strange, because the body column is defined as ADOdb XL metatype in the database schema; for MySQL, this gets converted to LONGTEXT, which can store 4'294'967'296 bytes [1].

dregad

dregad

2025-01-18 17:01

developer   ~0069719

Last edited: 2025-01-18 17:02

Looks like the DB schema on this tracker's is not in sync with what the installer generates:

mantisbt.org

mysql> desc mantis_email_table;
+-----------+--------------+------+-----+---------+----------------+
| Field     | Type         | Null | Key | Default | Extra          |
+-----------+--------------+------+-----+---------+----------------+
| email_id  | int unsigned | NO   | PRI | NULL    | auto_increment |
| email     | varchar(191) | NO   |     |         |                |
| subject   | varchar(250) | NO   |     | NULL    |                |
| metadata  | text         | NO   |     | NULL    |                |
| body      | text         | NO   |     | NULL    |                |
| submitted | int unsigned | NO   |     | 1       |                |
+-----------+--------------+------+-----+---------+----------------+

fresh install 2.27.1

mysql> desc mantis_email_table;
+-----------+--------------+------+-----+---------+----------------+
| Field     | Type         | Null | Key | Default | Extra          |
+-----------+--------------+------+-----+---------+----------------+
| email_id  | int unsigned | NO   | PRI | NULL    | auto_increment |
| email     | varchar(191) | NO   |     |         |                |
| subject   | varchar(250) | NO   |     |         |                |
| metadata  | longtext     | NO   |     | NULL    |                |
| body      | longtext     | NO   |     | NULL    |                |
| submitted | int unsigned | NO   |     | 1       |                |
+-----------+--------------+------+-----+---------+----------------+

body and metadata columns are defined as TEXT (65'536 bytes) instead of LONGTEXT.

dregad

dregad

2025-01-18 17:38

developer   ~0069720

The discrepancy comes from an old commit from 2007 MantisBT master 55384a2f that changed the column type from XS to XL.

As far as I know, XS is not a standard ADOdb metatype (but maybe it was back in 2007 ?)

I guess that change was never applied to the mantisbt.org bugtracker's schema... We should probably fix that.

That being said, in my opinion it makes absolutely no sense to include the whole list of bugnotes in a notification email as nobody would read the whole lot (in this specific case, that's 78 bugnotes as of this writing, and each notification mail if it got sent, would be over 70 KB). We should limit the number of bugnotes to a reasonable number (e.g. the most recent 10).

dregad

dregad

2025-01-20 12:12

developer   ~0069730

@atrol following up on your note 0012830:0069724

There is already $g_default_email_bugnote_limit which is set t 0 = unlimited.
Users can override it in their account settings.

The problem is that the default value will be stored in user preferences, so changing the value in config_inc.php is not sufficient to prevent the error from occurring when adding a note to 0012830.

mysql> select email_bugnote_limit, count(*) from mantis_user_pref_table group by 1 order by 1;
+---------------------+----------+
| email_bugnote_limit | count(*) |
+---------------------+----------+
|                  -9 |        1 |
|                   0 |    17323 |
|                   1 |       15 |
|                   2 |        6 |
|                   3 |        5 |
|                   4 |        4 |
|                   5 |       11 |
|                  10 |       12 |
|                  20 |        2 |
|                  90 |        2 |
|                  99 |        5 |
+---------------------+----------+

If we don't alter the DB schema to align it with how it's currently generated by the installer, we would need to update all the 17323 zeros, plus the 90 and 99 (in reality, anyone having more than 0000040:0000070).

As mentioned in my earlier post, I think we should enforce a maximum value for this setting, i.e. prevent users from setting a value too high. This means either an arbitrary, hardcoded number (10 ? 20 ?) or introduce a new config. The latter would be better IMO.

atrol

atrol

2025-01-20 13:02

developer   ~0069731

Last edited: 2025-01-20 13:10

@dregad my intention when adding this note was just to prevent that Cas introduces a redundant option.

The approach to limit by numbers of notes is not resolving the database storage issue in a clean way.
E.g. 2 very big notes can be bigger than 200 very small notes.
The right solution would be to truncate the content if it cannot be stored due to size in bytes.
Of course, hardly a real limit when using MySQL and longtext data type (didn't check what this means for other databases).

i.e. prevent users from setting a value too high.

We do (limit to two digits), but there is setting 0 (unlimited) that must just be checked if user has enabled "E-mail Full Issue Details" (somehow redundant, it might have been better if we had used setting 0 (unlimited) for that

dregad

dregad

2025-01-21 08:54

developer   ~0069732

The approach to limit by numbers of notes is not resolving the database storage issue in a clean way.

Correct, it's not a 100% safe approach but with a reasonably small(er) maximum number of notes, the risk of encountering the error is greatly reduced.

The right solution would be to truncate the content if it cannot be stored due to size in bytes.

True, but considering the way the mails are generated, it's not so straightforward to do in a clean way.

Of course, hardly a real limit when using MySQL and longtext data type (didn't check what this means for other databases).

  • MySQL LONGTEXT gives us 4GB
  • PostgreSQL TEXT is unlimited in theory but according to FAQ in reality 1GB
  • SQL Server TEXT is limited to 2GB
  • Oracle CLOB is maximum (4 GB - 1) * DB_BLOCK_SIZE (8 TB to 128 TB)

Bottomline is, our DB is wrong and we need to fix it. Probably not worth the effort to make code adaptations to fit email body to 65KB.

atrol

atrol

2025-01-21 12:34

developer   ~0069735

Last edited: 2025-01-21 13:11

@dregad fixing our DB should be enough, no code changes needed.

True, but considering the way the mails are generated, it's not so straightforward to do in a clean way.

Couldn't we do it in a similar way like we do it for history values?

            $c_old_value = string_truncate( $p_old_value, DB_FIELD_SIZE_HISTORY_VALUE, '…' );

[EDIT] certainly not that good in terms of performance and memory usage if this would be done at one single place before inserting to the table

dregad

dregad

2025-02-01 10:19

developer   ~0069809

Removing target version, as this is not caused by MantisBT code, but due to specifics of the mantisbt.org database.