postgres – btree level 1 not found in index

We have a jira instance which uses the postgres database to store its information. The server had roughly 50 Gb of disk, so it ran out of space before we noticed. We extended the partition to 100 Gb but apparently it was too late.
Jira started complaining about it with an 500 internal server error (Estado HTTP 500 – Could not determine database type. (Conexión rechazada. Verifique que el nombre del Host y el puerto sean correctos y que postmaster este aceptando conexiones TCP/IP.)). Viewing the logs we found Caused by: java.net.ConnectException: Connection refused

We checked our database and database settings for external connections and couldn’t connect.

Then we tried to start the postgres service

sudo service postgresql start

2018-12-04 09:01:59.268 CST [21281] LOG:  database system was interrupted while in recovery at 2018-12-04 08:59:20 CST
2018-12-04 09:01:59.268 CST [21281] HINT:  This probably means that some data is corrupted and you will have to use the last backup for recovery.
2018-12-04 09:01:59.631 CST [21281] LOG:  database system was not properly shut down; automatic recovery in progress
2018-12-04 09:01:59.636 CST [21281] LOG:  redo starts at 7/CC020C68
2018-12-04 09:01:59.639 CST [21281] LOG:  record with zero length at 7/CC07E998
2018-12-04 09:01:59.639 CST [21281] LOG:  redo done at 7/CC07E968
2018-12-04 09:01:59.639 CST [21281] LOG:  last completed transaction was at log time 2018-12-03 10:29:49.99078-06
2018-12-04 09:01:59.641 CST [21281] FATAL:  btree level 1 not found in index "19881"

VITALLY IMPORTANT FIRST RESPONSE

I did a backup of the postgres data files

ssh admin@jira.server
sudo tar cvf postgres.bkp.tar /var/lib/postgresql/

Copied it to my local machine

rsync -av admin@jira.server:postgres.bkp.tar .

then installed the same version (series) that is on the server into my local machine.
Since I have ubuntu 18.04 I had to add the postgres repository to install postgres version 9.3.

echo «deb http://apt.postgresql.org/pub/repos/apt/ bionic-pgdg main» | sudo tee /etc/apt/sources.list.d/pgdg.list
wget –quiet -O – https://www.postgresql.org/media/keys/ACCC4CF8.asc | sudo apt-key add –
sudo apt update
sudo apt install postgresql-9.3 postgresql-contrib-9.3

Then I tried to run postgres locally with the backup data

cd /tmp
tar xvf postgres.bkp.tar
sudo su postgres
/usr/lib/postgresql/9.3/bin/postgres -D /tmp/var/lib/postgresql/9.3/main/

it complained about not finding the configuration file so I just copied it from the default dirs

cp /etc/postgresql/10/main/postgresql.conf /tmp/var/lib/postgresql/9.3/main/
cp -R /etc/postgresql/10/main/conf.d/ /tmp/postgres/var/lib/postgresql/9.3/main/

edit the postgresql.conf file and change the data dir appropiately

data_directory = ‘/tmp/var/lib/postgresql/9.3/main’

then I tried again (but this time in single mode) and it reproduced exactly the same error as on server. Great!

