Create new topic via SQL

(William Di Luigi) #1

Hi, I’m trying to create a new topic via SQL, I am basically issuing a INSERT query inside the topics table and then some INSERT queries inside the posts table.

I noticed however that the reply count of the topic (the one beside the view count and the last activity) is stuck to -1. The schema of the topics table says that reply_count defaults to 0, and it is indeed 0, and I have some other topics (created in the usual manner) that have 0 replies, but in the web interface I see -1 for the topic created via SQL.

Even if I increase the reply_count, it stays -1.

What am I missing?

(Rafael dos Santos Silva) #2

If you need to programmatically create topics and posts please use the API.

I have done this sql madness before and it doesn’t end very well.

You can write a simple ruby script that interacts with the api, with loops and such, and it will way better.

(Matt Palmer) #3

Don’t do that. Really, really don’t do that. Discourse uses the SQL database as a pretty bare-basic relational repository of data, all of the interesting “business logic” is in the ActiveRecord models. By going straight to the database you’re doing an end-run around all of the intelligence, and means you’ll need to reverse-engineer all of the logic in the model layer, reimplement it yourself, and then keep track of changes as they occur.

Use the HTTP API instead. It’s designed for programmatic manipulation of all the data. If there’s something missing in the API for your use case, then bring that up as a feature request and there’s a good chance it’ll be added.

(Rafael dos Santos Silva) #4

And if you need to move data from one database to another (when people drop to sql is usually the case) you can hack together a script that reads from the source with Sequel, or another lib) and loops writing to the api.

I have done Mainframe Db2 to Discourse using this approach (in 2016 :poop:) and it works very well.

(William Di Luigi) #5

Thanks. By looking here I can see that there are only three parameters that can be used to create a topic:

  • title
  • raw
  • category

I need to also set the creation time :frowning:

(William Di Luigi) #6

Would it be OK if I create the topic (and posts) with the HTTP API and then manually change the created_at field of the topic/posts in the database?

Also: is it OK to change (again with SQL) the created_at field of users?

(Matt Palmer) #7

That’s far less likely to cause problems, although I couldn’t guarantee it’ll be hunky dory now, or that it won’t become problematic in the future.

Taking a quick glance at one of the importers (they’re all under script/import_script), it looks like at least some API endpoints will accept a created_at field, even if it isn’t documented as doing so. @team, what’s the consensus on expanding the documentation to include those parameters?

(Régis Hanol) #8

Isn’t that post a wiki? :wink:

(Matt Palmer) #9

Yes, but I’m not sufficiently versed in the details of our API to be willing to unilaterally go rewriting chunks of it.

(William Di Luigi) #10

OK it looks like the “created_at” parameter is accepted.

Another issue, however, is that the topics I am creating may have a “too short” body. How can I force the HTTP API to accept a request even if the topic body is too short?

(Carlo Kok) #11

When importing, disable all those checks and reset them later. Also do a few test runs :slight_smile: thats how we did it years and years ago.

(William Di Luigi) #12

I’m now trying to update the created_at field of users, but in this case it looks like it’s ignored: the HTTP API returns {"success":"OK",... but the creation time is not updated… should I just do it with SQL?

(William Di Luigi) #13

I am now getting http error: 429 too many requests.

How do I get around this?

(William Di Luigi) #14

I added a sleep time to avoid the error 429, I hope it will suffice. I have another question: if I delete a topic and then I try to create it again I get an error saying that the title is already used. I guess this is because the original topic was not really deleted. How do I delete it? (permanently, since it was a test)

(Rafael dos Santos Silva) #15

There is a site setting that allow repeated titles.

(Kane York) #16

Try adding skip_validations as a POST parameter. (You need to be admin for that to take effect IIRC)

(William Di Luigi) #17

But if I set &api_username=some_admin_account then the posts will be published as them, not as the user I want to post as :confused:

Oh, by the way, I’m getting a very weird error when trying to post. It worked for some time (a lot of posts were published) but now it gives:

requests.exceptions.ConnectionError: ('Connection aborted.', BadStatusLine('<html>\r\n',))

From what I understand, discourse is giving out a status line (e.g. HTTP/1.1 <status>) which does not make sense: <html>\r\n. Any ideas? :confused:

(William Di Luigi) #18

After some debugging, I found out that the post fails when the text is longer than 1812 bytes.

If I try to post a topic with raw parameter set to xxxx...x (1812 bytes long) it works, but if I add another x it will fail with the aforementioned error.

The max topic length (from admin settings) is set to 32000. Any ideas?

(Matt Palmer) #20

Whaaaaaaaaaaaaa? Can you give the full, exact reproduction steps for this? Sounds like a right clanger of a bug, would love to see it in action.

(William Di Luigi) #21

(You will need python3 and the requests python package, python3-requests on ubuntu).

The following contains my API key (I will change it later). If you try to run it, you get an error. If you change 2000 * "x" to something shorter, like 1800 * "x", it will post.

P.S. the 1812 number turned out to not be exact. It seems to depend also on the length of the topic title and I guess the username as well.

SAMPLE_POST = 2000 * "x"
URL = ""
API_KEY = "e4819859bee6cdf84e14d97fa60690203391aeebe60e6c7feccfd94dc1f3c80c"

import requests
import sys

timestamp = "2014-04-24 16:02:00.356085"
username = "wil93"

x = + "/posts" +
    "?api_key=" + API_KEY +
    "&api_username=" + username, params={
        "created_at": timestamp,
        "title": "This is a test topic",
        "category": "uncategorized",
        "raw": SAMPLE_POST,
        "skip_validations": "true",

if x.status_code != 200:
    print("[{}] Fail when creating topic".format(x.status_code))
    print("[{}] OK when creating topic".format(x.status_code))
    real_id = x.json()["topic_id"]
    print("" + str(real_id))