Page 1 of 2

instruction on how to -replace- my PHPBB database

Posted: Mon Feb 17, 2014 1:53 am
by techman41973
I want to replace my PHPBB database with a different version.
I found instructions on how to import my database on a new server https://www.phpbb.com/kb/article/transf ... or-domain/
however I've been unable to find specific instructions on how to replace my existing database.
If I run the following command,

mysql -u USERNAME -p PASSWORD --default-character-set=utf8 DATABASE_NAME < NAME_OF-FILE

will it just overwrite everything with the new database or should I delete the old database first.
I don't see an option to delete an entire database in PHPMYADMIN,

Before I mess up anything seriously, I'd like to confirm the appropriate strategy. Thanks

Re: instruction on how to -replace- my PHPBB database

Posted: Mon Feb 17, 2014 3:18 am
by Oyabun1
What do you mean by "replace"? What do you mean by "a different version"? What are your trying to achieve?

Re: instruction on how to -replace- my PHPBB database

Posted: Mon Feb 17, 2014 5:41 am
by techman41973
I had my site running on 2 different servers (shared and VPS), switching between them.
My VPS was live for atleast a week, with new members joining and new posts.
I have now have a newer version of my database that I want to transfer from my VPS to my shared server.
So yes, I want to replace the current phpbb database on my shared server with database (newer) from my VPS (which I already exported). I'm going back to and staying with my shared server as my traffic will be far less in the next several months.

Re: instruction on how to -replace- my PHPBB database

Posted: Mon Feb 17, 2014 7:47 am
by Oyabun1
Just follow the instructions in the article. If both boards are the same phpBB versions nothing more needs to be done. Importing the database backup will drop and recreate the tables, in effect, replacing the existing one.

Re: instruction on how to -replace- my PHPBB database

Posted: Wed Feb 19, 2014 11:09 am
by techman41973
OK, I tried execting the MYSQL statement and I received the following error
ERROR 1062 (23000) at line 9348: Duplicate entry '1' for key 'PRIMARY'
Doing a search on phpbb.com, I found a thread that discusses this issue, but the instructions on how to resolve don't seem clear.
viewtopic.php?f=46&t=2100573
It seems that the error is relating to overwriting the existing tables. Any help would be appreciated . Thanks

Re: instruction on how to -replace- my PHPBB database

Posted: Wed Feb 19, 2014 11:35 am
by Oyabun1
techman41973 wrote:OK, I tried execting the MYSQL statement
What "statement"?

If you followed the instructions in the article and included the drop table statements then there would be no data existing.

Re: instruction on how to -replace- my PHPBB database

Posted: Wed Feb 19, 2014 5:54 pm
by techman41973
I'm refering to the statement in my original post

mysql -u USERNAME -p PASSWORD --default-character-set=utf8 DATABASE_NAME < NAME_OF-FILE

No I did not check the box "Add DROP DATABASE statement" when I exported my database
I followed the specific instructions in the quote box

"IMPORTANT: If you are using phpMyAdmin 3.4.3.2 or higher, please follow the instructions in this quote box to backup your database."

Instruction in the quote box did not tell me to check the box "Add Drop Database statement"
However, this is probably why I'm running the issue.

Is there any way I can add rectify this issue without re-exporting the database?
I don't have access to my VPS to export the database again. I'm stuck with what I have.

Thank you again for your time.

Re: instruction on how to -replace- my PHPBB database

Posted: Wed Feb 19, 2014 6:03 pm
by 5hocK
Can you create a new, empty database and import into that?

Re: instruction on how to -replace- my PHPBB database

Posted: Wed Feb 19, 2014 6:07 pm
by techman41973
5hocK , on my shared server I'm limited to only 2 databases. I can't add a new empty one.

Re: instruction on how to -replace- my PHPBB database

Posted: Wed Feb 19, 2014 6:15 pm
by 5hocK
Could you drop the tables in the database using phpmyadmin?

Re: instruction on how to -replace- my PHPBB database

Posted: Wed Feb 19, 2014 6:26 pm
by techman41973
Not sure I can look into it.
I don't specifically know how to drop the tables in the file I want to import.

Re: instruction on how to -replace- my PHPBB database

Posted: Wed Feb 19, 2014 6:35 pm
by 5hocK
If you have a control panel for your shared hosting then you probably have phpmyadmin; if so then you should be able to drop tables, leaving the database empty and ready to accept an import.
drop.png
drop.png (10.66 KiB) Viewed 749 times

Re: instruction on how to -replace- my PHPBB database

Posted: Thu Feb 20, 2014 11:22 pm
by techman41973
Thanks, I dropped the tables manually and this resolved the duplicate entry error.
Now I have a new error to deal with.

User 'has exceeded the 'max_updates' resource (current value: 36000).

max_updates is the number of updates that an account can issue per hour, and since my database is somewhat large, I'm exceeed this value
when I try to import

I'm on a shared server and my host doesn't want to increase this value.
Is there a switch I can add to the mysql import command (below) that will import my database in smaller pieces to avoid this contention?

mysql -u USERNAME -p PASSWORD --default-character-set=utf8 DATABASE_NAME < NAME_OF-FILE

Re: instruction on how to -replace- my PHPBB database

Posted: Fri Feb 21, 2014 12:29 am
by Oyabun1
You can't import only some parts from a single file.

Ask you host whether they will import the backup for you, some will, some won't.

If they won't do it then you may need to manually create smaller files.

How big is the database?

Re: instruction on how to -replace- my PHPBB database

Posted: Fri Feb 21, 2014 5:03 am
by techman41973
The database is 23mb. Not sure why it's so big. I have about 200 posts.
It could be related to MODs I installed.
I'll talk to my host again