View Issue Details

IDProjectCategoryView StatusLast Update
0020547mantisbtattachmentspublic2016-11-09 10:44
ReporterDLeonAssigned Todregad 
PrioritynormalSeveritymajorReproducibilityalways
Status closedResolutionfixed 
Product Version1.3.0-beta.1 
Target Version1.3.0-rc.2Fixed in Version1.3.0-rc.2 
Summary0020547: Attachments can't be uploaded after upgrade from 1.2 with MySQL in STRICT_ALL_TABLES sql_mode
Description

We updated our Mantis from Version 1.2.15 to 1.3.0-rc.1 because of the solution for bug 0012544.

After the update we have problems with the upload of attachments. We get the error message "Database query failed. Error received from database was #1364: Field 'content' doesn't have a default value for the query: INSERT INTO mantis_bug_file_table ( bug_id, title, description, diskfile, filename, folder,
filesize, file_type, date_added, user_id )
VALUES
( ?, ?, ?, ?, ?, ?, ?, ?, ?, ? )."

The update process worked fine withour any error messages.

Steps To Reproduce

System:
Window 2008 R2 SP1
Webserver IIS 7.5
DB MYSQL 5.1
PHP 5.6.0

Settings in confic_inc.php:

--- Attachments / File Uploads ---

$g_allow_file_upload = ON;
$g_file_upload_method = DATABASE;
$g_absolute_path_default_upload_folder = '';
$g_max_file_size = 5000000;
$g_preview_attachments_inline_max_size = 256 * 1024;
$g_allowed_files = '';
$g_disallowed_files = '';

Additional Information

Table field "content" is of type longblob and for longblob it is not possible to set a default value.

Tagsmssql, MySQL

Relationships

related to 0021890 new oracle, schema steps 203 204 fails 

Activities

dregad

dregad

2016-01-28 07:25

developer   ~0052417

I was not able to reproduce your problem with a fresh install of 1.3.0-rc.1, attachments can be added without errors. I tested on Linux, PHP 5.5 and MySQL 5.5, but I don't think that should make any difference.

I suspect you're not running standard MantisBT code, because the SQL statement you reported does not match what is executed by the file API [1] (your query does not include the 'content' column).

[1] https://github.com/mantisbt/mantisbt/blob/release-1.3.0-rc.1/core/file_api.php#L1054

DLeon

DLeon

2016-01-28 07:41

reporter   ~0052419

I'm using standard MantisBT code without any changes. Did yo test it with an existing database from an older version?

atrol

atrol

2016-01-28 08:40

developer   ~0052420

but I don't think that should make any difference.
Me too, but

PHP 5.6.0
This is certainly not a good one, the latest one at the moment for 5.6 is 5.6.17 [1]
I remember users having problems using early 5.6 versions.

@dregad, 1.3.0-rc.2 will come with an updated adodb version.
Maybe there are changes in it that affect this issue.

[1] http://php.net/ChangeLog-5.php#5.6.17

dregad

dregad

2016-01-28 09:57

developer   ~0052421

@dregad, 1.3.0-rc.2 will come with an updated adodb version.
Maybe there are changes in it that affect this issue.

No. I tested both with 1.3.0-rc.1 and master, and was not able to reproduce on either versions. Including with a DB installed under 1.2.19 and upgraded afterwards.

@DLeon, sorry but I maintain that the query

INSERT INTO mantis_bug_file_table ( bug_id, title, description, diskfile, filename, folder,
filesize, file_type, date_added, user_id )
VALUES
( ?, ?, ?, ?, ?, ?, ?, ?, ?, ? )

does not exist anywhere in 1.3.x code. The code reference I provided in 0020547:0052417 is the only occurence of an insert statement on mantis_bug_file_table.

Maybe you can provide the output of the error page, with $g_detailed_errors = ON; this may help understanding what the problem is.

DLeon

DLeon

2016-01-28 12:56

reporter   ~0052424

