Ticket id off by one day

1

Using RT 4.4.4…

When we create tickets today 8/17/2020 the ticket id shows as one day off e.g. 20200816008.

I have the timezone set correctly in RT_SiteConfig.pm to Set($Timezone, “US/Eastern”);

Also the timezone set in our database is correct.

MariaDB [rtweb]> SELECT @@system_time_zone;
±-------------------+
| @@system_time_zone |
±-------------------+
| EDT |
±-------------------+

2

The Id or the Created date?

3

Just the id is off. The created date is correct.

e.g.
Date: Mon, 17 Aug 2020 10:37:24 -0400

4

The Id value is just incremented each time a new ticket is created, so it shouldn’t be anything other than the next value in the database

5

We recently upgraded to RT4.4.4. On the old system the ticket id was built from the date like this yyyymmddxxx where xxx would increment throughout the day, and the date portion would rollover at midnight. Is this not the case anymore?

6

Is it the Id field or the subject line of the ticket that gets the yyyymmddxxx format? I believe the Id value for a ticket has always been a Integer in the database

7

the id and EffectiveId a in the Tickets table are the same as the subject line ticket id.

8

Looking at the schema in RT5 but I assume its similar in RT4 I see:

id INTEGER NOT NULL  AUTO_INCREMENT,
EffectiveId integer NOT NULL DEFAULT 0  ,
Subject varchar(200) NULL DEFAULT '[no subject]' ,

Where subject isn’t the same type as Id/EffectiveId

9

Did you have a scrip that set the subject to the date on ticket create perhaps?

10

No scrips from the old server I can see. It’s really not the subject line, but the id in the database. I didn’t admin the old system, so I’m not sure what was done to change it. This is from our DB, you can see that after 18, something was done to change it.

MariaDB [rtweb]> select id,EffectiveId from Tickets limit 100;
±------------±------------+
| id | EffectiveId |
±------------±------------+
| 1 | 1 |
| 2 | 2 |
| 3 | 3 |
| 4 | 4 |
| 5 | 5 |
| 6 | 6 |
| 7 | 7 |
| 8 | 8 |
| 9 | 9 |
| 10 | 10 |
| 11 | 11 |
| 12 | 12 |
| 13 | 13 |
| 14 | 14 |
| 15 | 15 |
| 16 | 16 |
| 17 | 17 |
| 18 | 18 |
| 20130311001 | 20130311001 |

11

Yeah that is interesting, can you run: mysql> describe Tickets;

I believe 20130311001 is too large for a normal int column

12

MariaDB [rtweb]> describe Tickets;
±----------------±-------------±-----±----±-------------±---------------+
| Field | Type | Null | Key | Default | Extra |
±----------------±-------------±-----±----±-------------±---------------+
| id | bigint(15) | NO | PRI | NULL | auto_increment |
| EffectiveId | bigint(13) | YES | MUL | NULL | |
| Queue | bigint(15) | NO | MUL | 0 | |
| Type | varchar(16) | YES | | NULL | |
| Owner | bigint(15) | NO | MUL | 0 | |
| Subject | varchar(200) | YES | | [no subject] | |
| InitialPriority | bigint(15) | NO | | 0 | |
| FinalPriority | bigint(15) | NO | | 0 | |
| Priority | bigint(15) | NO | | 0 | |
| TimeEstimated | bigint(15) | NO | | 0 | |
| TimeWorked | bigint(15) | NO | | 0 | |
| Status | varchar(64) | YES | | NULL | |
| TimeLeft | bigint(15) | NO | | 0 | |
| Told | datetime | YES | | NULL | |
| Starts | datetime | YES | | NULL | |
| Started | datetime | YES | | NULL | |
| Due | datetime | YES | | NULL | |
| Resolved | datetime | YES | | NULL | |
| LastUpdatedBy | bigint(15) | NO | | 0 | |
| LastUpdated | datetime | YES | | NULL | |
| Creator | bigint(15) | NO | | 0 | |
| Created | datetime | YES | | NULL | |
| IsMerged | smallint(6) | YES | | NULL | |
| SLA | varchar(64) | YES | | NULL | |
±----------------±-------------±-----±----±-------------±---------------+

13

Yeah looks like the Id column was made not unique, I am guessing there is some kind of custom code in the ticket create method. Do you have the original RT source code before upgrade still?

Also it seems like having Id not be unique could cause problems for any tickets that have the same Id value

14

I’ve reached out to the old admin, and he’s saying he did it with an external script.

15

I wonder what happens if two tickets are created within the same time, if they have the same ID I assume RT will not be able to load either

16

We figured it out. There was a cronjob that altered the AUTO_INCREMENT value every night at midnight.

1 Like