Mybb.rb imports nothing


(Dave Higgins) #1

I’m running the mybb.rb script as described by @elberet and the script is running and returns successfully, but no changes result. The script contains the correct remote host name, db name, pw, etc. The remote server is configured to allow remote mysql from the IP of the current Discourse install. I’m not sure why nothing is imported. Can anyone shed any light?

(I believe the three users mentioned here are the existing users already in the Discourse to which I am trying to import)


(Dave Higgins) #2

@dandv I followed your list of steps here


(except for the edit to my.cnf on the remote server).

You seem to have had some experience with a mybb db import, do you have any idea why the mybb.rb script doesn’t pick up the data from the remote database?
Thanks for any insight!


(Dave Higgins) #3

Is there a log of the importer’s actions which I can check?
I’m stuck because the importer returns successfully, but does not result in import of anything. Thanks.


(Gerhard Schlager) #4

Do you have more than 3 users in your myBB forum? Then you’ll need to figure out why these queries return only 3 users.

Regarding the topics and posts… You’ll have to debug those queries too. Do they return all the posts from your forum?


(Dave Higgins) #5

I have hundreds of users and thousands of posts on the remote forum. I have presumed that the “creating users 3 / 3” is somehow related to the Discourse forum, where I happen to have three users set up. But the script is definitely set up to contact a remote host:

The table prefix is correct; from my remote db phpMyAdmin:

After the mybb.rb script returns, there are no new users in Discourse, and no changes to the existing three users, no new posts, nothing!

If I change any of the connection details, the script throws an error. (host, username, pw)
So I think it’s connecting ok.


(Gerhard Schlager) #6

Did you try to execute the queries with phpMyAdmin to which I posted links in my previous post?
Here they are with the right table prefix for your convenience:

SELECT count(*) count
 FROM mybb_users u
 JOIN mybb_usergroups g ON g.gid = u.usergroup
WHERE g.title != 'Banned';

SELECT uid id, email email, username, regdate, g.title `group`
  FROM mybb_users u
  JOIN mybb_usergroups g ON g.gid = u.usergroup
 WHERE g.title != 'Banned'
 ORDER BY u.uid ASC;

SELECT count(*) count
  FROM mybb_posts;

SELECT p.pid id,
       p.tid topic_id,
       t.fid category_id,
       t.subject title,
       t.firstpost first_post_id,
       p.uid user_id,
       p.message raw,
       p.dateline post_time
  FROM mybb_posts p,
       mybb_threads t
 WHERE p.tid = t.tid
ORDER BY p.dateline;

Do they work and return the expected amount of rows? If they don’t, try tweaking them until they work. :wink:


(Dave Higgins) #7

Ah I see - in phpMyAdmin :facepalm: :smile:
Thanks for editing the queries for testing.
In phpMyAdmin where the mybb forum resides, those queries work just fine! I get my correct count of users, the user list, post count, and posts list.
So the queries look ok.

And, if I add puts '', total_count into mybb.rb, after that mysql query is executed, I see the correct result. So the query is working from remote as well.


(Gerhard Schlager) #8

It’s hard to tell without debugging the import script…
The only thing I can think of is, that all record are skipped during the import. Try removing all lines which start with next if all_records_exist? from the import script.


(Dave Higgins) #9

I commented out the next if all_records_exist? as suggested, and: It’s working - thanks!

Interestingly, the user count starts going up:

but when it reaches it’s total, it pings back to “3 / 3”:

Perhaps this is part of the issue. No matter, I suppose.

However, when the posts-total is reached (or the batch-size total, for a large batch size), the script prematurely aborts with “MySQL server has gone away” which sounds very sad :wink:

So we’re making progress. But - come back, MySQL server :persevere: I want the script to finish! It seems there are a lot of reasons this could happen, according the the Internet, but I fixed this issue by simply dramatically reducing the BATCH_SIZE in myrb.bb (to 50). Then the remaining parts of the script ran as well.

I’m there! I imported my mybb db! After 3 days of trying! :blush: :rolling_eyes:
@gerhard, thanks SO much for your tips.


(Dave Higgins) #10

For others wishing to import a mybb MySQL database, here’s what worked for me, from start to finish. My mybb was hosted on a server with cpanel.

I used a live fresh-install of Discourse on DigitalOcean. It is advised elsewhere to import into a local developer Discourse and then backup that Discourse and restore into the live one. However that is rather involved if you’re not a regular Github and Vagrant user (see this and this). So if the live Discourse is new, why not import right into it. If everything breaks, I can start over and have Discourse back up in 30 mins.

Start by performing a new Discourse backup through admin pages, and download it.

Then you need to allow remote MySQL connections from the new (Discourse) server.
I used cpanel where my mybb forum is hosted, and clicked on “Remote MySQL” and entered the Discourse server IP there.

Then collect the mybb database connection details. In cpanel you can see these under “MySQL databases”. Note the database name, the user, and look up in your records the password for that user (you did make a record of that, right?!) For the IP of the server where your mybb forum resides, you may be able to just do a DNS lookup on the domain name. But my hosting provider advised using a different IP for the server where the shared hosting resides. (Both may work, I am not sure.) If your table prefix is not “mybb_”, you’ll need to edit that too (you can check in phpMyAdmin from cpanel).

