IIS7 SQL 2008 R2 Upgrade fails 3.2.9->3.3.3

Moderator: crythias

Post Reply
ssanfordph
Znuny newbie
Posts: 8
Joined: 14 Mar 2013, 17:54
Znuny Version: 3.2.3

IIS7 SQL 2008 R2 Upgrade fails 3.2.9->3.3.3

Post by ssanfordph »

Currently I'm using OTRS ITSM 3.2.9 and am trying to upgrade to 3.3.3. The .sql file listed in upgrading.md fails to execute completely.

Code: Select all

Msg 2627, Level 14, State 1, Line 7
Violation of UNIQUE KEY constraint 'ticket_history_type_name'. Cannot insert duplicate key in object 'dbo.ticket_history_type'. The duplicate key value is (TitleUpdate).
The statement has been terminated.
Msg 3701, Level 11, State 7, Line 10
Cannot drop the index 'dynamic_field_value.index_search_date', because it does not exist or you do not have permission.
Msg 1913, Level 16, State 1, Line 11
The operation failed because an index or statistics with name 'dynamic_field_value_search_date' already exists on table 'dynamic_field_value'.
Msg 4902, Level 16, State 1, Line 3
Cannot find the object "standard_response" because it does not exist or you do not have permissions.
Msg 15225, Level 11, State 1, Procedure sp_rename, Line 338
No item by the name of 'standard_response' could be found in the current database 'OTRS_IT_HD', given that @itemtype was input as '(null)'.
Msg 15225, Level 11, State 1, Procedure sp_rename, Line 338
No item by the name of 'queue_standard_response' could be found in the current database 'OTRS_IT_HD', given that @itemtype was input as '(null)'.
Msg 15225, Level 11, State 1, Procedure sp_rename, Line 338
No item by the name of 'standard_response_attachment' could be found in the current database 'OTRS_IT_HD', given that @itemtype was input as '(null)'.
Msg 2714, Level 16, State 5, Line 2
There is already an object named 'standard_template_name' in the database.
Msg 1750, Level 16, State 0, Line 2
Could not create constraint. See previous errors.
Msg 15248, Level 11, State 1, Procedure sp_rename, Line 215
Either the parameter @objname is ambiguous or the claimed @objtype (COLUMN) is wrong.

(0 row(s) affected)
Msg 15248, Level 11, State 1, Procedure sp_rename, Line 215
Either the parameter @objname is ambiguous or the claimed @objtype (COLUMN) is wrong.

(0 row(s) affected)
Msg 2714, Level 16, State 5, Line 2
There is already an object named 'FK_standard_template_attachment_standard_template_id_id' in the database.
Msg 1750, Level 16, State 0, Line 2
Could not create constraint. See previous errors.
After running the update.pl I am unable to login to the agent interface. logs show successful authentication. Any ideas?
ssanfordph
Znuny newbie
Posts: 8
Joined: 14 Mar 2013, 17:54
Znuny Version: 3.2.3

Re: IIS7 SQL 2008 R2 Upgrade fails 3.2.9->3.3.3

Post by ssanfordph »

Appears to be an issue with OTRS setting the session cookie. If session key is added to browser manually using firebug it functions as normal. Session information is added to the db. Anyone know what would cause this?
ssanfordph
Znuny newbie
Posts: 8
Joined: 14 Mar 2013, 17:54
Znuny Version: 3.2.3

Re: IIS7 SQL 2008 R2 Upgrade fails 3.2.9->3.3.3

Post by ssanfordph »

Did a fresh install, and am encountering the same issue on a brand new site. Has anyone gotten 3.3.x to install correctly on IIS7 and MS SQL? CheckModules returns all good, as does CheckDB. Log's simply show authentication ok, then return to the login screen with no error shown.
[Notice][Kernel::System::Auth::DB::Auth] User: root@localhost authentication ok (Method: sha256, REMOTE_ADDR: x.x.x.x).
alexgleith
Znuny newbie
Posts: 2
Joined: 07 May 2014, 03:18
Znuny Version: 3.3.6
Real Name: Alex Leith
Company: Glenorchy City Council

