5

I managed to screw up an index in my SpatiaLite database. The index was created using spatialite-gui. In the meantime, I deleted the original table and three out of four index files: idx_tablename_Geometry is still left while idx_tablename_Geometry_node, idx_tablename_Geometry_parent and idx_tablename_Geometry_rowid have been deleted too.

Whenever I start the GUI or refresh the list of tables, I'll get the error message: No such table main.idx_tablename_Geometry_node. Trying to drop the table idx_tablename_Geometry using either SQL or the GUI results in the same error message and nothing is deleted.

The database is still usable in my GIS, but it's annoying to have this error pop up every minute when working with spatialite-gui. Any ideas how to fix it?

7

Have you tried disabling the spatial index first?

SELECT DisableSpatialIndex('TableName', 'geom');

DROP TABLE idx_tablename_Geometry;

-- check to see if it is now gone
.tables

-- if it is gone, vacuum to clean up your db
VACUUM;
  • 2
    If there is some sort of corruption and you really can't drop the index table, you may want to create a new db and transfer the valid contents to it. – DavidF Aug 27 '10 at 13:53
  • While DisableSpatialIndex() returned 1, dropping the table still isn't possible. Guess I'll have to create a new db. – underdark Aug 27 '10 at 14:58
  • 1
    Not a direct solution, but I would highly recommend posting your issue to the Spatialite Users list. groups.google.com/group/spatialite-users Alessandro, the primary developer of Spatialite fixes bugs almost faster than you can post them... – DavidF Aug 27 '10 at 17:03
1

I had a similar problem: After deleting the spatial index on one of my tables, I received the error message whenever I refreshed the list of tables. I had a view that referenced the index, which turned out to be the problem. After deleting the view, the error messages stopped. So, my suggestion: make sure that no views rely on the index that you're trying to delete.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.