英文:
gocql SELECT * doesn't return all columns
问题
我在尝试为我的应用程序实现一些计数器时，遇到了这种奇怪的行为。基本上，我创建了一个计数器表：
CREATE TABLE stats_dev.log_counters (
date text PRIMARY KEY,
all counter
);
然后，我还想计算一些特定类型的消息，所以在我的Go应用程序中，我使用ALTER语句向表中添加了之前没有的列。
我的应用程序正在不断增长，我开始有超过30个列（不应该超过50个），当我想要检索所有这些计数器时，结果中缺少一些列。
query := s.Query(`SELECT * FROM `+_apiCountersTable+` WHERE date IN ?`, dates)
res, err := query.Iter().SliceMap()
这给我返回了30个列中的34个。然而，当我在CQLSH上执行以下请求时：
cqlsh:stats_dev> SELECT * FROM api_counters WHERE date = 'total';
我得到了正确的完整结果。所以：
- 这是因为我的请求应该不同吗？
- 这可能是gocql驱动程序引起的吗？
- 这种模式完全愚蠢吗？
我的临时解决方案是从system.schema_columns表中选择列名，并将它们使用strings.Join()连接到我的SELECT查询中...
非常感谢您的帮助。
英文:
I came across this weird behaviour while trying to implement some counters for my application.
Basically, I did a counter table like so :
CREATE TABLE stats_dev.log_counters (
date text PRIMARY KEY,
all counter
);
Then I have some specific types of message I want to count as well, so in my Go app, I ALTER the table to add the column I didn't have before.
My app is growing, and I start to have more than 30 columns (shouldn't be more than 50) and when I want to retrieve all those counters, some columns are missing in the result.
query := s.Query(`SELECT * FROM `+_apiCountersTable+` WHERE date IN ?`, dates)
res, err := query.Iter().SliceMap()
This returns me something like 30 over 34 columns. Although, when I do the request on CQLSH :
cqlsh:stats_dev> SELECT * FROM api_counters WHERE date = 'total';
I get the proper full result. So :
- Does that come from my request which should be different ?
- Could that come from gocql driver ?
- Is that pattern completely stupid ?
My temporary solution is to SELECT the column names from the system.schema_columns table and to strings.Join() all of that to my SELECT query ...
Thank you very much for your help.
答案1
得分: 2
我对gocql库不太熟悉，但听起来你可能遇到了一些问题，其中包括没有重新准备语句和CASSANDRA-7910。
每当一个请求被准备（就像在Select * from ___ where date in ?中所做的那样），它会发送一个请求到Cassandra，Cassandra会响应该表的列元数据，因此当你从Cassandra获取查询的响应时，你知道可以查找哪些列。看起来gocql有一个名为"Automatic query preparation"的功能，它可能将你的请求视为准备好的语句。
当你修改一个表时，准备好的语句不会在客户端更新，所以修复这个问题的唯一方法是重新准备语句（不确定你是否可以从gocql控制这个级别）。然而，这仍然不起作用，因为Cassandra中存在一个bug（CASSANDRA-7910），它不返回新的列，因为它自己在缓存准备好的语句，并且在模式更改时不使其无效。这个问题在2.1.3中已经修复（即将发布），你可以尝试在git的cassandra-2.1分支上使用它，看看是否解决了你的问题。
当应用程序运行时，修改模式并不是一种异常模式，所以这应该是一个可以工作的场景，但不幸的是却不能工作。我建议查看一下是否有办法在gocql中重新准备语句。
我看到cluster.go中有一个名为stmtsLRU的变量。如果你能找到它，你可以使准备好的语句无效。如果没有办法做到这一点，最好向gocql提出一个问题，因为其他驱动程序可以重新准备语句。我知道Java驱动程序允许你这样做，但会给出警告。我想这可能是gocql和其他驱动程序之间的一个重大区别，其他驱动程序中你明确地使用准备好的语句对象，而在gocql中它会自动处理。
由于Cassandra中的bug仍然存在，我认为你应该避免使用准备好的语句，而是使用类似这样的查询：SELECT * FROM api_counters WHERE date = 'total';
英文:
I'm not familiar with the gocql library, but it sounds like you may be running into a combination of not repreparing your statements and CASSANDRA-7910.
Whenever a request is Prepared (like what is being done in Select * from ___ where date in ?), it sends a request to cassandra, which responds with the column metadata for that table, so when you get a response to a query back from cassandra, you know what columns are available to look for. It looks like gocql has a feature called Automatic query preparation which may be treating your request as a prepared statement.
When you alter a table, the prepared statement does not get updated on your client side, so really the only way to fix this is to reprepare your statement (not sure if you have that level of control from gocql). However this still doesn't work since there is a bug in cassandra (CASSANDRA-7910) where it does not return the new columns since it is itself caching the prepared statement on it's side and not invalidating it when the schema changes. This issue is fixed in 2.1.3 (coming soon), it may be worth trying this against the cassandra-2.1 branch in git to see if that resolves your issue.
It's not an abnormal pattern to alter your schema when your application is running, so this is a scenario that should work, but unfortunately doesn't. I'd look into seeing if there is a way to reprepare statements in gocql.
I see there is a stmtsLRU var in cluster.go. If you could somehow get to that you could invalidate prepared statements. If there isn't a way to do this, would be good to open up an issue against gocql as you can reprepare statements again in other drivers. I know that the java driver allows you to do this, but gives you a warning. I suppose this could be a big difference between gocql and the other drivers in that in the other drivers you explicitly use a prepared statement object, where in gocql it gets handled for you automatically in the library.
With the cassandra bug outstanding, I think you should stick to not using prepared statements and instead make queries like: SELECT * FROM api_counters WHERE date = 'total';
答案2
得分: 2
感谢Andy的帮助。
起初，根据你告诉我的，我认为我宁愿在system.schema_columns上执行SELCT column_name，并在修改表时刷新它。然后，我只需在SELECT FROM api_counters中使用strings.join()连接列。这种方法有效，但如果我有两个不同的实例，其中一个更新了模式，而另一个收到了GET请求，那个收到请求的实例将不知道新的列。
然后，我重新整理了我的想法，发现显然还有另一种方法，我只需更改为以下模式：
CREATE TABLE stats_dev.api_counters (
date text,
description text,
all counter,
PRIMARY KEY (date, description)
);
然后，我根据我期望的描述更新字段。到目前为止还不错。
我知道这绝对是选项3：我的模式不是最好的。
英文:
Thanks Andy for your help.
At first, I thought that considering what you told me, I would rather do a SELCT column_name on the system.schema_columns sometimes and refresh it when I alter my table. I would just then strings.join() the columns in my SELECT FROM api_counters. It worked but if I had 2 different instances, and one would update the schema and the other would receive a GET request, this one would not know the new column still.
And then I rearranged my ideas and found out that there was obviously an other way of doing that and I simply change for this schema :
CREATE TABLE stats_dev.api_counters (
date text,
description text,
all counter,
PRIMARY KEY (date, description)
);
and I am updating the field based on the description I am expecting. So far so good.
I knew it was definitely Option 3 : my pattern was not the best one.
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论