[Building Sakai] 2.9.0 DB Conversion, MySQL script error

Thu Jan 24 15:51:45 PST 2013

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 listsakai-dev at collab.sakaiproject.orghttp://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/20130124/5cdff248/attachment.html 