Advertisement:

Author Topic: Help with dB move  (Read 1621 times)

Offline pepf

  • Semi-Newbie
  • *
  • Posts: 49
Help with dB move
« on: October 23, 2019, 12:38:04 AM »
Hello,

Looking for some advice regarding dB move to new installation. Not sure about this as I'm doing it for the first time myself.
After installing the same version of SMF anew I would like to use the old dB, settings and attachments.

I assume just moving the old attachments folder to the new installation will do, but what about the dB.

Currently the old dB and the one created during the new installation are both present. What is the easiest way to have the new SMF recognize the old dB and settings?
Does moving the old Settings.php or just renaming the dB do this, or do I need to import the old dB into the new one?

TIA for any help.

Offline Looking

  • SMF Hero
  • ******
  • Posts: 2,381
  • SMF Customization
    • jeanborde on Facebook
    • SMF Custom Themes & Custom Coding
Re: Help with dB move
« Reply #1 on: October 23, 2019, 05:01:27 AM »
You could use the old DB with the new install but what version was the old DB?

Offline pepf

  • Semi-Newbie
  • *
  • Posts: 49
Re: Help with dB move
« Reply #2 on: October 23, 2019, 10:00:20 AM »
Both are 2.0.15, the same version.
What I do not understand is how to get the new SMF installation to connect to the existing dB instead of the new dB that is created during the installation.
The procedure I used may be wrong.
« Last Edit: October 23, 2019, 10:25:29 AM by pepf »

Offline Looking

  • SMF Hero
  • ******
  • Posts: 2,381
  • SMF Customization
    • jeanborde on Facebook
    • SMF Custom Themes & Custom Coding
Re: Help with dB move
« Reply #3 on: October 23, 2019, 10:03:13 AM »
Use this: repair settings and in the DB section put the credentials of the old DB. Understanding where it goes and what it does: What is repair_settings.php?

Offline Illori

  • Project Manager
  • SMF Legend
  • *
  • Posts: 51,579

Offline Sir Osis of Liver

  • SMF Hero
  • ******
  • Posts: 9,463
Re: Help with dB move
« Reply #5 on: October 23, 2019, 12:17:13 PM »
You can drop tables in the new db, import old db into new.  Or do as suggested and fix settings to connect to old db.

Offline Nate Silver

  • Semi-Newbie
  • *
  • Posts: 14
Re: Help with dB move
« Reply #6 on: October 23, 2019, 04:21:14 PM »
In case the new installation does not have the same URL the previous forum had, you can use a text editor (I use notepad++) to search every time the old url shows up and replace it with the new one. You should to the same thing to the "/home/USER/public_html/FORUM/" bits, then import that dB to a blank dB and change the settings file to reflect the new dB.

It might be convoluted and there might be ways with less steps, but it works for me and it's quicker than it seems.

Offline pepf

  • Semi-Newbie
  • *
  • Posts: 49
Re: Help with dB move
« Reply #7 on: October 23, 2019, 06:55:50 PM »
Thank you all for the help. Something to work with, I will try your suggestions.
I have some more questions.

Tried some things but not yet the Settings file suggestion, will do that next.

I assume the different naming of the old dB (moved from another host) does not affect this negatively?
I actually imported the old dB into the new one, but still the new SMF showed no change, I assume that is because I did not use the Settings file. I will do that next.

Is there anything else I can move to restore the old board settings and appearance of the forum?

Thanks again for your generous help.




Offline Illori

  • Project Manager
  • SMF Legend
  • *
  • Posts: 51,579
Re: Help with dB move
« Reply #8 on: October 23, 2019, 07:03:00 PM »
did you read the link i provided above? it tells you what you need to do.

Offline pepf

  • Semi-Newbie
  • *
  • Posts: 49
Re: Help with dB move
« Reply #9 on: October 23, 2019, 07:59:16 PM »
Yes. I cannot count on help from my host. (Their support was quite nasty telling me that SMF was old and not updated (it is the updated version 2.0.15). They insisted that some code had been injected and quarantined the whole site, even completely unrelated files. The only scripts active were SMF.) But I have not gotten to that yet.

I am redoing now the whole process, deleted and reinstalled SMF,,,
I have a problem that I am trying to resolve right now. I just reinstalled and tried to import the old dB again, but it has no 'user' attached. Somehow this was removed in the process. I assume I will have to give it privileges in order to work? However, phpmyadmin does not have a tab for assigning users or privileges to a dB. Trying to find out how to do that at present.

Offline Sir Osis of Liver

  • SMF Hero
  • ******
  • Posts: 9,463
Re: Help with dB move
« Reply #10 on: October 23, 2019, 09:23:59 PM »
Database credentials are in Settings.php in your forum root -

