If you have comments after the last CQL statements, you are presented with this lovely stacktrace.

java.lang.IllegalStateException: File had a non-terminated cql line
  at com.google.common.base.Preconditions.checkState(Preconditions.java:176)
  at uk.sky.cqlmigrate.CqlFileParser$LineProcessor.check(CqlFileParser.java:170)
  at uk.sky.cqlmigrate.CqlFileParser$LineProcessor.access$100(CqlFileParser.java:42)
  at uk.sky.cqlmigrate.CqlFileParser.getCqlStatementsFrom(CqlFileParser.java:37)

Right now, it is hard coded to always be a unique UUID

It might be useful to derive that from a client's perspective, for example from a service that is running multiple pods in Kubernetes so the locks can be traced back.

#13 looked to remove the dependency, but it still must be part of the transitives.

For example, at

This means this library can't be used with Guava 26.0+ due to those static final CharMatchers being removed - see https://github.com/google/guava/releases/tag/v26.0

As shown on this page:

https://datastax.github.io/java-driver/2.1.8/features/metadata/

It is possible for a schema changing query to return successfully even if it timed out. This causes an issue when one query alters the schema and the next query to be run requires that schema change. To ensure this doesn't happen we should check after every query that the schema is in agreement and fail if not.

Keyspace currently defaults to cqlmigrate, table name current defaults to locks.

When running CqlMigrate, allow the user to provide configuration to allow them to override the consistency levels for reads and writes to whatever they deem to be appropriate for their context. Currently the CL defaults are LOCAL_ONE and ALL for reads and writes respectively.

Use case: With authentication on cassandra and creating users for applications with relevant access on their keyspace. The CQL files may contains sensitive information such as the password for the user e.g.

// Change default user password
ALTER ROLE cassandra WITH PASSWORD='somePassDev'

CREATE ROLE someApp WITH PASSWORD = 'somePasswordForDev' AND LOGIN = true;
GRANT ALL ON KEYSPACE someAppKeyspace TO someApp;

Possible approach, allow for the password to be some cipher text and cqlmigrate is able to decrypt the cipher text given the key to the actual password at runtime and apply the CQL. It would be great useful feature for teams who also manage their own infra. Possible encryption library: Jasypt

We should be able to execute this from the command line.
All configuration required around locking should also be useable from the command line.

What happens:

Node acquires the lock
Starts SchemaLoader to do the migration
Schema loader checks cluster health which says "unhealthy"
Library throws an error and lock hangs there forever
Logs:

[2016-12-21 11:37:18,003] INFO  [main] u.s.c.Lock - [] Attempting to acquire lock for 'app.schema_migration', using client id '047b5b3c-ee9e-4059-b083-3a6bcd7e9259'
[2016-12-21 11:37:18,079] INFO  [main] u.s.c.CqlMigratorImpl - [] Loading cql files from [/tmp/cqlmigrate5345071027669587170]
[2016-12-21 11:37:18,093] INFO  [main] u.s.c.KeyspaceBootstrapper - [] Keyspace found, not applying bootstrap.cql
Exception in thread "main" uk.sky.cqlmigrate.exception.ClusterUnhealthyException: Cluster not healthy, the following hosts are down: [***]
        at uk.sky.cqlmigrate.ClusterHealth.check(ClusterHealth.java:28)
        at uk.sky.cqlmigrate.SessionContext.checkClusterHealth(SessionContext.java:35)
        at uk.sky.cqlmigrate.CqlLoader.load(CqlLoader.java:18)
        at uk.sky.cqlmigrate.SchemaLoader$Loader.apply(SchemaLoader.java:45)
        at uk.sky.cqlmigrate.CqlPaths.lambda$applyInSortedOrder$7(CqlPaths.java:49)
        at java.util.stream.ForEachOps$ForEachOp$OfRef.accept(ForEachOps.java:184)
        at java.util.stream.ReferencePipeline$2$1.accept(ReferencePipeline.java:175)
        at java.util.Iterator.forEachRemaining(Iterator.java:116)
        at java.util.Spliterators$IteratorSpliterator.forEachRemaining(Spliterators.java:1801)
        at java.util.stream.AbstractPipeline.copyInto(AbstractPipeline.java:481)
        at java.util.stream.AbstractPipeline.wrapAndCopyInto(AbstractPipeline.java:471)
        at java.util.stream.ForEachOps$ForEachOp.evaluateSequential(ForEachOps.java:151)
        at java.util.stream.ForEachOps$ForEachOp$OfRef.evaluateSequential(ForEachOps.java:174)
        at java.util.stream.AbstractPipeline.evaluate(AbstractPipeline.java:234)
        at java.util.stream.ReferencePipeline.forEach(ReferencePipeline.java:418)
        at uk.sky.cqlmigrate.CqlPaths.applyInSortedOrder(CqlPaths.java:49)
        at uk.sky.cqlmigrate.SchemaLoader.load(SchemaLoader.java:28)
        at uk.sky.cqlmigrate.CqlMigratorImpl.migrate(CqlMigratorImpl.java:90)
        at uk.sky.cqlmigrate.initializer.CqlMigrateInitializer.migrate(CqlMigrateInitializer.java:80)
        at uk.sky.cqlmigrate.initializer.CqlMigrateBundle.cqlMigrate(CqlMigrateBundle.java:62)
        at uk.sky.cqlmigrate.initializer.CqlMigrateBundle.run(CqlMigrateBundle.java:39)
        at uk.sky.cqlmigrate.initializer.CqlMigrateBundle.run(CqlMigrateBundle.java:15)
        at io.dropwizard.setup.Bootstrap.run(Bootstrap.java:200)
        at io.dropwizard.cli.EnvironmentCommand.run(EnvironmentCommand.java:42)
        at io.dropwizard.cli.ConfiguredCommand.run(ConfiguredCommand.java:85)
        at io.dropwizard.cli.Cli.run(Cli.java:75)
        at io.dropwizard.Application.run(Application.java:79)
        at com.bskyb.App.main(App.java:54)
Registered app server agent with Node ID[9579] Component ID[1980] Application ID [68]

When deploying to 2 nodes, the first gets the lock, runs migrate successfully, happy days.
The second then got the acquired the lock, skips the bootstrap but doesn't seem to have the updated schema metadata so it tries to apply the "create schema updates table".
This fails with an exception as it already exists.
[2017-01-06 11:58:09,563] lcm INFO [main] u.s.c.Lock - [] Attempting to acquire lock for 'channel_metadata.schema_migration', using client id '93155931-75b4-4fae-8be9-3dc51c111f4c' [2017-01-06 11:58:09,584] lcm INFO [main] u.s.c.CassandraLockingMechanism - [] Lock currently held by Row[false, channel_metadata.schema_migration, 948f2e5f-8447-4093-bd7f-cf32be7289f1] [2017-01-06 11:58:10,095] lcm INFO [main] u.s.c.CassandraLockingMechanism - [] Lock currently held by Row[false, channel_metadata.schema_migration, 948f2e5f-8447-4093-bd7f-cf32be7289f1] [2017-01-06 11:58:10,644] lcm INFO [main] u.s.c.CassandraLockingMechanism - [] Lock currently held by Row[false, channel_metadata.schema_migration, 948f2e5f-8447-4093-bd7f-cf32be7289f1] [2017-01-06 11:58:11,171] lcm INFO [main] u.s.c.CqlMigratorImpl - [] Loading cql files from [/tmp/cqlmigrate7688832900537474535] [2017-01-06 11:58:11,180] lcm INFO [main] u.s.c.KeyspaceBootstrapper - [] Keyspace found, not applying bootstrap.cql [2017-01-06 11:58:11,206] lcm ERROR [main] u.s.c.CqlLoader - [] Failed to execute cql statements [CREATE TABLE schema_updates (filename text primary key, checksum text, applied_on timestamp);]: Table channel_metadata.schema_updates already exists

Would be nice to have this functionality with Cassandra 3 (and DataStax 5). would nice to be backward compatible.

Each DDL change should be separated by a fixed delay to ensure we cater for time drift between the nodes: delay >= max node time drift

When performing DDL changes, cassandra is always using the node timestamp when announcing alter table statements or other DDL changes regardless of whether a custom timestamp generator is provided when creating the session. As each DDL change can be executed on a different node, we need to wait long enough to ensure upgrades are timestamp ordered across all nodes.

CqlMigrate should work in both versions, and should be able to be tested within both versions.
Could be done by creating three modules: one for CqlMigrate and two other for tests. The latter should share the tests meanwhile is possible (no break changes)

Using cqlmigrate requires too many steps out of the box. We should provide sensible defaults for the lock configuration and not require a user to provide it.

Similarly the migrator api could be a bit nicer, by not requiring asList.

On running cqlmigrate a new DirectoryStream object is created for each directory.

This is not closed, leading to the file descriptor resource being held for the duration of the run.

This is not so much an issue when cqlmigrate is being run once with the odd directory, but is a much larger issue when running cqlmigrate thousands of times. This results in the JVM's resources being filled up. We experienced this when running functional tests.

