MantisBT - TestLink
View Issue Details
0008568TestLinkDatabase MS-SQLpublic2019-02-15 11:372019-11-05 12:19
VincentLevel 
 
highblockalways
newopen 
MSSQL 12.0.2Windows server2012 R2
1.9.19 (2019 Q1) 
 
MSSQL 12.0.2
Firefox
5
TBD
0008568: MS-SQL SERVER - Database access error while logging
Hello, I'm trying to install Testlink on my company web server, it's a ms Server 2012 R2 with MSSQL 12 on it.
I've got a lot of problems, for one, the upgrade files aren't made for MSSQL (only mysql and postgres) but i fixed it and made a script that worked.

The main problem that I have is that i have some database error logs when I try to log in, it appears that i miss a few column in the users table, i'll upload some screens of the logs.

I tried to reinstall the DB a few time and always end up with this problem.

I downloaded the testlink instalation file on sourceforge.

Thank you
1- Install the testlink MSSQL DB
2- Try to login
 ==============================================================================

 DB Access Error - debug_print_backtrace() OUTPUT START

 ATTENTION: Enabling more debug info will produce path disclosure weakness (CWE-200)

            Having this additional Information could be useful for reporting

            issue to development TEAM.

 ==============================================================================

#0 database->exec_query( SELECT id,login,password,cookie_string,first,last,email, role_id,locale, login AS fullname, active,default_testproject_id, script_key,auth_method,creation_ts,expiration_date FROM users WHERE login = 'admin') called at [C:\Apache24\htdocs\testlink\lib\functions\database.class.php:553]
#1 database->fetchFirstRow( SELECT id,login,password,cookie_string,first,last,email, role_id,locale, login AS fullname, active,default_testproject_id, script_key,auth_method,creation_ts,expiration_date FROM users WHERE login = 'admin') called at [C:\Apache24\htdocs\testlink\lib\functions\tlUser.class.php:288]
0000002 tlUser->readFromDB(database Object ([db] => ADODB_mssqlnative Object ([databaseType] => mssqlnative,[dataProvider] => mssqlnative,[replaceQuote] => '',[fmtDate] => 'Y-m-d',[fmtTimeStamp] => 'Y-m-d\TH:i:s',[hasInsertID] => 1,[substr] => substring,[length] => len,[hasAffectedRows] => 1,[poorAffectedRows] => ,[metaDatabasesSQL] => select name from sys.sysdatabases where name <> 'master',[metaTablesSQL] => select name,case when type='U' then 'T' else 'V' end from sysobjects where (type='U' or type='V') and (name not in ('sysallocations','syscolumns','syscomments','sysdepends','sysfilegroups','sysfiles','sysfiles1','sysforeignkeys','sysfulltextcatalogs','sysindexes','sysindexkeys','sysmembers','sysobjects','syspermissions','sysprotects','sysreferences','systypes','sysusers','sysalternates','sysconstraints','syssegments','REFERENTIAL_CONSTRAINTS','CHECK_CONSTRAINTS','CONSTRAINT_TABLE_USAGE','CONSTRAINT_COLUMN_USAGE','VIEWS','VIEW_TABLE_USAGE','VIEW_COLUMN_USAGE','SCHEMATA','TABLES','TABLE_CONSTRAINTS','TABLE_PRIVILEGES','COLUMNS','COLUMN_DOMAIN_USAGE','COLUMN_PRIVILEGES','DOMAINS','DOMAIN_CONSTRAINTS','KEY_COLUMN_USAGE','dtproperties')),[metaColumnsSQL] => select c.name,
        t.name as type,
        c.length,
        c.xprec as precision,
        c.xscale as scale,
        c.isnullable as nullable,
        c.cdefault as default_value,
        c.xtype,
        t.length as type_length,
        sc.is_identity
        from syscolumns c
        join systypes t on t.xusertype=c.xusertype
        join sysobjects o on o.id=c.id
        join sys.tables st on st.name=o.name
        join sys.columns sc on sc.object_id = st.object_id and sc.name=c.name
        where o.name='%s',[hasTop] => top,[hasGenID] => 1,[sysDate] => convert(datetime,convert(char,GetDate(),102),102),[sysTimeStamp] => GetDate(),[maxParameterLen] => 4000,[arrayClass] => ADORecordSet_array_mssqlnative,[uniqueSort] => 1,[leftOuter] => *=,[rightOuter] => =*,[ansiOuter] => 1,[identitySQL] => select SCOPE_IDENTITY(),[uniqueOrderBy] => 1,[_bindInputArray] => 1,[_dropSeqSQL] => drop table %s,[connectionInfo] => Array (),[cachedSchemaFlush] => ,[sequences] => ,[mssql_version] => ,[database] => testlink,[host] => localhost,[port] => ,[user] => sa,[password] => not stored,[debug] => ,[maxblobsize] => 262144,[concat_operator] => +,[random] => rand(),[upperCase] => upper,[true] => 1,[false] => 0,[nameQuote] => ",[charSet] => ,[emptyDate] => ,[emptyTimeStamp] => ,[lastInsID] => ,[hasLimit] => ,[readOnly] => ,[hasMoveFirst] => ,[hasTransactions] => 1,[genID] => 0,[raiseErrorFn] => ,[isoDates] => ,[cacheSecs] => 3600,[memCache] => ,[memCacheHost] => ,[memCachePort] => 11211,[memCacheCompress] => ,[sysUTimeStamp] => ,[noNullStrings] => ,[numCacheHits] => 0,[numCacheMisses] => 0,[pageExecuteCountRows] => 1,[autoRollback] => ,[fnExecute] => ,[fnCacheExecute] => ,[blobEncodeType] => ,[rsPrefix] => ADORecordSet_,[autoCommit] => 1,[transOff] => 0,[transCnt] => 0,[fetchMode] => 2,[null2null] => null,[bulkBind] => ,[_oldRaiseFn] => ,[_transOK] => ,[_connectionID] => Resource id #71,[_errorMsg] => ,[_errorCode] => ,[_queryID] => Resource id #77,[_isPersistentConnection] => ,[_evalAll] => ,[_affected] => ,[_logsql] => ,[_transmode] => ,[*connectionParameters] => Array ()),[queries_array] => Array (),[is_connected] => 1,[nQuery] => 0,[overallDuration] => 0,[dbType] => mssqlnative,[databaselogEnabled] => 0,[databaselogQueries] => 0), 2) called at [C:\Apache24\htdocs\testlink\lib\functions\doAuthorize.php:66]
0000003 doAuthorize(database Object ([db] => ADODB_mssqlnative Object ([databaseType] => mssqlnative,[dataProvider] => mssqlnative,[replaceQuote] => '',[fmtDate] => 'Y-m-d',[fmtTimeStamp] => 'Y-m-d\TH:i:s',[hasInsertID] => 1,[substr] => substring,[length] => len,[hasAffectedRows] => 1,[poorAffectedRows] => ,[metaDatabasesSQL] => select name from sys.sysdatabases where name <> 'master',[metaTablesSQL] => select name,case when type='U' then 'T' else 'V' end from sysobjects where (type='U' or type='V') and (name not in ('sysallocations','syscolumns','syscomments','sysdepends','sysfilegroups','sysfiles','sysfiles1','sysforeignkeys','sysfulltextcatalogs','sysindexes','sysindexkeys','sysmembers','sysobjects','syspermissions','sysprotects','sysreferences','systypes','sysusers','sysalternates','sysconstraints','syssegments','REFERENTIAL_CONSTRAINTS','CHECK_CONSTRAINTS','CONSTRAINT_TABLE_USAGE','CONSTRAINT_COLUMN_USAGE','VIEWS','VIEW_TABLE_USAGE','VIEW_COLUMN_USAGE','SCHEMATA','TABLES','TABLE_CONSTRAINTS','TABLE_PRIVILEGES','COLUMNS','COLUMN_DOMAIN_USAGE','COLUMN_PRIVILEGES','DOMAINS','DOMAIN_CONSTRAINTS','KEY_COLUMN_USAGE','dtproperties')),[metaColumnsSQL] => select c.name,
        t.name as type,
        c.length,
        c.xprec as precision,
        c.xscale as scale,
        c.isnullable as nullable,
        c.cdefault as default_value,
        c.xtype,
        t.length as type_length,
        sc.is_identity
        from syscolumns c
        join systypes t on t.xusertype=c.xusertype
        join sysobjects o on o.id=c.id
        join sys.tables st on st.name=o.name
        join sys.columns sc on sc.object_id = st.object_id and sc.name=c.name
        where o.name='%s',[hasTop] => top,[hasGenID] => 1,[sysDate] => convert(datetime,convert(char,GetDate(),102),102),[sysTimeStamp] => GetDate(),[maxParameterLen] => 4000,[arrayClass] => ADORecordSet_array_mssqlnative,[uniqueSort] => 1,[leftOuter] => *=,[rightOuter] => =*,[ansiOuter] => 1,[identitySQL] => select SCOPE_IDENTITY(),[uniqueOrderBy] => 1,[_bindInputArray] => 1,[_dropSeqSQL] => drop table %s,[connectionInfo] => Array (),[cachedSchemaFlush] => ,[sequences] => ,[mssql_version] => ,[database] => testlink,[host] => localhost,[port] => ,[user] => sa,[password] => not stored,[debug] => ,[maxblobsize] => 262144,[concat_operator] => +,[random] => rand(),[upperCase] => upper,[true] => 1,[false] => 0,[nameQuote] => ",[charSet] => ,[emptyDate] => ,[emptyTimeStamp] => ,[lastInsID] => ,[hasLimit] => ,[readOnly] => ,[hasMoveFirst] => ,[hasTransactions] => 1,[genID] => 0,[raiseErrorFn] => ,[isoDates] => ,[cacheSecs] => 3600,[memCache] => ,[memCacheHost] => ,[memCachePort] => 11211,[memCacheCompress] => ,[sysUTimeStamp] => ,[noNullStrings] => ,[numCacheHits] => 0,[numCacheMisses] => 0,[pageExecuteCountRows] => 1,[autoRollback] => ,[fnExecute] => ,[fnCacheExecute] => ,[blobEncodeType] => ,[rsPrefix] => ADORecordSet_,[autoCommit] => 1,[transOff] => 0,[transCnt] => 0,[fetchMode] => 2,[null2null] => null,[bulkBind] => ,[_oldRaiseFn] => ,[_transOK] => ,[_connectionID] => Resource id #71,[_errorMsg] => ,[_errorCode] => ,[_queryID] => Resource id #77,[_isPersistentConnection] => ,[_evalAll] => ,[_affected] => ,[_logsql] => ,[_transmode] => ,[*connectionParameters] => Array ()),[queries_array] => Array (),[is_connected] => 1,[nQuery] => 0,[overallDuration] => 0,[dbType] => mssqlnative,[databaselogEnabled] => 0,[databaselogQueries] => 0), admin, admin, stdClass Object ([doSessionExistsCheck] => 1)) called at [C:\Apache24\htdocs\testlink\login.php:45]
No tags attached.
Issue History
2019-02-15 11:37VincentLevelNew Issue
2019-02-16 10:47fmanQA Team - Task Workflow Status => TBD
2019-02-16 10:47fmanSummaryDatabase acces error while logging => MS-SQL SERVER - Database access error while logging
2019-02-16 10:47fmanDescription Updatedbug_revision_view_page.php?rev_id=5748#r5748
2019-02-16 10:50fmanNote Added: 0028528
2019-11-05 12:19jean_pierre.delouche@asn.comNote Added: 0029281

Notes
(0028528)
fman   
2019-02-16 10:50   
Hi
if you have fixed the scripts, please share it.
It will be better if you can create a Pull Request, but the important thing is to get the scripts.

To understand if there is some missing column you can compare with Postgres Scripts because IMHO, these scripts are similar to MS SQL.

I do my best to maintain multiple Databases, but because I do not use Windows as a development environment the effort is not low.
(0029281)
jean_pierre.delouche@asn.com   
2019-11-05 12:19   
I had the same problem with mysql. By upgrading my database since 1.9.14 and checking consistently the result in the database, I found the following issue in the script install/sql/alter_tables/1.9.17/mysql/DB.1.9.17/step1/db_schema_update.sql
on INSERT INTO /*prefix*/role_rights (role_id,right_id) VALUES (8,28); => duplicated key.

In addition it seems bizarre not to insert for right_id 49, and I had the users table wrong as mentioned above, so I guess this might be the fix.
Seems to be the issue on other database types.

-- since 1.9.17
INSERT INTO /*prefix*/rights (id,description) VALUES (49,'exec_ro_access');
INSERT INTO /*prefix*/rights (id,description) VALUES (50,'monitor_requirement');
INSERT INTO /*prefix*/rights (id,description) VALUES (51,'codetracker_management');
INSERT INTO /*prefix*/rights (id,description) VALUES (52,'codetracker_view');
INSERT INTO /*prefix*/rights (id,description) VALUES (53,'cfield_assignment');
INSERT INTO /*prefix*/rights (id,description) VALUES (54,'exec_assign_testcases');

INSERT INTO /*prefix*/role_rights (role_id,right_id) VALUES (8,28);
INSERT INTO /*prefix*/role_rights (role_id,right_id) VALUES (8,29);
INSERT INTO /*prefix*/role_rights (role_id,right_id) VALUES (8,30);
INSERT INTO /*prefix*/role_rights (role_id,right_id) VALUES (8,50);
INSERT INTO /*prefix*/role_rights (role_id,right_id) VALUES (8,51);
INSERT INTO /*prefix*/role_rights (role_id,right_id) VALUES (8,52);
INSERT INTO /*prefix*/role_rights (role_id,right_id) VALUES (8,53);
INSERT INTO /*prefix*/role_rights (role_id,right_id) VALUES (8,54);


ALTER TABLE /*prefix*/testprojects ADD COLUMN code_tracker_enabled tinyint(1) NOT NULL default '0';
ALTER TABLE /*prefix*/users ADD COLUMN creation_ts timestamp NOT NULL DEFAULT now();
ALTER TABLE /*prefix*/users ADD COLUMN expiration_date date DEFAULT NULL;