Corruption of database when renaming a table

classic Classic list List threaded Threaded
5 messages Options
Reply | Threaded
Open this post in threaded view
|

Corruption of database when renaming a table

tjerzyko
I'm having corruption problem with a certain database file. You can download it here:
https://drive.google.com/file/d/1RCPoPgoLdc2VgF2uX2zPFrkheFi9z3b_/view?usp=sharing
It was created with SQLite 3.8.7 or older version (I cannot say for sure now)
I open it with sqlite3.exe and run the following script:

begin;
alter table fs_params rename to fs_params_20291_27910;
CREATE TABLE FS_PARAMS(FS_NO INTEGER,FS_OWNER_ID TEXT,FS_OWNER_NAME TEXT);
drop table 'fs_params_20291_27910';
alter table file_locks rename to file_locks_27446_24559;
CREATE TABLE FILE_LOCKS(FILE_ID INTEGER, PLAYER_ID TEXT UNIQUE, USER_NAME TEXT, FOREIGN KEY(FILE_ID) REFERENCES FILE(FILE_ID));
drop table 'file_locks_27446_24559';
CREATE INDEX L_FILE_ID ON FILE_LOCKS(FILE_ID);
alter table cam_in_file rename to cam_in_file_22705_10035;
end;

It throws the following error:
Error: near line 9: error in trigger T_FILE_BDELETE: no such table: main.file_locks_27446_24559

Probably my script is incorrect, but another thing worries me more. I open the database again and:

e:\recorder\DB\LOOP-003>sqlite3 loop-003.dat
SQLite version 3.27.2 2019-02-25 16:06:06
Enter ".help" for usage hints.
sqlite> .tables
AUDIO_IN_FILE          FS_ATTRIBUTES          REC_RECORDING_MODE
AUDIO_IN_FRAGMENT      FS_PARAMS              REC_WORKING_MODE
CAM_IN_FILE            INTEGRITY_TEST         STORAGE_BLOCK
CAM_IN_FRAGMENT        LOOP_FRAGMENT          T_SCHEDULE
FILE                   REC_LATEST_CONNECTION
FILE_LOCKS             REC_LIST_TIMESTAMP
sqlite> select * from CAM_IN_FILE;
Error: no such table: CAM_IN_FILE
sqlite>

The table exists, but not quite. The database seems to be corrupted.

When I tried the same procedure on database created with newer SQLite version, there were no such problems.

Tomasz Jerzykowski


_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Corruption of database when renaming a table

R Smith-2
For a start, do not use single quotes for table names. In SQL, DB object
identifiers can either be unquoted or contained in double-quotes. SQLite
specifically even allows backticks or square brackets for compatibility,
but nobody likes single quotes.

I.e. change this:

drop table 'fs_params_20291_27910';

To this:

drop table "fs_params_20291_27910";


Another thing SQL cares about is the case you use in names, but again SQLite allows referring to a table without needing to match the schema case. (Just putting this out there to satisfy my internal pedantry).

Lastly, you probably have a trigger on one of these tables you intend to rename, but the trigger has internally code referring to another table (or some such) causing the error. Could you please send the full schema of your DB?

using the sqlite3 CLI will output it on issuing: .fullschema

Or post a DB on a download site somewhere if it isn't sensitive.


Thanks,
Ryan

On 2019/03/15 3:29 PM, tjerzyko wrote:

> I'm having corruption problem with a certain database file. You can download it here:
> https://drive.google.com/file/d/1RCPoPgoLdc2VgF2uX2zPFrkheFi9z3b_/view?usp=sharing
> It was created with SQLite 3.8.7 or older version (I cannot say for sure now)
> I open it with sqlite3.exe and run the following script:
>
> begin;
> alter table fs_params rename to fs_params_20291_27910;
> CREATE TABLE FS_PARAMS(FS_NO INTEGER,FS_OWNER_ID TEXT,FS_OWNER_NAME TEXT);
> drop table 'fs_params_20291_27910';
> alter table file_locks rename to file_locks_27446_24559;
> CREATE TABLE FILE_LOCKS(FILE_ID INTEGER, PLAYER_ID TEXT UNIQUE, USER_NAME TEXT, FOREIGN KEY(FILE_ID) REFERENCES FILE(FILE_ID));
> drop table 'file_locks_27446_24559';
> CREATE INDEX L_FILE_ID ON FILE_LOCKS(FILE_ID);
> alter table cam_in_file rename to cam_in_file_22705_10035;
> end;
>
> It throws the following error:
> Error: near line 9: error in trigger T_FILE_BDELETE: no such table: main.file_locks_27446_24559
>
> Probably my script is incorrect, but another thing worries me more. I open the database again and:
>
> e:\recorder\DB\LOOP-003>sqlite3 loop-003.dat
> SQLite version 3.27.2 2019-02-25 16:06:06
> Enter ".help" for usage hints.
> sqlite> .tables
> AUDIO_IN_FILE          FS_ATTRIBUTES          REC_RECORDING_MODE
> AUDIO_IN_FRAGMENT      FS_PARAMS              REC_WORKING_MODE
> CAM_IN_FILE            INTEGRITY_TEST         STORAGE_BLOCK
> CAM_IN_FRAGMENT        LOOP_FRAGMENT          T_SCHEDULE
> FILE                   REC_LATEST_CONNECTION
> FILE_LOCKS             REC_LIST_TIMESTAMP
> sqlite> select * from CAM_IN_FILE;
> Error: no such table: CAM_IN_FILE
> sqlite>
>
> The table exists, but not quite. The database seems to be corrupted.
>
> When I tried the same procedure on database created with newer SQLite version, there were no such problems.
>
> Tomasz Jerzykowski
>
>
> _______________________________________________
> sqlite-users mailing list
> [hidden email]
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Corruption of database when renaming a table

