Why did updating the Inventory Plugin to version 1.10.5 wipe out the JIP data? (forms, attributes, records, etc)

In JIRA, manage Add-Ons, I updated Inventory Plugin to 1.10.5.  When I went to explore the inventory records, they were no longer there.  All forms, attributes and data disappeared (deleted).

What would cause this to happen?

4 answers

0 vote
Tuncay Senturk Community Champion Feb 23, 2017

Hi Daniel,

I do not think your data has been deleted.

Do you have database access?

Could you please connect to database and see whether there are any rows in AO_23ADAF_JIP_INVENTORY table?

Regards

No records found in AO_23ADAF_JIP_INVENTORY, but here are my findings and questions regarding data loss after updating Inventory Plugin from version 1.10.4 to 1.10.5.

 

Please provide clarification and a resolution for the following:

  1. AO_23ADAF JIP table group vs AO_CA2219 JIP table group?
  2. Which of the two table groups above should be used for 1.10.5 (see comparison below)?
  3. Why didn't the 1.10.5 UPDATE reference the correct JIP table group?

EMPTY AO_23ADAF TABLE GROUP

AO_23ADAF - All JIP tables are Empty except for the following:

  • JIP_ACCESS having values ("MENU", "ATTRIBUTE", "FORM", "INVENTORY")
  • JIP_PARAMETER having values ("1", "JIP_VERSION"; "2003003000")

     

SELECT * FROM AO_23ADAF_JIP_ACCESS; -- (4 rows)
SELECT * FROM AO_23ADAF_JIP_ATTRIBUTE; -- (0 rows)
SELECT * FROM AO_23ADAF_JIP_ATTRIBUTE_VALUE; -- (0 rows)
SELECT * FROM AO_23ADAF_JIP_FORM; -- (0 rows)
SELECT * FROM AO_23ADAF_JIP_FORM_ATTRIBUTE; -- (0 rows)
SELECT * FROM AO_23ADAF_JIP_HISTORY; -- (0 rows)
SELECT * FROM AO_23ADAF_JIP_INVENTORY; -- (0 rows)
SELECT * FROM AO_23ADAF_JIP_INVENTORY_ITEM; -- (0 rows)
SELECT * FROM AO_23ADAF_JIP_PARAMETER; -- (1 row)
SELECT * FROM AO_23ADAF_JIP_USER_PREFERENCE; -- (0 rows)

 

POPULATE AO_CA2219 TABLE GROUP

 

SELECT * FROM "AO_CA2219_JIP_ACCESS"; -- 4 rows
SELECT * FROM "AO_CA2219_JIP_ATTRIBUTE"; -- 40 rows
SELECT * FROM "AO_CA2219_JIP_ATTRIBUTE_VALUE"; -- 6 rows
SELECT * FROM "AO_CA2219_JIP_FORM"; -- 2 rows
SELECT * FROM "AO_CA2219_JIP_FORM_ATTRIBUTE"; -45 rows
SELECT * FROM "AO_CA2219_JIP_HISTORY"; -- 187 rows
SELECT * FROM "AO_CA2219_JIP_INVENTORY"; -- 36 rows
SELECT * FROM "AO_CA2219_JIP_INVENTORY_ITEM"; -- 1,282 rows
SELECT * FROM "AO_CA2219_JIP_PARAMETER"; -- 1 row
-- Note: JIP_PARAMETER has same values as AO_23ADAF ("1", "JIP_VERSION"; "2003003000")
SELECT * FROM "AO_CA2219_JIP_USER_PREFERENCE"; -- 1 row

 

Environment: JIRA Server 6.3.14
Database: Postgres 9.4
OS: Ubuntu

0 vote
Tuncay Senturk Community Champion Feb 23, 2017

Hi,

Thanks for the brilliant response. You already answered my latter question.

We faced this problem before, and asked here: https://answers.atlassian.com/questions/15289760/why-do-add-ons-tablenames-change

Then we raised this bug https://ecosystem.atlassian.net/browse/AMPS-1249 to Atlassian. No answer at all.

How about downgrading to 1.10.4 version?

Hi Tuncay,

I now know that I should have done a JIP Export (export_yyyymmdd.json) before updating the JIRA Inventory Plugin. Then I could have imported the file after the update.

But, for my situation, what would you recommend?

The data already exist from the previous version in the "AO_CA2219" JIP tables.

I did the following work around, but I am unsure if it may cause problems later when doing another update or plugin install.

Task Performed

Stop JIRA

-- Stop JIRA or Disable Inventory Plugin then Start or Enable after the following is performed
-- Rename Inventory Plugin 1.10.5 tables prefixed with "AO_23ADAF" to a temporary prefix "_temp_AO_23ADAF"
-- Temporary tables as a precautionary measure.

