mySQL, mySQLD, mySQLD_MAX ?

[mothra]

I want to run some transactional stuff like ON DELETE CASCADE etc... so I started looking into it and found that my tables have to be declared as INNODB. Ok fine, so I tried that and still no luck. I poked around some more and if I'm understanding correctly mySQLD is required for this to work. Is that correct? Is this server 'built into' mySQL or is it entirely different? I've searched around and I haven't seen anyone else having issues with transactional triggers not working other than them forgetting to declare their tables as INNODB. Maybe I just need to upgrade here's what I've got:

mySQL 4.0.0
SQLyog front end (not that it matters)
winXP Pro

[Kiwi]

mySqld is the mySql daemon.

Foreign keys were partially implemented in 3.23.44. But there were a bunch of issues that weren't really fixed until 4.1.2 (and one or two others are still floating around).

Take a look here (http://dev.mysql.com/doc/mysql/en/InnoDB_foreign_key_constraints.html).

Several things I would check: Changing myISAM tables to InnoDB are never particularly reliable. You'd be better to create new InnoDB tables and copy the data across. This isn't a huge job and is generally a good idea. You can rename the tables when you're finished to keep you applications happy.
You need to create indexes for all your keys. In your version, you have to do this manually. This is my bet for what's causing your error (from version 4.1.2 this is automatic).
Your version of mySql won't support undate cascades.
Your version of mySql won't support recursive cascades.

Finally, cascades are unrelated to transactions (the main thing that have in common is they're not supported by the myISAM tables). If this doesn't help, try googling ("Foreign Keys" OR "ON DELETE") AND mysql.

<edit>
I forgot to add: there were a lot of major changes to mySql 4.1.2 that related to InnoDB support; plus a series of bug-fixes in 4.1.3. If these are important, I'd highly recommend upgrating to at least 4.1.3.
</edit>

[mothra]

Kiwi, thanks I should upgrade anyway. Also good reading on the link...

[Kiwi]

I just remembered another issue - vaguely.

If I remember rightly, If you've already declared the FKs on myISAM tables, then convert them to InnoDB tables, the FKs will not be enforced in the changed InnoDB tables. I'm not sure if the existence of the FKs is even recognised in any way: but I am sure that it's not enforced.

Secondly, to make this even more fun, you can't drop a FK with ALTER TABLE until version 4.0.13. So if you've already declared the FKs on your myISAM tables, you're forced to create new tables as I suggested in step 1. This was probably why I suggested it, even if the exact reason escaped me at the time.