On the Lambda

Programming, Technology, and Systems Administration

On the Lambda

What’s up with MySql

May 4th, 2022 · No Comments · development, servers, sql

Historically, when one considers database server platforms there are four major players: Oracle, MySql, Sql Server, and Postgresql. (One could argue for Sqlite and even Access, but those are in-process engines and really belong in a different category.) Unfortunately, I question whether MySql still belongs with this group. It’s come to the point where I actively advocate avoiding MySql for new development. While I understand moving an existing, functioning app may be more trouble than it’s worth, if you’re starting from scratch I just can’t in good faith recommend MySql anymore.

To understand why, I need to deliver a short history lesson. Let’s go back to 2004. At this time the database market wasn’t exactly young any more, but products were still raw compared to what we have today. Enter MySql. It was a very capable database for the era, and could keep up and even beat the “enterprise” competitors charging into 5 and 6 figures for similar deployments, while at the same time being the only real FOSS option (Postgresql existed, but wasn’t really mature yet). For this reason MySql was widely adopted, especially among low-cost web hosts.

Unfortunately, MySql came to be owned by Sun, and Sun had fallen on hard times. They also had Java, but neither Java nor MySql brought in much revenue. Sun’s cash product selling Sparc workstations was fading, and fast, and so Sun couldn’t afford to support MySql development beyond basic bug fixes. Thus MySql began to stagnate.

Enter Oracle, who decides to buy out Sun. Unfortunately, Oracle wants Sun mainly for Java, so this doesn’t improve things much for MySql. Worse, they view MySql as competitor for their existing database product. The result is while they don’t actually kill off MySql, they also don’t do any more for the product than Sun. I do want to make clear I don’t blame Oracle for causing the stagnation; only for failing to end it.

But stagnate it did… for more than 10 years, until around 2017 when development of what became MySql 8.0 finally starts to accelerate. Even then, it wouldn’t release until late 2018.

In the meantime, competing products did not sit still. Features like lateral joins, common table expressions, windowing functions, that have only recently come to MySql, were built first (and often better, at least so far) in other products. And let’s not even talk about engine improvements for performance and stability.

There was some attempt to avoid all this via the MariaDB fork, but unfortunately it was never really able to achieve enough momentum to make up for the lost time; a case of too little, too late.

The point of all this is MySql 5.7 is still rooted to the 2004 state of the platform, even though it was released in 2015. In my opinion it doesn’t really even qualify as a modern database platform.

But that’s 5.7 and earlier. Now we can push things forward to the 2018 state of the art by getting to MySql 8.0; this is at least reasonable (to contrast running 5.7 or earlier, which is not reasonable). Just know you’d still be playing catch up from about four years behind the rest of the market. Worse, the fundamental issues that caused the original stagnation are not resolved.

Today, while I understand and respect the momentum of an existing product already using MySql, it wouldn’t even be on my list to consider for new development. If FOSS were a priority, Postgresql beats MySql in pretty much every possible category; otherwise Sql Server has a lot going for it as well.

Tags:

SQL vs NoSql

April 29th, 2022 · No Comments · development, sql, Sql Server

If you’ve heard of NoSql, maybe you wonder what it is and how it compares to a traditional SQL database. Let’s take a look.

SQL databases store well-structured data using known schemas. They require rigid adherence to a set of design principles, but provide amazing power if you can learn those principles and the associated query language well. NoSql databases come in several varieties, but typically store keys and “documents” (often JSON).

Let’s look at an example. Let’s say you have a business and need to record Sales. Here’s a look at how each kind of solution might handle the problem.

An SQL database would need several tables to store the data for each Sale: one for a header, one for each line in the order, and additional tables for things like customer or product. Retrieving data means knowing about those tables and how to JOIN them, and adding new fields into those tables can be exceptionally difficult.

A NoSql database would store one document for each Sale. It might also have separate primary documents for customers and products, but would almost certainly NOT need a separate document between the header and the details. If you want to retrieve the Sale, and you know how the order number is used to create the document key, but the code is very simple from there.

