Database

To test Cassandra replication, I removed a container containing a Cassandra node. Then rebuilt the container. To attach the Cassandra node using an existing/replaced address, using instructions from Replace a dead node in Cassandra, the trick is to add

JVM_OPTS="$JVM_OPTS -Dcassandra.replace_address=x,x,x,x"

to the end of /etc/cassandra/cassandra-env.sh, and remove the line after the node has re-connected.

# dpkg -i cassandra_3.11.4_all.deb 
Selecting previously unselected package cassandra.
(Reading database ... 12644 files and directories currently installed.)
Preparing to unpack cassandra_3.11.4_all.deb ...
Unpacking cassandra (3.11.4) ...
dpkg: dependency problems prevent configuration of cassandra:
 cassandra depends on openjdk-8-jre-headless | java8-runtime; however:
  Package openjdk-8-jre-headless is not installed.
  Package java8-runtime is not installed.
 cassandra depends on python (>= 2.7); however:
  Package python is not installed.

dpkg: error processing package cassandra (--install):
 dependency problems - leaving unconfigured
Processing triggers for systemd (241-5) ...
Errors were encountered while processing:
 cassandra
# apt-get -f install
Reading package lists... Done
Building dependency tree
Reading state information... Done
Correcting dependencies... Done
The following additional packages will be installed:
....

With this version of Java:

# java -version
openjdk version "11.0.4" 2019-07-16
OpenJDK Runtime Environment (build 11.0.4+11-post-Debian-1deb10u1)
OpenJDK 64-Bit Server VM (build 11.0.4+11-post-Debian-1deb10u1, mixed mode, sharing)

Some changes need to be made to the jvm options file:

#diff /etc/cassandra/jvm.options.original /etc/cassandra/jvm.options
102c102
< -XX:ThreadPriorityPolicy=42
---
> # -XX:ThreadPriorityPolicy=42
203c203
< -XX:+UseParNewGC
---
> # -XX:+UseParNewGC
247,251c247,251
< -XX:+PrintGCDateStamps
< -XX:+PrintHeapAtGC
< -XX:+PrintTenuringDistribution
< -XX:+PrintGCApplicationStoppedTime
< -XX:+PrintPromotionFailure
---
> #-XX:+PrintGCDateStamps
> #-XX:+PrintHeapAtGC
> #-XX:+PrintTenuringDistribution
> #-XX:+PrintGCApplicationStoppedTime
> #-XX:+PrintPromotionFailure
254,256c254,256
< -XX:+UseGCLogFileRotation
< -XX:NumberOfGCLogFiles=10
< -XX:GCLogFileSize=10M
---
> #-XX:+UseGCLogFileRotation
> #-XX:NumberOfGCLogFiles=10
> #-XX:GCLogFileSize=10M

But 'nodetool status' will abort with a NullPtrException, so can't use cassandra 3.11 on Buster. It will need to be installed on Stretch. Cassandra 4 may work on Buster.

• Cassandra vs. MongoDB
• About Deletes and Tombstones in Cassandra
• Cassandra anti-patterns: Queues and queue-like datasets
• Is it possible to use a cassandra table as a basic queue
• Redis::JobQueue
• Transactionally Staged Job Drains in Postgres
• Scylla Summit 2017: Planning Your Queries for Maximum Performance
• High performance queues with Cassandra
• Counting Keys In Cassandra
• Cassandra anti-patterns: Queues and queue-like datasets

• What Should a PostgreSQL Migrator do?: discusses schema migration methods from version to version
• arrays
• inheritance
• analytics
• statistics collector
• dynamic tracing
• logs
• proxies

This is something worth recording. The original article Transactionally Staged Job Drains in Postgres, which is informative by itself, was linked from Hacker News, which has even more supporting information.

Building a robust background worker system that leverages transactional isolation to never work a job too early, and transactional durabiity to never let one drop.

With this pattern, jobs aren’t immediately sent to the job queue. Instead, they’re staged in a table within the relational database itself, and the ACID properties of the running transaction keep them invisible until they’re ready to be worked. A secondary enqueuer process reads the table and sends any jobs it finds to the job queue before removing their rows.

Jobs are only removed after they’re successfully transmitted to the queue, so even if the worker dies partway through, it will pick back up again and send along any jobs that it missed. At least once delivery semantics are guaranteed.