tjerzyko
Link to my database in in the original post.

https://drive.google.com/file/d/1RCPoPgoLdc2VgF2uX2zPFrkheFi9z3b_/view?usp=sharing

Tomasz Jerzykowski

W dniu 2019-03-15 14:46:19 użytkownik R Smith <[hidden email]> napisał:

> For a start, do not use single quotes for table names. In SQL, DB object
> identifiers can either be unquoted or contained in double-quotes. SQLite
> specifically even allows backticks or square brackets for compatibility,
> but nobody likes single quotes.
>
> I.e. change this:
>
> drop table 'fs_params_20291_27910';
>
> To this:
>
> drop table "fs_params_20291_27910";
>
>
> Another thing SQL cares about is the case you use in names, but again SQLite allows referring to a table without needing to match the schema case. (Just putting this out there to satisfy my internal pedantry).
>
> Lastly, you probably have a trigger on one of these tables you intend to rename, but the trigger has internally code referring to another table (or some such) causing the error. Could you please send the full schema of your DB?
>
> using the sqlite3 CLI will output it on issuing: .fullschema
>
> Or post a DB on a download site somewhere if it isn't sensitive.
>
>
> Thanks,
> Ryan
>
> On 2019/03/15 3:29 PM, tjerzyko wrote:
> > I'm having corruption problem with a certain database file. You can download it here:
> > https://drive.google.com/file/d/1RCPoPgoLdc2VgF2uX2zPFrkheFi9z3b_/view?usp=sharing
> > It was created with SQLite 3.8.7 or older version (I cannot say for sure now)
> > I open it with sqlite3.exe and run the following script:
> >
> > begin;
> > alter table fs_params rename to fs_params_20291_27910;
> > CREATE TABLE FS_PARAMS(FS_NO INTEGER,FS_OWNER_ID TEXT,FS_OWNER_NAME TEXT);
> > drop table 'fs_params_20291_27910';
> > alter table file_locks rename to file_locks_27446_24559;
> > CREATE TABLE FILE_LOCKS(FILE_ID INTEGER, PLAYER_ID TEXT UNIQUE, USER_NAME TEXT, FOREIGN KEY(FILE_ID) REFERENCES FILE(FILE_ID));
> > drop table 'file_locks_27446_24559';
> > CREATE INDEX L_FILE_ID ON FILE_LOCKS(FILE_ID);
> > alter table cam_in_file rename to cam_in_file_22705_10035;
> > end;
> >
> > It throws the following error:
> > Error: near line 9: error in trigger T_FILE_BDELETE: no such table: main.file_locks_27446_24559
> >
> > Probably my script is incorrect, but another thing worries me more. I open the database again and:
> >
> > e:\recorder\DB\LOOP-003>sqlite3 loop-003.dat
> > SQLite version 3.27.2 2019-02-25 16:06:06
> > Enter ".help" for usage hints.
> > sqlite> .tables
> > AUDIO_IN_FILE          FS_ATTRIBUTES          REC_RECORDING_MODE
> > AUDIO_IN_FRAGMENT      FS_PARAMS              REC_WORKING_MODE
> > CAM_IN_FILE            INTEGRITY_TEST         STORAGE_BLOCK
> > CAM_IN_FRAGMENT        LOOP_FRAGMENT          T_SCHEDULE
> > FILE                   REC_LATEST_CONNECTION
> > FILE_LOCKS             REC_LIST_TIMESTAMP
> > sqlite> select * from CAM_IN_FILE;
> > Error: no such table: CAM_IN_FILE
> > sqlite>
> >
> > The table exists, but not quite. The database seems to be corrupted.
> >
> > When I tried the same procedure on database created with newer SQLite version, there were no such problems.
> >
> > Tomasz Jerzykowski
> >
> >
> > _______________________________________________
> > sqlite-users mailing list
> > [hidden email]
> > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
> _______________________________________________
> sqlite-users mailing list
> [hidden email]
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>



