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.

possible duplicate of [MySql - Convert InnoDB to MyISAM Storage Engine of Database](http://stackoverflow.com/questions/10280754/mysql-convert-innodb-to-myisam-storage-engine-of-database) — Xavjer, Nov 27 '14 at 10:08
As fancyPants wrote, this is only the frontend, the commands are the same, you changed your default, now all newly created tables will be MYISAM, but all existing tables will have to be migrated separately (In the first comment you will find the commands and an example script to do this with php) — Xavjer, Nov 27 '14 at 10:33
Why do you want to do this? There may be a modest performance benefit, but issues of referential integrity, maintenance of constraints, etc. would surely outweigh this. — Strawberry, Nov 27 '14 at 10:44

fancyPants · Accepted Answer · 2014-11-27T10:40:43.333

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 :)

Glad I could help. Please mark my answer as accepted then by clicking the check. Here's a quick guide, how this site works, in case you're interested: http://stackoverflow.com/tour — fancyPants, Nov 27 '14 at 11:19

score 3 · Answer 2 · edited Sep 17 '17 at 19:25

3

Add default_storage_engine=MYISAM; to my.cnf
Restart the mysql server

edited Sep 17 '17 at 19:25

Baum mit Augen

49,044
25
144
182

answered Nov 27 '14 at 10:30

hi @Geetha for cnf plz check this link-http://stackoverflow.com/questions/1712646/i-can-not-find-my-cnf-on-my-windows-computer – Jerry Nov 29 '14 at 09:41

Mysql changing default engine

2 Answers2

Linked

Related