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

HOWTO: List users that perform os authentication to log into the Oracle database instance (external users)

Oracle will Oraclerequire you to configure the Oracle RDBMS to use operating system authentication but if you inherit an Oracle instance, you will want to disable os authentication when possible. When the OS_AUTHENT_PREFIX is set, any os user that is created with “IDENTIFIED EXTERNALLY” will have the prefix. For example, in the below example, the value is “ops$”. The os user johnnybgood will have an internal Oracle user id of “ops$johnnybgood”. It’s a handy way to quickly identify such users but it isn’t full proof.

show parameter OS_AUTHENT_PREFIX
lang-NAME                            TYPE        VALUE
------------------------------------ ----------- ------------------------------
os_authent_prefix                    string      ops$

To determine exactly which users are set up for os authentication run the following query:

set pagesize 5000
set linesize 999
set trimspool on

select gn.GLOBAL_NAME as "Instance", username, authentication_type
from dba_users du, global_name gn
where authentication_type = 'EXTERNAL'

Now, there are valid reasons to use os authentication but I would push LDAP long before os authentication.

From the Oracle Security Admin Guide:

Advantages of External Authentication

Following are the advantages of external authentication:

More choices of authentication mechanism are available, such as smart cards, fingerprints, Kerberos, or the operating system.

Many network authentication services, such as Kerberos support single sign-on, enabling users to have fewer passwords to remember.

If you are already using some external mechanism for authentication, such as one of those listed earlier, then there may be less administrative overhead to use that mechanism with the database as well.

Share Button

FW: Hardening the Oracle 11g Database – Initial Steps (Kevin Sheehan)

Kevin Sheehan wrote how to harden the Oracle 11g RDBMS. I highly recommend it:

TheOracle following is a basic set of hardening guidelines for an Oracle 11g database along with some scripts you may find useful. This list is by no means complete. It does not cover file permissions, authentication controls and user profiles, encryption, grants or auditing but it is a good place to start.

Or perhaps takes Slavic’s advice from his comment on this post and start with Oracle’s Checklist for Database Security. It gives a broader, though perhaps less detailed view, and covers some of the topics I have left out (for now). Also check out Slavik’s Musings on Database Security Blog.

Read more on Kevin’s blog.

Share Button

Connect to Microsoft SQL Server (including Azure instances) from Linux (x86/x86-64) using ODBC and JDBC

Connecting to Microsoft SQL Server from Linux can be done through two different methods: ODBC and JDBC. Unfortunately, FreeTDS doesn’t connect to the newest versions of SQL Server unless you want to enable legacy connections.

The Microsoft JDBC Driver 4.0 for SQL Server, a Type 4 JDBC driver provides database connectivity through the standard JDBC application program interfaces (APIs) available in Java Platform, Enterprise Edition 5 and 6.

The Microsoft ODBC Driver (Linux) For SQL Server provides native connectivity from Windows to Microsoft SQL Server and Windows Azure SQL Database on Linux.

Share Button

FW: William Blunn’s DBIX::MULTIROW – UPDATING MULTIPLE DATABASE ROWS QUICKLY AND EASILY

William Blunn wrote a good article on DBIx::MultiRow.

A requirement arises in many systems to update multiple SQL database rows. For small numbers of rows requiring updates, it can be adequate to use an UPDATE statement for each row that requires an update. But if there are a large number of rows that require an update, then the overhead of issuing large numbers of UPDATE statements can result in the operation as a whole taking a long time to complete.

Go to William Blunn’s blog to read the blog post 🙂

He will be adding the Perl module to CPAN soon.

Share Button

Sybase ASE: A simple way to let a user backup a database using a stored procedure

I wrote this simple method to backup a database using a stored procedure. This allows for a user to restore a database with no knowledge of the dump/load commands. Related to Sybase ASE: A simple way to let a user restore a database using a stored procedure

use sybsystemprocs
go

if exists (select 1 from sybsystemprocs..sysobjects where name = "sp_dump_userdb")
	drop procedure sp_dump_userdb
go

