malformed registry

[RJVB (René Bertin)]

I have a registry malformation issue on pixilla's macports VM:

> sudo port -v uninstall qt5-creator-mac-devel
Warning: port definitions are more than two weeks old, consider updating them by running 'port selfupdate'.
--->  Deactivating qt5-creator-mac-devel @3.4.1_0
Error: Failed to deactivate qt5-creator-mac-devel: sqlite error: database disk image is malformed (11) while executing query: UPDATE registry.files INDEXED BY file_actual SET active=0 WHERE actual_path=? AND id=?
Error: See /opt/local/var/macports/logs/_opt_local_var_macports_registry_portfiles_qt5-creator-mac-devel-3.4.1_0_17a23cd57bf236c2cef7833fb52b5acf4d3d66ed590ad129d4c31a9c480be065-8026/qt5-creator-mac-devel/main.log for details.
Warning: Failed to execute portfile from registry for qt5-creator-mac-devel @3.4.1_0
--->  Deactivating qt5-creator-mac-devel @3.4.1_0
Error: Failed to uninstall qt5-creator-mac-devel: sqlite error: database disk image is malformed (11) while executing query: UPDATE registry.files INDEXED BY file_actual SET active=0 WHERE actual_path=? AND id=?
Error: See /opt/local/var/macports/logs/_opt_local_var_macports_registry_portfiles_qt5-creator-mac-devel-3.4.1_0_17a23cd57bf236c2cef7833fb52b5acf4d3d66ed590ad129d4c31a9c480be065-8026/qt5-creator-mac-devel/main.log for details.
Warning: Failed to execute portfile from registry for qt5-creator-mac-devel @3.4.1_0
--->  Deactivating qt5-creator-mac-devel @3.4.1_0
Error: Failed to deactivate qt5-creator-mac-devel: sqlite error: database disk image is malformed (11) while executing query: UPDATE registry.files INDEXED BY file_actual SET active=0 WHERE actual_path=? AND id=?
Error: See /opt/local/var/macports/logs/_opt_local_var_macports_registry_portfiles_qt5-creator-mac-devel-3.4.1_0_17a23cd57bf236c2cef7833fb52b5acf4d3d66ed590ad129d4c31a9c480be065-8026/qt5-creator-mac-devel/main.log for details.
Warning: Failed to execute portfile from registry for qt5-creator-mac-devel @3.4.1_0
--->  Deactivating qt5-creator-mac-devel @3.4.1_0
Error: port uninstall failed: sqlite error: database disk image is malformed (11) while executing query: UPDATE registry.files INDEXED BY file_actual SET active=0 WHERE actual_path=? AND id=?

Curiously, I just ran port selfupdate twice, the 2nd of which showed

> sudo port -v selfupdate
--->  Updating MacPorts base sources using rsync
receiving file list ... done

sent 36 bytes  received 69 bytes  70.00 bytes/sec
total size is 27013120  speedup is 257267.81
receiving file list ... done

sent 36 bytes  received 76 bytes  224.00 bytes/sec
total size is 512  speedup is 4.57
MacPorts base version 2.3.99 installed,
MacPorts base version 2.3.3 downloaded.
--->  Updating the ports tree
Synchronizing local ports tree from file:///opt/local/var/macports/sources/svn.macports.org/trunk/dports
Warning: port definitions are more than two weeks old, consider updating them by running 'port selfupdate'.
Creating port index in /opt/local/var/macports/sources/svn.macports.org/trunk/dports

Total number of ports parsed:   0 
Ports successfully parsed:      0 
Ports failed:                   0 
Up-to-date ports skipped:       23233

--->  MacPorts base is probably trunk or a release candidate

The ports tree has been updated. To upgrade your installed ports, you should run
  port upgrade outdated

I've had some weird issues the other day in which commands like port installed took really long to complete, so long that I killed several instances before deciding that I little choice but to wait and see if the command didn't complete in due time. The 1st such failed command was an uninstall, IIRC.

Is there any way to (auto)repair this error?

Would it be of any help if I attach the registry.db file?

[neverpanic (Clemens Lang)]

Any process with write access to the registry.db file should auto-correct this problem if it can be auto-corrected. You can also try running sudo sqlite3 $path_to_registry.db.

If that doesn't work, there's nothing we can do. As you noticed, interrupting installation/uninstallation is dangerous, especially in versions of MacPorts that don't have signal handling yet.

[RJVB (René Bertin)]

Replying to cal@…:

As you noticed, interrupting installation/uninstallation is dangerous, especially in versions of MacPorts that don't have signal handling yet.

If that's the case, there should be an additional safety: backup the registry.db file before starting the (un)installation process (and remove it after successful completion). Unless the backup file already exists, in which case an error should be raised or confirmation requested from the user.

[neverpanic (Clemens Lang)]

Replying to rjvbertin@…:

If that's the case, there should be an additional safety: backup the registry.db file before starting the (un)installation process (and remove it after successful completion).

I would expect such features to be the point of a database, and I would expect SQLite to provide this safety. Since it clearly doesn't (you didn't disable the journaling, right?), we should probably make a copy, yes. Patches are very welcome on this one. Note that we cannot copy the database reliably after we've opened the database, so the copy must be done before opening.

[RJVB (René Bertin)]

Replying to cal@…:

I would expect such features to be the point of a database,

Yes, though I've come to expect issues with sqlite. IIRC, the phpbb forum software is based on it too, and I've witnessed enough board crashes because of db corruption...

and I would expect SQLite to provide this safety. Since it clearly doesn't (you didn't disable the journaling, right?),

No, I wouldn't even know how. I also didn't knowingly remove the registry.db-journal file.

we should probably make a copy, yes. Patches are very welcome on this one. Note that we cannot copy the database reliably after we've opened the database, so the copy must be done before opening.

I'm guessing this should best be done in src/cregistry/registry.c, but should it be in reg_attach()/reg_detach() or rather in reg_open()/reg_close()? If I'm interpreting the function names correctly, the latter would provide the finer-grained safety (at the expense of adding a member to reg_registry). I'm not sure if there's a point to that though, nor how to handle cases like an existing backup file when a new backup is to be made. Should that be handled through reg_throw()?

[neverpanic (Clemens Lang)]

Replying to rjvbertin@…:

Yes, though I've come to expect issues with sqlite.

SQLite is actually a very-well tested piece of software.

IIRC, the phpbb forum software is based on it too, and I've witnessed enough board crashes because of db corruption...

Really? IIRC phpbb uses MySQL.

we should probably make a copy, yes. Patches are very welcome on this one. Note that we cannot copy the database reliably after we've opened the database, so the copy must be done before opening.

I'm guessing this should best be done in src/cregistry/registry.c, but should it be in reg_attach()/reg_detach() or rather in reg_open()/reg_close()? If I'm interpreting the function names correctly, the latter would provide the finer-grained safety (at the expense of adding a member to reg_registry). I'm not sure if there's a point to that though, nor how to handle cases like an existing backup file when a new backup is to be made. Should that be handled through reg_throw()?

I'd do it in reg_attach/reg_detach; reg_open/reg_close don't deal with the actual registry database file, but just setup the connection to SQLite and initialize a few things we need in SQLite. I don't think this backup mechanism should be a way to provide the feature set that should be provided by transactions, though. I'd rather have a couple of backup copies that a user can manually revert to if things stopped working correctly. Maybe use a timestamp in the target filename? Of course, we'd need some kind of cleanup, because copying on each open of the registry can generate a lot of data very quickly.

[RJVB (René Bertin)]

Replying to cal@…:

Replying to rjvbertin@…:

Yes, though I've come to expect issues with sqlite.

SQLite is actually a very-well tested piece of software.

I'm not claiming the contrary, but from what I understand it's not the best choice for all kinds of applications.

Really? IIRC phpbb uses MySQL.

As I said, I'm not sure. We're also talking about several years in the past (and a not-too-cutting-edge phpbb version at the time).

I'd do it in reg_attach/reg_detach; reg_open/reg_close don't deal with the actual registry database file, but just setup the connection to SQLite and initialize a few things we need in SQLite. I don't think this backup mechanism should be a way to provide the feature set that should be provided by transactions, though. I'd rather have a couple of backup copies that a user can manually revert to if things stopped working correctly. Maybe use a timestamp in the target filename? Of course, we'd need some kind of cleanup, because copying on each open of the registry can generate a lot of data very quickly.

No, AFAIC it's only to provide a safety against corruption due to unforeseen termination. What I think I'll do is create a registry.db.bak if the file doesn't already exist and remember if I did. Then, upon successful termination, in reg_detach, that file will be moved to registry.db.1.bak, after a backup rotation over a suitable number of files (5? 10?), but only if registry.db.bak was actually created this time around. If registry.db.bak exists in reg_attach, a warning will be printed (also in reg_detach) and it will be left alone.

It would be nice in reg_detach to do the rotation and cleanup regardless of whether registry.db.bak was created this time around, but is there a way of knowing whether a sufficient number of sufficiently different db operations were carried out to be sure that the current registry.db is indeed in good shape? If not, it's probably best to let the user run checks to verify this (possibly renaming registry.db.bak to something/someplace of his/er own choice).

We could consider compressing the .N.bak files.