Jump to content


Photo

Problem with huge database


  • Please log in to reply
13 replies to this topic

#1 jeffblochjr

jeffblochjr
  • Members
  • PipPip
  • Member
  • 10 posts

Posted 05 January 2003 - 04:40 AM

I am trying to move a mysql database from a site on one server to a new site on a different server. I dumped the old site\'s structure and imported it into a new mysql database on the new web site... but there is 700 meg of data on the old database. 26 text files make up the bulk of this 700 meg, and they must be uploaded to 26 tables on this new mysql database on the new server. When I try to do this via phpMyAdmin, it freezes up on me because it takes so long (I\'m even using a cable internet connection!). What are my other options for uploading the data of these tables? I have to get this information into the new database, but it always times out. Please help! Thanks.

Jeff

#2 effigy

effigy
  • Staff Alumni
  • Advanced Member
  • 3,600 posts
  • LocationIL

Posted 05 January 2003 - 06:03 AM

jeff, can you do the backup at the command line interface?
Regexp | Unicode Article | Letter Database
/\A(e)?((1)?ff(?:(?:ig)?y)?|f(?:ig)?)\z/

#3 Uranium-235

Uranium-235
  • Members
  • PipPip
  • Member
  • 21 posts

Posted 05 January 2003 - 06:25 AM

considering he has to use phpmyadmin, I doubt he has access to any command line
Random Quote:[br][br][url=http://www.paintbug.com/scripts/quote_source.phps]

#4 jeffblochjr

jeffblochjr
  • Members
  • PipPip
  • Member
  • 10 posts

Posted 05 January 2003 - 09:16 AM

My problem is... I have the whole structure of the database all worked out and transferred over. Now I need to move the 700+ meg of data from the old database to the new. I have these 26 data files (text documents) on my hard drive, and I click on \"Insert data from a textfile into table\" for the respective table. Then, I direct it to look at the text file on my hard drive, and click Submit. Then I wait about 3 or 4 minutes... and the page basically reloads itself. No confirmation or error messages... it just reloads and stops reading the file. That\'s it. How else can I get the data from these text files into the tables? Please help, I am stumped.

#5 Uranium-235

Uranium-235
  • Members
  • PipPip
  • Member
  • 21 posts

Posted 05 January 2003 - 09:38 AM

If the mysql port isn\'t blocked on your first server. write a script to connect to it and just transfer it over
Random Quote:[br][br][url=http://www.paintbug.com/scripts/quote_source.phps]

#6 jeffblochjr

jeffblochjr
  • Members
  • PipPip
  • Member
  • 10 posts

Posted 05 January 2003 - 09:42 AM

I\'m affraid I don\'t know much about mysql or writing scripts... and unfortunately I wouldn\'t know the first thing about writing a script to perform that action. There is no other way to simply upload this textual data into the mysql database? I mean, why isn\'t it working when I tell it to \"Insert data from a textfile into table\"??

#7 Uranium-235

Uranium-235
  • Members
  • PipPip
  • Member
  • 21 posts

Posted 05 January 2003 - 09:58 AM

probably cause of the 2Meg upload limit PHP has set default
Random Quote:[br][br][url=http://www.paintbug.com/scripts/quote_source.phps]

#8 jeffblochjr

jeffblochjr
  • Members
  • PipPip
  • Member
  • 10 posts

Posted 05 January 2003 - 10:03 AM

That\'s gotta be it!! How can I change that value? I just have to get these text files into my database. Is there any way to disable this upload limitation?

#9 jeffblochjr

jeffblochjr
  • Members
  • PipPip
  • Member
  • 10 posts

Posted 05 January 2003 - 09:58 PM

If I can get into shell, and I have these huge text files already uploaded to my website... is there a command I can use to import these files into my MySQL database tables?

#10 effigy

effigy
  • Staff Alumni
  • Advanced Member
  • 3,600 posts
  • LocationIL

Posted 06 January 2003 - 03:47 AM

the upload limit is changed in the php.ini file at this line:
upload_max_filesize

if you can get to the command line you can backup everything from there by using the mysql binary.
Regexp | Unicode Article | Letter Database
/\A(e)?((1)?ff(?:(?:ig)?y)?|f(?:ig)?)\z/

#11 jeffblochjr

jeffblochjr
  • Members
  • PipPip
  • Member
  • 10 posts

Posted 06 January 2003 - 07:53 AM

As it turns out, I dont have access to edit php.ini, and my web host couldn\'t help me much with it. I did, however, get this whole big problem to solve itself with a simple \"LOAD DATA INFILE...\" command (after uploading these incredible huge text files onto my web site), and it worked like a charm. Thanks for all the help you guys have given me.

#12 jeffblochjr

jeffblochjr
  • Members
  • PipPip
  • Member
  • 10 posts

Posted 11 January 2003 - 07:35 PM

Here\'s yet another problem... everything was going fine, but then I hit a certain file and I did this command as usual:

LOAD DATA INFILE \'/var/www/html/data/records_t.txt\' INTO TABLE records_t FIELDS TERMINATED BY \';\' ENCLOSED BY \'\"\' LINES TERMINATED BY \"n\";

However, this time for some reason, I get the following message:

Error



SQL-query :  



LOAD DATA INFILE \'/var/www/html/data/records_t.txt\' INTO TABLE records_t FIELDS TERMINATED BY \';\' ENCLOSED BY \'"\' LINES TERMINATED BY "n" 



MySQL said: 



Got error 122 from table handler


What should I do? How can these tables be corrupt? I just exported the structure from another database and uploaded it to this one. Please help!

#13 jeffblochjr

jeffblochjr
  • Members
  • PipPip
  • Member
  • 10 posts

Posted 11 January 2003 - 07:50 PM

When I do a check on these \"corrupted\" tables, I get these messages:


Table is marked as crashed and last repair failed

Size of datafile is: 14008320       Should be: 0

Can\'t read indexpage from filepos: 147456

Corrupt


Any suggestions?

#14 webworks

webworks
  • New Members
  • Pip
  • Newbie
  • 1 posts

Posted 17 January 2003 - 12:28 PM

guess I arrived too late..... :!:

here\'s something for the huge mysql backups that can easily be done using your webbrowser.

copy this coding and call it dump.php

<?php
include (\"YourConfigFile.php\");
$output=system(\"mysqldump -u \".$db_user.\" -p\".$db_pass.\" \".$db_dbname.\" --opt > NameOfFilebackup.sql\");
header(\"Location: NameOfFilebackup.sql\")
?>

create a blank file: NameOfFilebackup.sql
chmod it to 777
change to your specs: $db_user, $db_pass, $db_dbname

call dump.php in your browser and when it is complete you will have
your table/data imported into the file NameOfFilebackup.sql




0 user(s) are reading this topic

0 members, 0 guests, 0 anonymous users