CREATE PROCEDURE dbo.sp_dump_userdb
@dbName varchar(100) IN
AS 
BEGIN
	if (@dbName in ('master', 'tempdb', 'tempdb2', 'tempdb3', 'sybsystemprocs', 'sybsystemdb'))
	begin
		print "sp_load_userdb only works with user databases."
	end 
	else
	if exists (select 1 from master..sysdatabases where name = @dbName)
	begin
		DECLARE @DAYofMonth smallint
		DECLARE @dbNamePath varchar(255)
		DECLARE @out varchar(255)

		select @DAYofMonth = datepart(day, getdate())
		select @dbNamePath = "/backups/user_backups/" + @dbName + "_" + convert(varchar(10), @DAYofMonth) + ".dmp"
		select @out = "Backing up database '" + @dbName + "' to " + @dbNamePath
		print @out

		dump database @dbName to @dbNamePath with init,compression=3

		/*  Remove old dump files (older than 2 weeks) */
		exec xp_cmdshell "find /backups/user_backups -mtime +14 -exec rm {} \;"

		print "Backup complete!"
	end 
	else
	begin
		select "'" + @dbName + "' is an unknown database.  Please verify name."
	end 
END
go
exec sp_dump_userdb jf_test
Backing up database 'jf_test' to /some_dir/jf_test_5.dmp
Backup Server session id is: 95. Use this value when executing the 'sp_volchanged' system stored procedure after fulfilling any volume change request from the Backup Server.
Backup Server: 4.41.1.1: Creating new disk file /some_dir/jf_test_5.dmp.
(1 row affected)
Backup Server: 6.28.1.1: Dumpfile name 'jf_test131560B97B' section number 1 mounted on disk file '/some_dir/jf_test_5.dmp'
Backup Server: 4.188.1.1: Database jf_test: 600 kilobytes (1%) DUMPED.
Backup Server: 4.188.1.1: Database jf_test: 1134 kilobytes (1%) DUMPED.
Backup Server: 4.188.1.1: Database jf_test: 18242 kilobytes (5%) DUMPED.
Backup Server: 4.188.1.1: Database jf_test: 31492 kilobytes (11%) DUMPED.
Backup Server: 4.188.1.1: Database jf_test: 38072 kilobytes (19%) DUMPED.
Backup Server: 4.188.1.1: Database jf_test: 38416 kilobytes (36%) DUMPED.
Backup Server: 4.188.1.1: Database jf_test: 38930 kilobytes (61%) DUMPED.
Backup Server: 4.188.1.1: Database jf_test: 45854 kilobytes (75%) DUMPED.
Backup Server: 4.188.1.1: Database jf_test: 46204 kilobytes (92%) DUMPED.
Backup Server: 3.43.1.1: Dump phase number 1 completed.
Backup Server: 3.43.1.1: Dump phase number 2 completed.
Backup Server: 3.43.1.1: Dump phase number 3 completed.
Backup Server: 4.188.1.1: Database jf_test: 46390 kilobytes (100%) DUMPED.
Backup Server: 3.42.1.1: DUMP is complete (database jf_test).
 xp_cmdshell                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                              
 --------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

(0 rows affected)
Backup complete!
(return status = 0)
Share Button

Sybase ASE: A simple way to let users restore a database using a stored procedure

I wrote this simple method to restore a database using a stored procedure. This allows for a user to restore a database with no knowledge of the dump/load commands. Related: Sybase ASE: A simple way to let users restore a database using a stored procedure

use sybsystemprocs
go

if exists (select 1 from sybsystemprocs..sysobjects where name = "sp_load_userdb")
	drop procedure sp_load_userdb
go

CREATE PROCEDURE dbo.sp_load_userdb
@dbName varchar(100) = NULL, 
@DAYofMonth smallint = NULL
AS 
BEGIN
	if (@dbName is NULL or not exists select 1 from (master..sysdatabases where name = @dbName))
	begin
		print "Please specify a database and day of month to restore from."
		print "sp_load_userdb d1folio1, 15"
	end
	else
	if (@dbName in ('master', 'tempdb', 'tempdb2', 'tempdb3', 'sybsystemprocs', 'sybsystemdb'))
	begin
		print "sp_load_userdb only works with user databases."
	end 
	else
	if (@DAYofMonth is NULL or @DAYofMonth < 1 or @DAYofMonth > 31)
	begin
		DECLARE @cmd varchar(255)

		print "Dump file not found!"
		select @cmd = "find /some_dir/user_backups -name " + @dbName + "_*" + " -type f -exec basename {} \;"
		print @cmd
		exec xp_cmdshell @cmd
	end
	else
	begin
		if (db_name() = "master")
		begin
			DECLARE @dbNamePath varchar(255)

			exec kill_user_connections @dbName
			select @dbNamePath = "/some_dir/user_backups/" + @dbName + "_" + convert(varchar(10), @DAYofMonth) + ".dmp"
			load database @dbName from @dbNamePath
			online database @dbName
		end 
		else
			print "Please run sp_load_userdb from the master database."
	end 
