phpmyadmin problem recreating database

[sander815]

i am trying to recreate a phpbb database from a .sql backup file created from within phpbb( phpbb_db_backup.sql) from within phpmyadmin; It runs for a couple of minutes, then i get these errors:
(Don't know if that has to do with the fact that its a backup.sql from phpbb? Its structure is maybe different then a dump from phpmyadmin?
My user doen't have access to phpmyadmin at his provider he is now running his forum at)

Notice: Undefined variable: db in /var/www/html/phpMyAdmin-2.5.0/read_dump.php on line 26

Warning: Cannot modify header information - headers already sent by (output started at /var/www/html/phpMyAdmin-2.5.0/read_dump.php:26) in /var/www/html/phpMyAdmin-2.5.0/main.php on line 25

Warning: Cannot modify header information - headers already sent by (output started at /var/www/html/phpMyAdmin-2.5.0/read_dump.php:26) in /var/www/html/phpMyAdmin-2.5.0/libraries/ob.lib.php on line 65

Warning: Cannot modify header information - headers already sent by (output started at /var/www/html/phpMyAdmin-2.5.0/read_dump.php:26) in /var/www/html/phpMyAdmin-2.5.0/libraries/header_http.inc.php on line 14

etc etc

[l0rdphi1]

Can't the backup be restored through phpbb itself?

[ProWebUK]

phpmyadmin does not like large dump files, and does not work well with them. I suggest for larger MySQL dumps you use mysql itself from shell. You dont need any specific level of of access to shell, aslong as the mysql command is allowed and you have your mysql information handy your ok :)

Chris

[DirectAdmin Support]

Hello,

Databases can't be created through anything except DA. Since there are limits on the # of databases that can be created, DA needs to have control over how many are created. If you had database creation privileges, you could create 100 databases, and only be allowed to have 1 :)

John

[ProWebUK]

I'm not exactly sure how phpbb dumps turnout which doesn't help. Does the database name have a - in it?

I know the - character can cause problems within MySQL databases or through shell at least. if this is the case and the database name does contain a - I suggest you remove the first few lines of the dump file until you get to the USE database line, leave that and everything below it.

Also ensure that the tables you are trying to restore do *not* already exist as this can also cause problems.

I find the easiest way is to remove all tables, the first few lines and restore from there (usually in shell, although phpmyadmin should work for small dump files just as well)

Chris

[sander815]

it does start with --'s

-- MySQL dump 8.22
--
-- Host: localhost Database: xxx
---------------------------------------------------------
-- Server version 3.23.57

--
-- Table structure for table 'phpbb_auth_access'
--

CREATE TABLE phpbb_auth_access (
group_id mediumint(8) NOT NULL default '0',
forum_id smallint(5) unsigned NOT NULL default '0',


it doen't have anything to do with the fact this is a mysql 3 db?
db is around 30 mb in size

[sander815]

hmm, thats only for the db i got from his ISP

a dump from phpBB is like this:
#
# phpBB Backup Script
# Dump of tables for xxxxx
#
# DATE : 04-12-2003 20:45:44 GMT
#
#
# TABLE: phpbb_auth_access
#
DROP TABLE IF EXISTS phpbb_auth_access;
CREATE TABLE phpbb_auth_access(

[sander815]

Originally posted by ProWebUK
phpmyadmin does not like large dump files, and does not work well with them. I suggest for larger MySQL dumps you use mysql itself from shell. You dont need any specific level of of access to shell, aslong as the mysql command is allowed and you have your mysql information handy your ok :)

Chris

i don't have mysql access from the shell ....

it seems like the db is too large
i split it into 1.5 mb test file, and that worked

[ProWebUK]

If you dont have shell access contact your host asking if you can provide the location of the dump file and get them to restore through shell.

Its a simple 1 line command and I would honestly be suprised if they refused to do it.

Chris

[sander815]

can you provide me the commands that are being used?
I know my host pretty well, and if i know the commands, i could point him to the right direction

btw, splitting the file into around 1.5 mb files is working

[ProWebUK]

in ssh just type:



mysql -u USERNAME -pPASSWORD < dump.sql



USERNAME - your mysql username
PASSWORD - your mysql password
dump.sql - your dump file

you may need to add the following at the top of your dump file:

USE database;


so you have something like:

USE database;

-- Table structure for table 'table name'
--

CREATE TABLE

as the start.

Hope this helps you.

Chris

[sander815]

what do you mean use database?
database=name of my database, or just use database?


like this:

use sander_forum
#
# phpBB Backup Script
# Dump of tables for xxx
#
# DATE : 09-12-2003 21:46:15 GMT
#
#
# TABLE: phpbb_auth_access
#
DROP TABLE IF EXISTS phpbb_auth_access;
CREATE TABLE phpbb_auth_access(
group_id mediumint(8) NOT NULL,



or like this:
use database
#
# phpBB Backup Script
# Dump of tables for xxx
#
# DATE : 09-12-2003 21:46:15 GMT
#
#
# TABLE: phpbb_auth_access
#
DROP TABLE IF EXISTS phpbb_auth_access;
CREATE TABLE phpbb_auth_access(
group_id mediumint(8) NOT NULL,

[ProWebUK]

as your post shows it although replace:

use sander_forum

with

USE sander_forum;

Chris

[sander815]

don't know if i have enough access privs to do this, but i get this when running th eabove command

[sander@ns public_html]$ mysql -u sander_forum -pxxxxxx < sandertest.sql ERROR 1044 at line 1: Access denied for user: 'sander_forum@localhost' to databa'e 'sander_forum
[sander@ns public_html]$

login for phpmyadmin:
l: sander_forum
p: xxxxxx
dbname: sander_forum
dumpname: sandertest.sql

sandertest.sql dump:

USE sander_forum
#
# phpBB Backup Script
# Dump of tables for xxxxxx
#
# DATE : 10-12-2003 08:06:31 GMT
#
#
# TABLE: phpbb_auth_access
#
DROP TABLE IF EXISTS phpbb_auth_access;
CREATE TABLE phpbb_auth_access(
group_id mediumint(8) NOT NULL,

[ProWebUK]

Check the response to your PM :)

Chris

[sander815]

yes
done, it worx ! thx a lot

[S2S-Robert]

Or you could use MySQL Front to upload a backup of your file.

[ProWebUK]

ssh is the quickest and best way in my opinion. Nothing beats the speed of a local connection to mysql :p

Just restored 46mb dump file in around 30 seconds :p

[S2S-Robert]

Without a doubt, but not everybody has SSH xs (the current example f.i. ;))

[ProWebUK]

agreed :p

[sander815]

i upped a database of 32 mb

through ssh i restored it, took about 30 secs, very fast!
Way better then phpmyadmin