OTRS DB Insert New Queue Fail

[shaunnesbittnz]

Hey everyone,

I'm using the latest version of OTRS (3.0.5) and installed it using the windows installer.
Since than I've updated the configuration so that it connects to a MSSQL server using the following connection string.

$Self->{DatabaseDSN} = "DBI:ODBC:driver={SQL Server};Server=****, ****;database=****;uid=****;pwd=****;";
$Self->{"Database::Type"} = 'mssql';

Everything is working fantastically, I updated it to use ActiveState Perl instead of Strawberry perl as I find it runs much quicker. The only issue I've come across is when trying to add a new queue to the system (doesn't matter if i alternate between the different perl options).

[Microsoft][ODBC SQL Server Driver]Invalid character value for cast specification (SQL-22018), SQL: 'INSERT INTO queue (name, group_id, unlock_timeout, system_address_id, calendar_name, default_sign_key, salutation_id, signature_id, first_response_time, first_response_notify, update_time, update_notify, solution_time, solution_notify, follow_up_id, follow_up_lock, valid_id, comments, create_time, create_by, change_time, change_by) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, current_timestamp, ?, current_timestamp, ?)'

If I run an insert SQL query, either using the SQL statement option in OTRS or via the database, it works fine. My assumption is it's adding an invalid character into one of the INT fields but I'm not sure.

Any help would be much appreciated.

[pseudo]

Hi,

we have a similar Problem, all works but adding a new queue is'nt possible:

Log DB-Server:
<otrs 2011-01-27 07:37:29 CET xxx(60494) otrs>FEHLER: ungültige Eingabesyntax für ganze Zahl: »«
<otrs 2011-01-27 07:37:29 CET xxx(60494) otrs>ANWEISUNG: INSERT INTO queue (name, group_id, unlock_timeout, system_address_id, calendar_name, default_sign_key, salutation_id, signature_id, first_response_time, first_response_notify, update_time, update_notify, solution_time, solution_notify, follow_up_id, follow_up_lock, valid_id, comments, create_time, create_by, change_time, change_by) VALUES ($1, $2, $3, $4, $5, $6, $7, $8, $9, $10, $11, $12, $13, $14, $15, $16, $17, $18, current_timestamp, $19, current_timestamp, $20)

DB-Server: SLES11SP1 with PostgreSQL 8.3.13
Webserver: SLES11SP1 with OTRS 3.0.5

Is there any solution?

Thx pseudo

[jojo]

is this a fresh setup? Were the correct installation scripts for this database used?

[pseudo]

Yes, it is a fresh setup.

I created a new DB-User (otrs) and a new Database with otrs as owner.

In the next step I execute the following Scripts on the new Database otrs as DB-User otrs:
1.) /opt/otrs/scripts/database/otrs-schema.postgresql.sql
2.) /opt/otrs/scripts/database/otrs-initial_insert.postgresql.sql
3.) /opt/otrs/scripts/database/otrs-schema-post.postgresql.sql

I thougt, that this are the correct scripts for my DB-Backend (PostgreSQL 8.3.13)

Any idea?

Thx pseudo

[pseudo]

... we no make a test with OTRS 3.0.4 with same settings:

adding a new queue there works!

It seems, that in version 3.0.5 is something different/wrong!?

[jojo]

it seems that this a (known) bug with postgresql at the moment...

[RBehr]

I am encountering the same error on 3.0.5 using MS SQL. Is there any workaround in place yet?

[jojo]

no not yet. Can you please create a bug report on this MS SQL Topic?

[RBehr]

Will do.

We upgraded to 3.0.5 to integrate OTRS::ITSM. Does OTRS::ITSM integrate with 3.0.4 (the info on the site says it does not, but I just want to check).

[jojo]

ITSM is still beta and needs some of the fixes done in 3.0.5

[RBehr]

Have raised Bugzilla Bug 6811 re the SQL failure.

[keithf4]

Just got OTRS set up this week to evaluate and thought I'd done something wrong in my setup! Found the bug on the tracker and will follow and test any patches posted since I'm only running a test instance.

Ubuntu 10.04.1 LTS
PostgreSQL: 8.4.3-1
Perl: 5.10.1-8ubuntu2
OTRS 3.0.5

Hope this gets fixed soon!

[keithf4]

Don't have a bug tracker account, so just replying here that the patch posted in

http://bugs.otrs.org/show_bug.cgi?id=6837

works for me as well. Thanks for the quick turn-around!