But initial storage and retrieval is only one part of an application. When we move beyond simple CRUD, we find the SQL database tends to make it easier to aggregate data across many orders, especially on the fly and based on ad hoc fields. Want to know your sales total for the month? How much tax was collected? How many new customers you’ve served this year? SQL will let you write a query to find this kind of information with existing data without needing to retrieve each record from the database. NoSql would either need to retrieve every Sales record for processing or have additional application logic to update a designated “SalesAggregates” document. SQL databases also make it easier to do things like get a list of all your Sales records, or Customers. This also isn’t to say NoSql databases never have these capabilities, but it is often more natural or performant (thanks to indexing) in SQL.

Recently, developers sometimes find it easier to begin a project using a NoSql solution and then migrate to an SQL database later, as the schema has matures or stabilizes, the need for historical analysis grows, or the amount of stored data increases. However, you’ll also find plenty of projects migrate the opposite direction, or stick with NoSql (or SQL) for their entire life-cycle. I’ve also heard of hybrid solutions using NoSql for the main/initial application, but with periodic ETL (export/transform/load) jobs to shift some of the data on a schedule to an SQL database or data warehouse for later analysis and reporting.

Finally, NoSql sometimes has a perceived performance advantage. “It’s web scale!” I would discourage reading too much into this. In practice, well-built SQL databases perform very well and scale very far on similar hardware.

On a personal note and for full disclosure, my own skillset is deeply rooted in SQL. Relational databases have been a sound foundation for more than 50 years, with no signs of slowing down, which is an amazing feat in the fast-changing world of technology.

Tags:

Using Flash in the Browser post-2021

January 21st, 2021 · No Comments · IT News, networking, security, Windows

Let’s get this out of the way: Flash is bad, okay? If you have another way to do something — any way at all — you should use it.

Some of us don’t have a choice. Right now I need to support no fewer than three different embedded systems using Flash: a Wi-Fi controller, a security camera deployment, and an older HP LJ 40xx series printer. Any one of these would cost hundreds to thousands to replace, not to mention the man-hours. All three meant it was a better bet for me to find a way to extend my access to Flash for a little longer.

I found two options.

First is in a post by user crazyhammer on the Extreme Networks forums. He provides instructions on preserving the Flash client installed with Windows. And it works! Unfortunately it’s (ugh) Internet Explorer only. That got me most of the way, but it’s not my preferred option. Also, my security cameras absolutely refuse to talk to IE.

So I present you option two: my method to run Flash in a variant of Google Chrome in the Year of Our Lord 2021.

To start, you need a working copy Flash from the before-times as a donor. I can’t provide this — I don’t have the rights. Over time these will get harder to find, but for the moment they do exist. Especially if you’re in a business environment, your IT service probably has an old PC somewhere that hasn’t started up in a while. For my donor, I pulled the drive and put it in a cradle connected to another computer, to be sure Chrome’s auto-update wouldn’t nuke my Flash plugin as soon as I started the machine.

I found the Flash plugin files at the following location in the guest drive:

C:\Users\<username>\AppData\Google\Chrome\User Data\PepperFlash

Where “C:” is the guest drive letter. Copy the entire contents of this folder. One extra warning: Chrome claimed to include Flash out of the box. What actually happened was it downloaded the plugin the first time you needed it. A virgin machine might not have the Flash plugin you need. Instead, look for something that was used for a while and retired.

Now we need a version of Chrome that will accept this plugin. Thankfully, we have PortableApps to the rescue. Specifically, I used the 32-bit version of Chrome 79. I chose 32-bit because my donor plugin also came from a 32-bit machine. Most people reading this can and should choose a 64-bit version. Newer versions of Chrome probably also work, but I KNOW version 79 was okay and didn’t want to waste time testing.

One you install your preferred Portable Chrome edition, take the PepperFlash files we found earlier and put them in the Data\Profile\PepperFlash folder in your new installation.

