Sending system commands to server (mysqldump)

I was asked to implement a feature that allows a user to perform a database dump using a push button on one of the FactoryPMI screens. I’ve read up on the dumping process in general and have found that there is really no query to do it. All examples have referenced the ‘mysqldump’ application that comes with the server.

My question is: Am I able to send a system command to the computer running my FactoryPMI gateway, specifically the mysqldump command?

Also, if there is any way I can do this through a SQL query that would be preferred, as the mysqldump route would not provide any feedback to the user.

Thanks in advance.

Yeah, mysqldump isn’t going to help you because you can’t get the client to invoke programs on the server machine.

The only way I can think of to do it remotely is to do a series of:SELECT * INTO OUTFILE 'file_name' FROM tbl_name for each table. Note that the file path will reference a file on the server, not the client machine.

That works. Perhaps I can do SHOW TABLES and iterate through the result set and pop those into a query. Thanks!

It seems I am having trouble executing this.

This is my query: “SELECT * INTO OUTFILE ‘C:/manual_backups/2009-09-11, backup of alarmlog, by MK.sql’ FROM alarmlog”

I tried executing it with runQuery, runUpdateQuery, and runPrepStmt. Here are the following errors:

Traceback (innermost last):
File “event:actionPerformed”, line 12, in ?
ValueError: SQL error for “SELECT * INTO OUTFILE ‘C:/manual_backups/2009-09-11, backup of alarmlog, by MK.sql’ FROM alarmlog”: ResultSet is from UPDATE. No Data

See attached.

See attached.

Now it seems that runQuery will create the file, but it breaks the for loop (I have this query executed multiple times for different tables).

Any ideas?

Hmm, well that is a challenge. You should be running this via runQuery, but FactoryPMI expects anything run by runQuery to return a dataset, and SELECT INTO OUTFILE does not. JDBC is not allowing you to run the query as an UPDATE (see error #2). The only thing I can think of is to simply put a try…except inside your loop to catch that error for each query.

The try block didn’t suppress the SQL error. Is there anyway to execute a query and not care about the result set? The confusing thing is that it’s still creating the backup file, but breaking the loop.

or, is there I way I can somehow cause the query to not ultimately return an UPDATE set?

perhaps something like

SELECT * FROM (SELECT * INTO OUTFILE 'C:/manual_backups/2009-09-11, backup of alarmlog, by .sql' FROM alarmlog)

I know the syntax is wrong, but I think it illustrates my idea.

Really? You did something like this:

for table in mytables: try: fpmi.db.runQuery("SELECT * INTO OUTFILE...") except ValueError: pass

No, sorry. This seems consistent to me. Its only breaking after it returns successfully from the database and FactoryPMI is trying to pull a result set out of the respons that it breaks - so the backup has already been created. As for breaking the loop, see above.

[quote="karnick"]or, is there I way I can somehow cause the query to not ultimately return an UPDATE set?

perhaps something like ...[snip]...[/quote]
Interesting idea, it might just work. TIAS (try it and see)

Fixed the problem. I originally had a try block with no catch block, which was basically doing nothing for me. I wound up doing this in the end:

			x=1 #do nothing
			x=2 #do more nothing

Unfortunately since I cant access the result set I don’t know if the server was able to perform the backup. The only real issues I could run into are if the file already exists and if the disk is full. I’ll just make sure I timestamp everything down to the second to avoid this. Thanks guys.

Glad you got it working. You could also write a stored procedure that did the backup and returned some success/failure information.

Yeah I was actually 2 minutes away from going that route until I realized the lack of a catch block.