If it's MyISAM you can do it in two queries
SELECT COUNT(*) FROM yourTable;
SELECT * FROM yourTable LIMIT useTheCountHere - 1,1;
This is unreliable however because
- It assumes rows are only added to this table and never deleted.
- It assumes no other writes are performed to this table in the meantime (you can lock the table)
- MyISAM tables can be reordered using ALTER TABLE, so taht the insert order is no longer preserved.
It's not reliable at all in InnoDB, since this engine can reorder the table at will.
1 The cause of this problem is a CREATE TABLE statement collision. Do not generate tables dynamically from multiple clients, even with IF NOT EXISTS. First thing you need to do is fix your code so that this does not happen. Just create your tables manually from cqlsh allowing time for the schema to settle. Always wait for schema agreement when modifying schema.
2 Here's the fix:
1) Change your code to not automatically re-create tables (even with IF NOT EXISTS).
2) Run a rolling restart to ensure schema matches across nodes. Run nodetool describecluster around your cluster. Check that there is only one schema version.
ON EACH NODE:
3) Check your filesystem and see if you have two directories for the table in question in the data directory.
If THERE ARE TWO OR MORE DIRECTORIES:
4)Identify from schema_column_families which cf ID is the "new" one (currently in use).
cqlsh -e "select * from system.schema_column_families"|grep
5) Move the data from the "old" one to the "new" one and remove the old directory.
6) If there are multiple "old" ones repeat 5 for every "old" directory.
7) run nodetool refresh
IF THERE IS ONLY ONE DIRECTORY:
No further action is needed.
Futures
Schema collisions will continue to be an issue until - CASSANDRA-9424
Here's an example of it occurring on Jira and closed as not a problem
CASSANDRA-8387
Best Answer
[cqlsh 4.1.0 | Cassandra 2.0.4 | CQL spec 3.1.1 | Thrift protocol 19.39.0]
Currently, the command to use is: