cassandra-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Alex Popescu <al...@datastax.com>
Subject Re: Getting code=2200 [Invalid query] message=Invalid column name ... while executing ALTER statement
Date Fri, 13 Nov 2015 19:00:03 GMT
I'm glad to hear that you got it working; but I'd suggest trying to answer
these questions before moving forward with this solution:

1. is the set of columns really that dynamic? if not, then define them
upfront. there's no weight to empty columns.
2. if the set of columns is really dynamic, would using 1/more map
column(s) be better?

Avoiding to modify the schema dynamically and avoid concurrent schema
changes is always better.


On Fri, Nov 13, 2015 at 7:40 AM, Rajesh Radhakrishnan <
Rajesh.Radhakrishnan@phe.gov.uk> wrote:

> We got a work around now!
>
> Thank you Laing for the reply.
> Yes I do agree with your point, but we got a scenario where the columns
> need to be added in the later stage of the process.
> We are doing the following:
>
>    1. CREATE THE TABLE IF NOT EXISTS
>    2. INSERT IDS INTO THE TABLE
>    3. CHECK THE COLUMN NAMES OF THE TABLE
>    4. GET A LIST OF _NAMES (PYTHON SCRIPT)
>    5. ALTER TABLE IF THE _NAME(S) DONT EXIST IN COLUMN NAME
>    6. UPDATE TABLE WITH THE VALUE OF THE NEW COLUMN
>
> In our process step 3 to 6 are repeated.
>
> Now what I did is replaced session.execute(...) to
> session.execute_async(...) only for ALTER and UPDATE statements.
>
> And introduced 1 sec sleep for each ALTER statement and 5 sec sleep before
> UPDATE statement.
>
> It WORKS! now. I dont know this is right solution, but its a work around.
>
> So clearly some config value need to be updated for some parameter in
> cassandra.yaml
>
> Do you know which one?
>
>
>
> ------------------------------
> *From:* Laing, Michael [michael.laing@nytimes.com]
> *Sent:* 13 November 2015 12:26
>
> *To:* user@cassandra.apache.org
> *Subject:* Re: Getting code=2200 [Invalid query] message=Invalid column
> name ... while executing ALTER statement
>
> Dynamic schema changes are generally a bad idea, especially if they are
> rapid.
>
> You should rethink your approach.
>
> On Fri, Nov 13, 2015 at 7:20 AM, Rajesh Radhakrishnan <
> Rajesh.Radhakrishnan@phe.gov.uk> wrote:
>
>>
>> Thank you Carlos for looking.
>> But when I rand the nodetool describecluster.
>> It is showing the same schema versions for both nodes?
>>
>> So it is something else! Please help me from this bottleneck. Thank you.
>>
>> ------------------------------
>> *From:* Carlos Alonso [info@mrcalonso.com]
>> *Sent:* 13 November 2015 11:55
>> *To:* user@cassandra.apache.org
>> *Subject:* Re: Getting code=2200 [Invalid query] message=Invalid column
>> name ... while executing ALTER statement
>>
>> Maybe schema disagreement?
>>
>> Run nodetool describecluster to discover
>>
>> Carlos Alonso | Software Engineer | @calonso
>> <https://twitter.com/calonso>
>>
>> On 13 November 2015 at 11:14, Rajesh Radhakrishnan <
>> Rajesh.Radhakrishnan@phe.gov.uk> wrote:
>>
>>>
>>> Hi,
>>>
>>> I am using Cassandra 2.1.5 in a cluster of two nodes (running CentOS)
>>> and using Python driver to connect to Cassandra.
>>> My Python code snippet is show here:
>>>
>>>
>>> #-------------------------------------------------------------------------------------------------------------------
>>> import time, os, datetime, keyword
>>> import uuid
>>> from cassandra.cluster import Cluster
>>> import os.path, sys
>>> ....
>>> from cassandra.auth import PlainTextAuthProvider
>>> ....
>>>        auth_provider = PlainTextAuthProvider(username, password)
>>>        cluster = Cluster([node1,node2],auth_provider=auth_provider)
>>>        session = cluster.connect();
>>>
>>>         session.execute("CREATE table IF NOT EXISTS test.iau ("
>>>                     "id uuid, "
>>>                     "sample_id text, "
>>>                     "PRIMARY KEY (sample_id) )");
>>>
>>>         print " \n created the table"
>>>         #--------
>>>
>>>         sqlInsertSampleIdUid = "INSERT INTO test.iau (id, sample_id)
>>> VALUES ("+str(uuid.uuid1())+",'sample123')"
>>>         session.execute(sqlInsertSampleIdUid)
>>>         print " \n Inserted main ids into the table"
>>>         #-------
>>>
>>>         colNames
>>> =['col1','col2','col3','col4','col5','col6','col7','col8','col9']
>>>
>>>         for colName in colNames :
>>>
>>>             sqlAlterStatement1 = " ALTER TABLE test.iau ADD "+colName+"
>>> text"
>>>             print sqlAlterStatement1
>>>             session.execute(sqlAlterStatement1)
>>>             sqlAlterStatement1 = None
>>>
>>>         print " Altered tables :: "
>>>         # ----------------------------------------
>>>         count = 0
>>>         for colName in colNames :
>>>             count = count +1
>>>             sqlUpdateGeneDetection = " UPDATE test.iau SET "+colName+" =
>>> '"+str(count)+"' WHERE sample_id = 'sample123'"
>>>             session.execute(sqlUpdateGeneDetection)
>>>             sqlUpdateGeneDetection = None
>>>
>>>         print " Updated tables :: "
>>>         session.cluster.shutdown()
>>> ....
>>>
>>> #-------------------------------------------------------------------------------------------------------------------
>>>
>>> Very rarely this code works, but most of the time it fails when it reach
>>> ALTER statement.
>>> FYI, I tried preparedstatement with binding in INSERT, UPDATE statements
>>> too.
>>>
>>> The error with output is shown here:
>>>
>>> #------
>>> created the table
>>>
>>>  Inserted main ids into the table
>>>  ALTER TABLE test.iau ADD col1 text
>>>  ALTER TABLE test.iau ADD col2 text
>>>  ALTER TABLE test.iau ADD col3 text
>>>  ALTER TABLE test.iau ADD col4 text
>>>  ALTER TABLE test.iau ADD col5 text
>>>  ALTER TABLE test.iau ADD col6 text
>>>  ALTER TABLE test.iau ADD col7 text
>>>  ALTER TABLE test.iau ADD col8 text
>>>  ALTER TABLE test.iau ADD col9 text
>>> E
>>> ======================================================================
>>>
>>> ----------------------------------------------------------------------
>>> Traceback (most recent call last):
>>>   File "UnitTests.py", line 313, in test_insert_data
>>>     session.execute(sqlAlterStatement1)
>>>   File "/usr/local/lib/python2.7/site-packages/cassandra/cluster.py",
>>> line 1405, in execute
>>>     result = future.result(timeout)
>>>   File "/usr/local/lib/python2.7/site-packages/cassandra/cluster.py",
>>> line 2976, in result
>>>     raise self._final_exception
>>> InvalidRequest: code=2200 [Invalid query] message="Invalid column name
>>> col9 because it conflicts with an existing column"
>>>
>>> ----------------------------------------------------------------------
>>> Ran 1 test in 9.856s
>>> #------
>>>
>>> But when I checked the table using CQL col9 is not there.
>>>
>>> Is there' schema' refresh issue or is it bug in Cassandra 2.1.5?
>>>
>>> Thank you.
>>> Kind regards
>>> Rajesh R
>>>
>>>
>>>
>>>
>>> **************************************************************************
>>> The information contained in the EMail and any attachments is
>>> confidential and intended solely and for the attention and use of the named
>>> addressee(s). It may not be disclosed to any other person without the
>>> express authority of Public Health England, or the intended recipient, or
>>> both. If you are not the intended recipient, you must not disclose, copy,
>>> distribute or retain this message or any part of it. This footnote also
>>> confirms that this EMail has been swept for computer viruses by
>>> Symantec.Cloud, but please re-sweep any attachments before opening or
>>> saving. http://www.gov.uk/PHE
>>>
>>> **************************************************************************
>>>
>>
>>
>> **************************************************************************
>> The information contained in the EMail and any attachments is
>> confidential and intended solely and for the attention and use of the named
>> addressee(s). It may not be disclosed to any other person without the
>> express authority of Public Health England, or the intended recipient, or
>> both. If you are not the intended recipient, you must not disclose, copy,
>> distribute or retain this message or any part of it. This footnote also
>> confirms that this EMail has been swept for computer viruses by
>> Symantec.Cloud, but please re-sweep any attachments before opening or
>> saving. http://www.gov.uk/PHE
>> **************************************************************************
>>
>
>
> **************************************************************************
> The information contained in the EMail and any attachments is confidential
> and intended solely and for the attention and use of the named
> addressee(s). It may not be disclosed to any other person without the
> express authority of Public Health England, or the intended recipient, or
> both. If you are not the intended recipient, you must not disclose, copy,
> distribute or retain this message or any part of it. This footnote also
> confirms that this EMail has been swept for computer viruses by
> Symantec.Cloud, but please re-sweep any attachments before opening or
> saving. http://www.gov.uk/PHE
> **************************************************************************
>



-- 
Bests,

Alex Popescu | @al3xandru
Sen. Product Manager @ DataStax

Mime
View raw message