ALTER TABLE "AO_23ADAF_JIP_ACCESS" RENAME TO "_temp_AO_23ADAF_JIP_ACCESS";
ALTER TABLE "AO_23ADAF_JIP_ATTRIBUTE" RENAME TO "_temp_AO_23ADAF_JIP_ATTRIBUTE";
ALTER TABLE "AO_23ADAF_JIP_ATTRIBUTE_VALUE" RENAME TO "_temp_AO_23ADAF_JIP_ATTRIBUTE_VALUE";
ALTER TABLE "AO_23ADAF_JIP_FORM" RENAME TO "_temp_AO_23ADAF_JIP_FORM";
ALTER TABLE "AO_23ADAF_JIP_FORM_ATTRIBUTE" RENAME TO "_temp_AO_23ADAF_JIP_FORM_ATTRIBUTE";
ALTER TABLE "AO_23ADAF_JIP_HISTORY" RENAME TO "_temp_AO_23ADAF_JIP_HISTORY" 
ALTER TABLE "AO_23ADAF_JIP_INVENTORY" RENAME TO "_temp_AO_23ADAF_JIP_INVENTORY";
ALTER TABLE "AO_23ADAF_JIP_INVENTORY_ITEM" RENAME TO "_temp_AO_23ADAF_JIP_INVENTORY_ITEM";
ALTER TABLE "AO_23ADAF_JIP_PARAMETER" RENAME TO "_temp_AO_23ADAF_JIP_PARAMETER";
ALTER TABLE "AO_23ADAF_JIP_USER_PREFERENCE" RENAME TO "_temp_AO_23ADAF_JIP_USER_PREFERENCE";

-- Rename Inventory Plugin 1.10.4 original tables prefixed with "AO_CA2219" to "AO_23ADAF" 
-- "AO_23ADAF" are the table names required for Inventory Plugin 1.10.5

ALTER TABLE "AO_CA2219_JIP_ACCESS" RENAME TO "AO_23ADAF_JIP_ACCESS";
ALTER TABLE "AO_CA2219_JIP_ATTRIBUTE" RENAME TO "AO_23ADAF_JIP_ATTRIBUTE";
ALTER TABLE "AO_CA2219_JIP_ATTRIBUTE_VALUE" RENAME TO "AO_23ADAF_JIP_ATTRIBUTE_VALUE";
ALTER TABLE "AO_CA2219_JIP_FORM" RENAME TO "AO_23ADAF_JIP_FORM";
ALTER TABLE "AO_CA2219_JIP_FORM_ATTRIBUTE" RENAME TO "AO_23ADAF_JIP_FORM_ATTRIBUTE";
ALTER TABLE "AO_CA2219_JIP_HISTORY" RENAME TO "AO_23ADAF_JIP_HISTORY";
ALTER TABLE "AO_CA2219_JIP_INVENTORY" RENAME TO "AO_23ADAF_JIP_INVENTORY";
ALTER TABLE "AO_CA2219_JIP_INVENTORY_ITEM" RENAME TO "AO_23ADAF_JIP_INVENTORY_ITEM";
ALTER TABLE "AO_CA2219_JIP_PARAMETER" RENAME TO "AO_23ADAF_JIP_PARAMETER";
ALTER TABLE "AO_CA2219_JIP_USER_PREFERENCE" RENAME TO "AO_23ADAF_JIP_USER_PREFERENCE";

-- Verify temporary tables are empty before droping;

DROP TABLE "_temp_AO_23ADAF_JIP_INVENTORY_ITEM";
DROP TABLE "_temp_AO_23ADAF_JIP_FORM_ATTRIBUTE";
DROP TABLE "_temp_AO_23ADAF_JIP_ATTRIBUTE_VALUE";
DROP TABLE "_temp_AO_23ADAF_JIP_ACCESS";
DROP TABLE "_temp_AO_23ADAF_JIP_ATTRIBUTE";
DROP TABLE "_temp_AO_23ADAF_JIP_INVENTORY";
DROP TABLE "_temp_AO_23ADAF_JIP_FORM";
DROP TABLE "_temp_AO_23ADAF_JIP_HISTORY";
DROP TABLE "_temp_AO_23ADAF_JIP_PARAMETER";
DROP TABLE "_temp_AO_23ADAF_JIP_USER_PREFERENCE";

Start JIRA

After I performed the function above, the Inventory Plugin works.

However, I noticed the database tables still have the string "ao_ca2219" as part of the constraint, keys and index names.

Do you see a problem with the constraints names for future updates or plugin installations?

Example:

The constraints, primary key, foreign key and indexes all contain the former string  "ao_ca2219" for the "AO_23ADAF" tables