Thank you all for your answers. I know that the query in the error message is different from the Insert query in file_api.php. It seems that Content may be NULL. I switched g_detailed_errors to ON and got the following error message:

INSERT INTO mantis_bug_file_table ( bug_id, title, description, diskfile, filename, folder,
filesize, file_type, date_added, user_id )
VALUES
( 1270, '', '', 'ad56347a65d9b42f9270a141942a05ed', 'pic.jpg', '', 748577, 'image/jpeg', 1454003023, 2 ).

My logfile Shows the same query:
28-01-2016 18:43 CET DB array (
0 => 'INSERT INTO mantis_bug_file_table ( bug_id, title, description, diskfile, filename, folder,
filesize, file_type, date_added, user_id )
VALUES
( 1270, \'\', \'\', \'ad56347a65d9b42f9270a141942a05ed\', \'pic.jpg\', \'\', 748577, \'image/jpeg\', 1454003023, 2 )',
1 => '0.0000',
)

And here the query of my file_api.php
$t_query = 'INSERT INTO {bug_file} (
bug_id, title, description, diskfile, filename, folder,
filesize, file_type, date_added, user_id, content
)
VALUES ( '
. db_param() . ', ' . db_param() . ', ' . db_param() . ', '
. db_param() . ', ' . db_param() . ', ' . db_param() . ', '
. db_param() . ', ' . db_param() . ', ' . db_param() . ', '
. db_param() . ', ' . db_param() .
')';
db_query( $t_query, array(
$p_dest_bug_id, $t_bug_file['title'], $t_bug_file['description'],
$t_new_diskfile_name, $t_new_file_name, $t_file_path,
$t_bug_file['filesize'], $t_bug_file['file_type'], $t_bug_file['date_added'],
$t_bug_file['user_id'], $t_bug_file['content']
) );

Really strange! Content is missing.

cproensa

cproensa

2016-01-28 13:50

developer   ~0052425

The query is here:
https://github.com/mantisbt/mantisbt/blob/release-1.3.0-rc.1/core/file_api.php#L725

@DLeon
your mysql version seems a bit old (5.1)
This can be related: http://bugs.mysql.com/bug.php?id=25520

Don't know if its a inherent problem on mysql core, or its solved by newer versions

DLeon

DLeon

2016-01-29 03:59

reporter   ~0052429

Again thanks for your support.

@cproensa
You're right, the query is in line 725 not in 1054.

And this query changed from version 1.2.19. So maybe other people will have the same problem.

@cproensa
The link is helpful. Text and blob are not allowed to have set default values. This ist not a bug but a rule also for new versions of the mysql server. With sql mode 'STRICT_TRANS_TABLES' is becomes an error, without this mode it is only a warning and uploading files is possible.

So the solution for me is now to globally disable the STRICT_TRANS_TABLES mode.

cproensa

cproensa

2016-01-29 04:05

developer   ~0052430

@DLeon:
I tried with 1.3.master branch, setting manually STRICT_TRANS_TABLES on and off, and always could report a bug with attachment
My system is linux, so i cannot test on windows based mysql.

DLeon

DLeon

2016-01-29 04:11

reporter   ~0052431

@cproensa
Did you change the setting globally or per session?

DLeon

DLeon

2016-01-29 04:17

reporter   ~0052432

@cproensa

I used the following way:

set GLOBAL sql_mode='';
SELECT @@GLOBAL.sql_mode;

cproensa

cproensa

2016-01-29 04:20

developer   ~0052433

good pint, i probably changed only for current session

i will try again

dregad

dregad

2016-01-29 06:49

developer   ~0052434

/me needs glasses. And maybe a 'good pint' too ;-)

Apologies for my earlier comment, I failed to see that query. Thanks cproensa for pointing it out.

So, the issue has most likely been introduced by MantisBT master 12a7f834 - instead of doing a single insert, the code performs the update in 2 steps, insert basic data then update the blob.

I still find it strange to have this error since

  1. we do NOT specify a default value on this column
  2. MySQL automatically sets the default to NULL when none is specified

