Error While Restore the Database.

[mk_fastian]

hello,

i get error when i restore the database.. i don't know why this error always come .. when i import the database. please resolve this issue .. i am very thankful ..

ERROR:
----------
Error

SQL query:

--
-- Database: `phpdb`
--
CREATE DATABASE `phpdb` DEFAULT CHARACTER SET latin1 COLLATE latin1_swedish_ci;

MySQL said: Documentation
#1044 - Access denied for user 'forumsmf'@'localhost' to database 'phpdb'

[mk_fastian]

please reply if any buddy knows this error. 

[karlbenson]

Its appears that you aren't allowed to create databases in phpmyadmin.
Your host or cpanel hasn't given you that privilege.

You'll need to edit your backup file, and removing the line to create the database
CREATE DATABASE `phpdb` DEFAULT CHARACTER SET latin1 COLLATE latin1_swedish_ci;

Then create the database either in cpanel or your hosts control panel.
(remembering to choose latin1 character set and collation latin1_swedish_ci (IF it gives you the option to do so).

Then try restoring the backup.

[mk_fastian]

no always come error that its a to huge file. and its only 23MB.. i extract because of changes which Mr.regularexpression has told me .. and i create manual the database.

Thanks.

[RobertMfromLI]

I have a problem related to database dump file size as well, where it will not restore a database because of the file size.

My problem seems to be that each table gets one "INSERT" statement for all records, which gets timed-out during execution. A few of my insert statements in the dump file are over 30MB each.

The solution I found was to whitelist my IP address in cPanel for MySQL access (under account info if memory serves), and then use a script I wrote that parses the whole dump file and splits the insert lines into more manageable chunks, and sends each chunk.

If the dump file size is indeed the problem, then you may wish to try the same. I used REXX to do the scripting to split up the lines (as well as to do the database inserts and execs) - but there is no reason why you cant use anything else that is decent at handling text parsing. For me, REXX was the easiest, because I had the code written to handle everything needed (all the SQL communications stuff, the text parsing, etc) and because it's very quick on OS/2 and eComStation, which is what my local servers run on.

I'm presuming PHP is almost as easy to accomplish the same things though.

The key is maintaining the statements while breaking things into smaller chunks... for instance...

INSERT INTO .... (varlist) (data) (data) (data) (data) (data) (data) (data).........

grab the "INSERT INTO .... (varlist) " section, break each (data) section (or sections) into chunks (array them, parse through them using substr and pointers to last position, etc), and remember to put the "INSERT INTO .... (varlist) " at the beginning of each (or whatever the command happens to be). And of course, remember to tack on the ; at the end of each statement you send (without appending an extra one at the end of the last dataset).

I found using under 30,000 characters for each segment worked... so I just incremented a pointer with the quantity of data (# of chars) appended to the pending statement, and when it passed 30,000 I backed up to the end of the closest dataset, closed the command with the ; and sent it. Then built a new statement using the beginning of the command ("INSERT INTO .... (varlist) ") followed by the next 30,000 +/- characters worth of data declarations... wash... rinse... repeat.


When our SMF databases got deleted at Star Trek New Voyages, that is exactly what I had to do to restore them from a backup (that fortunately was only 2 days old).

78,000 posts in a dump file, and tons of PMs, and near 9,000 members does not seem to work well with a database restore from a dump file.

Hope that helps...
Robert

[Rumbaar]

Larger dump files can be issues for host that can't handle the sizes or even the vast amount of data being tried to be sent.

Take a look for BigDump.php (Google).  It performs a great service but breaking up the upload to more manageable portions and will help recovering very large database dumps.

[RobertMfromLI]

Larger dump files can be issues for host that can't handle the sizes or even the vast amount of data being tried to be sent.

Take a look for BigDump.php (Google).  It performs a great service but breaking up the upload to more manageable portions and will help recovering very large database dumps.

Sorry so long to reply. Since I have a bunch of other stuff using MySQL and REXX as the back end, I simply used it (with a little extra coding to do the splitting) to split up the commands and feed it to the server chunk by chunk. All went well and the Star Trek New Voyages forums went back online from the backup.

Thanks again for the response! That tool will come in handy when I have to move these forums and dont have a REXX enabled machine (or dont feel like waiting for one of my other servers running the REXX scripts to send the commands to the remote one).

Rob

[mashby]

Is this topic solved or do you require further assistance?