Syntax error in SQL statement "CREATE TABLE IF NOT EXISTS ""cluster""

[Tomasz Korzekwa]

Hello,

I'm trying to setup cassandra-reaper with postgres as a backend.

I'm getting SQL error. Can you tell me what can be wrong? Below you can find the log:

Message    : Syntax error in SQL statement "CREATE TABLE IF NOT EXISTS ""cluster"" (
  ""name""        TEXT PRIMARY KEY,
  ""partitioner"" TEXT    NOT NULL,
  ""seed_hosts""  TEXT [[*]] NOT NULL
)"; expected "(, FOR, UNSIGNED, INVISIBLE, VISIBLE, NOT, NULL, AS, DEFAULT, GENERATED, ON, NOT, NULL, AUTO_INCREMENT, BIGSERIAL, SERIAL, IDENTITY, NULL_TO_DEFAULT, SEQUENCE, SELECTIVITY, COMMENT, CONSTRAINT, PRIMARY, UNIQUE, NOT, NULL, CHECK, REFERENCES, ,, )"; SQL statement:
CREATE TABLE IF NOT EXISTS "cluster" (
  "name"        TEXT PRIMARY KEY,
  "partitioner" TEXT    NOT NULL,
  "seed_hosts"  TEXT [] NOT NULL
) [42001-197]
Location   : db/postgres/V1_0_1__Initial_reaper_postgres_db.sql (/file:/usr/local/lib/cassandra-reaper.jar!/db/postgres/V1_0_1__Initial_reaper_postgres_db.sql)
Line       : 32
Statement  : CREATE TABLE IF NOT EXISTS "cluster" (
  "name"        TEXT PRIMARY KEY,
  "partitioner" TEXT    NOT NULL,
  "seed_hosts"  TEXT [] NOT NULL
)

Thanks for help in advance.
Best regards,
Tomasz

[Alexander Dejanovski]

Hi Tomasz, which version of Postgres are you using?

Maybe "IF NOT EXISTS" is not available in your version.

--
You received this message because you are subscribed to the Google Groups "TLP Apache Cassandra Reaper users" group.
To unsubscribe from this group and stop receiving emails from it, send an email to tlp-apache-cassandra-r...@googlegroups.com.
To post to this group, send email to tlp-apache-cassa...@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/tlp-apache-cassandra-reaper-users/cd3411ba-bdbb-4167-8db6-40902b66c89c%40googlegroups.com.
For more options, visit https://groups.google.com/d/optout.

[Tomasz Korzekwa]

Hello,

I'm using this version:

postgres (PostgreSQL) 9.5.3

Which version is recommended?

BR

Tomasz

To unsubscribe from this group and stop receiving emails from it, send an email to tlp-apache-cassandra-reaper-users+unsubscribe@googlegroups.com.
To post to this group, send email to tlp-apache-cassandra-reaper-us...@googlegroups.com.

[Alexander Dejanovski]

IF NOT EXISTS was introduced in 9.1, so that's not the problem.

The error message suggests that there's something wrong with the syntax.

Could you try running the DDL statement again but adjusting it to see where it fails exactly? Maybe your postgres instance doesn't like the space between "TEXT" and "[]" for the seed_hosts column.

We run integration tests on all PRs and master push which recreate the schema from scratch on postgres 9.4 with no issue. I'll try upgrading this to 9.5 to see if we run into the same problem.

-----------------
Alexander Dejanovski
France
@alexanderdeja

Consultant
Apache Cassandra Consulting

http://www.thelastpickle.com

To unsubscribe from this group and stop receiving emails from it, send an email to tlp-apache-cassandra-r...@googlegroups.com.
To post to this group, send email to tlp-apache-cassa...@googlegroups.com.

To view this discussion on the web visit https://groups.google.com/d/msgid/tlp-apache-cassandra-reaper-users/cd3411ba-bdbb-4167-8db6-40902b66c89c%40googlegroups.com.
For more options, visit https://groups.google.com/d/optout.

--

You received this message because you are subscribed to the Google Groups "TLP Apache Cassandra Reaper users" group.

To unsubscribe from this group and stop receiving emails from it, send an email to tlp-apache-cassandra-r...@googlegroups.com.
To post to this group, send email to tlp-apache-cassa...@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/tlp-apache-cassandra-reaper-users/be0f7aa9-7b34-429c-be47-4982e6ac5415%40googlegroups.com.

[Tomasz Korzekwa]

Hello Alexander,

I think I know what is wrong.

I haven't mentioned that I use docker image. And in docker image you have the config file which is fully parameterized. All vars in config are envs.

But I needed to configure reaper to use postgres as a backend. So according to doc I set REAPER_STORAGE_TYPE='postgres' and added some additional postgres related config.

And it wasn't working. Then I noticed that in the example config file from documentation and from rpm package you don't use this config which is parameterized. You use config where the backend is hadcoded: storageType: postgres.

So I replaced the config but it still didn't work. But I didn't remove this REAPER_STORAGE_TYPE var as it was not used. After I run out of ideas what can be wrong I removed this "not used variable" and guess what... Now it works.

So if you can try to reproduce this error. Just set the REAPER_STORAGE_TYPE var to postgres and try to run your CI. I'm very curious if this var is really a problem.

Thanks!

Best regards,

Tomasz

--

-------------
Tomek