Re: IIS7 SQL 2008 R2 Upgrade fails 3.2.9->3.3.3

Post by alexgleith »

I'm having the same issue with version 3.3.6 on Windows Server 2008R2, IIS7 connecting to SQL Server 2008R2.

I have the default user, I go to index.pl and log in and there is no error message, it just doesn't start the session properly.

Here's the logs:

Code: Select all

[Wed May  7 11:14:23 2014][Notice][Kernel::System::Auth::DB::Auth] User: root@localhost authentication ok (Method: sha256, REMOTE_ADDR: 127.0.0.1).
[Wed May  7 11:14:23 2014][Debug][Kernel::System::User::Preferences::DB::SetPreferences][61] DB.pm->Do (2) SQL: '
            DELETE FROM user_preferences
            WHERE user_id = ?
                AND preferences_key = ?'
[Wed May  7 11:14:23 2014][Debug][Kernel::System::User::Preferences::DB::SetPreferences][70] DB.pm->Do (3) SQL: '
            INSERT INTO user_preferences
            (user_id, preferences_key, preferences_value)
            VALUES (?, ?, ?)'
[Wed May  7 11:14:24 2014][Debug][Kernel::System::AuthSession::DB::CreateSessionID][309] DB.pm->Do (4) SQL: '
            DELETE FROM sessions
            WHERE session_id = ?'
