View Issue Details

IDProjectCategoryView StatusLast Update
0021883mantisbtdb mssqlpublic2016-12-30 15:54
Reporterhiggins911 
Assigned Todregad 
PrioritynormalSeverityblockReproducibilityalways
Status closedResolutionfixed 
PlatformWindowsOS2012 ServerOS VersionR2
Product Version1.3.3 
Target Version1.3.5Fixed in Version1.3.5 
Summary0021883: 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 )
BAD
ALTER TABLE mantis_user_pref_table ALTER COLUMN redirect_delay INT DEFAULT 0 NOT NULL
SQLState: 42000 Error Code: 156 Message: [Microsoft][ODBC Driver 13 for SQL Server][SQL Server]Incorrect syntax near the keyword DEFAULT.

I am using MS SQL 2014.

Thanks

TagsNo tags attached.

Relationships

related to 0011524 closeddregad 1.2.0rc2 with MS SQL not installable 
related to 0021901 new oracle, schema steps 209 fails 
related to 0022063 confirmed Installation on MSSQL fails at step 209 

Activities

dregad

dregad

2016-11-08 06:14

developer   ~0054463

I dont 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 its 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)

cproensa

cproensa

2016-11-08 06:58

developer   ~0054466

Im 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
Maybe we could remove the DEFAULT caluses from those, since most, if not all, consist of a change in datatype, but not of the default values as defined in table creation.
Presumably, steps 59, 60, did not fail for that reason.

dregad

dregad

2016-11-08 08:24

developer   ~0054469

do you know if this is an issue with AdoDb?

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.

Its entirely possible that it does not, but as I said I dont use MSSQL so I cant confirm or test.

Changing schema.php might fix the issue, but Im not sure its the right way to go, Im concerned we might introduce regressions by doing so.

higgins911

higgins911

2016-11-08 09:28

reporter   ~0054470

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 youd like for me to try Im open to doing it.

http://mantisbt.org/forums/viewtopic.php?t=24007&p=59890

obmsch

obmsch

2016-11-08 10:01

reporter   ~0054471

This is definitely an issue with the underlying ADOdb library.

With MSSQL altering a column is a 3 step process:

  • Drop an existing default constraint on that column.
  • alter the column.
  • re-apply the default constraint.

In the actual datadict-mssqlnative.inc the function AlterColumnSQL is commented,
so no special processing takes place. A similar problem exist with DropColumnSQL,
you cant drop a column with an existing constraint in MSSQL.

The datadict-mssqlnative.inc in MantisBT 1.2x had code to handle this. Seems this
never made it to ADOdb/master.
A discussion (for 1.2.0rc2) with patches (tomkraw1) can be found in 0011524,
although those patches differ from the implementation used in MantisBT 1.2x.

higgins911

higgins911

2016-11-08 11:39

reporter   ~0054472

I apologize, but this is my first time trying to use Mantis. I suppose theres a product issue (ADOdb library)? If so, is there a path forward to address the issue? Thanks again!

higgins911

higgins911

2016-11-08 12:13

reporter   ~0054474

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.

obmsch

obmsch

2016-11-08 14:03

reporter   ~0054476

@higgings911, sorry for having those problems to get MantisBT installed in
your environment. But the likes of us (IIS and at least MSSQL) are somewhat
exotic here. Keep in mind, this is an open source project, and all the developers
spend their spare time to bring this forward. Worse they might not even have
access to an environment like yours to fix and test these issues.
If you are not able/willing to go through the hops mentioned in my last note, my bet is you have either to wait for/and test a fix, or change your environment.

@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 dont 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.

higgins911

higgins911

2016-11-08 14:40

reporter   ~0054478

@obmsch, thanks for your input and the insight on how things work here. Im 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 Im game. Thanks again, for taking the time out of your day to assist with my issue.

obmsch

obmsch

2016-11-08 16:35

reporter   ~0054482

@higgens911, I remember that it wasnt 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.
So if you are not willing/able to wait for a real fix and comfortable with all kinds of errors happening, which you have to fix yourself/here, you might go for this (and I am not sure if this really works for 1.3): Download adodb.txt from 0011524, follow the instructions in there to patch your installation of datadict-mssqlnative.inc.php and try install.php again.

dregad

dregad

2016-11-08 19:29

developer   ~0054484

@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.

Ill ping you if there is something you can do for us.

obmsch

