Category Archives: Databases

How to run a MariaDB local docker container ?

I’m developing on Windows software solutions targeting debian servers, and relying on MariaDB databases, whose versions vary a lot.

Docker is great to reproduce a production environment, and eliminate common issues related to versioning, or OS specificities (yes, I’m think about case sensitivity in table names on linux, which doesn’t occur on Windows).

So, here is a simple docker command to start a mariadb 10.3 container available on local port 3310 (as explained in details by the MariaDB team here):

docker run -p 3310:3306 --name mariadb -e MYSQL_ROOT_PASSWORD=myRootPassword -d mariadb/server:10.3

How to find a Vertica database last good epoch ?

If you need to check if your Vertica ROS is lagging behind your WOS, you can use the following query to retrieve the last good epoch (i.e : the last one written to disk, which ensures you won’t lose any data even in the case of a catastrophic failure where all your nodes would shut down simultaneously) :

SELECT epoch_number, epoch_close_time
FROM v_monitor.system
JOIN epochs dbadmin ON epoch_number = last_good_epoch;

Named parameter not found in JPA/Hibernate native SQL queries

When using named parameters in native SQL queries with Hibernate implementation of the Java Persistence API (JPA), you may run into the following error
[code lang=java]
org.springframework.dao.InvalidDataAccessApiUsageException: Parameter with that name [orderId] did not exist;
nested exception is java.lang.IllegalArgumentException: Parameter with that name [orderId] did not exist
[/code]

This happens even if the named parameter was properly set, as in the following example :

For example :
[code lang=java]
void update(EntityManager em) {
Query q = em.createNativeQuery(“SELECT * FROM orders where id = :orderId;”);
q.setParameter(“orderId”, “1234”);
q.executeUpdate();
}
[/code]

The issue comes from Continue reading

Using Amazon Redshift with Hibernate

Amazon Redshift is not designed for ORMs like Hibernate. However, it sometimes may be useful to let Hibernate manage a couple of tables. For example, we were automatically generating some large tables for big-data analysis, and wanted to store metadata about the generated structures in the same database, to keep them in sync. Hibernate seemed convenient to manipulate these metadata.

First, that’s not a good idea. Hibernate tends to generate a lot of useless queries, unless you take the time to properly tweak it, you’ll feel the pain of letting Hibernate query a RedShift server on the other side of the planet (close to 0.5 second for a “SELECT 1” query sent to the database…).

Then, you’ll notice that Redshift SQL may be based on Postgres, still not everything is working as expected. Especially, postgres serial type (identity columns) are not supported.

Eventually, you’ll find out there’s no way to retrieve the value of the last identity generated for a inserted row. That’s a serious issue knowing that hibernate requires a primary key to identify inserted objects.

But if you still crave using Hibernate with RedShift, once you’ll have downloaded the JDBC driver, you may want to use this (unperfect) Hibernate Redshift dialect to get you started :
[code lang=java]
import org.hibernate.dialect.PostgreSQL82Dialect;
import org.hibernate.id.IdentityGenerator;

public class RedshiftDialect extends PostgreSQL82Dialect {

@Override
public String getIdentitySelectString(String table, String column, int type) {
// There’s not method in the JDBC driver to retrieve the latest generated id.
// So try to retrieve the largest id, and pray for no concurrent access.
return “SELECT MAX(“+column+”) from “+table;
}

@Override
public String getIdentitySelectString() {
// Should never be called
return “SELECT 1 FROM PG_TEST”;
}

@SuppressWarnings(“rawtypes”)
@Override
public Class getNativeIdentifierGeneratorClass() {
return IdentityGenerator.class;
}

@Override
public String getIdentityColumnString( int type )
{
// replace the Postgres “serial” type with RedShift Vertica one
return “IDENTITY”;
}

@Override
public boolean hasDataTypeInIdentityColumn()
{
return true;
}
}
[/code]