At this point you should be good to go, with three important caveats: One, this version of Chrome will still block Flash by default; the difference it also still includes the settings to let you unblock it. Two, the Flash plug-in will likely show a banner that it’s out of date. That’s okay. The banner includes a button to let you run the content anyway. Third, both the plug-in and browser will not update or patch for any security issues; you really shouldn’t use them any longer than you need to finish retiring old systems.

Tags:

Certificate Authentication in SQL Server

November 23rd, 2020 · No Comments · development, networking, security, sql, Sql Server

I sometimes see questions asking how to do certificate authentication in SQL Server.

If you’re not aware, certificate authentication for databases works by issuing a certificate that can be trusted by the database server. The certificate might be signed by public certificate authority or by some other entity trusted by the database, and is associated with a specific user in the database. When a certificate is presented to the database, it can know both that the certificate is valid, and what user it’s dealing with.

Certificate authentication has a number of benefits over legacy passwords, including automating credential rotation, support for multiple credentials per user isolated by application or device, revocation, and more.

Contrary to what you may have heard, SQL Server does indeed have support for certificate authentication. It just looks a little different from how some other database products do it. Instead, SQL Server’s equivalent option is the Integrated Security feature.

It works like this:

When you use integrated security across machines, where the server lives on a different computer from the client, you also rely on an Active Directory domain to mediate trust between the machines. A user logs in to a client computer joined to an Active Directory domain. As part of the authentication process, a domain controller (DC) from the domain will issue an authentication token for the session.

The user then wants to login to SQL Server using integrated security, where the SQL Server installation is a member of the same domain. To do this, the token is passed to SQL Server, which in turn validates the token against a DC. Now SQL Server can trust the user is who they say they are. From there it consults it’s own user information to decide whether or what access to grant.

The integrity of this process is guaranteed using – you guessed it – certificates. Also a clock (thanks Kerberos).

The weakness here is it limits authentication to devices with Active Directory membership. However, it otherwise grants the same security benefits as more-traditional certificate authentication, as well as the additional benefit of automating certificate transport and issuance in a secure way.

Tags:

Dealing with a warped or bowed printer bed

January 8th, 2019 · No Comments · 3D Printing

Last time, I talked about a seesaw/lever effect when leveling a square bed with four posts. Today I’ll address dealing with a (slightly) warped printer bed. For simplicity sake, I will only examine warping on along one axis. You should be able to apply the principles here to examine the X and Y axis separately, to get the best possible results for your bed.

Bed warping is a common issue for square beds with four adjustable posts. It’s one reason you see so many people mount glass plates over top of their original bed. It’s a natural result of heating a fixed bed, which will cause it to expand, without also heating the carriage for the mount points. This stresses the metal plate, causing it to warp. Generally manufacturers try to address this by have loose screw mounts in the bed. The problem is the pressure from the adjustable springs tend to limit the ability of the bed to expand properly. Warping is also the result of heat spreading into the bed unevenly, where the bed will expand nearer the heating element first. Finally, some warping of the metal is just a natural result over time. At the tolerances we need for good print results, even a brand new bed is unlikely to be perfectly flat.

At this point, I need to make it clear I’m not talking about obvious bending or damage. The kind of warping I’m referring to is only as much 0.3 mm from one end of the bed to the other… but when we’re talking about 0.1 mm layer heights, that’s enough to cause serious problems for prints. Really, anything more than 0.5 mm and you may want to replace the bed entirely, or look into the glass addition I’ve already referred to. You can sometimes also deal with greater warps by getting the best level you can, and then using an automatic leveling system that can compensate for some surface variance with the software, where it knows the bed is higher or lower in certain areas and adjusts the print head height to account for it.

Here is an illustration of a print bed from the side, with some warping visible:

The warping is exaggerated. Any bed warped this much would be unusable.

Note how the bed bends up along the right side. Let’s say for a moment difference between the left edge and the right edge is 0.2 mm. Such a bed would be very difficult to level.

