Mysql changes default engine
How to change mysql engine to MYISAM. Now I have mysql with INNODB, but I want to change the engine to MYISAM. What should I 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 every table. Are there any settings for changing the engine?
I also run the query as SET default_storage_engine=MYISAM;
But no response.
source to share
Changing the value of a variable default_storage_engine
does not affect existing tables. All it does is create new tables with the engine specified in this variable, if you do not specify it in the instructions create table
. This is just the default.
Also keep in mind that you must distinguish between the values of the variables global
and session
. To actually have MyISAM by default whenever you create a new table, not just for the current session, follow these steps:
SET GLOBAL default_storage_engine=MYISAM;
If you want to keep the variable at this value even after restarting the server, you must put the follwing line in your default file my.cnf
under[mysqld]
default_storage_engine = MYISAM
To convert your current tables to MyISAM, do this for each table:
ALTER TABLE table_name ENGINE=MyISAM;
But be aware that the foreign key constraint will no longer work as MyISAM does not support it. He will not complain, he will simply ignore it. So you better be sure you know what you are doing :)
source to share