Performing database versioning the hard way means going into some management software and manually making changes to fields, tables, and relationships. This works fine for a one person project. But as soon as more developers are added to a project making use of one or more databases, chaos is bound to ensue.

Many shops have learned the wisdom of using version control to ensure consistency and centralization of source code control. On the other hand, very few have been able to do database versioning in a like fashion.

Mooney has a different way of saying it, but I suppose it boils down to:

• software versioning is reasonably easy as it only relies on getting everyone's text to play well together, and is usually processed in batches, but is otherwise in a quiescent state
• database versioning is more difficult as there are at least two factors at work: a) the database schema, which defines the data, and b) the data itself, with both living in a dynamic, always-on environment

Versioning your development databases has another good overview of handling versioning, testing, and deployment of database changes. All handled in a fashion similar to normal code version management.

The most elegant solution I've seen to-date is found in the Python language environment, specifically the SqlAlchemy database library through the Alembic add-on.

And to prove that this is not a new problem, there is a book called "Refactoring Databases: Evolutionary Database Design" which has been out since 2006.

Release 5.0.1 of Debian's Lenny GNU/Linux distribution includes version 8.3 of PostgreSQL.

During the creation of a new Debian Lenny server, a list of software packages is provided. To make a new PostgreSQL-only server, unselect everything, including the 'Standard system', then select 'SQL Database', and proceed with the installation.

Once installation has completed, and the new server has rebooted, the PostgreSQL service is not auto-started. There are a couple of manual commands to be applied. In prior versions, PostgreSQL was auto-started. I think I understand the reasoning, particularily because it is useful for my situation.

During the server creation, I have a separate set of disks allocated for the database. By manually finishing the PostgreSQL implementation, I am able to initialize the directory location during service creation. If I have I've mounted my drives at /var/local/db, then these two commands get the PostgreSQL 8.3 service started:

pg_createcluster -d /var/local/db 8.3 main
/etc/init.d/postgresql-8.3 start

Back in Febrary, I wrote a longish article on how to upgrade PostgreSQL. That article is outdated. An upgrade can now take place with two lines:

pg_upgradecluster -v 8.3 8.2 main
pg_dropcluster 8.2 main

The first copies the older version 8.2 files to the new 8.3 files directory. It does any modifications necessary. The second line then removes the old stuff.

Upgrading a PostgreSQL server version 7.x to version 8.x was relatively painless. It helped that there wasn't a significant amount of data to move, or blob objects to move.

For a Debian server on which I recently ran 'apt-get dist-upgrade', the Debian crew made PostgreSQL 8.2 run along side version 7.4. The /etc/postgresql/8.2/main/postgresql.conf file showed the server port as being 5433, by default. Since the service wasn't turned on, I set it for 5432, and changed the listen_addresses for '* Continue reading "Upgrading PostgreSQL 7.x to 8.x" »

Back in October 2007, I wrote an article about PostgreSQL, C++ and the lack of binary data and wrappers.

I stand corrected. On two counts. The first is binary access. After looking through the PostgreSQL API, I see that I can indeed get binary data into and out of a database easily. The C based library, libpq, has an API call named PQexecParams which allows full separation of binary values from the query statement. This is a good step towards preventing SQL injection attacks. Continue reading "Open Source Site of the Day -- SOCI: The C++..." »

I've looked at the C library for PostgreSQL and then wanted to see if there were any C++ wrappers for it. The most current appears to be libpqxx. On first blush, it looks very good. It if functional and robust. But... it has one draw back. The library insists on converting all binary stuff into text for passing back to the caller. For some applications, that can be a reasonable library simplifier.

But in this day and age of Templates and polymorphism, it seems to be a copout. Yes, if I had time, I'd probably try my hand at implementing some sort of 'variant' implementation to handle the various types of data that come back. Continue reading "libpqxx: A PosgreSQL C++ Wrapper Library" »

Many web sites use a back end SQL engine for serving up data. Some Credit Card number thefts can be attributed to poor protection of web page interactions with a back end SQL server through injecting SQL statements into web requests.

A well written article called The Unexpected SQL Injection goes into detail on how SQL Injection happens, and how to prevent it.

Spend some time on the site. There are many other documents providing valuable information regarding the protection of internet based activity.