MySQL Error: ENGINE = MyISAM invalid SQL?

[Megareus Rex]

When trying to install a few hacks, including QuoteIt, Radio Stations, and Multiple Login Detector, I get the following error:

MySQL Error : 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 'ENGINE=MyISAM' at line 15
Error Number : 1064

How can I fix/bypass/etc this? It's preventing me from installing hacks that I want, and likely will affect more in the future. I searched the site, but couldn't find any other instances of this problem.

[Eikinskjaldi]

This is a mysql version incompatibility.

Mysql 5 adds the engine definition to the create table syntax. Mysql 4 (and perhaps 4.1, I am not sure) does not recognise it.

2 solutions
1) upgrade your mysql
2) edit the plugin to remove the engine designation.

[Megareus Rex]

Originally Posted by Eikinskjaldi

This is a mysql version incompatibility.


Mysql 5 adds the engine definition to the create table syntax. Mysql 4 (and perhaps 4.1, I am not sure) does not recognise it.

2 solutions
1) upgrade your mysql
2) edit the plugin to remove the engine designation.

Thanks for the suggestions.

Does removing the engine designation have any effect on the performance of the hack, and/or are there any theoretical reprecussions?

[Eikinskjaldi]

Originally Posted by Megareus Rex

Thanks for the suggestions.

Does removing the engine designation have any effect on the performance of the hack, and/or are there any theoretical reprecussions?

The only theoretical repercussion is if a particular table in the hack is not using the MYISAM storage engine, regardless, the likely effect on performance is minimal. It is possible that the hack has defined an innodb table (quite why escapes me) or a memory table (which is an efficient way of storing data that does not need permanent storage, such as session details). In both cases MYIASM tables will also work.

From the mysql reference manual:
ENGINE was added in MySQL 4.0.18 (for 4.0) and 4.1.2 (for 4.1).

This means you are using a *really old and probably unsupported* version of mysql

[Wayne Luke]

Actually, if you look at the whole error, it is the collation that is causing the error. Language collations are in newer versions and you cannot restore with that information. You need to make the backup with compatibility for the older version.

[Megareus Rex]

Originally Posted by Wayne Luke

Actually, if you look at the whole error, it is the collation that is causing the error. Language collations are in newer versions and you cannot restore with that information. You need to make the backup with compatibility for the older version.

What does that mean, exactly? Is there anything I could edit in the hacks to still have them install? My MySQL version is 4.0.16, btw.

I'm just trying to understand the problem. The MySQL is controlled by the host I have, so I can't really easily upgrade it, though moving to a new host with a newer version is always possible (I've been wanting to move anyways).

Thanks in advance for any help or suggestions.

[Eikinskjaldi]

Originally Posted by Megareus Rex

What does that mean, exactly? Is there anything I could edit in the hacks to still have them install? My MySQL version is 4.0.16, btw.

I'm just trying to understand the problem. The MySQL is controlled by the host I have, so I can't really easily upgrade it, though moving to a new host with a newer version is always possible (I've been wanting to move anyways).

Thanks in advance for any help or suggestions.

I have no idea where Wayne is getting this from, unless there is some hidden extra error message I cannot see. Your mysql version is certainly incompatible with the engine declaration.

A character set defines what actual characters are represented in your strings, so if you were writing in French you would want a character set that supported accents over vowels. A collation determines how the members of the set are compared, for example does "a" come before "A".

Chances are you could delete any reference to these in the create table syntax, and not cause a problem. Obviously, I cannot guarantee this. Were it me, I'd be moving hosts. 4.0 is a poor choice of database for today. It's not even supported anymore.

[Wayne Luke]

In newer versions of MySQL the export of a table would be:

CREATE TABLE `vb_access` (
  `userid` int(10) unsigned NOT NULL default '0',
  `forumid` smallint(5) unsigned NOT NULL default '0',
  `accessmask` smallint(5) unsigned NOT NULL default '0',
  PRIMARY KEY  (`userid`,`forumid`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

For MySQL 4.0 compatibility it would be:

CREATE TABLE `vb_access` (
  `userid` int(10) unsigned NOT NULL default '0',
  `forumid` smallint(5) unsigned NOT NULL default '0',
  `accessmask` smallint(5) unsigned NOT NULL default '0',
  PRIMARY KEY  (`userid`,`forumid`)
) TYPE=InnoDB;

Just export the table with the --compat MYSQL40 flag as show here:
http://www.vbulletin.com/docs/html/m...servers_backup

Or just remove everything after the closing parenthesis for each table declaration. If it isn't specified than MySQL will use its defaults.

[Megareus Rex]

Originally Posted by Wayne Luke

In newer versions of MySQL the export of a table would be:

CREATE TABLE `vb_access` (
  `userid` int(10) unsigned NOT NULL default '0',
  `forumid` smallint(5) unsigned NOT NULL default '0',
  `accessmask` smallint(5) unsigned NOT NULL default '0',
  PRIMARY KEY  (`userid`,`forumid`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

For MySQL 4.0 compatibility it would be:

CREATE TABLE `vb_access` (
  `userid` int(10) unsigned NOT NULL default '0',
  `forumid` smallint(5) unsigned NOT NULL default '0',
  `accessmask` smallint(5) unsigned NOT NULL default '0',
  PRIMARY KEY  (`userid`,`forumid`)
) TYPE=InnoDB;

Just export the table with the --compat MYSQL40 flag as show here:
http://www.vbulletin.com/docs/html/m...servers_backup

Or just remove everything after the closing parenthesis for each table declaration. If it isn't specified than MySQL will use its defaults.

Ah, ok, I'll try that. Thanks for the help so far