Azure-docs: Query error

[ahamedembibe]

[Enter feedback here]
Can you please check anything wrong in this query?

mysql> SET group_concat_max_len = 8192;
Query OK, 0 rows affected (0.02 sec)

mysql> SELECT SchemaName, GROUP_CONCAT(DropQuery SEPARATOR ';\n') as DropQuery, GROUP_CONCAT(AddQuery SEPARATOR ';\n') as AddQuery
-> FROM
-> (SELECT
-> KCU.REFERENCED_TABLE_SCHEMA as SchemaName,
-> KCU.TABLE_NAME,
-> KCU.COLUMN_NAME,
-> CONCAT('ALTER TABLE ', KCU.TABLE_NAME, ' DROP FOREIGN KEY ', KCU.CONSTRAINT_NAME) AS DropQuery,
-> CONCAT('ALTER TABLE ', KCU.TABLE_NAME, ' ADD CONSTRAINT ', KCU.CONSTRAINT_NAME, ' FOREIGN KEY (', KCU.COLUMN_NAME, ') REFERENCES ', KCU.REFERENCED_TABLE_NAME, ' (', KCU.REFERENCED_COLUMN_NAME, ') ON UPDATE ',RC.UPDATE_RULE, ' ON DELETE ',RC.DELETE_RULE) AS AddQuery
-> FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE KCU, information_schema.REFERENTIAL_CONSTRAINTS RC
-> WHERE
-> KCU.CONSTRAINT_NAME = RC.CONSTRAINT_NAME
-> AND KCU.REFERENCED_TABLE_SCHEMA = RC.UNIQUE_CONSTRAINT_SCHEMA
-> AND KCU.REFERENCED_TABLE_SCHEMA = ('SchemaName') Queries
-> GROUP BY SchemaName;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'Queries
GROUP BY SchemaName' at line 13
mysql>

---

Document Details

⚠ Do not edit this section. It is required for docs.microsoft.com ➟ GitHub issue linking.

• ID: 4f155bac-1a8d-184d-6fe8-8784ec0e86bb
• Version Independent ID: 8943d45f-d066-0c23-a01d-e4ef4200f4e7
• Content: Tutorial: Use Azure Database Migration Service for an online migration of RDS MySQL to Azure Database for MySQL
• Content Source: articles/dms/tutorial-rds-mysql-server-azure-db-for-mysql-online.md
• Service: dms
• GitHub Login: @HJToland3
• Microsoft Alias: jtoland

[Karishma-Tiwari-MSFT]

Thanks for the question. We are currently investigating and will update you shortly.

[HJToland3]

@ahamedembibe, in the query above, you should replace 'SchemaName' with the name of your schema. Please let us know if this does not resolve your issue. Thanks!

[HJToland3]

Also, there appears to be an extra open parenthesis - this is the correct query to use - I will update the article accordingly.

SET group_concat_max_len = 8192;
SELECT SchemaName, GROUP_CONCAT(DropQuery SEPARATOR ';\n') as DropQuery, GROUP_CONCAT(AddQuery SEPARATOR ';\n') as AddQuery
FROM
(SELECT
KCU.REFERENCED_TABLE_SCHEMA as SchemaName,
KCU.TABLE_NAME,
KCU.COLUMN_NAME,
CONCAT('ALTER TABLE ', KCU.TABLE_NAME, ' DROP FOREIGN KEY ', KCU.CONSTRAINT_NAME) AS DropQuery,
CONCAT('ALTER TABLE ', KCU.TABLE_NAME, ' ADD CONSTRAINT ', KCU.CONSTRAINT_NAME, ' FOREIGN KEY (', KCU.COLUMN_NAME, ') REFERENCES ', KCU.REFERENCED_TABLE_NAME, ' (', KCU.REFERENCED_COLUMN_NAME, ') ON UPDATE ',RC.UPDATE_RULE, ' ON DELETE ',RC.DELETE_RULE) AS AddQuery
FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE KCU, information_schema.REFERENTIAL_CONSTRAINTS RC
WHERE
KCU.CONSTRAINT_NAME = RC.CONSTRAINT_NAME
AND KCU.REFERENCED_TABLE_SCHEMA = RC.UNIQUE_CONSTRAINT_SCHEMA
AND KCU.REFERENCED_TABLE_SCHEMA = 'SchemaName') Queries
GROUP BY SchemaName;

[HJToland3]

This issue has been addressed in the documentation.

[HJToland3]

@Mike-Ubezzi-MSFT, what more do I need to do on this issue?

[Mike-Ubezzi-MSFT]

@HJToland3 If there are no additional documentation updates to be made then I will go ahead and close it out. There was no PR linked to this issue, which is helpful to know when updates are published. Thank you for the update. Alternatively, you can simply type hashtag please hyphen close and the issue will be closed out.

[Mike-Ubezzi-MSFT]

@ahamedembibe
We will now proceed to close this thread. If there are further questions regarding this matter, please comment and we will gladly continue the discussion. Thank you!