<div dir="ltr">Thanks a lot for your help Jukka and Even. While I can drop the related triggers, I cannot drop the index table and its shadow tables - it always gives the database disk image malformed message. <br>ogr2ogr had worked - as you said - for the visible data. I guess the other data is lost for good...</div><br><div class="gmail_quote"><div dir="ltr" class="gmail_attr">On Mon, Feb 14, 2022 at 1:25 PM Even Rouault <<a href="mailto:even.rouault@spatialys.com">even.rouault@spatialys.com</a>> wrote:<br></div><blockquote class="gmail_quote" style="margin:0px 0px 0px 0.8ex;border-left:1px solid rgb(204,204,204);padding-left:1ex">
<div>
<p>I would just suggest using ogr2ogr to generate a new 'clean' GPKG
file from the corrupted one. This will not recover lost data, but
at least you'll get something sane. Well I should say
"structurally" sane. depending on how the corruption happened you
could get also get corrupted content in the data that tools can't
detect (there's no checksum mechanism at the row level in sqlite)<br>
</p>
<div>Le 14/02/2022 à 12:51, Rahkonen Jukka
(MML) a écrit :<br>
</div>
<blockquote type="cite">
<div>
<p class="MsoNormal"><span>Hi,<u></u><u></u></span></p>
<p class="MsoNormal"><span><u></u> <u></u></span></p>
<p class="MsoNormal"><span lang="EN-US">Maybe you can drop the rtree tables if you
first drop the rtree_xxx triggers that belong to the data
table. There are probably 6 of them. I don’t know if it
helps, though. rtreecheck()
<a href="https://sqlite.org/rtree.html" target="_blank">https://sqlite.org/rtree.html</a>
is probably useless because you know already that the index
is corrupted.
<u></u><u></u></span></p>
<p class="MsoNormal"><span lang="EN-US"><u></u> <u></u></span></p>
<p class="MsoNormal"><span lang="EN-US">-Jukka-<u></u><u></u></span></p>
<p class="MsoNormal"><span lang="EN-US"><u></u> <u></u></span></p>
<div style="border-right:none;border-bottom:none;border-left:none;border-top:1pt solid rgb(225,225,225);padding:3pt 0cm 0cm">
<p class="MsoNormal"><b>Lähettäjä:</b> Isabel Kiefer
<a href="mailto:isabel@opengis.ch" target="_blank"><isabel@opengis.ch></a> <br>
<b>Lähetetty:</b> maanantai 14. helmikuuta 2022 13.08<br>
<b>Vastaanottaja:</b> Rahkonen Jukka (MML)
<a href="mailto:jukka.rahkonen@maanmittauslaitos.fi" target="_blank"><jukka.rahkonen@maanmittauslaitos.fi></a>;
<a href="mailto:gdal-dev@lists.osgeo.org" target="_blank">gdal-dev@lists.osgeo.org</a><br>
<b>Aihe:</b> Re: [gdal-dev] Corrupted geopackage<u></u><u></u></p>
</div>
<p class="MsoNormal"><u></u> <u></u></p>
<div>
<div>
<p class="MsoNormal">Hi Jukka, <u></u><u></u></p>
</div>
<div>
<p class="MsoNormal"><u></u> <u></u></p>
</div>
<p class="MsoNormal">Yes, exactly, there are only 78 entries
in a dump to text file. <u></u><u></u></p>
<div>
<p class="MsoNormal"><u></u> <u></u></p>
<div>
<p class="MsoNormal">In the original .gpkg-file, the
rtree_XX_geom table is completely empty (no columns, no
entries) - this must be where the problem comes from..<u></u><u></u></p>
</div>
</div>
</div>
<p class="MsoNormal"><u></u> <u></u></p>
<div>
<div>
<p class="MsoNormal">On Mon, Feb 14, 2022 at 11:59 AM
Rahkonen Jukka (MML) <<a href="mailto:jukka.rahkonen@maanmittauslaitos.fi" target="_blank">jukka.rahkonen@maanmittauslaitos.fi</a>>
wrote:<u></u><u></u></p>
</div>
<blockquote style="border-top:none;border-right:none;border-bottom:none;border-left:1pt solid rgb(204,204,204);padding:0cm 0cm 0cm 6pt;margin-left:4.8pt;margin-right:0cm">
<div>
<div>
<p class="MsoNormal">Hi,<u></u><u></u></p>
<p class="MsoNormal"> <u></u><u></u></p>
<p class="MsoNormal"><span lang="EN-US">And when you .dump
<a href="https://www.sqlitetutorial.net/sqlite-dump/" target="_blank">https://www.sqlitetutorial.net/sqlite-dump/</a>
the damaged table into a text file, do you only get
78 insert entries into it?</span><u></u><u></u></p>
<p class="MsoNormal"><span lang="EN-US"> </span><u></u><u></u></p>
<p class="MsoNormal"><span lang="EN-US">-Jukka-</span><u></u><u></u></p>
<p class="MsoNormal"><span lang="EN-US"> </span><u></u><u></u></p>
<div style="border-right:none;border-bottom:none;border-left:none;border-top:1pt solid rgb(225,225,225);padding:3pt 0cm 0cm">
<p class="MsoNormal"><b>Lähettäjä:</b>
Isabel Kiefer <<a href="mailto:isabel@opengis.ch" target="_blank">isabel@opengis.ch</a>>
<br>
<b>Lähetetty:</b> maanantai 14. helmikuuta 2022
12.53<br>
<b>Vastaanottaja:</b> Rahkonen Jukka (MML) <<a href="mailto:jukka.rahkonen@maanmittauslaitos.fi" target="_blank">jukka.rahkonen@maanmittauslaitos.fi</a>><br>
<b>Kopio:</b> <a href="mailto:gdal-dev@lists.osgeo.org" target="_blank">gdal-dev@lists.osgeo.org</a><br>
<b>Aihe:</b> Re: [gdal-dev] Corrupted geopackage<u></u><u></u></p>
</div>
<p class="MsoNormal"> <u></u><u></u></p>
<div>
<p class="MsoNormal">Thanks
for your suggestion, Jukka! I tried all the advice
from stackoverflow. I can do ".recover", but get
only the features that have been visible all along. <u></u><u></u></p>
<div>
<p class="MsoNormal">In
the original .gpkg, I cannot DROP the affected
RTree-Index. It gives me the same disk malformed
error. But that would also have been my initial
idea... <u></u><u></u></p>
</div>
</div>
<p class="MsoNormal"> <u></u><u></u></p>
<div>
<div>
<p class="MsoNormal">On
Mon, Feb 14, 2022 at 9:26 AM Rahkonen Jukka (MML)
<<a href="mailto:jukka.rahkonen@maanmittauslaitos.fi" target="_blank">jukka.rahkonen@maanmittauslaitos.fi</a>>
wrote:<u></u><u></u></p>
</div>
<blockquote style="border-top:none;border-right:none;border-bottom:none;border-left:1pt solid rgb(204,204,204);padding:0cm 0cm 0cm 6pt;margin:5pt 0cm 5pt 4.8pt">
<div>
<div>
<p class="MsoNormal">Hi,<u></u><u></u></p>
<p class="MsoNormal"> <u></u><u></u></p>
<p class="MsoNormal"><span lang="EN-US">Without being able to see your
database it is hard to give more advice that
<a href="https://stackoverflow.com/questions/18259692/how-to-recover-a-corrupt-sqlite3-database/18260642" target="_blank">
https://stackoverflow.com/questions/18259692/how-to-recover-a-corrupt-sqlite3-database/18260642</a>
gives. Did you try all of them? Because the
problem has something to do with the spatial
index I would have a try be dropping it and
repeating the suggested tricks.</span><u></u><u></u></p>
<p class="MsoNormal"><span lang="EN-US"> </span><u></u><u></u></p>
<p class="MsoNormal"><span lang="EN-US">-Jukka Rahkonen-</span><u></u><u></u></p>
<p class="MsoNormal"><span lang="EN-US"> </span><u></u><u></u></p>
<div style="border-right:none;border-bottom:none;border-left:none;border-top:1pt solid rgb(225,225,225);padding:3pt 0cm 0cm">
<p class="MsoNormal"><b>Lähettäjä:</b>
gdal-dev <<a href="mailto:gdal-dev-bounces@lists.osgeo.org" target="_blank">gdal-dev-bounces@lists.osgeo.org</a>>
<b>Puolesta </b>Isabel Kiefer<br>
<b>Lähetetty:</b> maanantai 14. helmikuuta
2022 10.17<br>
<b>Vastaanottaja:</b> <a href="mailto:gdal-dev@lists.osgeo.org" target="_blank">gdal-dev@lists.osgeo.org</a><br>
<b>Aihe:</b> [gdal-dev] Corrupted geopackage<u></u><u></u></p>
</div>
<p class="MsoNormal"> <u></u><u></u></p>
<div>
<p class="MsoNormal">Hi
everyone,<u></u><u></u></p>
<div>
<p class="MsoNormal"> <u></u><u></u></p>
</div>
<div>
<p class="MsoNormal">I've
a problem with a corrupted geopackage. <u></u><u></u></p>
</div>
<div>
<p class="MsoNormal">It
should contain a table with 694 entries,
but only 78 are visible when opening the
file with DB Browser for SQLite or QGIS.
The table gpkg_ogr_contents says 694
though. When opening the .gpkg with a
Notepad or similar, I can see that there
are more than 78 entries.<u></u><u></u></p>
</div>
<div>
<p class="MsoNormal"> <u></u><u></u></p>
</div>
<div>
<p class="MsoNormal">The
error message in DB Browser is "database
disk image is malformed in "PRAGMA
"main".TABLE_INFO("rtree_XXX");" So it
seems that there is a problem with the
index of the concerned table.<u></u><u></u></p>
</div>
<div>
<p class="MsoNormal"> <u></u><u></u></p>
</div>
<div>
<p class="MsoNormal">Does anyone
know how to fix this? I tried
<a href="https://stackoverflow.com/questions/18259692/how-to-recover-a-corrupt-sqlite3-database/18260642" target="_blank">
<span style="font-size:10.5pt;font-family:Roboto">https://stackoverflow.com/questions/18259692/how-to-recover-a-corrupt-sqlite3-database/18260642</span></a> but
without success.<br clear="all">
<u></u><u></u></p>
<div>
<p class="MsoNormal"> <u></u><u></u></p>
</div>
<div>
<p class="MsoNormal">Thanks
in advance for your help!<u></u><u></u></p>
</div>
<div>
<p class="MsoNormal">Isabel<u></u><u></u></p>
</div>
<div>
<p class="MsoNormal"> <u></u><u></u></p>
</div>
<p class="MsoNormal">--
<u></u><u></u></p>
<div>
<div>
<p class="MsoNormal"><span style="font-family:Arial,sans-serif">Isabel Kiefer</span><u></u><u></u></p>
<div>
<p class="MsoNormal"><span style="font-family:Arial,sans-serif">OPENGIS.ch</span><u></u><u></u></p>
</div>
<div>
<p class="MsoNormal"> <u></u><u></u></p>
</div>
<div>
<p class="MsoNormal"><span style="font-size:7.5pt;font-family:Arial,sans-serif"><a href="mailto:isabel@opengis.ch" target="_blank">isabel@opengis.ch</a></span><u></u><u></u></p>
</div>
<div>
<p class="MsoNormal"> <u></u><u></u></p>
</div>
</div>
</div>
</div>
</div>
</div>
</div>
</blockquote>
</div>
</div>
</div>
</blockquote>
</div>
</div>
<br>
<fieldset></fieldset>
<pre>_______________________________________________
gdal-dev mailing list
<a href="mailto:gdal-dev@lists.osgeo.org" target="_blank">gdal-dev@lists.osgeo.org</a>
<a href="https://lists.osgeo.org/mailman/listinfo/gdal-dev" target="_blank">https://lists.osgeo.org/mailman/listinfo/gdal-dev</a>
</pre>
</blockquote>
<pre cols="72">--
<a href="http://www.spatialys.com" target="_blank">http://www.spatialys.com</a>
My software is free, but my time generally not.</pre>
</div>
_______________________________________________<br>
gdal-dev mailing list<br>
<a href="mailto:gdal-dev@lists.osgeo.org" target="_blank">gdal-dev@lists.osgeo.org</a><br>
<a href="https://lists.osgeo.org/mailman/listinfo/gdal-dev" rel="noreferrer" target="_blank">https://lists.osgeo.org/mailman/listinfo/gdal-dev</a><br>
</blockquote></div>