obmsch

2016-11-09 13:42

reporter   ~0054491

@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.
Open points:

  1. Install errors out (Driver not supported) after step 64. Looks like the state of the database is somewhat temporary and limited in the count of DDL statements it handels. Either running install.php again, or creating the (empty) database manually beforehand, remedies this. Cant test if this is a limitation of my SQLExpress installation or equally true for a full fletched SQLSERVER. A possible workaround would be to close and reopen the connection after the create.
  2. Step 65 doesnt work. The AlterColumnSql receives a statement without the DEFAULT..., looks like a parsing error. If I change X to XL in schema, this is fixed. The problem here is, only when a DEFAULT.. is present AfterColumnSql drops an existing constraint. Perhaps we have to do this in any case if a constraint exits.
  3. Steps 206 doesnt work (perhaps 207,208 too). Another limitation on MSSQL. As with constraints, you cant alter a column with an index. So AlterColumnSql has to scope for that too. This is a bit trickier, because we need to recreate the index with all flags before the drop.

Before I proceed with this 2 questions regarding php:

  1. In AlterColumnSql/DropColumnSql there is $constraintname = $row[0] (always empty for me). If I change to $constraintname = $row[name] this is filled correctly. Bug or different behaviour with php7?
  2. In AlterColumnSql/DropColumnSql an SQL-statement is executed to get the contraintname on the field ($rs = (Excecute)...). Needs that $rs to be closed/ freed, or is this done auto when $rs left the scope?
obmsch

obmsch

2016-11-09 14:44

reporter   ~0054493

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....

obmsch

obmsch

2016-11-10 03:18

reporter   ~0054494

After some thoughts I come to the conclusion, that handling 3. (Index problem in AlterColumnSQL) silently
(especially in an upstream lib) is utterly wrong. Its the responsibility of the caller to make sure his DDL goes trough.
In case of MantisBT that would mean making changes to schema to explicitly DropIndex/AlterField/CreateIndex
in this cases(perhaps only for dbtype MSSQL). I know this cant be done for a patch release (1.3.x), but 2.0
should be fine. Maybe offering a 1.4 release for this is a way out (and tag 1.3.x as broken for MSSQL).

Thoughts?

cproensa

cproensa

2016-11-10 03:59

developer   ~0054496

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.
This could be done by changing current and future schema changes to perform this as an exception for mssql.
Note that other considerations exists too for ALTERing blobs in oracle (see 0021890)

Another option is fix it upstream, but unfortunately i dont have the expertise nor the familiarity with the library internals.

For the mantis part, a custom function for altering could be done.
Bau as you noted before, none of the the core developers have access to a mssql enviroment.

higgins911

higgins911

2016-11-10 09:18

reporter   ~0054500

Id be willing to configure a temporary SQLServer environment for the core developers to work on the issue if necessary?

obmsch

obmsch

2016-11-11 06:41

reporter   ~0054503

Last edited: 2016-11-11 06:51

View 2 revisions

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

dregad

dregad

2016-11-11 07:12

developer   ~0054505

Thanks for that. Ill follow up with Mark Newnham, the ADOdb dev who maintains the MSSQL drivers.

obmsch

obmsch

2016-11-12 11:39

reporter   ~0054510

Some more info on why step 66 (even with my patch) doesnt work for MSSQL:

1) This an AlterColumnSQL ( ... possible_values X NOTNULL DEFAULT \ \
2) This field is originally created in step 24 as C(255) NOTNULL DEFAULT \ \ and therefore has a default constraint.
3) The _GenFields function in adodb-datadict.inc.php is internally used to parse the field(list) with its attributes. And in there are:

a) if ($ty == X || $ty == X2 || $ty == B) $fnotnull = false; // some blob types do not accept nulls
  ...
b) // some databases do not allow blobs to have defaults
    if ($ty == X) $fdefault = false;

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:
ALTER ... ALTER COLUMN possible_values TEXT
which gets no special processing (no default -> dont consider a constraint that might exist) and fails because of the existing constraint.

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.
@dregad, I know you would prefer a pull request, but I am with subversion and dont have time to install and figure out git on my win7 box for now.

obmsch

obmsch

2016-11-14 05:21

reporter   ~0054518

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).
user_id I UNSIGNED NOTNULL DEFAULT 0 is the culprit here.

Created in step 200 with I DEFAULT 0 and indexed in step 201. And because
of that index MSSQL doesnt allow the change to NOTNULL.

