MySQL - Impossible to use REFERENCES on mysql 5.7 (but was working on 5.0)
<p>I am trying to add foreign keys to tables in a database. Let's consider the following minimal example: </p>

<pre><code>CREATE DATABASE db_foo;
USE db_foo;
CREATE TABLE a(b VARCHAR(3) PRIMARY KEY);
CREATE TABLE c(d VARCHAR(3), KEY c_ix (d));
ALTER TABLE c ADD CONSTRAINT fk_c_a FOREIGN KEY (d) REFERENCES a(b);
</code></pre>

<p>The code given above works perfectly on a MySQL server with version 5.0.95, but is giving me the following error when I am running it on another server that runs MySQL version 5.7.12:  </p>

<pre><code>ERROR 1142 (42000): REFERENCES command denied to user 'toto'@'xxx.xxx.xx.xx' for table 'a'
</code></pre>

<p>So my first guess was to check if I had the same privileges on both server. This is the output of <code>SHOW GRANTS;</code>: </p>

<p><strong>On the server with MySQL 5.0.95:</strong></p>

<pre><code>+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Grants for toto@%                                                                                                                                                                            |
+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| GRANT USAGE ON *.* TO 'toto'@'%' IDENTIFIED BY PASSWORD '*********'                                                                                          |
| GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, DROP, INDEX, ALTER, CREATE TEMPORARY TABLES, EXECUTE, CREATE VIEW, SHOW VIEW, CREATE ROUTINE, ALTER ROUTINE ON `db\_%`.* TO 'toto'@'%'  |
+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
</code></pre>

<p><strong>On the server with MySQL 5.7.12:</strong></p>

<pre><code>+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Grants for toto@%                                                                                                                                                                                            |
+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| GRANT USAGE ON *.* TO 'toto'@'%'                                                                                                                                                                             |
| GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, DROP, INDEX, ALTER, CREATE TEMPORARY TABLES, EXECUTE, CREATE VIEW, SHOW VIEW, CREATE ROUTINE, ALTER ROUTINE, EVENT, TRIGGER ON `db\_%`.* TO 'toto'@'%' |
+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
</code></pre>

<p>I have no idea why it would work in a case and not in the other, given the fact that it seems that I have the same privileges in both cases. Any suggestion? </p>