CODEWITHSUNDEEP
javadatatablecassandracqlmismatch
Happyman
Happyman

Reputation: 81

Cassandra/Java mismatched character Exception when creating table with MD5 as name

I'm setting up a Cassandra database and try to create tables with an MD5 hash code as table name. When I do this, I get an com.datastax.oss.driver.api.core.servererrors.SyntaxError: line 1:35 mismatched character '2' expecting '-' exception.

My Query:

CREATE TABLE IF NOT EXISTS 77f2d6b127b7d4a89a940d0829ff1e672afb4362_status (
    jsonSchema TEXT,
    onlineStatus INT,
    key text,
    timestamp timeuuid,
    primary key(key, timestamp)
) WITH CLUSTERING ORDER BY (timestamp DESC) 
  AND compaction = {'class': 'TimeWindowCompactionStrategy', 
                    'compaction_window_size': 1, 
                    'compaction_window_unit': 'DAYS'};

And the exception:

Caused by: com.datastax.oss.driver.api.core.servererrors.SyntaxError: line 1:35 mismatched character '2' expecting '-'
    at com.datastax.oss.driver.api.core.servererrors.SyntaxError.copy(SyntaxError.java:48)
    at com.datastax.oss.driver.internal.core.util.concurrent.CompletableFutures.getUninterruptibly(CompletableFutures.java:113)
    at com.datastax.oss.driver.internal.core.cql.CqlRequestSyncProcessor.process(CqlRequestSyncProcessor.java:53)
    at com.datastax.oss.driver.internal.core.cql.CqlRequestSyncProcessor.process(CqlRequestSyncProcessor.java:30)
    at com.datastax.oss.driver.internal.core.session.DefaultSession.execute(DefaultSession.java:207)
    at com.datastax.oss.driver.api.core.CqlSession.execute(CqlSession.java:47)
    at com.datastax.oss.driver.api.core.CqlSession.execute(CqlSession.java:56)

I wonder why this happens. According to https://docs.datastax.com/en/archived/cql/3.3/cql/cql_reference/ref-lexical-valid-chars.html the query should have a correct syntax.

What I have tried

Double quotation marks

It works when I put the table name in double quotation marks (CREATE TABLE IF NOT EXISTS "77f2d6b127b7d4a89a940d0829ff1e672afb4362_status" (... but then I get problems with my IntelliJ database IDE because it does not query the database using double quotation marks for the table names.

Start table name with a letter ([a-z])

When I add in front of the table name manually a letter (not a number; e.g. a77f2d6b127b7d4a89a940d0829ff1e672afb4362_status), the table is created but I get another timeout exception:

com.datastax.oss.driver.api.core.DriverTimeoutException: Query timed out after PT2S
    at com.datastax.oss.driver.api.core.DriverTimeoutException.copy(DriverTimeoutException.java:34)
    at com.datastax.oss.driver.internal.core.util.concurrent.CompletableFutures.getUninterruptibly(CompletableFutures.java:113)
    at com.datastax.oss.driver.internal.core.cql.CqlRequestSyncProcessor.process(CqlRequestSyncProcessor.java:53)
    at com.datastax.oss.driver.internal.core.cql.CqlRequestSyncProcessor.process(CqlRequestSyncProcessor.java:30)
    at com.datastax.oss.driver.internal.core.session.DefaultSession.execute(DefaultSession.java:207)
    at com.datastax.oss.driver.api.core.CqlSession.execute(CqlSession.java:47)
    at com.datastax.oss.driver.api.core.CqlSession.execute(CqlSession.java:56)

After the timeout exception I can execute the same query again. This time without errors -> table was successfully created.

I use following Java driver: [Maven: com.datastax.oss:java-driver-core:4.0.1]

Any help is more than welcome.

Upvotes: 2

Views: 2347

Answers (2)

adutra
adutra

Reputation: 4526

  1. In the CQL grammar, the rule for table names is called cfName. As you can see a table name can be either a non-quoted identifier (IDENT), a quoted name (QUOTED_NAME) or unreserved keywords. The rule for IDENT is defined here. As you can see, it needs to start with a letter. To create a table named like yours, you need to double quote it (I tried with CQLSH, and it worked – sorry if IntelliJ cannot understand that).
  2. The driver timeout for CQL queries is by default 2 seconds. This is usually not enough for DDL queries. You can raise the timeout for an individual query like this:
     session.execute(
        SimpleStatement.newInstance(
            "CREATE TABLE IF NOT EXISTS ...")
        .setTimeout(Duration.ofSeconds(30)));

Upvotes: 1

Alex Ott
Alex Ott

Reputation: 87154

I think that documentation is incorrect - it should say:

starts with alpha character, and contains alpha-numeric, ...

I haven't found this definition for table/keyspace names in the grammar for CQL (only for column names), but it's visible in the cqlsh code.

Upvotes: 1

Related Questions