Opened 8 years ago

Closed 8 years ago

Last modified 5 years ago

#32686 closed defect (fixed)

Registry DB corrupted after selfupdate

Reported by: falkschramm@… Owned by: macports-tickets@…
Priority: Normal Milestone:
Component: base Version: 2.0.3
Keywords: lack-of-interest Cc: pixilla (Bradley Giesbrecht), neverpanic (Clemens Lang), evandrix (Lee Wei Yeong)
Port:

Description (last modified by ryandesign (Ryan Schmidt))

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

Change History (22)

comment:1 Changed 8 years ago by falkschramm@…

Cc: falkschramm@… added

Cc Me!

comment:2 Changed 8 years ago by ryandesign (Ryan Schmidt)

Cc: falkschramm@… removed
Component: portsbase
Description: modified (diff)

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.

comment:3 Changed 8 years ago by pixilla (Bradley Giesbrecht)

Cc: pixilla@… added

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

comment:4 Changed 8 years ago by pixilla (Bradley Giesbrecht)

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

comment:5 Changed 8 years ago by ecronin (Eric Cronin)

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

comment:6 in reply to:  5 Changed 8 years ago by 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.

comment:7 Changed 8 years ago by pixilla (Bradley Giesbrecht)

Has duplicate #32830

comment:8 Changed 8 years ago by pixilla (Bradley Giesbrecht)

Did r88411 not fix this issue?

What is your port version/revision?

comment:9 in reply to:  8 Changed 8 years ago by raimue (Rainer Müller)

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.

comment:10 Changed 8 years ago by neverpanic (Clemens Lang)

Cc: cal@… added

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

comment:11 Changed 8 years ago by yaseppochi (Stephen J. Turnbull)

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

comment:12 Changed 8 years ago by neverpanic (Clemens Lang)

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?

comment:13 Changed 8 years ago by yaseppochi (Stephen J. Turnbull)

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.

comment:14 Changed 8 years ago by neverpanic (Clemens Lang)

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.

comment:15 Changed 8 years ago by yaseppochi (Stephen J. Turnbull)

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?

comment:16 Changed 8 years ago by jmroot (Joshua Root)

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.

comment:17 in reply to:  16 ; Changed 8 years ago by neverpanic (Clemens Lang)

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?

comment:18 in reply to:  17 ; Changed 8 years ago by jmroot (Joshua Root)

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.

comment:19 in reply to:  18 Changed 8 years ago by neverpanic (Clemens Lang)

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.

comment:20 Changed 8 years ago by neverpanic (Clemens Lang)

Does this problem still persist?

comment:21 Changed 8 years ago by neverpanic (Clemens Lang)

Keywords: lack-of-interest added
Resolution: fixed
Status: newclosed

Assuming this is fixed until somebody can reproduce.

comment:22 Changed 5 years ago by evandrix (Lee Wei Yeong)

Cc: evandrix@… added

Cc Me!

Note: See TracTickets for help on using tickets.