View Issue Details
ID | Project | Category | View Status | Date Submitted | Last Update |
---|---|---|---|---|---|
0020547 | mantisbt | attachments | public | 2016-01-28 05:30 | 2016-11-09 10:44 |
Reporter | DLeon | Assigned To | dregad | ||
Priority | normal | Severity | major | Reproducibility | always |
Status | closed | Resolution | fixed | ||
Product Version | 1.3.0-beta.1 | ||||
Target Version | 1.3.0-rc.2 | Fixed in Version | 1.3.0-rc.2 | ||
Summary | 0020547: 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, The update process worked fine withour any error messages. | ||||
Steps To Reproduce | System: Settings in confic_inc.php: --- Attachments / File Uploads ---$g_allow_file_upload = ON; | ||||
Additional Information | Table field "content" is of type longblob and for longblob it is not possible to set a default value. | ||||
Tags | mssql, MySQL | ||||
related to | 0021890 | new | oracle, schema steps 203 204 fails |
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 |
|
I'm using standard MantisBT code without any changes. Did yo test it with an existing database from an older version? |
|
@dregad, 1.3.0-rc.2 will come with an updated adodb version. |
|
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
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. |
|
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, My logfile Shows the same query: And here the query of my file_api.php Really strange! Content is missing. |
|
The query is here: @DLeon Don't know if its a inherent problem on mysql core, or its solved by newer versions |
|
Again thanks for your support. @cproensa And this query changed from version 1.2.19. So maybe other people will have the same problem. @cproensa So the solution for me is now to globally disable the STRICT_TRANS_TABLES mode. |
|
@DLeon: |
|
@cproensa |
|
I used the following way: set GLOBAL sql_mode=''; |
|
good pint, i probably changed only for current session i will try again |
|
/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
Anyway, I will now try to replicate the problem using SET sql_mode = 'STRICT_ALL_TABLES'. |
|
@dregad |
|
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).
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.
|
|
I think this is ADOdb related My DB for 1.3-master was migrated from 1.2.3, and i have:
Other DBs, created by 1.3-beta3 and 1.2.19, both show:
So i tried, with 1.3 master:
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, @DLeon: |
|
You're right, but not upstream adodb - rather a consequence of our specific patches in 1.2. Using the following test code
The following SQL is generated (extra whitespace removed): 1.2: CREATE TABLE a ( b LONGBLOB NOT NULL ) 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. |
|
I think the right approach to address this issue is to fix the insert statement to populate the content column. |
|
To reproduce the problem, execute the following SQL commands: set global sql_mode = 'STRICT_ALL_TABLES'; 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, Please see proposed fix in PR https://github.com/mantisbt/mantisbt/pull/715 |
|
Seems not to be just a MySQL issue with sql_mode = 'STRICT_ALL_TABLES' but also a SQL-Server issue: |
|
MantisBT: master f1d5a8b3 2016-01-30 13:04 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 |
Affected Issues 0020547 |
|
mod - api/soap/mc_file_api.php | Diff File | ||
mod - core/file_api.php | Diff File | ||
MantisBT: master a3e52aab 2016-01-30 13:30 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 |
Affected Issues 0020547 |
|
mod - admin/schema.php | Diff File | ||
MantisBT: master c7a47d31 2016-01-30 23:49 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 |
Affected Issues 0020547 |
|
mod - api/soap/mc_file_api.php | Diff File | ||
mod - core/file_api.php | Diff File | ||
MantisBT: master 53245f5c 2016-02-05 11:59 Details Diff |
Fix 0020547: attachments upload |
Affected Issues 0020547 |
|
mod - admin/schema.php | Diff File | ||
mod - api/soap/mc_file_api.php | Diff File | ||
mod - core/file_api.php | Diff File |