View Issue Details

WikiJump to Notes
IDProjectCategoryView StatusDate SubmittedLast Update
0015454mantisbtdb mssqlpublic2013-02-02 15:232014-05-16 15:00
Reporterjeckyll Assigned Todregad  
PrioritynormalSeverityminorReproducibilityhave not tried
Status closedResolutionduplicate 
Product Version1.2.14 
Summary0015454: installation errors with SQLServer
Description

I had some errors when installing MantisBT 1.2.14 with SQL Server (v2012) and mssqlnative driver. Here's my findings :
(1) the first query to fail is the following



ALTER TABLE mantis_bugnote_table DROP COLUMN last_modified

This is because you cannot drop a column when it is included in an index

(2) Next, you have a syntax error with:



ALTER TABLE mantis_bug_revision_table DROP CONSTRAINT

ALTER TABLE mantis_bug_revision_table DROP COLUMN timestamp

(3) You have also to delete the index on the column timestamp abobe

My config : MantisBT 1.2.14, PHP 5.4.3/IIS on Windows 7, last version of SQL native driver for PHP (v3.0), SQL Server 2012 Express

Additional Information

Fixes :
(1) To drop the index, run



drop index mantis_bugnote_table.idx_last_mod

(2) Patching DropColumnSQL in adodb did the trick for me (see attachment), but I'm not sure about how this should fix.

(3) drop the index with



drop index mantis_bug_revision_table.idx_bug_rev_id_time

TagsNo tags attached.
Attached Files
datadict-mssqlnative.inc.php.patch (816 bytes)    
--- C:/WebSites/Mantisbt/library/adodb/datadict/datadict-mssqlnative.inc.origin.php	sam.  2 f�vr. 2013, 20:59:00
+++ C:/WebSites/Mantisbt/library/adodb/datadict/datadict-mssqlnative.inc.patched.php	sam.  2 f�vr. 2013, 20:59:30
@@ -189,8 +189,10 @@
 			$rs = $this->connection->Execute( "select name from sys.default_constraints WHERE object_name(parent_object_id) = '" . $tabname ."' AND col_name(parent_object_id, parent_column_id) = '" . $v . "'");
 			if ( is_object($rs) ) {
 				$row = $rs->FetchRow();
-				$constraintname = $row[0];
-				$sql[] = 'ALTER TABLE '.$tabname.' DROP CONSTRAINT '. $constraintname;
+				if ($row) {
+					$constraintname = $row[0];
+					$sql[] = 'ALTER TABLE '.$tabname.' DROP CONSTRAINT '. $constraintname;
+				}
 			}
 		
 			$f[] = "\n$this->dropCol ".$this->NameQuote($v);
datadict-mssqlnative.inc.php.patch (816 bytes)   
datadict-mssqlnative.inc.php.adodb510 vs mantis 1.2.14.diff (3,458 bytes)    
--- C:/Users/Chris/Temporaires/datadict-mssqlnative.inc.adodb510.php	mar. 10 nov. 2009, 10:34:54
+++ C:/Users/Chris/Temporaires/datadict-mssqlnative.inc.mantis 1.2.14.php	mar. 22 janv. 2013, 08:56:36
@@ -69,7 +69,7 @@
 		case 'TINYINT': return  'I1';
 		case 'SMALLINT': return 'I2';
 		case 'BIGINT':  return  'I8';
-		
+		case 'SMALLDATETIME': return 'T';
 		case 'REAL':
 		case 'FLOAT': return 'F';
 		default: return parent::MetaType($t,$len,$fieldobj);
@@ -121,19 +121,62 @@
 		return $sql;
 	}
 	
