Originally from the User Slack
@Carlos_V: Is there a reason why a query, using gocql, will return paginated data smaller than PageSize() size, and iter.PageState() for next page will be either empty or using the result as PageState(token) won’t return anything?
Querying directly from database using cqlsh, I have a query with 207 rows, but it will only return 205, if I put limit 206 it will return 205 and 30 minutes lates it will return the 206 one.
@Marko_Ćorić: All under the same cluster key?
And after 30 minutes you see 206th record?
@Botond_Dénes: you probably have tons of tombstones
@Carlos_V: Yes, after the 30 minutes waiting the --MORE-- the 206 comes back.
This is a materializes view.
I did run a nodetool flush and repair, no change.
@Botond_Dénes: materialized views do have tombstones
repair won’t help, try nodetool compact
@Carlos_V: Let me try
still running compact, in the meantime regarding cluster key question, it a single node scylla instance and the materialized view is this:
PRIMARY KEY ((1), 2, 3, 4, 5, 6)
query:
SELECT 5, 6, ... FROM view WHERE 1 = ? AND 2 = false ORDER BY 3 DESC, 4 ASC
@Botond_Dénes: this is from the MV definition?
@Carlos_V: Yes
After 3 hours, node compact has finished, but the same issue happens, seems to be with the same 2 rows. I’m supposed to have 177 rows now. Using limit 175 is fine, with limit 176 then it wait for a long time until it comes back.
Funnily enough if I run the same query with COUNT(1) I get 177 qty.
@Botond_Dénes: looks like your tombstones could not be purged
@Carlos_V: Perhaps I don’t have enough space for that? Use% 77%
@Botond_Dénes: tombstones won’t be purged before gc_grace_seconds, this is 10 days by default
@Carlos_V: Is there a way to force?
the 10 days have passed and the data is surely returning much faster now, only a few seconds now. Count is now 187. If I do LIMIT 187 100 comes then --MORE-- and then another 85 come, and then another --MORE-- and then the 2 come after less than a second. But the go query still won’t bring these two rows.
@Botond_Dénes: Maybe there is a problem in how the go driver handles empty pages
@Carlos_V: Any change this can be looked at?
@Marko_Ćorić: can you share that part of code?
@Carlos_V: This is the way I’m fetch data
@Marko_Ćorić: Sorry for delay, I had to find our implementation since we rewrite whole production to Rust. This is code that worked for us without single problem for like 2 years
func (GameSession) Get(c *gin.Context) ([]GameSession, error) {
nickName := c.Params.ByName("nickName")
session, err := NewSession(nickName[:3)
if err != nil {
return nil, err
}
provider := c.Params.ByName("provider")
// Create log
log := logger.NewLogger(skin)
// Create context
ctx := context.Background()
cql := `SELECT
transaction_type, transaction_time, game_name, round_id, amount, round_details
FROM game_rounds
WHERE
nick_name = ? AND external_type = ? AND transaction_time > ? AND transaction_time < ?`
params := []interface{}{
nickName, provider,
fmt.Sprintf("%s 00:00:00.00000", c.DefaultQuery("from", time.Now().AddDate(-1, 0, 0).Format(DateTimeFormatDate))),
fmt.Sprintf("%s 23:59:59.99999", c.DefaultQuery("to", time.Now().Format(DateTimeFormatDate))),
}
log.Debugf("PlayersGameSessions.Get CQL: %v", cql)
log.Infof("PlayersGameSessions.Get PARAMS: %+v", params)
finalResults := make([]GameSession, 0)
var pageState []byte
for {
iter := session.Query(cql, params...).WithContext(ctx).PageSize(1000).PageState(pageState).Iter()
nextPageState := iter.PageState()
var transactionType, transactionTime, gameName, roundID, roundDetails string
var amount int64
for iter.Scan(&transactionType, &transactionTime, &gameName, &roundID, &amount, &roundDetails) {
gameRound := GameSession{
TransactionType: transactionType,
TransactionTime: transactionTime,
Name: gameName,
RoundID: roundID,
Amount: amount,
}
// Filter freespins win (not display real/bonus) balance
jsonResults := map[string]interface{}{}
if gameRound.TransactionType != "freespins-win" {
if err := json.Unmarshal([]byte(roundDetails), &jsonResults); err == nil {
gameRound.Real = int64(jsonResults["available_real"].(float64))
gameRound.Bonus = int64(jsonResults["available_bonus"].(float64))
}
}
finalResults = append(finalResults, gameRound)
}
// Quit if there is no more pages
if len(nextPageState) == 0 {
_ = iter.Close()
break
}
pageState = nextPageState
}
// Sort results
sort.Slice(finalResults, func(i, j int) bool {
return finalResults[i].TransactionTime > finalResults[j].TransactionTime
})
return finalResults, nil
}