Solved Questions on sqlite3 update of database

C

Crotalus

I have a record or 2 in the database that will not allow me to update or install due to a
Code: 
database disk image is malformed
Since this appears to be a conventional style database would the following work as a fix as opposed to doing a complete install from scratch of the whole system. I know next to nothing about sqlite3.

Delete the offending record in the database.
MySQL will delete a record with this DELETE FROM table_name [WHERE Clause]. The where would be the table and specific record by key and value. Sqlite has a similar command.
Code: 
DELETE
FROM 
   table
WHERE search_condition
ORDER BY criteria
LIMIT row_count OFFSET offset;

I believe that the database is /var/db/local.sqlite
I am able add/update/delete other ports/packages without any problems using pkg install/delete [I]name[/I] or portmaster [I]name[/I]
If one would know the table name and the key and format for the record would it be possible to delete the bad entry and do an install of the port/package? I have not found where anybody has attempted this. I have tried to find out what the database looks like from a copy, but the commands I try using are incorrect. A little or more learning curve for my fried brain trying to add kde after a version update.
 
Crotalus said:
I have a record or 2 in the database that will not allow me to update or install due to a
Code: 
database disk image is malformed
Since this appears to be a conventional style database would the following work as a fix as opposed to doing a complete install from scratch of the whole system. I know next to nothing about sqlite3.

Delete the offending record in the database.
MySQL will delete a record with this DELETE FROM table_name [WHERE Clause]. The where would be the table and specific record by key and value. Sqlite has a similar command.
Code: 
DELETE
FROM
   table
WHERE search_condition
ORDER BY criteria
LIMIT row_count OFFSET offset;

I believe that the database is /var/db/local.sqlite
I am able add/update/delete other ports/packages without any problems using pkg install/delete [I]name[/I] or portmaster [I]name[/I]
If one would know the table name and the key and format for the record would it be possible to delete the bad entry and do an install of the port/package? I have not found where anybody has attempted this. I have tried to find out what the database looks like from a copy, but the commands I try using are incorrect. A little or more learning curve for my fried brain trying to add kde after a version update.
Click to expand...

These are the tables in the data base. I have no idea what is what or how these tables are related and which would be the top dog.
Code: 
root@Prometheus:/var/db/pkg # sqlite3
SQLite version 3.24.0 2018-06-04 19:24:41
Enter ".help" for usage hints.
Connected to a transient in-memory database.
Use ".open FILENAME" to reopen on a persistent database.
sqlite> .open saved.sqlite
sqlite> .tables
annotation           pkg_annotation       pkg_script         
categories           pkg_categories       pkg_shlibs         
config_files         pkg_conflicts        pkg_shlibs_provided
deps                 pkg_directories      pkg_shlibs_required
directories          pkg_groups           pkg_users         
files                pkg_licenses         provides           
groups               pkg_lock             requires           
licenses             pkg_lock_pid         script             
mtree                pkg_option           scripts           
option               pkg_option_default   shlibs             
option_desc          pkg_option_desc      users             
options              pkg_provides       
packages             pkg_requires       
sqlite>

Does anybody out there in the universe know what the tables are?
 
If you can open the database, why do you think it is damaged? There are multiple (local and one per repo) sqlite databases in /var/db/pkg/ directory. Have you checked them all?
 
Shkhln,

When trying to install kde after having problems I deleted the old version and started fresh. I had even upgraded from VERSION 10.4 to VERSION 11.1. When it reached farstream it died with the following error;
Code: 
root@Prometheus:/ # pkg install farstream
Updating FreeBSD repository catalogue...
FreeBSD repository is up to date.
All repositories are up to date.
Checking integrity... done (0 conflicting)
The following 1 package(s) will be affected (of 0 checked):

New packages to be INSTALLED:
        farstream: 0.2.7

Number of packages to be installed: 1

The process will require 2 MiB more space.

Proceed with this action? [y/N]: y
[1/1] Installing farstream-0.2.7...
pkg: sqlite error while executing iterator in file pkgdb_iterator.c:1035: database disk image is malformed
pkg: sqlite error while executing INSERT OR REPLACE INTO files (path, sha256, package_id) VALUES (?1, ?2, ?3) in file pkgdb.c:1791: database disk image is malformed
root@Prometheus:/ #
There are several references in the forums that this is a damaged data base and the only fix is to completely install the operating system from scratch and rebuild the box. That is a last resort. If the data base is damaged it should be possible to fix it without a complete install of the system. Is it damaged? I don't know. I have found that farstream is not in the tables that I have checked so far. As below;
Code: 
sqlite> select name from packages where name like "%zip%";
libzip
quazip-qt4
quazip-qt5
zip
zziplib
sqlite>

sqlite> select name from packages where name like "%farstream%";
sqlite>
I don't know what the problem is, but an update on farstream dies and kdewon't install. I am open to any suggestions. I am also having the same problem with nvidia-driver-340.
 
In a default FreeBSD setup there is a backup script dumping the local database into /var/backups. It's a nightly cron job, so ymmw. If you don't have any backups then try making an sql dump from your corrupted database and import the data into a new database, there is not much else to try.
 
I restored the backup and got the same result. I should have mentioned it. I have a script written that saves 3 days of backups, all are bad. I don't know when it went bad.

The real question is it possible to fix the data base?
 
I did a dump and restored the database into a new location without any errors or problems. The size difference between the old and the new database was significant. I tried to run with the restored version and almost every thing was jacked up. Next step load most current version of FreeBSD over the bad version and start new. I am now in the process of a new install of 11.2. Thanks for the help.
 
You must log in or register to reply here.
Back
Top