Updated procedure for creating MySQL FEDERATED tables

Three months ago, I wrote about a procedure to create MySQL FEDERATED tables.

I just added a quick fix that should make the procedure less susceptible to issues relating to bug #23856. To do that, I had to remove the ORDER BY ordinal_position bits in all the calls to GROUP_CONCAT over the rows in the information_schema.COLUMNS table.

If you include the ORDER BY ordinal_position clause, the concatenation result will mess up sometimes. The behaviour can be attenuated somewhat by decreasing value for the group_concat_max_len server variable, but so far, I have not seen a sufficiently large valy of group_concat_max_len that does not display this behaviour.

Omitting the ORDER BY ordinal_position clause does not seem change the order. Rows from information_schema.COLUMNS seem to be ordered by TABLE_SCHEMA, TABLE_NAME, COLUMN and ORDINAL_POSITION anyhow. I hope I can rely on that order, as all sorts of trouble are to be expected when the column order of the local table differs from that of the remote table.

I also added some output so you can see what the procedure is doing. Some steps, like getting the remote metadata, take rather long, and I found some output to be helpful.

The updated procedure can be found here in the usual spot at MySQLForge.

The updated procedure does not yet support creating federated tables using a separate SERVER schema object (see the manual).

Please, try it, and report bugs in the procedure by adding a comment to this blog entry. Thank you!