END
go
exec sp_load_userdb jf_test, 5
-----------------------------------------------------------------------
There is no user connections in database jf_test

------------------------------------------------------------------------------------
Please log out if you currently connected to database jf_test
Backup Server session id is: 68. Use this value when executing the 'sp_volchanged' system stored procedure after fulfilling any volume change request from the Backup Server.
Backup Server: 6.28.1.1: Dumpfile name 'jf_test131560B97B' section number 1 mounted on disk file '/somedir/jf_test_5.dmp'

(1 row affected)
Backup Server: 4.188.1.1: Database jf_test: 25480 kilobytes (4%) LOADED.
Backup Server: 4.188.1.1: Database jf_test: 53770 kilobytes (10%) LOADED.
Backup Server: 4.188.1.1: Database jf_test: 98316 kilobytes (18%) LOADED.
Backup Server: 4.188.1.1: Database jf_test: 141328 kilobytes (27%) LOADED.
Backup Server: 4.188.1.1: Database jf_test: 184848 kilobytes (35%) LOADED.
Backup Server: 4.188.1.1: Database jf_test: 227860 kilobytes (43%) LOADED.
Backup Server: 4.188.1.1: Database jf_test: 271380 kilobytes (52%) LOADED.
Backup Server: 4.188.1.1: Database jf_test: 314392 kilobytes (60%) LOADED.
Backup Server: 4.188.1.1: Database jf_test: 357912 kilobytes (69%) LOADED.
Backup Server: 4.188.1.1: Database jf_test: 387100 kilobytes (74%) LOADED.
Backup Server: 4.188.1.1: Database jf_test: 430620 kilobytes (83%) LOADED.
Backup Server: 4.188.1.1: Database jf_test: 473120 kilobytes (91%) LOADED.
Backup Server: 4.188.1.1: Database jf_test: 516640 kilobytes (99%) LOADED.
Backup Server: 4.188.1.1: Database jf_test: 518178 kilobytes (100%) LOADED.
Backup Server: 4.188.1.1: Database jf_test: 518186 kilobytes (100%) LOADED.
Backup Server: 3.42.1.1: LOAD is complete (database jf_test).
Caution:  You have set up this database to include space on disk 36 for both data and the transaction log.  This can make recovery impossible if that disk fails.
Started estimating recovery log boundaries for database 'jf_test'.
Database 'jf_test', checkpoint=(224579, 10), first=(224579, 10), last=(224579, 11).
Completed estimating recovery log boundaries for database 'jf_test'.
Started ANALYSIS pass for database 'jf_test'.
Completed ANALYSIS pass for database 'jf_test'.
Started REDO pass for database 'jf_test'. The total number of log records to process is 2.
Completed REDO pass for database 'jf_test'.
Use the ONLINE DATABASE command to bring this database online; ASE will not bring it online automatically.
Started estimating recovery log boundaries for database 'jf_test'.
Database 'jf_test', checkpoint=(224579, 10), first=(224579, 10), last=(224579, 11).
Completed estimating recovery log boundaries for database 'jf_test'.
Started ANALYSIS pass for database 'jf_test'.
Completed ANALYSIS pass for database 'jf_test'.
Recovery of database 'jf_test' will undo incomplete nested top actions.
Database 'jf_test' is now online.
(return status = 0)
exec sp_load_userdb
Please specify a database and day of month to restore from.
sp_load_userdb d1folio1, 15
(return status = 0)
Share Button

Optimizing the Firefox (SQLite databases)

Most people that want to speed up Firefox know about the various settings in about:config but many forget that Firefox uses SQLite databases that can become cluttered.

In your home directory ($HOME on *nix/Mac and %APPDIR% on Windows), run sqlite3 with vacuum, reindex and analyze on eche of the *.sqlite databases. Don’t have sqlite3? No problem, get it from the SQLite Download page or your distribution’s package manager (e.g. apt-get install sqlite3)

#!/bin/bash

cd ${HOME}/.mozilla/firefox/

for profile in *.default; do
cd ${profile}

for db in *.sqlite; do 
echo Processing $db
sqlite3 "${db}" < 

That wasn’t hard was it? 🙂

Share Button

