[Building Sakai] 2.9.0 DB Conversion, MySQL script error

[Gast, Cynthia (cmw6s)]

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<mailto: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<mailto: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/b6ab3a12/attachment.html