If I run the install with a modified schema (I NOTNULL DEFAULT 0 in step 200) all is Ok.

dregad

dregad

2016-11-14 05:54

developer   ~0054519

I know you would prefer a pull request, but I am with subversion and dont have
time to install and figure out git on my win7 box for now.

Dont 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 havent had time to look at your patch, and Mark hasnt responded yet.

dregad

dregad

2016-12-22 06:01

developer   ~0054816

@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 - Ill look at this separately) ?

Thanks in advance

obmsch

obmsch

2016-12-22 07:23

reporter   ~0054817

@dregad: Tested with:
MantisBT 2.0.rc1 (patched with adodb 5.20.9)
Windows 7 Prof SP1(32) - All Updates/Fixes applied (Locale: de-DE)
IIS 7.5
SQLServer Express 2012
PHP 7.0.14
Microsoft ODBC Driver 11
MS PHP Drivers for SQL Server PHP7 V4.1.4 (https://github.com/Microsoft/msphpsql/releases)

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.

dregad

dregad

2016-12-22 08:46

developer   ~0054818

Thanks for the confirmation !

dregad

dregad

2016-12-22 11:12

developer   ~0054821

PR https://github.com/mantisbt/mantisbt/pull/979

Related Changesets

MantisBT: master-1.3.x 219a10db

2016-12-22 10:28:52

dregad

Details Diff
Update ADOdb library to 5.20.9

Fixes 0021883, 0021930
mod - library/README.md Diff File
mod - library/adodb Diff File

Issue History

Date Modified Username Field Change
2016-11-08 00:32 higgins911 New Issue
2016-11-08 06:14 dregad Note Added: 0054463
2016-11-08 06:58 cproensa Note Added: 0054466
2016-11-08 08:24 dregad Note Added: 0054469
2016-11-08 09:28 higgins911 Note Added: 0054470
2016-11-08 10:01 obmsch Note Added: 0054471
2016-11-08 11:39 higgins911 Note Added: 0054472
2016-11-08 12:13 higgins911 Note Added: 0054474
2016-11-08 14:03 obmsch Note Added: 0054476
2016-11-08 14:40 higgins911 Note Added: 0054478
2016-11-08 16:35 obmsch Note Added: 0054482
2016-11-08 19:29 dregad Note Added: 0054484
2016-11-09 07:54 dregad Relationship added related to 0011524
2016-11-09 13:42 obmsch Note Added: 0054491
2016-11-09 14:44 obmsch Note Added: 0054493
2016-11-10 03:18 obmsch Note Added: 0054494
2016-11-10 03:59 cproensa Note Added: 0054496
2016-11-10 09:18 higgins911 Note Added: 0054500
2016-11-11 06:41 obmsch Note Added: 0054503
2016-11-11 06:51 dregad Note Edited: 0054503 View Revisions
2016-11-11 07:12 dregad Note Added: 0054505
2016-11-12 11:39 obmsch Note Added: 0054510
2016-11-14 05:21 obmsch Note Added: 0054518
2016-11-14 05:50 dregad Relationship added related to 0021901
2016-11-14 05:54 dregad Note Added: 0054519
2016-11-17 08:09 dregad Category installation => db mssql
2016-11-17 08:11 dregad Target Version => 1.3.4
2016-11-27 08:22 dregad Target Version 1.3.4 => 1.3.5
2016-12-22 06:01 dregad Note Added: 0054816
2016-12-22 07:23 obmsch Note Added: 0054817
2016-12-22 08:46 dregad Note Added: 0054818
2016-12-22 08:55 dregad Relationship added related to 0022063
2016-12-22 09:45 dregad Assigned To => dregad
2016-12-22 09:45 dregad Status new => assigned
2016-12-22 09:45 dregad Summary MantisBT 1.3 installation with MSSQL with BAD ALTER TABLE err => MSSQL installation fails with BAD ALTER TABLE error
2016-12-22 11:12 dregad Note Added: 0054821
2016-12-24 05:29 dregad Changeset attached => MantisBT master-1.3.x 219a10db
2016-12-24 05:29 dregad Status assigned => resolved
2016-12-24 05:29 dregad Resolution open => fixed
2016-12-24 05:29 dregad Fixed in Version => 1.3.5
2016-12-30 15:54 vboctor Status resolved => closed