HOWTO: Install SAP Sybase ASE 15.x and Linux Containers (LXC) – Ubuntu Server 13.04

Requirements:

Host OS:

  • Ubuntu Server 13.04 x86-64 (64bit)
  • At least 4GB RAM
  • 300GB available disk space

Sybase Patches:

  • If you’re going to run 32bit SQL Anywhere (or an embedded RSSD with Replication Server), you need to apply a SQL Anywhere patch: http://search.sybase.com/kbx/changerequests?bug_id=694479

Install LXC and the bridge-utils to support a network bridge:

aptitude install lxc debootstrap bridge-utils

Create the network bridge br0:

cd /etc/network

Edit interfaces:
replace:

  auto eth0
  iface eth0 inet dhcp

with:

  # LXC bridge
  auto br0
  iface br0 inet dhcp
      bridge_ports eth0
      bridge_stp off
      bridge_fd 0
      bridge_maxwait 0

  auto eth0
  iface eth0 inet manual

Restart networking:

service networking restart

Create lxc-default-sybase Apparmor profile:

cd /etc/apparmor.d/lxc
cp lxc-default lxc-default-sybase

Edit lxc-default-sybase:
replace:

  profile lxc-container-default flags=(attach_disconnected,mediate_deleted) {

with:

  profile lxc-container-default-sybase flags=(attach_disconnected,mediate_deleted) {

replace/add only if using you’re using LXC version LOWER than 0.7.5-3ubuntu60 – see LXC bug 1021411:

   deny @{PROC}/sys/kernel/** wklx,

with:

   deny @{PROC}/sys/kernel/(^shm)** wklx,

Update the kernel with the new lxc-default-sybase profile

apparmor_parser -r /etc/apparmor.d/lxc-containers

Replace /etc/lxc/lxc.conf with:

lxc.network.type=veth
lxc.network.link=br0
lxc.network.flags=up

# cap the memory available to the container
lxc.cgroup.memory.limit_in_bytes = 2G

Assuming that volume group VG02 exists, let’s create the container with the name of “sybase”, and a logical volume of 100G using the xfs file system:

lxc-create -n sybase -t ubuntu -B lvm --lvname sybase --vgname VG02 --fstype xfs --fssize 100G

Before we start the ‘sybase’ container, we need to update the container configuration to use the lxc-container-default-sybase container:

cd /var/lib/lxc/sybase

Edit config:
replace:

  #lxc.aa_profile = unconfined

with:

  lxc.aa_profile = lxc-container-default-sybase

We’re ready to start the container in console mode (to start it as a daemon add -d):

lxc-start -n sybase

The default login credentials are ubuntu / ubuntu

Let’s switch to root so we can install the packages to support Sybase ASE 15.7:

sudo su - root

Enable up to 512MB of shared memory:

echo "kernel.shmmax = 536870912" >> /etc/sysctl.conf
sysctl -p

Install the packages (nmon is a great os monitoring tool but if you don’t want it, don’t include it):

dpkg --add-architecture i386
apt-get update
apt-get install ia32-libs
apt-get install aptitude libaio1 xauth ia32-libs libstdc++5 nmon ncompress

If you want to install PPA repositories:

apt-get install python-software-properties

We need to set up the locale:

locale-gen en_US.UTF-8
echo 'LANG="en_US.UTF-8"' > /etc/default/locale

Copy the sybase software to your new Linux Container and extract it into a temporary directory.

Because Ubuntu seems to think that gzip can uncompress files compressed with the ‘compress’ command (it really can’t btw), you need to alias the uncompress with /usr/bin/uncompress.real:

alias uncompress='/usr/bin/uncompress.real'

From here, you would install and configure Sybase ASE 15.x as you normally would.

Determine ip address for eth0:

ifconfig|grep "inet addr"

In my case, the eth0 ip address is 192.168.0.115. You will either want to reserve this address with your DHCP server or setup a static ip in /etc/interfaces.

Assuming we want to keep the 192.168.0.115 address for this container, we need to modify the /etc/hosts file:
Replace:

  127.0.0.1 localhost sybase

with:

  127.0.0.1 localhost
  192.168.0.115 sybase
Share Button

How to install PostgreSQL 9 on Ubuntu Linux 10.04/10.10

Very easy, just add a repository and run apt-get install postresql-9.0 🙂

Dctr Watson explains how:

Installing PostgreSQL 9.0 on Ubuntu 10.04

Share Button