Schema inconsistency

sqlschema
(Mike Smith) #1

I have a benchmark which begins by creating a table if it doesn’t exist and then truncates it. After some number of runs truncate ended up hanging. So used cockroach sql to drop the table manually. Now the database seems to be in an inconsistent state:

root@:24442/yabf> CREATE TABLE object (type STRING, id STRING, st_apply_metadata BYTES, value BYTES, PRIMARY KEY(type, id));
pq: relation "object" already exists
root@:24442/yabf> SHOW TABLES;
+-------+
| Table |
+-------+
+-------+
(0 rows)
root@:24442/> drop database yabf;
pq: table "object" is being dropped

Curious if anyone has thoughts on what might be the root cause or links to the best way to debug issues like this.

(vivek) #2

I’m curious if you were eventually able to drop the database? Cockroachdb doesn’t support transactional drop of a table, because it’s designed to cache table descriptors on all nodes. However we recently added the capability to allow the table name to be released quickly, and that is in the latest beta.

(Mike Smith) #3

Yes, the problem eventually went away.

Is there any documentation on how CRDB handles schemas and schema changes?

(Jesse) #4

@msmith10101, this blog post provides a good overview. Eventually, we’ll add this topic to our official docs as well.

#5

Team, I am seeing this with 2.0.5:

root@:26257/db> drop table <tablename>;
pq: relation "cmssync_nodeserver_full" does not exist
root@:26257/db> CREATE TABLE <tablename> (columns....);
pq: relation "<tablename>" already exists

It’s been 1 hour now and still cannot do it… Also in the jobs table, there is still an “index” job that was isn progress but is no longer running (the cockroach process was restarted).

I tried cancelling the job but the command-line says the job is uncontrollable. How to fix schema issue?

#6

One more thing to add. When looking at the crdb_internal table, I see these drops waiting to happen. I assume they are due to ttl?

 select * from crdb_internal.tables where state <> 'PUBLIC';
  table_id | parent_id |          name           | database_name | version |             mod_time             |        mod_time_logical        |      format_version      | state | sc_lease_node_id | sc_lease_expiration_time |            drop_time             | audit_mode  
+----------+-----------+-------------------------+---------------+---------+----------------------------------+--------------------------------+--------------------------+-------+------------------+--------------------------+----------------------------------+------------+
       543 |        51 | nodeserver_old          | tickdb        |      28 | 2018-09-20 22:57:20.414173+00:00 | 1537484240414173022.0000000000 | InterleavedFormatVersion | DROP  |             NULL | NULL                     | 2018-09-20 22:57:20.405331+00:00 | DISABLED    
       544 |        51 | nodeserver_old          | tickdb        |      28 | 2018-09-20 23:00:27.282402+00:00 | 1537484427282402284.0000000000 | InterleavedFormatVersion | DROP  |             NULL | NULL                     | 2018-09-20 23:00:27.276694+00:00 | DISABLED    
       545 |        51 | nodeserver_old          | tickdb        |      28 | 2018-09-20 23:04:05.323017+00:00 | 1537484645323017368.0000000000 | InterleavedFormatVersion | DROP  |             NULL | NULL                     | 2018-09-20 23:04:05.315028+00:00 | DISABLED    
       546 |        51 | nodeserver_old          | tickdb        |      28 | 2018-09-20 23:06:15.04525+00:00  | 1537484775045249897.0000000000 | InterleavedFormatVersion | DROP  |             NULL | NULL                     | 2018-09-20 23:06:15.037628+00:00 | DISABLED    
       547 |        51 | nodeserver_old          | tickdb        |      28 | 2018-09-20 23:08:47.649785+00:00 | 1537484927649784979.0000000000 | InterleavedFormatVersion | DROP  |             NULL | NULL                     | 2018-09-20 23:08:47.642783+00:00 | DISABLED    
       548 |        51 | nodeserver_old          | tickdb        |      28 | 2018-09-20 23:11:02.683844+00:00 | 1537485062683843960.0000000000 | InterleavedFormatVersion | DROP  |             NULL | NULL                     | 2018-09-20 23:11:02.676254+00:00 | DISABLED    
       550 |        51 | cmssync_nodeserver_full | tickdb        |      17 | 2018-09-21 00:42:28.540565+00:00 | 1537490548540564730.0000000000 | InterleavedFormatVersion | DROP  |             NULL | NULL                     | 2018-09-20 23:14:20.106976+00:00 | DISABLED
(vivek) #7

This issue was fixed through https://github.com/cockroachdb/cockroach/commit/bff307a7dc25082b8d37ab1028ef0b0bfdd7b4c6
and will be available in our 2.1 release

In the mean time reducing the value of ttlseconds as described here to 10 minutes will allow reuse of the name.

https://www.cockroachlabs.com/docs/stable/configure-replication-zones.html

(vivek) #8

Yes, the tables have a delayed drop because of the GC TTL.

#9

Thanks @vivek!

Speaking of v2.1, I noticed “SHOW COLUMNS” etc type commands show different column names now. This will be in the final release?

Thanks,
-Dennis

(vivek) #10

I didn’t quite understand your question. Thanks

#11

Run the ‘show columns’ for CRDB 2.0.5 and 2.1. You will see the columns names of the table definition are different. in 2.0.5 it looks like this:

root@:26257/db> show columns from table;
±--------------±-------±-----±--------±----------------------+
| Field | Type | Null | Default | Indices |
±--------------±-------±-----±--------±----------------------+

In 2.1 ‘Field’ was changed to ‘column_name’.

What other schema type of changes are there in 2.1.x?

(Jesse) #12

Hi @dennisjbr,

Yes, this change was announced in the v2.1.0-alpha.20180730 release notes:

The column labels in the output of EXPLAIN and all SHOW statements have been renamed for consistency.

The change was implemented in this PR. All of the SHOW docs for v2.1 have been updated accordingly.

Best,
Jesse

#13

Thanks Jesse! Will re-read the release notes with glasses this time :smiley:

(Jesse) #14

My pleasure, @dennisjbr.

Also, harking back to the start of this thread, you might be interested in recently added docs on online schema changes.

Jesse