View Issue Details

IDProjectCategoryView StatusLast Update
0006284mantisbtdb mssqlpublic2016-07-11 15:29
Reporterdrewr Assigned To 
PrioritynormalSeverityminorReproducibilityalways
Status newResolutionsuspended 
Product Version1.0.0rc1 
Summary0006284: INSERT statement in schema.php - MSSQL Error
Description

Having already got Mantis 1.0.0a3 working with MS SQL, I really like the new install.php/schema.php ADODB installation method !

However, the install process currently fails when using MS SQL as the database, because of the final (currently, the only) INSERT statement in the schema.php file, where we create the administrator account in the mantis_user_table.

The error I first got is that MS SQL complains because we use the "MD5(NOW())" MySQL function to create a cookie string, which of course doesn't exist in MS SQL. The solution I came up with for this was to create a variable directly before the insert statement in the schema.php file, using PHP to do the work:

$timenow = md5( strftime( "%Y-%m-%d %T" ) );
$upgrade[] = Array('InsertData', Array( config_get('mantis_user_table'),
"(1, 'administrator', '', 'root@localhost', '63a9f0ea7bb98050796b649e85481845', '2003-02-16 02:03:48', '2004-07-08 23:59:22', 1, 1, 90, 3, 0, 0, '$timenow')" )
);

The next error is caused because we try to manually insert a "1" into the "id" column, which is apparently prohibited in MS SQL unless you do something different. This is the error I got:

INSERT INTO mantis_user_table VALUES (1, 'administrator', '', 'root@localhost', '63a9f0ea7bb98050796b649e85481845', '2003-02-16 02:03:48', '2004-07-08 23:59:22', 1, 1, 90, 3, 0, 0, 'b32e6f9f6b8b7833e76b300269dbacbe')
[Microsoft][ODBC SQL Server Driver][SQL Server]An explicit value for the identity column in table 'mantis_user_table' can only be specified when a column list is used and IDENTITY_INSERT is ON.

So I removed the "1, " at the start of the query, and it works fine, but then it doesn't work in MySQL.

So I figure there are probably a couple of ways I know to fix this, and maybe some I don't know, that's why I'm reporting this ...

One way would be to change the query structure to provide a column list, so it's like:

INSERT INTO mantis_user_table (id, username, realname, ...etc) VALUES (1, 'administrator', '', 'root@localhost', '63a9f0ea7bb98050796b649e85481845', '2003-02-16 02:03:48', '2004-07-08 23:59:22', 1, 1, 90, 3, 0, 0, 'b32e6f9f6b8b7833e76b300269dbacbe')

OR check for the database type before doing the INSERT, and then change the syntax of it depending on what DB it is. This would probably be the simplest way, but the least forwards-compatible.

Hope this will help a little,

Drew

TagsNo tags attached.

Activities

grangeway

grangeway

2014-02-17 13:26

reporter   ~0039444

Marking as Suspended

MSSQL support is currently known broken. We are going to be replacing the DB Layer in Mantis to fix this properly after the next release.

I'd strongly advise using MYSQL for now.

The new DB layer already contains the appropriate fixes for MS SQL Support.

grangeway

grangeway

2014-03-27 19:12

reporter   ~0039762

MSSQL support is currently known broken. We are going to be replacing the DB Layer in Mantis to fix this properly after the next release.

I'd strongly advise using MYSQL for now.

The new DB layer already contains the appropriate fixes for MS SQL Support.

grangeway

grangeway

2014-05-16 15:00

reporter   ~0040317

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

drewr

drewr

2014-12-11 23:10

reporter   ~0042003

The wonder of open source software - my bug I opened over 9 years ago (!!) still has activity - I love it.

Thanks for the work you're putting in guys. I'm no longer using Mantis (changed jobs since I filed this), but I do remember it was a very useful product at the time.

At least I have MySQL now at my current work, though I'm not supposed to run "unapproved" software on our servers :)

Cheers,
Drew

Issue History

Date Modified Username Field Change
2005-09-21 05:37 drewr New Issue
2005-09-22 06:41 grangeway Assigned To => grangeway
2012-08-26 15:11 atrol Status new => assigned
2012-08-26 15:11 atrol Category installation => db mssql
2014-02-17 13:26 grangeway Note Added: 0039444
2014-03-27 19:12 grangeway Note Added: 0039762
2014-03-27 19:12 grangeway Status assigned => resolved
2014-03-27 19:12 grangeway Resolution open => suspended
2014-03-28 04:18 atrol Status resolved => assigned
2014-04-12 19:33 grangeway Target Version => 1.3.0-beta.1
2014-05-16 15:00 grangeway Note Added: 0040317
2014-05-23 15:08 grangeway Project mantisbt => @24@
2014-11-07 14:11 atrol Assigned To grangeway =>
2014-11-07 14:11 atrol Project @24@ => mantisbt
2014-11-07 14:48 atrol Status assigned => new
2014-12-08 02:10 atrol Target Version 1.3.0-beta.1 => 1.3.0-beta.2
2014-12-11 23:10 drewr Note Added: 0042003
2015-03-15 19:59 dregad Target Version 1.3.0-beta.2 => 1.3.0-beta.3
2015-09-06 17:47 vboctoradmin Target Version 1.3.0-beta.3 => 1.3.0-rc.1
2015-12-06 02:55 vboctor Target Version 1.3.0-rc.1 => 1.3.0-rc.2
2016-06-12 02:37 atrol Target Version 1.3.0-rc.2 => 1.3.0
2016-07-10 07:57 atroladmin Target Version 1.3.0 => 1.3.1
2016-07-11 15:29 atrol Target Version 1.3.1 =>