Now, ssh to your Discourse install. I did this as root, with PuTTY.
Then,

cd /var/discourse/ ./launcher enter app apt-get update apt-get install libmysqlclient-dev nano gem install mysql2 su - discourse cd /var/www/discourse/script/import_scripts bundle install

nano mybb.rb
…and edit the connection details (MYBB_DB, host, username, password). I also needed to comment out the two lines beginning next if, and reduce the BATCH_SIZE to 50 (see above in this topic for more info).
To save the file in nano, ctrlO. To exit nano, ctrlX.

Now go back to your mybb admin page and put the board into offline mode, in the Configuration tab.

Now back in your ssh window to your Discourse install,

RAILS_ENV=production ruby mybb.rb

At this point, for me, the script executed successfully. Check your Discourse install - is everything there? (Don’t be fooled by the Dashboard stats, they may not update for a while)

In the online Discourse admin UI, perform another backup, and download it. There now may be some manual cleanup of posts required (links, attachments etc).

Once the import is complete,
exit out of the discourse login
exit out of the Rails app

If you now
./launcher rebuild app
the libmysqlclient-dev, nano, and mysql2 gem installations should be removed, as well as your edits to mybb.rb.

Thanks to @elberet, @dandv and @gerhard for their posts on this task


(Dave Higgins) #11

Something else I edited, might be useful to others was the SQL queries.

These changes to the mybb.rb script achieve the following:

  • do not import any users in the “Awaiting Activation” group
  • do not import users who have (1) never posted and (2) haven’t visited the forum for 365 days or longer.
total_count = mysql_query("SELECT count(*) count
    FROM #{TABLE_PREFIX}users u
    JOIN #{TABLE_PREFIX}usergroups g ON g.gid = u.usergroup
    WHERE g.title != 'Banned'
       AND g.title != 'Awaiting Activation'
       AND !((postnum < 1) AND (DATEDIFF(now(), FROM_UNIXTIME(lastactive)) > 365) )
    ;").first['count']

batches(BATCH_SIZE) do |offset|
    results = mysql_query(
      "SELECT uid id, email email, username, regdate, g.title `group`
       FROM #{TABLE_PREFIX}users u
       JOIN #{TABLE_PREFIX}usergroups g ON g.gid = u.usergroup
       WHERE g.title != 'Banned'
          AND g.title != 'Awaiting Activation'
          AND !((postnum < 1) AND (DATEDIFF(now(), FROM_UNIXTIME(lastactive)) > 365) )
          ORDER BY u.uid ASC
          LIMIT #{BATCH_SIZE}
          OFFSET #{offset};")

#12

What would you recommend in this case?

loading existing groups...
loading existing users...
loading existing categories...
loading existing posts...
loading existing topics...
/usr/local/lib/ruby/gems/2.3.0/gems/mysql2-0.4.8/lib/mysql2/client.rb:89:in `connect': Access denied for user 'root'@'localhost' (using password: NO) (Mysql2::Error)
        from /usr/local/lib/ruby/gems/2.3.0/gems/mysql2-0.4.8/lib/mysql2/client.rb:89:in `initialize'
        from mybb.rb:31:in `new'
        from mybb.rb:31:in `initialize'
        from mybb.rb:304:in `new'
        from mybb.rb:304:in `<main>'

(Matt Palmer) #13

Set a password for your MySQL connection, most likely.


#14

I set it already. :frowning:


(Matt Palmer) #15

And yet…

Access denied for user 'root'@'localhost' (using password: NO)


#16

I change my mysql root password with the new. That command:

mysqladmin -u root -p'oldpass' password 'newpass'

And tried again.

RAILS_ENV=production ruby mybb.rb

Output:

discourse@discourse-gameofthronestr-app:/var/www/discourse/script/import_scripts$ RAILS_ENV=production ruby mybb.rb
loading existing groups...
loading existing users...
loading existing categories...
loading existing posts...
loading existing topics...
/usr/local/lib/ruby/gems/2.3.0/gems/mysql2-0.4.8/lib/mysql2/client.rb:89:in `connect': Access denied for user 'root'@'localhost' (using password: YES) (Mysql2::Error)
        from /usr/local/lib/ruby/gems/2.3.0/gems/mysql2-0.4.8/lib/mysql2/client.rb:89:in `initialize'
        from mybb.rb:31:in `new'
        from mybb.rb:31:in `initialize'
        from mybb.rb:304:in `new'
        from mybb.rb:304:in `<main>'

(Régis Hanol) #17

You have to provide your login/password as environment variables when running the import script.


#18

I set db and password variables. Except this there is no variable in Mybb.rb. How can I provide variables?


#19

I reinstalled it.

when i use that command: RAILS_ENV=production ruby mybb.rb

Output:

mybb.rb screenshot:

Any ideas?


(Jay Pfaffman) #20

If you installed the development environment, don’t include production when you start the script.

Oh, but the problem is that it can’t connect to the MySQL database. It looks like it’s not even getting connected. Is it running and the local machine?