[Wed May  7 11:14:24 2014][Debug][Kernel::System::AuthSession::DB::CreateSessionID][326] DB.pm->Do (5) SQL: 'INSERT INTO sessions (session_id, data_key, data_value, serialized) VALUES (?,?,?,?)'
[Wed May  7 11:14:24 2014][Debug][Kernel::System::AuthSession::DB::CreateSessionID][326] DB.pm->Do (6) SQL: 'INSERT INTO sessions (session_id, data_key, data_value, serialized) VALUES (?,?,?,?)'
[Wed May  7 11:14:24 2014][Debug][Kernel::System::AuthSession::DB::CreateSessionID][326] DB.pm->Do (7) SQL: 'INSERT INTO sessions (session_id, data_key, data_value, serialized) VALUES (?,?,?,?)'
[Wed May  7 11:14:24 2014][Debug][Kernel::System::AuthSession::DB::CreateSessionID][326] DB.pm->Do (8) SQL: 'INSERT INTO sessions (session_id, data_key, data_value, serialized) VALUES (?,?,?,?)'
[Wed May  7 11:14:24 2014][Debug][Kernel::System::AuthSession::DB::CreateSessionID][326] DB.pm->Do (9) SQL: 'INSERT INTO sessions (session_id, data_key, data_value, serialized) VALUES (?,?,?,?)'
[Wed May  7 11:14:24 2014][Debug][Kernel::System::AuthSession::DB::CreateSessionID][326] DB.pm->Do (10) SQL: 'INSERT INTO sessions (session_id, data_key, data_value, serialized) VALUES (?,?,?,?)'
[Wed May  7 11:14:24 2014][Debug][Kernel::System::AuthSession::DB::CreateSessionID][326] DB.pm->Do (11) SQL: 'INSERT INTO sessions (session_id, data_key, data_value, serialized) VALUES (?,?,?,?)'
[Wed May  7 11:14:24 2014][Debug][Kernel::System::AuthSession::DB::CreateSessionID][326] DB.pm->Do (12) SQL: 'INSERT INTO sessions (session_id, data_key, data_value, serialized) VALUES (?,?,?,?)'
[Wed May  7 11:14:24 2014][Debug][Kernel::System::AuthSession::DB::CreateSessionID][326] DB.pm->Do (13) SQL: 'INSERT INTO sessions (session_id, data_key, data_value, serialized) VALUES (?,?,?,?)'
[Wed May  7 11:14:24 2014][Debug][Kernel::System::AuthSession::DB::CreateSessionID][326] DB.pm->Do (14) SQL: 'INSERT INTO sessions (session_id, data_key, data_value, serialized) VALUES (?,?,?,?)'
[Wed May  7 11:14:24 2014][Debug][Kernel::System::AuthSession::DB::CreateSessionID][326] DB.pm->Do (15) SQL: 'INSERT INTO sessions (session_id, data_key, data_value, serialized) VALUES (?,?,?,?)'
[Wed May  7 11:14:24 2014][Debug][Kernel::System::AuthSession::DB::CreateSessionID][326] DB.pm->Do (16) SQL: 'INSERT INTO sessions (session_id, data_key, data_value, serialized) VALUES (?,?,?,?)'
[Wed May  7 11:14:24 2014][Debug][Kernel::System::AuthSession::DB::CreateSessionID][326] DB.pm->Do (17) SQL: 'INSERT INTO sessions (session_id, data_key, data_value, serialized) VALUES (?,?,?,?)'
[Wed May  7 11:14:24 2014][Debug][Kernel::System::AuthSession::DB::CreateSessionID][326] DB.pm->Do (18) SQL: 'INSERT INTO sessions (session_id, data_key, data_value, serialized) VALUES (?,?,?,?)'
[Wed May  7 11:14:24 2014][Debug][Kernel::System::AuthSession::DB::CreateSessionID][326] DB.pm->Do (19) SQL: 'INSERT INTO sessions (session_id, data_key, data_value, serialized) VALUES (?,?,?,?)'
[Wed May  7 11:14:24 2014][Debug][Kernel::System::AuthSession::DB::CreateSessionID][326] DB.pm->Do (20) SQL: 'INSERT INTO sessions (session_id, data_key, data_value, serialized) VALUES (?,?,?,?)'
[Wed May  7 11:14:24 2014][Debug][Kernel::System::AuthSession::DB::CreateSessionID][326] DB.pm->Do (21) SQL: 'INSERT INTO sessions (session_id, data_key, data_value, serialized) VALUES (?,?,?,?)'
[Wed May  7 11:14:24 2014][Debug][Kernel::System::AuthSession::DB::CreateSessionID][326] DB.pm->Do (22) SQL: 'INSERT INTO sessions (session_id, data_key, data_value, serialized) VALUES (?,?,?,?)'
[Wed May  7 11:14:24 2014][Debug][Kernel::System::AuthSession::DB::CreateSessionID][326] DB.pm->Do (23) SQL: 'INSERT INTO sessions (session_id, data_key, data_value, serialized) VALUES (?,?,?,?)'
[Wed May  7 11:14:24 2014][Debug][Kernel::System::AuthSession::DB::CreateSessionID][326] DB.pm->Do (24) SQL: 'INSERT INTO sessions (session_id, data_key, data_value, serialized) VALUES (?,?,?,?)'
[Wed May  7 11:14:24 2014][Debug][Kernel::System::AuthSession::DB::CreateSessionID][326] DB.pm->Do (25) SQL: 'INSERT INTO sessions (session_id, data_key, data_value, serialized) VALUES (?,?,?,?)'
[Wed May  7 11:14:24 2014][Debug][Kernel::System::AuthSession::DB::CreateSessionID][326] DB.pm->Do (26) SQL: 'INSERT INTO sessions (session_id, data_key, data_value, serialized) VALUES (?,?,?,?)'
[Wed May  7 11:14:24 2014][Debug][Kernel::System::AuthSession::DB::CreateSessionID][326] DB.pm->Do (27) SQL: 'INSERT INTO sessions (session_id, data_key, data_value, serialized) VALUES (?,?,?,?)'
[Wed May  7 11:14:24 2014][Debug][Kernel::System::AuthSession::DB::CreateSessionID][326] DB.pm->Do (28) SQL: 'INSERT INTO sessions (session_id, data_key, data_value, serialized) VALUES (?,?,?,?)'
[Wed May  7 11:14:24 2014][Debug][Kernel::System::AuthSession::DB::CreateSessionID][326] DB.pm->Do (29) SQL: 'INSERT INTO sessions (session_id, data_key, data_value, serialized) VALUES (?,?,?,?)'
[Wed May  7 11:14:24 2014][Debug][Kernel::System::AuthSession::DB::CreateSessionID][326] DB.pm->Do (30) SQL: 'INSERT INTO sessions (session_id, data_key, data_value, serialized) VALUES (?,?,?,?)'
[Wed May  7 11:14:24 2014][Debug][Kernel::System::AuthSession::DB::CreateSessionID][326] DB.pm->Do (31) SQL: 'INSERT INTO sessions (session_id, data_key, data_value, serialized) VALUES (?,?,?,?)'
[Wed May  7 11:14:24 2014][Debug][Kernel::System::AuthSession::DB::CreateSessionID][326] DB.pm->Do (32) SQL: 'INSERT INTO sessions (session_id, data_key, data_value, serialized) VALUES (?,?,?,?)'
[Wed May  7 11:14:24 2014][Debug][Kernel::System::AuthSession::DB::CreateSessionID][326] DB.pm->Do (33) SQL: 'INSERT INTO sessions (session_id, data_key, data_value, serialized) VALUES (?,?,?,?)'
[Wed May  7 11:14:24 2014][Debug][Kernel::System::AuthSession::DB::CreateSessionID][326] DB.pm->Do (34) SQL: 'INSERT INTO sessions (session_id, data_key, data_value, serialized) VALUES (?,?,?,?)'
[Wed May  7 11:14:24 2014][Debug][Kernel::System::AuthSession::DB::CreateSessionID][326] DB.pm->Do (35) SQL: 'INSERT INTO sessions (session_id, data_key, data_value, serialized) VALUES (?,?,?,?)'
[Wed May  7 11:14:24 2014][Debug][Kernel::System::AuthSession::DB::CreateSessionID][326] DB.pm->Do (36) SQL: 'INSERT INTO sessions (session_id, data_key, data_value, serialized) VALUES (?,?,?,?)'
[Wed May  7 11:14:24 2014][Debug][Kernel::System::AuthSession::DB::CreateSessionID][326] DB.pm->Do (37) SQL: 'INSERT INTO sessions (session_id, data_key, data_value, serialized) VALUES (?,?,?,?)'
[Wed May  7 11:14:24 2014][Debug][Kernel::System::AuthSession::DB::CreateSessionID][326] DB.pm->Do (38) SQL: 'INSERT INTO sessions (session_id, data_key, data_value, serialized) VALUES (?,?,?,?)'
[Wed May  7 11:14:24 2014][Debug][Kernel::System::AuthSession::DB::CreateSessionID][326] DB.pm->Do (39) SQL: 'INSERT INTO sessions (session_id, data_key, data_value, serialized) VALUES (?,?,?,?)'
[Wed May  7 11:14:24 2014][Debug][Kernel::System::AuthSession::DB::CreateSessionID][326] DB.pm->Do (40) SQL: 'INSERT INTO sessions (session_id, data_key, data_value, serialized) VALUES (?,?,?,?)'
[Wed May  7 11:14:24 2014][Debug][Kernel::System::AuthSession::DB::CreateSessionID][326] DB.pm->Do (41) SQL: 'INSERT INTO sessions (session_id, data_key, data_value, serialized) VALUES (?,?,?,?)'
[Wed May  7 11:14:24 2014][Debug][Kernel::System::AuthSession::DB::CreateSessionID][326] DB.pm->Do (42) SQL: 'INSERT INTO sessions (session_id, data_key, data_value, serialized) VALUES (?,?,?,?)'
[Wed May  7 11:14:24 2014][Debug][Kernel::System::AuthSession::DB::CreateSessionID][326] DB.pm->Do (43) SQL: 'INSERT INTO sessions (session_id, data_key, data_value, serialized) VALUES (?,?,?,?)'
[Wed May  7 11:14:24 2014][Debug][Kernel::System::Web::InterfaceAgent::DESTROY][920] Global handle stopped.
[Wed May  7 11:14:25 2014][Debug][Kernel::System::Web::InterfaceAgent::new][84] Global handle started...
[Wed May  7 11:14:25 2014][Debug][Kernel::System::DB::Connect][268] DB.pm->Do (1) SQL: 'SET DATEFORMAT ymd'
[Wed May  7 11:14:26 2014][Debug][Kernel::System::Web::InterfaceAgent::DESTROY][920] Global handle stopped.
alexgleith
Znuny newbie
Posts: 2
Joined: 07 May 2014, 03:18
Znuny Version: 3.3.6
Real Name: Alex Leith
Company: Glenorchy City Council

