SQLite Forum

How to remove a column and guide from the docs
Login

How to remove a column and guide from the docs

(1.1) By mdv (melnikovdv) on 2020-08-17 11:02:48 edited from 1.0 [link] [source]

Hi. I'm trying to remove a column and add a different one instead.

I've found a guide in docs and follow point 5 Making Other Kinds Of Table Schema Changes.

I made a script that works when I execute it from CLI, but when I try to work with sqlite jdbc it fails with

A table in the database is locked (database table is locked)

on the "drop old table" line.

Could you explain why is it and how to get it work from my app's db migration flow?

(2) By Keith Medcalf (kmedcalf) on 2020-08-17 12:37:12 in reply to 1.1 [link] [source]

I made a script that works when I execute it from CLI, but when I try to work with sqlite jdbc it fails with

A table in the database is locked (database table is locked)

on the "drop old table" line.

Indicates that there is an statement outstanding (executing as in not reset/finalized) against the table you are trying to drop. You cannot drop a table while it is in use by an executing statement.

That is, if you do a:

select * from x

and have commenced executing that statement, then you must reset it before the statement

drop table x

can proceed. You are being prevented from dropping the floor out from underneath your own feet.

(3) By mdv (melnikovdv) on 2020-08-17 16:29:13 in reply to 2 [link] [source]

Thank you, sounds reasonable.

I tried different transactions during migration within one connection, but it didn't help. I bet it would work with connection recreation.

Are there any recommendations how to implement migrations? I suppose people want to remove columns from time to time.

(4) By Kees Nuyt (knu) on 2020-08-17 22:44:34 in reply to 3 [link] [source]

Are there any recommendations how to implement migrations? I suppose people want to remove columns from time to time.

I script all my conversions. Conversion scripts always look like:

.timeout 20000
BEGIN IMMEDIATE TRANSACTION;
-- not necessary, it is the default, 
-- but might not always be the default:
PRAGMA foreign_keys=off; 
-- conversion SQL here
-- always use explcit name lists, never SELECT *
COMMIT TRANSACTION;

and I run the script with sqlite3 -bail database.sqlite <conversionscript

For renaming/adding/removing colums, I usually create a new database with the new schema, then populate it with something like:

ATTACH '/path/to/olddb.sqlite' AS olddb;
INSERT INTO main.newtable (col1,col2,...) SELECT col1,col2,... FROM olddb.oldtable ;
-- etcetera.
DETACH olddb;

If triggers would cause unwanted updates, I DROP them first and recreate them after the conversion.

A common "problem" is ownership and permissions of the database file, its -joourrnal, -shm, and/or -wal file and the directory they are in.

If you run a script with sqlite3 shell, this usually runs as another user than e.g. a webserver.

-- 
Regards,
Kees Nuyt

(5) By mdv (melnikovdv) on 2020-08-18 06:40:22 in reply to 4 [link] [source]

Thanks.

(6) By mdv (melnikovdv) on 2020-08-18 06:42:56 in reply to 2 [link] [source]

I'm interested what is the difference between CLI migration script call and my app's migration call.

Why CLI works well, but my shows "A table in the database is locked (database table is locked)".

It seems I'm doing something a bit different or wrong.

(7) By Kees Nuyt (knu) on 2020-08-18 10:25:32 in reply to 6 [link] [source]

Find the other process that has your database open:

sudo lsof /path/to/your/database

(Unix, Linux)

-- 
Regards,
Kees Nuyt

(8) By Simon Slavin (slavin) on 2020-08-18 12:53:34 in reply to 6 [link] [source]

Your problem is not with the process which is removing the column. Your problem is that some other process is accessing the same database and hasn't finished whatever it's doing.

A common cause of this is reading a set of rows from the database using SELECT and not using the API call which tells SQLite that you're finished with that set of rows. The connection stays open even if SQLite has told you there are no more rows of data to return. Under normal circumstances the connection remains live until the application quits, and doesn't affect anything else. But in this specific case you're making a change which affects the way the database is organised, so SQLite is insisting that nothing else is accessing the database while that happens.

Try rebooting, then immediately making that change before anything else accesses the database. Does it work ?

And as was commented upthread your OS probably provides a way for you to see which processes are accessing a specific file. You can use that to figure out which process is not properly terminating its access when its finished with the data.

(9) By mdv (melnikovdv) on 2020-08-19 07:15:42 in reply to 8 [source]

No other processes are using the database. Tried lots of things, but nothing helps =(

May be something in my setup: WAL mode + SqlDelight library over JDBC driver, but these thing are quite simple and can't see a mistake in the code.

Seems I have to create a sample to share to get ideas outside.

Thank you all.

(10) By Keith Medcalf (kmedcalf) on 2020-08-19 15:30:37 in reply to 9 [link] [source]

You jdbc driver is probably not finalizing the interim statements.