Showing entries 1 to 10 of 168
10 Older Entries »
Displaying posts with tag: DBA (reset)
Improving Query Performance with Multi-Valued Indexing in MySQL 8.0

Learn how Multi-Valued Indexing in MySQL 8.0 can enhance query performance by efficiently indexing and querying JSON arrays. Discover the benefits, implementation steps, and considerations for optimizing your MySQL database.

  1. Multi-Valued Indexing in MySQL 8.0
  2. Understanding Multi-Valued Indexes and their benefits
  3. Creating Multi-Valued Indexes in MySQL …
[Read more]
Mastering MySQL Group Replication Primary Promotion Techniques

Table of contents:

  1. Introduction
  2. Common reasons for switching the primary node
  3. Primary Promotion and its importance
  4. Methods for switching the primary node
[Read more]
MySQL Interview – ChatGPT is not your DBA

Given the recent surge of interest in ChatGPT and the AI capabilities it offers, I decided to conduct an interview with it to assess its suitability for a MySQL Consultant…

The post MySQL Interview – ChatGPT is not your DBA first appeared on Change Is Inevitable.

AlmaLinux MySQL Workbench

AlmaLinux doesn’t natively support MySQL Workbench but these notes will help you install it. The great news is that MySQL Workbench works perfectly once you’ve installed all the dependent libraries. It’ll look like the following:

Disclaimer of sorts:

AlmaLinux is an open-source, community-driven project that intends to fill the gap left by the demise of the CentOS stable release. AlmaLinux is a 1:1 binary compatible fork of RHEL® 9 and it is built by the AlmaLinux OS Foundation as a standalone, completely free OS. The AlmaLinux OS Foundation will support future RHEL® releases by updating AlmaLinux. Ongoing development efforts are governed by the members of the community.

You can download MySQL Workbench from the following website:

https://dev.mysql.com/downloads/workbench

When you open this page, select the Red Hat Enterprise Linux 9 / Oracle Linux 9 (x86, 64-bit), RPM Package from the dropdown …

[Read more]
AlmaLinux MySQL+Perl

A quick primer on Perl programs connecting to the MySQL database. It’s another set of coding examples for the AlmaLinux instance that I’m building for students. This one demonstrates basic Perl programs, connecting to MySQL, returning data sets by reference and position, dynamic queries, and input parameters to dynamic queries.

  1. Naturally, a hello.pl is a great place to start:
    #!/usr/bin/perl
    
    # Hello World program.
    print "Hello World!\n";
    

    After setting the permissions to -rwxr-xr-x. with this command:

    chmod 755 hello.pl
    

    You call it like this from the Command-Line Interface (CLI):

    ./hello.pl
    

    It prints:

    Hello World!
    
  2. Next, a connect.pl program lets us test the Perl::DBI connection to the MySQL database.
    #!/usr/bin/perl
    
    # Import libraries.
    use strict;
    use warnings;
    use v5.10;     # for …
[Read more]
MySQL Query from JSON

One of my students asked how you could get JSON data out in tabular format. I said they should look at Øystein Grøvlen’s JSON_TABLE – Best of Both Worlds blog post from 2018. Unfortunately, the student wanted another example with the Video Store model that we use in class.

For clarity, all path definitions start with a $ followed by other selectors:

  • A period followed by a name, such as $.website
  • [N] where N is the position in a zero-indexed array
  • The .[*] wildcard evaluates all members of an object
  • The [*] wildcard evaluates all members of an array
  • The prefix and suffix wildcard, **, evaluates to all paths that begin with the named prefix and end with the named suffix

So, here’s a quick supplement to what’s already there. It assumes you created an …

[Read more]
MySQL Backslashes

Yesterday, I wrote a blog post that showed you how to write a query returning a JSON structure for a 1:many relationship. The relationship was between the member and contact table. It returns one account_number from the member table and a list of first_name and last_name columns from the contact table in a JSON structure.

One of my students asked why I choose to strip the backslashes with Python, and my reply was the SQL was already complex for most blog readers. The student asked but how would you do it in SQL. OK, that’s a fair question for two reasons. First, you don’t need to do in your local programs because it’ll run faster on the server. Second, if you strip the backslashes you can insert it into a standard JSON column. This blog post will show you how to do both.

You would use three REGEXP_REPLACE function calls, like:

[Read more]
Is SQL Programming


Is SQL, or Structured Query Language, a programming language? That’s a great question! A question that many answer with emphasis: “No, SQL is not a programming language!” There are some who answer yes; and they usually qualify that answer with something like: “SQL is a programming language designed to communicate with relational databases.”

It strikes me that those saying “yes” are saying that SQL is only a collection of interface methods to read from and write to a database engine. Those saying SQL is not a programming language often qualify that a programming language must have conditional logic and iterative structures, which don’t exist in SQL.

There’s a third group that are fence sitters. They decline to say whether SQL is a programming language, but they also say individuals who only write SQL aren’t programmers. That’s a bit harsh from my perspective.

Before …

[Read more]
Node.js MySQL Error

While I blogged about how to setup Node.js and MySQL almost two years ago, it was interesting when a student ran into a problem. The student said they’d configured the environment but were unable to use Node.js to access MySQL.

The error is caused by this import statement:

const mysql = require('mysql') 

The student got the following error, which simply says that they hadn’t installed the Node.js package for MySQL driver.

internal/modules/cjs/loader.js:638
    throw err;
    ^

Error: Cannot find module 'mysql'
    at Function.Module._resolveFilename (internal/modules/cjs/loader.js:636:15)
    at Function.Module._load (internal/modules/cjs/loader.js:562:25)
    at Module.require (internal/modules/cjs/loader.js:692:17)
    at require (internal/modules/cjs/helpers.js:25:18)
    at Object. (/home/student/Data/cit325/oracle-s/lib/Oracle12cPLSQLCode/Introduction/query.js:4:15)
    at Module._compile …
[Read more]
MySQL Partitioned Tables

MySQL Partitioned Tables

Learning Outcomes

  • Learn about List Partitioning.
  • Learn about Range Partitioning.
  • Learn about Columns Partitioning.
  • Learn about Hash Partitioning.
  • Learn about Key Partitioning.
  • Learn about Subpartitioning.

Lesson Material

MySQL supports partitioning of tables. It supports range, list, hash, and key partitioning. Range partitioning lets you partition based on column values that fall within given ranges. List partitioning lets you partition based on columns matching one of a set of discrete values. Hash partitioning lets you partition based on the return value from a user-defined expression (the result from a stored SQL/PSM function). Key partitioning performs like hash partitioning, but it lets a user select one or more columns from the …

[Read more]
Showing entries 1 to 10 of 168
10 Older Entries »