View Issue Details

IDProjectCategoryView StatusLast Update
0006284mantisbtdb mssqlpublic2023-05-04 16:23
Reporterdrewr Assigned Todregad  
PrioritynormalSeverityminorReproducibilityalways
Status closedResolutionno change required 
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

dregad

dregad

2023-04-22 10:45

developer   ~0067688

We are resolving this issue as "no change required", because it was reported against an old version of MantisBT which is no longer supported. Also noting @drewr's note that he's no longer using Mantis.

Anyone else facing this, please upgrade to the latest stable version [1]; if after doing so the problem still exists, do not hesitate to reopen the issue or file a new one.

[1] http://www.mantisbt.org/download.php