Code: [Select]

########## Database Info ##########
$db_type = 'mysql';
$db_server = 'localhost';
$db_name = 'thekrash_smf20';
$db_user = 'thekrash_smf20';
$db_passwd = 'xxxxxxxxxxxx';
$ssi_db_user = '';
$ssi_db_passwd = '';
$db_prefix = 'smf_';
$db_persist = 0;
$db_error_send = 1;


You can update the settings by editing the file manually, or use repair_settings.php.  $db_server is usually localhost, but not always.  $db_name, $db_user, $db_passwd are required to connect to the database.  If you don't know what they are, look in the mysql database section of your host cpanel, that's where users are created and assigned.  You'll have to reset the password if you don't have it.


Offline pepf

  • Semi-Newbie
  • *
  • Posts: 49
Re: Help with dB move
« Reply #11 on: October 23, 2019, 09:32:59 PM »
Thank you. I resolved the problem with no user privileges, now both databases have the same user and privileges, but after I tried to reimport the new dB is still the same in size. Will first have to sort this out and then go to to the next step.
The import was given as completed by phpMyAdmin, but in cPanel the size of both remained the same. I wonder if there is another way to see whether the process was actually completed.
« Last Edit: October 23, 2019, 09:47:58 PM by pepf »

Offline Sir Osis of Liver

  • SMF Hero
  • ******
  • Posts: 9,463
Re: Help with dB move
« Reply #12 on: October 23, 2019, 09:59:36 PM »
If I understand correctly, you have a new scratch db created by the 2.0.15 install package, which is connected to the new install, and a backup of your production db.  Drop all tables in the new db (with phpmyadmin), it will have 0 tables.  Then import the production backup.  IIRC, there are 63 tables in a new 2.0 install (first is smf_admin_info_files, last is smf_topics ), so you should see at least that many after import completes.  Mods can add additional tables.

Online Antechinus

  • SMF Friend
  • SMF Super Hero
  • *
  • Posts: 24,844
  • Master of BBC Abuse
Re: Help with dB move
« Reply #13 on: October 23, 2019, 10:15:06 PM »
Just be aware that phpMyAdmin tends to have trouble importing large databases. If it's 20 megabytes or less, it should be fine. If it's much larger than that, it may be necessary to break it into manageable chunks.

Alternatively, since you say you have cPanel, it should be possible to restore via cPanel's database tools.

Offline pepf

  • Semi-Newbie
  • *
  • Posts: 49
Re: Help with dB move
« Reply #14 on: October 23, 2019, 11:29:05 PM »
Thank you Sir Osis. It is really complex for someone who never did that, but the dropping of the tables went fine, as did the following importing into the newDB. I can see the tables in phpMyAdmin, but strangely the size has not changed at all if viewed in cPanel. New and old should now approximately the same.

To Antechinus, I can see how to restore a backup in cPanel, but nothing about importing an old one into a new dB.

Offline Sir Osis of Liver

  • SMF Hero
  • ******
  • Posts: 9,463
Re: Help with dB move
« Reply #15 on: October 23, 2019, 11:35:40 PM »
How large is the db?  If the total size is correct in phpmyadmin, it's probably ok.  Is the new install connecting to the db?  Link?

Offline pepf

  • Semi-Newbie
  • *
  • Posts: 49
Re: Help with dB move
« Reply #16 on: October 24, 2019, 12:01:31 AM »
Just did the repair_settings step. It did make both dBs the same size,
But there is now a blank page with the message Table 'xxxx.smfph_settings' doesn't exist.
xxxx is the actual dB name, not sure what 'smfph_settings' is...

Offline pepf

  • Semi-Newbie
  • *
  • Posts: 49
Re: Help with dB move
« Reply #17 on: October 24, 2019, 12:04:08 AM »
The dB size is 25.99 mb.

Offline Sir Osis of Liver

  • SMF Hero
  • ******
  • Posts: 9,463
Re: Help with dB move
« Reply #18 on: October 24, 2019, 12:08:15 AM »
smfph_ is the table prefix, default value is smf_.  $db_prefix in Settings.php must agree with prefix you see in phpmyadmin in front of all table names.


Offline pepf

  • Semi-Newbie
  • *
  • Posts: 49
Re: Help with dB move
« Reply #19 on: October 24, 2019, 12:40:35 AM »
Both old and new dB have always started with "east...", all database names start with user/account name_dB prefix. Not sure why the smfph was added during the SMF installation. So should I uninstall and reinstall again to correct this?

Offline pepf

  • Semi-Newbie
  • *
  • Posts: 49
Re: Help with dB move
« Reply #20 on: October 24, 2019, 02:23:57 AM »
I did uninstall and reinstall SMF, paying special attention to this. It remains the same with the same prefix as the dB.