Re: IIS7 SQL 2008 R2 Upgrade fails 3.2.9->3.3.3

Post by alexgleith »

I found the issue. It's something to do with redirects.

The fix is to change line 991 of C:\otrs\OTRS\Kernel\Output\HTML\Layout.pm from 6 to 7 seems to fix.

This is the section:

Code: Select all

# check if IIS 6 is used, add absolute url for IIS workaround
    # see also:
    #  o http://bugs.otrs.org/show_bug.cgi?id=2230
    #  o http://bugs.otrs.org/show_bug.cgi?id=9835
    #  o http://support.microsoft.com/default.aspx?scid=kb;en-us;221154
    if ( $ENV{SERVER_SOFTWARE} =~ /^microsoft\-iis\/7/i ) {
        my $Host = $ENV{HTTP_HOST} || $Self->{ConfigObject}->Get('FQDN');
        my $HttpType = $Self->{ConfigObject}->Get('HttpType');
        $Param{Redirect} = $HttpType . '://' . $Host . $Param{Redirect};
    }
    my $Output = $Cookies . $Self->Output( TemplateFile => 'Redirect', Data => \%Param );
Teskor
Znuny newbie
Posts: 2
Joined: 28 Apr 2014, 16:47
Znuny Version: 3.3.6
Real Name: Teskor
Company: MT AB