We can improve things some, though, without changing the bed at all. Consider the following illustration:

The same print bed as before.

This is the same print bed from the previous illustration. This time, we have loosened the knobs on the left side, and tightened the knobs along the right, so the entire bed rotated slightly clockwise. Now, instead of rising on the right side, it rises on both sides from the middle. The result is the left side and right side are on the same level, and the middle is now a little lower.

The point is the change between the left or right and where the warping is worst is likely to be much less than the 0.2 mm we were dealing with before before… likely close enough to get good results throughout the entire bed.

If you find yourself with a section of your print bed where you have trouble getting good adhesion, even if you’re sure you’ve leveled the bed, you may want to consider the bed may be warped, and an adjustment like this (in combination with re-setting your Z-stop) may be just the answer.

Tags:

Leveling a square 3D printer bed with four mounting posts

January 7th, 2019 · No Comments · 3D Printing

3D printers come in a surprising variety of shapes and models, and this extends to the print beds as well. I’ve seen round beds, for printers that have a radial print size. I’ve seen triangle beds, which are supposedly easier to level, because three points determines a plane. I’ve even seen printers without any bed at all, that work by extruding resin from a tank.

By far the most common kind of printer bed I’ve seen is square, and it almost always has for posts for level adjustment, with one post at each corner.

Top down view of a 3D printer bed

This can actually make for tricky leveling where adjustments have unintuitive consequences. As was said, it’s three points that define a plane, not four. Using four posts can put strain on a bed and cause warping. I’ve even heard of people removing an entire post, and even re-mounting the third post, in order to get better levels.

But not all of us want to do that. Here’s a way to help understand what happens when you adjust a knob on the bed. First, view the bed rotated 45 degrees:

Top down view of a 3D printer bed rotated 45 degrees
Now view the bed rotated 90 degrees away from you, so we see it from the side rather than the top. From this view, only three of the knobs are visible. The fourth knob is hidden behind the one in the middle:

3D Printer bed viewed from the side

The two knobs in the center can act as the fulcrum of a lever or seesaw, where raising or lowering the bed at one end can have the opposite result on the far end.

We can now think of the printer bed acting more like a seesaw or lever, where the two knobs in the center act as the fulcrum. It’s important to understand that a knob does not set a fixed position for that corner. Each corner is secured with a spring that increases or decreases the amount of pressure on the bed at that location relative to the fulcrum created by the two adjacent knobs. Tightening a knob to lower the bed in one corner can also have the effect of raising the bed slightly at the far corner opposite the fulcrum, because now there is more lift pressure against that spring. Because we are dealing with springs, rather than a free-moving lever, this effect is not absolute, but it does happen.

Hopefully this helps explain why you sometimes need more than one time around the bed at each corner to get a good level. And perhaps it will help you make smarter adjustments when leveling your print bed in the future.

Tags:

Indexing a CSV file with .Net

November 28th, 2018 · No Comments · .net, stackoverflow

I recently answered a Stack Overflow question, where I supplied what I believe may be a useful tool for indexing simple CSV data where you might not want to hold the entire data set in memory. I wanted to list it here, because I added the solution that supports CSV as an afterthought; the question does not indicate it uses CSV records. The question uses VB.Net, but the C# translation should be trivial. Just watch out for needing to add the “out” keyword in a few places.

Some caveats:

  • The code assumes the records exist on a single line.
  • There’s nothing at this time to skip the header row, though that is trivial to add.
  • You have to supply your own key selector function.
  • The code is untested, entered directly into the Stack Overflow answer box. It almost certainly still has several bugs.

Still, I found it interesting and fun to write. Click the link above to the Stack Overflow question to see the code.

Tags:

Tracking through Lightspeed: Complexity vs Reliability

December 18th, 2017 · No Comments · networking, non-computer, servers

If you haven’t yet seen “The Last Jedi” and don’t want part of it spoiled, you may want to give this one a pass. I’m going to focus on how a scene from the movie relates to current technology.

