Write on MySQL Jira DB

[MarcoC]

Hi!

I have Jira 5.2 and MySQL 5.5 on my server.

I create a script to write on the DB (after stopping the service) that import a CSV file in the worklog table...

If I write few lines and restart the service, when I add a Log Work from Jira, the line is created and the ID number was changed for a different "starter number" of hundred..

----------------------------------------------

EXAMPLE:

my WORKLOG table end at ID = 12345

if I manual add the new line with ID = 12346

automatically, next line that I will create from LogWork in JIRA, will set to ID = 12400

and the next worklog 12401, 12402, etc...

-----------------------------------------------

If my WORKLOG table end at ID = 12898

if I manual add the new lines with ID = 12899 and ID = 12900 and ID = 12901

when I add a new LogWork from Jira, I have a screen error that told me that "key 12900 is just used"

Is it possible to "change" this jump of the next hundred of ID with a jump bigger?? Not like one thousand, but 3 hundred could be OK for me...

Regards,

Marco

[Nic Brough -Adaptavist- · Accepted solution]

Ok.

Do not write to a Jira database while it is running. You will break it.

To answer the actual question though, yes, you can jump numbers as much as you want. Jira will simply re-read the starting counter after you start it and pick up where it left off. It doesn't care about missing numbers or how big the gaps are.

The process for amending Jira data in the database is breifly:

1. Stop Jira
2. Get a proven backup
3. Run the SQL
4. Start Jira
5. Re-index it

[MarcoC]

Yes, I know what I have to do to write on a DB, and the list that you give me, it is exactly what I do. Thanks for remember me!

But it is possible change that "value" of hundred?

----------------------------------------------

EXAMPLE:

my WORKLOG table end at ID = 12345

if I manual add the new line from ID = 12346 to ID = 12410

automatically, next line that I will create from LogWork in JIRA, will set to ID = 12400

and, when I click on "Add and Close" in the screen, it give me an error "key 12400 is just used"

if I click another time error "key 12401 is just used"

and go like this until 12411, the first free!!!!!

-----------------------------------------------

[Nic Brough -Adaptavist-]

Oh, I see, you weren't increasing the counter. That's one of the problems with direct access - people miss a lot of the application/business logic and don't make all the updates they need to.

Look at the table sequence_value_item. I think you probably need to increase the counter named "worklog" so that it is at least <your highest numbered insert> + 1.

[MarcoC]

Where can I find the "sequence_value_item"?? is it the ID??

If I try to find the <highest number insert>, i think that my problem could remain (if i found that the highest number is XX498 and I have to add 5 elements, the problem is the same, or I'm wrong??)

[Nic Brough -Adaptavist-]

Again,

Llook at the table sequence_value_item

There is a line in there saying "worklog"

Make sure it is set higher than the highest ID in the worklog table

The number is irrelevant - if you added lines to worklog with IDs of 10010, 10011, 10012, 20001, then make sure you set the worklog counter to 20002 or more.

[MarcoC]

OK!

I found the table sequnce_number_item.

I create a Script that when I finish to insert manually in the DB, select the max number of ID, add 1 to this value and write it in "worklog" of the sequence_number_item....

The problem now is that if I start to add a LogWork from Jira, it give me an error like this:

but the value in the "worklog" ok sequence_number_item is 14121....

WHY??

Regards,

Marco

[Nic Brough -Adaptavist-]

Sounds like you did not have Jira shut down when you ran your SQL.

Check the value of the sequence_number_item again - I suspect the cache has overwritten your change.

Did you re-index after restarting as well?

[crf]

Just to chime in here...

1. As Nic has pointed out, it is difficult to make this work correctly while JIRA is running precisely because the entity engine has an in-memory cache of where it is in the sequence.  You can see the code for this on Bitbucket.
2. You could safely bump the sequence_value_item rows yourself to acquire banks of IDs to use.  This is the mechanism that JIRA Data Center relies on (each node of the cluster keeps its own in-memory cache of ID banks it is using, and they go back to this table in the database to claim another 100 ids whenever they run out of them).
3. However, you should not write to JIRA's schema yourself, AT ALL.  JIRA's database schema is not part of its API.  We make no guarantees about how it works or what you will find when reading from it directly, much less what happens if you write to it.  It is subject to change without warning, even in a bugfix release.
4. Even ignoring the fact that writing to the schema directly isn't supported, it also fails to reindex the issues you have added worklogs for.
5. There is a REST API for creating worklogs.  It is safer.  It is supported.  You don't have to shut JIRA down to use it.  It reindexes the issues properly.  You should use that.

[crf]

(Of course, I'm not sure that REST endpoint existed yet way back in 5.2.  You should also upgrade. :) )