Anyway, I will now try to replicate the problem using SET sql_mode = 'STRICT_ALL_TABLES'.

cproensa

cproensa

2016-01-29 07:07

developer   ~0052436

@dregad
in my db that field was defined as NOT NULL. Does it sound suspicious?

dregad

dregad

2016-01-29 12:24

developer   ~0052438

Still not able to reproduce - inserting attachment is successful even when sql_mode is STRICT_ALL_TABLES (the 1st 2 log entries in the extract below are the result of debugging statements I added).

2016-01-29 17:10 UTC DB file_api.php:726 file_add() array (
  0 => 'SELECT @@sql_mode',
  1 => '0.0003',
)
2016-01-29 17:10 UTC DB file_api.php:727 array (
  '@@sql_mode' => 'STRICT_ALL_TABLES',
)
2016-01-29 17:10 UTC DB file_api.php:734 file_add() array (
  0 => 'INSERT INTO mantis_bug_file_table ( bug_id, title, description, diskfile, filename, folder,
                filesize, file_type, date_added, user_id )
        VALUES
                ( 2, \'\', \'\', \'95de937370a6d220630483d48f4c95c3\', \'1.patch\', \'\', 1117, \'application/octet-stream\', 1454087408, 1 )',
  1 => '0.0006',
)

Can you share the structure of your table's content column (i.e. desc mantis_bug_file_table content) ? For the record, mine's like this, both in a fresh 1.3.0-rc.1 install, and from the one I upgraded from 1.2.19.

mysql> desc mantis_bug_file_table content;
+---------+----------+------+-----+---------+-------+
| Field   | Type     | Null | Key | Default | Extra |
+---------+----------+------+-----+---------+-------+
| content | longblob | YES  |     | NULL    |       |
+---------+----------+------+-----+---------+-------+
1 row in set (0.00 sec)
cproensa

cproensa

2016-01-29 13:03

developer   ~0052439

Last edited: 2016-01-29 18:38

View 2 revisions

I think this is ADOdb related

My DB for 1.3-master was migrated from 1.2.3, and i have:

mysql> desc mantis_bug_file_table content;
+---------+----------+------+-----+---------+-------+
| Field   | Type     | Null | Key | Default | Extra |
+---------+----------+------+-----+---------+-------+
| content | longblob | NO   |     | NULL    |       |
+---------+----------+------+-----+---------+-------+

Other DBs, created by 1.3-beta3 and 1.2.19, both show:

+---------+----------+------+-----+---------+-------+
| Field   | Type     | Null | Key | Default | Extra |
+---------+----------+------+-----+---------+-------+
| content | longblob | YES  |     | NULL    |       |
+---------+----------+------+-----+---------+-------+

So i tried, with 1.3 master:

$schema[] = array( 'CreateTableSQL', array( 'blob_test2', "
content B NOTNULL ",
$t_table_options
) );

mysql> desc blob_test2 content;
+---------+----------+------+-----+---------+-------+
| Field   | Type     | Null | Key | Default | Extra |
+---------+----------+------+-----+---------+-------+
| content | longblob | YES  |     | NULL    |       |
+---------+----------+------+-----+---------+-------+

So i am saying NOT NULL, but the field is created as NULL. Maybe its a conversion made by ADOdb by itself.

Once reproduced in my affected DB,
I got to fix the problem adding an explicit "content" field in the insert statement, and using 'empty_blob()' as value (string).
ATM I don't know if it may cause any regression, but if this works, may be less hassle that modify schema

@DLeon:
Did you migrate you current DB from previos versions <1.2.19?
If so, this may be the explanation.

dregad

dregad

2016-01-30 05:40

developer   ~0052446

Last edited: 2016-01-30 05:42

View 5 revisions

I think this is ADOdb related

You're right, but not upstream adodb - rather a consequence of our specific patches in 1.2.

Using the following test code

<?php
include('library/adodb/adodb.inc.php');
$db = ADONewConnection('mysqli');
$db->connect('localhost',$u,$p,'test');
$dd = NewDataDictionary($db);
$dd->CreateTableSQL('a', 'b B notnull');
print($dd->CreateTableSQL('a', 'b B notnull')[0]);

