sql: drop table and recreate fail #7348

idsj · 2016-06-21T03:21:13Z

Please follow the steps below to help us help you.

header
I160621 11:06:31.940101 util/log/clog.go:998 [config] file created at: 2016/06/21 11:06:31
I160621 11:06:31.940101 util/log/clog.go:998 [config] running on machine: longong-dsj-kv1
I160621 11:06:31.940101 util/log/clog.go:998 [config] binary: CockroachDB beta-20160616 (linux amd64, built 2016/06/16 16:51:45, go1.6.2)
I160621 11:06:31.940101 util/log/clog.go:998 [config] arguments: [./cockroach start --host=192.168.181.91 --insecure --join=192.168.181.92:26258]
Please describe the issue you observed:

What did you do?
e.g
root@192.168.181.91:26257> drop table test_serial;
DROP TABLE
root@192.168.181.91:26257> select count(*) from test_serial;
pq: table is being deleted
root@192.168.181.91:26257> show tables;
+-------------+
| Table |
+-------------+
| test_serial |
+-------------+
What did you expect to see?
create a new table with same name
What did you see instead?
i can not create a new table with same name
e.g
root@192.168.181.91:26257> create table test_serial(a serial primary key,b string(30),c bool);
pq: table "test_serial" already exists

andreimatei · 2016-06-21T04:13:02Z

Are you running the DROP statement in a transaction? If so, DDL statements only take effect when you commit the txn.

idsj · 2016-06-21T06:12:14Z

@andreimatei I'm not using the transaction actually, and after the statement, all the nodes got errors. I used to catch this situation when I drop a table with 1m+ records and I thought the reason lies in the table size, but now I meet the same error for table of only 10k+ size.

andreimatei · 2016-06-21T16:10:30Z

Hmmm this "shouldn't happen" without a txn. I'll try to reproduce. Or if
you have a simple repro that you can share, that'd be useful.

On Tue, Jun 21, 2016 at 2:12 AM, idsj notifications@github.com wrote:

@andreimatei https://github.com/andreimatei I'm not using the
transaction actually, and after the statement, all the nodes got errors. I
used to catch this situation when I drop a table with 1m+ records and I
thought the reason lies in the table size, but now I meet the same error
for table of only 10k+ size.

—
You are receiving this because you were mentioned.
Reply to this email directly, view it on GitHub
#7348 (comment),
or mute the thread
https://github.com/notifications/unsubscribe/AAXBcbtDQR0faui3C8EzvZQeJUI19o00ks5qN4DBgaJpZM4I6U3v
.

vivekmenezes · 2016-06-21T19:45:38Z

I believe this is the intended behavior right. The name is available for
reuse only after thr table has been fully deleted.

On Tue, Jun 21, 2016, 12:10 PM Andrei Matei notifications@github.com
wrote:

Hmmm this "shouldn't happen" without a txn. I'll try to reproduce. Or if
you have a simple repro that you can share, that'd be useful.

On Tue, Jun 21, 2016 at 2:12 AM, idsj notifications@github.com wrote:

@andreimatei https://github.com/andreimatei I'm not using the
transaction actually, and after the statement, all the nodes got errors.
I
used to catch this situation when I drop a table with 1m+ records and I
thought the reason lies in the table size, but now I meet the same error
for table of only 10k+ size.

—
You are receiving this because you were mentioned.
Reply to this email directly, view it on GitHub
<
#7348 (comment)
,
or mute the thread
<
https://github.com/notifications/unsubscribe/AAXBcbtDQR0faui3C8EzvZQeJUI19o00ks5qN4DBgaJpZM4I6U3v

.

—
You are receiving this because you are subscribed to this thread.
Reply to this email directly, view it on GitHub
#7348 (comment),
or mute the thread
https://github.com/notifications/unsubscribe/ALOpBNlHu8TG9aWkIdlpJpTGo47Jdq0iks5qOA0IgaJpZM4I6U3v
.

andreimatei · 2016-06-21T19:49:55Z

Right, but the table is truncated and "fully deleted" by the schema changer, which (when run through the "synchronous path") blocks the client executing the DROP.

idsj · 2016-06-22T07:23:50Z

@andreimatei
create a new cockroach cluster with three node(192.168.181.91/92/93);Then do follow:

root@192.168.181.91:26257> create database test;
CREATE DATABASE
root@192.168.181.91:26257> set database = test;
SET

/*
create a table named test_serial and insert 30000 records using the node(192.168.181.93);
*/

root@192.168.181.91:26257> show tables;
+-------------+
| Table |
+-------------+
| test_serial |
+-------------+
root@192.168.181.91:26257> select count() from test_serial;
+----------+
| count() |
+----------+
| 30000 |
+----------+
root@192.168.181.91:26257> drop table test_serial;
DROP TABLE
root@192.168.181.91:26257> show tables;
+-------------+
| Table |
+-------------+
| test_serial |
+-------------+
root@192.168.181.91:26257> select count(*) from test_serial;
pq: table is being deleted
root@192.168.181.91:26257> create table test_serial(a serial primary key,b string(30),c bool);
pq: table "test_serial" already exists
root@192.168.181.91:26257> drop table test_serial;
pq: table "test_serial" has been deleted
root@192.168.181.91:26257> show tables;
+-------------+
| Table |
+-------------+
| test_serial |
+-------------+
root@192.168.181.91:26257>