Current Implementation CqlPaths:

        directories.stream()
                .map(CqlPaths::directoryStreamFromPath)
                .flatMap(directoryStream -> StreamSupport.stream(directoryStream.spliterator(), false))
                .forEach(path -> addPathToMap(cqlPathsMap, path));

Proposed Solution:

    directories.stream()
                .forEach( directory -> {
                    try( DirectoryStream<Path> directoryStream = directoryStreamFromPath(directory)) {
                        StreamSupport.stream(directoryStream.spliterator(), false)
                                .forEach(path -> addPathToMap(cqlPathsMap, path));
                    } catch(IOException e){
                        throw new UncheckedIOException(e);
                    }
                });

After upgrade from 0.9.6 to 0.9.8, previously working cql files no longer parse.

If a CQL files as more than one 'empty' line at the end, parsing fails with:

File had a non-terminated cql line
java.lang.IllegalStateException: File had a non-terminated cql line
at uk.sky.cqlmigrate.CqlFileParser$LineProcessor.check(CqlFileParser.java:179)
at uk.sky.cqlmigrate.CqlFileParser$LineProcessor.access$100(CqlFileParser.java:39)
at uk.sky.cqlmigrate.CqlFileParser.getCqlStatementsFrom(CqlFileParser.java:34)
at uk.sky.cqlmigrate.CqlFileParserTest.trailingEmptyLinesAreAllowed(CqlFileParserTest.java:102)

This breaks backward compatibility. Changing old changelogs to remove the 'redundant' trailing empty lines is not an option, since that breaks SHA1's.

The list of comment styles in https://github.com/sky-uk/cqlmigrate/blob/master/src/main/java/uk/sky/cqlmigrate/CqlFileParser.java#L45-L48 does not include lines starting with //, which according to https://docs.datastax.com/en/cql/3.1/cql/cql_reference/cqlRefComment.html is perfectly valid.

Builds are currently intermittently failing due to:

uk.sky.cqlmigrate.ClusterHealthTest > shouldThrowExceptionIfHostIsDown FAILED
    java.lang.NullPointerException at ClusterHealthTest.java:70

https://travis-ci.org/sky-uk/cqlmigrate/builds/279515435#L1065

• core - contains the library only
• cli - contains a standalone jar that can be run at the command line
• dw - dropwizard plugin to add cqlMigrate task

We currenty upload the cqlmigrate artefacts to Bintray. Unfortunately, it's being shut down so we need to find an alternative solution. https://jfrog.com/blog/into-the-sunset-bintray-jcenter-gocenter-and-chartcenter/

Is there currently a way of using this tool without locking? I would like to use it with scylla db, but scylla does not currently allow for LWT. If not, I can work on adding a configuration to allow for use without locking.

Cassandra driver 3.7.x doesn't work with metrics 4.x - the JMXReporter class has moved in metrics making it incompatible.

https://docs.datastax.com/en/developer/java-driver/3.5/manual/metrics/ details how to address this; disabling the JMX reporter when constructing the cluster and then optionally, explicitly setting up JMX reporting if desired.

I'm unsure if users of this library rely on the JMXReporter behaviour, so unsure whether to simply disable it always, or if there is a need to add support to optionally enable it.

I am using below code line to execute my Java App which is simply using cqlmigrate as maven dependency.
schemas = Paths.get(cqlmigrateApp.class.getResource("/cql/").toURI());
When I run the java program as a stand alone it works fine , keyspace gets created and .cql files run successfully. But when using same code I build a fat jar and run it. Then it seems the code is unable to obtain the /cql folder path from within the jar. And exception is thrown java.nio.file.FileSystemNotFoundException.
The program stops here and the lock is held on schema and I can not do anything.
Could anyone help here please ?
Regards
Chandan

We ran into a problem where we replaced some cassandra nodes. The cluster was perfectly fine, but the old nodes were still in the system.peers table. So the migration failed, since it saw the old dead node as unhealthy.

I'd prefer if:

• Cqlmigrate took the number of expected nodes in the cluster
• Ensure that a quorum is healthy (rather than all)

I think that should be sufficient to give us confidence that a schema change is safe to apply. The drawback is it adds more configuration - perhaps a simplification is to just check that a quorum of getAllHosts is available, rather than all of them.

Is it possible to make changes in keyspace cqlmigration configurability?

The ClusterHealth check is considered unhealthy if some nodes are unreachable even if the configured consistencyLevel can be satisfied.

Our app can is healthy and able to serve clients, but cqlmigrate prevents it from starting if the cluster is considered unhealthy.

