instruction on how to -replace- my PHPBB database

techman41973 · Post by **techman41973** » Mon Feb 17, 2014 1:53 am

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

Oyabun1 · Post by **Oyabun1** » Mon Feb 17, 2014 3:18 am

What do you mean by "replace"? What do you mean by "a different version"? What are your trying to achieve?

techman41973 · Post by **techman41973** » Mon Feb 17, 2014 5:41 am

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.

Oyabun1 · Post by **Oyabun1** » Mon Feb 17, 2014 7:47 am

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.

techman41973 · Post by **techman41973** » Wed Feb 19, 2014 11:09 am

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

Oyabun1 · Post by **Oyabun1** » Wed Feb 19, 2014 11:35 am

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.

techman41973 · Post by **techman41973** » Wed Feb 19, 2014 5:54 pm

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.

5hocK · Post by **5hocK** » Wed Feb 19, 2014 6:03 pm

Can you create a new, empty database and import into that?

techman41973 · Post by **techman41973** » Wed Feb 19, 2014 6:07 pm

5hocK , on my shared server I'm limited to only 2 databases. I can't add a new empty one.

5hocK · Post by **5hocK** » Wed Feb 19, 2014 6:15 pm

Could you drop the tables in the database using phpmyadmin?

techman41973 · Post by **techman41973** » Wed Feb 19, 2014 6:26 pm

Not sure I can look into it.
I don't specifically know how to drop the tables in the file I want to import.

5hocK · Post by **5hocK** » Wed Feb 19, 2014 6:35 pm

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 (10.66 KiB) Viewed 754 times

techman41973 · Post by **techman41973** » Thu Feb 20, 2014 11:22 pm

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

Oyabun1 · Post by **Oyabun1** » Fri Feb 21, 2014 12:29 am

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?

techman41973 · Post by **techman41973** » Fri Feb 21, 2014 5:03 am

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

advertisements