[Building Sakai] 2.9.0 DB Conversion, MySQL script error

Steve Swinsburg steve.swinsburg at gmail.com
Thu Jan 24 16:06:23 PST 2013 

That rule is not written anywhere for Sakai developers to follow, it is simply a best practice. Whereas the recommendation to use lower case table names on a MySQL setup is. And has been in the install guide since 2007.

And the way the 'rule' is written means it applies only to modifications to a table, not to the initial creation of the table. Why shouldn't the initial table creation be standardised on one case? Why only make people that modify a table follow the rule?

Perhaps then it could avoid SQL mayhem like this table, which has every combination of case type in its fields:

describe chat2_channel;

CHANNEL_ID
PLACEMENT_ID
CONTEXT
CREATION_DATE
title
description
filterType	
filterParam
timeParam
numberParam
placementDefaultChannel
ENABLE_USER_OVERRIDE
migratedChannelId	

cheers,
Steve

On 25/01/2013, at 10:51 AM, Sam Ottenhoff <ottenhoff at longsight.com> wrote:

> The rule is simple: queries should refer to the table in the same case as the creation script.  If someone breaks the rule, we file a JIRA and fix it.
> 
> 
> 
> 
> 
> On Thu, Jan 24, 2013 at 6:42 PM, Steve Swinsburg <steve.swinsburg at gmail.com> wrote:
> That it works fine now on a case sensitive setup doesn't mean it will work fine later, if there are no rules around what case SQL queries should be written in.
> 
> Because there is no rule for developers, there is nothing stopping developer A writing a query in lowercase, and developer B writing a different query in uppercase. And because they are using lower case table names, the discrepancy won't be evident. It will work for the majority of deployers that have their DB setup according to the recommendations, but will cause errors for those that don't. And it may be in a contrib tool.
> 
> I don't believe I mentioned anything to do with a DBA position in regards to a institutional database.
> 
> This has been discussed before with the same outcome [1]. It's recommended to run case insensitive as per the install guide, you are taking a risk (though small and fixable) if you deviate from the recommendations.
> 
> [1] http://sakai-project-mail-list-archives.1343168.n2.nabble.com/Building-Sakai-A-Sensitive-Subject-td7583109.html
> 
> cheers,
> Steve
> 
> 
> 
> On 25/01/2013, at 10:28 AM, Sam Ottenhoff <ottenhoff at longsight.com> wrote:
> 
>> Sakai 2.* works just fine with a Linux MySQL database that uses the defaults (mixed-case tables).  I'm not sure what you're insinuating with "there is no guarantee", but I can guarantee that Sakai CLE works just fine with a mixed-case MySQL database.  We have consistently fixed bugs like the one Cynthia has reported.
>> 
>> A DBA responding that they are unwilling to change their entire institutional MySQL setup seems perfectly reasonable to me.
>> 
>> --Sam
>> 
>> 
>> On Thu, Jan 24, 2013 at 6:12 PM, Steve Swinsburg <steve.swinsburg at gmail.com> wrote:
>> Hi Cynthia,
>> 
>> This may be risky as there is no guarantee that SQL queries will all be of the same case. The admin guide does mention that you should use lower case table names:
>> https://confluence.sakaiproject.org/display/DOC/Sakai+Admin+Guide+-+Database+Configuration+and+Tuning
>> 
>> but I know others have the same settings as you without issue. YMMV.
>> 
>> cheers,
>> Steve
>> 
>> 
>> On 25/01/2013, at 3:14 AM, "Gast, Cynthia (cmw6s)" <cmw6s at eservices.virginia.edu> wrote:
>> 
>>> Hi Mark:
>>> Reporting back...
>>> 
>>> Our DBA would prefer to leave 'lower_case_table_names=0'; this is the setting on all our MySQL databases, including replication databases.  Our Sakai databases are comprised of tables with both uppercase and lowercase names.  From what I read about it, a possible implication of changing this setting to '1' sounds like we might need to also adjust all tablenames to lowercase, for all databases (vs changing this one script to run cleanly for us).  
>>> 
>>> I've noticed the Sakai MySQL database conversion scripts typically maintain each tablename's case for backward compatibility.  After further review, the good news is this is the only case I've found for 2.9.0 upgrade (table 'citation_schema_field') where I've been required to change the tablename case in a script (sakai_2_8_2-2_8_3_mysql_conversion.sql) to run cleanly for us.  I'll create a JIRA for this citation issue.  
>>> 
>>> Thanks for considering this issue going forward.  
>>> Cynthia Gast
>>> University of Virginia
>>> Sakai Development Team
>>> 
>>> From: sakai-dev-bounces at collab.sakaiproject.org [sakai-dev-bounces at collab.sakaiproject.org] on behalf of Gast, Cynthia (cmw6s) [cmw6s at eservices.virginia.edu]
>>> Sent: Wednesday, January 23, 2013 1:45 PM
>>> To: Mark J. Norton; sakai-dev at collab.sakaiproject.org
>>> Subject: Re: [Building Sakai] 2.9.0 DB Conversion, MySQL script error
>>> 
>>> Thank-you for the information, Mark.  Our 'lower_case_table_names' is currently set to 0.  I'll ask our DBA about this.  
>>> 
>>> BTW, we are running MySQL 5.5.24, and 'SHOW lower_case_table_names;'  threw a syntax error, so to see this variable setting I found I could use:
>>>     show variables;
>>> 
>>> Thanks again,
>>> Cynthia
>>> 
>>> 
>>> From: sakai-dev-bounces at collab.sakaiproject.org [sakai-dev-bounces at collab.sakaiproject.org] on behalf of Mark J. Norton [markjnorton at earthlink.net]
>>> Sent: Wednesday, January 23, 2013 11:31 AM
>>> To: sakai-dev at collab.sakaiproject.org
>>> Subject: Re: [Building Sakai] 2.9.0 DB Conversion, MySQL script error
>>> 
>>> Have a look at http://www.google.com/url?q=http%3A%2F%2Fdev.mysql.com%2Fdoc%2Frefman%2F5.0%2Fen%2Fidentifier-case-sensitivity.html&sa=D&sntz=1&usg=AFrqEzdg2e7ezXlIZjIiXeakJy7WhxejlA
>>> 
>>> There is a MySQL system variable called lower_case_table_names that should be to 1:  not case sensitive.  You can view the current value using this query:
>>> 
>>>     SHOW lower_case_table_names;
>>> 
>>> The variable can be set in your my.cnf file.
>>> 
>>> - Mark Norton
>>> 
>>> On 1/23/2013 11:01 AM, Gast, Cynthia (cmw6s) wrote:
>>>> Hello:
>>>> 
>>>> In running the MySQL DB upgrade SQL scripts for a 2.9.0 upgrade (from 2.8.1), I received an error for the following script:  sakai_2_8_2-2_8_3_mysql_conversion.sql
>>>>     Error:  ERROR 1146 (42S02): Table 'sakai_29x_dev.citation_schema_field' doesn't exist
>>>> 
>>>> The script attempts to affect one table: update citation_schema_field ...
>>>> 
>>>> The table name needs to be in uppercase for MySQL.  
>>>> 
>>>> Unsure if this has been reported.  If not, should SAK-22700 or SAK-16091 be re-opened, or a new issue opened?
>>>> 
>>>> Attaching the adjusted script, which runs without error after editing the table name.
>>>> 
>>>> Thanks,
>>>> Cynthia Gast, UVa
>>>> 
>>>> 
>>>> 
>>>> 
>>>> 
>>>> _______________________________________________
>>>> 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"
>>> 
>>> _______________________________________________
>>> 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"
>> 
>> 
>> _______________________________________________
>> 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/20130125/fafc3adb/attachment.html

More information about the sakai-dev mailing list