[
http://mifosforge.jira.com/browse/MIFOS-1513?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=58519#action_58519
]
Udai Gupta edited comment on MIFOS-1513 at 6/24/10 5:32 AM:
------------------------------------------------------------
Mifos database should work without a problem when migrated from
lower_case_table_name=1, lower_case_table_name=0/2
lower_case_table_names applies to tables only
If set to 0, table names are stored as specified and comparisons are case
sensitive. (unix)
If set to 1, table names are stored in lowercase only on disk and comparisons
are not case sensitive. (windows)
If set to 2, table names are stored as given but compared in lowercase. (mac os
x)
http://dev.mysql.com/doc/refman/5.0/en/server-system-variables.html#sysvar_lower_case_table_names
1 is safe mode of MySQL which means moving from 1 to 0 should not be an issue
but the opposite is not true (a rare case).
form Mifos 1.6+ it's possible to migrating from Windows to Unix
(lower_case_table_names=0)
all you need to do is make MySQL dump of your database and restore it on Unix.
(Copy the data directory might also work but It not a recommended way)
Why it wasn't possible with less than Mifos 1.6?
Bbefore Mifos 1.6 all references to tables were in upper case from code. All
those has been converted to lower case now.
NOTE FOR REPORTS: Any report outside Mifos (which runs on Mifos database) would
need to convert the query to lower case to avoid issues like "XYZ table not
found".
You would need to change the case of your query to lower if you are migrating
from lower_case_table_name=1 to 0.
Now regarding "forcing" a column to always be lower case.
MySQL can store case sensitive column names but the comparison of column names
of MySQL is case insensitive.
So in theory we should not worry about column case in database, but in case if
there any issue arises with lower/upper case mismatch column names we can use a
trigger to restore the mysql dump which will convert all the column names to
lower case.
we can use triggers as the following example shows:
mysql> DELIMITER //
mysql> CREATE TRIGGER lowercased BEFORE INSERT ON case_test FOR EACH ROW BEGIN
SET NEW.word=LOWER(NEW.word); END;//
Query OK, 0 rows affected (0.00 sec)
mysql> INSERT INTO case_test VALUES
('Frank'),('Google'),('froogle'),('flickr'),('FlicKr');
Query OK, 7 rows affected (0.00 sec)
Records: 7 Duplicates: 0 Warnings: 0
mysql> SELECT * FROM case_test;
+---------+
| word |
+---------+
| frank |
| google |
| froogle |
| flickr |
| flickr |
+---------+
7 rows in set (0.00 sec)
was (Author: ugupta):
Mifos database should work without a problem when migrated from
lower_case_table_name=1, lower_case_table_name=0/2
lower_case_table_names applies to tables only
If set to 0, table names are stored as specified and comparisons are case
sensitive. (unix)
If set to 1, table names are stored in lowercase only on disk and comparisons
are not case sensitive. (windows)
If set to 2, table names are stored as given but compared in lowercase. (mac os
x)
http://dev.mysql.com/doc/refman/5.0/en/server-system-variables.html#sysvar_lower_case_table_names
1 is safe mode of MySQL which means moving from 1 to 0 should not be an issue
but the opposite is not true (a rare case).
form Mifos 1.6+ it's possible to migrating from Windows to Unix
(lower_case_table_names=0)
all you need to do is make MySQL dump of your database and restore it on Unix.
(Copy the data directory might also work but It not a recommended way)
Why it wasn't possible with less than Mifos 1.6?
Bbefore Mifos 1.6 all references to tables were in upper case from code. All
those has been converted to lower case now.
NOTE FOR REPORTS: Any report outside Mifos (which runs on Mifos database) would
need to convert the query to lower case to avoid issues like "XYZ table not
found".
You would need to change the case of your query to lower if you are migrating
from lower_case_table_name=1 to 0.
Now regarding "forcing" a column to always be lower case.
MySQL can store case sensitive column names but the comparison of column names
of MySQL is case insensitive.
So in theory we should not worry about column case in database, but in case if
there any issue arises with lower/upper case mismatch column names we can use a
trigger to restore the mysql dump which will convert all the column names to
lower case.
we can use triggers as the following example shows:
mysql> DELIMITER //
mysql> CREATE TRIGGER lowercased BEFORE INSERT ON case_test FOR EACH
ROW BEGIN SET NEW.word=LOWER(NEW.word);
END;//
Query OK, 0 rows affected (0.00 sec)
mysql> INSERT INTO case_test VALUES
('Frank'),('Google'),('froogle'),('flickr'),('FlicKr');
Query OK, 7 rows affected (0.00 sec)
Records: 7 Duplicates: 0 Warnings: 0
mysql> SELECT * FROM case_test;
+---------+
| word |
+---------+
| frank |
| google |
| froogle |
| flickr |
| flickr |
+---------+
7 rows in set (0.00 sec)
> Linux/MySQL deployments broken without lower_case_table_names setting
> ---------------------------------------------------------------------
>
> Key: MIFOS-1513
> URL: http://mifosforge.jira.com/browse/MIFOS-1513
> Project: mifos
> Issue Type: Bug
> Components: Build and Testing
> Affects Versions: Release 1.1, Shamim D
> Environment: Platform: All, OS: Linux
> Reporter: Adam Monsen
> Assignee: Udai Gupta
> Priority: Major
> Fix For: Release E - Iteration 1
>
> Attachments: IssueTracker 1513.gz
>
>
> Mifos/MySQL on Linux and Mac OS X development/deployment requires setting
> lower_case_table_names=1. This need not be a gotcha for non-Windows folks. A
> fix
> for this issue should:
> 1. eliminate the requirement for Linux and Mac OS X users to set the
> lower_case_table_names variable
> 2. provide a unit test to ensure cross-platform compatibility
> 3. provide a run-time check to ensure cross-platform compatibility (if this
> can
> be done in under a second)
> mentioned here:
> http://mifos.org/developers/install-mifos/linux-install-notes
> related discussions:
> http://sourceforge.net/mailarchive/message.php?msg_id=CA91BAB6-7F05-42ED-BC1F-8D08F5464D36%40dravid.net
> http://sourceforge.net/mailarchive/message.php?msg_id=dca7158e0708131214ydbd89cs983baf02efa40c83%40mail.gmail.com
> http://sourceforge.net/mailarchive/message.php?msg_id=6842AFC9-D756-464E-A371-D6124E840A8E%40dravid.net
> more information on the MySQL setting here:
> http://dev.mysql.com/doc/refman/5.0/en/identifier-case-sensitivity.html
--
This message is automatically generated by JIRA.
-
If you think it was sent incorrectly contact one of the administrators:
http://mifosforge.jira.com/secure/Administrators.jspa
-
For more information on JIRA, see: http://www.atlassian.com/software/jira
------------------------------------------------------------------------------
ThinkGeek and WIRED's GeekDad team up for the Ultimate
GeekDad Father's Day Giveaway. ONE MASSIVE PRIZE to the
lucky parental unit. See the prize list and enter to win:
http://p.sf.net/sfu/thinkgeek-promo
_______________________________________________
Mifos-issues mailing list
[email protected]
https://lists.sourceforge.net/lists/listinfo/mifos-issues
