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 closeddregad Installation on MSSQL fails at step 209 

Activities

dregad

dregad

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

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.

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.

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.

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 you'd like for me to try I'm 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 can't 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 there's 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 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.

higgins911

higgins911

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.

obmsch

obmsch

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

I'll 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. 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?
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 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).

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

higgins911

higgins911

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?

obmsch

obmsch

2016-11-11 06:41

reporter   ~0054503

Last edited: 2016-11-11 06:51

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. I'll 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) 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 '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 -> 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.

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

dregad

dregad

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.

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 - I'll 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 05:28

dregad


Details Diff
Update ADOdb library to 5.20.9

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