Mysql changing default engine

Question

How to change the mysql engine to MYISAM. Now I am having mysql with INNODB but I want to change the engine to MYISAM. What i have to do?

CREATE TABLE `classifieds_category` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `site_id` int(11) NOT NULL,
  `template_prefix` varchar(200) NOT NULL,
  `name` varchar(200) NOT NULL,
  `slug` varchar(50) NOT NULL,
  `enable_contact_form_upload` tinyint(1) NOT NULL DEFAULT '0',
  `contact_form_upload_max_size` int(11) NOT NULL DEFAULT '1048576',
  `contact_form_upload_file_extensions` varchar(200) NOT NULL DEFAULT 'txt,doc,odf,pdf',
  `images_max_count` int(11) NOT NULL DEFAULT '0',
  `images_max_width` int(11) NOT NULL DEFAULT '1024',
  `images_max_height` int(11) NOT NULL DEFAULT '1024',
  `images_max_size` int(11) NOT NULL DEFAULT '1048576',
  `description` longtext NOT NULL,
  `sortby_fields` varchar(200) NOT NULL,
  `sort_order` int(10) unsigned NOT NULL DEFAULT '0',
  PRIMARY KEY (`id`),
  KEY `classifieds_category_6223029` (`site_id`),
  KEY `classifieds_category_56ae2a2a` (`slug`),
  CONSTRAINT `site_id_refs_id_2d06e6c6` FOREIGN KEY (`site_id`) REFERENCES `django_site` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8

I tried to change the engine here. but I don't want to change the every table. Is any settings is there to change engine commonly??

i also run the query like SET default_storage_engine=MYISAM; But no reaction.

Answer 1

Changing the value of the variable default_storage_engine has no effect on existing tables. All it does, is to create new tables with the engine you specified in this variable when you don't specify it in your create table statement. It's just a default value.

Also keep in mind, that you have to distinguish between global and session variable values. To really have MyISAM as default whenever you create a new table, and not just for the current session, do it like this:

SET GLOBAL  default_storage_engine=MYISAM;

If you want to keep the variable to this value even after restarting the server, you have to put follwing line into your default file my.cnf under the section [mysqld]

default_storage_engine = MYISAM

To convert your current tables to MyISAM do this for every table:

ALTER TABLE table_name ENGINE=MyISAM;

But keep in mind, that you foreign key constraint will not work anymore, as MyISAM doesn't support it. It will not complain, it will just ignore it. So you better be sure, you know what you're doing :)

Answer 2

1. Add default_storage_engine=MYISAM; to my.cnf

2. Restart the mysql server