1

Well I've gotten 100+ answers to my questions on Stack Overflow, but it's time I finally registered and posted my first question!

Alright, I have a table for the users of my program and 11 tables attached to this table with 1:1 and 1:M relationships. Most of the foreign keys are set to ON UPDATE CASCADE ON DELETE RESTRICT. If I delete the center table, it allows me to, despite the foreign keys that should be in place. When I have dealt with relational databases before, I would receive these error messages:

#1217 - Cannot delete or update a parent row: a foreign key constraint fails
#1452 - Cannot add or update a child row: a foreign key constraint fails

Now mind you, this is one time I actually want error messages. I want the functionality of a relational database and all of my tables are InnoDB. Google has not helped me with this and I could not find anything in the MySQL documentation.

NobleUplift
  • 5,631
  • 8
  • 45
  • 87
  • 3
    Do you DROP the table or DELETE a row from the center table? – ypercubeᵀᴹ Aug 21 '11 at 10:50
  • you may want to check if there really is a foreign key constraint – pif Aug 21 '11 at 11:00
  • 1
    Check also the setting of `foreign_key_checks` variable: http://dev.mysql.com/doc/refman/5.1/en/server-system-variables.html#sysvar_foreign_key_checks – ypercubeᵀᴹ Aug 21 '11 at 11:03
  • @ypercube It lets me do both. I can also insert rows in the child table with a foreign key value not in the parent table. `SHOW VARIABLES` gives me `foreign_key_checks ON`. – NobleUplift Aug 22 '11 at 02:38
  • @pif I extracted the `CONSTRAINT... FOREIGN KEY... ON...` from the `CREATE TABLE` and made it an `ALTER TABLE`. It still did not work. – NobleUplift Aug 22 '11 at 02:38
  • @NobleUplift: As @pif suggested, can you try `SHOW CREATE table` and post it here? It will show if the Foreign Key constraints are actually created or not. Can you also post the version of the MySQL server you use? – ypercubeᵀᴹ Aug 22 '11 at 06:56
  • I found the problem in my /var/log/mysqld.log. I didn't catch it before because for a good half of the log it said InnoDB was running fine, then this: `InnoDB: Error: log file ./ib_logfile0 is of different size 0 5243265 bytes InnoDB: than specified in the .cnf file 0 5242880 bytes!` – NobleUplift Aug 22 '11 at 07:10

1 Answers1

0

This post on the MySQL forums came close but did not detail exactly what needs to be done to fix this. Basically:

service mysqld stop
cd /var/lib/mysql
mkdir old
mv ib* old
vim /etc/my.cnf
servie mysqld start

For the vim, make sure innodb_buffer_pool_size, innodb_log_file_size, and innodb_log_buffer_size are set properly. Another post on the MySQL forums had a bit of misconfiguration, namely that innodb_log_file_size needs to be 25% of innodb_buffer_pool_size, so I have 16M, 4M, and 8M for these three respectively.

NobleUplift
  • 5,631
  • 8
  • 45
  • 87