unable to add none admin users mantis 1.2.2 and few other..

[skydrol151]

Hello everyone,

Currently I'm having a problem with a new mantis install version 1.2.2 and few other database related issue, first thing first; I'm new to this whole web application setup, please excuse me if I'm not clear on anything i've posted here:

Mantis version 1.2.2
Windows 2003 Standard Edition SP2
IIS 6.0
FastCGI 1.5 for IIS 6.0
MS SQL server driver for PHP 1.1
MS SQL 2005 (9.0.3042)
SQL user with sysadmin rights

I've followed this guide and few other guides to get PHP running using MS WPI. Ran /admin/check.php and no "warning" or "bad" remarks
http://blogs.iis.net/donraman/archive/2 ... ndows.aspx

I couldn't get mantis to connect to MSSQL using $g_db_type = 'mssql' so i opt with 'odbc_mssql' from another post here, which allows mantis installation.

I'm able to create admins but anything below that user level would cause this error:
APPLICATION ERROR #401
Database query failed. Error received from database was #7001: [Microsoft][ODBC SQL Server Driver]COUNT field incorrect or syntax error for the query: SELECT DISTINCT p.id, p.name
FROM mantis_project_table p
LEFT JOIN mantis_project_user_list_table u
ON p.id=u.project_id AND u.user_id=?
WHERE p.enabled = ? AND
u.user_id IS NULL
ORDER BY p.name.

Clicking on summary page would get this:
APPLICATION ERROR #401
Database query failed. Error received from database was #7001: [Microsoft][ODBC SQL Server Driver]COUNT field incorrect or syntax error for the query: SELECT b.id, b.date_submitted, b.last_updated, MAX(h.date_modified) as hist_update, b.status
FROM mantis_bug_table b LEFT JOIN mantis_bug_history_table h
ON b.id = h.bug_id AND h.type=0 AND h.field_name='status' AND h.new_value=?
WHERE b.status >=? AND project_id=1
GROUP BY b.id, b.status, b.date_submitted, b.last_updated
ORDER BY b.id ASC.

When clicking on a test issue
APPLICATION ERROR #401
Database query failed. Error received from database was #37000: [Microsoft][ODBC SQL Server Driver][SQL Server]Incorrect syntax near ')'. for the query: SELECT id, name, description FROM mantis_tag_table WHERE id IN ( ) ORDER BY name ASC.

As you can see it's all db connection issue, and with my limited knowledge in SQL I couldn't find any info close to what the error gave me. I've already delete and re-create the database in case there were any fluke the first time. All green during the table creation. Please let me know if any thing I should check in php.ini, config_inc.php.

Thanks in advance!
Wilson

[istvanb]

Hey Wilson,

I am definitely not sure about what is wrong, but the queries looks funky. I have never seen anything like this. Can you check your mantis database tables to see if everything is ok? Are the rest of the mantis work fine? Maybe your issue has already been resolved, but you can PM me if you want to see if we can figure out something!

i-

[Boyd]

We've been having the same problems using the odbc_mssql driver on sql server 2008. Apparently the question marks in the query are supposed to be replaced by values, but for some reason this doesn't seem to happen.

I've written workarounds for three problematic queries, but I can't find the source of the problem.

Does anyone have any idea what could cause this?

Cheers,
Boyd

[Markus1234]

Hi Boyd,

could you tell me your workarounds? I get this error message if I want to manage a project:

APPLICATION ERROR #401
Datenbankabfrage fehlgeschlagen. Die Reportmeldung lautet #7001: [Microsoft][ODBC SQL Server Driver]COUNT field incorrect or syntax error für die Abfrage: SELECT DISTINCT u.id, u.username, u.realname
FROM mantis_user_table u
LEFT JOIN mantis_project_user_list_table p
ON p.user_id=u.id AND p.project_id=?
WHERE u.access_level<? AND
u.enabled = ? AND
p.user_id IS NULL
ORDER BY u.realname, u.username.

Everything else is working fine.

[Boyd]

Hi Boyd,

could you tell me your workarounds? I get this error message if I want to manage a project:

APPLICATION ERROR #401
Datenbankabfrage fehlgeschlagen. Die Reportmeldung lautet #7001: [Microsoft][ODBC SQL Server Driver]COUNT field incorrect or syntax error für die Abfrage: SELECT DISTINCT u.id, u.username, u.realname
FROM mantis_user_table u
LEFT JOIN mantis_project_user_list_table p
ON p.user_id=u.id AND p.project_id=?
WHERE u.access_level<? AND
u.enabled = ? AND
p.user_id IS NULL
ORDER BY u.realname, u.username.

Everything else is working fine.

Basically what the workaround does is, insert the values directly into the query instead of using the question marks to replace them later.

For your problem: open up the file 'mantisbt/core/print_api.php' in a text editor. I recommend you backup the file before you start changing it. Replace the query on line 1051 with:

Code: Select all

$query = "SELECT DISTINCT u.id, u.username, u.realname
	FROM $t_mantis_user_table u
	LEFT JOIN $t_mantis_project_user_list_table p
	ON p.user_id=u.id AND p.project_id=" . $c_project_id . "
	WHERE u.access_level<" . $t_adm . " AND
		u.enabled = 1 AND
		p.user_id IS NULL
	ORDER BY u.realname, u.username";

This should solve the problem for now. Keep in mind that this is the equivalent of using ducktape to fix your car. It's ugly and it might eventually cause other problems.

Cheers,
Boyd

[Markus1234]

Wow,
thank you very much! I am very impressed

It works!

[biglesias]

I am having the same issues trying to using the odbc driver for SQL Server. I am installing on SQL Server 2000. Any resolution of these issues?

[biglesias]

I have found the issue to be binding using the driver. In file \library\adodb\drivers\adodb-odbc-mssql.inc.php , I have changed the class variable $_bindInputArray to false in class ADODB_odbc_mssql. This appears to solve the problems for now.