View Issue Details

IDProjectCategoryView StatusLast Update
0022063mantisbtdb mssqlpublic2017-04-19 06:02
Reporterobmsch Assigned Todregad  
PrioritynormalSeverityblockReproducibilityalways
Status closedResolutionfixed 
Product Version1.3.0-beta.1 
Target Version1.3.9Fixed in Version1.3.9 
Summary0022063: Installation on MSSQL fails at step 209
Description

The last step (209) of the installation is failing (might be related to 0021901).

"user_id I UNSIGNED NOTNULL DEFAULT '0'" is the culprit here.

The column is created in step 200 as "user_id I DEFAULT '0'"

An index on that column is created in step 201; because of that index MSSQL doesn't allow the change to NOTNULL in 209.

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

Additional Information

Initially reported by @obmsch in 0021883:0054518

TagsNo tags attached.

Relationships

related to 0021901 new oracle, schema steps 209 fails 
related to 0021883 closeddregad MSSQL installation fails with BAD ALTER TABLE error 
related to 0020565 closedcproensa Installation crashes when using SQL Server 2012 

Activities

MDylan

MDylan

2017-01-05 03:33

reporter   ~0054929

We have the same problem with 2.0.0 stable version, so we can't install it on MSSQL.
Any update for this issue?

BAD
ALTER TABLE mantis_api_token_table DROP CONSTRAINT DFmantis_ap_user07AC1A97
ALTER TABLE mantis_api_token_table ALTER COLUMN user_id INT NOT NULL
ALTER TABLE mantis_api_token_table ADD CONSTRAINT DFmantis_api_token_table__user_id634e DEFAULT 0 FOR user_id
ALTER TABLE mantis_api_token_table DROP CONSTRAINT DFmantis_ap_date08A03ED0
ALTER TABLE mantis_api_token_table ALTER COLUMN date_created INT NOT NULL
ALTER TABLE mantis_api_token_table ADD CONSTRAINT DFmantis_api_token_table__date_created3c6f DEFAULT 1 FOR date_created
ALTER TABLE mantis_api_token_table DROP CONSTRAINT DFmantis_ap_date09946309
ALTER TABLE mantis_api_token_table ALTER COLUMN date_used INT NOT NULL
ALTER TABLE mantis_api_token_table ADD CONSTRAINT DFmantis_api_token_table__date_used3d7c DEFAULT 1 FOR date_used
SQLState: 42000 Error Code: 5074 Message: [Microsoft][ODBC Driver 11 for SQL Server][SQL Server]The index 'idx_user_id_name' is dependent on column 'user_id'. SQLState: 42000 Error Code: 4922 Message: [Microsoft][ODBC Driver 11 for SQL Server][SQL Server]ALTER TABLE ALTER COLUMN user_id failed because one or more objects access this column.

obmsch

obmsch

2017-01-05 05:03

reporter   ~0054936

@MDylan: Is this a new install or an upgrade from an earlier version?

1) New install
Easiest way would be to drop the newly created Mantis DB (SSMS).
After that alter 'schema.php' step(200) as detailed in the description of this issue.
Rerun the installation.

2) Upgrade
a) If possible restore the DB prior to the failed upgrade, fix schema (1) and rerun the upgrade.
b) If not use SSMS to manually apply step(209). This may include dropping the index created
in step(201) beforehand and restoring it afterwards.
In 'config_table' set 'database_version(value)' to '209' (!!!).
Finally rerun the upgrade to complete.

MDylan

MDylan

2017-01-05 08:29

reporter   ~0054940

@obmsch
Thank you, this is helped for me (new install).

obmsch

obmsch

2017-03-22 11:41

reporter   ~0056163

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

dregad

dregad

2017-03-31 16:13

developer   ~0056303

Considering that this blocks installation, for consistency, I'll backport this to 1.3.x (hence the change in target version) and apply it in supported 2.x releases as well.

Related Changesets

MantisBT: master 3a0706a1

2017-03-22 07:15

obmsch

Committer: dregad


Details Diff
Modify schema to get install/upgrade work with db-mssql.

Although the problem appears in step 209, the real cause is the
combination of steps 200 and 201. The Index ('user_id','name')
created in step 201 (with a nullable 'user_id' as of step 200)
forbids the alteration of 'user_id' to NOTNULL in step 209, at
least with mssql. So fix this in step 200 and set 'user_id'
NOTNULL right from start.

While this is a 'post release' change, it is justifiable as it
happens in the same install/upgrade sequence and the final
db (schema) is identical.

Fixes 0022063
Affected Issues
0022063
mod - admin/schema.php Diff File

MantisBT: master-1.3.x 8decd714

2017-03-22 07:15

obmsch

Committer: dregad


Details Diff
Modify schema to get install/upgrade work with db-mssql.

Although the problem appears in step 209, the real cause is the
combination of steps 200 and 201. The Index ('user_id','name')
created in step 201 (with a nullable 'user_id' as of step 200)
forbids the alteration of 'user_id' to NOTNULL in step 209, at
least with mssql. So fix this in step 200 and set 'user_id'
NOTNULL right from start.

While this is a 'post release' change, it is justifiable as it
happens in the same install/upgrade sequence and the final
db (schema) is identical.

Fixes 0022063

Signed-off-by: Damien Regad <dregad@mantisbt.org>

Backported from master 3a0706a1bc291be6ee684bd30199bd6544f0cc6b
Affected Issues
0022063
mod - admin/schema.php Diff File

MantisBT: master-2.1 5fec5fb9

2017-03-22 07:15

obmsch

Committer: dregad


Details Diff
Modify schema to get install/upgrade work with db-mssql.

Although the problem appears in step 209, the real cause is the
combination of steps 200 and 201. The Index ('user_id','name')
created in step 201 (with a nullable 'user_id' as of step 200)
forbids the alteration of 'user_id' to NOTNULL in step 209, at
least with mssql. So fix this in step 200 and set 'user_id'
NOTNULL right from start.

While this is a 'post release' change, it is justifiable as it
happens in the same install/upgrade sequence and the final
db (schema) is identical.

Fixes 0022063
Affected Issues
0022063
mod - admin/schema.php Diff File

MantisBT: master-2.2 98a31d53

2017-03-22 07:15

obmsch

Committer: dregad


Details Diff
Modify schema to get install/upgrade work with db-mssql.

Although the problem appears in step 209, the real cause is the
combination of steps 200 and 201. The Index ('user_id','name')
created in step 201 (with a nullable 'user_id' as of step 200)
forbids the alteration of 'user_id' to NOTNULL in step 209, at
least with mssql. So fix this in step 200 and set 'user_id'
NOTNULL right from start.

While this is a 'post release' change, it is justifiable as it
happens in the same install/upgrade sequence and the final
db (schema) is identical.

Fixes 0022063
Affected Issues
0022063
mod - admin/schema.php Diff File