06 Dec

Escaping “Lesson Learned” dealing with MySQL Databases & Case Sensitiviy

Do you ever need to transfer databases between servers? Different OSes?

Two common ways of data migration:
1. Create mysqldump and load it.
2. File transfer using SCP.

Case Sensitivity and Issue:
Yes, that can create big issues when you have to deal with systems having different case sensitivity.
E.g. on linux you can create directories with names “Kedar” or “kedar” which is not possible on windows!
It will be harmful when file-system itself restricting the names to lower case.
MySQL has a variable for that: lower_case_file_system.

Documentation says: This variable describes the case sensitivity of file names on the file system where the data directory is located. OFF means file names are case sensitive, ON means they are not case sensitive. This variable is read only because it reflects a file system attribute and setting it would have no effect on the file system.

Try loading data having different values for lower_case_file_system and you will get it!

How MySQL tries to solves this problem:
It asks user to handle the issue by changing system variable lower_case_table_names accordingly.
Check the options available:
0 : Table names are stored as specified and comparisons are case sensitive.
1 : Table names are stored in lowercase on disk and comparisons are not case sensitive.
2 : Table names are stored as given but compared in lowercase.

So where do I see the problem?
Here control is given to user with the variable lower_case_table_names and ignoring which can definitely lead to issues. These issues can be avoided by putting restrictions.
Also queries working on MAC / Windows may not work well on Linux / Other Flavours; cause problems on application side.

Documentation Says: You should not set this variable to 0 if you are running MySQL on a system that has case-insensitive file names (such as Windows or Mac OS X). If you set this variable to 0 on such a system and access MyISAM tablenames using different lettercases, index corruption may result.

Now, I recently read the change: PERFORMANCE_SCHEMA is renamed to lowercase in MySQL 5.5 to answer a bug [http://bugs.mysql.com/bug.php?id=57609] pointed by Marc Alff’s post [http://marcalff.blogspot.com/].

In one line I wish: lower_case_table_names, RIP!
I wish MySQL does the same for all tables in order to avoid case sensitivity problems :).

I feel MySQL should not allow upper cases. What do you think?

06 Dec

Life At Rest – A few lines for her

:Life at rest:

Oh my Dear,

insomniac for ur sleep,
u’r awesome when its deep,
pale skin, nighty hair,
mighty life and u’r so near…

warm breath, and ur charm,
makes me wait, till u wake,
while u sleep, lemme gaze,
beauty like u and i await….

Kedar's Angel

Kedar's Angel

sun wont rise, birdz wont sing,
oh dear, u oughta rest,
my life, im with u, wont leave,
i know u will rise and i wont laze..

its your time, waiting next door,
i have the keyz, letz go n open,
life.. i am waiting..
life u r resting, waiting..

i see u radiance, felicitous
you glow , weaknesses fading,
feeling u, i was waiting for u..
arise, arise and krex iz with u..

- Krex [with you forever]

PS: This 100th post is dedicated to my angel, my first poem for her!

05 Dec

Kedar and Madhumita Wedding Invitation 25 Dec 2010

Dear God,

You’re almighty and kind. You know everything about me and us. Still I’m writing this formally to you.
With blessings of our parents we’ve decided to “join” each other forever on 25th December 2010. (You know who)

Please execute below commands on our wedding day :)

GRANT ALL PRIVILEGES ON madhumita.* TO 'kedar'@'%' INDENTIFIED BY '******' WITH GRANT OPTION;
GRANT ALL PRIVILEGES ON kedar.* TO 'madhumita'@'%' INDENTIFIED BY '******' WITH GRANT OPTION;

Create table KedarMadhumita like God.BlessedLife;
insert into KedarMadhumita select life from kedar join madhumita on (christmasday) where love is true;
call bless(KedarMadhumita);

I’m always thankful and greatful to you God!

Okay,

Though the God will execute above commands on Christmas day, you my friend is invited to share the joy and happiness of our wedding.
So below is the invite architecture for you. ;)

How to get the Great News:

SELECT gr8news FROM madhumita;

How to get the Invitation:

SELECT invite AS Invitation FROM kedar JOIN madhumita ON (25122010);

How to get the Location:
SELECT venue AS Venue, wedDate AS Wedding_Date, wedtime AS Wedding_Time FROM kedar JOIN madhumita ON (25122010);

Wanna know a bit about our likings?

SELECT kedar.likes Kedar_Likes FROM kedar JOIN madhumita ON (25122010);
SELECT madhumita.likes Madhumita_Likes FROM kedar JOIN madhumita ON (25122010);


and ofcourse we like each other as well :)

Have doubts? What is there on 25th December? Contact me:
SELECT email as Email, phone AS Phone FROM kedar JOIN madhumita ON (25122010);

I’m sure many of you will try to execute:

SELECT * FROM OurStory;
0 Rows Affected. You do not have sufficient privileges. Please contact Kedar.

:)

Okay enough !! For those who hate reading above stuff, below is clear-cut invitation for you :)

So here you go with Official Wedding Invite Card:

Kedar Madhumita Wedding Invitation

Kedar Madhumita Wedding Invitation

Thanks,
Kedar & Madhumita.

PS: Closed Event. Invitees Only. :)

-- Kedar Vaijanapurkar --