developed on mysql 5, need to move to mysql 4

I get this error

CREATE TABLE `modx_active_users` (
`internalKey` int( 9 ) NOT NULL default ’0’,
`username` varchar( 50 ) NOT NULL default ’’,
`lasthit` int( 20 ) NOT NULL default ’0’,
`id` int( 10 ) default NULL ,
`action` varchar( 10 ) NOT NULL default ’’,
`ip` varchar( 20 ) NOT NULL default ’’,
PRIMARY KEY ( `internalKey` )
)ENGINE = MYISAM DEFAULT CHARSET = utf8 COMMENT = ’Contains data about active users.’

MySQL said:

#1064 - 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 ’DEFAULT CHARSET=utf8 COMMENT=’Contains data about active users.


I’am not sure where to start

Well, I believe in both mysql 4 and 5 the syntax you need here is :-

[DEFAULT] CHARACTER SET = charset_name

from the manual, not CHARSET as you have in your example, also I don’t think the MODx tables are created like this, at least they aren’t in the 0.9.6.2 setup.sql script I’ve just looked at so I’m not sure how you’ve generated this.

The manual says ’CHARSET’ is a synonym for ’CHARACTER SET’ and is supposed to work - but it wasn’t introduced until mySQL 4.1.0.

The other ’gotcha’ is ’ENGINE = MYISAM’. Again it is a synonym and is supposed to work, but I have had some systems throw an error with it as well. Since MYISAM is the default for mySQL4, I usually just remove it. Or, you can try ’TYPE = MYISAM’.

I discovered that I can export the db from PhpMyAdmin for MySql4.

Unfortunately I still have problems with it. I might try an upgrade.
I will use current server for now but any other ideas are welcomed.

Thx

For what it’s worth, I just tried your CREATE TABLE statement on a MySQL 5.0.45 database, and it worked perfectly. You’re definitely dealing with some version-specific errors. Start deleting portions of the statement and try running it again until you can isolate it...

Good luck!