[Building Sakai] 2.9.0 DB Conversion, MySQL script error

[Sam Ottenhoff]

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/e5816ccd/attachment.html