Cockroach: sql: DROP DATABASE CASCADE fails due to ghost table descriptor
BUG REPORT
- Please describe the issue you observed:
- I'm not sure how I got into that, If I want to force drop database, it must work regardless.
P/s: I manually drop table by hand and then drop database, but one table (migrations) show some error, and then I can't drop database
kocoten1992
All 22 comments
@kocoten1992 Thanks for your report!
Could you please run the following two statements and paste the results:
> SELECT * FROM encoderv2.crdb_internal.tables;
> SELECT * FROM system.eventlog WHERE info LIKE '%encoderv2%';
thank you in advance!
knz
on 5 Jul 2018
cc @vivekmenezes
knz
on 5 Jul 2018
@knz here https://gist.github.com/kocoten1992/67d51ea5e504e4617f0cb678a80e65ef
but the second one a bit long it not my shell scroll only 500 lines, how does I output sql directly to a file, thank you :+1:
P/s: sorry, the event system only have my newest database encoderv22 (my shell won't scroll up further)
kocoten1992
on 5 Jul 2018
I just notice that, I haven't fully (some machine configured, some don't) configure systemd-timesync to time1.google.com yet at that time, hope that won't mess thing up
kocoten1992
on 5 Jul 2018
@kocoten1992 you can redirect the output by entering the following command in your shell:
$ cockroach sql -e "SELECT * FROM system.eventlog WHERE info LIKE '%encoderv2%'" >output.txt
yep, I'll update it now
kocoten1992
on 5 Jul 2018
@kocoten1992 does the database encoderv2 still exist? From the output it seems that it has been deleted successfully in the end. Is that possible?
For sure you can run the following statement too:
> select * from system.namespace;
[email protected]:26257/defaultdb> select * from system.namespace;
+----------+------------------+-----+
| parentID | name | id |
+----------+------------------+-----+
| 0 | defaultdb | 52 |
| 0 | encoderv2 | 56 |
| 0 | encoderv22 | 250 |
| 0 | system | 1 |
| 1 | descriptor | 3 |
| 1 | eventlog | 12 |
| 1 | jobs | 15 |
| 1 | lease | 11 |
| 1 | locations | 21 |
| 1 | namespace | 2 |
| 1 | rangelog | 13 |
| 1 | role_members | 23 |
| 1 | settings | 6 |
| 1 | table_statistics | 20 |
| 1 | ui | 14 |
| 1 | users | 4 |
| 1 | web_sessions | 19 |
| 1 | zones | 5 |
| 70 | migrations | 71 |
| 72 | migrations | 73 |
| 74 | migrations | 75 |
| 77 | migrations | 78 |
| 81 | migrations | 82 |
| 85 | migrations | 86 |
| 89 | migrations | 90 |
| 89 | users | 91 |
| 94 | migrations | 95 |
| 94 | password_resets | 97 |
| 94 | users | 96 |
| 100 | machines | 104 |
| 100 | migrations | 101 |
| 100 | password_resets | 103 |
| 100 | users | 102 |
| 107 | machines | 111 |
| 107 | migrations | 108 |
| 107 | password_resets | 110 |
| 107 | users | 109 |
| 114 | machines | 118 |
| 114 | migrations | 115 |
| 114 | password_resets | 117 |
| 114 | users | 116 |
| 121 | machines | 125 |
| 121 | migrations | 122 |
| 121 | password_resets | 124 |
| 121 | users | 123 |
| 128 | cpus | 133 |
| 128 | machines | 132 |
| 128 | migrations | 129 |
| 128 | password_resets | 131 |
| 128 | users | 130 |
| 135 | cpus | 140 |
| 135 | machines | 139 |
| 135 | migrations | 136 |
| 135 | password_resets | 138 |
| 135 | users | 137 |
| 151 | cpus | 156 |
| 151 | machines | 155 |
| 151 | materials | 157 |
| 151 | migrations | 152 |
| 151 | password_resets | 154 |
| 151 | profiles | 158 |
| 151 | users | 153 |
| 161 | cpus | 166 |
| 161 | machines | 165 |
| 161 | materials | 167 |
| 161 | migrations | 162 |
| 161 | password_resets | 164 |
| 161 | profiles | 168 |
| 161 | users | 163 |
| 171 | cpus | 176 |
| 171 | machines | 175 |
| 171 | materials | 177 |
| 171 | migrations | 172 |
| 171 | password_resets | 174 |
| 171 | profiles | 178 |
| 171 | users | 173 |
| 181 | cores | 195 |
| 205 | first_passes | 215 |
| 250 | audio_streams | 262 |
| 250 | backups | 265 |
| 250 | cores | 263 |
| 250 | cpus | 255 |
| 250 | depots | 266 |
| 250 | first_passes | 260 |
| 250 | machines | 254 |
| 250 | materials | 256 |
| 250 | migrations | 251 |
| 250 | mpds | 259 |
| 250 | password_resets | 253 |
| 250 | profiles | 257 |
| 250 | replicas | 267 |
| 250 | settings | 264 |
| 250 | tasks | 258 |
| 250 | users | 252 |
| 250 | video_streams | 261 |
+----------+------------------+-----+
(95 rows)
kocoten1992
on 5 Jul 2018
[email protected]:26257/defaultdb> show databases;
+------------+
| Database |
+------------+
| defaultdb |
| encoderv2 |
| encoderv22 |
| system |
+------------+
(4 rows)
Time: 11.699053ms
kocoten1992
on 5 Jul 2018
Ok I think I know what was happening: when you deleted the migrations table in encoderv2, the table was immediately marked as "dropped" but the database deletion had to wait for the table lease to expire.
This is unfortunate, but I think now the lease has expired (5-10 minutes later) you can delete the database successfully. What is the output of drop database encoderv2 now? (no cascade needed I think)
knz
on 5 Jul 2018
[email protected]:26257/defaultdb> drop database encoderv2
-> ;
DROP DATABASE
Time: 200.069411ms
What magic is this, wow thank you :smile: , thank you, we can close this now
kocoten1992
on 5 Jul 2018
No we should not close this -- I think drop database cascade should ignore the dropped table descriptors, even if there is a lease still active. But I am glad your issue is resolved.
knz
on 5 Jul 2018
Want to point that, it not 5-10 min later, much longer (maybe a day or two)
(I'm currently have 1, will update when I could delete it)
I couldn't delete yet - about 1 day already
deleted, take more than a day
kocoten1992
on 6 Jul 2018
@kocoten1992 I'm unable to reproduce this problem by creating a table, dropping the table and then dropping the database before the table data is cleared. I'd appreciate it if you can give me some instructions to better reproduce this problem.
vivekmenezes
on 10 Jul 2018
I'm been running a loop of create and drop tables, been 8 times already and unable to reproduce it (yayyyyyy!!!! :smile:), previously it show up quite often.
I'll let it run for several days and see what happen
kocoten1992
on 11 Jul 2018
while we do support creating and dropping a table, running it in a loop will eventually result the database running out of internal table IDs. We do not support this at the moment.
vivekmenezes
on 11 Jul 2018
@vivekmenezes I mis-read the question, it was dropping the database instead of tables,
and yes, the bug still exists
I'll drop an email to [email protected] the loop code and further detail
kocoten1992
on 11 Jul 2018
hi @vivekmenezes , have you able to reproduce the issue ?
kocoten1992
on 17 Jul 2018
@kocoten1992 I've not been able to reproduce this problem. Any help would be very much appreciated. Thanks!
vivekmenezes
on 17 Jul 2018
This issue occurred for me when attempting to drop a table that was cascade FK linked to another table. The tables are not present when viewing but CR still thinks they are there and any attempt to drop the database gives me the same error as above. I attempted this using Postico and after waiting a while I cut the connection.
poobah
on 27 Jul 2018
I'm having this same exactly issue.
I have a blog table:
+----------------------------------+-----------------+----------+-------------+--------------------------------------------------------------------------+-------------------------------------------------------+
| timestamp | eventType | targetID | reportingID | info | uniqueID |
+----------------------------------+-----------------+----------+-------------+--------------------------------------------------------------------------+-------------------------------------------------------+
| 2018-08-03 19:04:41.198182+00:00 | create_table | 76 | 1 | {"TableName":"xxx.public.blog_posts","Statement":"CREATE TABLE | \xf7\xca3\x8160O\u01ca\x9a\xc6\xe5\x98nSd |
| | | | | blog_posts (created_at TIMESTAMP WITH TIME ZONE, updated_at TIMESTAMP | |
| | | | | WITH TIME ZONE, deleted_at TIMESTAMP WITH TIME ZONE, id TEXT, | |
| | | | | status INTEGER, user_id TEXT, banner TEXT, title TEXT, description | |
| | | | | TEXT, body TEXT, post_at TIMESTAMP WITH TIME ZONE, PRIMARY KEY | |
| | | | | (id))","User":"styles"} | |
| 2018-08-03 19:37:57.900544+00:00 | alter_table | 76 | 4 | {"TableName":"xxx.public.blog_posts","Statement":"ALTER | \xc0UBE\x1b\x89CD\x9a\xc0\x18\u062d\xfc\x02\xee |
| | | | | TABLE blog_posts ADD slug | |
| | | | | TEXT","User":"styles","MutationID":1,"CascadeDroppedViews":null} | |
| 2018-08-11 03:59:02.889119+00:00 | drop_table | 77 | 2 | {"TableName":"xxx.public.blog_posts","Statement":"DROP TABLE | \xf7\xd3\x1c\u01f0\xe3@\xa7\x8b3\xf8q\xda\xcb\xcf\xd3 |
| | | | | blog_posts","User":"root","CascadeDroppedViews":null} | |
| 2018-08-11 03:59:34.454816+00:00 | create_table | 79 | 5 | {"TableName":"xxx.public.blog_posts","Statement":"CREATE TABLE | r\x96\xee\xd4A\rJ\xc1\xaec\xc0<X $& |
| | | | | blog_posts (created_at TIMESTAMP WITH TIME ZONE, updated_at TIMESTAMP | |
| | | | | WITH TIME ZONE, deleted_at TIMESTAMP WITH TIME ZONE, id TEXT, user_id | |
| | | | | TEXT, slug TEXT, status INTEGER, banner TEXT, title TEXT, description | |
| | | | | TEXT, body TEXT, post_at TIMESTAMP WITH TIME ZONE, PRIMARY KEY | |
| | | | | (id))","User":"styles"} | |
| 2018-08-26 01:55:05.61154+00:00 | create_table | 80 | 5 | {"TableName":"xxx.public.blog_categories","Statement":"CREATE TABLE | \xa7CU\xdc\xc1\xb0G\x97\x8a0\x91}\x11\xb0\xc2\xe7 |
| | | | | blog_categories (created_at TIMESTAMP WITH TIME ZONE, updated_at | |
| | | | | TIMESTAMP WITH TIME ZONE, deleted_at TIMESTAMP WITH TIME ZONE, id TEXT, | |
| | | | | slug TEXT, \"name\" TEXT, PRIMARY KEY (id))","User":"styles"} | |
| 2018-08-27 11:55:36.519782+00:00 | alter_table | 79 | 1 | {"TableName":"xxx.public.blog_posts","Statement":"ALTER | \x00\xfb\xe2\xe5X\xb5@\xe4\xa2\u055b\xaf6\x05@( |
| | | | | TABLE blog_posts ADD category_id | |
| | | | | TEXT","User":"styles","MutationID":1,"CascadeDroppedViews":null} | |
| 2018-08-27 12:25:19.192987+00:00 | create_database | 82 | 3 | {"DatabaseName":"blog","Statement":"CREATE DATABASE blog","User":"root"} | D,W\xf8c\xecDw\x91\x03E\xadj\xb2\x8bp |
| 2018-08-27 12:29:44.253889+00:00 | create_table | 83 | 1 | {"TableName":"blog.public.posts","Statement":"CREATE TABLE posts | %D)\xb3\xf9\x1a@\xed\xa4\xae\xf8\x0e\x83]~/ |
| | | | | (created_at TIMESTAMP WITH TIME ZONE, updated_at TIMESTAMP WITH TIME | |
| | | | | ZONE, deleted_at TIMESTAMP WITH TIME ZONE, id TEXT, user_id TEXT, | |
| | | | | category_id TEXT, slug TEXT, status INTEGER, banner TEXT, title TEXT, | |
| | | | | description TEXT, body TEXT, post_at TIMESTAMP WITH TIME ZONE, PRIMARY | |
| | | | | KEY (id))","User":"styles"} | |
| 2018-08-27 12:29:44.288968+00:00 | create_table | 84 | 1 | {"TableName":"blog.public.categories","Statement":"CREATE TABLE | \x05\xe6\xab\xd1hMG\xab\x8d:\u03c9\x14\xe3h\xd4 |
| | | | | categories (created_at TIMESTAMP WITH TIME ZONE, updated_at TIMESTAMP | |
| | | | | WITH TIME ZONE, deleted_at TIMESTAMP WITH TIME ZONE, id TEXT, slug TEXT, | |
| | | | | \"name\" TEXT, PRIMARY KEY (id))","User":"styles"} | |
| 2018-08-27 12:40:18.173354+00:00 | drop_table | 84 | 3 | {"TableName":"blog.public.categories","Statement":"DROP TABLE | \x84[\xb55\xfb4O\xa6\xbe\xe6!9\xf4\xbf\x00\x9e |
| | | | | categories","User":"root","CascadeDroppedViews":null} | |
| 2018-08-27 12:40:20.757592+00:00 | drop_table | 83 | 3 | {"TableName":"blog.public.posts","Statement":"DROP TABLE | \x86t_\xed\xb1\xeaIt\x96Z\xcc@3~1\xca |
| | | | | posts","User":"root","CascadeDroppedViews":null} | |
+----------------------------------+-----------------+----------+-------------+--------------------------------------------------------------------------+-------------------------------------------------------+
select * from system.namespace;
+----------+-------------------+----+
| parentID | name | id |
+----------+-------------------+----+
| 0 | blog | 82 |
| 82 | posts | 83 |
+----------+-------------------+----+
Show tables:
+-------+
| Table |
+-------+
+-------+
(0 rows)
clanstyles
on 27 Aug 2018
Related issues
nvanbenschoten
路
3Comments
intech
路
3Comments
couchand
路
3Comments
lwsanty
路
4Comments
melskyzy
路
3Comments
Most helpful comment
@kocoten1992 I'm unable to reproduce this problem by creating a table, dropping the table and then dropping the database before the table data is cleared. I'd appreciate it if you can give me some instructions to better reproduce this problem.