I'm not sure here, but I'm going to go out on a limb. I think your issue might be with your DENY CONTROL
record. See here about half way down the page:
Denying CONTROL permission on a database implicitly denies CONNECT permission on the database. A principal that is denied CONTROL permission on a database will not be able to connect to that database.
I realize that example is for a database, but take it one more granual level. A DENY CONTROL
on a table will deny all privileges on it, I'm guessing. Do a REVOKE CONTROL
to get rid of that and see if that fixes your issue.
If so, you'll have to place the user in a database role or deny them the explicit privileges against the table.
Unfortunately, you cannot force the ordering of columns in a mysqldump. You can, however, use the table INFORMATION_SCHEMA.COLUMNS
. You will need the following:
- MySQL Instance with the old data loaded
- MySQL Instance with the new data loaded
- Some Patience and Scripting
With this setup in mind, here is what you need to do:
STEP 01) Use Query to Present Columns Ordered Per Database/Table
Here is the Query:
SELECT CONCAT(table_schema,'.',table_name),
GROUP_CONCAT(column_name ORDER BY column_name)
FROM information_schema.columns WHERE table_schema NOT IN
('information_schema','performance_schema')
GROUP BY table_schema,table_name;
This will give you every table followed by a CSV list of alphabetically-sorted columns
STEP 02) Launch this Column Query From Both MySQL Instances
IPADDR_OLD=10.1.20.30
IPADDR_NEW=10.1.20.40
SQLSTMT="SELECT CONCAT(table_schema,'.',table_name),"
SQLSTMT="${SQLSTMT} GROUP_CONCAT(column_name ORDER BY column_name)"
SQLSTMT="${SQLSTMT} FROM information_schema.columns WHERE table_schema NOT IN"
SQLSTMT="${SQLSTMT} ('information_schema','performance_schema')"
SQLSTMT="${SQLSTMT} GROUP BY table_schema,table_name"
mysql -u... -p... -h${IPADDR_OLD} -ANe"${SQLSTMT}" > column_listing1.txt
mysql -u... -p... -h${IPADDR_NEW} -ANe"${SQLSTMT}" >> column_listing2.txt
cat column_listing1.txt > column_listing.txt
cat column_listing2.txt >> column_listing.txt
Run the Column Query and Collect the Results in column_listing.txt
STEP 03) Perform Unique Sort ; Check For Duplicate Table Names
sort -u < column_listing.txt > column_listing.unq
UNQ_LINES=`wc -l < column_listing.unq`
TXT_LINES=`wc -l < column_listing.txt`
(( DIF_LINES = (UNQ_LINES + UNQ_LINES - TXT_LINES) / 2))
echo ${DIF_LINES}
If everything is identical, DIF_LINES
should 0. Otherwise, there are duplicates present.
STEP 04) If There Are Duplicates, Find Them
diff column_listing1.txt column_listing2.txt
Give it a Try !!!
UPDATE 2013-03-22 13:04 EDT
You can also check other metadata tables such as
TABLE_CONSTRAINTS
mysql> desc TABLE_CONSTRAINTS;
+--------------------+--------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+--------------------+--------------+------+-----+---------+-------+
| CONSTRAINT_CATALOG | varchar(512) | NO | | | |
| CONSTRAINT_SCHEMA | varchar(64) | NO | | | |
| CONSTRAINT_NAME | varchar(64) | NO | | | |
| TABLE_SCHEMA | varchar(64) | NO | | | |
| TABLE_NAME | varchar(64) | NO | | | |
| CONSTRAINT_TYPE | varchar(64) | NO | | | |
+--------------------+--------------+------+-----+---------+-------+
6 rows in set (0.01 sec)
STATISTICS
mysql> desc STATISTICS;
+---------------+---------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+---------------+---------------+------+-----+---------+-------+
| TABLE_CATALOG | varchar(512) | NO | | | |
| TABLE_SCHEMA | varchar(64) | NO | | | |
| TABLE_NAME | varchar(64) | NO | | | |
| NON_UNIQUE | bigint(1) | NO | | 0 | |
| INDEX_SCHEMA | varchar(64) | NO | | | |
| INDEX_NAME | varchar(64) | NO | | | |
| SEQ_IN_INDEX | bigint(2) | NO | | 0 | |
| COLUMN_NAME | varchar(64) | NO | | | |
| COLLATION | varchar(1) | YES | | NULL | |
| CARDINALITY | bigint(21) | YES | | NULL | |
| SUB_PART | bigint(3) | YES | | NULL | |
| PACKED | varchar(10) | YES | | NULL | |
| NULLABLE | varchar(3) | NO | | | |
| INDEX_TYPE | varchar(16) | NO | | | |
| COMMENT | varchar(16) | YES | | NULL | |
| INDEX_COMMENT | varchar(1024) | NO | | | |
+---------------+---------------+------+-----+---------+-------+
16 rows in set (0.01 sec)
REFERENTIAL_CONSTRAINTS
mysql> desc REFERENTIAL_CONSTRAINTS;
+---------------------------+--------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+---------------------------+--------------+------+-----+---------+-------+
| CONSTRAINT_CATALOG | varchar(512) | NO | | | |
| CONSTRAINT_SCHEMA | varchar(64) | NO | | | |
| CONSTRAINT_NAME | varchar(64) | NO | | | |
| UNIQUE_CONSTRAINT_CATALOG | varchar(512) | NO | | | |
| UNIQUE_CONSTRAINT_SCHEMA | varchar(64) | NO | | | |
| UNIQUE_CONSTRAINT_NAME | varchar(64) | YES | | NULL | |
| MATCH_OPTION | varchar(64) | NO | | | |
| UPDATE_RULE | varchar(64) | NO | | | |
| DELETE_RULE | varchar(64) | NO | | | |
| TABLE_NAME | varchar(64) | NO | | | |
| REFERENCED_TABLE_NAME | varchar(64) | NO | | | |
+---------------------------+--------------+------+-----+---------+-------+
11 rows in set (0.01 sec)
mysql>
Setup queries that perform the same styled comparison
Best Answer
This may not be an answer but worth to check up. Some indexes may have been set to invisible as an alternative to being dropped. These are still maintained by the database but ignored by the optimizer. You'll be able to see them by querying the dictionary, but the compare tool may ignore them.