Hello!

I would like to know how could I execute several long queries at a time? I'm running a board and do back-ups (export) via phpMyAdmin – it creates a large .sql file which contains all the queries for creating the tables and inserting the rows.

However, when I need to use that backup, I cannot import it into a database neither on the server using phpMyAdmin, nor on my home computer using some graphical SQL program. It gives me the error: "MySQL server has gone away".

What would you suggest me to do?

Thanks,
Janis

You can pass this long .sql file as an input parameter to isql utility.

Check for help on isql utility. You command should be as follows.

isql -S server -U loginname -P password -i your.sql file -o output file

your.sql file - keep that in your current directory from where you execute isql

output file - say result.txt, created under current directory. This paramaeter is provided for writing the result of the transaction, you can check this file after you get the comman prompt back, to see if the .sql file has been completely execute or was that stuck in between due to errors. If any error, you would be notified in that output file.

Hope that helps.
Cheers!

_________________________
-Vijay G
Strive for Perfection

Thank you for the reply!

Is it a MS SQL Server utility? Because I use MySQL and I cannot find anything about isql in the manual.

Janis

Do you specifically want a graphical toll? Because on a command line, you'd simply type:

mysql {your_database_name} < file.sql -u root -p

...and enter root's password when prompted.

(FWIW, happygv's instructions look like those for doing the same thing on an CA Ingress database, if I'm not mistaken).

If the SQL file already contains a reference to the database in question, you can omit that from the command - or indeed add:

"use {my_database_name}"

...at the beginning of the file!

Let us know how you get on...

Take it easy
dan

Mind you, just looking at arecent version of PHPMyAdmin, I see that you can select a text file on your local filesystem to upload and use, instead of just typing in the command.

That's probably your easiest option, with the live DB. (Bear in mind, however, just how effective a demonstration, this is, of how easy it would be for a mallicious user to do serious harm to your data, if they could find their way into your PHPMyAdmin interface - so keep it locked down :) ).

It is not just MS SQL utility. It works for mysql too, even with sybase, I have tried that. It is a generic SQL command prompt utility. You can check that from your comman prompt by typing just isql or isqlw

Cheers!

_________________________
-Vijay G
Strive for Perfection

Daniel, it gave me the same "MySQL server has gone away" error.

happygv, where do I get this isql utility?

Janis

Well, the reason for that is that the connection to the server is being closed during execution of the queries. Unless you are actually leaving this thing running for eight hours (the MySQL default timeout), I'd check that there isn't actually something in the text file which instructing the server to close the connection or otherwise causing the connection to crash. Until you find out why your connection is going over, it more or less doesn't matter how you try to enter the file into the DB.

Here's more about the "MySQL server has gone away" error:
http://dev.mysql.com/doc/mysql/en/Gone_away.html

FWIW, I think what happygv is on about is what Microsoft call the Query Analyser. It is a part of SQL Server - and, indeed, Sybase (SQL Server is based on Sybase, remember).
http://msdn.microsoft.com/library/de...anlzr_1zqq.asp

I found isql.exe in some Firebird installation I found on the Internet. I'm not sure if that's the same...

With a few tries, I get it to work, I think, but all it does is giving a lot of these two errors and then stops:

Single isql command exceeded maximum buffer size
Use CONNECT or CREATE DATABASE to specify a database

Janis

Well, the sql file is just a text file, so as long as what's in the database can be represented as text, you should be able to open it up with any text editor (Wordpad, if you're on Windows, if its any size at all - and it certainly sounds it) and have a read of what's in there and modify anything that's causing the problems. Most of it will just be a huge number of INERST statement, intersperesed with CREATE TABLE stuff.

Either way, if it is a gigantic file, then you could try breaking it into a bunch of smaller ones and execute each of them, in turn, until you isolate where the problem lies.

There aren't any blob fields, or anything like that, are there?