[Deploying Sakai] 2.6.0-2.6.1 mysql conversion

[Steve Swinsburg]

Might be best to go all upper case since that's generally the default  
on Oracle.

We can somewhat enforce the case via the Hibernate mapping (for  
Hibernate based services) simply by specifying the 'table' attribute.

cheers,
Steve


On 27/10/2009, at 3:55 AM, Matthew Jones wrote:

> On the sakai admin guide it makes a recommendation for editing unix
> my.cnf and forcing case insensitivity because there's no way to have
> case sensitivity on Windows.  (lower_case_table_names=1)
> http://confluence.sakaiproject.org/display/DOC/Sakai+Admin+Guide+-+Database+Configuration+and+Tuning#SakaiAdminGuide-DatabaseConfigurationandTuning-CaseSensitivity
>
> It is confusing that all 3 operating systems have different settings  
> for mysqld.
> 0 = General Unix = case sensitive read & write
> 1 = Windows = case insensitive (lowercase) read and case insensitive
> (lowercase) write
> 2 = OSX = case sensitive write & case insensitive (lowercase) read
>
> There is a conversion mentioned on the mysql page (that I've haven't
> tried) to create a script to rename all of your tables to be
> lowercase, if you decide to turn on this option afterward, though
> converting it over to option 1 or 2 from 0 should work without
> converting names. Sakai shouldn't have a problem with tables of either
> case, as it works fine with a windows mysql, but you'd need to have
> the right option in your my.cnf for lookups.
>
> -Matthew
>
> On Mon, Oct 26, 2009 at 11:46 AM, Seth Theriault <slt at columbia.edu>  
> wrote:
>> Hello,
>>
>> Is there are particular reason why we are not recommending a
>> specific case-sensitivity setting for a MySQL database when
>> running Sakai?
>>
>> This issue seems to pop up from time to time and it seems to me
>> that a community recommendation/requirement is in order.
>>
>> Seth
>>
>> Matthew Jones wrote:
>>
>>> Thanks for the feedback. I'll make a comment on the update notes and
>>> make the change in the 2.6.x conversion script for mysql.
>>>
>>> -Matthew
>>>
>>> On Mon, Oct 26, 2009 at 11:31 AM, Tom Hall <thall at brocku.ca> wrote:
>>>> Hi Matthew
>>>>
>>>> Thanks,  I do have the DB to be case sensitive.  I changed the  
>>>> table names
>>>> in the script to be upper case and it ran fine.  I have seen  
>>>> other systems
>>>> with two different tables depending upon case sensitivity for  
>>>> proper
>>>> system operation so I always ask before making that change.
>>>>
>>>> Tom
>>>>
>>>>
>>>>
>>>> On Mon, 26 Oct 2009, Matthew Jones wrote:
>>>>
>>>>> This probably is related to case sensitivity of the table names.  
>>>>> I was
>>>>> testing this on a mysql running on windows which doesn't have
>>>>> distinction for case. I was just talking to Noah and he said  
>>>>> likely,
>>>>> unless you'd had options set, the table names need to be in  
>>>>> upper case
>>>>> for sakai tables.
>>>>>
>>>>> Try making both of the lowercase table names in the script
>>>>> (assignment_submission, assignment_submission_id_temp) into  
>>>>> uppercase
>>>>> names (ASSIGNMENT_SUBMISSION,ASSIGNMENT_SUBMISSION_ID_TEMP) and  
>>>>> see if
>>>>> that fixes it.
>>>>>
>>>>> http://dev.mysql.com/doc/refman/5.0/en/identifier-case-sensitivity.html
>>>>>
>>>>> On Mon, Oct 26, 2009 at 10:44 AM, Tom Hall <thall at brocku.ca>  
>>>>> wrote:
>>>>>> Good Day Everyone
>>>>>>
>>>>>> I have just built ans started to test 2.6.1 on our development  
>>>>>> system.
>>>>>> When I attempted to run the  
>>>>>> sakai_2_6_0-2_6_1_mysql_conversion.sql upgrade
>>>>>> script I got the following error repeated 16 times.
>>>>>>
>>>>>> ERROR 1146 (42S02): Table 'sakai26.assignment_submission'  
>>>>>> doesn't exist
>>>>>>
>>>>>> corresponding to the internationization fix in the  
>>>>>> assignment_submission
>>>>>> talbe.
>>>>>>
>>>>>> This table does not exist on any of our systems.  Are we  
>>>>>> missign something
>>>>>> or is that part of an optional module/tool?
>>>>>>
>>>>>> Thanks
>>>>>>
>>>>>> Tom
>>>>>>
>>>>>>
>>>>>>
>>>>>>
>>>>>> _______________________________________________
>>>>>> production mailing list
>>>>>> production at collab.sakaiproject.org
>>>>>> http://collab.sakaiproject.org/mailman/listinfo/production
>>>>>>
>>>>>> TO UNSUBSCRIBE: send email to production-unsubscribe at collab.sakaiproject.org 
>>>>>>  with a subject of "unsubscribe"
>>>>>>
>>>>>
>>>>
>>> _______________________________________________
>>> production mailing list
>>> production at collab.sakaiproject.org
>>> http://collab.sakaiproject.org/mailman/listinfo/production
>>>
>>> TO UNSUBSCRIBE: send email to production-unsubscribe at collab.sakaiproject.org 
>>>  with a subject of "unsubscribe"
>>>
>>>
>>
> _______________________________________________
> production mailing list
> production at collab.sakaiproject.org
> http://collab.sakaiproject.org/mailman/listinfo/production
>
> TO UNSUBSCRIBE: send email to production-unsubscribe at collab.sakaiproject.org 
>  with a subject of "unsubscribe"