foreign keys not updating

[nabeel]

Hey guys,

I'm not sure what I'm doing wrong. I have about 15 tables, but I'll give one example.

Here's the queries:

CREATE TABLE `phpvms_airlines` (
    `id` INT NOT NULL AUTO_INCREMENT ,
    `code` VARCHAR( 3 ) NOT NULL ,
    `name` VARCHAR( 30 ) NOT NULL ,
    PRIMARY KEY ( `id` ),
    UNIQUE KEY `code` (`code`)
)ENGINE=INNODB;

CREATE TABLE `phpvms_ranks` (
    `rankid` int(11) NOT NULL auto_increment,
    `rank` varchar(32) NOT NULL default '',
    `minhours` smallint(6) NOT NULL default '0',
    PRIMARY KEY  (`rankid`),
    UNIQUE KEY `rank` (`rank`)
)ENGINE=INNODB;

INSERT INTO `phpvms_ranks` VALUES(1, 'New Hire', 0);

CREATE TABLE `phpvms_pilots` (
    `pilotid` int(11) NOT NULL auto_increment,
    `firstname` varchar(25) NOT NULL default '',
    `lastname` varchar(25) NOT NULL default '',
    `email` varchar(32) NOT NULL default '',
    `code` varchar(3) NOT NULL default '',
    `location` varchar(32) NOT NULL default '',
    `password` varchar(32) NOT NULL default '',
    `salt` varchar(32) NOT NULL default '',
    `lastlogin` date NOT NULL default '0000-00-00',
    `totalflights` int(11) NOT NULL default '0',
    `totalhours` float NOT NULL default '0',
    `rank` varchar(32) NOT NULL default 'New Hire',
    `confirmed` enum('y','n') NOT NULL default 'n',
    `retired` enum('y','n') NOT NULL default 'y',
    PRIMARY KEY  (`pilotid`),
    UNIQUE KEY `email` (`email`),
    FOREIGN KEY (`code`) REFERENCES phpvms_airlines(`code`) ON UPDATE CASCADE,
    FOREIGN KEY (`rank`) REFERENCES phpvms_ranks(`rank`) ON UPDATE CASCADE
)ENGINE=INNODB;

So when I change "New Hire" to something else, in the ranks table, it doesn't update in the pilots table.

This same thing whenever I update the other fields.
The engine is correct, it seems. And it seems to be creating the keys (when I mess with them, I do get errors, like if I change the column name or something).

Same thing happens when I update the 'code' in the airlines table.

So, what am I missing, or am I getting the usage/concept completely wrong?

[nabeel]

Example:

mysql> select * from phpvms_airlines;
+----+------+--------+
| id | code | name   |
+----+------+--------+
|  1 | VMS  | phpvms |
+----+------+--------+
1 row in set (0.00 sec)

mysql> select pilotid, code from phpvms_pilots;
+---------+------+
| pilotid | code |
+---------+------+
|       1 | VMS  |
+---------+------+
1 row in set (0.00 sec)

mysql> update phpvms_airlines set code='VMX' where code='VMS'; select pilotid,code from phpvms_pilots;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

+---------+------+
| pilotid | code |
+---------+------+
|       1 | VMS  |
+---------+------+
1 row in set (0.00 sec)

mysql> select * from phpvms_airlines;
+----+------+--------+
| id | code | name   |
+----+------+--------+
|  1 | VMX  | phpvms |
+----+------+--------+
1 row in set (0.00 sec)

As you can see, there's no change

[nabeel]

And another aside to my question - is it safe to have InnoDB, from a distribution standpoint, or should I just forget the foreign keys and stick to myisam. Or worded better, is InnoDB ok for compatibililty, will everyone have it?

[r937]

i ran your code, and it updated fine for me, on mysql version 4.1.20

what version are you on?

[nabeel]

Odd.

-bash-3.00$ mysql --version
mysql  Ver 14.7 Distrib 4.1.22, for pc-linux-gnu (i686) using readline 4.3

site5 is my host.

[longneck]

please provide the output of:

show variable like 'have%';

[nabeel]

Gives an error:

mysql> show variable like 'have%';
ERROR 1064 (42000): 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 'variable like 'have%'' at line 1

[nabeel]

nvm, I just copy pasted the section:

| have_archive                    | NO                                       |
| have_bdb                        | NO                                       |
| have_blackhole_engine           | NO                                       |
| have_compress                   | YES                                      |
| have_crypt                      | YES                                      |
| have_csv                        | NO                                       |
| have_example_engine             | NO                                       |
| have_geometry                   | YES                                      |
| have_innodb                     | YES                                      |
| have_isam                       | NO                                       |
| have_merge_engine               | YES                                      |
| have_ndbcluster                 | NO                                       |
| have_openssl                    | NO                                       |
| have_query_cache                | YES                                      |
| have_raid                       | NO                                       |
| have_rtree_keys                 | YES                                      |
| have_symlink                    | YES

[longneck]

sorry, that should have been "show variables".

anyway, the only other thing i can think of is that someone has FOREIGN_KEY_CHECKS set to 0. take a look at that variable.

[nabeel]

sorry, that should have been "show variables".

anyway, the only other thing i can think of is that someone has FOREIGN_KEY_CHECKS set to 0. take a look at that variable.

Something weird,

mysql> SET FOREIGN_KEY_CHECKS=1;
Query OK, 0 rows affected (0.00 sec)

mysql> SET FOREIGN_KEY_CHECKS=0;
Query OK, 0 rows affected (0.00 sec)

mysql> SET FOREIGN_KEY_CHECKS=1;
Query OK, 0 rows affected (0.00 sec)

And it's now showing up in SHOW VARIABLES;
This is a problem, I have to enforce this through code now, since it might not be reliable on customer's systems.

[nabeel]

It somehow magically started working. I contacted my host, I'm not sure what they did, but they emailed me back saying there wasn't any issues, but it started working.

So I'm not sure.

But thanks everyone! I guess I'll also run the foreign_keys command first thing after connect.