cancel
Showing results for 
Search instead for 
Did you mean: 

move a table from MySQL to another MySQL

firkin
Grafter
Posts: 51
Registered: ‎11-01-2009

move a table from MySQL to another MySQL

Hi
I have installed a smf as a community website using the curve template. I successfully ran a few plugins on the site (smf 2.0.2 version) then there was a  call to upgrade to version 2.0.3. I installed that taking a backup as you do. Problem was some of the plugins had dug themselves deep in to the style sheet and the MySQL data base. One of the plugins was SimpleAds!!
I've been trying to transfer the members names, messages etc., to a new MySQL data base using a fresh install of smf . I really don't want to risking losing the member names or their messages. I've got two test site ready for a transfer but I need to know how to transfer a table from one to another.
Any advice?
12 REPLIES 12
spraxyt
Superuser
Superuser
Posts: 10,063
Thanks: 1,369
Fixes: 75
Registered: ‎06-04-2007

Re: move a table from MySQL to another MySQL

You should be able to do this using phpMyAdmin. Access the source database and export the tables required as a gzipped SQL file to your computer. Then, assuming you are using the same table prefix, swap to the new database and import the same file to it.
If you need to overwrite existing tables in the new database you can do this by ticking the Add DROP TABLE checkbox before doing the export (you can do this in any case, it doesn't matter whether the table initially exists or not). You might find it helpful to use a filename template such as __DB__smf_%Y-%m-%d for the export file; the % tokens will be replaced by yyyy-mm-dd.
David
Gabe
Community Veteran
Posts: 767
Registered: ‎29-10-2008

Re: move a table from MySQL to another MySQL

firkin
Grafter
Posts: 51
Registered: ‎11-01-2009

Re: move a table from MySQL to another MySQL

Hi spraxyt
Thanks for your fast reply.
I managed to move the member list from database dir01 to dir02 and that worked fine. However, it had no structure so I tried doing the same with  categories and got the following error:
Quote
Unknown column 'c.id_cat' in 'field list'
File: /home/xxxxxxxxx/public_html/dir02/Sources/Subs-BoardIndex.php
Line: 72
Note: It appears that your database may require an upgrade. Your forum's files are currently at version SMF 2.0.4, while your database is at version 2.0.3. The above error might possibly go away if you execute the latest version of upgrade.php.

I had upgraded both sites to 2.0.4 before trying to move the members and there wasn't an error so I don't  think the data base  version that's causing the error. One thing that might have caused it was the first time I tried to move  categories  it wouldn't  have it so I dropped that table then imported the new file:
Unknown column 'c.id_cat' in 'field list'
If so how would I go about putting in id for column
'c.id_cat' in 'field list' ?
spraxyt
Superuser
Superuser
Posts: 10,063
Thanks: 1,369
Fixes: 75
Registered: ‎06-04-2007

Re: move a table from MySQL to another MySQL

I suspect the tables in SMF are probably more interlinked than the ad-hoc table copies you are trying to do will support.
I assume the test sites (effectively) have all the mods removed from files. If you copy all the database tables across are any extra tables introduced by the mods simply ignored so that the forum works normally? Alternatively if you didn't copy tables that aren't in the standard distribution over (ie assume any additional tables are mod-related) does the test forum still work?
David
firkin
Grafter
Posts: 51
Registered: ‎11-01-2009

Re: move a table from MySQL to another MySQL

Test board 02 was new before I uploaded the main site's back up SQL. Test board 01 was new also but I wanted to keep this clean; the idea was that I could transfer members names, messages, categories etc., to that board keeping the mods out of it. I have tried to remove the mods in the database and also using the 'Package Manager'. None of these procedures succeeded completely leaving a Fatal Error message.
Strange thing is that I was using the curve theme when I did the upgrade from smf 2.0.2 to 2.0.3 which broke my forum, I was able to get into the admin area and use the old 'core' theme, this worked even with the mods although I had removed the SimpleAds plug in or at least I thought I had! I'll try putting back the original 'c.id_cat' in 'field list id' that I had dropped using the original old categories 'id' then go through the process again.
As mentioned the live site is working using the core theme just showing a few errors that only the admin can see, the test board 01 is working the same as the on-line live site, but test board 02 throws up the error I put on my previous post. I can still get in to the admin area on all boards but there is no structure  / categories to the test board 02.
Thank you for your help so far, I have at least managed to move my members names over to the new board. I'll let you know if putting in the  categories old 'id', works.
spraxyt
Superuser
Superuser
Posts: 10,063
Thanks: 1,369
Fixes: 75
Registered: ‎06-04-2007

Re: move a table from MySQL to another MySQL

You won't have a table column called c.id_cat - the notation refers to a column called id_cat in a table assigned the alias name c in the SELECT (or other) SQL statement that is being processed. That notation is used to distinguish between two columns both called id_cat in two different tables being merged in the statement. For example rows in one table might be joined with rows in another table by executing
SELECT userid, username, a.id_cat AS id_cat FROM table1 AS a, table3 AS c WHERE a.id_cat = c.id_cat;
Are your tables too large to make looking at their contents impractical?
A brief look at the SimpleAds plugin shows there is a database.sql file for modifying the SMF database, but an equivalent file to remove the modifications has not been provided. Sad
David
firkin
Grafter
Posts: 51
Registered: ‎11-01-2009

Re: move a table from MySQL to another MySQL

After reading your last post I approached another way. I deleted all mods in the database (dir01) then I exported that database to my computer, went to database (dir02) and imported the clean database from (dir01). They both populated and seem none the worse for wear.
However, when I try to change the theme to Curve from Core I get this error:
Quote
Fatal error: Call to undefined function PublicIndextop() in /home/xxxxxxx/public_html/dir01/Sources/Load.php(2151) : eval()'d code on line 314

I prefer to use the Curve theme because I'm trying to keep the site up to date in every way. I'm assuming that an edit of the 'Sources/Load.php(2151)' file may do the trick if only I knew what I was looking for and how to remove/replace the php code that is causing the error.
I don't think my tables are that large. The sites only been running seven months now and has sixty odd members.
I've been looking at the posts on the smf site and there are a lot of people trying to sort out the problems that the SimpleAds plugin has caused. The original coder sold the site a few years ago and the new owner is now charging people $15 to $50 to adapt the code from one theme to another. I'd rather go without using the plugin, not because I object to his charging but I have no confidence in his work judging from peoples comments.
Do you know how I would go about fixing the fatal error in the load.php file?
Thanks for all your help todate spraxyt.
Gabe
Community Veteran
Posts: 767
Registered: ‎29-10-2008

Re: move a table from MySQL to another MySQL

Sounds like the mod's still in the curve theme. Do you have a line including PublicIndextop() in that theme's index.template.php? Just guessing line 314.
You may need a fresh reload of that theme.
Gabe
spraxyt
Superuser
Superuser
Posts: 10,063
Thanks: 1,369
Fixes: 75
Registered: ‎06-04-2007

Re: move a table from MySQL to another MySQL

Line 2151 of Load.php seems to be part of generic code to include required files by DIY means (eval-ing file_get_contents) to give better control of error checking/messages. This probably isn't the source of the problem, just where it manifests itself.
I think the Curve theme might be the one in the default folder. The SimpleAds manual installation instructions mention making changes to default, but I didn't spot any reference to PublicIndextop in these instructions. Perhaps it occurs in one of the additional SimpleAds files - are any of those still in the ./Themes/default folders?
Perhaps Core isn't affected because the mods were never applied to it? Reloading a clean default theme as suggested by Gabe might fix this. Huh
David
firkin
Grafter
Posts: 51
Registered: ‎11-01-2009

Re: move a table from MySQL to another MySQL

Thank you both.
It seemed to be a problem with the Curve theme, I did as you both suggested and installed a fresh Curve theme making sure that the database I used was clean of any mods. Then went to themes settings and used the Reset Theme URLs and Directories.
All is working as it should.
Without your help I'd still be struggling.
Smiley
Gabe
Community Veteran
Posts: 767
Registered: ‎29-10-2008

Re: move a table from MySQL to another MySQL

That's good news.  Smiley
Gabe
spraxyt
Superuser
Superuser
Posts: 10,063
Thanks: 1,369
Fixes: 75
Registered: ‎06-04-2007

Re: move a table from MySQL to another MySQL

Excellent, glad to hear this has worked for you. Smiley
David