Registry DB corrupted after selfupdate

[falkschramm@…]

Hi there,
After running "port selfupdate" today the registry DB seems to be corrupted. Before "port list outdated" was running fine. After the selfupdate it fails with the following error message:

Error: /opt/local/bin/port: port installed failed: sqlite error: SQL logic error or missing
database (1) while executing query: SELECT name FROM registry.ports WHERE id=572

Looking into the /opt/local/var/macports/registry/registry.db file with sqlite3 the file seems to be corrupted:

sqlite> SELECT id FROM ports;

Fails after ID 571 with "Error: database disk image is malformed".

So far it seems that the selfupdate corrupted the registry.db file.
Is there any way to rebuild/reconstruct the file?

Thanks in advance,
Falk

[falkschramm@…]

Cc Me!

[ryandesign@…]

I don't know why your registry corrupted, nor how to fix it. But I do want to point out that "port list" doesn't do what you think it does.

[pixilla@…]

Looks similar to a recent experience of mine.  macports-dev@lists: MP trunk sql.c COLLATE VERSION

[pixilla@…]

If you have a registry.db-journal file recovery should be automatic.

$ cd /opt/local/var/macports/registry
$ ls registry.db-journal
$ sudo sqlite3 registry.db
sqlite> .tables
sqlite> SELECT id FROM ports;
sqlite> .quit

[ecronin@…]

Automatic if the process has write access to registry.db, which it doesn't if you're doing 'port outdated' and not 'sudo port outdated'. The original description doesn't mention if any of the commands were tried as root, but that would explain why the journal didn't fix it automatically.

I thought a fix went into base right after the 2.0 release to use the extended sqlite error messages to catch this, but maybe it was only to print a better but still unhelpful error message. If "Error: /opt/local/bin/port: port installed failed: sqlite error: SQL logic error or missing database (1) while executing query: " is now the new "you need to open the database as root so it can be repaired from the journal" error we still need to do better at catching the sqlite library error and translating it to something the user has a chance at understanding

[falkschramm@…]

Commands that change or update installed ports are executed with sudo from my normal user account. Information only commands are issued without sudo from my normal user account.

Unfortunately, there is no journal file in this case. (I don't know why, I didn't disable it as far as I remember.)
"port outdated" fails with the same error message with and without sudoing it.

[pixilla@…]

Has duplicate #32830

[pixilla@…]

Did r88411 not fix this issue?

What is your port version/revision?

[raimue@…]

Replying to pixilla@…:

Did r88411 not fix this issue?

r88411 is not in a relased version of MacPorts. The issue only existed in trunk and was never released. Our trunk is not distributed via the selfupdate mechanism.

[cal@…]

#32830 is running trunk: MacPorts/base 23:31$ svn up.

[stephen@…]

ports tree (including base) at r88772 here, port last built 8 minutes ago, and

MacPorts 21:40$ sudo port version
Password:
sqlite error: SQL logic error or missing database (1) while executing query: COMMIT
    while executing
"registry::open $db_path"
    (procedure "mportinit" line 578)
    invoked from within
"mportinit ui_options global_options global_variations"
Error: /opt/local/bin/port: Failed to initialize MacPorts, sqlite error: SQL logic error or missing database (1) while executing query: COMMIT

So I don't think the fix in r88411 is related to this.

/opt/local/var/macports/registry/registry.db-journal exists and has permissions "-rw-r--r-- 1 root admin" with size 7176 and date Jan 11 23:32 JST (ie, about the same as the build time). /opt/local/var/macports/registry/registry.db exists and has permissions "-rw-r--r-- 1 root admin" with size 76739584 and the same time.

