How to install mysql gem on Mac

Download and install the x86 version of MySql, the run the following command:

sudo env ARCHFLAGS="-arch i386" gem install mysql -- \
--with-mysql-dir=/usr/local/mysql --with-mysql-lib=/usr/local/mysql/lib \
--with-mysql-include=/usr/local/mysql/include

This should be enough to have the mysql gem installed

ERROR 1005 (HY000) at line...: Can’t create table ... (errno: 150)

If you get an error simialr to this:

ERROR 1005 (HY000) at line 244: Can’t create table ‘./intranet2/dept.frm’ (errno: 150)

It means that you should turn off the mysql integrity check with this command:

SET FOREIGN_KEY_CHECKS = 0;

In my case was not enough, I had to remove the to foreign key of that table, load the dump and then recreate the foreign keys. The where some orphan rows, some I removed the problem and applied the foreign keys again.

MIT 6.00 Introduction to Computer Science and Programming

The following Youtube video is the first lesson of th 6.00 MIT course. It is a fantastic introduction to computer science.

Find orphan rows in MySql

If you have two tables, with already date on both of them, when you try to apply a foreign key to have referential integrity, you might get this error:

Cannot add or update a child row: a foreign key constraint fails ...

This means that you have one or more rows, in the referenced table, which is missing, so you are trying to reference a row that does not exist. You can use this SQL query to find the missing ids of the referenced table:

SELECT t1.id_referencing_t2
FROM a_table AS t1
LEFT JOIN referenced_table AS t2
ON t1.id_referencing_t2 = t2.primary_id_of_t2
WHERE t2.primary_id_of_t2 IS NULL

You have to substitute the following name with your real one:

id_referencing_t2: the id the reference the primary key on the referenced table.
primary_id_of_t2: the primary key of the referenced table.
a_table: the table that references the referenced table.
referenced_table: is the table that you want to reference, the table that have the missing ids.

The result of the query is the primary key of the rows that points to missing id in the other table. With those ids you can alter the value to a default one, or delete the rows.

Now you should be able to apply referencial integrity to your tables.