-	/*
-	function AlterColumnSQL($tabname, $flds)
+	function AlterColumnSQL($tabname, $flds, $tableflds='',$tableoptions='')
 	{
 		$tabname = $this->TableName ($tabname);
 		$sql = array();
-		list($lines,$pkey) = $this->_GenFields($flds);
+
+		list($lines,$pkey,$idxs) = $this->_GenFields($flds);
+		// genfields can return FALSE at times
+		if ($lines == null) $lines = array();
+		$alter = 'ALTER TABLE ' . $tabname . $this->alterCol . ' ';
 		foreach($lines as $v) {
-			$sql[] = "ALTER TABLE $tabname $this->alterCol $v";
+
+	       $not_null = false;
+	        if ($not_null = preg_match('/NOT NULL/i',$v)) {
+	           $v = preg_replace('/NOT NULL/i','',$v);
+	        }         
+
+			if (preg_match('/^([^ ]+) .*DEFAULT (\'[^\']+\'|\"[^\"]+\"|[^ ]+)/',$v,$matches)) {
+	            list(,$colname,$default) = $matches;
+				$existing = $this->MetaColumns($tabname);
+				$constraintname = false;
+				$rs = $this->connection->Execute( "select name from sys.default_constraints WHERE object_name(parent_object_id) = '" . $tabname ."' AND col_name(parent_object_id, parent_column_id) = '" . $colname . "'");
+				if ( is_object($rs) ) {
+					$row = $rs->FetchRow();
+					$constraintname = $row[0];
+				}
+	            $v = preg_replace('/^' . preg_quote($colname) . '\s/', '', $v);
+	            $t = trim(str_replace('DEFAULT '.$default,'',$v));
+				if ( $constraintname != false ) {
+					$sql[] = 'ALTER TABLE '.$tabname.' DROP CONSTRAINT '. $constraintname;
+				}
+				$sql[] = $alter . $colname . ' ' . $t ;
+				if ( $constraintname != false ) {
+					$sql[] = 'ALTER TABLE '.$tabname.' ADD CONSTRAINT '.$constraintname.' DEFAULT ' . $default . ' FOR ' . $colname;
+				} else {
+					$sql[] = 'ALTER TABLE '.$tabname.' ADD CONSTRAINT DF__'. $tabname . '__'.  $colname.  '__' . dechex(rand()) .' DEFAULT ' . $default . ' FOR ' . $colname;				
+				}
+				if ($not_null) {
+					$sql[] = $alter . $colname . ' ' . $t  . ' NOT NULL';
+				}
+			} else {
+				if ($not_null) {
+					$sql[] = $alter . $v  . ' NOT NULL';
+		         } else {
+					$sql[] = $alter . $v;
+				}
+			}
 		}
+		if (is_array($idxs)) {
+			foreach($idxs as $idx => $idxdef) {
+				$sql_idxs = $this->CreateIndexSql($idx, $tabname, $idxdef['cols'], $idxdef['opts']);
+				$sql = array_merge($sql, $sql_idxs);
+		}
 
+		}
 		return $sql;
 	}
-	*/
 	
 	function DropColumnSQL($tabname, $flds)
 	{
@@ -143,6 +186,13 @@
 		$f = array();
 		$s = 'ALTER TABLE ' . $tabname;
 		foreach($flds as $v) {
+			$rs = $this->connection->Execute( "select name from sys.default_constraints WHERE object_name(parent_object_id) = '" . $tabname ."' AND col_name(parent_object_id, parent_column_id) = '" . $v . "'");
+			if ( is_object($rs) ) {
+				$row = $rs->FetchRow();
+				$constraintname = $row[0];
+				$sql[] = 'ALTER TABLE '.$tabname.' DROP CONSTRAINT '. $constraintname;
+			}
+		
 			$f[] = "\n$this->dropCol ".$this->NameQuote($v);
 		}
 		$s .= implode(', ',$f);
datadict-mssqlnative.inc.php.adodb510 vs mantis 1.2.14.diff (3,458 bytes)   

Relationships

Relationship GraphDependency Graph
duplicate of 0011524 closeddregad 1.2.0rc2 with MS SQL not installable 
related to 0014095 closeddregad Cannot initialize DB on SQL Server 2008 R2 

Activities

dregad

dregad

2013-02-04 07:44

developer   ~0035019

Thanks for the bug report.

Mantis 1.2.14 comes bundled with a quite old version of ADOdb library (5.10). We may upgrade to a more recent one (see 0013713), but it's not confirmed yet.

Could you please download from Sourceforge the latest version of ADOdb (as I write this, 5.18a), and try again. If the error persists, I recommend that you open a bug report upstream on the ADOdb support forums [1], and post a link to it here.

It would also help if you could report here, the MantisBT schema version when the error(s) occur.

[1] http://phplens.com/lens/lensforum/topics.php?id=4
jeckyll

jeckyll

2013-02-04 16:40

reporter   ~0035027

Last edited: 2013-02-04 16:41

I tried using ADOdb 5.18a but it doesn't works better. This version is unable to drop columns which have default constraints, and this gives a lot of errors during installation process.

It seems to me like a bug, but the code suggests that this could be by design (some constraint-checking code has been removed in ADOdb 5.18a)

Here's the thread on Adodb forum :
http://phplens.com/lens/lensforum/msgs.php?id=19342
grangeway

grangeway

2013-02-04 17:00

reporter   ~0035028

MSSQL support broke when we removed the custom adodb patches we had in the core to make it work.

We should probably remove MSSQL as a supported DB until we get our new db layer in place.
dregad

dregad

2013-02-05 05:00

developer   ~0035030

MSSQL support broke when we removed the custom adodb patches we had in the core to make it work.

I may be mistaken, but I don't think these patches were removed from 1.2.x, only in master.

We should probably remove MSSQL as a supported DB until we get our new db layer in place.

Considering that most of the issues I know about relate to installation/upgrade, and that can in most cases be worked around by manually updating the schema, maybe just a warning in the documentation and possibly the installer would be sufficient for now.

Removing support entirely would probably cause issues to the many instances running on MSSQL today.
jeckyll

jeckyll

2013-02-05 06:53

reporter   ~0035031

Issues (1) and (3) reported above can indeed be easilly worked around with some basic SQL knowledge.

However for (2), letting the code as it is seems to me more problematic, since it requires an advanced knowlegde of Mantis / PHP to solve it.
grangeway

grangeway

2013-02-05 11:58

reporter   ~0035033

Dregad: RE patches - they were removed between 1.2.0RC2 and 1.2.0, as people didn't agree with us modifying upstream libraries.

The various forks of the next/db/2.0 branches that exist today happening about 12-15 months after that. I suspect that work started before that date.

Paul
dregad

dregad

2013-02-06 06:15

developer   ~0035053

RE patches - they were removed between 1.2.0RC2 and 1.2.0,
as people didn't agree with us modifying upstream libraries.

Well in 1.2.x branch there are still a lot of differences between upstream ADOdb 5.10 and the version we bundle, so I think your statement above is incorrect (or maybe only some of the patches were removed). Refer to 0013713, more specifically to dhx's note 0013713:0030836 and the linked mailing list discussion.

In 1.3.x (master) branch, ADOdb was upgraded to 5.11, and there are only whitespace differences (and an UTF8 BOM in a language file, which we probably don't care about)
jeckyll

jeckyll

2013-02-10 13:42

reporter   ~0035097

Some news from ADOdb forum [1] : ADOdb doesn't supports by design dropping columns with "DEFAULT" constraint on SQL Server

So it's up to clients apps to « make things work » in spite of this major incompatibility with SQL Server. And the custom patches meant to adress this issue in Mantis 1.2.14 [2] will remain necessary even with a more recent version of ADOdb.

[1] http://phplens.com/lens/lensforum/msgs.php?id=19342
[2] see attachment "datadict-mssqlnative.inc.php.adodb510 vs mantis 1.2.14.diff"
dregad

dregad

2013-02-13 06:12

developer   ~0035120

I'm not really sure why you interpret the response given by Mark Newnham as something that's by design in ADOdb, it seems to me the guy is just stating that refusing to drop a column with constraints is standard MSSQL behavior.

I would only get a response from John Lim (ADOdb author) as authoritative with regards to something being by design in the library.

From past experience when dealing with him, the best way to get a patch in, is to propose a fix for ADOdb as a diff against the latest version on the forum, and also e-mailing it to him.

some constraint-checking code has been removed in ADOdb 5.18a

Can you be more specific ? What version(s) did you compare against ? standard 5.10 ? MantisBT-bundled 5.10 ? Keep in mind that we do not use a standard version of the library.
jeckyll

jeckyll

2013-02-14 17:44

reporter   ~0035153

I agree that the "default constraint" problem with ADOdb may not be a deliberate choice of the author. But if ADOdb developers (at least one) are aware of MSSQL behavior (in this particular area) why didn't they do anything to handle it ?

Maybe I'm extrapolating a bit too far. Anyway, if there's a possibily to integrate the custom Mantis patch [1] into ADOdb it would be certainly a good thing to do, as these are not meant to solve a specific MantisBT problem, but a problem which concerns any app using ADOdb datadict to run schema upgrades on a mssql database.

Having said that, I'm not a php specialist and there's a lot of "grey areas" for me in this patch ; I have no idea whether it can be submitted "as it" to the ADOdb author or not.

some constraint-checking code has been removed in ADOdb 5.18a

The statement above is a mistake. At the time I wrote this comment, I didn't knew about the custom patches of ADOdb in Mantis, so I took bundled ADOdb lib in Mantis for standard ADOdb 5.10.

[1] see attachment "datadict-mssqlnative.inc.php.adodb510 vs mantis 1.2.14.diff". (This is a diff between standard ADOdb 5.10 and current version the library bundled in Mantis)
jeckyll

jeckyll

2013-02-15 21:53

reporter   ~0035180

Some additionnal remarks :
dregad

dregad

2013-02-18 05:40

developer   ~0035200

Thanks for the update and the reference to 0014095. Since the issue you reported is already documented, I'll resolve this as duplicate and add you to the monitoring list of 0011524. Please follow up there.
grangeway

grangeway

2014-05-16 15:00

reporter   ~0040354

MantisBT currently supports Mysql and has support for other database engines.

The support for other databases is known to be problematic.

Having implemented the current database layer into Mantis 10 years ago, I'm currently working on replacing the current layer.

If you are interested in using Mantis with non-mysql databases - for example, Oracle, PGSQL or MSSQL, and would be willing to help out testing the new database layer, please drop me an email at paul@mantisforge.org

In the meantime, I'd advise running Mantis with Mysql Only to avoid issues.

Thanks
Paul