Foolz\SphinxQL\Exception\DatabaseException - Syntax error or access violation: 1064 unknown column: 'categories_id'

[DrakeJones]

For a couple of weeks releases haven't been showing up. They are in the db, but not displayed or searchable. It looks like it's breaking when I start back-filling, but that could just be coincidence. I did a fresh install and it happened again. I didn't spot a cause in the logs.

Trying to manually populate with rt_indexes in the database goes like so:

php populate_rt_indexes.php releases_rt Truncating index releases_rt finished. [Starting to populate sphinx RT index releases_rt with 39 releases.]

Foolz\SphinxQL\Exception\DatabaseException

[42000] SQLSTATE[42000]: Syntax error or access violation: 1064 unknown column: 'categories_id' [REPLACE INTO releases_rt (id, name, searchname, fromname, categories_id, filename) VALUES (1, '[nzbforyou.com] (01/60) - \"Tokyo.Vice.S01.COMPLETE.720p.WEB-DL.x265.10Bit-Pahe.li.par2\" - 2.59 GB - yEnc', '[nzbforyou.com] (01/60) - \"Tokyo.Vice.S01.COMPLETE.720p.WEB-DL.x265.10Bit-Pahe.li.par2\" - 2.59 GB - yEnc', 'NZBforYOU nzb@foryou.com', '5010', '\'\'')]

at /var/www/NNTmux/vendor/foolz/sphinxql-query-builder/src/Drivers/Pdo/Connection.php:28 24▕ 25▕ try { 26▕ $statement->execute(); 27▕ } catch (PDOException $exception) { ➜ 28▕ throw new DatabaseException('[' . $exception->getCode() . '] ' . $exception->getMessage() . ' [' . $query . ']', 29▕ (int)$exception->getCode(), $exception); 30▕ } 31▕ 32▕ return new ResultSet(new ResultSetAdapter($statement));

  +3 vendor frames 

4 /var/www/NNTmux/Blacklight/SphinxSearch.php:75 Foolz\SphinxQL\SphinxQL::execute()

5 populate_rt_indexes.php:86 Blacklight\SphinxSearch::insertRelease()

[ghost]

You need to recreate the Sphinx indexes, stop Sphinx, delete the data directory and then start again. Then run the populate script.

categories_id was added recently and Sphinx doesn't create them automatically.

[DrakeJones]

Thanks Fossil. /var/lib/manticore/data?

[DariusIII]

While it is not mandatory it helps a lot to delete /var/lib/manticore/data. As @Fossil01 already said, you need to recreate manticore indexes.

[DrakeJones]

Got it. Thank you both.

[DrakeJones]

I followed your instructions and I'm still getting the error. I can see that "categories_id" is actually in the new releases_se table, but there is still a problem. Maybe a permissions issue? I don't see any issues in the logs.

`[Starting to populate sphinx RT index releases_rt with 40 releases.]

Foolz\SphinxQL\Exception\DatabaseException

[42000] SQLSTATE[42000]: Syntax error or access violation: 1064 unknown column: 'categories_id' [REPLACE INTO releases_rt (id, name, searchname, fromname, categories_id, filename) VALUES (84, '[nzbforyou.com] (01/46) - \"Upload.S02.COMPLETE.720p.WEB-HD.x264-Pahe.in.par2\" - 1.86 GB - yEnc', '[nzbforyou.com] (01/46) - \"Upload.S02.COMPLETE.720p.WEB-HD.x264-Pahe.in.par2\" - 1.86 GB - yEnc', 'NZBforYOU nzb@foryou.com', '10', '\'\'')]

at /var/www/NNTmux/vendor/foolz/sphinxql-query-builder/src/Drivers/Pdo/Connection.php:28 24▕ 25▕ try { 26▕ $statement->execute(); 27▕ } catch (PDOException $exception) { ➜ 28▕ throw new DatabaseException('[' . $exception->getCode() . '] ' . $exception->getMessage() . ' [' . $query . ']', 29▕ (int)$exception->getCode(), $exception); 30▕ } 31▕ 32▕ return new ResultSet(new ResultSetAdapter($statement));

  +3 vendor frames 

4 /var/www/NNTmux/Blacklight/SphinxSearch.php:75 Foolz\SphinxQL\SphinxQL::execute()

5 populate_rt_indexes.php:86 Blacklight\SphinxSearch::insertRelease() `

[ghost]

unknown column: 'categories_id'

It's still not created properly. The _se tables don't say much as they are just a proxy to Manticore.

Did you delete Manticores data directory contents after stopping Manticore?

