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