_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Corruption of database when renaming a table

Scott Perry
In reply to this post by tjerzyko
The behaviour of ALTER TABLE changed in 3.25.0 and breaks compatibility with many existing applications. Your statements (corrected for use of single quotes, as Ryan mentioned) work for me when `PRAGMA legacy_alter_table=1;`.

More info on SQLite's website: https://www.sqlite.org/lang_altertable.html

Scott

> On Mar 15, 2019, at 06:29, tjerzyko <[hidden email]> wrote:
>
> I'm having corruption problem with a certain database file. You can download it here:
> https://drive.google.com/file/d/1RCPoPgoLdc2VgF2uX2zPFrkheFi9z3b_/view?usp=sharing
> It was created with SQLite 3.8.7 or older version (I cannot say for sure now)
> I open it with sqlite3.exe and run the following script:
>
> begin;
> alter table fs_params rename to fs_params_20291_27910;
> CREATE TABLE FS_PARAMS(FS_NO INTEGER,FS_OWNER_ID TEXT,FS_OWNER_NAME TEXT);
> drop table 'fs_params_20291_27910';
> alter table file_locks rename to file_locks_27446_24559;
> CREATE TABLE FILE_LOCKS(FILE_ID INTEGER, PLAYER_ID TEXT UNIQUE, USER_NAME TEXT, FOREIGN KEY(FILE_ID) REFERENCES FILE(FILE_ID));
> drop table 'file_locks_27446_24559';
> CREATE INDEX L_FILE_ID ON FILE_LOCKS(FILE_ID);
> alter table cam_in_file rename to cam_in_file_22705_10035;
> end;
>
> It throws the following error:
> Error: near line 9: error in trigger T_FILE_BDELETE: no such table: main.file_locks_27446_24559
>
> Probably my script is incorrect, but another thing worries me more. I open the database again and:
>
> e:\recorder\DB\LOOP-003>sqlite3 loop-003.dat
> SQLite version 3.27.2 2019-02-25 16:06:06
> Enter ".help" for usage hints.
> sqlite> .tables
> AUDIO_IN_FILE          FS_ATTRIBUTES          REC_RECORDING_MODE
> AUDIO_IN_FRAGMENT      FS_PARAMS              REC_WORKING_MODE
> CAM_IN_FILE            INTEGRITY_TEST         STORAGE_BLOCK
> CAM_IN_FRAGMENT        LOOP_FRAGMENT          T_SCHEDULE
> FILE                   REC_LATEST_CONNECTION
> FILE_LOCKS             REC_LIST_TIMESTAMP
> sqlite> select * from CAM_IN_FILE;
> Error: no such table: CAM_IN_FILE
> sqlite>
>
> The table exists, but not quite. The database seems to be corrupted.
>
> When I tried the same procedure on database created with newer SQLite version, there were no such problems.
>
> Tomasz Jerzykowski
>
>
> _______________________________________________
> sqlite-users mailing list
> [hidden email]
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Corruption of database when renaming a table

Richard Hipp-3
On 3/15/19, Scott Perry <[hidden email]> wrote:
> The behaviour of ALTER TABLE changed in 3.25.0 and breaks compatibility with
> many existing applications. Your statements (corrected for use of single
> quotes, as Ryan mentioned) work for me when `PRAGMA legacy_alter_table=1;`.

All true.  But even so, SQLite shouldn't leave the schema in a corrupt
state - it should roll the schema back to something usable.  I'm still
investigating, but this looks like a real bug.

I did observe that the bug only occurs if the sequence of ALTER TABLE
statements that Tomasz provides occurs inside a transaction.  If you
do them all separately, no schema corruption occurs.

Looking at the database after the error, I see that the SQLITE_MASTER
table has an entry with a NAME of 'CAM_IN_FILE' and  a TYPE of 'table'
but with SQL of: 'CREATE TABLE "cam_in_file_22705_10035"(...)'.  In
other words, the sqlite_master.name does not agree with the name in
the CREATE TABLE statement in sqlite_master.sql.
--
D. Richard Hipp
[hidden email]
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users