I also have a broken port, py25-wxpython (see ticket #32700), which is from about two weeks ago.

Why does port need to open the registry to give its version?

Following the thread described by pixilla, I ran the integrity check:

wideload:MacPorts/base 0:01$ sudo sqlite3
SQLite version 3.7.9 2011-11-01 00:52:41
sqlite> attach database "/opt/local/var/macports/registry/registry.db" as registry;
sqlite> .load /Users/steve/src/MacPorts/base/src/cregistry/macports.sqlext
sqlite> pragma integrity_check;
rowid 45024 missing from index file_path
rowid 138844 missing from index file_actual
sqlite> .quit

[cal@…]

Can you please attach the result of the query

SELECT * FROM metadata WHERE key='version';

I suspect the problem occurs when trying to upgrade the registry to the newer version. Do you mind mailing me your registry.db (use some compression, they are getting fairly large these days) so I can try to reproduce your issue?

[stephen@…]

Is this what you need?

MacPorts/base 0:04$ sudo sqlite3 /opt/local/var/macports/registry/registry.db
Password:
SQLite version 3.7.9 2011-11-01 00:52:41
Enter ".help" for instructions
Enter SQL statements terminated with a ";"
sqlite> SELECT * FROM metadata WHERE key='version';
version|1.0
sqlite> .quit

I'll send the xz file (about 6 MB) in a bit, but I've got to run to a meeting now.

[cal@…]

Can you please try again with the code in r88874? This should either give us a useful error message or work, if the problem was caused by sqlite3_step returning SQLITE_BUSY.

[stephen@…]

I have svn up'ed and I'm now at revision 88878.

Here's the tail of the result of make clean; ./configure; make && sudo make install in MacPorts/base:

===> making install in tests
make[1]: Nothing to be done for `install'.
[ ! -f /opt/local/etc/macports/mp_version ] || rm -vf /opt/local/etc/macports/mp_version
/usr/bin/install -c -o root -g admin -m 444 setupenv.bash  /opt/local/share/macports/
/usr/bin/install -c -o root -g admin -m 444 macports-pubkey.pem  /opt/local/share/macports/
/usr/bin/tclsh src/upgrade_sources_conf_default.tcl "/opt/local"
/usr/bin/tclsh src/images_to_archives.tcl "/opt/local/share/macports/Tcl"
sqlite error: cannot commit transaction - SQL statements in progress (1) while executing query: COMMIT
    while executing
"registry::open $db_path"
    (procedure "mportinit" line 581)
    invoked from within
"mportinit ui_options"
    (file "src/images_to_archives.tcl" line 18)
make: *** [install] Error 1

followed by a manual sudo port sync:

sqlite error: cannot commit transaction - SQL statements in progress (1) while executing query: COMMIT
    while executing
"registry::open $db_path"
    (procedure "mportinit" line 581)
    invoked from within
"mportinit ui_options global_options global_variations"
Error: /opt/local/bin/port: Failed to initialize MacPorts, sqlite error: cannot commit transaction - SQL statements in progress (1) while executing query: COMMIT

So, it didn't "work", but the error message changed, at least. :-/

What is the "manual upgrade" you mentioned in your email message?

[jmr@…]

That change will have made base unbuildable on Tiger IIRC. Looks like you could probably just #define sqlite3_prepare_v2 to sqlite3_prepare when SQLITE_VERSION_NUMBER is too old.

[cal@…]

Can you retry with r88888, please?

Replying to stephen@…:

What is the "manual upgrade" you mentioned in your email message?

You can run the queries needed to update your database manually. See base/src/cregistry/sql.c:update_db for the list of queries to run.

Replying to jmr@…:

That change will have made base unbuildable on Tiger IIRC. Looks like you could probably just #define sqlite3_prepare_v2 to sqlite3_prepare when SQLITE_VERSION_NUMBER is too old.

I couldn't find version information for the interfaces in the SQLite documentation. Do you happen to know when sqlite3_prepare_v2 was introduced? What version of SQLite shipped with Tiger?

[jmr@…]

Replying to cal@…:

I couldn't find version information for the interfaces in the SQLite documentation. Do you happen to know when sqlite3_prepare_v2 was introduced? What version of SQLite shipped with Tiger?

 http://www.sqlite.org/changes.html says 3.3.9 introduced the v2 API, but there were significant bugs until 3.3.11. So probably check if it's < 3003011.

[cal@…]

Replying to jmr@…:

 http://www.sqlite.org/changes.html says 3.3.9 introduced the v2 API, but there were significant bugs until 3.3.11. So probably check if it's < 3003011.

r88981.

[cal@…]

Does this problem still persist?

[cal@…]

Assuming this is fixed until somebody can reproduce.