<html><body><div style="font-family: arial, helvetica, sans-serif; font-size: 12pt; color: #000000"><div><style>/*<![CDATA[*/p.MsoNormal, li.MsoNormal, div.MsoNormal {
margin: 0.0in;
font-size: 11.0pt;
font-family: Calibri , sans-serif;
}
a:link, span.MsoHyperlink {
color: blue;
text-decoration: underline;
}
p.MsoListParagraph, li.MsoListParagraph, div.MsoListParagraph {
margin-top: 0.0in;
margin-right: 0.0in;
margin-bottom: 0.0in;
margin-left: 0.5in;
font-size: 11.0pt;
font-family: Calibri , sans-serif;
}
span.EmailStyle24 {
font-family: Calibri , sans-serif;
color: windowtext;
}
*.MsoChpDefault {
font-size: 10.0pt;
}
div.WordSection1 {
page: WordSection1;
}
* {
}
* {
}
* {
}
* {
}
* {
}
* {
}
* {
}
* {
}
* {
}
* {
}
* {
}
* {
}
* {
}
* {
}
* {
}
* {
}
* {
}
* {
}
* {
}
* {
}
* {
}
* {
}
* {
}
* {
}
* {
}
* {
}
* {
}
* {
}
* {
}
* {
}
* {
}
* {
}
* {
}
* {
}
* {
}
* {
}
* {
}
* {
}
* {
}
* {
}
* {
}
* {
}
* {
}
* {
}
* {
}
* {
}
* {
}
* {
}
* {
}
* {
}
ol {
margin-bottom: 0.0in;
}
ul {
margin-bottom: 0.0in;
}
/*]]>*/</style></div><div data-marker="__QUOTED_TEXT__"><div style="font-family:'arial' , 'helvetica' , sans-serif;font-size:12pt;color:#000000"><div>Hi Jeremiah<br></div><br>I found one harmless problem with the audit upgrade script - which is the first call:</div><div style="font-family:'arial' , 'helvetica' , sans-serif;font-size:12pt;color:#000000"><strong>call apply_audit_change(0, $$ SELECT 1 $$, true);</strong></div><div style="font-family:'arial' , 'helvetica' , sans-serif;font-size:12pt;color:#000000"><br data-mce-bogus="1"></div><div style="font-family:'arial' , 'helvetica' , sans-serif;font-size:12pt;color:#000000">The line was somehow forgotten there - but, luckily, this is something that does not hurt the upgrade process now, because the only change there (#1, support for partitions in the past) is <strong>create or replace</strong> - hence repeatable.<br></div><div style="font-family:'arial' , 'helvetica' , sans-serif;font-size:12pt;color:#000000"><br data-mce-bogus="1"></div><div style="font-family:'arial' , 'helvetica' , sans-serif;font-size:12pt;color:#000000">I tried to create fresh DB, initialized PUBLIC schema with only the content of <strong>postgres-new-audit.sql</strong> from version 4.4 (original LTS release) and then tried to apply the upgrade script <strong>postgres-new-upgrade-audit.sql</strong> from 4.4.1.</div><div style="font-family:'arial' , 'helvetica' , sans-serif;font-size:12pt;color:#000000"><br data-mce-bogus="1"></div><div style="font-family:'arial' , 'helvetica' , sans-serif;font-size:12pt;color:#000000">I observed no error and the change was applied.</div><div style="font-family:'arial' , 'helvetica' , sans-serif;font-size:12pt;color:#000000"><br data-mce-bogus="1"></div><div style="font-family:'arial' , 'helvetica' , sans-serif;font-size:12pt;color:#000000">Also, trying to call harmless call <strong>apply_change(0, $$ SELECT 1 $$);</strong> on that DB resulted in error - the procedure is not there, which is expected.</div><div style="font-family:'arial' , 'helvetica' , sans-serif;font-size:12pt;color:#000000">But there was no such error running the upgrade script for audit only.</div><div style="font-family:'arial' , 'helvetica' , sans-serif;font-size:12pt;color:#000000"><br data-mce-bogus="1"></div><div style="font-family:'arial' , 'helvetica' , sans-serif;font-size:12pt;color:#000000">How do you apply the script? What tools do you use?</div><div style="font-family:'arial' , 'helvetica' , sans-serif;font-size:12pt;color:#000000"><br data-mce-bogus="1"></div><div style="font-family:'arial' , 'helvetica' , sans-serif;font-size:12pt;color:#000000">Regards<br data-mce-bogus="1"></div><div style="font-family:'arial' , 'helvetica' , sans-serif;font-size:12pt;color:#000000"><br data-mce-bogus="1"></div><div style="font-family:'arial' , 'helvetica' , sans-serif;font-size:12pt;color:#000000">Richard</div><div style="font-family:'arial' , 'helvetica' , sans-serif;font-size:12pt;color:#000000"> <!--StartFragment--><div><p class="MsoNormal" style="margin: 0px;" data-mce-style="margin: 0px;"><span style="font-size: 12pt; font-family: 'arial' , sans-serif; color: black;" data-mce-style="font-size: 12pt; font-family: 'arial' , sans-serif; color: black;">Software Developer</span></p></div><div><p class="MsoNormal" style="margin: 0px;" data-mce-style="margin: 0px;"><span style="font-size: 12pt; font-family: 'arial' , sans-serif; color: black;" data-mce-style="font-size: 12pt; font-family: 'arial' , sans-serif; color: black;">Evolveum</span></p></div><br><br><hr id="zwchr"><div><b>From: </b>"Jeremiah Haywood" <jhaywo1@ilstu.edu><br><b>To: </b>"Richard Richter" <virgo@evolveum.com>, "midPoint General Discussion" <midpoint@lists.evolveum.com><br><b>Sent: </b>Tuesday, February 22, 2022 7:23:06 PM<br><b>Subject: </b>RE: [midPoint] Midpoint Separate Audit Database Issues<br></div><div><br></div><div><div class="WordSection1"><p class="MsoNormal">Hi Richard,</p><p class="MsoNormal"> </p><p class="MsoNormal">Working with our DBA team they have stated that apply_audit_change() makes a call to the apply_change() procedure. Running the script against the audit table returned with the following:</p><p class="MsoNormal"> </p><p class="MsoNormal">ERROR: procedure apply_change(integer, unknown, Boolean) does not exist</p><p class="MsoNormal">LINE 13: call apply_change(0, $$ SELECT 1 $$, true);</p><p class="MsoNormal"> </p><p class="MsoNormal">HINT: No procedure matches the given name and argument types. You might need to add explicit type casts.</p><p class="MsoNormal">ERROR: procedure apply_change(integer, unknown) does not exist</p><p class="MsoNormal">LINE 1: call apply_change(1, $aa</p><p class="MsoNormal"> </p><p class="MsoNormal">HINT: No procedure matches the given name and argument types. You might need to add explicit type casts.</p><p class="MsoNormal"> </p><p class="MsoNormal">Please disregard the “m_org” error, this appeared after attempting to add the apply_change() procedure to the audit table.</p><p class="MsoNormal"> </p><p class="MsoNormal">Thank you,</p><p class="MsoNormal"> </p><p class="MsoNormal">Jeremiah Haywood</p><p class="MsoNormal">Lead IAM Administrator</p><p class="MsoNormal">Office of Technology Solutions | Illinois State University</p><p class="MsoNormal">Phone Number (309) 438-3829</p><p class="MsoNormal"> </p><p class="MsoNormal"> </p><p class="MsoNormal"> </p><div><div style="border:none;border-top:solid #e1e1e1 1pt;padding:3pt 0in 0in 0in"><p class="MsoNormal"><b>From:</b> Richard Richter <virgo@evolveum.com> <br><b>Sent:</b> Tuesday, February 22, 2022 11:09 AM<br><b>To:</b> midPoint General Discussion <midpoint@lists.evolveum.com><br><b>Cc:</b> Haywood, Jeremiah <jhaywo1@ilstu.edu><br><b>Subject:</b> Re: [midPoint] Midpoint Separate Audit Database Issues</p></div></div><p class="MsoNormal"> </p><table class="MsoNormalTable" style="width:100%" width="100%" cellspacing="0" cellpadding="0" border="0" align="left"><tbody><tr><td style="background:#a6a6a6;padding:5.25pt 1.5pt 5.25pt 1.5pt"></td><td style="width:100%;background:#eaeaea;padding:5.25pt 3.75pt 5.25pt 11.25pt" width="100%"><div><p class="MsoNormal"><span style="font-size:9pt;color:#212121">You don't often get email from </span><span style="color:black"><a href="mailto:virgo@evolveum.com" rel="nofollow noopener noreferrer nofollow noopener noreferrer" target="_blank"><span style="font-size:9pt">virgo@evolveum.com</span></a></span><span style="font-size:9pt;color:#212121">. </span><span style="color:black"><a href="http://aka.ms/LearnAboutSenderIdentification" rel="nofollow noopener noreferrer nofollow noopener noreferrer" target="_blank"><span style="font-size:9pt">Learn why this is important</span></a></span><span style="font-size:9pt;color:#212121"></span></p></div></td><td style="width:56.25pt;background:#eaeaea;padding:5.25pt 3.75pt 5.25pt 3.75pt" width="75"></td></tr></tbody></table><div><div><p class="MsoNormal" style="background:white"><b><span style="font-size:10.5pt;font-family:'arial' , sans-serif;color:#eb0000">[This message came from an external source. If suspicious, report to </span></b><span style="color:black"><a href="mailto:abuse@ilstu.edu" rel="nofollow noopener noreferrer nofollow noopener noreferrer" target="_blank"><b><span style="font-size:10.5pt;font-family:'arial' , sans-serif">abuse@ilstu.edu</span></b></a></span><b><span style="font-size:10.5pt;font-family:'arial' , sans-serif;color:#eb0000">] </span></b></p></div><div><div><div><p class="MsoNormal"><span style="font-size:12pt;font-family:'arial' , sans-serif;color:black">Hi Jeremiah</span></p></div><div><p class="MsoNormal"><span style="font-size:12pt;font-family:'arial' , sans-serif;color:black"> </span></p></div><div><p class="MsoNormal"><span style="font-size:12pt;font-family:'arial' , sans-serif;color:black">This sounds more like running the wrong script on the wrong DB. There is no apply_change() in the audit upgrade script as seen here: </span><a href="https://nam02.safelinks.protection.outlook.com/?url=https%3A%2F%2Fgithub.com%2FEvolveum%2Fmidpoint%2Fblob%2Fsupport-4.4%2Fconfig%2Fsql%2Fnative-new%2Fpostgres-new-upgrade-audit.sql&data=04%7C01%7C%7C7a793c7a11594e104c0c08d9f626084a%7C085f983a0b694270b71d10695076bafe%7C1%7C0%7C637811466862005209%7CUnknown%7CTWFpbGZsb3d8eyJWIjoiMC4wLjAwMDAiLCJQIjoiV2luMzIiLCJBTiI6Ik1haWwiLCJXVCI6Mn0%3D%7C3000&sdata=EIqprl0cY5NQoC7v7VDQZfB7Th6WhfUWsh8odOnyMUc%3D&reserved=0" rel="nofollow noopener noreferrer nofollow noopener noreferrer" target="_blank"><span style="font-size:12pt;font-family:'arial' , sans-serif">https://github.com/Evolveum/midpoint/blob/support-4.4/config/sql/native-new/postgres-new-upgrade-audit.sql</span></a><span style="font-size:12pt;font-family:'arial' , sans-serif;color:black"></span></p></div><div><p class="MsoNormal"><span style="font-size:12pt;font-family:'arial' , sans-serif;color:black">I also check unlikely possibility of some wrong build, but my local extracted build has the file <strong><span style="font-family:'arial' , sans-serif">...\midpoint-4.4.1\doc\config\sql\native-new\postgres-new-upgrade-audit.sql</span></strong> - also without any <strong><span style="font-family:'arial' , sans-serif">apply_change</span></strong>, only <strong><span style="font-family:'arial' , sans-serif">apply_audit_change</span></strong>.</span></p></div><div><p class="MsoNormal"><strong><span style="font-size:12pt;font-family:'arial' , sans-serif;color:black">m_org</span></strong><span style="font-size:12pt;font-family:'arial' , sans-serif;color:black"> upgrade is in the main upgrade script, so it seems like it was run against the audit database.</span></p></div><div><p class="MsoNormal"><span style="font-size:12pt;font-family:'arial' , sans-serif;color:black"> </span></p></div><div><p class="MsoNormal"><span style="font-size:12pt;font-family:'arial' , sans-serif;color:black">Weren't the scripts overwritten accidentally with one another? Please, check their content as well, and compare them with the original files from the distribution package - of course, just the beginning and end is more than enough.</span></p></div><div><p class="MsoNormal"><span style="font-size:12pt;font-family:'arial' , sans-serif;color:black"> </span></p></div><div><p class="MsoNormal"><span style="font-size:12pt;font-family:'arial' , sans-serif;color:black">Regards</span></p></div><div><p class="MsoNormal"><span style="font-size:12pt;font-family:'arial' , sans-serif;color:black"> </span></p></div><div><p class="MsoNormal"><span style="font-size:12pt;font-family:'arial' , sans-serif;color:black">Richard Richter</span></p></div><div><p class="MsoNormal"><span style="font-size:12pt;font-family:'arial' , sans-serif;color:black">Software Developer</span></p></div><div><p class="MsoNormal"><span style="font-size:12pt;font-family:'arial' , sans-serif;color:black">Evolveum</span></p></div><div><p class="MsoNormal"><span style="font-size:12pt;font-family:'arial' , sans-serif;color:black"> </span></p></div><div><p class="MsoNormal"><span style="font-size:12pt;font-family:'arial' , sans-serif;color:black"> </span></p></div><div class="MsoNormal" style="text-align:center" align="center"><span style="font-size:12pt;font-family:'arial' , sans-serif;color:black"><hr width="100%" size="2" align="center"></span></div><div><p class="MsoNormal"><b><span style="font-size:12pt;font-family:'arial' , sans-serif;color:black">From: </span></b><span style="font-size:12pt;font-family:'arial' , sans-serif;color:black">"midPoint General Discussion" <</span><a href="mailto:midpoint@lists.evolveum.com" rel="nofollow noopener noreferrer nofollow noopener noreferrer" target="_blank"><span style="font-size:12pt;font-family:'arial' , sans-serif">midpoint@lists.evolveum.com</span></a><span style="font-size:12pt;font-family:'arial' , sans-serif;color:black">><br><b>To: </b>"midPoint General Discussion" <</span><a href="mailto:midpoint@lists.evolveum.com" rel="nofollow noopener noreferrer nofollow noopener noreferrer" target="_blank"><span style="font-size:12pt;font-family:'arial' , sans-serif">midpoint@lists.evolveum.com</span></a><span style="font-size:12pt;font-family:'arial' , sans-serif;color:black">><br><b>Cc: </b>"Jeremiah Haywood" <</span><a href="mailto:jhaywo1@ilstu.edu" rel="nofollow noopener noreferrer nofollow noopener noreferrer" target="_blank"><span style="font-size:12pt;font-family:'arial' , sans-serif">jhaywo1@ilstu.edu</span></a><span style="font-size:12pt;font-family:'arial' , sans-serif;color:black">><br><b>Sent: </b>Tuesday, February 22, 2022 5:41:27 PM<br><b>Subject: </b>[midPoint] Midpoint Separate Audit Database Issues</span></p></div><div><p class="MsoNormal"><span style="font-size:12pt;font-family:'arial' , sans-serif;color:black"> </span></p></div><div><p class="MsoNormal"><span style="color:black">Hello all,</span></p><p class="MsoNormal"><span style="color:black"> </span></p><p class="MsoNormal"><span style="color:black">At ISU we have been pursuing the implementation of auditing to a separate database with Midpoint. We are running into some issues when upgrading from 4.4 to 4.4.1 where it seems that there are procedures referenced in postgres-new-upgrade-audit.sql that only exist in the main database. Has anyone come across similar issues?</span></p><p class="MsoNormal"><span style="color:black"> </span></p><p class="MsoNormal"><span style="color:black">Steps taken so far:</span></p><p class="MsoNormal"><span style="color:black"> </span></p><p class="MsoNormal"><span style="color:black">4.4:</span></p><ul style="margin-top:0in" type="disc"><li class="MsoListParagraph" style="color:black;margin-left:0in">postgres-new.sql on main db</li><li class="MsoListParagraph" style="color:black;margin-left:0in">postgres-new-quartz.sql on main db</li><li class="MsoListParagraph" style="color:black;margin-left:0in">postgres-new-audit.sql on audit db</li></ul><p class="MsoNormal"><span style="color:black"> </span></p><p class="MsoNormal"><span style="color:black">4.4.1:</span></p><ul style="margin-top:0in" type="disc"><li class="MsoListParagraph" style="color:black;margin-left:0in">postgres-new-upgrade.sql on main db</li><li class="MsoListParagraph" style="color:black;margin-left:0in">postgres-new-upgrade-audit.sql on audit db</li></ul><p class="MsoNormal" style="margin-left:1in;text-indent:-0.25in"><span style="font-size:10pt;font-family:'symbol';color:black">·<span style="font:7pt 'times new roman'"> </span></span><span style="font-size:12pt;font-family:'arial' , sans-serif;color:black"> </span></p><ul style="margin-top:0in" type="disc"><li><ul style="margin-top:0in" type="circle"><li class="MsoListParagraph" style="color:black;margin-left:0in">Ran into issues with procedure apply_change() not existing and relation "m_org" does not exist</li></ul></li></ul><p class="MsoNormal"><span style="color:black"> </span></p><p class="MsoNormal"><span style="color:black">Thank you,</span></p><p class="MsoNormal"><span style="color:black"> </span></p><p class="MsoNormal"><span style="color:black">Jeremiah Haywood</span></p><p class="MsoNormal"><span style="color:black">Lead IAM Administrator</span></p><p class="MsoNormal"><span style="color:black">Office of Technology Solutions | Illinois State University</span></p><p class="MsoNormal"><span style="color:black">Phone Number (309) 438-3829</span></p><p class="MsoNormal"><span style="color:black"> </span></p><p class="MsoNormal"><span style="font-size:12pt;font-family:'arial' , sans-serif;color:black"><br>_______________________________________________<br>midPoint mailing list<br></span><a href="mailto:midPoint@lists.evolveum.com" rel="nofollow noopener noreferrer nofollow noopener noreferrer" target="_blank"><span style="font-size:12pt;font-family:'arial' , sans-serif">midPoint@lists.evolveum.com</span></a><span style="font-size:12pt;font-family:'arial' , sans-serif;color:black"><br></span><a href="https://lists.evolveum.com/mailman/listinfo/midpoint" rel="nofollow noopener noreferrer nofollow noopener noreferrer" target="_blank"><span style="font-size:12pt;font-family:'arial' , sans-serif">https://lists.evolveum.com/mailman/listinfo/midpoint</span></a><span style="font-size:12pt;font-family:'arial' , sans-serif;color:black"></span></p></div></div></div></div></div></div></div><br></div></div></body></html>