Migrating tables - translating DESCRIBE to CREATE? 1

[csteinhilber]

I have a need to promote a given table from one mySQL server (dev/staging) to another (production)... as the two boxes CAN NOT point to the same mySQL server for various reasons.

One of the solutions I'm implementing is a webservice running on the production box that receives a table description (produced on the staging box via

DESCRIBE table_name

) and the data to be populated (simple

SELECT * FROM table_name

).

The webservice receptor then creates a temporary table based on the table description it receives, inserts the data, drops any existing table with the destination name if it exists, and renames the temporary table to the destination name.

Everything's working fairly well, except I'm not sure I'm doing it the most efficient way... particularly the "create temporary table". I pretty much take the resultset that's produced by the DESCRIBE function, and loop through it's rows, and parse the fields to assemble a valid table description for CREATE. Is there a way to simply pass CREATE or a similar function the resultset from a DESCRIBE directly? As I say, I've been pretty successful in assembling the CREATE statement manually, but I'm not certain that it will work for ALL (unforeseen) situations.

Or, is there an existing script built by someone who knows more about mySQL than I do that can translate a DESCRIBE resultset to a CREATE statement?

Or, is there a better way to do this altogether? I thought about setting up MASTER/SLAVEs, but this is more of a "promote to production once approved on staging" situation, rather than a "keep production and staging in sync"... so it didn't seem like MASTER/SLAVE was the right way to go.

Anyone?
Thanks much!


-Carl

 

[sleipnir214]

Can you run an external application?

Executing something like:

path/to/mysqldump -h <host> -u <username> --password=<password> --no-data <databasename> <tablename>

Will return the SQL statements necessary to create a table.

Want the best answers? Ask the best questions:

http://www.catb.org/~esr/faqs/smart-questions.html

TANSTAAFL!!

 

[csteinhilber]

Thanks sleipnir.

I'm using ColdFusion, and for some reason it's CFEXECUTE doesn't want to fire off mysqldump (at least under UNIX), from what I can tell.

It does look like exactly what I wanted, though. So I'll keep poking around and seeing if I can't get it to work.

Thanks again!


-Carl

 

[csteinhilber]

Since I couldn't get CFEXECUTE to play nice with the commandline utes, I found an alternative...

A straight SQL statement of

   SHOW CREATE TABLE

tablename

will produce the equivilent of

mysqldump -nodata

Then, in ColdFusion, it's a simple matter of outputting the result within a CFQUERY tag, and the table is created perfectly.

Thanks for your help.





-Carl