Offline Sir Osis of Liver

  • SMF Hero
  • ******
  • Posts: 9,463
Re: Help with dB move
« Reply #21 on: October 24, 2019, 05:35:21 PM »
If you reinstall SMF from scratch, the installer will populate a new database with 63 tables with smf_ prefix.  Your database backup will retain the table prefix from the old database.  If they are different, you must either update $db_prefix setting to old prefix, or change the table prefix for all tables to smf_ in phpmyadmin.  Obviously it's easier to just change the setting.  The database name is not usually appended to table names, but if it is, it's part of the prefix and must be included as such in $db_prefix.  The db dump will always import the same way, with the original prefix.  SMF will not recognize the tables if prefix setting is incorrect.

Offline pepf

  • Semi-Newbie
  • *
  • Posts: 49
Re: Help with dB move
« Reply #22 on: October 24, 2019, 10:49:05 PM »
Hi, can you see anything wrong with this? It appears to be exactly like your example.

########## Database Info ##########
$db_type = 'mysql';
$db_server = 'localhost';
$db_name = 'easter71_smf650';
$db_user = 'easter71_smf650';
$db_passwd = 'xxxxxxxxx;
$ssi_db_user = '';
$ssi_db_passwd = '';
$db_prefix = 'smf_';
$db_persist = 0;
$db_error_send = 1;

And still it says:  "Table 'easter71_smf650.smf_settings' doesn't exist"

I assume that if the easter71 part is different it doesn't matter? The original database names of the previous host were retained after moving, and it still worked.
After exporting and importing the old part changed to easter71 too. I tried the whole procedure with the importing of the old dB about four or five times, using different versions (backup time), and the 'settings' message appeared each time after using repair_settings.php. BTW, what settings is that about, is it the SMF configuration?

Offline pepf

  • Semi-Newbie
  • *
  • Posts: 49
Re: Help with dB move
« Reply #23 on: October 24, 2019, 10:53:22 PM »
Do dBases change when SMF is updated? Could it be that something was not properly updated, making it appear it is 2.0.15 while in reality is at an earlier level? Or is it possible that a mod could be causing this?

Offline Sir Osis of Liver

  • SMF Hero
  • ******
  • Posts: 9,463
Re: Help with dB move
« Reply #24 on: October 24, 2019, 11:54:58 PM »
Not on my computer, so this will be quick.  Look at the table prefix in phpmyadmin.  That must be the setting for $db_prefix.  It almost certainly isn't smf_ .  If you can't get it, pm cpanel access (url/user/pw), I'll have a look tomorrow.

Offline pepf

  • Semi-Newbie
  • *
  • Posts: 49
Re: Help with dB move
« Reply #25 on: October 25, 2019, 01:26:00 AM »
The table prefix smf_ is  shown when using repair_settings.php, also in the settings.php file. It was the same during at least two test installations.
This is confusing.

Offline pepf

  • Semi-Newbie
  • *
  • Posts: 49
Re: Help with dB move
« Reply #26 on: October 25, 2019, 02:52:53 AM »
Got it!
Thank you. The concept of what the prefix might be be was all wrong! That did also resolve the error string. Need just to work on the theme and forum should be back up. Thanks again.

Question, which files and/folders should I transfer from the old forum to the new one to retain as much as possible of the original flavor, especially the theme and page settings. Now everything is just lined up on the left without any page structure. I cannot get inside yet, the links do not work for some reason.
« Last Edit: October 25, 2019, 03:56:26 AM by pepf »

Offline Illori

  • Project Manager
  • SMF Legend
  • *
  • Posts: 51,579
Re: Help with dB move
« Reply #27 on: October 25, 2019, 04:59:45 AM »
have you run repair_settings.php to correct the paths and URL's after you got the database working?

Offline pepf

  • Semi-Newbie
  • *
  • Posts: 49
Re: Help with dB move
« Reply #28 on: October 26, 2019, 01:38:48 AM »
Yes, it seems back to normal now. Thank you.

Offline pepf

  • Semi-Newbie
  • *
  • Posts: 49
Re: Help with dB move
« Reply #29 on: October 26, 2019, 02:21:12 AM »
Actually, I have another question. Moved to a new installation but mods and some other settings are missing. Is there a specific file(s) to move, or is it at least possible to see which mods were installed, originally?

Also, I remember restricting Google spider access to one instance at a time, somewhere, but now do not find where that was.

Offline Sir Osis of Liver

  • SMF Hero
  • ******
  • Posts: 9,463
