Jiraissue primary key

[baltasar ramis]

Hi people

I want to insert serveral rows in JIRAISSUE table with sql INSERTS

If I do it I get a PRIMARY KEY error so I need to know where JIRA gets its PK

I read that the table PROJECT has a field called pcounter which pointed the next value of the key but if I do

SELECT PROJECT.pname,

pcounter,

COUNT (*),

MAX (JIRAISSUE.id),

MIN (JIRAISSUE.id),

MAX( JIRAISSUE.pkey),

MIN( JIRAISSUE.pkey),

MIN (workflow_id),

MAX (workflow_id)

FROM JIRADBUSER.JIRAISSUE, JIRADBUSER.PROJECT

WHERE project.id = JIRAISSUE.project

GROUP BY PROJECT.pname, pcounter;

the values don't seem right

Does anybody know where the ID field of JIRAISSUE gets its value from?

Thanks in advance

Baltasar

[Andrzej Pasterczyk]

Why would you want to do that? I suggest that you describe what you're trying to achive and get help here on how to do it. Playing around with Jira DB doesn't seem safe and there are ways to do stuff in a more reasonable manner (Java API, REST, plugins etc.)

[baltasar ramis]

The reason to do that is easy, because I've been asked to

If you don't know the answer fine but don't tell me I'm not reasonable, if is safe or not is up to me to decide. Don't answer if you don't have anything helpful to say

[Andrzej Pasterczyk]

I'm sorry if you got my message this way, wasnt my intention to judge what you're trying to do.

Still it's hard for me to believe that you were asked to use SQL inserts, my guess would be that you want some issues registered in Jira and you want to do it by script instead of by hand (maybe forcing some values or whatever). Of course I could be wrong.

[Renjith Pillai]

Hey @simar. Swiniak's comment was with good intentions. I guess no one in the community here will be really for manipulating database directly.

[Nic Brough -Adaptavist-]

>I want to insert serveral rows in JIRAISSUE table with sql INSERTS

No, you do not. (Well, there are edge cases, but "no, you don't" is the case 99.999% of the time) Two reasons.

Reason 1: Process - when changing data in a Jira database with SQL, the process is

1. Stop Jira
2. Take a backup of the database
3. TEST the backup works
4. Run the SQL
5. Restart Jira
6. RE-index Jira

Do NOT use SQL on a running Jira installation. It is suicidal - I can guarantee that you WILL damage the data, and potentially render it unusable. You also have significant downtime to deal with.

Reason 2: The data you are inserting

If you are wanting to add a Jira issue, you can't just add to the issue table. You've spotted the project table, but you also need to change the counters in the sequence table, and then the workflow tables. If you want to create an issue with any custom fields, versions, components worklogs, etc, then you've got yet more tables to push data into.

What you need to do now

1. If you do want to insert moderately simple issues, then you'll find CSV imports are a) safe and b) easier.
2. For some issues, Jelly may be better.
3. For complicated cases, then use an API - REST, Plugins etc
4. Go and tell the person who is asking you to do this that they are wrong, and get them to explain why they think a highly risky, potentially disasterous and time wasting process is a "good idea", when there's plenty of safer and quicker alternatives.

Other bits

>I read that the table PROJECT has a field called pcounter which pointed the next value of the key but if I do

>the values don't seem right

What do you think is wrong with them?

>If I do it I get a PRIMARY KEY error so I need to know where JIRA gets its PK

>Does anybody know where the ID field of JIRAISSUE gets its value from?

Yes, because you aren't inserting the other data it needs. The value is generated by looking at the previous data.

[baltasar ramis]

Thanks for your answers

I'll have to look for another way to solve the problem I've been assigned

[Nic Brough -Adaptavist-]

Good move. If you tell us what you're trying to do, we can probably help guide you to a good solution or at least share any tricks/pitfalls.

[Jo-Anne MacLeod]

there is also the command line plugin that may work for you.

[Renjith Pillai]

And if you are looking at bulk imports, this may help

http://confluence.atlassian.com/display/JIRA/Importing+Data+from+CSV