Shrinking an outsized KeePassX/KeePass database

There is an error in KeePassX (and there was a similar bug in KeePass, too) that can cause the database file to grow at a very rapid pace (I don't know what triggers this growth). To be precise, the database size doubles every time it is saved even if a very small change is made.

My database reached 256 MB. KeePass running on Windows XP could not open it (because of an Out of memory error) while KeePassX on Ubuntu could open it but it was taking a few minutes. But the main problem anyway was the fact that I couldn't add new entries or make any changes because the file would grow again.

I tried to shrink the file by saving it as a new database or exporting and then importing it, but it didn't work. I also tried to find a solution on the Internet, but I only found an information that there was such a bug and it was fixed in Windows version. So after a few days I downloaded the KeePass sources, spent half a day (it's been a long time since I last saw a C++ code :) first trying to make them compile (I didn't have a libboost_regex-vc80-mt-sgd-1_34_1.lib library file; I installed it with BoostPro Binary Installer for Visual C++ - take a look here and here) and then trying to find a place in the code where the individual entries get loaded. :)

Finally, it turned out that the problem was caused by an attachment which was taking almost all space in the database file (it had a few KB when I attached it some time ago).

So I placed a line responsible for loading the attachment inside a try... catch... block and added a message box.

try
{
pEntry->pszBinaryDesc = _UTF8ToString((UTF8_BYTE *)pData);
}
catch (...)
{
AfxMessageBox((CString)"There was a problem with reading pszBinaryDesc " 
+ (CString)"field of " + pEntry->pszTitle + (CString)" entry.\n"
+ (CString)"This data has been LOST. However, you can now save the "
+ (CString)"database as a new file and its size should be normal again.", 
MB_ICONEXCLAMATION);
}

Thanks to this if there is a broken (outsized) attachment in some entry, an exception is thrown (important: if you try to run KeePass on Linux using WINE, this exception will not be thrown) and KeePass skips loading it and informs the user that such a problem occurred. Be aware that I didn't find a way to restore a broken attachment so some data is lost, but the database can be opened and saved as a new database. The new database has a normal size.

If anyone has a similar problem, let me know - I can e-mail modified sources or a compiled EXE file.

UPDATE:

A tip from an anonymous reader:

I was able to get around the same issue and shrink my keepass database via "File" -> "Export To" -> "KeePass Database" in v1.14. Using "Save As" wouldn't do it, but exporting did the trick!

Invalid 'Remote table-valued function calls are not allowed' message error

Assuming that [11.99.120.12] is a linked server created on a SQL Server 2005 computer and pointing to a SQL Server 2000 computer, executing a simple query like this:

  SELECT *
  FROM [11.99.120.12].SALES_DB.dbo.ORDERS (nolock)

will fail with the following error message:

  Server: Msg 4122, Level 16, State 1, Line 1
  Remote table-valued function calls are not allowed.

This error message is not correct, since ORDERS is a table and not a function.

This query will run correctly only if the WITH keyword is used before the (nolock) hint:

  SELECT *
  FROM [11.99.120.12].SALES_DB.dbo.ORDERS WITH (nolock)

This problem is also discussed here.

What I recently learned about casting an empty string (T-SQL)

SELECT CAST('' AS money)
-- .0000

SELECT CAST('' AS decimal(19,4))
-- Server: Msg 8114, Level 16, State 5, Line 1
-- Error converting data type varchar to numeric.

Error when attempting to change a revision description (Subversion)

If you cannot modify a revision description in Subversion (SVN) because of the following error:

Repository has not been enabled to accept revision propchanges;
ask the administrator to create a pre-revprop-change hook

(or 'almost in Polish' ;)

Repozytorium nie ma włączone możliwości zmieniania atrybutów;
poproś administratora o utworzenie skryptu hook pre-revprop-change

it can be solved by creating an pre-revprop-change.bat file (in case you are using Windows) in the hooks directory of the repository. Of course the file does not have to be empty, it can do something like logging information about changes being made (because Subversion's revision properties are not versioned).

UPDATE:

On Windows 2003 BAT files cannot be empty - see here.

Shorewall does not start at boot time

I am neither shell scripting nor security guru, but I think that there is an error in the /etc/init.d/shorewall script that installs with the version 3.2.6-2 of Shorewall.

I installed Shorewall using Synaptic today, configured it and tested that I can start it manually. Then I restarted the computer and found out that there are no rules defined in any iptables chain (sudo iptables -L). I used the sudo invoke-rc.d shorewall start command to check what's happening during boot time and I saw the following error:

  Please read about Debian specific customization in
  /usr/share/doc/shorewall/README.Debian.gz.

After a few minutes of checking by trial and error I knew there was a problem with a piece of code right after the # check if shorewall is configured or not comment and after a few more minutes I modified it and the problem was solved. Below is the modified version - the script was looking for a wrong file and checking a non-existent variable.

# check if shorewall is configured or not
if [ -f "/etc/shorewall/shorewall.conf" ]
then
. /etc/shorewall/shorewall.conf
if [ "$STARTUP_ENABLED" != "Yes" ]
then
not_configured
fi
else
not_configured
fi

Listing users and all roles they're members of (SQL Server 2005)

I admit it's trivial but it took me awhile today to understand what data is shown in the sys.database_role_members catalog view, so I am posting this in case I forget.

SELECT
  DP1.name as ROLE_NAME
, DP2.name as [USER_NAME]
FROM sys.database_role_members DRM
INNER JOIN sys.database_principals DP1
  ON DRM.role_principal_id = DP1.principal_id
INNER JOIN sys.database_principals DP2
  ON DRM.member_principal_id = DP2.principal_id

Disabling or enabling multiple jobs with a single click

If you navigate to <SQL Server instance>\SQL Server Agent\Jobs in SQL Server Management Studio and then right-click on some job, you'll see a context menu with Enable and Disable commands.

However, when you select more than one job and then right-click on the selection, a different menu shows and, of course [sic], it does not contain these commands.



Luckily there is another way to do it - you can use Job Activity Monitor which uses the same context menu for a single job as well as multiple jobs.

SSIS imports NULLs instead of numeric values from an Excel file

Let's imagine that you have been assigned a task to import some kind of parts catalogue from an Excel file to SQL Server table. You decided to use Integration Services. One of the columns in the Excel file contains catalogue numbers. Most of them look like this XYZ123456 but some of them are plain numeric values.

This is what can happen:

Excel          SQL Server
CAT_NO         CAT_NO
---------      ---------
XYZ121156      XYZ121156
XYZ654321      XYZ654321
ABC120456  =>  ABC120456
CBA183456      CBA183456
ZYX123416      ZYX123416
123            NULL       (!)
ZXV654521      ZXV654521

This article from SQL Server 2005 Books Online explains this weird behaviour:

Missing values
The Excel driver reads a certain number of rows (by default, 8 rows) in the specified source to guess at the data type of each column. When a column appears to contain mixed data types, especially numeric data mixed with text data, the driver decides in favor of the majority data type, and returns null values for cells that contain data of the other type. (In a tie, the numeric type wins.) Most cell formatting options in the Excel worksheet do not seem to affect this data type determination. You can modify this behavior of the Excel driver by specifying Import Mode. To specify Import Mode, add IMEX=1 to the value of Extended Properties in the connection string of the Excel connection manager in the Properties window.

Preventing code from being generated in InitializeComponent() method for a property

When you place controls on a form, Visual Studio generates code in the InitializeComponent() method that sets values of their properties.

If you are developing a custom control and you don't want Visual Studio to generate such code for some of its properties, you should apply the DesignerSerializationVisiblityAttribute to such properties and place DesignerSerializationVisiblity.Hidden value on them.

[DesignerSerializationVisibility(DesignerSerializationVisibility.Hidden)]
public DateTime Value
{
    get { /* ... */ }
    set { /* ... */ }
}

More information about customizing the code generated by Visual Studio can be found here.

Changing file permissions (chmod) without SSH/telnet access

If you need to change file permissions (chmod) for more than just a few files and your web hosting provider gives you only FTP access to your account (neither SSH/telnet nor a good web hosting control panel system), you can use the chmod function available in PHP.

You can either write a PHP script that calls chmod for each file that needs its permissions to be changed or use this snippet to call chmod recursively and change permissions for an entire folder tree.