Re: Help with dB move
« Reply #30 on: October 27, 2019, 12:10:44 AM »
I believe you did a clean install, then imported your production database.  Mods need to be reinstalled, they're not in the clean files.  The database may contain tables and settings created by the mods, so you wouldn't have to reconfigure the reinstalled mods from scratch.  If you have access to the original install, you can look in /Packages to see which mods are there, and in /Packages/backups, which mods were installed when.

Offline pepf

  • Semi-Newbie
  • *
  • Posts: 49
Re: Help with dB move
« Reply #31 on: October 27, 2019, 06:55:59 AM »
Thank you, I found them. The intention was to find out which mods I had used and then install them again.

Offline pepf

  • Semi-Newbie
  • *
  • Posts: 49
Re: Help with dB move
« Reply #32 on: November 03, 2019, 01:04:13 AM »
Succeeded in getting everything to work normally for a few days; but now all images prior to 26th last month have disappeared. There were broken image links showing a 404. Did maintenance and now all  image links are simply not existing anymore.

I will try to install a dB backup, but would it be possible to copy the newer messages, 26th and later somehow into the new dB?

One other question is, are the images stored in the dB or attachment folder? If in the dB, what does the attachment folder do?

Offline davejo

  • Jr. Member
  • **
  • Posts: 346
    • Dave's Hobby Workshop
Re: Help with dB move
« Reply #33 on: November 03, 2019, 03:35:09 AM »
Succeeded in getting everything to work normally for a few days; but now all images prior to 26th last month have disappeared. There were broken image links showing a 404. Did maintenance and now all  image links are simply not existing anymore.

Short answer to that is yes as far as the database is concerned the images are not where it's been told they are

I will try to install a dB backup, but would it be possible to copy the newer messages, 26th and later somehow into the new dB?

Yes, you need to open your backup database, which has the newer posts in it, copy and paste those into the new database. I use Notepad++ as it does not add any formating to the file.

One other question is, are the images stored in the dB or attachment folder? If in the dB, what does the attachment folder do?

When an image is uploaded to the site it's given a unique code which is then stored in the sites attachments folder (if you look in the attachments folder you will see files that have no apparent filename meaning), the code is also stored in the database so it knows the location of the posts the attachments are linked to.

If you want 'Quiz' questions Click here
Quiz Test Site Link

Online Kindred

  • The Mean One
  • Support Specialist
  • SMF Legend
  • *
  • Posts: 58,613
  • Gender: Male
    • Kindred-999 on GitHub
Re: Help with dB move
« Reply #34 on: November 03, 2019, 05:27:01 PM »
It is much more complicated than that...  there are inter-related tables involved....
Please do not PM, IM or Email me with support questions.  You will get better and faster responses in the support boards.  Thank you.

Offline pepf

  • Semi-Newbie
  • *
  • Posts: 49
Re: Help with dB move
« Reply #35 on: November 03, 2019, 07:53:55 PM »
What should I copy? The attachment tables? I cannot identify which are from when.

I now have a test install, new SMF installation, copied over old dB backup and old attachment folder from two weeks ago. Would like to add the posts of the last two weeks, but do not know how to do it.

Looking at the newer dB in PhpMyadmin I cannot identify those made in the last two weeks as there are no dates. Is there a simple way to copy them manually? One by one would be fine too.

One important question. Is it worthwhile doing this at all or does this create more complications? Would you advise to just omit this step and go on without those posts?

Offline GigaWatt

  • The Smiley Guy
  • Support Specialist
  • SMF Hero
  • *
  • Posts: 2,187
  • Gender: Male
    • Macedonian electronics forum
Re: Help with dB move
« Reply #36 on: November 04, 2019, 03:51:33 AM »
Yes, there is a way, but it's really tedious. How many attachments are supposed to be "copied" (it won't be a simple copy/paste) to the other (new ???, I didn't read the whole thread) database?
"This is really a generic concept about human thinking - when faced with large tasks we're naturally inclined to try to break them down into a bunch of smaller tasks that together make up the whole."

"A 500 error loosely translates to the webserver saying, "WTF?"..."

Offline pepf

  • Semi-Newbie
  • *
  • Posts: 49
Re: Help with dB move
« Reply #37 on: November 04, 2019, 04:19:11 AM »
Thanks a lot for your support and information. It helped me to make a decision.

I decided not to try to recover those posts. They are not worth the trouble. The test forum displays the images now and I will just change the link over to it, leaving those away.

Thank you all.


Offline Sir Osis of Liver

  • SMF Hero
  • ******
  • Posts: 9,463
Re: Help with dB move
« Reply #38 on: November 04, 2019, 11:31:20 AM »
FYI, in smf_messages poster_time is timestamp for the post.  It's unix time, there are many converters online that will give you date/time.  But no, not a good idea to try recovering the posts from one db to another.