View Issue Details
ID | Project | Category | View Status | Date Submitted | Last Update |
---|---|---|---|---|---|
0007623 | mantisbt | db mssql | public | 2006-12-01 06:58 | 2023-05-04 16:23 |
Reporter | RogierB | Assigned To | dregad | ||
Priority | normal | Severity | minor | Reproducibility | always |
Status | closed | Resolution | duplicate | ||
Summary | 0007623: Can't store binary files into MSSQL database / can't attach files to bugs | ||||
Description | When the MSSQL Database server is used, it's not possible to store binary files in the database. The error shown after the Steps To Reproduce is: Database query failed. Error received from database was 0000206: Operand type clash: text is incompatible with image for the query: INSERT INTO mantis_bug_file_table Text files are stored correctly into the database. I tried to set the mantis_bug_file_table content from image to text, but then only the first 4000 bytes are stored in the database. In additional info is a description of how image / text / ntext should be implemented in MSSQL. | ||||
Steps To Reproduce |
| ||||
Additional Information | Managing ntext, text, and image Data If an ntext, text, and image data value is no longer than a Unicode, character, or binary string (4,000 characters, 8,000 characters, 8,000 bytes respectively), the value can be referenced in SELECT, UPDATE, and INSERT statements much the same way as the smaller data types. For example, an ntext column with a short value can be referenced in a SELECT statement select list the same way an nvarchar column is referenced. Some restrictions that must be observed, such as not being able to directly reference an ntext, text, or image column in a WHERE clause. These columns can be included in a WHERE clause as parameters of a function that returns another data type (such as ISNULL, SUBSTRING or PATINDEX) or in an IS NULL, IS NOT NULL, or LIKE expression. Handling Larger Data Values The database APIs follow a common pattern in the ways they handle long ntext, text, and image columns: To read a long column, the application simply includes the ntext, text, or image column in a select list, and then binds the column to a program variable large enough to hold a reasonable block of the data. The application then executes the statement and uses an API function or method to retrieve the data into the bound variable one block at a time. To write a long column, the application executes an INSERT or UPDATE statement with a parameter marker (?) in the place of the value to be placed in the ntext, text, or image column. The parameter marker (or parameter in the case of ADO) is bound to a program variable large enough to hold the blocks of data. The application goes into a loop where it first moves the next set of data into the bound variable, and then calls an API function or method to write that block of data. This is repeated until the entire data value has been sent. To enable the option, execute the sp_tableoption stored procedure, specifying text in row as the option name and on as the option value. The default maximum size that can be stored in a row for a BLOB (binary large object: text, ntext, or image data) is 256 bytes, but values may range from 24 through 7000. To specify a maximum size that is not the default, specify an integer within the range as the option value. text, ntext, or image strings are stored in the data row if the following conditions apply: text in row is enabled. The length of the string is shorter than the limit specified in @OptionValue There is enough space available in the data row. If a text, ntext, or image string is larger than the specified limit or the available space in the row, pointers are stored in the row instead. The conditions for storing the BLOB strings in the row still apply though: There must be enough space in the data row to hold the pointers. | ||||
Tags | No tags attached. | ||||
Attached Files | |||||
got this working myself changed in file_api.php :
to and also the following line:
to $query = "INSERT INTO $t_file_table updated the php.ini file and set mssql.textsize = 2147483647 and mssql.textlimit = 2147483647 (these values may be a 'bit' too large but it did the trick, besides php upload limit will also reject larger files). The purpose of setting those values is that else only the first 4096 bytes get stored in the server. Couldn't do this with ini_set('mssql.textsize','2147483647'). The bug_api.php also has an file upload routine, but i don't think it's being used, else it probably should also be updated. |
|
See 0006063. We came to the same conclusion/patch. |
|
As of 1.1.0a2 this is still an issue and RogierB's fix still works. Don't forget to change mssql.textsize and mssql.textlimit. |
|
It is still an issue in version 1.1.8. atabase query failed. Error received from database was 0002006: MySQL server has gone away for the query: INSERT INTO mantis_bug_file_table |
|
There have been several changes in file upload mechanism since version 1.2, as well as in the ADOdb abstraction layer module for mssql. Please check if problem is still reproducible using a nightly build >= 18-Aug-2012, and report any issues you may encounter. |
|
@degrad, I will test this as soon as I get a chance, good job. (Luckily we are still using mantis bt after 6 years :) ) |
|
Marking as Suspended MSSQL support is currently known broken. We are going to be replacing the DB Layer in Mantis to fix this properly after the next release. I'd strongly advise using MYSQL for now. The new DB layer already contains the appropriate fixes for MS SQL Support. |
|
MSSQL support is currently known broken. We are going to be replacing the DB Layer in Mantis to fix this properly after the next release. I'd strongly advise using MYSQL for now. The new DB layer already contains the appropriate fixes for MS SQL Support. |
|
Is the release schedule for the new DB layer for MSSQL available? |
|
Dcoomber, I plan to have completed testing of the new DB layer with MSSQL at some point over this weekend. Would you be interested in helping to test the new layer? Paul |
|
Paul, I am willing to assist with testing of the new FB layer. If possible, please PM me and we can discuss further. David |
|
Hi guys, We have also got around the attachment issue in MSSQL with the latest version of mantis by altering the following: Change these lines in core/file_api.php: $query = "INSERT INTO $t_file_table Remove $c_content from the Array() and put it directly into the query parameters: $query = "INSERT INTO $t_file_table |
|
MantisBT currently supports Mysql and has support for other database engines. The support for other databases is known to be problematic. Having implemented the current database layer into Mantis 10 years ago, I'm currently working on replacing the current layer. If you are interested in using Mantis with non-mysql databases - for example, Oracle, PGSQL or MSSQL, and would be willing to help out testing the new database layer, please drop me an email at paul@mantisforge.org In the meantime, I'd advise running Mantis with Mysql Only to avoid issues. Thanks |
|
The issue still exists and is due to an unnecessary varbinary/image encoding intended for inline queries. I examined all (4) instances of db_prepare_binary_string() calls, and in all cases the return value is now used in parameterized queries (i.e. with db_param()). Because of this, no encoding is necessary at least for the mssqlnative driver, and very likely for others as well. I do not know if such encoding is necessary for PostgreSQL, but I propose to actually make the db_prepare_binary_string() function a no-op for the time being, until any broken use cases emerge. As it stands, this function appears to hurt more than it helps. If not made a no-op, then the function would need to be split down the road into two variants: one for inlined query values, and one for parameterized queries. Attached here is the minimal patch needed for mssqlnative. mssqlnative-binary-min.patch (826 bytes)
diff -ur -U 5 mantisbt-1.3.0-beta.3\core\database_api.php mantisbt-1.3.0-beta.3-mspl1\core\database_api.php --- mantisbt-1.3.0-beta.3\core\database_api.php Sun Sep 06 14:10:14 2015 +++ mantisbt-1.3.0-beta.3-mspl1\core\database_api.php Tue Sep 08 14:03:14 2015 @@ -802,11 +802,12 @@ global $g_db; $t_db_type = config_get_global( 'db_type' ); switch( $t_db_type ) { case 'mssql': - case 'mssqlnative': + # XXX: Is this function even needed now? All of its instances are used in parameterized SQL queries (db_param()) + #case 'mssqlnative': # Definitely do not need to encode varbinary in parameterized SQL queries with MS sqldrv v3.2 (php_sqlsrv_56_nts.dll) case 'odbc_mssql': case 'ado_mssql': $t_content = unpack( 'H*hex', $p_string ); return '0x' . $t_content['hex']; break; |
|
Thanks for the research. I'll test this with PostgreSQL. |
|
I believe this was fixed in 0022208. Closing as duplicate. |
|