and the node(192.168.181.93) got error.

andreimatei · 2016-06-24T19:09:45Z

I couldn't manage to reproduce this. @idsj would you mind confirming you're running a recent build of cockroach? When you launch it with ./cockroach start, there should be a line in the log (or console, if you specify --logtostderr) like

build:     beta-20160609-328-g775699c @ 2016/06/24 18:59:25 (go1.6.2)

Also, can you confirm that the DROP statement takes a noticeable time to execute (a few secs)?

Also also would you mind trying to reproduce it using a single node cluster, just to see if that changes anything?

Thanks!

idsj · 2016-06-27T08:56:52Z

@andreimatei
i use the beta-20160616 version.The version message is as follow：
Build Tag: beta-20160616
Build Time: 2016/06/16 16:51:45
Platform: linux amd64
Go Version: go1.6.2
C Compiler: gcc 4.9.2

yes,it execute a few seconds.

Did you reproduce it using the cluster with VM? It did not happen in VM environment,i have done too. It happened in the physical machine environment.

andreimatei · 2016-06-27T15:27:57Z

OK, managed to repro using three nodes. Looking.

andreimatei · 2016-06-29T16:11:39Z

For an update here, a number of problems have been made apparent by this. There's an outright bug that would sometimes lead to this behavior that's being fixed in #7504.
More fundamentally, there's problems with humongous transactions, such as the one we use for a truncation. For example: #627

Ways to avoid a huge transaction for truncation are being discussed here: #7499

vivekmenezes · 2017-02-26T17:00:08Z

We should fix this by renaming the table before dropping it.

When dropping a table, the table name would be released only after the table was truncated. Table truncation can take a long time and it doesn't make for a good user experience when a user expects a name for a dropped table to be available almost immediately. fixes cockroachdb#7348

andreimatei self-assigned this Jun 22, 2016

petermattis changed the title ~~drop table and recreate fail~~ sql: drop table and recreate fail Jun 27, 2016

andreimatei mentioned this issue Jun 27, 2016

cannot delete table struct #7478

Closed

petermattis added the S-1-stability Severe stability issues that can be fixed by upgrading, but usually don’t resolve by restarting label Jul 11, 2016

petermattis added this to the Q3 milestone Jul 11, 2016

knz added C-bug Code not up to spec/doc, specs & docs deemed correct. Solution expected to change code/behavior. S-3-ux-surprise Issue leaves users wondering whether CRDB is behaving properly. Likely to hurt reputation/adoption. labels Oct 27, 2016

a-robinson mentioned this issue Dec 7, 2016

sql: Can't drop and replace a table within a transaction #12123

Closed

dianasaur323 added community-answered and removed community-answered labels Feb 2, 2017

rjnn mentioned this issue Feb 7, 2017

sql: all inspection queries hang during table dropping #13460

Closed

knz mentioned this issue Feb 25, 2017

sql: schema updates have problematic shortcomings #13804

Closed

22 tasks

vivekmenezes mentioned this issue Feb 26, 2017

sql: dropping a table prevents recreating a table with the same name #13700

Closed

vivekmenezes assigned vivekmenezes and unassigned andreimatei Mar 6, 2017

vivekmenezes mentioned this issue Mar 6, 2017

sql: release table name as soon as it is no longer referenced #13908

Merged

vivekmenezes closed this as completed in #13908 Mar 7, 2017

rjnn mentioned this issue Feb 28, 2018

sql: dropping large table blocks creation of new table with the same name #23223

Closed

jordanlewis added C-question A question rather than an issue. No code/spec/doc change needed. O-community Originated from the community and removed O-deprecated-community-questions labels Apr 24, 2018

Provide feedback

Saved searches

Use saved searches to filter your results more quickly

sql: drop table and recreate fail #7348

sql: drop table and recreate fail #7348

idsj commented Jun 21, 2016

andreimatei commented Jun 21, 2016

idsj commented Jun 21, 2016

andreimatei commented Jun 21, 2016

vivekmenezes commented Jun 21, 2016

andreimatei commented Jun 21, 2016

idsj commented Jun 22, 2016

andreimatei commented Jun 24, 2016

idsj commented Jun 27, 2016

andreimatei commented Jun 27, 2016

andreimatei commented Jun 29, 2016

vivekmenezes commented Feb 26, 2017

sql: drop table and recreate fail #7348

sql: drop table and recreate fail #7348

Comments

idsj commented Jun 21, 2016

andreimatei commented Jun 21, 2016

idsj commented Jun 21, 2016

andreimatei commented Jun 21, 2016

vivekmenezes commented Jun 21, 2016

andreimatei commented Jun 21, 2016

idsj commented Jun 22, 2016

andreimatei commented Jun 24, 2016

idsj commented Jun 27, 2016

andreimatei commented Jun 27, 2016

andreimatei commented Jun 29, 2016

vivekmenezes commented Feb 26, 2017