ERROR: SQLite database is malformed – SOLVED

Every once in a while if SQLiteyou’re making heavy use of a SQLite database, as with any other DBMS, it will become corrupted. Usually this is due to bugs within the DBMS itself and sometimes hardware failure.

I had a SQLite database go corrupt when an application crashed. Normally I would have a backup to go to but in this case I didn’t because it was a new application that I was in the process of setting up.

Attempting to list tables in the database using sqlite3 resulted in the following message:

Error: near line 1: database disk image is malformed

In SQLite, the consistency checker is pragma integrity_check

This pragma does an integrity check of the entire database. The integrity_check pragma looks for out-of-order records, missing pages, malformed records, missing index entries, and UNIQUE and NOT NULL constraint errors. If the integrity_check pragma finds problems, strings are returned (as multiple rows with a single column per row) which describe the problems. Pragma integrity_check will return at most N errors before the analysis quits, with N defaulting to 100. If pragma integrity_check finds no errors, a single row with the value ‘ok’ is returned.

PRAGMA integrity_check does not find FOREIGN KEY errors. Use the PRAGMA foreign_key_check command for to find errors in FOREIGN KEY constraints.

sqlite> pragma integrity_check;
Error: database disk image is malformed

Yeah, it’s effectively a boolean response. OK if all is good else “database disk image is malformed”. Not very helpful IMO.

So what to do? Obviously, reverting to an earlier backup is preferred but if you remember, I didn’t have one and I didn’t want to redo all that work.

We need to export the schema and data and create a new database, import the schema and data. Simple:

sqlite> .mode insert
sqlite> .output mydb_export.sql
sqlite> .dump
sqlite> .exit

The .output sets the name of the file that the output of following commands to mydb_export.sql.
The .dump will export the database in SQL format.

Now, move the mydb.db file to mydb.db.original.

mv mydb.db mydb.db.original

We are almost done. Let’s create the database and load the exported schema & data:

sqlite3 mydb.db < mydb_export.sql

We have one last step. If the database had indexes, and all databases should have indexes, you will want to update the statistics for the indexes:

sqlite> analyze;
sqlite> .exit
Share Button

Getting Flickr::API to work on Ubuntu Linux SOLVED

In order to use the Flickr::API example by Gabor Szabo, I needed to create a LWP object specifying the path to the system certificates when creating the Flickr::API object.

Works:

my $flickr_api = Flickr::API->new( {
    key       => $key,
    # secret  => $secret,
    unicode   => 1,
    lwpobj    => LWP::UserAgent->new(
        ssl_opts => { 
            SSL_ca_path => '/etc/ssl/certs',
        },
    ),
}); 

Doesn’t work Perl(reports a 500 error with “Can’t connect to api.flickr.com:443 (certificate verify failed)” ) :

my $flickr_api = Flickr::API->new( {
    key       => $key,
    # secret  => $secret,
    unicode   => 1,
    ssl_opts  => { 
        SSL_ca_path       => '/etc/ssl/certs',
    },
});

You could use Mozilla::CA as brian d foy suggests but the bundled file is from 2014. Personally, I’ll use the certificate files from the OpenSSL project. 🙂

Share Button

Perl: How to check that the Perl Modules are installed at runtime

Often when you write a Perlprogram in Perl, you know which system(s) you’re going to be using the program on and you can ensure the required Perl modules are installed. Other times, you may not. Unless you want to be called simply because a required Perl module wasn’t installed on a system, you probably should check and provide a meaningful error message.

In this example, we’ll use the core module Module::Load in order to load a module. We also declare the %MISSING_MODULE hash:

use Module::Load;

our %MISSING_MODULE;

We populate the %noncore_modules with non-core modules we want to use. If the value for the module is an array, we will import those specific variables/subroutines:

BEGIN {
    # We're going to use:
    #   Pod::Usage with no special imports
    #   My::Secret::Module importing *ONLY* potato and carrot subroutines
    my %noncore_modules = (
        'Pod::Usage'         => 'default',
        'My::Secret::Module' => [ qw(
            potato
            carrot
            ) ],
    );

    foreach my $tmp_module (keys %noncore_modules ) { 
        if (eval { load $tmp_module ; 1; } ) { 
            if ( ref( $noncore_modules{$tmp_module} ) eq 'ARRAY' ) { 
                # Only specified subroutines/variables specified in
                #  %noncore_modules will be imported
                $tmp_module->import( @{ $noncore_modules{$tmp_module} } );
            } else {
                # anything in the @EXPORT of the module will be imported
                #  @EXPORT_OK will not be imported

                $tmp_module->import();
            }   
        } else {
            $MISSING_MODULE{$tmp_module} = 1;
        }   
    }
}

Now that we’ve loaded Perl modules that we could, let’s report on any that failed to load:

