Category Archives: Databases

How to list Vertica Tables by number of rows ?

When working with Vertica, you may need to find out which tables have the largest number of rows. To do so, you must tap into the storage_containers system table, that will give metrics about the number of rows, AND the number of deleted rows. But since containers are distributed, you will need to aggregate this data, and ensure to limit your query to the super-projection, which guarantee to contain all table info. In brief :

with num_rows as (
    select schema_name,
           anchor_table_name as table_name,
           sum(total_row_count - deleted_row_count) as rows
    from v_monitor.storage_containers sc
    join v_catalog.projections p
         on sc.projection_id = p.projection_id
         and p.is_super_projection = true
    group by schema_name,
select schema_name,
       max(rows) as rows
from num_rows
group by schema_name,
order by rows desc;

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

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”);

The issue comes from Continue reading