Strange "Ticket" table configuration

Moderator: crythias

Post Reply
Amukinado
Znuny newbie
Posts: 76
Joined: 02 Jun 2010, 15:52
Znuny Version: 3.2.8
Location: Portugal

Strange "Ticket" table configuration

Post by Amukinado »

Hi. I was caught in the middle of applications migration. Some work was done before I arrived.
A department of the company I'm with has an OTRS working. This was the base start. Don't know wich version was there before apdating, but now has OTRS 2.4.7 and ITSM 2.0.2. Some work was done by another person who is not in the company now, and it seems information was lost. We have been analysing and at minimum, the "ticket" table is somehow changed and I can't understand how or why. Responsibles say they never did changes to the DB.

I'm posting that table configuration, maybe it will ring a bell on more expert users:

Code: Select all

mysql> desc ticket;
+--------------------------+--------------+------+-----+---------+----------------+
| Field                    | Type         | Null | Key | Default | Extra          |
+--------------------------+--------------+------+-----+---------+----------------+
| id                       | bigint(20)   | NO   | PRI | NULL    | auto_increment | 
| tn                       | varchar(50)  | NO   | UNI | NULL    |                | 
| title                    | varchar(255) | YES  | MUL | NULL    |                | 
| queue_id                 | int(11)      | NO   | MUL | NULL    |                | 
| ticket_lock_id           | smallint(6)  | NO   | MUL | NULL    |                | 
| ticket_answered          | smallint(6)  | NO   | MUL | NULL    |                | 
| type_id                  | smallint(6)  | YES  | MUL | NULL    |                | 
| service_id               | int(11)      | YES  | MUL | NULL    |                | 
| sla_id                   | int(11)      | YES  | MUL | NULL    |                | 
| user_id                  | int(11)      | NO   | MUL | NULL    |                | 
| responsible_user_id      | int(11)      | NO   | MUL | NULL    |                | 
| group_id                 | int(11)      | NO   |     | NULL    |                | 
| ticket_priority_id       | smallint(6)  | NO   | MUL | NULL    |                | 
| ticket_state_id          | smallint(6)  | NO   | MUL | NULL    |                | 
| group_read               | smallint(6)  | YES  |     | NULL    |                | 
| group_write              | smallint(6)  | YES  |     | NULL    |                | 
| other_read               | smallint(6)  | YES  |     | NULL    |                | 
| other_write              | smallint(6)  | YES  |     | NULL    |                | 
| customer_id              | varchar(150) | YES  | MUL | NULL    |                | 
| customer_user_id         | varchar(250) | YES  | MUL | NULL    |                | 
| timeout                  | int(11)      | NO   | MUL | NULL    |                | 
| until_time               | int(11)      | NO   | MUL | NULL    |                | 
| escalation_time          | int(11)      | NO   | MUL | NULL    |                | 
| escalation_update_time   | int(11)      | NO   | MUL | NULL    |                | 
| escalation_response_time | int(11)      | NO   | MUL | NULL    |                | 
| escalation_solution_time | int(11)      | NO   | MUL | NULL    |                | 
| freekey1                 | varchar(80)  | YES  |     | NULL    |                | 
| freetext1                | varchar(150) | YES  |     | NULL    |                | 
| freekey2                 | varchar(80)  | YES  |     | NULL    |                | 
| freetext2                | varchar(150) | YES  |     | NULL    |                | 
| freekey3                 | varchar(80)  | YES  |     | NULL    |                | 
| freetext3                | varchar(150) | YES  |     | NULL    |                | 
| freekey4                 | varchar(80)  | YES  |     | NULL    |                | 
| freetext4                | varchar(150) | YES  |     | NULL    |                | 
| freekey5                 | varchar(80)  | YES  |     | NULL    |                | 
| freetext5                | varchar(150) | YES  |     | NULL    |                | 
| freekey6                 | varchar(80)  | YES  |     | NULL    |                | 
| freetext6                | varchar(150) | YES  |     | NULL    |                | 
| freekey7                 | varchar(80)  | YES  |     | NULL    |                | 
| freetext7                | varchar(150) | YES  |     | NULL    |                | 
| freekey8                 | varchar(80)  | YES  |     | NULL    |                | 
| freetext8                | varchar(150) | YES  |     | NULL    |                | 
| freekey9                 | varchar(80)  | YES  |     | NULL    |                | 
| freetext9                | varchar(150) | YES  |     | NULL    |                | 
| freekey10                | varchar(80)  | YES  |     | NULL    |                | 
| freetext10               | varchar(150) | YES  |     | NULL    |                | 
| freekey11                | varchar(80)  | YES  |     | NULL    |                | 
| freetext11               | varchar(150) | YES  |     | NULL    |                | 
| freekey12                | varchar(80)  | YES  |     | NULL    |                | 
| freetext12               | varchar(150) | YES  |     | NULL    |                | 
| freekey13                | varchar(80)  | YES  |     | NULL    |                | 
| freetext13               | varchar(150) | YES  |     | NULL    |                | 
| freekey14                | varchar(80)  | YES  |     | NULL    |                | 
| freetext14               | varchar(150) | YES  |     | NULL    |                | 
| freekey15                | varchar(80)  | YES  |     | NULL    |                | 
| freetext15               | varchar(150) | YES  |     | NULL    |                | 
| freekey16                | varchar(80)  | YES  |     | NULL    |                | 
| freetext16               | varchar(150) | YES  |     | NULL    |                | 
| freetime1                | datetime     | YES  |     | NULL    |                | 
| freetime2                | datetime     | YES  |     | NULL    |                | 
| freetime3                | datetime     | YES  |     | NULL    |                | 
| freetime4                | datetime     | YES  |     | NULL    |                | 
| freetime5                | datetime     | YES  |     | NULL    |                | 
| freetime6                | datetime     | YES  |     | NULL    |                | 
| valid_id                 | smallint(6)  | NO   | MUL | NULL    |                | 
| create_time_unix         | bigint(20)   | NO   |     | NULL    |                | 
| create_time              | datetime     | NO   |     | NULL    |                | 
| create_by                | int(11)      | NO   | MUL | NULL    |                | 
| change_time              | datetime     | NO   |     | NULL    |                | 
| change_by                | int(11)      | NO   | MUL | NULL    |                | 
+--------------------------+--------------+------+-----+---------+----------------+
70 rows in set (0.01 sec)
The actual data model has 68 columns and with many names so much diference. What does this looks to the experts?

An sqldump was imported do the DB after upgrading. I have the VM whith all updates to OTRS and ITSM and before importing the data from the dump, the table has 68 rows, but this dump is from an earlier version. I'm too noob to understand this... can you help?

Thanks in advance
Regards
Bruno
OTRS V3.2.8 - ITSM 3.2.6 - Cent OS 5.9x64
Daniel Obee
Moderator
Posts: 644
Joined: 19 Jun 2007, 17:11
Znuny Version: various
Real Name: Daniel Obée
Location: Berlin

Re: Strange "Ticket" table configuration

Post by Daniel Obee »

Seems this is a pre 2.4 version of the ticket table. I guess 2.3 but not sure.

I'd check for the db changes (2.2, 2.3., 2.4) and determine which of those wasn't made. Then execute the db update scripts missing - should do the trick.

Greets,
Daniel
Amukinado
Znuny newbie
Posts: 76
Joined: 02 Jun 2010, 15:52
Znuny Version: 3.2.8
Location: Portugal

Re: Strange "Ticket" table configuration

Post by Amukinado »

It doesn't seem like that to me... I have been analysing the diferences. Here they are

My DB

Group_Id
Group Read
Group_Write
Other_Read
Other_Write
Create_Time_Unix

So... The model has 64 rows.
My DB has 70 rows. My DB matches all rows and has 6 more rows.
So 64 Rows match perfectly and there are 6 new rows I don't know where they came from.

I Have been reviewing all OTRS data models from the beggining version. I can see that " Group_id" is present in "Group_user" and "Queue" since OTRS 1.1 and on "Group_Customer_User" since OTRS 1.2. It has never been on the "Ticket" table.

Through a SQL Dump, i can see that "Group_ID" is also created on "Change_notification_rec" and on "Ticket_index". These tables are not part nor were in previous releases of OTRS, part of the DB model. I assume these are mysql system tables and indexes. Am I right?

"Create_Time_Unix" is a row from the table "Web_Upload_Cache" and is part of the OTRS DB since v2.0, but never on the "Ticket" table.

Group Read, Group_Write, Other_Read, Other_Write, as far as I can see, where never part of the OTRS DB. I don't think ITSM modules add rows to the OTRS DB, do they?

Hope this can help you and me...

Regards.
Bruno Barosa
OTRS V3.2.8 - ITSM 3.2.6 - Cent OS 5.9x64
Amukinado
Znuny newbie
Posts: 76
Joined: 02 Jun 2010, 15:52
Znuny Version: 3.2.8
Location: Portugal

Re: Strange "Ticket" table configuration

Post by Amukinado »

...recovered to an earlier version... 2.1... ticket table has 60 rows...

These 3 rows are included...can't understand this...
Group Read
Group_Write
Create_Time_Unix

Installing ITSM Modules, makes any change to the OTRS database?

Regards
Bruno
OTRS V3.2.8 - ITSM 3.2.6 - Cent OS 5.9x64
Amukinado
Znuny newbie
Posts: 76
Joined: 02 Jun 2010, 15:52
Znuny Version: 3.2.8
Location: Portugal

Re: Strange "Ticket" table configuration

Post by Amukinado »

Hey... Guess what!

I JUST FINISHED MOUNTING A NEW VM WITH CENT OS. INSTALLED OTRS 2.4.7 FROM SCRATCH!

GUESS THE RESULT OF "DESC TICKET"....

Code: Select all

mysql> desc ticket;
+--------------------------+--------------+------+-----+---------+----------------+
| Field                    | Type         | Null | Key | Default | Extra          |
+--------------------------+--------------+------+-----+---------+----------------+
| id                       | bigint(20)   | NO   | PRI | NULL    | auto_increment | 
| tn                       | varchar(50)  | NO   | UNI | NULL    |                | 
| title                    | varchar(255) | YES  | MUL | NULL    |                | 
| queue_id                 | int(11)      | NO   | MUL | NULL    |                | 
| ticket_lock_id           | smallint(6)  | NO   | MUL | NULL    |                | 
| ticket_answered          | smallint(6)  | NO   | MUL | NULL    |                | 
| type_id                  | smallint(6)  | YES  | MUL | NULL    |                | 
| service_id               | int(11)      | YES  | MUL | NULL    |                | 
| sla_id                   | int(11)      | YES  | MUL | NULL    |                | 
| user_id                  | int(11)      | NO   | MUL | NULL    |                | 
| responsible_user_id      | int(11)      | NO   | MUL | NULL    |                | 
| group_id                 | int(11)      | NO   |     | NULL    |                | 
| ticket_priority_id       | smallint(6)  | NO   | MUL | NULL    |                | 
| ticket_state_id          | smallint(6)  | NO   | MUL | NULL    |                | 
| group_read               | smallint(6)  | YES  |     | NULL    |                | 
| group_write              | smallint(6)  | YES  |     | NULL    |                | 
| other_read               | smallint(6)  | YES  |     | NULL    |                | 
| other_write              | smallint(6)  | YES  |     | NULL    |                | 
| customer_id              | varchar(150) | YES  | MUL | NULL    |                | 
| customer_user_id         | varchar(250) | YES  | MUL | NULL    |                | 
| timeout                  | int(11)      | NO   | MUL | NULL    |                | 
| until_time               | int(11)      | NO   | MUL | NULL    |                | 
| escalation_time          | int(11)      | NO   | MUL | NULL    |                | 
| escalation_update_time   | int(11)      | NO   | MUL | NULL    |                | 
| escalation_response_time | int(11)      | NO   | MUL | NULL    |                | 
| escalation_solution_time | int(11)      | NO   | MUL | NULL    |                | 
| freekey1                 | varchar(80)  | YES  |     | NULL    |                | 
| freetext1                | varchar(150) | YES  |     | NULL    |                | 
| freekey2                 | varchar(80)  | YES  |     | NULL    |                | 
| freetext2                | varchar(150) | YES  |     | NULL    |                | 
| freekey3                 | varchar(80)  | YES  |     | NULL    |                | 
| freetext3                | varchar(150) | YES  |     | NULL    |                | 
| freekey4                 | varchar(80)  | YES  |     | NULL    |                | 
| freetext4                | varchar(150) | YES  |     | NULL    |                | 
| freekey5                 | varchar(80)  | YES  |     | NULL    |                | 
| freetext5                | varchar(150) | YES  |     | NULL    |                | 
| freekey6                 | varchar(80)  | YES  |     | NULL    |                | 
| freetext6                | varchar(150) | YES  |     | NULL    |                | 
| freekey7                 | varchar(80)  | YES  |     | NULL    |                | 
| freetext7                | varchar(150) | YES  |     | NULL    |                | 
| freekey8                 | varchar(80)  | YES  |     | NULL    |                | 
| freetext8                | varchar(150) | YES  |     | NULL    |                | 
| freekey9                 | varchar(80)  | YES  |     | NULL    |                | 
| freetext9                | varchar(150) | YES  |     | NULL    |                | 
| freekey10                | varchar(80)  | YES  |     | NULL    |                | 
| freetext10               | varchar(150) | YES  |     | NULL    |                | 
| freekey11                | varchar(80)  | YES  |     | NULL    |                | 
| freetext11               | varchar(150) | YES  |     | NULL    |                | 
| freekey12                | varchar(80)  | YES  |     | NULL    |                | 
| freetext12               | varchar(150) | YES  |     | NULL    |                | 
| freekey13                | varchar(80)  | YES  |     | NULL    |                | 
| freetext13               | varchar(150) | YES  |     | NULL    |                | 
| freekey14                | varchar(80)  | YES  |     | NULL    |                | 
| freetext14               | varchar(150) | YES  |     | NULL    |                | 
| freekey15                | varchar(80)  | YES  |     | NULL    |                | 
| freetext15               | varchar(150) | YES  |     | NULL    |                | 
| freekey16                | varchar(80)  | YES  |     | NULL    |                | 
| freetext16               | varchar(150) | YES  |     | NULL    |                | 
| freetime1                | datetime     | YES  |     | NULL    |                | 
| freetime2                | datetime     | YES  |     | NULL    |                | 
| freetime3                | datetime     | YES  |     | NULL    |                | 
| freetime4                | datetime     | YES  |     | NULL    |                | 
| freetime5                | datetime     | YES  |     | NULL    |                | 
| freetime6                | datetime     | YES  |     | NULL    |                | 
| valid_id                 | smallint(6)  | NO   | MUL | NULL    |                | 
| create_time_unix         | bigint(20)   | NO   |     | NULL    |                | 
| create_time              | datetime     | NO   |     | NULL    |                | 
| create_by                | int(11)      | NO   | MUL | NULL    |                | 
| change_time              | datetime     | NO   |     | NULL    |                | 
| change_by                | int(11)      | NO   | MUL | NULL    |                | 
+--------------------------+--------------+------+-----+---------+----------------+
70 rows in set (0.00 sec)
It's exactly the same... sorry... but someone should explain this. Can you please, check your "ticket" table... or am I doing something wrong. Don't think so... i just installed CENT OS on a vm, installed OTRS... logged in to MySQL and described the table and got that result.

It seems to me that that's not my problem with the migrated data... will wait for your replies to confirm how your ticket table is configured.

Regards
Bruno
OTRS V3.2.8 - ITSM 3.2.6 - Cent OS 5.9x64
crythias
Moderator
Posts: 10169
Joined: 04 May 2010, 18:38
Znuny Version: 5.0.x
Location: SouthWest Florida, USA
Contact:

Re: Strange "Ticket" table configuration

Post by crythias »

I think the question(s) I have is (are):
More columns? Is this really a problem?
Does the system not work?
What are you feeling you're missing?
OTRS 6.0.x (private/testing/public) on Linux with MySQL database.
Please edit your signature to include your OTRS version, Operating System, and database type.
Click Subscribe Topic below to get notifications. Consider amending your topic title to include [SOLVED] if it is so.
Need help? Before you ask
Amukinado
Znuny newbie
Posts: 76
Joined: 02 Jun 2010, 15:52
Znuny Version: 3.2.8
Location: Portugal

Re: Strange "Ticket" table configuration

Post by Amukinado »

well... since things are not working properly, we started by comparing our DB with the DB model... it doesn't matches... but would like to know if anyone can confirm if this is normal or not. Can you check that on your DB?

I've read the source code/scripts and I can see the part where those rows are created... it seems this is normal... but why are those rows ommited on the DB Model?

Regards
Bruno
OTRS V3.2.8 - ITSM 3.2.6 - Cent OS 5.9x64
crythias
Moderator
Posts: 10169
Joined: 04 May 2010, 18:38
Znuny Version: 5.0.x
Location: SouthWest Florida, USA
Contact:

Re: Strange "Ticket" table configuration

Post by crythias »

Rows=records. Columns=Fields. If you have less rows, you've lost data. If you have less columns, you've got most of your data, but not in the fields you have different. Since you have both your "bad" data and a good fresh install, you have in your hands is the same as if I checked my own database.
OTRS 6.0.x (private/testing/public) on Linux with MySQL database.
Please edit your signature to include your OTRS version, Operating System, and database type.
Click Subscribe Topic below to get notifications. Consider amending your topic title to include [SOLVED] if it is so.
Need help? Before you ask
Amukinado
Znuny newbie
Posts: 76
Joined: 02 Jun 2010, 15:52
Znuny Version: 3.2.8
Location: Portugal

Re: Strange "Ticket" table configuration

Post by Amukinado »

well sorry for my bad English. No missing rows, i have more fields to input data then the ones represented on the OTRS DB model...

So, you have checked your DB on that table, and it's just the same as mine? DO you have ITSM modules installed? I Think they don't alter the OTRS DB in anyway. It adds tables, but their own tables, and doesn't alter OTRS existing tables, right?

Regards
Bruno
OTRS V3.2.8 - ITSM 3.2.6 - Cent OS 5.9x64
crythias
Moderator
Posts: 10169
Joined: 04 May 2010, 18:38
Znuny Version: 5.0.x
Location: SouthWest Florida, USA
Contact:

Re: Strange "Ticket" table configuration

Post by crythias »

I'm sorry. I meant to say that I haven't checked the database. I don't have ITSM modules installed. You've already said you have installed a fresh copy in a virtual machine, so I assumed you at that point had in your hands the same thing you're asking for someone else to check.

I don't at all mean to suggest anything bad about your use of English, so if I offended you, please accept my apologies. I merely wanted to make certain that we are talking in the same terms (as well as anyone else who might be reading this thread.)

If you have fields in your original database that have been added, it's likely they have been added by hand per the documentation, with "Alter Tables".
OTRS 6.0.x (private/testing/public) on Linux with MySQL database.
Please edit your signature to include your OTRS version, Operating System, and database type.
Click Subscribe Topic below to get notifications. Consider amending your topic title to include [SOLVED] if it is so.
Need help? Before you ask
Amukinado
Znuny newbie
Posts: 76
Joined: 02 Jun 2010, 15:52
Znuny Version: 3.2.8
Location: Portugal

Re: Strange "Ticket" table configuration

Post by Amukinado »

No... you have not offended me...lol.

I read this files, the instalation scripts that create the DB and it's structure and inserts the first data:

http://source.otrs.org/viewvc.cgi/otrs/ ... /database/

otrs-initial_insert.mysql.sql
otrs-schema-post.mysql.sql
otrs-schema.mysql.sql
otrs-schema.xml

I can see the tables and fields beeing created on those files...


Well... it seems i have to start searching for another reason for the fail.

Regards
Bruno
OTRS V3.2.8 - ITSM 3.2.6 - Cent OS 5.9x64
Post Reply