Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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

share|improve this question
    
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. –  a_horse_with_no_name Mar 12 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. –  Michael - sqlbot Mar 12 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! –  watcher Mar 12 at 18:38
    
(the issue I was referring to was InnoDB vs MyISAM.. not enough rep yet to edit the comment) –  watcher Mar 12 at 18:54
add comment

1 Answer 1

up vote 2 down vote accepted

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)
share|improve this answer
    
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! –  watcher Mar 12 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) –  a_horse_with_no_name Mar 12 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. –  ypercube Mar 12 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 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...) –  a_horse_with_no_name Mar 13 at 12:11
show 3 more comments

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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