[Using Sakai] Sakai upgrade to 2.9.x mysql question

Tue May 7 07:09:26 PDT 2013

Exactly, you already have the value in your database so when the 2.9.0
conversion script attempts to add it again, you receive the error.

The only way to make the query better would be to add more complex SQL that
first checks for the presence of the value before inserting.  Or for MySQL
we could use "INSERT IGNORE".  Or you can simply run the conversion script
with "mysql -f" which will continue to run the rest of the script despite
the presence of an error.

On Tue, May 7, 2013 at 10:02 AM, Anders Nordkvist
<anders.nordqvist at his.se>wrote:

>  Thanks for clarifying this J****
>
> Then I suppose it is correct in my case with the error because the values
> are already set in the table, and that is why the duplicate error is shown?
> ****
>
> ** **
>
> ** **
>
> Regards ****
>
> Anders Nordkvist ****
>
> System administrator****
>
> University Of Skövde****
>
> Sweden****
>
> ** **
>
> ** **
>
> ** **
>
> ** **
>
> ** **
>
> ** **
>
> ** **
>
> ** **
>
> ** **
>
> *From:* Steve Swinsburg [mailto:steve.swinsburg at gmail.com]
> *Sent:* den 7 maj 2013 13:50
>
> *To:* Anders Nordkvist
> *Cc:* sakai-user at collab.sakaiproject.org
> *Subject:* Re: [Using Sakai] Sakai upgrade to 2.9.x mysql question****
>
>  ** **
>
> Hi Anders,****
>
> ** **
>
> DEFAULT is a reserved word and means use whatever the default value is for
> the column. ****
>
> ** **
>
> That table is defined like this:****
>
> ** **
>
> CREATE TABLE `sakai_realm_function` (****
>
>   `FUNCTION_KEY` int(11) NOT NULL *AUTO_INCREMENT*,****
>
>   `FUNCTION_NAME` varchar(99) NOT NULL,****
>
>   PRIMARY KEY (`FUNCTION_KEY`),****
>
>   UNIQUE KEY `IE_SAKAI_REALM_FUNCTION_NAME` (`FUNCTION_NAME`),****
>
>   KEY `SAKAI_REALM_FUNCTION_KN` (`FUNCTION_KEY`,`FUNCTION_NAME`)****
>
> ) ENGINE=InnoDB AUTO_INCREMENT=197 DEFAULT CHARSET=utf8;****
>
> ** **
>
> So in this case, the DEFAULT value means it will get a value from the auto
> increment sequence.****
>
> ** **
>
> So this SQL is correct. But it may be duplicated which is not correct,
> though can be ignored.****
>
> ** **
>
> cheers,****
>
> Steve****
>
> ** **
>
> On 07/05/2013, at 5:33 PM, Anders Nordkvist <anders.nordqvist at his.se>
> wrote:****
>
>
>
> ****
>
> Hi Steve and other Sakai friendly people,****
>
>  ****
>
> I suppose it should be some kind of DEFAULT set for the FUNCTION_KEY
> column? Or else you can’t use the DEFAULT value when you insert (And its
> set to AUTO_INCREMENT)?****
>
> Can I find information about the Sakai database somewhere?****
>
>  ****
>
> PS. I think I should wait until I file a report because I don’t know how
> its working yet.****
>
>  ****
>
> SAKAI_REALM_FUNCTION | CREATE TABLE `SAKAI_REALM_FUNCTION` (****
>
>   `FUNCTION_KEY` int(11) NOT NULL AUTO_INCREMENT,****
>
>   `FUNCTION_NAME` varchar(99) NOT NULL,****
>
>   PRIMARY KEY (`FUNCTION_KEY`),****
>
>   UNIQUE KEY `IE_SAKAI_REALM_FUNCTION_NAME` (`FUNCTION_NAME`),****
>
>   KEY `SAKAI_REALM_FUNCTION_KN` (`FUNCTION_KEY`,`FUNCTION_NAME`)****
>
> ) ENGINE=InnoDB AUTO_INCREMENT=200 DEFAULT CHARSET=utf8 |****
>
>  ****
>
> I****
>
>  ****
>
> *From:* Steve Swinsburg [mailto:steve.swinsburg at gmail.com]
> *Sent:* den 7 maj 2013 00:32
> *To:* Anders Nordkvist
> *Cc:* sakai-user at collab.sakaiproject.org
> *Subject:* Re: [Using Sakai] Sakai upgrade to 2.9.x mysql question****
>
>  ****
>
> It's probably too late to go back and fix this in the tag but can you file
> a Jira so it can be tracked?****
>
>  ****
>
> Thanks.
>
> Sent from my iPhone****
>
>
> On 07/05/2013, at 0:02, Anders Nordkvist <anders.nordqvist at his.se> wrote:*
> ***
>
>  Thanks for answering,****
>
>  ****
>
> Its the 2.9.0 conversion script and I have already run the 2.7.1 – 2.8.0
> script. We are running sitestats  version 2.1.11. Isnt it strange that the
> script is trying to insert a value like “default” in a table with a column
> with type INT? The values for the two rows are:****
>
>  ****
>
> mysql> select FUNCTION_KEY from SAKAI_REALM_FUNCTION where
> FUNCTION_NAME='sitestats.view';****
>
> +--------------+****
>
> | FUNCTION_KEY |****
>
> +--------------+****
>
> |          186 |****
>
> +--------------+****
>
> 1 row in set (0.00 sec)****
>
>  ****
>
> mysql> select FUNCTION_KEY from SAKAI_REALM_FUNCTION where
> FUNCTION_NAME='sitestats.admin.view';****
>
> +--------------+****
>
> | FUNCTION_KEY |****
>
> +--------------+****
>
> |          187 |****
>
> +--------------+****
>
> 1 row in set (0.00 sec)****
>
>  ****
>
>  ****
>
> Regards****
>
> Anders Nordkvist****
>
> System administrator****
>
> University Of Skövde****
>
> Sweden****
>
>  ****
>
>  ****
>
>  ****
>
>  ****
>
>  ****
>
>  ****
>
> *From:* Steve Swinsburg [mailto:steve.swinsburg at gmail.com<steve.swinsburg at gmail.com>
> ]
> *Sent:* den 6 maj 2013 15:46
> *To:* Anders Nordkvist
> *Cc:* sakai-user at collab.sakaiproject.org
> *Subject:* Re: [Using Sakai] Sakai upgrade to 2.9.x mysql question****
>
>  ****
>
> Hi Anders,****
>
>  ****
>
> What version sitestats are you currently running in 2.7? In what upgrade
> script did you receive this error?****
>
>  ****
>
> Note you can ignore this, as you presumed, the entry already exists.****
>
>  ****
>
> cheers,****
>
> Steve****
>
>  ****
>
>  ****
>
> On 06/05/2013, at 11:28 PM, Anders Nordkvist <anders.nordqvist at his.se>
> wrote:****
>
>
>
>
>
> ****
>
> Hi guys,****
>
>  ****
>
> Im testing a database upgrade from Sakai 2.8.x to 2.9.x (we are going to
> upgrade from 2.7.x to 2.9.x this summer) but I get Mysql errors when
> inserting values. I suppose it is  because there already is a value for
> that column?  ****
>
>  ****
>
> mysql> INSERT INTO SAKAI_REALM_FUNCTION VALUES (DEFAULT,
> 'sitestats.admin.view');****
>
> ERROR 1062 (23000): Duplicate entry 'sitestats.admin.view' for key
> 'IE_SAKAI_REALM_FUNCTION_NAME'****
>
> mysql> INSERT INTO SAKAI_REALM_FUNCTION VALUES (DEFAULT, 'sitestats.view');
> ****
>
> ERROR 1062 (23000): Duplicate entry 'sitestats.view' for key
> 'IE_SAKAI_REALM_FUNCTION_NAME'****
>
> mysql> show create table****
>
>  ****
>
> Regards****
>
> Anders Nordkvist****
>
> System administrator****
>
> University Of Skövde****
>
> Sweden****
>
>  ****
>
> _______________________________________________
> sakai-user mailing list
> sakai-user at collab.sakaiproject.org
> http://collab.sakaiproject.org/mailman/listinfo/sakai-user
>
> TO UNSUBSCRIBE: send email to
> sakai-user-unsubscribe at collab.sakaiproject.org with a subject of
> "unsubscribe"****
>
>  ****
>
>  ** **
>
> _______________________________________________
> sakai-user mailing list
> sakai-user at collab.sakaiproject.org
> http://collab.sakaiproject.org/mailman/listinfo/sakai-user
>
> TO UNSUBSCRIBE: send email to
> sakai-user-unsubscribe at collab.sakaiproject.org with a subject of
> "unsubscribe"
>
-------------- next part --------------
An HTML attachment was scrubbed...
URL: http://collab.sakaiproject.org/pipermail/sakai-user/attachments/20130507/2a0163d8/attachment-0001.html 