Re: IIS7 SQL 2008 R2 Upgrade fails 3.2.9->3.3.3

Post by Teskor »

alexgleith wrote:I found the issue. It's something to do with redirects.

The fix is to change line 991 of C:\otrs\OTRS\Kernel\Output\HTML\Layout.pm from 6 to 7 seems to fix.

This is the section:

Code: Select all

# check if IIS 6 is used, add absolute url for IIS workaround
    # see also:
    #  o http://bugs.otrs.org/show_bug.cgi?id=2230
    #  o http://bugs.otrs.org/show_bug.cgi?id=9835
    #  o http://support.microsoft.com/default.aspx?scid=kb;en-us;221154
    if ( $ENV{SERVER_SOFTWARE} =~ /^microsoft\-iis\/7/i ) {
        my $Host = $ENV{HTTP_HOST} || $Self->{ConfigObject}->Get('FQDN');
        my $HttpType = $Self->{ConfigObject}->Get('HttpType');
        $Param{Redirect} = $HttpType . '://' . $Host . $Param{Redirect};
    }
    my $Output = $Cookies . $Self->Output( TemplateFile => 'Redirect', Data => \%Param );

Great!! This solved my issue with OTRS. Thank you for sharing the solution! :D
OTRS 3.3.7 with bundled MySQL, IIS 7, Windows 2008 r2 x64.
ckilmer75
Znuny newbie
Posts: 4
Joined: 07 Mar 2014, 17:03
Znuny Version: 3.3.5
Real Name: Chris Kilmer

Re: IIS7 SQL 2008 R2 Upgrade fails 3.2.9->3.3.3

Post by ckilmer75 »

Hi Thanks for this fix!!! Just an fyi, if anyone reading this is using IIS 8 rather than 7, then change the # mentioned to an 8. This worked for me as I am using IIS 8.
rodolphoramos
Znuny newbie
Posts: 8
Joined: 19 Aug 2013, 14:21
Znuny Version: 3.3.5
Real Name: Rodolpho Ramos
Company: Ministerio Publico do Rio Grande do Norte

Re: IIS7 SQL 2008 R2 Upgrade fails 3.2.9->3.3.3

Post by rodolphoramos »

Greats, this work here too. Thanks a lot!
OTRS 3.3.7
Windows Server 2008 R2
MySQL
IIS 7.5
m9445
Znuny newbie
Posts: 1
Joined: 26 Jun 2014, 13:16
Znuny Version: 3.3.7
Real Name: Max

Re: IIS7 SQL 2008 R2 Upgrade fails 3.2.9->3.3.3

Post by m9445 »

Ooh, Thanks!
Two days I was sitting on it...
I confirm that the scheme works.
Windows Server 2008 + ISS 7 +MSSQL
Post Reply