ERROR 1064 (42000): no such local index in manticore

Question

I'm trying to connect to manticore database using the official go-sdk. I use the docs instructions expect I've added a DROP and a CREATE statement in order to create testrt index programmatically.

func FillManticore(w http.ResponseWriter, r *http.Request) {
    sess := session.Instance(r)
    cl := manticore.NewClient()
    cl.SetServer("localhost", 9312)
    cl.Open()

    q := "DROP TABLE testrt"
    res, err := cl.Sphinxql(q)
    if err != nil {
        fmt.Println("create error:", q)
    }

    q = "CREATE TABLE testrt(id int, title text, content text, counter int)"
    fmt.Println("create query is:", q)
    res, err = cl.Sphinxql(q)
    if err != nil {
        fmt.Println("create error:", q)
    }

    res, err = cl.Sphinxql(`replace into testrt values(1,'my subject', 'my content', 15)`)
    fmt.Println(res, err)
    res, err = cl.Sphinxql(`replace into testrt values(2,'another subject', 'more content', 15)`)
    fmt.Println(res, err)
    res, err = cl.Sphinxql(`replace into testrt values(5,'again subject', 'one more content', 10)`)
    fmt.Println(res, err)
    res2, err2 := cl.Query("subject", "testrt")
    fmt.Println(res2, err2)

But I get:

[ERROR 1064 (42000): no such local index 'testrt'] <nil>
[ERROR 1064 (42000): no such local index 'testrt'] <nil>
[ERROR 1064 (42000): no such local index 'testrt'] <nil>
<nil> <nil>

The docs is really spare so I could not figure out what is wrong there with my code. So I appreciate your hints.

Answer 1

The wrong thing is that you can't add column named id as there's one by default. If you try the same query directly with mysql client you'll get:

mysql> CREATE TABLE testrt(id int, title text, content text, counter int);
ERROR 1064 (42000): error adding index 'testrt': can not add multiple attributes with same name 'id'

Here's the fixed code:

    cl := manticore.NewClient()
    cl.SetServer("localhost", 9312)
    cl.Open()

    q := "DROP TABLE testrt"
    res, err := cl.Sphinxql(q)
    if err != nil {
        fmt.Println("create error:", err)
    }

    q = "CREATE TABLE testrt(/*id int, */title text, content text, counter int)"
    fmt.Println("create query is:", q)
    res, err = cl.Sphinxql(q)
    if err != nil {
        fmt.Println("create error:", err)
    }
    fmt.Println(res)

    res, err = cl.Sphinxql(`replace into testrt values(1,'my subject', 'my content', 15)`)
    fmt.Println(res, err)
    res, err = cl.Sphinxql(`replace into testrt values(2,'another subject', 'more content', 15)`)
    fmt.Println(res, err)
    res, err = cl.Sphinxql(`replace into testrt values(5,'again subject', 'one more content', 10)`)
    fmt.Println(res, err)
    res2, err2 := cl.Query("subject", "testrt")
    fmt.Println(res2, err2)

which returns:

[Query OK, 0 rows affected]
[Query OK, 1 rows affected] <nil>
[Query OK, 1 rows affected] <nil>
[Query OK, 1 rows affected] <nil>
Status: ok
Query time: 0s
Total: 3
Total found: 3
Schema:
    Fields:
        title
        content
    Attributes:
        counter: int
        title: string
        content: string
Matches:
    Doc: 1, Weight: 1, attrs: [15 my subject my content]
    Doc: 2, Weight: 1, attrs: [15 another subject more content]
    Doc: 5, Weight: 1, attrs: [10 again subject one more content]
Word stats:
    'subject' (Docs:3, Hits:3)
 <nil>

The diff is:

8c8
<         fmt.Println("create error:", q)
---
>         fmt.Println("create error:", err)
11c11
<     q = "CREATE TABLE testrt(id int, title text, content text, counter int)"
---
>     q = "CREATE TABLE testrt(/*id int, */title text, content text, counter int)"
15c15
<         fmt.Println("create error:", q)
---
>         fmt.Println("create error:", err)
16a17
>     fmt.Println(res)
26d26
<