View Issue Details

IDProjectCategoryView StatusLast Update
0021155mantisbtdb mssqlpublic2017-01-18 18:09
Reporterkismert Assigned To 
PriorityhighSeveritymajorReproducibilityalways
Status newResolutionopen 
PlatformIIS, SQL Server 2016, PHP 5.3.28OSWindows 
Product Version1.2.19 
Summary0021155: SQL Server (mssqlnative) DROP DEFAULT constraints are not scripted when 'Script' option is checked
Description

When you select the Script checkbox for the SQL Server Native install, ALTER TABLE DROP CONSTRAINT statements are not generated before table columns with default values are dropped.
Running the resulting script generates MANY errors similar to:

Msg 5074, Level 16, State 1, Line 27
The object 'DFmantis_bu_date2B3F6F97' is dependent on column 'date_added'.
Msg 4922, Level 16, State 9, Line 27
ALTER TABLE DROP COLUMN date_added failed because one or more objects access this column.

Steps To Reproduce

1) Under IIS, setup SQL Server, SQL Server Native Client 11, PHP, sqlsrv
2) Unzip Mantis files to wwwroot
3) Open http://yoursite/mantisbt/admin/install.php
4) Set SQL connect, admin/user account
5) Check the 'Script' option
6) Run the install
7) Copy the SQL script generated into SSMS
8) Observe that ALTER TABLE tablename DROP CONSTRAINT constraintname statements are not generated before columns with DEFAULT constraints are dropped
9) When you try to execute, many Msg 5074 and Msg 4922 errors are generated

Additional Information

The Problem:
Code: /admin/datadict-mssqlnative.inc.php, function DropColumnSQL.
1) Since the script is not actively executing, the query "select name from sys.default_constraints WHERE object_name(parent_object_id) = '" ... returns no rows.
2) Therefore, no ALTER TABLE DROP CONSTRAINT statements get generated, because the object being queried doesn't exist.

Solution (SQL Server-Specific):
1) Decide on a column default constraint naming convention. I use: [df_table_column], i.e. [df_mantis_bug_file_table_date_added]
2) In the CREATE TABLE statement, name the constraint for the column:
[column] {type} {NULL/NOT NULL} CONSTRAINT [df_table_column] DEFAULT {default}
3) Then, when you need to drop the column later on, you can issue:
ALTER TABLE [table] DROP CONSTRAINT [df_table_column];
ALTER TABLE [table] DROP COLUMN [column];

This way, the same script will work when executed immediately, or copied and executed later.

TagsNo tags attached.

Activities

kismert

kismert

2016-06-24 13:34

reporter  

kismert

kismert

2016-07-12 11:03

reporter   ~0053578

@atrol - I would be willing to take a shot at fixing this, if you want me to.

Please also see note on 0021182.

Thanks,
-Ken

atrol

atrol

2016-07-12 13:11

developer   ~0053581

see 0021182:0053580

Issue History

Date Modified Username Field Change
2016-06-24 13:34 kismert New Issue
2016-06-24 13:34 kismert File Added: zTest_InstallScriptErrorDemo.sql
2016-07-12 11:03 kismert Note Added: 0053578
2016-07-12 13:11 atrol Note Added: 0053581
2017-01-18 18:09 atrol Category installation => db mssql