We've run into this problem a few times in our dev environments. When the cluster is in a state that quorum will not be met for the CQL updates, CQLMigrate executes as follows:

• Obtain lock (via insert if not exists)
• Attempt to execute CQL updates
• Fail to achieve write consistency, and fail the CQLMigrate run
• Do not release lock

While this behaviour is great, it means that if the cluster recovers itself (eg, an unavailable node is restarted) then it requires manual intervention to release locks explicitly before re-running the jobs. Recently the ability to specify lock consistency levels was added, but that has effectively the same behaviour, since the create-lock call will fail but the lock will still be inserted on nodes.

We'd like to suggest the following behaviour change to try and make cqlmigrate recover more gracefully:

• Attempt to read locks table at specified locks consistency
• Obtain lock (via insert if not exists)
• Attempt to execute CQL updates
• ... as before

In this scenario, if the locks consistency cannot be met then the locks read will fail, causing cqlmigrate to fail but no lock to be held, and the whole process to be very easily retryable. Should an error occur when obtaining locks or executing cql updates, the lock continues to be held in the same way today.

I'm unsure if this would be considered a breaking change, it's a change in behaviour such that a run may fail but no lock would be held, but from our perspective that would be an improved feature rather than fundamental behaviour change.

Assuming this is acceptable we're happy to raise a PR for the changes, but wanted to open it up to discussions first.

Followed example in the README, and fails with the following.

uk.sky.cqlmigrate.exception.CannotAcquireLockException: Query to prepare locks queries failed
[info]   at uk.sky.cqlmigrate.CassandraLockingMechanism.init(CassandraLockingMechanism.java:43)
[info]   at uk.sky.cqlmigrate.Lock.lock(Lock.java:38)
[info]   at uk.sky.cqlmigrate.CqlMigratorImpl.migrate(CqlMigratorImpl.java:77)
[info]   ...

[info]   Cause: com.datastax.driver.core.exceptions.InvalidQueryException: Keyspace cqlmigrate does not exist
[info]   at com.datastax.driver.core.exceptions.InvalidQueryException.copy(InvalidQueryException.java:50)
[info]   at com.datastax.driver.core.DriverThrowables.propagateCause(DriverThrowables.java:37)
[info]   at com.datastax.driver.core.AbstractSession.prepare(AbstractSession.java:98)
[info]   at uk.sky.cqlmigrate.CassandraLockingMechanism.init(CassandraLockingMechanism.java:39)
[info]   at uk.sky.cqlmigrate.Lock.lock(Lock.java:38)
[info]   at uk.sky.cqlmigrate.CqlMigratorImpl.migrate(CqlMigratorImpl.java:77)

Code:

import java.io.{FileNotFoundException, File}
import java.nio.file.{Paths, Path}
import java.time.Duration

import com.datastax.driver.core.Session
import uk.sky.cqlmigrate.{CqlMigratorFactory, CqlMigrator, CassandraLockConfig}
import scala.collection.JavaConversions._

...

val lockConfig: CassandraLockConfig =
      CassandraLockConfig.builder()
      .withTimeout(Duration.ofSeconds(3))
      .withPollingInterval(Duration.ofMillis(100))
      .build()

    val migrator: CqlMigrator = CqlMigratorFactory.create(lockConfig)
    val schemas: Path = Paths.get(
      Option(ClassLoader.getSystemResource(toSchemaResourcePath.stripPrefix("/")))
      .getOrElse(throw new FileNotFoundException(toSchemaResourcePath))
      .toURI())
    migrator.migrate(session, keyspace, List(schemas))

In order for the locking mechanism to work, each application instance has to use a unique client ID.

The CassandraLockConfig.withClientId() method is potentially misleading - if a client application uses a static value for this, instead of a UUID, then multiple instances can share the same lock and schema chaos may result.

@adamdougal and I believe this method should be either:

• removed entirely, or
• at least renamed to make it clear that the value provided should be a unique ID.

Although #29 is still needed for DDL changes, I believe we can and should ensure greater robustness of DML changes by using client-side timestamps (as described in http://www.datastax.com/dev/blog/java-driver-2-1-2-native-protocol-v3) for the CQL statements invoked by cqlmigrate.

Any thoughts / counter-opinions? If not, I'll raise a PR to cover this.

The typical model for using cqlmigrate is for it to run at application startup, but for applications deployed to Kubernetes an alternative approach is to run it as a Kubernetes job before rolling out a new version of the application.

It would be helpful if there was a prebuilt cqlmigrate Docker image for this purpose.

The caveats section of the README says this about schema change safety:

Generally, adding columns is safe (but be careful with collection types).

Might it be possible to add some more information about the possible problems with adding collection columns or perhaps link to documentation or some relevant issue(s) if any?

Trying to run from within a jar on files contained within that jar, the following exception is hit.

[2015-12-17 17:04:55,095] umv-service INFO [main] u.s.c.CqlMigratorImpl - [request=] [client=] [x-skyint-requestid=] [] [] [] [] Loading cql files from [cqlschema/common, cqlschema/local] Exception in thread "main" java.lang.UnsupportedOperationException at com.sun.nio.zipfs.ZipPath.toFile(ZipPath.java:597) at uk.sky.cqlmigrate.CqlPaths.lambda$create$4(CqlPaths.java:28) at java.util.stream.ReferencePipeline$3$1.accept(ReferencePipeline.java:193) at java.util.ArrayList$ArrayListSpliterator.forEachRemaining(ArrayList.java:1374) at java.util.stream.AbstractPipeline.copyInto(AbstractPipeline.java:481) at java.util.stream.AbstractPipeline.wrapAndCopyInto(AbstractPipeline.java:471) at java.util.stream.ForEachOps$ForEachOp.evaluateSequential(ForEachOps.java:151) at java.util.stream.ForEachOps$ForEachOp$OfRef.evaluateSequential(ForEachOps.java:174) at java.util.stream.AbstractPipeline.evaluate(AbstractPipeline.java:234) at java.util.stream.ReferencePipeline.forEach(ReferencePipeline.java:418) at uk.sky.cqlmigrate.CqlPaths.create(CqlPaths.java:32) at uk.sky.cqlmigrate.CqlMigratorImpl.migrate(CqlMigratorImpl.java:83) at uk.sky.cqlmigrate.CqlMigratorImpl.migrate(CqlMigratorImpl.java:65)

I gather from @adamdougal that there's been a fair amount of refactoring from the legacy CqlMigrate to update to Java 8 - and I'm guessing that thus far this (mainline) use case has not been tested?

This suggests to me that we probably need the following:

• Integration tests to prove running CqlMigrate from within a JAR
• This issue to be fixed.

Hi,

Is it possible to use clqmigrate 0.9.6 version jar as standalone app like here I just realized, that it doesn't contains dependencies.

We have noticed this repository was marked as public. There are certain standards required before a repository in the sky-uk org can be made public, including the presence of valid LICENSE.md, README.md, and CONTRIBUTING.md files.

• Repos without license information are immediately made private.
• Repos failing the other criteria will be made private after 30 days.

Please resolve these problems ASAP and close this GitHub issue when you have done so.

If you have any questions, please ask them on the #github Slack channel.

There is an internal locking mechanism which enables multiple application make changes to the same database on one on one basis so that they do not step on each others feet.
What is the waiting timeout duration currently set in the tool ?
How can we increase this duration ?

Hi,

I can see some changes in master that include No op locking.
It is possible to release new version of cqlmigrate that include these?

Thanks

At the moment cqlmigrate requires that all nodes in all datacentres are up before it will run. We would like to make this check optional so that it will still run if a node is down so that the migration will be attempted with the supplied consistency level - which we will plan to set to LOCAL_QUORUM for read and EACH_QUORUM for write for our migrations.

https://github.com/datastax/simulacron/tree/master/doc/java_api

From here:

Version 4 is major redesign of the internal architecture. As such, it is not binary compatible with previous versions. However, most of the concepts remain unchanged, and the new API will look very familiar to 2.x and 3.x users.

The "not binary compatible" will require changes in this library as well as existing testing tools (like scassandra).

References

• https://github.com/datastax/java-driver/tree/4.x/upgrade_guide
• https://docs.datastax.com/en/developer/java-driver/4.0/changelog/
• https://docs.datastax.com/en/developer/java-driver/4.0/manual/core/

To enforce whitespace / un-used imports etc so we don't waste time reviewing that type of stuff

I would like the ability to provide a bootstrap.template, with the keyspace name, and the replication factor as replaceable values, so that don't have to duplicate the bootstrap.cql for each environment.

This should surely obey the read and write consistency levels (LOCAL_ONE and ALL, respectively) that are used elsewhere in cqlmigrate?

@adamdougal @chbatey @sebbonnet @jsravn @balooo your thoughts welcomed.

Currently in 0.9.0, the consistency level is defaulted to ALL for both reads and writes when performing migrations. Enforcing ConsistencyLevel.ALL for both might be a little stringent, and has been known to cause read timeouts when attempting to read the schema_updates table when looking to see which files have already been applied. Allowing the user to define what they want as the read/write consistency level would allow more flexibility.