create INNODB table

[Gasolene]

I want to creat tables of type INNODB bacaude i want to cascade deletes.

however when i add "TYPE = INNODB" the table does not create.

here is one of the 7 tables... (works if i remove 'TYPE = INNODB')

mysql_query("CREATE TABLE tblUsers(user_id INT TO_INCREMENT NOT NULL,
account_id INT NOT NULL,
UserName VARCHAR(255) NOT NULL,
Password VARCHAR(255) NOT NULL,
AccessLevel INT NOT NULL,
LastLogin DATE,
IsLoggedIn INT(1),
PRIMARY KEY (user_id),
INDEX (account_id),
FOREIGN KEY (account_id)
REFERENCES tblAccounts (account_id)
ON DELETE CASCADE
) TYPE = INNODB ");

is there something wrong with this sql query?

[Erkilite]

what does tblAccounts look like?

[Gasolene]

mysql_query("CREATE TABLE tblAccounts(account_id INT AUTO_INCREMENT NOT NULL,
LastAccessed DATE,
LastModified DATE,
LastUser VARCHAR(255),
PRIMARY KEY (account_id)
) TYPE = INNODB");

[Erkilite]

try doing this

if(mysql_query($query,$dblink)==false){
echo mysql_error();
exit();
}

see what the error says. it should help you narrow it down.

also here are a few tables i have created using InnoDB. They have a foreign key constrant and I know it works. it might help

$dbquery='CREATE TABLE '.TABLE_TEMPTYPES.' (
id VARCHAR(20) NOT NULL,
title VARCHAR(254) NOT NULL,
PRIMARY KEY (id)) TYPE = InnoDB;';

$dbquery='CREATE TABLE '.TABLE_TEMPLATES.' (
id INT UNSIGNED NOT NULL AUTO_INCREMENT,
title VARCHAR(100) NOT NULL,
template LONGTEXT,
typeid VARCHAR(20) NOT NULL,
PRIMARY KEY (id),
INDEX (typeid),
FOREIGN KEY (typeid) REFERENCES '.TABLE_TEMPTYPES.'(id) ON DELETE RESTRICT ON UPDATE CASCADE) TYPE = InnoDB;';

$dbquery='CREATE TABLE '.TABLE_EVENTS.' (
id INT UNSIGNED NOT NULL auto_increment,
title VARCHAR(254),
edate CHAR(8),
etext TEXT,
templateid INT UNSIGNED,
PRIMARY KEY (id),
INDEX (templateid),
FOREIGN KEY (templateid) REFERENCES '.TABLE_TEMPLATES.'(id) ON DELETE RESTRICT ON UPDATE CASCADE) TYPE = InnoDB;';

[Gasolene]

<?

if(mysql_query($query,$dblink)==false){
echo mysql_error();
exit();
}

?>

error: Warning: Supplied argument is not a valid MySQL-Link resource in /home/######/######.com/dbInvoice/tst.php on line 3

[Erkilite]

/ Connecting, selecting database /

$dblink = mysql_connect("mysql_host", "mysql_user", "mysql_password")
or die("Could not connect : " . mysql_error());

print "Connected successfully";

mysql_select_db("my_database") or die("Could not select database");

are you connecting to the db first?

[Gasolene]

yup

[Drakla]

Well it could be the fact that the first line is cocked up:

mysql_query("CREATE TABLE tblUsers(user_id INT TO_INCREMENT NOT NULL,

change it to AUTO_INCREMENT and the table creates fine.

Also do a query of

SHOW VARIABLES like 'have%'

and check that have_innodb says yes.