The following SQL is generated (extra whitespace removed):

1.2: CREATE TABLE a ( b LONGBLOB NOT NULL )
1.3: CREATE TABLE a ( b LONGBLOB )

I also checked with upstream ADOdb 5.10 (which is the version we use in 1.2), and the datadict generates the same SQL as 1.3. Ergo, this does comes from a MantisBT-specific change to the library, traced to MantisBT master 19dbfb0e.

dregad

dregad

2016-01-30 05:45

developer   ~0052447

I think the right approach to address this issue is to fix the insert statement to populate the content column.

dregad

dregad

2016-01-30 19:05

developer   ~0052450

To reproduce the problem, execute the following SQL commands:

set global sql_mode = 'STRICT_ALL_TABLES';
alter table mantis_bug_file_table modify content longblob not null;

Adding an attachment in Mantis then triggers application error 401 (file_api.php, line 731)

Database query failed. Error received from database was #1364: Field 'content' doesn't have a default value for the query: INSERT INTO mantis_bug_file_table ( bug_id, title, description, diskfile, filename, folder,
filesize, file_type, date_added, user_id )
VALUES
( ?, ?, ?, ?, ?, ?, ?, ?, ?, ? ).

Please see proposed fix in PR https://github.com/mantisbt/mantisbt/pull/715

atrol

atrol

2016-02-03 17:21

developer   ~0052466

Seems not to be just a MySQL issue with sql_mode = 'STRICT_ALL_TABLES' but also a SQL-Server issue:
https://www.mantisbt.org/forums/viewtopic.php?f=2&t=23590

Related Changesets

MantisBT: master f1d5a8b3

2016-01-30 18:04:18

dregad

Details Diff
Insert file attachments records in a single step

Commit 12a7f8342b9df21b694a7c0bf23f251230add2ad split the attachment of
a BLOB in the bug_file and project_file tables in 2 steps, first
inserting the record, then updating it to add the BLOB's content.

This introduced a regression with MySQL when SQL_MODE is set to
STRICT_ALL_TABLES and the database was created pre 1.3.x and
subsequently upgraded. In 1.2, BLOB columns are created with a NOT NULL
attribute, due to custom ADOdb code (see 19dbfb0e290a30fcfe1ec29566e611d36c1c7aa9)

This reverts the behavior to what it was before 12a7f834, i.e. execute a
single INSERT statement that also populates the BLOB (except with Oracle
which requires this to occur as a separate operation).

Fixes 0020547
mod - api/soap/mc_file_api.php Diff File
mod - core/file_api.php Diff File

MantisBT: master a3e52aab

2016-01-30 18:30:35

dregad

Details Diff
Ensure consistent definition blob columns

In 1.2.x, custom code in adodb-datadict.inc.php disabled silent dropping
of NOT NULL attribute (see 19dbfb0e290a30fcfe1ec29566e611d36c1c7aa9).
Consequently, BLOBs columns created in that version do not allow NULL
values:

mysql> desc mantis_bug_file_table content;
+---------+----------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+---------+----------+------+-----+---------+-------+
| content | longblob | NO | | NULL | |
+---------+----------+------+-----+---------+-------+

In 1.3, we reverted to standard ADOdb behavior, so the NOT NULL
attribute is ignored by the data dictionary and BLOB columns are
allowed to be NULL:

mysql> desc mantis_bug_file_table content;
+---------+----------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+---------+----------+------+-----+---------+-------+
| content | longblob | YES | | NULL | |
+---------+----------+------+-----+---------+-------+

This results in an inconsistent schema definition between instances
upgraded from 1.2.x to 1.3.x, vs created straight in 1.3.x.

To avoid any issues due to this situation, we add 2 schema steps to
update the BLOB columns so they allow NULL values.

Issue 0020547
mod - admin/schema.php Diff File

MantisBT: master c7a47d31

