{"id":1879,"date":"2012-04-29T15:31:54","date_gmt":"2012-04-29T19:31:54","guid":{"rendered":"http:\/\/linuxhostingsupport.net\/blog\/?p=1879"},"modified":"2013-03-20T08:38:23","modified_gmt":"2013-03-20T12:38:23","slug":"converting-table-to-innodb-the-used-table-type-doesnt-support-fulltext-indexes","status":"publish","type":"post","link":"https:\/\/linuxhostingsupport.net\/blog\/converting-table-to-innodb-the-used-table-type-doesnt-support-fulltext-indexes","title":{"rendered":"Converting table to InnoDB: &#8220;The used table type doesn&#8217;t support FULLTEXT indexes&#8221;"},"content":{"rendered":"<p>Somtimes you may want to change the Mysql table from MyISAM to InnoDB engine to setup foreign keys, to use row level locks, improve performace etc.<\/p>\n<p>The conversion of the MyISAM table to InnoDB is easy however, if the table is setup with &#8220;FULLTEXT indexes&#8221;, it cannot be converted as this feature is not supported in InnoDB.<\/p>\n<p>If a table is setup with &#8220;FULLTEXT indexes&#8221;, the conversion of table to InnoDB will result in &#8220;The used table type doesn&#8217;t support FULLTEXT indexes&#8221; error message.<\/p>\n<blockquote>\n<pre><strong>mysql&gt; ALTER TABLE test ENGINE=InnoDB;<\/strong>\r\nERROR 1214: The used table type doesn't support FULLTEXT indexes<\/pre>\n<\/blockquote>\n<p>The solution is to remove &#8220;FULLTEXT indexes&#8221; from the table before converting to InnoDB. To <strong>check if the table is setup with FULLTEXT indexes<\/strong>, execute:<\/p>\n<blockquote>\n<pre><strong>mysql&gt; show create table test;<\/strong>\r\n ------------------------\r\n | Table | Create Table\r\n ------------------------\r\n | test | CREATE TABLE `test` (\r\n `col_name` varchar(10) DEFAULT NULL,\r\n FULLTEXT KEY `keyname` (`col_name`)\r\n ) ENGINE=MyISAM DEFAULT CHARSET=latin1 |<\/pre>\n<\/blockquote>\n<p>If FULLTEXT is setup, the output of the above command will display a line as follows:<\/p>\n<blockquote>\n<pre>FULLTEXT KEY `keyname` (`col_name`)<\/pre>\n<\/blockquote>\n<p>Now, <strong>remove &#8220;FULLTEXT&#8221; indexes<\/strong> from the table:<\/p>\n<blockquote>\n<pre><strong>mysql&gt;<\/strong> ALTER TABLE test DROP INDEX keyname;<\/pre>\n<\/blockquote>\n<p>Now, this <strong>table can be converted to InnoDB<\/strong> using the following command:<\/p>\n<blockquote>\n<pre><strong>mysql&gt; ALTER TABLE test ENGINE=InnoDB;<\/strong>\r\n Query OK, 0 rows affected (0.04 sec)\r\n Records: 0\u00a0 Duplicates: 0\u00a0 Warnings: 0<\/pre>\n<\/blockquote>\n","protected":false},"excerpt":{"rendered":"<p>Somtimes you may want to change the Mysql table from MyISAM to InnoDB engine to setup foreign keys, to use row level locks, improve performace etc.<br \/>\nThe conversion of the MyISAM table to InnoDB is easy however, if the table is setup with &#8220;FULLTEXT indexes&#8221;, it cannot be converted as this feature is not supported in InnoDB.<br \/>\nIf a table is setup with &#8220;FULLTEXT indexes&#8221;, the conversion of table to InnoDB will result in &#8220;The used table type doesn&#8217;t support FULLTEXT indexes&#8221; error message.<\/p>\n<p>mysql&gt; ALTER TABLE test ENGINE=InnoDB;<br \/>\nERROR 1214: The [&#8230;]<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"closed","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[111],"tags":[1388,1387,1389,1386],"_links":{"self":[{"href":"https:\/\/linuxhostingsupport.net\/blog\/wp-json\/wp\/v2\/posts\/1879"}],"collection":[{"href":"https:\/\/linuxhostingsupport.net\/blog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/linuxhostingsupport.net\/blog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/linuxhostingsupport.net\/blog\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/linuxhostingsupport.net\/blog\/wp-json\/wp\/v2\/comments?post=1879"}],"version-history":[{"count":13,"href":"https:\/\/linuxhostingsupport.net\/blog\/wp-json\/wp\/v2\/posts\/1879\/revisions"}],"predecessor-version":[{"id":1891,"href":"https:\/\/linuxhostingsupport.net\/blog\/wp-json\/wp\/v2\/posts\/1879\/revisions\/1891"}],"wp:attachment":[{"href":"https:\/\/linuxhostingsupport.net\/blog\/wp-json\/wp\/v2\/media?parent=1879"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/linuxhostingsupport.net\/blog\/wp-json\/wp\/v2\/categories?post=1879"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/linuxhostingsupport.net\/blog\/wp-json\/wp\/v2\/tags?post=1879"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}