You could try using the mysql CLI and truncate/drop all the indexes in releases_rt manually TRUNCATE releases_rt and then restarting Manticore.

[DrakeJones]

I deleted Manticores' data directory contents after stopping Manticore and re-created the tables (a few times). Reinstalled Manticore. Deleted all releases, re-created the tables.

Maybe this is the problem:

MariaDB [(none)]> TRUNCATE TABLE nntmux.releases_rt; ERROR 1146 (42S02): Table 'nntmux.releases_rt' doesn't exist

MariaDB [(none)]> TRUNCATE TABLE nntmux.releases_se; Query OK, 0 rows affected (0.000 sec)

My Manticore tables are releases_se and predb_se.

Re-indexing after truncating gives:

Truncating index releases_rt finished. [Starting to populate sphinx RT index releases_rt with 11 releases.]

Foolz\SphinxQL\Exception\DatabaseException

[42000] SQLSTATE[42000]: Syntax error or access violation: 1064 unknown column: 'categories_id' [REPLACE INTO releases_rt (id, name, searchname, fromname, categories_id, filename) VALUES (1, 'Re: Please post - The Big Bang Theory S12E09 THANKS IN ADVANCE :) :) [01/01] \"The Big Bang Theory - 12x09 - The Citation Negation.mkv\"', 'Re: Please post - The Big Bang Theory S12E09 THANKS IN ADVANCE :) :) [01/01] \"The Big Bang Theory - 12x09 - The Citation Negation.mkv\"', 'xaunloc xaunloc@gmail.com', '10', '\'\'')]

at /var/www/NNTmux/vendor/foolz/sphinxql-query-builder/src/Drivers/Pdo/Connection.php:28 24▕ 25▕ try { 26▕ $statement->execute(); 27▕ } catch (PDOException $exception) { ➜ 28▕ throw new DatabaseException('[' . $exception->getCode() . '] ' . $exception->getMessage() . ' [' . $query . ']', 29▕ (int)$exception->getCode(), $exception); 30▕ } 31▕ 32▕ return new ResultSet(new ResultSetAdapter($statement));

  +3 vendor frames 

4 /var/www/NNTmux/Blacklight/SphinxSearch.php:75 Foolz\SphinxQL\SphinxQL::execute()

5 populate_rt_indexes.php:86 Blacklight\SphinxSearch::insertRelease()

[ghost]

Ah, you need to copy the manticore config file from NNTmux to /etc/manticore. The indexes are created from the config file, not from the PHP script.

[DrakeJones]

Doh! I'll give that a shot.

[DrakeJones]

That did help a lot. I can manually populate the releases_rt() table and I'm getting releases again. That wasn't happening before. But, I'm still getting a "Syntax error or access violation: 1064 error." https://pasteboard.co/XLj1jCKc9H4T.jpg.

[DrakeJones]

Sorry about that. The image disappeared somehow. The error is different from the original one. This one expecting a string.

1990 Collections ready to be converted to releases.
[42000] SQLSTATE[42000]: Syntax error or access violation: 1964 row l, column 5: string expected [REPLACE INTO releases_rt (id,
name, searchname, fromname, categories_id, filename) VALUES (8115, '[Ol/14] - \ "All Together Now 503 17 Love Me Tender.mp4.991
\ " (14596K) yEnc', '[01/ 14] - \ "All Together Now 503 17 Love Me Tender .mp4.001\ " (14596K) yEnc ' , 'NoSpam@Me.com (NoSpam) •, 19

[DariusIII]

What version of manticore are you using?

[DrakeJones]

I was using 3.2.2-191226-afd60463-release.bionic_amd64-bin.deb, then tried to upgrade to 4.2.0-211223-15e927b28 before Fossil pointed out the config file issue. Which version is actually compatible with the current iteration of NNTmux?

[DariusIII]

You need latest version 4. Manticore 5 has been released, but i haven't tried it yet.

[ghost]

My staging machine is on 5.0.1, seems to work but haven't tested it properly yet.

[DrakeJones]

Good to know that 5.0.1 seems to be working for you. I tried all three flavors of Manticore 3, 4, & 5 - still getting the error. Incidentally, the error message was popping up when I tried to populate releases_rt and it was killing the process. Now the error is only popping up in the Update Binaries window - for what that's worth. I must have configured or broken something. On the plus side, I am getting releases.

If a fix or workaround isn't obvious to you guys I don't want you to waste your time. I'll do a rebuild from the ground up on a fresh Ubuntu 20.04 install.

Thanks.

[DrakeJones]

I did a rebuild. I got that error again, but adjusted some permissions and it seems to eliminated the error.