CentOS vs Ubuntu – Differences in Information Schema

foreign keyinformation-schemaMySQLUbuntu

I have a Vagrant VM running CentOS 6.5. My localhost is Ubuntu 12.04, both running MySQL. I have the exact same database on both (structured exactly the same, different testing seed data in both). My issue is that running the following query gives different results between them:

SELECT TABLE_NAME, COLUMN_NAME, CONSTRAINT_NAME,
    REFERENCED_TABLE_NAME, REFERENCED_COLUMN_NAME
FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE
WHERE REFERENCED_TABLE_NAME = 'videos' OR
    TABLE_NAME = 'videos'

The output of describe videos; is exactly the same:

mysql> describe videos;
+-----------------+--------------+------+-----+-------------------+-------+
| Field           | Type         | Null | Key | Default           | Extra |
+-----------------+--------------+------+-----+-------------------+-------+
| id              | varchar(36)  | NO   | PRI | NULL              |       |
| created_at      | timestamp    | NO   |     | CURRENT_TIMESTAMP |       |
| updated_at      | timestamp    | YES  |     | NULL              |       |
| title           | varchar(255) | NO   |     | NULL              |       |
| url             | varchar(127) | NO   |     | NULL              |       |
| provider_id     | varchar(36)  | NO   | MUL | NULL              |       |
| deleted_at      | timestamp    | YES  |     | NULL              |       |
+-----------------+--------------+------+-----+-------------------+-------+

My question is should there be any difference between the contents of INFORMATION_SCHEMA.KEY_COLUMN_USAGE for the foreign keys of a table between CentOS and Ubuntu? I have no idea why that query would be returning different results.

Results on CentOS:

+------------+-------------+-----------------+-----------------------+------------------------+
| TABLE_NAME | COLUMN_NAME | CONSTRAINT_NAME | REFERENCED_TABLE_NAME | REFERENCED_COLUMN_NAME |
+------------+-------------+-----------------+-----------------------+------------------------+
| videos     | id          | PRIMARY         | NULL                  | NULL                   |
+------------+-------------+-----------------+-----------------------+------------------------+

Results on Ubuntu:

+-----------------+-------------+----------------------------------+-----------------------+------------------------+
| TABLE_NAME      | COLUMN_NAME | CONSTRAINT_NAME                  | REFERENCED_TABLE_NAME | REFERENCED_COLUMN_NAME |
+-----------------+-------------+----------------------------------+-----------------------+------------------------+
| presenter_video | video_id    | presenter_video_video_id_foreign | videos                | id                     |
| videos          | id          | PRIMARY                          | NULL                  | NULL                   |
| videos          | provider_id | videos_provider_id_foreign       | providers             | id                     |
+-----------------+-------------+----------------------------------+-----------------------+------------------------+

A little background in case it helps: The Vagrant VM and Ubuntu host share a Laravel repository folder, so the codebase is exactly the same. The databases are built using Laravel's artisan migrate command (executing exactly the same code to construct the DB's).

Best Answer

I see two possible reasons:

  1. Your installation script is not correct and failed to create the tables correctly
  2. The CentOS installation does not have InnoDB installed (or activated). MySQL will not tell you about it if you request a non-existing storage engine (or at least not loud and clear)
Related Question