View Issue Details

IDProjectCategoryView StatusLast Update
0021883mantisbtdb mssqlpublic2016-12-30 15:54
Reporterhiggins911 Assigned Todregad  
Status closedResolutionfixed 
PlatformWindowsOS2012 Server 
Product Version1.3.3 
Target Version1.3.5Fixed in Version1.3.5 
Summary0021883: MSSQL installation fails with BAD ALTER TABLE error

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


TagsNo tags attached.


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




2016-11-08 06:14

developer   ~0054463

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] (see example D



2016-11-08 06:58

developer   ~0054466

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



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.

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.



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 you'd like for me to try I'm open to doing it.



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 '' the function 'AlterColumnSQL' is commented,
so no special processing takes place. A similar problem exist with 'DropColumnSQL',
you can't drop a column with an existing constraint in MSSQL.

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



2016-11-08 11:39

reporter   ~0054472

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!



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.



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



2016-11-08 14:40

reporter   ~0054478

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



2016-11-08 16:35

reporter   ~0054482

@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.
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 '' and try 'install.php' again.



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.

I'll ping you if there is something you can do for us.



2016-11-09 13:42

reporter   ~0054491

@dregad, made some tests today for a fresh install of 2.0rc1 with a patched version of '' (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. Can't 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 doesn't 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 doesn't work (perhaps 207,208 too). Another limitation on MSSQL. As with constraints, you can't 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?


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



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 can't 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).




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 don't 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.



2016-11-10 09:18

reporter   ~0054500

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



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



2016-11-11 07:12

developer   ~0054505

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



2016-11-12 11:39

reporter   ~0054510

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 \" '' \"
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 '' 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 -> don't 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 don't have time to install and figure out git on my win7 box for now.



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 doesn't allow the change to NOTNULL.

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



2016-11-14 05:54

developer   ~0054519

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

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.



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 - I'll look at this separately) ?

Thanks in advance



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 (

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.



2016-12-22 08:46

developer   ~0054818

Thanks for the confirmation !



2016-12-22 11:12

developer   ~0054821


Related Changesets

MantisBT: master-1.3.x 219a10db

2016-12-22 10:28:52


Details Diff
Update ADOdb library to 5.20.9

Fixes 0021883, 0021930
Affected Issues
0021883, 0021930
mod - library/ 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