[Building Sakai] 2.9.0 DB Conversion, MySQL script error

[Steve Swinsburg]

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"

-------------- next part --------------
An HTML attachment was scrubbed...
URL: http://collab.sakaiproject.org/pipermail/sakai-dev/attachments/20130125/27817082/attachment.html