In the “The Last Jedi”, part of the plot revolves around trying to shut down a tracking mechanism on the main First Order ship. There’s a whole scene about how this important specially-protected “A-class” process could be reset at a single breaker. I don’t remember the exact details, but what sticks out is that such important process would have such a simple point of failure. In talking about the movie with non-technical people, bring up the scene and this always bothers them. Even programmers and networking pros are bothered by this.

Here’s the thing: I’m not. This may be the plot point that makes the most sense to me of any. Rather, my personal grudge against the plot (I liked the movie, btw) is making us sit through Finn’s whole side adventure at all if it’s going to (spoiler) come to nothing? (And, yes, I know there are several others reasons for those sequences, but it seems like it belongs in the novelization rather than the movie). But the easily breakable critical process? That I totally get.

You see, this would not be first the time an outage is caused by the very system put in place to improve or ensure High Availability. Far from it. You see, complexity and reliability are in direct opposition. Whenever you add complexity in order to improve reliability — and all high available systems do this — you need to step back and look at the new risk you’re about to introduce, and then make very sure the gains will be worth it. Most often there’s risk you haven’t even begun to identify yet.

 

Tags:

PowerShell ExpandProperty vs Property

October 2nd, 2017 · No Comments · development, Powershell

I’m only an occasional PowerShell user, and therefore it’s taking some time to develop a deeper understanding of the language. I’m gonna share something that recently clicked for me that I think isn’t well explained elsewhere: using ExpandProperty, especially in combination with understand putting values vs Objects on the pipeline.

ExpandProperty is part of the Select-Object cmdlet, and it’s used to expand properties into the PowerShell pipeline. This is still a beginner-level guide, but if you find words like cmdlet and PipeLine confusing, you might want to go do a basic PowerShell tutorial first. Select-Object accepts a number or parameters, but I would argue that Property (which is the default if no parameter name is used) and ExpandProperty are the main two, and it’s not always easy for beginners to know when to use which, or to understand why you need to do this at all.

So let’s look at some examples. I have four different scenarios to cover, and in each I’ll show how these two Parameters work for that situation. For best results, you’ll want to follow along in a PowerShell console on your own machine.

We’ll use Get-Process throughout the examples as a data source, and for shorthand and to ensure consistent results if the system state changes, I’ll save it to a variable, like this:

$p = Get-Process

Collections

First up are collections.  The Get-Process cmdlet returns objects with many properties. One of these is the “Modules” property, which happens to be a collection. Let’s look at that property:

$p | Select-Object -Property Modules

You’ll likely see a bunch of text that’s not very useful. You’ll also see a bunch of blanks, where processes that require administrator or System access were just skipped. Now run it like this:

$p | Select-Object -ExpandProperty Modules

This will likely produce some errors (again: for processes that require admin or System access), and that’s okay; we get a lot of results back, too. These results should be much more meaningful. In fact, let’s assign them to a variable:

$m = $p | Select-Object -ExpandProperty Modules

Now we can look at $m.Length. On my system, I see 2931 modules, more than you’re likely to get from any one process. So we see ExpandProcess rolled up the contents of the Modules collection from every object in the pipeline. We can also look at individual entries in $m, such $m[0], to get a feel for the kind of data we have now. And we can use $m | Get-Member to get the full list of properties and methods available on each object. Importantly, we see we’re working with real System.Diagnostics.ProcessModule objects, and not something more generic that happens to have similar properties, or a bad string representation.

Objects

Let’s try another example. In this case we’ll look at selecting a single item instead of a collection, but we’ll focus on the StartInfo property, which is itself an Object with several properties of it’s own.

$p | Select-Object -Property StartInfo

This produces a bunch of a lines on the console that all have the same “System.Diagnostics.ProcessStartinfo” text. That’s not very useful. Now let’s try ExpandProperty:

$p | Select-Object -ExpandProperty StartInfo

Wow, there’s a lot here. Let’s put this data into a variable so we can look more closely:

$info = $p | Select-Object -ExpandProperty StartInfo

Now I can check $info.Length and see a number that matches the number of processes. I can look at $info[0] and see a representation of that object. And I can look at $info | Get-Member to see the full list of items available for use this object. Most of all, Get-Member tells me I’m working with a real System.Diagnostics.StartInfo object. The entire object is there, with all of it’s fields, properties, and methods, and they are all in the pipeline.

Properties

But what if I wanted just the Id and Processname… say to eventually output to a CSV file, or to make the screen easier and quicker to read? In that case, I can do this:

$p | Select-Object -Property Id,ProcessName

This let me use just those two properties. There are some things to note here. First all, there is no ExpandProperty equivalent to show. The ExpandProperty parameter can only use one property at a time. Second, these are not System.Diagnostics.Process objects anymore, regardless of what Get-Member shows. They are a custom type with only the base methods (ToString(), etc) and the properties I asked for.

Values

Finally, let’s look at a simple property. Let’s say I have another cmdlet, and I need to give it the ProcessName as an argument. I could try this:

$p | Select-Object -Property ProcessName

but it wouldn’t work for most cmdlets. Let’s look closely at the output, especially near the top. You’ll see this:

ProcessName
-----------
[a bunch of process here]

That header means we’re still dealing with an object, just like in the previous example. This object has one property named “ProcessName”. Unfortunately, my cmdlet wanted a string value. So let’s do this instead:

$p | Select-Object -ExpandProperty ProcessName

It seems like the same data, but look again at the output at the top of the list. This time there’s no header. Now we’re dealing with simple string values, and that’s much more likely to match up with whatever cmdlet you’re using needs to see.

Conclusion

Hopefully this has been helpful. My intent was demonstrate not only the differences between Property and ExpandProperty, but also help you understand objects vs values, why that can matter, and to give you some tools to help you know which you’re dealing with.

Tags:

Supporting Outlook with G Suite

May 4th, 2017 · No Comments · IT News, networking, security

Where I’m at, we use Google Apps (G Suite) for e-mail, but still rely on Active Directory for individual accounts and use MS Office rather than Google Docs most of the time. One situation to come up in the last few years is Google no longer supports MS Outlook out of the box. If you want to use Outlook, you must first enable “Less Secure Apps”. 

I know, I know. Why would you ever use Outlook when the Gmail web client and apps are so much better? I hear you. I use the web browser for day to day work. But there are still those who prefer Outlook, and there are a few things Outlook can do that Gmail does not do, or does not do well. Mail merges are an important one.

So, I need a way to support Outlook for some of my users without seriously undermining their security. I think I may finally have a plan.

One mitigation against allowing Less Secure Apps is enabling two-factor authentication. As much as I’d like to mandate that here from a security standpoint, as a practical matter I know that’s not gonna fly with our user base, especially as our campus is in a rural area and we have a small number of students who do not have reliable cell phone service for receiving two-factor keys. What I could do, however, is offer users a trade-off.

The idea is to enforce that Less Secure Apps are disabled. However, if someone wants to use Outlook or other “less secure app”, I can move their account to a different OU within Google where two-factor authentication is enforced. This will allow the creation of an App Password that will work with Outlook. I also see this as way to encourage two-factor adoption among my user base.

Note this is not the ideal solution. Needing to manually move an account to a different OU adds friction on both the IT and user sides of the equation. It’s also not discoverable; users won’t automatically know why they can’t enable Less Secure Apps by themselves, or if they do understand it’s an IT policy they may not realize we have a work-around available. A better option would be for Google to support this automatically, and prompt the user to turn on two-factor at the time they try to enable Less Secure Apps. Sadly, this is not possible.

Also note I have not yet implemented this scheme. I have historical users who may already have one feature enabled but not the other, and I’ll need to identify and clean up these accounts. This idea also does not currently mesh well with our sync process from Active Directory. It will be a significant project to align the two.

Nevertheless, I may at last have a path forward.

Tags: