Strange "Ticket" table configuration

Amukinado · Post by **Amukinado** » 09 Jun 2010, 18:27

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

Post by **Daniel Obee** » 11 Jun 2010, 14:33

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 · Post by **Amukinado** » 11 Jun 2010, 17:09

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

Amukinado · Post by **Amukinado** » 14 Jun 2010, 11:25

...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

Amukinado · Post by **Amukinado** » 14 Jun 2010, 12:47

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

Post by **crythias** » 14 Jun 2010, 16:07

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?

Amukinado · Post by **Amukinado** » 14 Jun 2010, 16:15

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

Post by **crythias** » 14 Jun 2010, 16:20

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.

Amukinado · Post by **Amukinado** » 14 Jun 2010, 16:31

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

Post by **crythias** » 14 Jun 2010, 16:43

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".

Amukinado · Post by **Amukinado** » 14 Jun 2010, 17:05

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

Znuny and ((OTRS)) Community Edition

Strange "Ticket" table configuration

Strange "Ticket" table configuration

Re: Strange "Ticket" table configuration

Re: Strange "Ticket" table configuration

Re: Strange "Ticket" table configuration

Re: Strange "Ticket" table configuration

Re: Strange "Ticket" table configuration

Re: Strange "Ticket" table configuration

Re: Strange "Ticket" table configuration

Re: Strange "Ticket" table configuration

Re: Strange "Ticket" table configuration

Re: Strange "Ticket" table configuration