-- Generate by PSQL "SCRIPT --> CREATE"
-- Table: "AO_23ADAF_JIP_INVENTORY"
-- DROP TABLE "AO_23ADAF_JIP_INVENTORY";
CREATE TABLE "AO_23ADAF_JIP_INVENTORY"
(
  "CUSTOMFIELD_ID" character varying(255),
  "FORM_ID" integer,
  "ID" integer NOT NULL DEFAULT nextval('"AO_CA2219_JIP_INVENTORY_ID_seq"'::regclass),
  "NAME" character varying(255),
  "OPTION_ID" bigint,
  "SORT_ORDER" integer DEFAULT 0,
  CONSTRAINT "AO_CA2219_JIP_INVENTORY_pkey" PRIMARY KEY ("ID"),
  CONSTRAINT fk_ao_ca2219_jip_inventory_form_id FOREIGN KEY ("FORM_ID")
      REFERENCES "AO_23ADAF_JIP_FORM" ("ID") MATCH SIMPLE
      ON UPDATE NO ACTION ON DELETE NO ACTION
)
WITH (
  OIDS=FALSE
);
ALTER TABLE "AO_23ADAF_JIP_INVENTORY"
  OWNER TO jiradbuser;
-- Index: index_ao_ca2219_jip1078548063
-- DROP INDEX index_ao_ca2219_jip1078548063;
CREATE INDEX index_ao_ca2219_jip1078548063
  ON "AO_23ADAF_JIP_INVENTORY"
  USING btree
  ("OPTION_ID");
-- Index: index_ao_ca2219_jip1556219913
-- DROP INDEX index_ao_ca2219_jip1556219913;
CREATE INDEX index_ao_ca2219_jip1556219913
  ON "AO_23ADAF_JIP_INVENTORY"
  USING btree
  ("CUSTOMFIELD_ID" COLLATE pg_catalog."default");
-- Index: index_ao_ca2219_jip1947516368
-- DROP INDEX index_ao_ca2219_jip1947516368;
CREATE INDEX index_ao_ca2219_jip1947516368
  ON "AO_23ADAF_JIP_INVENTORY"
  USING btree
  ("FORM_ID");
-- Index: index_ao_ca2219_jip871522577
-- DROP INDEX index_ao_ca2219_jip871522577;
CREATE INDEX index_ao_ca2219_jip871522577
  ON "AO_23ADAF_JIP_INVENTORY"
  USING btree
  ("NAME" COLLATE pg_catalog."default");
Tuncay Senturk Community Champion Feb 24, 2017

Hi,

Sorry for the late response.

The queries are OK, and as you mentioned it should work.

Actually AO_23ADAF is the right name and in the feature updates, it will search for that. But when you upgrade to JIRA 7, we will do the same trick only once and I suppose, you won't need any more SQL updates

I'll be upgrading JIRA to 7.0 next week.  Do you have an article on what to do before upgrading JIRA?

Is the trick to do a export JIP data to json and import it once JIRA 7 is online?

Tuncay Senturk Community Champion Feb 24, 2017

Normally, there is nothing to do in upgrades. But in your case, after upgrading JIRA and addon, we will check whether JIRA uses AO_23ADAF or ao_ca2219 as table name prefixes. 

Please just backup your database, and export JIP data as json. But do not import it. We will use tables instead.

This is the first case that we faced this problem from a customer. We were facing this problem in our development environment rarely, but we never released those binaries. 

So, are you saying JIP (1.10.5) uses AO_23ADAF tables and JIP (1.10.4) uses AO_CA2219 tables?

I was hoping for another solution because I've updated the JIP plugin because it is suppose to have the CSV import capability and I wanted to test it out. 

What about doing something at the DB level?

(i.e Renaming tables or inserting records from tables)

 

-- 10 tables example using JIP_ATTRIBUTE
 
-- Renaming Table Example
ALTER TABLE "AO_23ADAF_JIP_ATTRIBUTE" RENAME TO "_empty_AO_23ADAF_JIP_ATTRIBUTE";
ALTER TABLE "AO_CA2219_JIP_ATTRIBUTE" RENAME TO "AO_23ADAF_JIP_ATTRIBUTE";
 
-- Inserting Records into empty table from populate table. Assuming destination tables are empty.
INSERT INTO "AO_23ADAF_JIP_ATTRIBUTE" ("ATTRIBUTE_NAME", "ATTRIBUTE_TYPE","ID", "PATTERN")
SELECT "ATTRIBUTE_NAME", "ATTRIBUTE_TYPE","ID", "PATTERN" FROM "AO_CA2219_JIP_ATTRIBUTE";

Suggest an answer

Log in or Sign up to answer
How to earn badges on the Atlassian Community

How to earn badges on the Atlassian Community

Badges are a great way to show off community activity, whether you’re a newbie or a Champion.

Learn more
Community showcase
Posted Wednesday in Jira

Join our webinar: How 1B+ feature flag events helped us build the new Jira

Every time you release software, there's a bit of risk – that there's a bug, that something breaks, or that the feature doesn't resonate with customers. Feature flagging helps make high stakes s...

116 views 0 3
Join discussion

Atlassian User Groups

Connect with like-minded Atlassian users at free events near you!

Find a group

Connect with like-minded Atlassian users at free events near you!

Find my local user group

Unfortunately there are no AUG chapters near you at the moment.

Start an AUG

You're one step closer to meeting fellow Atlassian users at your local meet up. Learn more about AUGs

Groups near you