HackerOne users: Testing against this community violates our program's Terms of Service and will result in your bounty being denied.

How to inject discussions from SQL?

Hi, I have a database from a previous forum which was a custom script. I want to migrate to Vanilla, and I tried to insert discussions, users and posts into a Vanilla test forum that I have, but they are not showing up. It seems that what I uploaded matches data types and everything.


I'm using gdn_comment, gdn_discussion and gdn_user tables.


Is there any doc that I can use as reference, or can someone explain what I need to do? thanks.

Comments

  • R_JR_J Ex-Fanboy Munich Admin

    On windows table names are oftentimes in lower case. If you use a Linux system, it would be

    GDN_Comment

    GDN_Discussion

    GDN_User


    Discussions musthave a category from GDN_Category (not -1)

    Users must have a role from GDN_Role in GDN_UserRole

  • Do IDs have to be inmediately incremental from the ones already in the DB? For example, my lower user ID is 1000, not 1, would that cause any trouble? Same with comments etc.

  • R_JR_J Ex-Fanboy Munich Admin

    I think there is a way to "tell" mysql from which index number to start. I would increase that to the first free number higher than all the existing numbers

  • pitkmipitkmi scifi-meshes.com


    Do IDs have to be inmediately incremental from the ones already in the DB? For example, my lower user ID is 1000, not 1, would that cause any trouble? Same with comments etc.

    I don't think that's an issue. I spun a couple of different databases (separate gallery and a downloads section from an old forum) into the main forum and I just added ten million (10000000) to all the DiscussionIDs to keep them from overlapping with the actual forum content. CommentIDs for the gallery and downloads (they had their own comments) got bumped up by two million (2000000). I was probably going for twenty million actually, but looks like I missed a zero there... anyway... also moved the system users (System, Stopforumspam, a bot account for feeds) to 9999999 and onwards, since they would have overlapped with imported userIDs.

    Any new discussions and users have IDs in the ten million range, while comments get IDs in the two million range. As an added bonus, it's really easy to tell old and new forum content apart in the database. Don't think I had to anything special to get it working. Vanilla just figured out the biggest ID, and started incrementing from there.

  • @pitkmi you scare me with this imagery

    example.com/discussion/1928929953999694/how-to-bring-back-my-credit

  • R_JR_J Ex-Fanboy Munich Admin
  • Ah. the Count! His elevator counts where the most heart breaking. Speaking of digital addiction.

  • Hi all again, I'm having some trouble, for some reason comments are not showing. I have some questions.


    What does the ForeignID column do in gdn_discussion? I'm just injecting without that column and as result discussions have a null value.

    For this table I'm injecting:

    DiscussionID

    CategoryID

    InserUserID

    Body

    Name

    DateInserted (most are ~2004 threads from another old forum)


    For gdn_comment:


    InsertUserID

    CommentID

    DiscussionID

    Body

    Format

    DateInserted


    But somehow it's all messed up. Most threads are not linked to a category? or don't even show up. It's strange. I double checked all the references in my database, and my Comment and Discussion IDs are ok

    In gdn_category just inserted CategoryID, ParentCategoryID (all -1 value), and Name.


    Users and userrole are also covered. Is there a table that I'm missing? What would you suggest to begin digging?

  • R_JR_J Ex-Fanboy Munich Admin

    Foreign Id is not important. Make sure all user related fields are filled and all date fields. If you have problems with categories, use CategoryID = 1 for all discussions and make sure you have a category with that ID


    But before you do that, see what happens when you visit yourforum/utility/structure and /dba/counts. Maybe that will already fix some problems

  • Unless you mean something else, visiting those urls gets me a 404

  • R_JR_J Ex-Fanboy Munich Admin

    Let's say your forum is at example.com. The url of a single discussion should look like here: example.com/discussion/1/foo. If it looks like that: example.com/index.php?p=/discussion/1/foo then you have one more step to do. Add $Configuration['Garden']['RewriteUrls'] = true; to /conf/config.php, but if you are using IIS instead of Apache, you might have more to do...

    After that example.com/dba/counts and example.com/utility/structure must work.


    After rereading your above post, I think /utility/structure would be helpful. The Category table needs a lot more information

  • Hmm, I did, using nginx 1.14, still getting a 404 on http://localhost/vanilla/utility/structure

    Restarting nginx didn't seem to make it.

    Anyway they were not showing up because I wass messing with IDs on my data transformations, now they seem ok.

    Another problem is that, despite having about +74K rows in gdn_discussion I don't have pagination in categories, I only see a bunch of threads per category. I don't see any pagination option in the control panel.

  • R_JR_J Ex-Fanboy Munich Admin

    Try /vanilla/index.php&p=/utility/structure and /vanilla/index.php&p=/dba/counts

  • iagovar2iagovar2 New
    edited July 2020

    Still 404, is there any documentation about this? Couldn't really find much.

  • R_JR_J Ex-Fanboy Munich Admin

    What is the url for a discussion in your setup?

  • Ramdomly picking one: http://localhost/vanilla/index.php?p=/discussion/9934/textos-de-humor#latest


    If I run:

    select count(*)

    from gdn_discussion where CategoryId = 6

    I get 787 rows.


    But If I browse to that category, I get 30 threads. There's no pagination in the bottom.



    This matches with the options of discussions per page, which is set to 30


    But I need a pagination system so I can browse all of them! I just can't find it anywhere in the configuration!


    I'm honestly pretty frustrated, I was thinking on Vanilla as a pretty nice out of the box solution, and I think I solved all the hoops of my unortodox whay of injecting all my data into it. But man I need that damn pagination.

  • R_JR_J Ex-Fanboy Munich Admin

    The way you injected the content most probably will be no problem. There are some issues I see:

    1. Your database seems to be on Windows. If you have tables named gdn_discussion instead of GDN_Discussion, you will get troubles if you move to a linux host.
    2. You are not using pretty urls. Take a look at the nginx sample and add $Configuration['Garden']['RewriteUrls'] = true; to your /conf/config.php
    3. All category "meta" information is missing. This is being fixed by calling the url "/utility/structure"
    4. All "count" information is missing, therefore you need to call "/dba/counts" to recalculate those columns (that will fix the pagination problem)


    As you can see here, in order to open a discussion here in the forums, you click on a link called "/discussion/123/foo", while in your setup that same url would be "index.php?p=/discussion/123/foo". So if I give advise to visit "/utility/structure" and "/dba/counts", you need to make it "index.php?p=/utility/structure" and "index.php?p=/dba/counts" for your setup. You might have been too focused on the database side to notice that. But since pretty urls are a requirement anyway, you should follow my second point above first


  • Hi, It took several attempts to make recalculation work, I cant say why, just tried a few times and then it worked. Also pagination is showing now. Thank you so much man, you've saved my day.

    Now, I was looking about this /db/ and /utility/ in the documentation but I found nothing. I'd like to know more about stuff like this that helps me with Vanilla, as the settings panel seems a bit limited, where do I find it?

Sign In or Register to comment.