[Building Sakai] SAK-21739 conversion fails at removing duplicates

[Sam Ottenhoff]

Okay, the conversion script is intended for upgrading from 2.8 - 2.9.  If
your database has the correct unique key for that table, all should be fine.

--Sam


On Tue, Nov 6, 2012 at 10:32 AM, Wolfgang Rohregger <
Wolfgang.Rohregger at mci.edu> wrote:

> Hi,
>
> sorry for the late answer.
>
> The database has been at 2.9.x-b07 version after Tomcat startup with
> auto.ddl enabled.
>
> Cheers,
>
> Wolfgang
>
> -----Ursprüngliche Nachricht-----
> Von: Sam Ottenhoff [mailto:ottenhoff at longsight.com]
> Gesendet: Donnerstag, 01. November 2012 23:10
> An: Wolfgang Rohregger
> Cc: sakai-dev at collab.sakaiproject.org
> Betreff: Re: [Building Sakai] SAK-21739 conversion fails at removing
> duplicates
>
> Hi Wolfgang,
>
> Can you share your experiences with the 2.9 conversion scripts?  Did the
> script in fact remove the duplicates?  Did you run the conversion script
> before or after you started up Sakai 2.9's Tomcat?
>
> --Sam
>
> On Tue, Oct 30, 2012 at 2:17 PM, Sam Ottenhoff <ottenhoff at longsight.com>
> wrote:
> > Hi Wolfgang,
> >
> > It appears that the script below did in fact remove the duplicates and
> > then failed when trying to run a final update.  Can you confirm that
> > duplicates were removed and the script created the unique key as
> expected?
> >
> > Did you run the conversion script before or after starting up Sakai
> > 2.9?  If you ran the conversion script after starting the 2.9 Tomcat
> > instance with auto.ddl enabled, I can understand the error on the
> > update.  It should be safe to ignore.  If you ran the conversion
> > script before running Tomcat, I would be curious how you had an item
> > in your database with a locale set to 'default'.
> >
> > --Sam
> >
> > On Tue, Oct 30, 2012 at 11:19 AM, Wolfgang Rohregger
> > <Wolfgang.Rohregger at mci.edu> wrote:
> >>
> >> Hi,
> >>
> >>
> >>
> >> we just tried to apply SAK-21739 conversion script statements to our
> >> local test instance (MySQL). The script comments state that it "will
> >> remove duplicates" and fails in doing so:
> >>
> >>
> >>
> >> -- SAK-21739 Enforce uniqueness on template key + locale
> >>
> >> -- The following lines will remove duplicates from your database by
> >> filtering first on date and then on max id (tie break).
> >>
> >> CREATE TABLE EMAIL_TEMPLATE_ITEM_TEMP (ID INTEGER);
> >>
> >> INSERT INTO EMAIL_TEMPLATE_ITEM_TEMP SELECT ID from
> >> EMAIL_TEMPLATE_ITEM
> >>
> >>   where LAST_MODIFIED not in (select MAX(LAST_MODIFIED) from
> >> EMAIL_TEMPLATE_ITEM GROUP BY template_key, template_locale);
> >>
> >> DELETE FROM EMAIL_TEMPLATE_ITEM WHERE ID IN (SELECT ID FROM
> >> EMAIL_TEMPLATE_ITEM_TEMP);
> >>
> >> DELETE FROM EMAIL_TEMPLATE_ITEM_TEMP;
> >>
> >> INSERT INTO EMAIL_TEMPLATE_ITEM_TEMP SELECT MAX(ID) FROM
> >> EMAIL_TEMPLATE_ITEM GROUP BY UPPER(TEMPLATE_KEY),
> >> UPPER(TEMPLATE_LOCALE);
> >>
> >> DELETE FROM EMAIL_TEMPLATE_ITEM WHERE ID NOT IN (SELECT ID FROM
> >> EMAIL_TEMPLATE_ITEM_TEMP);
> >>
> >> DROP TABLE EMAIL_TEMPLATE_ITEM_TEMP;
> >>
> >>
> >>
> >> alter table EMAIL_TEMPLATE_ITEM add unique key
> >> EMAIL_TEMPLATE_ITEM_KEY_LOCALE_KEY (TEMPLATE_KEY,TEMPLATE_LOCALE);
> >>
> >> -- end of SAK-21739
> >>
> >>
> >>
> >> -- SAK-22223 don't use null as a template key
> >>
> >> update EMAIL_TEMPLATE_ITEM set TEMPLATE_LOCALE = 'default' where
> >> TEMPLATE_LOCALE is null or TEMPLATE_LOCALE = '';
> >>
> >>
> >>
> >> The error output is:
> >>
> >>
> >>
> >> update EMAIL_TEMPLATE_ITEM set TEMPLATE_LOCALE = 'default' where
> >> TEMPLATE_LOCALE is null or TEMPLATE_LOCALE = ''          Error Code:
> 1062.
> >> Duplicate entry 'polls.notifyDeletedOption-default' for key
> >> 'EMAIL_TEMPLATE_ITEM_KEY_LOCALE_KEY'
> >>
> >>
> >>
> >>
> >>
> >> From reading the SAK-21739 JIRA issue comments I understand that in
> >> certain conditions no automatic making of uniqueness is possible. If
> >> that assumption is true, shouldn't the comments in the conversion
> >> script file be appended with something like "this operation can fail
> >> under certain circumstances. In this case you have to delete the
> >> wrong duplicates for yourself."?
> >>
> >>
> >>
> >> Cheers,
> >>
> >>
> >>
> >> Wolfgang
> >>
> >>
> >> _______________________________________________
> >> sakai-dev mailing list
> >> sakai-dev at collab.sakaiproject.org
> >> http://collab.sakaiproject.org/mailman/listinfo/sakai-dev
> >>
> >> TO UNSUBSCRIBE: send email to
> >> sakai-dev-unsubscribe at collab.sakaiproject.org with a subject of
> >> "unsubscribe"
> >
> >
>
-------------- next part --------------
An HTML attachment was scrubbed...
URL: http://collab.sakaiproject.org/pipermail/sakai-dev/attachments/20121106/97c8222e/attachment.html