{"type":"doc","content":[{"type":"heading","attrs":{"level":2},"content":[{"type":"emoji","attrs":{"id":"1f914","text":"\\uD83E\\uDD14","shortName":":thinking:"}},{"text":" Problem","type":"text"}]},{"type":"paragraph","content":[{"text":"For unknown reason Dictionary can contain multiple Terms sharing same ‘term-id’ value. This can cause unpredicted errors during import synchronization, or during editing of Concept.","type":"text"}]},{"type":"heading","attrs":{"level":2},"content":[{"type":"emoji","attrs":{"id":"1f331","text":"\\uD83C\\uDF31","shortName":":seedling:"}},{"text":" Solution","type":"text"}]},{"type":"paragraph","content":[{"text":"To solve this case, we need to update values stored in DB, to achieve this do next steps:","type":"text"}]},{"type":"orderedList","attrs":{"order":1},"content":[{"type":"listItem","content":[{"type":"paragraph","content":[{"text":"First double check if Dictionary has duplicates by executing next SQL, don’t to forget to provide actual Dictionary id instead of ","type":"text"},{"text":"","type":"text","marks":[{"type":"code"}]},{"text":":","type":"text"}]},{"type":"codeBlock","attrs":{"language":"sql"},"content":[{"text":"SELECT a.*\nFROM tw_term a\n JOIN (SELECT term_id, dictionary_oid, COUNT(*)\n FROM tw_term\n GROUP BY term_id, dictionary_oid\n HAVING count(term_id) > 1\n and dictionary_oid = '') b\n ON a.term_id = b.term_id\n AND a.dictionary_oid = b.dictionary_oid\nORDER BY a.term_id","type":"text"}]}]},{"type":"listItem","content":[{"type":"paragraph","content":[{"text":"If you see duplicate records and want to continue then go to step 3","type":"text"}]}]},{"type":"listItem","content":[{"type":"paragraph","content":[{"text":"Stop Tomcat and backup database","type":"text"}]}]},{"type":"listItem","content":[{"type":"paragraph","content":[{"text":"To update duplicate records, we need to launch first script which will generate update scripts, don’t to forget to provide actual Dictionary id instead of ","type":"text"},{"text":"","type":"text","marks":[{"type":"code"}]},{"text":":","type":"text"}]},{"type":"codeBlock","attrs":{"language":"sql"},"content":[{"text":"SELECT (CONCAT('UPDATE tw_term AS a SET a.term_id = CONCAT(\\'', CONCAT(REGEXP_REPLACE(a.term_id, '(\\\\d+-)\\\\d+', '$1')),\n '\\', (SELECT MAX(CONVERT(REGEXP_REPLACE(d.term_id, ''\\\\\\\\d+-(\\\\\\\\d+)'', ''$1'') , UNSIGNED INTEGER)) + 1 FROM (SELECT b.* FROM (SELECT c.term_id FROM tw_term AS c WHERE c.concept_oid = \\'',\n a.concept_oid, '\\') AS b) AS d)) WHERE a.oid = \\'', a.oid, '\\';')) AS script\nFROM tw_term a\n JOIN (SELECT term_id, dictionary_oid\n FROM tw_term\n GROUP BY term_id, dictionary_oid\n HAVING count(term_id) > 1\n and dictionary_oid = '') b\n ON a.term_id = b.term_id\n AND a.dictionary_oid = b.dictionary_oid\nORDER BY a.term_id;","type":"text"}]},{"type":"paragraph","content":[{"text":"In case when there is a lot of records you may save results to file via mysql cli:","type":"text"}]},{"type":"codeBlock","attrs":{"language":"shell"},"content":[{"text":"mysql -h localhost -u user -p --skip-column-names -e \"SELECT (CONCAT('UPDATE tw_term AS a SET a.term_id = CONCAT(\\'', CONCAT(REGEXP_REPLACE(a.term_id, '(\\\\\\\\d+-)\\\\\\\\d+', '\\$1')), '\\', (SELECT MAX(CONVERT(REGEXP_REPLACE(d.term_id, ''\\\\\\\\d+-(\\\\\\\\d+)'', ''\\$1'') , UNSIGNED INTEGER)) + 1 FROM (SELECT b.* FROM (SELECT c.term_id FROM tw_term AS c WHERE c.concept_oid = \\'', a.concept_oid, '\\') AS b) AS d)) WHERE a.oid = \\'', a.oid, '\\';')) AS script FROM tw_term a JOIN (SELECT term_id, dictionary_oid FROM tw_term GROUP BY term_id, dictionary_oid HAVING count(term_id) > 1 and dictionary_oid = '') b ON a.term_id = b.term_id AND a.dictionary_oid = b.dictionary_oid ORDER BY a.term_id;\" > scripts.sql","type":"text"}]},{"type":"paragraph","content":[{"text":"Don’t forget to change ","type":"text"},{"text":"","type":"text","marks":[{"type":"code"}]},{"text":" and ","type":"text"},{"text":"","type":"text","marks":[{"type":"code"}]},{"text":". Please note that for bash script has been added extra escape symbols ","type":"text"},{"text":"\\","type":"text","marks":[{"type":"code"}]},{"text":".","type":"text"}]}]},{"type":"listItem","content":[{"type":"paragraph","content":[{"text":"Copy generated scripts and launch it in your favorite SQL editor program.","type":"text"},{"type":"hardBreak"},{"text":"In case of script file you may run it, like this: ","type":"text"},{"text":"mysql -h localhost -u user -p < scripts.sql","type":"text","marks":[{"type":"code"}]}]}]},{"type":"listItem","content":[{"type":"paragraph","content":[{"text":"Start Tomcat and verify results.","type":"text"}]}]}]},{"type":"paragraph"},{"type":"panel","attrs":{"panelType":"info"},"content":[{"type":"paragraph","content":[{"text":"If Dictionary contains a lot of Terms it can take a significant amount time to scan all records for duplicates.","type":"text"}]}]},{"type":"heading","attrs":{"level":2},"content":[{"type":"emoji","attrs":{"id":"1f4ce","text":"\\uD83D\\uDCCE","shortName":":paperclip:"}},{"text":" Related articles","type":"text"}]},{"type":"extension","attrs":{"layout":"default","extensionType":"com.atlassian.confluence.macro.core","extensionKey":"contentbylabel","parameters":{"macroParams":{"showLabels":{"value":"false"},"max":{"value":"5"},"spaces":{"value":"com.atlassian.confluence.content.render.xhtml.model.resource.identifiers.SpaceResourceIdentifier@277f1a"},"sort":{"value":"modified"},"showSpace":{"value":"false"},"reverse":{"value":"true"},"type":{"value":"page"},"labels":{"value":"kb-troubleshooting-article"},"cql":{"value":"label = \"kb-troubleshooting-article\" and type = \"page\" and space = \"TWKB\""}},"macroMetadata":{"macroId":{"value":"db572073-55f7-4625-93de-ec352d94d669"},"schemaVersion":{"value":"4"},"title":"Content by Label"}},"localId":"8d201597-7956-4a23-b2c1-c41ebdf9e49a"}}],"version":1}

Browser not supported