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

[Daniel Holcombe]

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?

[Daniel Holcombe]

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";

[Tuncay Senturk]

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?

[Daniel Holcombe]

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]

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

[Daniel Holcombe]

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]

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. 

[Daniel Holcombe]

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

[Tuncay Senturk]

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