2016-01-31 04:49:14

dregad

Details Diff
Fix file attachment for PostgreSQL

Use of an associative array causes issues with pgsql as it expects a
0-based numeric array for the query parameters.

Issue 0020547
mod - api/soap/mc_file_api.php Diff File
mod - core/file_api.php Diff File

MantisBT: master 53245f5c

2016-02-05 16:59:51

dregad

Details Diff
Fix 0020547: attachments upload
mod - admin/schema.php Diff File
mod - api/soap/mc_file_api.php Diff File
mod - core/file_api.php Diff File

Issue History

Date Modified Username Field Change
2016-01-28 05:30 DLeon New Issue
2016-01-28 07:25 dregad Assigned To => dregad
2016-01-28 07:25 dregad Status new => assigned
2016-01-28 07:25 dregad Note Added: 0052417
2016-01-28 07:25 dregad Assigned To dregad =>
2016-01-28 07:25 dregad Status assigned => feedback
2016-01-28 07:41 DLeon Note Added: 0052419
2016-01-28 07:41 DLeon Status feedback => new
2016-01-28 08:40 atrol Note Added: 0052420
2016-01-28 09:57 dregad Note Added: 0052421
2016-01-28 09:57 dregad Status new => feedback
2016-01-28 12:56 DLeon Note Added: 0052424
2016-01-28 12:56 DLeon Status feedback => new
2016-01-28 13:50 cproensa Note Added: 0052425
2016-01-29 03:59 DLeon Note Added: 0052429
2016-01-29 04:05 cproensa Note Added: 0052430
2016-01-29 04:11 DLeon Note Added: 0052431
2016-01-29 04:17 DLeon Note Added: 0052432
2016-01-29 04:20 cproensa Note Added: 0052433
2016-01-29 06:49 dregad Note Added: 0052434
2016-01-29 07:07 cproensa Note Added: 0052436
2016-01-29 12:24 dregad Note Added: 0052438
2016-01-29 12:24 dregad Status new => feedback
2016-01-29 13:03 cproensa Note Added: 0052439
2016-01-29 18:38 cproensa Note Edited: 0052439 View Revisions
2016-01-30 05:40 dregad Note Added: 0052446
2016-01-30 05:40 dregad Note Edited: 0052446 View Revisions
2016-01-30 05:41 dregad Note Edited: 0052446 View Revisions
2016-01-30 05:41 dregad Note Edited: 0052446 View Revisions
2016-01-30 05:42 dregad Note Edited: 0052446 View Revisions
2016-01-30 05:45 dregad Assigned To => dregad
2016-01-30 05:45 dregad Status feedback => assigned
2016-01-30 05:45 dregad Target Version => 1.3.0-rc.2
2016-01-30 05:45 dregad Note Added: 0052447
2016-01-30 18:58 dregad Tag Attached: MySQL
2016-01-30 19:05 dregad Note Added: 0052450
2016-01-30 19:06 dregad Product Version 1.3.0-rc.1 => 1.3.0-beta.1
2016-01-30 19:06 dregad Summary Attachments can't be uploaded after update => Attachments can't be uploaded after upgrade from 1.2 with MySQL in STRICT_ALL_TABLES sql_mode
2016-02-03 17:21 atrol Note Added: 0052466
2016-02-04 02:33 dregad Tag Attached: mssql
2016-02-05 17:05 dregad Changeset attached => MantisBT master f1d5a8b3
2016-02-05 17:05 dregad Changeset attached => MantisBT master a3e52aab
2016-02-05 17:05 dregad Changeset attached => MantisBT master c7a47d31
2016-02-05 17:05 dregad Changeset attached => MantisBT master 53245f5c
2016-02-05 17:05 dregad Status assigned => resolved
2016-02-05 17:05 dregad Resolution open => fixed
2016-02-05 17:05 dregad Fixed in Version => 1.3.0-rc.2
2016-06-12 00:42 vboctor Status resolved => closed
2016-11-09 10:44 dregad Relationship added related to 0021890