Playing around with memory tables

The other day I was discussing about using the alter table statement in MySQL to convert from MyISAM to Memory engine with some of my colleagues. We were just wondering what will really happen if we try issue an alter table statement, which will subsequently try to create a memory table which will have a table size larger that the max_heap_table_size parameter. We were wondering if data truncation will occur after that limit.

So I thought of testing it and I did the following,

initially I set the following in my.cnf
[mysqld]
max_heap_table_size = 8K

then I tried the following,
alter table City2 engine=memory;
ERROR 1114 (HY000): The table '#sql-2bc1_1' is full

and as soon as I checked
show create table City2;
City2 | CREATE TABLE `City2` (
`ID` int(11) NOT NULL auto_increment,
`Name` char(35) NOT NULL default '',
`CountryCode` char(3) NOT NULL default '',
`District` char(20) NOT NULL default '',
`Population` int(11) NOT NULL default '0',
PRIMARY KEY (`ID`)
) ENGINE=MyISAM AUTO_INCREMENT=4080 DEFAULT CHARSET=latin1

It was clear that no changes has taken place and the alter table has completely failed and the table continue to be MyISAM,

After that just to confirm it I did the following,

set global max_heap_table_size=2*1024*1024;

alter table City2 engine=memory;

City2 | CREATE TABLE `City2` (
`ID` int(11) NOT NULL auto_increment,
`Name` char(35) NOT NULL default '',
`CountryCode` char(3) NOT NULL default '',
`District` char(20) NOT NULL default '',
`Population` int(11) NOT NULL default '0',
PRIMARY KEY (`ID`)
) ENGINE=MEMORY AUTO_INCREMENT=4080 DEFAULT CHARSET=latin1

So finally I know for sure that there will be no data truncation when you issue an alter table statement to convert to Memory. Hopefully this will be useful to the guys who are playing around with memory tables these days :)

PS: I tested this on mysql 5.0.27-max-log

Copying tables in MySQL

The other day I was asked by friend on how a table could be copied in mysql, this query made me think that I should list down some interesting yet simple statements which might be important to people.

QUERY 1: If you want to create a new table out of an existing one

create table City2 like City;

This would create a new table with the same structure as City, but the rows in City will not be copied to City2.

QUERY 2: If you want to create a new table along from an existing table along with all the existing data.

create table City3 select * from City;

QUERY 3: If you want to select only a few columns and create a new table from an existing one

create table City4 select Name,CountryCode from City;

QUERY 4: If you want to copy all data from one table to the other

insert into City2 select * from City;

QUERY 5: If you want to copy certain data from one table to the other

insert into City6(Name, CountryCode) select Name, CountryCode from City;

** NOTE in this query we do not have a values part in the insert query.

Eating a humble pie or rather a thosai

Since my childhood days eating thosai was the last thing i wanted to do. Usually ammaa making a thosai was a reason for me to protest and turn the house upside down. Ammaa knowing this fact made sure that she made some alternative food for me when she made thosai for dinner for the rest at home.

But strangely when ever I come down to KL I am made to survive on thosai as thats the most pure vegetarian food that I could find! What a turn of fate, All my life I have been too selective and resisted eating the best thosai which could ever possibly made and now I am made to survive on a very bad version of it! Adding insult to injury I am learning the foolish "art" of eating thosai with folk and spoon, but probably the only positive that happened out of this was that I spent a very long time trying to eat it and eventually my mind started to wander into the past and think about the irony of me eating a humble thosai!

Ammaa surely would love this and would be laughing and would be wondering what has happened to all my demands. So probably to cover up my slip I should say that thosai in KL is tastier, but telling a lie is easier but putting it on paper is impossible.

Student Strikes - On facebook

The other day I was speaking to a foreigner and I fell into an awkward position trying to explain the concept of student strikes in the Sri Lankan universities, which was quiet alien to him, he said "I do not understand the concept of student strike, they are supposed to be at the university at the tax payers expense to study, so why do they strike?".

He continued, if studying is not their priority then they must be freaking students who should be kicked out. I am looking for answers to give him, if i am asked about this again?

Should I say we strike to cover up the sins of our fellow so called "colleague/s" against whom disciplinary action has been taken?

Should I say we our just pawns in a larger political board game?

Should I say that becoming a university student gives us the luxury of wasting the tax payers money at will and then demand for more?

Should I say that we have not prepared for the exam yet and strike is the device used to get the exams postponed?

or should I say that my university is closed because we ran out of toddy?

my intention is not to ridicule the learning organizations, but to make students think on a bigger scale and to make the students to break the shackles and come of out of the mental prison that they have been caged in by systematic brain washing.

I wrote the above and started a discussion on facebook, check out how this proceeds!
http://www.facebook.com/topic.php?uid=3056930281&topic=5018&ref=mf

Tribute to Flori

The first and the most cherished cat of our home passed away on an April fools' day 8 long years ago.

Her remembrance, brings back sweetest memories.

Though we raised many a cat not a single came close to her braveness, cuteness or bestmanners. She made even her formidable opponents(cat opponents, including appa) love and pet her.