if (%MISSING_MODULE) {                                            
    $RC = 1;

    foreach my $perl_module (keys %MISSING_MODULE) {
        printf STDERR "Missing Perl module %s. Please have unix team install the module and verify permissions!\n", $perl_module;
        print STDERR "ABORTING\n";
    }

    exit $RC;
}
Share Button

Wanted: SAP manuals in ePub format

Every time SAP comes out with a new set ofSybase SAP pdf manuals, the meta data has to be corrected. Often the stored titles, description, etc are wildly wrong. Very sloppy and unprofessional for a mega corp the size of SAP.

The ePub book format has been out for many years and has many features that make it tablet, phone, PC, whatever friendly. Reading a SAP manual at night? No problem, change the font color to white on black so you don’t wake your spouse. The font is too small? No problem, choose a larger or different font. You can’t do any of that with a PDF. Try reading the ASE Admin guides on a 4″ iPhone. I dare you. You might as well pour salt in your eye sockets.

Share Button

SAP IQ: dbisql is unable to load the SybaseIQ SQLAnywhere plugins. SOLVED!

I recently patched an SAP IQ server to 16.0 SP8 PL30 and ran into an interesting error message when trying to start dbisql:

$ dbisql
Interactive SQL could not load the "SQLAnywhere" plug-in.
Its "ngdbc.jar" file has moved or has been deleted. You will not be able to connect to the databases handled by that plug-in.
Interactive SQL could not load the "SybaseIQ" plug-in.
Its "ngdbc.jar" file has moved or has been deleted. You will not be able to connect to the databases handled by that plug-in.
Interactive SQL could not load the "HANA" plug-in.
Its "ngdbc.jar" file has moved or has been deleted. You will not be able to connect to the databases handled by that plug-in.
Interactive SQL could not load the "GenericODBC" plug-in.
Its "ngdbc.jar" file has moved or has been deleted. You will not be able to connect to the databases handled by that plug-in.
Interactive SQL cannot start because it is not installed correctly. No database plug-ins has been registered.
To fix this problem, you should reinstall the program.

If you scan your IQ directory, you will notice there isn’t a “ngdbc.jar” file. You take a look at another IQ box that is working and it doesn’t have the ngdbc.jar file either. The error message is incorrect. The message should report that it isn’t able to access the saip16.jar (or saip11.jar if you’re on IQ 15.x) and/or the jodbc4.jar file in the $SYBASE/IQ-(IQ RELEASE)/java directory (e.g. $SYBASE/IQ-16_0/java).
Verify that the two files exist and the permissions are correct:

316 -rwxr-xr-x.  1 sybase sybase  320071 Mar 20 14:51 jodbc4.jar
112 -rwxr-xr-x.  1 sybase sybase  112325 Mar 20 14:56 saip16.jar

If everything looks okay and it still gives the error, you will need to re-register the plugins. The first thing is to move the dbisql ‘registry’:

$ mv $SYBASE/IQ-(IQ RELEASE)/bin64/dbisql_64.rep $SYBASE/IQ-(IQ RELEASE)/bin64/dbisql_64.rep.old

Next, re-register (for IQ 16):

$ cd $SYBASE/IQ-(IQ RELEASE)/java
$ dbisql -Xregister sa16 SybaseIQ com.sybase.saisqlplugin.IQISQLPlugin "$(pwd)/saip16.jar:$(pwd)/jodbc4.jar"

for IQ 15:

$ cd $SYBASE/IQ-(IQ RELEASE)/java
$ dbisql -Xregister sa11 SybaseIQ com.sybase.saisqlplugin.IQISQLPlugin "$(pwd)/saip11.jar:$(pwd)/jodbc4.jar"

Newer IQ 15 patches use SQL Anywhere 12, so if you have saip12.jar instead of saip11.jar in your java dir, use that:

$ cd $SYBASE/IQ-(IQ RELEASE)/java
$ dbisql -Xregister sa12 SybaseIQ com.sybase.saisqlplugin.IQISQLPlugin "$(pwd)/saip12.jar:$(pwd)/jodbc4.jar"

dbisql should now work 🙂

The dbisql_64.rep is simply a glorified ini file that contains the following which should be transferrable but I’ve found that dbisql is very finicky with regards to this file.

[SybaseIQ]
classLoaderName=sa16
mainclass=com.sybase.saisqlplugin.IQISQLPlugin
classpath=/opt/client/SAP-IQ/IQ-16_0/java/saip16.jar:/opt/client/SAP-IQ/IQ-16_0/java/jodbc4.jar

You may need to update the permissions if other users rely on this particular dbisql installation.

Share Button

HOWTO: Enable 3D Acceleration in VMWare Player/Workstation in Ubuntu Linux 15.04 with Intel Graphics SOLVED

When youVMware start a virtual machine using VMWare Workstation or Player and receive a warning saying 3D Acceleration is not available, you can easily enable it. All you need to do is add a single line to the .vmx file (e.g. “Fedora Linux.vmx”):

mks.gl.allowBlacklistedDrivers = "TRUE"
Share Button