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:

    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:

| videos     | id          | PRIMARY         | NULL                  | NULL                   |

Results on Ubuntu:

| 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).

  • I would say: whatever script you use to install/create your tables, that script didn't run properly on the CentOS machine. Another possibility: you don't have InnoDB enabled on CentOS and therefor your foreign keys weren't created.
    – user1822
    Mar 12, 2014 at 17:11
  • SHOW CREATE TABLE will give you more useful information than DESCRIBE TABLE. I think @a_horse_with_no_name is onto something, there... It sounds a bit like one table might show ENGINE=InnoDB and the other ENGINE=MyISAM... If SELECT @@default_storage_engine; returns different values on the two machines, and the setup scripts aren't explicit in their table declarations, that could be the explanation. Mar 12, 2014 at 17:46
  • @a_horse_with_no_name That is indeed the issue! if you submit it as an answer I'll mark it as correct. Thanks so much! Mar 12, 2014 at 18:38
  • (the issue I was referring to was InnoDB vs MyISAM.. not enough rep yet to edit the comment) Mar 12, 2014 at 18:54

1 Answer 1


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)
  • just for future reference: the version of mysql running locally is 5.5.35. On the VM it is 5.1.73. The default storage engine changed in 5.5.5 to InnoDB which is why the tables were being created locally with that engine vs MyISAM on the VM. Solution: make sure the most up-to-date mysql version is installed in vagrant. Thanks again! Mar 12, 2014 at 19:01
  • @watcher: or use a DBMS that fails if it can't comply with a request rather than trying to be smart and do "something" just to avoid an error message (that situation can still happen with current MySQL versions if for some reason the InnoDB engine could not be loaded/started)
    – user1822
    Mar 12, 2014 at 19:09
  • I suppose the possibility of the script not explicitly stating the engine (and running it in different MySQL versions) could be added as a 3rd reason, different than the 1st. Mar 12, 2014 at 19:44
  • @a_horse_with_no_name Not in a properly configured MySQL (NO_ENGINE_SUBSTITUTION, STRICT_TRANS_TABLES, innodb strict mode). The first one is by default in 5.6; the second one, part of the default configuration by Oracle.
    – jynus
    Mar 13, 2014 at 12:05
  • @jynus: there are still situations where foreign keys are silently ignored: sqlfiddle.com/#!2/db0d8/1 (try that with any other DBMS...)
    – user1822
    Mar 13, 2014 at 12:11

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.