postgres@elite-tsj:/tmp$ /usr/lib/postgresql/9.3/bin/postgres --single -P -d 1 -D /tmp/var/lib/postgresql/9.3/main/
2018-12-04 09:01:59.268 CST [21281] LOG:  database system was interrupted while in recovery at 2018-12-04 08:59:20 CST
2018-12-04 09:01:59.268 CST [21281] HINT:  This probably means that some data is corrupted and you will have to use the last backup for recovery.
2018-12-04 09:01:59.631 CST [21281] DEBUG:  checkpoint record is at 7/CC025C50
2018-12-04 09:01:59.631 CST [21281] DEBUG:  redo record is at 7/CC020C68; shutdown FALSE
2018-12-04 09:01:59.631 CST [21281] DEBUG:  next transaction ID: 0/9547804; next OID: 227497
2018-12-04 09:01:59.631 CST [21281] DEBUG:  next MultiXactId: 3; next MultiXactOffset: 5
2018-12-04 09:01:59.631 CST [21281] DEBUG:  oldest unfrozen transaction ID: 676, in database 1
2018-12-04 09:01:59.631 CST [21281] DEBUG:  oldest MultiXactId: 1, in database 1
2018-12-04 09:01:59.631 CST [21281] DEBUG:  transaction ID wrap limit is 2147484323, limited by database with OID 1
2018-12-04 09:01:59.631 CST [21281] DEBUG:  MultiXactId wrap limit is 2147483648, limited by database with OID 1
2018-12-04 09:01:59.631 CST [21281] LOG:  database system was not properly shut down; automatic recovery in progress
2018-12-04 09:01:59.634 CST [21281] DEBUG:  resetting unlogged relations: cleanup 1 init 0
2018-12-04 09:01:59.636 CST [21281] LOG:  redo starts at 7/CC020C68
2018-12-04 09:01:59.639 CST [21281] LOG:  record with zero length at 7/CC07E998
2018-12-04 09:01:59.639 CST [21281] LOG:  redo done at 7/CC07E968
2018-12-04 09:01:59.639 CST [21281] LOG:  last completed transaction was at log time 2018-12-03 10:29:49.99078-06
2018-12-04 09:01:59.639 CST [21281] DEBUG:  resetting unlogged relations: cleanup 0 init 1
2018-12-04 09:01:59.641 CST [21281] FATAL:  btree level 1 not found in index "19881"

since this data is a copy i just executed pg_resetxlog as shown here.
Be aware that use of the pg_resetxlog utility should be done as an absolute last resort, and there are still some things you should try first..

/usr/lib/postgresql/9.3/bin/pg_resetxlog /tmp/var/lib/postgresql/9.3/main/

it complained I had to force it, there you go

/usr/lib/postgresql/9.3/bin/pg_resetxlog -f /tmp/var/lib/postgresql/9.3/main/

done! Now let’s see if it’s working again

sudo su postgres
/usr/lib/postgresql/9.3/bin/postgres -D /tmp/var/lib/postgresql/9.3/main/

boom! it’s working.

Now it’s time to do a backup with sql dump (pg_dump) or better yet a full sql dump (pg_dumpall).

In another terminal (or tab)

sudo su postgres
pg_dumpall -p 5433 > /tmp/postgres-full.dmp

in my case I had to specify the port (-p 5433).

Now that I have a full backup I can try the same on production.

/usr/lib/postgresql/9.3/bin/pg_resetxlog -f /var/lib/postgresql/9.3/main/
sudo service postgresql start

thank the gods! (to recall the option pointed here)

Ubuntu – Crear una base de datos postgresql para un proyecto

Para comenzar un proyecto, de las primeras cosas que necesitamos es una base de datos. En ubuntu podemos utilizar postgresql.

Para instalar postgres basta con ejecutar el siguiente comando

sudo apt-get update
sudo apt-get install postgresql postgresql-contrib

Una vez instalado podemos conectarnos como el usuario postgres (super admin)

sudo -i -u postgres
psql

Una vez en el prompt de postgresql podemos ejecutar los siguientes comandos

\conninfo    # nos muestra información de nuestra conexión
\conn database_name    # cambiar la base de datos activa
\d    # lista los objetos de la base de datos
\dt    # lista las tablas de la base de datos
\q    # salir del prompt de postgresql

Ya fuera del prompt de postgresql podemos crear un usuario y su base de datos de la siguiente manera

createuser -D -A -P username
createdb -O username dbname

El primer comando crea un usuario sin privilegio de crear bases de datos (-D), ni agregar usuarios (-A) y nos preguntará la contraseña para el usuario (-P). El segundo comando crea la base de datos y asigna como propietario al usuario que especifiquemos.

Para conectarnos como un usuario diferente necesitamos hacerlo de la siguiente manera

psql -U username -h 127.0.0.1 database

Fuentes:
https://www.digitalocean.com/community/tutorials/how-to-install-and-use-postgresql-on-ubuntu-14-04
http://stackoverflow.com/questions/8420761/postgres-authentication-fails-when-try-to-login-with-different-unix-user
https://help.ubuntu.com/community/PostgreSQL#Managing_users_and_rights

Spring – Utilizar un pool de conexiones y una clase auxiliar para la ejecución de SQL

La mayoría de aplicaciones requieren acceso a una base de datos. En java existe JDBC que nos permite realizar esta tarea.
En una aplicación distribuida y escalable nos vemos en la necesidad de utilizar un pool de conexiones para no causar un problema de rendimiento. Por ejemplo, he creado servicios web (REST) que deseo sean consumidos desde dispositivos móviles, para ello necesito que los servicios sean escalables.

Ya que utilicé Spring Roo para crear la plantilla inicial del proyecto, este me agregó la siguiente configuración inicial.

applicationContext.xml

...
    <bean class="org.apache.commons.dbcp.BasicDataSource" destroy-method="close" id="dataSource">
        <property name="driverClassName" value="${database.driverClassName}"/>
        <property name="url" value="${database.url}"/>
        <!--<property name="username" value="${database.username}"/>-->
        <!--<property name="password" value="${database.password}"/>-->
        <property name="testOnBorrow" value="true"/>
        <property name="testOnReturn" value="true"/>
        <property name="testWhileIdle" value="true"/>
        <property name="timeBetweenEvictionRunsMillis" value="1800000"/>
        <property name="numTestsPerEvictionRun" value="3"/>
        <property name="minEvictableIdleTimeMillis" value="1800000"/>
        <property name="validationQuery" value="SELECT 1 FROM DUAL"/>
    </bean>
...

como podemos ver, esta configuración crea un DataSource de tipo org.apache.commons.dbcp.BasicDataSource. Este tipo de DataSource crea un pool de conexiones automáticamente. Genial!

Ahora, ¿cómo lo utilizamos en nuestras clases para poder ejecutar una consulta SQL?

Podríamos utilizarlo de la forma tradicional con JDBC

...
    @Autowired
    public DistritosServiceImpl(DataSource dataSource) {
        this.dataSource = dataSource;
    }

    @Override
    public List<Distrito> getDistritos() {
        List<Distrito> distritos = new ArrayList<Distrito>();
        String sql = 
                "select id, nombre " +
                        "from distritos";
        Connection con = null;
        try{
            con = this.dataSource.getConnection();
            PreparedStatement stmt = con.prepareStatement(sql);
            ResultSet rs = stmt.executeQuery();
            while(rs.next()){
                distritos.add(new Distrito(rs.getInt("id"), rs.getString("nombre")));
            }
        }catch (Exception e){
            e.printStackTrace();
            if(con != null){
                try {
                    con.close();
                } catch (SQLException e1) {
                    e1.printStackTrace();
                }
            }
        }
        return distritos;
    }
...

pero existe una mejor manera, utilizando JdbcTemplate

...
    @Autowired
    public DistritosServiceImpl(DataSource dataSource) {
        this.dataSource = dataSource;
        this.jdbcTemplate = new JdbcTemplate(dataSource);
    }

    @Override
    public List<Distrito> getDistritos() {
        String sql =
                "select id, nombre " +
                        "from distritos";
        return jdbcTemplate.query(sql, new RowMapper<Distrito>() {
            @Override
            public Distrito mapRow(ResultSet rs, int i) throws SQLException {
                return new Distrito(rs.getInt("id"), rs.getString("nombre"));
            }
        });
    }
...

Fuentes:

ORA-06550: número o tipos de argumentos erróenos

Recientemente tuve que desempolvar un proyecto de .NET (C#) y hacer unas modificaciones.
Había que utilizar las clases de System.Data.Oracle en vez de Oracle.DataAccess debido a que, la segunda, ocasionaba un timeout de conexión con la versión 11.2 de Oracle Database Server.

Una vez que sustituí las clases y corregí todos los errores de compilación ocasionados por el cambio, ejecute la aplicación para probar su funcionalidad. Para mi sorpresa me lanzó la siguiente excepción:

ORA-06550: línea 1, columna 7: PLS-00306: número o tipos de argumentos erróenos al llamar a ‘PR_GETEXPEDIENTESXFACUERDO’
ORA-06550: línea 1, columna 7: PL/SQL: Statement ignored

Verifiqué muchas veces que el número o tipo de los parámetros fuera el correcto, pero seguía sin funcionar. En un acto de desesperación sustituí la llamada al procedimiento por una consulta sql estándar, pero no tuve éxito. Ya sin más ideas noté que el nombre de los parámetros en el código era distinto a los nombres en el procedimiento almacenado. Intenté renombrarlos, ya sin nada que perder, y asombrosamente funcionó. Al parecer la clase OracleCommand de System.Data.Oracle toma en cuenta el nombre de los parámetros mientras que la de Oracle.DataAccess no.

Fuentes:
https://community.oracle.com/thread/2171363?start=0&tstart=0
http://support.microsoft.com/kb/322160
http://blogs.msdn.com/b/spike/archive/2009/06/16/oracle-stored-procedures-with-ref-cursor-from-net-code-and-bid-tracing.aspx

Java – Ambiente de producción y desarrollo

Lo más común es desarrolllar nuestras aplicaciones en un ambiente de desarrollo, es decir, utilizar servidores de prueba para después implementarlo en un ambiente de produción. Si el código de tu proyecto se encuentra en un sistema de control de código (git, svn, etc.) es díficil, si no tedioso, cambiar entre un ambiente y otro. Tienes que modificar los archivos de configuración para que se conecte a la base de datos de producción cada vez que actualizas el sistema y debes tener cuidado de no incluir en el control de código las conexiones de prueba o visceversa.

Para resolver este problema podemos utilizar JNDI, pero se vuelve complicado el mantener un entorno replicado en nuestra máquina de desarrollador. Eixsten plataformas para desarrollar y ejecutar aplicaciones web de forma escalable -tales como- Heroku, Google App Engine o Microsoft Azure. Para resolver este problema de contextos, Heroku ha optado por utilizar variables de ambiente para cambiar entre el contexto de desarrollo y el de producción.

Esta solución me pareció adecuada y decidí implementarla en un proyecto Java. Realmente es muy sencillo, normalmente creamos una conexión a la base de datos de la siguiente forma:

DriverManager.getConnection(dbUrl, dbUser,dbPassword);

Necesitamos 3 cosas: url a la base de datos, usuario y contraseña.
Opcionalmente podemos incluir el usuario y contraseña en la URL de la base de datos si el controlador lo permite.En el caso de Oracle es de la siguiente manera:

jdbc:oracle:thin:[USER/PASSWORD]@[HOST][:PORT]:SID
jdbc:oracle:thin:[USER/PASSWORD]@//[HOST][:PORT]/SERVICE

Para leer la configuración de las variables de ambiente sería

DriverManager.getConnection(System.getenv(«DATABASE_URL»));

En nuestro caso utilizamos diferentes conexiones de acuerdo al usuario y la base de datos, por lo que seguimos una nomenclatura de la siguiente manera

USUARIO_AT_DATABASE

así por ejemplo, podríamos conectarnos a distintas bases de datos

   DriverManager.getConnection(System.getenv("RH_AT_QUERETARO"));
   DriverManager.getConnection(System.getenv("RH_AT_AMEALCO"));
   DriverManager.getConnection(System.getenv("USRCIVQRO_AT_QUERETARO"));

Por último necesitas configurar estas variables de ambiente en tu sistema operativo.

En Ubuntu lo puedes hacer de diferentes formas.
A mi me funcionó creando un archivo env.sh en la carpeta /etc/profile.d

   #!/usr/bin/bash
   export RH_AT_CENTRO_DE_JUSTICIA=jdbc:oracle:thin:rh/secret@127.0.0.1:1521:dbtest
   export COMPRAS_AT_AMEALCO=jdbc:oracle:thin:compras/secret@127.0.0.1:1521:dbtest

Fuentes:
https://devcenter.heroku.com/articles/heroku-postgresql#connecting-in-java
http://stackoverflow.com/questions/5547224/username-password-in-jdbc-connection-url
https://help.ubuntu.com/community/EnvironmentVariables
http://askubuntu.com/questions/307023/command-not-working-in-profile

Apache Shiro – Lista de usuarios en la base de datos de Oracle

Apache Shiro es un framework que nos permite configurar e implementar la autenticación, autorización, manejo de sesiones y criptografía en nuestras aplicaciones.
Para configurar Oracle como el almacen de usuarios, necesitas descargar el archivo ojdbc6.jar y agregarlo a la carpeta WEB-INF/lib o agregar una dependencia con Maven. Luego, agrega las siguientes líneas en el archivo de configuración shiro.ini

 [main]
 jdbcRealm=org.apache.shiro.realm.jdbc.JdbcRealm
# El valor por defecto de JdbcRealm es false
jdbcRealm.permissionsLookupEnabled=true
# El valor por defecto de JdbcRealm es "select password from users where username = ?"
 jdbcRealm.authenticationQuery = SELECT PASSWORD FROM TUSUARIOS WHERE SUSUARIO = ?
 # El valor por defecto de JdbcRealm es "select role_name from user_roles where username = ?"
 jdbcRealm.userRolesQuery = SELECT ROL FROM TROLES_USUARIO WHERE SUSUARIO = ?
# El valor por defecto de JdbcRealm es "select permission from roles_permissions where role_name = ?"
jdbcRealm.permissionsQuery= SELECT SPERMISO FROM TPERMISOS_ROL WHERE SROL = ?

ds = oracle.jdbc.pool.OracleDataSource
 ds.driverType = thin
 ds.serverName = 192.168.0.1
 ds.portNumber = 1521
 ds.databaseName = dbtest
 ds.user = scott
 ds.password = tiger
 jdbcRealm.dataSource = $ds

sha256Matcher = org.apache.shiro.authc.credential.Sha256CredentialsMatcher
 jdbcRealm.credentialsMatcher = $sha256Matcher
 

Es muy importante que establezcas la propiedad ‘permissionsLookupEnabled’ en true, de lo contrario no se cargarán los permisos del usuario y todas las llamadas al método Subject.isPermitted() regresaran false.

El último par de líneas nos permite almacenar el hash de las contraseñas utilizando el algoritmo sha256, para que nadie pueda verlas. La columna que almacena este valor la puse como varchar2(64) ya que siempre se genera una cadena de 64 caracteres. Puedes generar el hash desde la línea de comandos -en linux- ejecutando:

echo -n mi_password_secreto | sha256sum

o en la página http://www.xorbin.com/tools/sha256-hash-calculator

Fuentes:
http://shiro.apache.org/
http://raibledesigns.com/
http://www.java2s.com/
http://stackoverflow.com/

SQLDeveloper – Trabajar con MySQL

Habia escuchado que sqldeveloper te deja trabajar con MySQL pero nunca lo habia intentado. Decidi probarlo el dia de hoy y no fue tan facil como pense.

Primero crei que mediante las actualizaciones iba a poder instalar el soporte para MySQL asi que di clic en el menu Help->Check for Updates… y seleccione los 3 centros de actualizacion: Oracle SQL Developer, Oracle Extensions y Third Party SQL Developer Extensions. Despues busque «mysql» y me aparecio casi inmediatamente «MySQL JDBC Driver 11.1.1.58.17» en los resultados asi que segui el asistente esperando que todo quedara listo para crear una conexion a MySQL. Me pidio que aceptara la licencia del controlador y que ingresara mis datos de OTN para desargar los archivos necesarios. No me marco ningun error y me pidio que reiniciara la aplicacion. Una vez hecho esto quise crear mi conexion pero ups!… no aparecia ninguna opcion de MySQL en el asistente de conexion. Lo intente varias veces sin obtener resultados positivos.

Asi que buscando en internet encontre este sitio donde se explica como descargar e instalar manualmente el controlador JDBC de MySQL.

En resumen, descarga y descomprime el archivo Connector/J de esta pagina y ve a las preferencias de SQLDeveloper (Tools->Preferences) en la seccion de Database->Third Party JDBC Drivers, da clic en Add Entry y selecciona el archivo mysql-connector-java-5.1.13-bin.jar de la carpeta que descomprimiste. Listo, ya tendras disponible una pestaña de MySQL a la hora de crear una nueva conexion.

Pentaho – Compartir la Conexión a la Base de Datos

Para importar los datos de unas tablas de Visual Fox Pro a Oracle utilizo Pentaho Data Integration ,tambien conocido como Kettle o Spoon.
Para relizar esta tarea he creado un «trabajo (job)» para controlar el orden de ejecución y varias «transformaciones (transformations)» para copiar los datos. Para no tener que configurar las mismas conexiones en cada transformación, busqué la forma de compartir una conexión. Afortunadamente Keetle nos permite hacer esto de una forma muy sencilla, solo tienes que dar clic derecho sobre la conexion que deseas compartir y dar clic en «Share» -esto guardará la información de la conexión en el archivo «~/.kettle/shared.xml» y te permitirá usar la conexión en otras transformaciones-
Si ya tenías abierta la transformación donde quieres utilizar la conexión, cierrala y abrela nuevamente para que se actualice la lista de conexiones.