TicketID is not the URL ID

Infos:

  • Used Zammad version: 5.0.3-1641822517.3413a290.bionic
  • Installation method (source, package, …): package
  • Operating system: Ubuntu 18.04 LTS
  • Database + version: Postgres 10.19-0ubuntu0.18.04.1
  • Elasticsearch version: 6.8.22
  • Browser + version: Not relevant

Expected behavior:

The URL ID and the Ticket ID should be the same.
example.org/#ticket/zoom/4274
TicketID = 4274

Actual behavior:

The URL ID is one number higher asthe Ticket ID.
example.org/#ticket/zoom/4275
TicketID = 4274

Steps to reproduce the behavior:

We are unsure when this problem happens but now we always get a false URL ID.
The api is also not more working correctly because your need to work with the URL ID and not the ticket id.

Yes I’m sure this is a bug and no feature request or a general question.

1 Like

We have this problem too, there seems to be no solution to this and it’s really bugging us.

We fixed this issue at our side. But i don´t know if it´s the read way and i think you can realy damage your installation this way.

We looked in our database where this problem comes from and in table ‘tickets’ we found the error.
The problem is that you id and number is not the same in the table.

We created an entry with the new number and with id 99999999.

The system looks at the next free number and the id will autoincrement.

I don´t have the correct sql statement anymore but it should look something like that (I copyed and old test ticket)

Please create a dump before

pg_dump -t tickets zammad > /tmp/zammad.dump
INSERT INTO tickets (id, group_id, priority_id, state_id, number, title, owner_id, customer_id, close_at, updated_by_id, created_by_id, created_at, updated_at) VALUES (999999,1,2,4,424347,"Das ist der Titel",1,9,"2021-12-16 09:25:26.17",3,70,"2021-12-16 09:20:20.60086","2021-12-16 09:25:26.168");

Hi,

base on your input we did further investigations because we also have a difference between the tickets.id column (which is the number in the zoom url) and the ticket number itself.

Out Ticket Prefix is “17”.
So run a sql query agains the postgres database to check if there is a offset between id and number column:
select id, (cast(REGEXP_REPLACE("number", '^17', '') as integer) - id) as diff from tickets;
the result is that until a certain ticket number the difference is zero and then after the ticket its 32.
This maybe could come through a zammad update because the first ticket with offset is a test ticket from our dev ops team.

Result is that until Ticket X the zoom url is:
https://tickets.XXXX.com/#ticket/zoom/<ticket number without 17 prefix)
for example for ticket ID 171234 its
https://tickets.XXXX.com/#ticket/zoom/1234

after the Ticket X were the offset starts it is:
https://tickets.XXXX.com/#ticket/zoom/<ticket number without 17 prefix + offset 32>
for example for ticket ID 174562 its
https://tickets.XXXX.com/#ticket/zoom/4594

We do a reference from our CRM System to the zammad ticket system and can fix this offset by code in the CRM itself. We currently be careful to fix this in the zammad database because we dont want to break the ticket references which are outside by the customers by email.

Hope this helps,
Best

I saw this in the database already and I tried to fix it by just manually editing the ticket number of the most recent ticket in the database to match with its ticket id (ID is 5 higher than the ticket number for us). So I changed ticket ID 11480 ticket number 11475 to ticket number 11480. A newly created ticket should therefore get ID 11481 and number 11481 but it doesn’t, Zammad ignores the number I edited and gives the next ticket 11476. I tried a Zammad restart but it doesn’t help and I don’t know how Zammad knows what the old number was. If I look at the ticket I changed in the webinterface the number also stays on the old number, manually editing the database is completely ignored.

This problem could easily be prevented by the devs if Zammad would always generate the ticket number based on the prefix + ticket id instead of just counting up the number field. This way it would always match.
In our case the numbers grew apart because the ticket creation process crashed on some e-mails because of a certain trigger. The e-mails then went into unprocessable mail and the ticket id where it crashed was essentially skipped. So these ids are not present in the database but the number just counted up and that’s where it began.
I would really like to fix this somehow.

You need to create entrys in your database were you fill your ticket number to the correct id.

At the moment you have

ID Number
123 11123
124 11127

Then you need to create entrys for the missing ticketnumbers with a very high ID like

ID Number
123 11123
127 11124
9999999999 11124
9999999998 11125
9999999997 11126
9999999996 11127

Then just create a new ticket and it should be

128 11128

Thats how i fixed the problem.
Zammad isn´t activly looking with id is free. It just uses pq instead and when you make an dump of the table you can you the mechanics.

SELECT pg_catalog.setval('public.tickets_id_seq', 1234, true);
CREATE UNIQUE INDEX index_tickets_on_number ON public.tickets USING btree (number);

you don’t have to create entries for the missing ticket numbers, instead you can directly modify the value in the postgres sequence:

--if the max of ticket.id would be 128 and the prefix is "11"
SELECT setval('public.tickets_id_seq', 11129, true);

I just got around to trying this on my test Zammad installation but this doesn’t help at all. My problem (and I think the others that have this also) is that the ticket ID is higher than the ticket number. The number has to be set to a higher sequence somehow, not the ID.

After executing the statement you posted and then creating a new ticket I got this result:
grafik

This is not good :wink:

Hi,
I am also seeing the problem, I would like to use your method of creating ghost tickets. Is it possible you could post a more detailed “how to” for us not so technical users?
Currently I have a difference of 3 between ticket No and URL.
I am using latest ver 5x. Package installation with default options on ubuntu 18.04

Kind regards
chris

Hi,

Same problem here :frowning:

Zammad installed on Debian 11 with latest official packages.

Any news or easy/safe solution to fix this issue, at least for the new tickets created in zammad, (without touching the database)

I still don’t understand why Zammad dev team doesn’t consider this issue as a bug (they closed the github ticket #3916 and locked it to contributor only)

This thread clearly shows why issue number 3916 is a question and not a bug.
The ticket numbers contain a counter - however, not the ticket ID.

You can’t mix ticket number and ticket ID together - it’s two different things!
The number that’s contained in the ticket URL is the ticket ID not number.

The counter may drift away from the actual ticket ID for various technical reasons and, at least what I believe, is absolutely fine and no issue at all.

Sure, its a workaround to use the ticket id for the url.

The main problem is that there is no way provided by zammad to use the ticket id in a public url to reference it from other systems.

So maybe this should be tried to address first because there is currently to my knowledge no solution for that.

Zammad is a nice system, but this important function is not available and should be implemented.

Which is not true at all.
You receive both ID and number within the API calls without any issues. You can also use them both in variables. I think you’re trying to construct issues which are not there or may not have found the answer. Without a clear use case however nobody will be able to help you.

Thanks for your answer and I am happy there is a solution for that.

I already did check your API description and I cannot find in the REST calls a way to get the internal ticket id for the external provided in the WebUI of zammad.

I did understand:

https://abc.com/#ticket/zoom/13348 <= internal ticket id

I have a external ticket id (e.g. 1713318)
Using ticket show via REST API:
curl -H “Authorization: Token token=XXX” https://abc.com/api/v1/tickets/1713318
=> "Couldn’t find Ticket with ‘id’=1713318

Seems I can only use the “internal ticket id”. That does not help to solve my problem.

I tried also using a search call:
/api/v1/tickets/search?query={search string}

This brings any ticket using the number 1713318 in the articles. I only want the one ticket for the ticket number.

So is there a way, please provide me a hint how we can solve this via the REST API.

In general: I think zammad should provide an endpoint to show a ticket detail page for a given public ticket number. I can image that many users cannot deploy custom code to resolve this via the REST API if there is a way for it.

I am happy if we can use the REST API, please advice.

Best

Have a look at the advance search section. You can search for e.g. ticket numbers directly if you know how.
https://user-docs.zammad.org/en/latest/advanced/search.html

Thanks for the hint, this brings us one step further.

I tried:

https://abc.com/#search/number:1713130

This shows the search result list with the ticket for the given number.

This helps a little bit, but the problem is that the user still has to select the ticket in the result list to see the details of the ticket. Its a additional step which every ticket agent has to do to get the details of the ticket. As we have many tickets to be opened from our external system this is not very practical for our users.

Is there a way to directly open the ticket detail page?

Best

You have to view this from a user perspective and not so technical.

When we implemented Zammad in our company the ticket ID and the ticket number were identical so the users got used to being able to just enter the number (without prefix) in the url to open a specific ticket for example when you are on the phone with a colleague and he / she says “could you have a look at ticket 123…” we just put the number in the url and have the ticket open. Another reason to do this could be the one window / tab limitation and this really annoying behaviour when you click on a link to a ticket in an e-mail and instead of opening the ticket in the existing Zammad browser tab it opens a new one and your old tab has the session warning. This is so annoying that no one uses the links and instead some people type the number in the url.
Those are just 2 examples of why people would use this.

Now imagine you are used to this being possible and suddenly after 2 years of using Zammad the numbers don’t match anymore. Of course you would call your Zammad admin and ask him what’s up and he says “hey the id and the ticketnumber are not the same, how could you assume that?”. This is not at all a satisfying answer for anybody, not me the admin who has to say this and definitely not for the user.

Technically it would be no problem to ensure the number and the id always match when creating a new ticket so maybe you are correct that this is not a bug, but it definitely would be an enhancement to the usability of Zammad that would reduce questions and discussions about this in the future.

If you beleive this is a thing, please create a feature request:

Mate, look at ANY productive ticket within your instance and have a lurk into the address bar of your browser.