Need TicketID ticket.valid_id issue OTRS 4.0.8-02

PriteshP23 · Post by **PriteshP23** » 18 Jun 2015, 15:26

Hello,

I follow all the steps from https://otrs.github.io/doc/manual/admin ... ading.html

I have doubt for step 6.1 and 6.2
shell> cat scripts/DBUpdate-to-4.postgresql.sql | psql --set ON_ERROR_STOP=on --single-transaction otrs otrs
Step 6.2: Database migration script
Run the migration script (as user otrs, NOT as root):
shell> scripts/DBUpdate-to-4.pl

After update, i can't creat any ticket and there is error:

Code: Select all

Need TicketNumber!
Need TicketID!
Need ObjectID!
ERROR:  null value in column "valid_id" violates not-null constraint

How to solve issue of "valid_id" violates not-null constraint ??

Thanks !

Post by **crythias** » 18 Jun 2015, 16:17

It means *something* (I don't know what) -- a custom state, priority, etc., is being requested (maybe it was renamed) and it might find it in a table but the valid_id column is null for that entry.

How to fix? Well, it's likely something custom. Maybe it was added manually to the database. In any case, what you will need to do is find out that thing
select * from (table) where valid_id IS NULL.

PriteshP23 · Post by **PriteshP23** » 18 Jun 2015, 16:53

@crythias
Thanks for quick reply.

How to fix? Well, it's likely something custom. Maybe it was added manually to the database. In any case, what you will need to do is find out that thing
select * from (table) where valid_id IS NULL.

I can confirm that there was never customization in otrs database from my side. I only change ticket state from interface after update to put status close successfully when we reply. Nothing else.

I found it to repair/missing table and i execute 3 files

Code: Select all

otrs < scripts/database/otrs-schema.mysql.sql
otrs < scripts/database/otrs-initial_insert.mysql.sql
otrs < scripts/database/otrs-schema-post.mysql.sql

FROM this link https://github.com/mbeijen/otrs/blob/ma ... atabase.md

NOW, The biggest problem is there is no valid_id in ticket table. I don't know what to do next.

Thanks.

Post by **crythias** » 18 Jun 2015, 17:14

PriteshP23 wrote:The biggest problem is there is no valid_id in ticket table. I don't know what to do next.

That's because all tickets are *valid*, so it doesn't make any sense to invalidate a ticket.

Things that would have a valid_id would be attributes that can be set as valid or invalid. This runs the gammut and is impossible to list all of them, but you can brows scripts/database/otrs-schema.mysql.sql to find which tables are most likely to be the thing you're looking for.

Also, when I say custom, I mean that you've created an entry (INSERT?) that does not have a valid_id.

OK, maybe not. but there is a better log report that should indicate where the error message occurs.

PriteshP23 · Post by **PriteshP23** » 18 Jun 2015, 17:54

Also, when I say custom, I mean that you've created an entry (INSERT?) that does not have a valid_id.

i didn't do any INSERT.

What i found from log before:

Code: Select all

Need TicketID!
Need TicketNumber!
Need TicketID!
Need ObjectID!
ERROR:  null value in column "valid_id" violates not-null constraint
No State for open found!

The thing is now i can create the ticket but there is no column valid_id in ticket table. What i should to solve the problem?

I am sorry its first time i am doing OTRS Update. Thats why in my previous post i asked for common issue for update.

How to add valid_id column in ticket table such a way that includes all foreign key and constraint with other tables in database?

Thanks !

EXG133 · Post by **EXG133** » 18 Jun 2015, 18:07

"No State for open found!"

Can you find the state 'open' in the SysConfig?

PriteshP23 · Post by **PriteshP23** » 18 Jun 2015, 18:21

Can you find the state 'open' in the SysConfig?

Yes, i can find state Open here:

Ticket::Frontend::AgentTicketEmail###StateType

I just modified " Ticket::Frontend::AgentTicketEmail###StateDefault => successfully closed ". Thats it. Nothing more.

The problem seems in otrs database schema. How to debug it in a way that can assure it has all tables of OTRS 4 without any loss of data and database tables.

jojo · Post by **jojo** » 19 Jun 2015, 00:54

the Database migration script to OTRS 4 contains "ALTER TABLE ticket DROP valid_id;“. So I assume you did not perform a successful migration. You need expert consulting to operate OTRS!

PriteshP23 · Post by **PriteshP23** » 19 Jun 2015, 09:55

the Database migration script to OTRS 4 contains "ALTER TABLE ticket DROP valid_id;“.

I commented it in .sql file in order to create ticket. So, i need to enable it ? But in this case, i will not able to create ticket anymore. How to solve it ?

Post by **crythias** » 19 Jun 2015, 13:33

Previously, ticket.valid_id was used and the schema set as NOT NULL. Now it is unused/unset in the code. The conversion SQL file was supposed to drop the valid_id column. Apparently it did not get dropped during running the dbupdate sql that you did run on upgrade and it completed without errors, right?

PriteshP23 · Post by **PriteshP23** » 19 Jun 2015, 15:25

@crythias

Yes, how to solve this issue because other steps went well during update process. Its an issue for good .sql file to run in right order to do update database. I believe that i used wrong sql file or misorder it.
What i did so far,

STEP 1: update as per guide

Code: Select all

cat scripts/DBUpdate-to-4.postgresql.sql | psql --set ON_ERROR_STOP=on --single-transaction otrs otrs
scripts/DBUpdate-to-4.pl

issue after step 1: Not able to create ticket
Error log:

Code: Select all

Need TicketNumber!
Need TicketID!
Need ObjectID!
ERROR:  null value in column "valid_id" violates not-null constraint

------------------------------------------------------------------------------------------------------------------------------------
STEP 2:
To execute these scripts to create ticket

Code: Select all

psql otrs < scripts/database/otrs-schema.postgresql.sql
psql otrs < scripts/database/otrs-initial_insert.postgresql.sql
psql otrs < scripts/database/otrs-schema-post.postgresql.sql

SAME issue after step 2: Not able to create ticket

Code: Select all

Error log:
Need TicketNumber!
Need TicketID!
Need ObjectID!
ERROR:  null value in column "valid_id" violates not-null constraint

------------------------------------------------------------------------------------------------------------------------------------
STEP 3:
I did this sql to create ticket:

Code: Select all

ALTER TABLE ticket DROP valid_id cascade

Issue Solved to create ticket BUT there is no valid_id in ticket table, which is not normal. So, its a big problem.
Next ??

I mean to make sure all tables with all joins in database without any loss of data.

Post by **crythias** » 19 Jun 2015, 16:17

PriteshP23 wrote:Issue Solved to create ticket BUT there is no valid_id in ticket table, which is not normal. So, its a big problem.
Next ??

I mean to make sure all tables with all joins in database without any loss of data.

Next... ??? 4. Profit.

You keep missing the point that's been told you no less than three times that the upgrade of the database drops valid_id as a column in ticket, so it should not be there. That *is* normal, and it is not a problem.

Post by **crythias** » 19 Jun 2015, 16:20

jojo wrote:the Database migration script to OTRS 4 contains "ALTER TABLE ticket DROP valid_id;“.

crythias wrote:The conversion SQL file was supposed to drop the valid_id column

PriteshP23 · Post by **PriteshP23** » 19 Jun 2015, 16:40

That *is* normal, and it is not a problem.

I am sorry again but in this case how i can import tickets from otrs 3.3.6 ?

Post by **crythias** » 19 Jun 2015, 17:52

PriteshP23 wrote:I am sorry again but in this case how i can import tickets from otrs 3.3.6 ?

http://otrs.github.io/doc/manual/admin/ ... ading.html
Follow every step. Don't tell me you've already done this, successfully, or else you wouldn't be having this problem.

PriteshP23 · Post by **PriteshP23** » 19 Jun 2015, 18:09

Follow every step.

Both i need to run as "otrs" user, right ?

Code: Select all

step 6.1
cat scripts/DBUpdate-to-4.postgresql.sql | psql --set ON_ERROR_STOP=on --single-transaction otrs otrs
step 6.2
scripts/DBUpdate-to-4.pl

What is the difference ?

Many thanks.

Post by **crythias** » 19 Jun 2015, 18:27

PriteshP23 wrote:Both i need to run as "otrs" user, right ?

I would assume so. I don't have adequate background in postgresql to address the .sql part.
I would also suggest you might look at the .sql file and the .pl file and get a feel for what the difference is rather than ask here to explain it. One will be a straight schema change and the other would be whatever logic manipulation to do with the data after that schema change. Or whatever it says in the tin.

PriteshP23 · Post by **PriteshP23** » 23 Jun 2015, 15:01

One will be a straight schema change and the other would be whatever logic manipulation to do with the data after that schema change.

Code: Select all

Step 6.1
shell> cat scripts/DBUpdate-to-4.postgresql.sql | psql --set ON_ERROR_STOP=on --single-transaction otrs otrs

I need to run as "otrs" user ans last otrs is password ? If we don't write password and execute, it will work ?
Is there any notification message after this step to make sure that it went well and we can go for step 6.2?

It is written well that i need to execute Step 6.2 as "otrs" user, not root. So, its ok.

Thanks again!

Post by **crythias** » 23 Jun 2015, 15:37

I'm not able to answer your question besides to say this: your interaction with postgresql at this point would be better answered on a site or by a dbadmin that handles postgresql. This is not directly a question that should necessarily be answered by OTRS users. In my thoughts, I'd be trying to run as root, but maybe I'd just su otrs and run it that way. I don't know. But your best bet would be to back up your database, try the command, and find out if it did what it was supposed to do. One hint would be that the valid_id table is now gone from tickets.

PriteshP23 · Post by **PriteshP23** » 25 Jun 2015, 14:44

In my thoughts, I'd be trying to run as root, but maybe I'd just su otrs and run it that way. I don't know. But your best bet would be to back up your database, try the command, and find out if it did what it was supposed to do.

Well, it seems i need to run both script as "otrs" user.

here is why: http://lists.otrs.org/pipermail/otrs/20 ... 24668.html

What you think ?

Post by **crythias** » 25 Jun 2015, 15:18

crythias wrote:your best bet would be to back up your database, try the command, and find out if it did what it was supposed to do. One hint would be that the valid_id table is now gone from tickets.

PriteshP23 · Post by **PriteshP23** » 29 Jun 2015, 10:50

Help !!
I got error at step 6.1 !

Code: Select all

-bash-4.1$ cat scripts/DBUpdate-to-4.postgresql.sql | psql --set ON_ERROR_STOP=on --single-transaction otrs otrs
SET
ERROR:  must be owner of relation dynamic_field_value_field_values

Post by **crythias** » 29 Jun 2015, 13:11

What did a Web search tell you?

PriteshP23 · Post by **PriteshP23** » 29 Jun 2015, 14:07

Problem of rights ! I assign otrs as superuser ! So, i could execute it BUT got error:

Code: Select all

bash-4.1$ cat /opt/otrs/scripts/DBUpdate-to-4.postgresql.sql | psql --set ON_ERROR_STOP=on --single-transaction -U otrs otrs
SET
DROP INDEX
CREATE INDEX
ALTER TABLE
ALTER TABLE
ERROR:  constraint "fk_ticket_valid_id_id" of relation "ticket" does not exist

Thanks !

Post by **crythias** » 29 Jun 2015, 14:29

It seems you're running this multiple times against the same database. You should consult a database expert.

PriteshP23 · Post by **PriteshP23** » 29 Jun 2015, 14:40

Yes, it could possible that i run more than one time.

So, i did Step 6.2 and got this

Code: Select all

-bash-4.1$ perl DBUpdate-to-4.pl

Migration started...

Step 1 of 15: Refresh configuration cache... 
If you see warnings about 'Subroutine Load redefined', that's fine, no need to worry!
done.

Step 2 of 15: Check framework version... [Mon Jun 29 14:34:30 2015] DBUpdate-to-4.pl: Subroutine Load redefined at /opt/otrs/Kernel/Config/Files/ZZZAuto.pm line 7.
done.

Step 3 of 15: Migrate FontAwesome icons... ERROR: OTRS-DBUpdate-to-4.pl-98 Perl: 5.10.1 OS: linux Time: Mon Jun 29 14:34:30 2015

 Message: Can't write /opt/otrs/Kernel/Config/Files/ZZZAuto.pm: 

 Traceback (4783): 
   Module: Kernel::System::SysConfig::ConfigItemUpdate Line: 398
   Module: main::_MigrateFontAwesome Line: 384
   Module: DBUpdate-to-4.pl Line: 111

ERROR: OTRS-DBUpdate-to-4.pl-98 Perl: 5.10.1 OS: linux Time: Mon Jun 29 14:34:30 2015

 Message: Can't write /opt/otrs/Kernel/Config/Files/ZZZAuto.pm: Permission denied

 Traceback (4783): 
   Module: Kernel::System::SysConfig::ConfigItemUpdate Line: 398
   Module: main::_MigrateFontAwesome Line: 384
   Module: DBUpdate-to-4.pl Line: 111

ERROR: OTRS-DBUpdate-to-4.pl-98 Perl: 5.10.1 OS: linux Time: Mon Jun 29 14:34:30 2015

 Message: Can't write /opt/otrs/Kernel/Config/Files/ZZZAuto.pm: Permission denied

 Traceback (4783): 
   Module: Kernel::System::SysConfig::ConfigItemUpdate Line: 398
   Module: main::_MigrateFontAwesome Line: 384
   Module: DBUpdate-to-4.pl Line: 111

ERROR: OTRS-DBUpdate-to-4.pl-98 Perl: 5.10.1 OS: linux Time: Mon Jun 29 14:34:30 2015

 Message: Can't write /opt/otrs/Kernel/Config/Files/ZZZAuto.pm: Permission denied

 Traceback (4783): 
   Module: Kernel::System::SysConfig::ConfigItemUpdate Line: 398
   Module: main::_MigrateFontAwesome Line: 436
   Module: DBUpdate-to-4.pl Line: 111

ERROR: OTRS-DBUpdate-to-4.pl-98 Perl: 5.10.1 OS: linux Time: Mon Jun 29 14:34:30 2015

 Message: Can't write /opt/otrs/Kernel/Config/Files/ZZZAuto.pm: Permission denied

 Traceback (4783): 
   Module: Kernel::System::SysConfig::ConfigItemUpdate Line: 398
   Module: main::_MigrateFontAwesome Line: 436
   Module: DBUpdate-to-4.pl Line: 111

done.

Step 4 of 15: Migrate ProcessManagement EntityIDs... done.

Step 5 of 15: Migrate ProcessManagement Dynamic Field Types... done.

Step 6 of 15: Migrate ACLs stored in the DB... done.

Step 7 of 15: Set service notifications... [Mon Jun 29 14:34:30 2015] DBUpdate-to-4.pl: DBD::Pg::db do failed: ERROR:  duplicate key value violates unique constraint "notifications_pkey"
[Mon Jun 29 14:34:30 2015] DBUpdate-to-4.pl: DETAIL:  Key (id)=(1) already exists. at /opt/otrs/Kernel/System/DB.pm line 442.
ERROR: OTRS-DBUpdate-to-4.pl-98 Perl: 5.10.1 OS: linux Time: Mon Jun 29 14:34:30 2015

 Message: ERROR:  duplicate key value violates unique constraint "notifications_pkey"
DETAIL:  Key (id)=(1) already exists., SQL: '
                INSERT INTO notifications (notification_type, notification_language,
                    subject, text, notification_charset, content_type, create_time, create_by,
                    change_time, change_by)
                VALUES ( ?, ?, ?, ?, 'utf-8', 'text/plain', 
        '2015-06-29 14:34:30'
    , 1,
                    
        '2015-06-29 14:34:30'
    , 1 )'

 Traceback (4783): 
   Module: main::_SetServiceNotifications Line: 1259
   Module: DBUpdate-to-4.pl Line: 154

error.

[Mon Jun 29 14:34:30 2015] DBUpdate-to-4.pl: Died at DBUpdate-to-4.pl line 159.

jojo · Post by **jojo** » 29 Jun 2015, 14:42

revert to 3.3. and try again.

But as explained several times here and on Facebook you need to get professional services.

PriteshP23 · Post by **PriteshP23** » 29 Jun 2015, 14:45

I am doing it again only. It seems i need to set permission like this:

Code: Select all

-rw-rw---- 1 otrs apache  12528 Jun 29 14:30 ZZZAuto.pm

After above command it wroks well till 6th step

but there is a problem in 7th step

duplicate key value violates unique constraint "notifications_pkey"

PriteshP23 · Post by **PriteshP23** » 30 Jun 2015, 16:20

@jojo:

revert to 3.3. and try again.

Finally, it works after putting comments this two lines:

Code: Select all

--ALTER TABLE ticket DROP CONSTRAINT FK_ticket_valid_id_id;
--ALTER TABLE ticket_history DROP CONSTRAINT FK_ticket_history_valid_id_id;

Output DBUpdate-to-4.pl:

Code: Select all

$ perl DBUpdate-to-4.plMigration started...
Step 1 of 15: Refresh configuration cache...
If you see warnings about 'Subroutine Load redefined', that's fine, no need to worry!
done.
Step 2 of 15: Check framework version... [Mon Jun 29 15:31:11 2015] DBUpdate-to-4.pl: Subroutine Load redefined at /opt/otrs/Kernel/Config/Files/ZZZAuto.pm line 7.
done.
Step 3 of 15: Migrate FontAwesome icons... done.
Step 4 of 15: Migrate ProcessManagement EntityIDs... done.
Step 5 of 15: Migrate ProcessManagement Dynamic Field Types... done.
Step 6 of 15: Migrate ACLs stored in the DB... done.
Step 7 of 15: Set service notifications... done.
Step 8 of 15: Migrate Settings... done.
Step 9 of 15: Uninstall Merged Feature Add-Ons... done.
Step 10 of 15: Migrate SysConfig settings from DT to Template::Toolkit... done.
Step 11 of 15: Migrate Dynamic Field links from DT to Template::Toolkit... done.
Step 12 of 15: Migrate Generic Agent Process Management Dynamic Fields ... done.
Step 13 of 15: Migrate Notification (Event) Process Management Dynamic Fields ... done.
Step 14 of 15: Clean up the cache... done.
Step 15 of 15: Refresh configuration cache another time...
If you see warnings about 'Subroutine Load redefined', that's fine, no need to worry!
done.Migration completed!

There is no valid_id in ticket table, so that is good sign !
So, I would like to assure that how we can verify technically if update otrs 4 is went well or not ? I want to verify now because I would like to avoid problems in future.

Thank you all.

jojo · Post by **jojo** » 30 Jun 2015, 18:06

changing working scripts from the vendor to avoid errors is not a solution.

PriteshP23 · Post by **PriteshP23** » 02 Jul 2015, 16:35

It seems these lines were executed during the update. I agree that i should not change script but i got same error at step 6.1 for two times while updating.

It works fine till now but not sure for a real solution yet.

Thanks you all.

Znuny Open Source Ticketsystem

Need TicketID ticket.valid_id issue OTRS 4.0.8-02

Need TicketID ticket.valid_id issue OTRS 4.0.8-02

Re: Need TicketID ticket.valid_id issue OTRS 4.0.8-02

Re: Need TicketID ticket.valid_id issue OTRS 4.0.8-02

Re: Need TicketID ticket.valid_id issue OTRS 4.0.8-02

Re: Need TicketID ticket.valid_id issue OTRS 4.0.8-02

Re: Need TicketID ticket.valid_id issue OTRS 4.0.8-02

Re: Need TicketID ticket.valid_id issue OTRS 4.0.8-02

Re: Need TicketID ticket.valid_id issue OTRS 4.0.8-02

Re: Need TicketID ticket.valid_id issue OTRS 4.0.8-02

Re: Need TicketID ticket.valid_id issue OTRS 4.0.8-02

Re: Need TicketID ticket.valid_id issue OTRS 4.0.8-02

Re: Need TicketID ticket.valid_id issue OTRS 4.0.8-02

Re: Need TicketID ticket.valid_id issue OTRS 4.0.8-02

Re: Need TicketID ticket.valid_id issue OTRS 4.0.8-02

Re: Need TicketID ticket.valid_id issue OTRS 4.0.8-02

Re: Need TicketID ticket.valid_id issue OTRS 4.0.8-02

Re: Need TicketID ticket.valid_id issue OTRS 4.0.8-02

Re: Need TicketID ticket.valid_id issue OTRS 4.0.8-02

Re: Need TicketID ticket.valid_id issue OTRS 4.0.8-02

Re: Need TicketID ticket.valid_id issue OTRS 4.0.8-02

Re: Need TicketID ticket.valid_id issue OTRS 4.0.8-02

Re: Need TicketID ticket.valid_id issue OTRS 4.0.8-02

Re: Need TicketID ticket.valid_id issue OTRS 4.0.8-02

Re: Need TicketID ticket.valid_id issue OTRS 4.0.8-02

Re: Need TicketID ticket.valid_id issue OTRS 4.0.8-02

Re: Need TicketID ticket.valid_id issue OTRS 4.0.8-02

Re: Need TicketID ticket.valid_id issue OTRS 4.0.8-02

Re: Need TicketID ticket.valid_id issue OTRS 4.0.8-02

Re: Need TicketID ticket.valid_id issue OTRS 4.0.8-02

Re: Need TicketID ticket.valid_id issue OTRS 4.0.8-02

Re: Need TicketID ticket.valid_id issue OTRS 4.0.8-02