3

I executed the command show table status and found that all my tables had the engine type as MyISAM. I want to change the engine type to InnoDB.

I searched and found the respective query and wrote it as :-

alter table PROJECT_TITLES_ORGANIZATION ENGINE=INNODB;

but this did not work, I got this error message:

You have an error in your SQL syntax near 'ENGINE=INNODB' at line 1.

I 'm using MySql version is 3.23.54a-11.

Please tell me why is this happening and how to make it work?

Jon
  • 428,835
  • 81
  • 738
  • 806
abhinav chourasia
  • 31
  • 1
  • 2

2 Answers2

2

I don't think you can do that in your version. From old MySQL docs for CREATE TABLE . . .

The ENGINE and TYPE options specify the storage engine for the table. ENGINE was added in MySQL 4.0.18 (for 4.0) and 4.1.2 (for 4.1).

Mike Sherrill 'Cat Recall'
  • 91,602
  • 17
  • 122
  • 185
1

Your MySQL release is extremely old so you must pay close attention to the exact version when you seek for information. The keyword to set the store engine in v3 was TYPE rather than ENGINE:

ALTER TABLE PROJECT_TITLES_ORGANIZATION TYPE=InnoDB;

The ENGINE and TYPE options specify the storage engine for the table. ENGINE was added in MySQL 4.0.18 (for 4.0) and 4.1.2 (for 4.1). It is the preferred option name as of those versions, and TYPE has become deprecated. TYPE is supported throughout the 4.x series, but likely will be removed in the future.

http://dev.mysql.com/doc/refman/4.1/en/create-table.html

Update: I don't have 3.23 to test stuff. You should make sure that your server has InnoDB enabled. I guess the simplest way is to create a test table as InnoDB and see if you really get an InnoDB table.

If everything else fails and InnoDB is enabled, you can always dump the table into a text file, edit the SQL code to change the engine and reload the dump. Use your server's mysqldump utility and it should do the trick.

Álvaro González
  • 142,137
  • 41
  • 261
  • 360
  • thanks for the wonderful and extremely valuable reply....in fact i guessed the problem would be 'coz of my MySql version. But the problem still lies unsolved. I actually used type instead of engine as : ALTER TABLE PROJECT_TITLES_ORGANIZATION TYPE=InnoDB; the problem should have been solved. but when i checked it as : show table status..... it still reported the same engine as myisam and not the updated one as innodb. i even restarted mysqld....but it was of no use....... looking forward for solutions....!! – abhinav chourasia Sep 17 '11 at 17:02