Why Cassandra throwing com.datastax.driver.core.exceptions.InvalidQueryException: Multiple definitions found for column

SHAKU picture SHAKU · Aug 1, 2014 · Viewed 7.8k times · Source

Context:

I am running a jUnit test in eclipse by using embedded Cassandra to test my DAO class which is using an Astyanax client configured for JavaDriver. When DAO object instance insert into Cassandra I am getting this exception com.datastax.driver.core.exceptions.InvalidQueryException: Multiple definitions found for column ..columnname

TestClass

public class LeaderBoardDaoTest {

    private static LeaderBoardDao   dao;
    public static CassandraCQLUnit  cassandraCQLUnit;

    private String                  hostIp  = "127.0.0.1";
    private int                     port    = 9142;
    public Session                  session;
    public Cluster                  cluster;

    @BeforeClass
    public static void startCassandra() throws IOException, TTransportException, ConfigurationException, InterruptedException {
        System.setProperty("archaius.deployment.applicationId", "leaderboardapi");
        System.setProperty("archaius.deployment.environment", "test");

        EmbeddedCassandraServerHelper.startEmbeddedCassandra("cassandra.yaml");
        // cassandraCQLUnit = new CassandraCQLUnit(new
        // ClassPathCQLDataSet("simple.cql", "lbapi"), "cassandra.yaml");
        Injector injector = Guice.createInjector(new TestModule());
        dao = injector.getInstance(LeaderBoardDao.class);

    }

    @Before
    public void load() {
        cluster = new Cluster.Builder().withClusterName("leaderboardcassandra").addContactPoints(hostIp).withPort(port).build();
        session = cluster.connect();
        CQLDataLoader dataLoader = new CQLDataLoader(session);
        dataLoader.load(new ClassPathCQLDataSet("simple.cql", "lbapi"));
        session = dataLoader.getSession();
    }


    @Test
    public void test() {
        ResultSet result = session.execute("select * from mytable WHERE id='myKey01'");
        Assert.assertEquals(result.iterator().next().getString("value"), "myValue01");
    }

    @Test
    public void testInsert() {
        LeaderBoard lb = new LeaderBoard();
        lb.setName("name-1");
        lb.setDescription("description-1");
        lb.setActivityType(ActivityType.FUEL);
        lb.setImage("http:/");
        lb.setLbId(UUID.fromString("3F2504E0-4F89-41D3-9A0C-0305E82C3301"));
        lb.setStartTime(new Date());
        lb.setEndTime(new Date());
        dao.insert(lb);
        ResultSet resultSet = session.execute("select * from leaderboards WHERE leaderboardid='3F2504E0-4F89-41D3-9A0C-0305E82C3301'");
    }

    @After
    public void clearCassandra() {
        EmbeddedCassandraServerHelper.cleanEmbeddedCassandra();
    }

    @AfterClass
    public static void stopCassandra() {
        EmbeddedCassandraServerHelper.stopEmbeddedCassandra();
    }
}

Class under test

@Singleton
public class LeaderBoardDao {

    private static final Logger                 log                 = LoggerFactory.getLogger(LeaderBoardDao.class);

    @Inject
    private AstyanaxMutationsJavaDriverClient   client;

    private static final String                 END_TIME            = "end_time";
    private static final String                 START_TIME          = "start_time";
    private static final String                 IMAGE               = "image";
    private static final String                 ACTIVITY_TYPE       = "activity_type";
    private static final String                 DESCRIPTION         = "description";
    private static final String                 NAME                = "name";
    private static final String                 LEADERBOARD_ID      = "leaderboardID";
    private static final String                 COLUMN_FAMILY_NAME  = "leaderboards";

    private ColumnFamily<UUID, String>          cf;

    public LeaderBoardDao() throws ConnectionException {
        cf = ColumnFamily.newColumnFamily(COLUMN_FAMILY_NAME, UUIDSerializer.get(), StringSerializer.get());
    }

    /**
     * Writes the Leaderboard to the database.
     * 
     * @param lb
     */
    public void insert(LeaderBoard lb) {
        try {
            MutationBatch m = client.getKeyspace().prepareMutationBatch();
            cf.describe(client.getKeyspace());
            m.withRow(cf, lb.getLbId()).putColumn(LEADERBOARD_ID, UUIDUtil.asByteArray(lb.getLbId()), null).putColumn(NAME, lb.getName(), null).putColumn(DESCRIPTION, lb.getDescription(), null)
                    .putColumn(ACTIVITY_TYPE, lb.getActivityType().name(), null).putColumn(IMAGE, lb.getImage()).putColumn(START_TIME, lb.getStartTime()).putColumn(END_TIME, lb.getEndTime());

            m.execute();
        } catch (ConnectionException e) {
            Throwables.propagate(e);
        }
    }

    /**
     * Reads leaderboard from database
     * 
     * @param id
     * @return {@link LeaderBoard}
     */
    public LeaderBoard read(UUID id) {
        OperationResult<ColumnList<String>> result;
        LeaderBoard lb = null;
        try {
            result = client.getKeyspace().prepareQuery(cf).getKey(id).execute();

            ColumnList<String> cols = result.getResult();
            if (!cols.isEmpty()) {
                lb = new LeaderBoard();
                lb.setLbId(cols.getUUIDValue(LEADERBOARD_ID, null));
                lb.setName(cols.getStringValue(NAME, null));
                lb.setActivityType(ActivityType.valueOf(cols.getStringValue(ACTIVITY_TYPE, null)));
                lb.setDescription(cols.getStringValue(DESCRIPTION, null));
                lb.setEndTime(cols.getDateValue(END_TIME, null));
                lb.setStartTime(cols.getDateValue(START_TIME, null));
                lb.setImage(cols.getStringValue(IMAGE, null));
            } else {
                log.warn("read: is empty: no record found for " + id);
            }

            return lb;
        } catch (ConnectionException e) {
            log.error("failed to read from C*", e);
            throw new RuntimeException("failed to read from C*", e);
        }
    }

}

Answer

Olivier Michallat picture Olivier Michallat · Aug 3, 2014

When the Java driver throws an InvalidQueryException, it's rethrowing an error from Cassandra. The error "Multiple definitions found for column..." indicates that a column is mentioned more than once in an update statement. You can simulate it in cqlsh:

cqlsh> create table test(i int primary key);
cqlsh> insert into test (i, i) values (1, 2);
code=2200 [Invalid query] message="Multiple definitions found for column i"

I'm not familiar with Astyanax, but my guess is that it already adds the id to the query when you call withRow, so you don't need to add it again with putColumn. Try removing that call (second line in reformatted sample below):

m.withRow(cf, lb.getLbId())
 .putColumn(LEADERBOARD_ID, UUIDUtil.asByteArray(lb.getLbId()), null)
 ... // other putColumn calls