View Issue Details
ID | Project | Category | View Status | Date Submitted | Last Update |
---|---|---|---|---|---|
0021883 | mantisbt | db mssql | public | 2016-11-08 00:32 | 2016-12-30 15:54 |
Reporter | higgins911 | Assigned To | dregad | ||
Priority | normal | Severity | block | Reproducibility | always |
Status | closed | Resolution | fixed | ||
Platform | Windows | OS | 2012 Server | OS Version | R2 |
Product Version | 1.3.3 | ||||
Target Version | 1.3.5 | Fixed in Version | 1.3.5 | ||
Summary | 0021883: MSSQL installation fails with BAD ALTER TABLE error | ||||
Description | After configuring IIS and PHP with MSSQL driver and ODBC connect, while I try to run installation through http://localhost/mantis/admin/install.php it is displaying an error as below and the installation abort without completed. Schema step 65: AlterColumnSQL ( mantis_user_pref_table ) I am using MS SQL 2014. Thanks | ||||
Tags | No tags attached. | ||||
I don't use MSSQL, but looking at the doc [1] it would appear that supplying the DEFAULT constraint is not supported when altering a column. If that is indeed the case, then it's an issue with underlying ADOdb library. [1] https://msdn.microsoft.com/en-us/library/ms190273.aspx#Syntax (see example D https://msdn.microsoft.com/en-us/library/ms190273.aspx#add) |
|
I'm no expert in MSSQL. From what i think is happening: MSSQL does not accept a "default" declaration in an alter table command, as that should be modified separately through table contrains. @dregad, do you know if this is an issue with AdoDb? I guess that other AlterColumnSQL steps will also fail |
|
In theory, since 'redirect_delay I NOT NULL DEFAULT 0' is valid ADOdb syntax, the library should generate a proper SQL statement (or a series of them) to handle it. It's entirely possible that it does not, but as I said I don't use MSSQL so I can't confirm or test. Changing schema.php might fix the issue, but I'm not sure it's the right way to go, I'm concerned we might introduce regressions by doing so. |
|
While searching for solutions I came across this thread in the forums. Looks like others are experiencing the same issue. Many thanks for investigating. If there is something you'd like for me to try I'm open to doing it. http://mantisbt.org/forums/viewtopic.php?t=24007&p=59890 |
|
This is definitely an issue with the underlying ADOdb library. With MSSQL altering a column is a 3 step process:
In the actual 'datadict-mssqlnative.inc' the function 'AlterColumnSQL' is commented, The 'datadict-mssqlnative.inc' in MantisBT 1.2x had code to handle this. Seems this |
|
I apologize, but this is my first time trying to use Mantis. I suppose there's a product issue (ADOdb library)? If so, is there a path forward to address the issue? Thanks again! |
|
I also tried "Print SQL Queries instead of Writing to the Database" but I get the following error: System Warning 'htmlentites() expects parameter 1 to be a string, array given in 'S:\inetpub\wwwroot\mantisbt\admin\install.php' line 952 Please use the "Back" button in your web browser to return ot 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. |
|
@higgings911, sorry for having those problems to get MantisBT installed in @dregad, so with MSSQL still on the table even for 2.x (0021841) this is indeed a blocker and needs to be fixed. I offer my help to test, but don't expect me to supply patches (my php skills are really limited). My current environment (Win7, IIS, MSSQL2012, MantisBT 2.0.0-rc.1) runs stable, so this should be only an issue with install/update (schema.php ...) and MSSQL. |
|
@obmsch, thanks for your input and the insight on how things work here. I'm definitely learning as I go and willing to go through the suggestions you posted. I probably would need some "hand holding" on what to do though. If anyone has some suggestions I'm game. Thanks again, for taking the time out of your day to assist with my issue. |
|
@higgens911, I remember that it wasn't a smooth process for me to install 1.2 and migrate to 1.3 (2.0rc) on MSSQL, I patched 'schema.php' and used SSMS to apply the left changes. This should be better today. |
|
@obmsch many thanks for your input especially the pointer to how this was handled in 1.2x, as well as the offer for testing help, it is appreciated. I'll ping you if there is something you can do for us. |
|
@dregad, made some tests today for a fresh install of 2.0rc1 with a patched version of 'datadict_mssqlnative.inc.php' (from my 1.2.19 backup, with a couple of modifications). This almost works.
Before I proceed with this 2 questions regarding php:
|
|
Found no way to edit my last note, so as a add on: Perhaps AlterColumnSql should Drop/Recreate an existing constraint independently from a given 'DEFAULT...', and either recreate the original or create a new one on 'DEFAULT...'. |
|
After some thoughts I come to the conclusion, that handling 3. (Index problem in AlterColumnSQL) 'silently' Thoughts? |
|
My thoughts at the moment is that if we want to support schema changes for mssql we should take into account that ALTER is tricky, and needs to be done as a complex operation in mantis layer. Another option is fix it upstream, but unfortunately i don't have the expertise nor the familiarity with the library internals. For the mantis part, a custom function for altering could be done. |
|
I'd be willing to configure a temporary SQLServer environment for the core developers to work on the issue if necessary? |
|
I have filed an issue and submitted a patch on ADOdb to fix the problems with Drop/Alter column on MSSQL. EDIT [dregad]: adding link https://github.com/ADOdb/ADOdb/issues/290 |
|
Thanks for that. I'll follow up with Mark Newnham, the ADOdb dev who maintains the MSSQL drivers. |
|
Some more info on why step 66 (even with my patch) doesn't work for MSSQL: 1) This an AlterColumnSQL ( ... possible_values X NOTNULL DEFAULT \" '' \"
Despite the fact, that the comment (a) says the opposite from what the statement does, in the end both NOTNULL and DEFAULT \" '' \" are removed and the AlterColumnSQL (patched) has to handle: As (3) looks like a bug or a least an over-reacting fix to handle the restrictions/capabilties of some dbs, I will file an issue for this on ADOdb. BTW: This field will lack NOTNULL for a new installation of MantisBT on mysql too. I will revise and resubmit my patch to handle an ALTER COLUMN without a default as an implicit drop of an existing constraint, when I am back in office. |
|
Using my revised patch I am almost able to install MantisBT 2.0.0-rc.1. Only the last step(209) is failing still (might be related to 0021901). Created in step 200 with "I DEFAULT '0'" and indexed in step 201. And because If I run the install with a modified schema ("I NOTNULL DEFAULT '0'" in step 200) all is Ok. |
|
Don't worry, I can live with unified diff (as long as I can apply it, i.e. it is clear what the patch is based on) I haven't had time to look at your patch, and Mark hasn't responded yet. |
|
@obmsch ADOdb 5.20.9 has been released, including your patch. Would you be able to test a MantisBT installation to see if it works now (except for the issue in step 209 per 0021883:0054518 - I'll look at this separately) ? Thanks in advance |
|
@dregad: Tested with: Installation fails on step 209 as expected. Rerun with modified schema (step 200: user_id I DEFAULT '0' -> user_id I UNSIGNED NOTNULL DEFAULT '0') completes successfully. |
|
Thanks for the confirmation ! |
|