Wrox Programmer Forums

Need to download code?

View our list of code downloads.

Go Back   Wrox Programmer Forums > PHP/MySQL > MySQL
Password Reminder
Register
Register | FAQ | Members List | Calendar | Search | Today's Posts | Mark Forums Read
MySQL General discussion about the MySQL database.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the MySQL section of the Wrox Programmer to Programmer discussions. This is a community of tens of thousands of software programmers and website developers including Wrox book authors and readers. As a guest, you can read any forum posting. By joining today you can post your own programming questions, respond to other developers’ questions, and eliminate the ads that are displayed to guests. Registration is fast, simple and absolutely free .
DRM-free e-books 300x50
Reply
 
Thread Tools Display Modes
  #1 (permalink)  
Old June 3rd, 2004, 10:08 AM
Authorized User
 
Join Date: Mar 2004
Location: , , Latvia.
Posts: 61
Thanks: 0
Thanked 0 Times in 0 Posts
Default Too large query - what to do?

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

Reply With Quote
  #2 (permalink)  
Old June 3rd, 2004, 10:47 AM
Friend of Wrox
 
Join Date: Jun 2003
Location: Bangalore, KA, India.
Posts: 2,480
Thanks: 0
Thanked 1 Time in 1 Post
Default

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
Reply With Quote
  #3 (permalink)  
Old June 3rd, 2004, 12:14 PM
Authorized User
 
Join Date: Mar 2004
Location: , , Latvia.
Posts: 61
Thanks: 0
Thanked 0 Times in 0 Posts
Default

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
Reply With Quote
  #4 (permalink)  
Old June 3rd, 2004, 12:29 PM
Friend of Wrox
 
Join Date: Jun 2003
Location: , , United Kingdom.
Posts: 256
Thanks: 0
Thanked 0 Times in 0 Posts
Default

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
Reply With Quote
  #5 (permalink)  
Old June 3rd, 2004, 12:34 PM
Friend of Wrox
 
Join Date: Jun 2003
Location: , , United Kingdom.
Posts: 256
Thanks: 0
Thanked 0 Times in 0 Posts
Default

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 :) ).
Reply With Quote
  #6 (permalink)  
Old June 3rd, 2004, 12:38 PM
Friend of Wrox
 
Join Date: Jun 2003
Location: Bangalore, KA, India.
Posts: 2,480
Thanks: 0
Thanked 1 Time in 1 Post
Default

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
Reply With Quote
  #7 (permalink)  
Old June 3rd, 2004, 01:02 PM
Authorized User
 
Join Date: Mar 2004
Location: , , Latvia.
Posts: 61
Thanks: 0
Thanked 0 Times in 0 Posts
Default

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

happygv, where do I get this isql utility?

Janis

Reply With Quote
  #8 (permalink)  
Old June 3rd, 2004, 01:12 PM
Friend of Wrox
 
Join Date: Jun 2003
Location: , , United Kingdom.
Posts: 256
Thanks: 0
Thanked 0 Times in 0 Posts
Default

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
Reply With Quote
  #9 (permalink)  
Old June 3rd, 2004, 01:39 PM
Authorized User
 
Join Date: Mar 2004
Location: , , Latvia.
Posts: 61
Thanks: 0
Thanked 0 Times in 0 Posts
Default

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
Reply With Quote
  #10 (permalink)  
Old June 3rd, 2004, 04:14 PM
Friend of Wrox
 
Join Date: Jun 2003
Location: , , United Kingdom.
Posts: 256
Thanks: 0
Thanked 0 Times in 0 Posts
Default

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?
Reply With Quote
Reply


Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Trackbacks are Off
Pingbacks are On
Refbacks are Off

Similar Threads
Thread Thread Starter Forum Replies Last Post
Procedure too large - Urgent devireddybalu Beginning VB 6 26 April 4th, 2008 03:06 AM
Reading large files ravichandrae Pro Java 1 January 11th, 2008 04:42 AM
Query database with large number of records andyj00 ASP.NET 1.0 and 1.1 Professional 6 June 27th, 2005 09:47 PM
Large Project ken killeen All Other Wrox Books 0 October 27th, 2004 08:43 AM



All times are GMT -4. The time now is 11:53 PM.


Powered by vBulletin®
Copyright ©2000 - 2019, Jelsoft Enterprises Ltd.
© 2013 John Wiley & Sons, Inc.