Category: Technology

Troubleshooting Kafka

Our server metrics are fed into a Kafka bus, and various applications are able to pick up and process this data. Problem is, however, that everything I’m sending doesn’t end up in the downstream system. The conflunce_kafka module I’m using in python reports that data is send along it’s merry way, but the primary system that is used to present metrics to end users says they’re not consistently getting data across the channel. Not never like there’s something outright wrong, but long periods of time where there’s no data followed by a cycle where data shows up.

I’ve exhausted all of the in-script debugging I can — the messages are getting there. But I wondered if the async nature of Kafka might mean that the client’s “it got there” wouldn’t actually mean something arrived. So I had to figure out how to test a Kafka server the same way I test my MQTT server — how do I use a quick command line program to send a message and how do I use a quick command line program to subscribe to various topics.

Turns out this is easier than anticipated — the binary build of Kafka includes windows batch files. Download the latest Kafka binary. Untar/unzip it somewhere. This is easy if you have the Win32 port of the GNU utilities and can just run “tar vxfz kafka_2.13-2.8.0.tgz”.

In the .\kafka<version>\bin\windows folder, there are kafka-console-consumer.bat and kafka-console-producer.bat files that can be used for testing Kafka. You can open two command prompts — one for the producer (sending data to Kafka) and one for the consumer (watching Kafka for new messages). In the consumer window, run

kafka-console-consumer.bat –bootstrap-server yourkafkaserver.example.com:Port –topic Test

Then, in the producer, run

kafka-console-producer.bat –broker-list yourkafkaserver.example.com:Port –topic Test

The producer will bring you to a “>” prompt where you can type some strings and hit enter to send the message to Kafka. You should see the messages pop into the consumer window.

To subscribe to multiple topics, use “–whitelist” followed by a pipe-bar delimited list of topics.

Oracle Collection Instead of Dual

I’m still retrofitting a bunch of SQL queries to use bind_by_name and came across a strange scenario. I created a recursive query (STARTS WITH / CONNECT BY PRIOR) but I needed to grab the original value too. The quickest way to accomplish this was to union in something like “select 12345CDE as equipment_id from dual”. But the only way to get a bunch of these original values grafted onto the result set is to iterate through the array once to build my :placeholder1, :placeholder2, …, placeholderN placeholders and then iterate through the array again to bind each placeholder to its proper value.

I’ve been working with Oracle collections for LIKE and IN queries, and thought I could use a table that only exists within the query to glom the entire array into a single placeholder. It works! A query like

select column_value equipment_id from TABLE(sys.ODCIVARCHAR2LIST('12345CDE', '23456BCD', '34567ABC') );

Adds each of the values to my result set.

Which means I can use a query like “select column_value as equipment_id from TABLE(:myIDs)” and bind the collection to :myIDs.

Docker – List Container Startup Policies

A quick one-line Bash command to output all containers and the startup policy:

docker container ls -aq | xargs docker container inspect --format '{{ .Name }}: {{.HostConfig.RestartPolicy.Name}}'

For Docker on Windows, the following PowerShell command produces similar results:

$jsonData = docker container ls -aq |%{docker container inspect --format "{{json .}}"$_}
[System.Collections.ArrayList]$arrayContainerConfig = @()
foreach($jsonContainerConfig in $jsonData ){
	$psobjContainerConfig = ConvertFrom-JSON $jsonContainerConfig
	$arrayContainerConfig.add(@{Name=$psobjContainerConfig.Name;Hostname=$psobjContainerConfig.Config.Hostname;CurrentlyRunning=$psobjContainerConfig.State.Running;RestartPolicy=$psobjContainerConfig.HostConfig.RestartPolicy.Name})
}

$arrayContainerConfig | ForEach {[PSCustomObject]$_} | Format-Table -AutoSize

Oracle – Collections and IN or LIKE Queries

I’ve been retrofitting a lot of PHP/SQL queries to use oci_bind_by_name recently. When using “IN” clauses, you can iterate through your array twice, but it’s an inefficient approach.

// Build the query string with a bunch of placeholders
$strQuery = "select Col1, Col2, Col3 from TableName where ColName IN (";
for($i=0; $i < count($array); $i++){
    if($i > 0){
        $strQuery = $strQuery . ", ";
    }
    $strQuery = $strQuery . ":bindvar" . $i;
}
$strQuery = $strQuery . ")";
... 
// Then bind each placeholder to something
for($i=0; $i < count($array); $i++){
    oci_bind_by_name($stmt, ":bindvar".$i, $array[$i]);
}

Building a table from the array data and using an Oracle collection object creates cleaner code and avoids a second iteration of the array:

$strQuery = "SELECT indexID, objName FROM table WHERE objName in (SELECT column_value FROM table(:myIds))";
$stmt = oci_parse($conn, $strQuery);

$coll = oci_new_collection($kpiprd_conn, 'ODCIVARCHAR2LIST','SYS');
foreach ($arrayValues as $strValue) {
     $coll->append($strValue);
}
oci_bind_by_name($stmt, ':myIds', $coll, -1, OCI_B_NTY);
oci_set_prefetch($stmt, 300);
oci_execute($stmt);

A simple like clause is quite straight-forward

$strNameLikeString = "SomeName%";
$strQuery = "SELECT ds_dvrsty_set_nm from ds_dvrsty_set WHERE ds_dvrsty_set_nm LIKE :divsetnm ORDER BY ds_dvrsty_set_nm DESC fetch first 1 row only";

$stmt = oci_parse($connDB, $strQuery);
oci_bind_by_name($stmt, ":divsetnm", $strNameLikeString);
oci_set_prefetch($stmt, 300);
oci_execute($stmt);

But what about an array of inputs essentially reproducing the LIKE ANY predicate in PostgreSQL? There’s not a direct equivalent in Oracle, and iterating through the array twice to build out a query WHERE (Field1 LIKE ‘Thing1%’ OR Field1 LIKE ‘Thing2%’ OR Field1 LIKE ‘Thing3%’) is undesirable. The with EXISTS allows me to create a LIKE ANY type query and only iterate through my array once to bind variables to placeholders using the same collection approach as was used with the IN clause. 

$arrayLocs = array('ERIEPAXE%', 'HNCKOHXA%', 'LTRKARXK%');
$strQuery = "SELECT location_id, clli_code FROM network_location WHERE EXISTS (select 1 FROM TABLE(:likelocs) WHERE clli_code LIKE column_value)";
$stmt = oci_parse($connDB, $strQuery);

$coll = oci_new_collection($connDB, 'ODCIVARCHAR2LIST','SYS');
foreach ($arrayLocs as $strLocation) {
    $coll->append($strLocation);
}
oci_bind_by_name($stmt, ':likelocs', $coll, -1, OCI_B_NTY);
oci_execute($stmt);
print "<table>\n";
print "<tr><th>Loc ID</th><th>CLLI</th></tr>\n";
while ($row = oci_fetch_array($stmt, OCI_ASSOC+OCI_RETURN_NULLS)) {
    print "<tr><td>" . $row['LOCATION_ID'] . "</td><td>" . $row['CLLI_CODE'] . "</td></tr>\n";
}
print "</table>\n";

There are many different collection types in Oracle which can be used with oci_new_collection. A full list of the system collection types can be queried from the database. 

SELECT * FROM SYS.ALL_TYPES WHERE TYPECODE = 'COLLECTION' and OWNER = 'SYS';

PostgreSQL Sequences

I’m having a problem with a database refusing to change INSERTS to UPDATES on the ON CONFLICT condition — it insists that a ‘duplicate key value violates unique constraint’. A little time with a search engine tells me that sequences can get out of sync, and then you cannot insert items into the table. How do you know your sequence is out of sync?

SELECT NEXTVAL((SELECT PG_GET_SERIAL_SEQUENCE('"table_name"', 'name_of_column_with_sequence'))) as "NextValue", MAX("name_of_column_with_sequence") AS "Max Value" FROM "table_name";

So null seems like it would be a problem!

For future reference, when the next value is smaller than the max value in the table, the solution is to set the series value based on the max value

SELECT SETVAL((SELECT PG_GET_SERIAL_SEQUENCE('"table_name"', 'name_of_column_with_sequence')), (SELECT (MAX("name_of_column_with_sequence") + 1) FROM "table_name"), FALSE);

Android 11 Arrived!

This morning, my phone was very slow. It got progressively worse — finally getting to a point where there was a ten second lag between touching something and a response. I was trying to reboot because closing all of the apps didn’t do anything. The phone locked instead. And, when I woke the screen back up, I had a strange circle arrow icon in the notification bar. It turns out my phone had been downloading an OS update. It was soon ready to install, and I was actually able to use my phone again. Installed the update — that took a long while too — and voila, I’ve got Android 11 on a TCL T770B. Woohoo!

Excel – Converting Unix Timestamp to Human Readable Date(time)

You can use the formula =(B2/86400)+DATE(1970,1,1) to convert a unix epoch time to a human readable date (or date time). In my case, I have the unix timestamp in microseconds so I’ve got to divide by 86400000. The value you get is a not-so-meaningful float … but that’s actually a date.

Select a date format to display the value as a date

Or chose a custom format and use something like “m/d/yyyy hh:mm” to display a date and time.