I am using "create temporary table [whatever] ...", however, I am doing this in MySQL 4.1+ whereby the temporary table becomes InnoDB by mistake (it has to be searchable via Fulltext indexes, therefore, must be MyISAM). How do I change the temporary table's type from InnoDB to MyISAM either during creation or after?

Thanx
Phil

    CREATE Temporary TABLE test (a INT NOT NULL AUTO_INCREMENT, PRIMARY KEY (a), KEY(b)) TYPE=MyISAM;

    That ought to do the trick.

    Andy

      Got a MySQL syntax error around "TYPE=MyISAM" when I did that. Using MySQL 4.1.10.

      You have an error in your SQL syntax. Check the manual that corresponds to your MySQL server version for the right syntax to use near 'TYPE = MyISAM' at line 3

      Phil

        Of course, to no avail. The moment you take out the "TYPE=MyISAM" line the temporary table is created with no problem - except that it MUST be MyISAM because it will obtain a FULLTEXT index required for the searching routine of my app.

        Phil

          i only have v3.23.58 but
          CREATE Temporary TABLE test (a INT NOT NULL AUTO_INCREMENT, PRIMARY KEY (a)) TYPE=MyISAM;
          works on that.

            That's just it, it works for 3.23.58 and 4.0+ but fails for 4.1+ consistently. I think this is an undocumented MySQL bug.

            Phil

              It is now a documented MySQL bug. I reported this as I believe it's a bug in MySQL.

              Phil

                Write a Reply...