Showing entries 1 to 10 of 122
10 Older Entries »
Displaying posts with tag: MySQL 8 (reset)
Updating SQL_MODE

This is an update for MySQL 8 Stored PSM to add the ONLY_FULL_GROUP_BY mode to the global SQL_MODE variable when it’s not set during a session. Here’s the code:

/* Drop procedure conditionally on whether it exists already. */
DROP PROCEDURE IF EXISTS set_full_group_by;

/* Reset delimter to allow semicolons to terminate statements. */
DELIMITER $$

/* Create a procedure to verify and set connection parameter. */
CREATE PROCEDURE set_full_group_by()
  LANGUAGE SQL
  NOT DETERMINISTIC
  SQL SECURITY DEFINER
  COMMENT 'Set connection parameter when not set.'
BEGIN

  /* Check whether full group by is set in the connection and
     if unset, set it in the scope of the connection. */
  IF EXISTS
    (SELECT TRUE
     WHERE NOT REGEXP_LIKE(@@SESSION.SQL_MODE,'ONLY_FULL_GROUP_BY'))
  THEN
    SET @@GLOBAL.SQL_MODE := CONCAT(@@SESSION.sql_mode,',ONLY_FULL_GROUP_BY');
  END IF;
END;
$$

/* Reset the default delimiter. */
DELIMITER ;

You can call the …

[Read more]
MySQL PNG Files

LAMP (Linux, Apache, MySQL, Perl/PHP/Python) Architecture is very flexible. All the components can be positioned on the same server or different servers. The servers are divided into two types. The types are known as the Application or database tiers. Generally, the application tier holds the Apache Server, any Apache Modules, and local copies of Server Side Includes (SSI) programs.

In many development environments, you also deploy the client to the same machine. This means a single machine runs the database server, the application server, and the browser. The lab for this section assumes these configurations.

Before you test an installation, you should make sure that you’ve started the database and Apache server. In an Oracle LAMP configuration (known as an OLAP – Oracle, Linux, Apache, Perl/PHP/Python), you must start both the Oracle Listener and …

[Read more]
AlmaLinux+Java+MySQL

AlmaLinux generally has Java installed. You can check whether java is installed with this command:

which -a java

It should return:

/usr/bin/java

Then, you can check the Java version with this command:

java -version

For AlmaLinux 9, it should return:

openjdk version "11.0.17" 2022-10-18 LTS
OpenJDK Runtime Environment (Red_Hat-11.0.17.0.8-2.el9_0) (build 11.0.17+8-LTS)
OpenJDK 64-Bit Server VM (Red_Hat-11.0.17.0.8-2.el9_0) (build 11.0.17+8-LTS, mixed mode, sharing)

Next, you check whether javac is installed. You can use the which command to determine whether it is installed. Generally, its not installed and you use this command to

sudo dnf search jdk | egrep -- '-17'

It should return:

Last metadata expiration check: 0:11:17 ago on Mon 19 Dec 2022 11:32:48 PM EST.
java-17-openjdk.x86_64 : OpenJDK 17 Runtime Environment
java-17-openjdk-demo.x86_64 …
[Read more]
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]
AlmaLinux LAMP


After installing and configuring MySQL 8.0.30, I installed the Apache Web Server, PHP and the MySQLi packages. Here are the step-by-step instructions after installing and configuring the MySQL Server and provisioning a student user and the sakila and studentdb databases (blog for those steps). After installing the major components, I completed the HTTPS configuration steps for Apache 2.

The installation steps are:

  1. Install the Apache packages as the sudoer user with this command:
    sudo dnf install -y httpd
    
  2. Enable Apache as the sudoer user with this command:
    chkconfig httpd on
    

    This returns the following completion message:

    Note: Forwarding request to 'systemctl enable httpd.service'.
    Created symlink …
[Read more]
AlmaLinux MySQL+Python

After installing and configuring MySQL 8.0.30, I installed the Python connector. During that process on AlmaLinux, there were several changes since I last installed the Python’s mysql module. Here are the step-by-step instructions after installing and configuring MySQL Server (blog for those steps).

Using the MySQL Connector/Python X DevAPI Reference, you must install the pip utility before you install the library. You install the pip library as a sudoer user with the following command:

sudo yum install -y pip

Then, using the pip utility as a sudoer user install the mysql-connector-python module with the following command:

sudo pip install mysql-connector-python

Please note that this …

[Read more]
MySQL on AlmaLinux

After installing AlmaLinux in a VMware VM on my MacBook Pro (Intel Chip), and updating the packages with the following command:

sudo dnf upgrade --refresh -y

MySQL was first on my installation and configuration list. Here are the commands to install and configure it on AlmaLinux.

Install the MySQL Server packages and dependents:

sudo dnf install mysql mysql-server -y

Install the MySQL service utilities with the initscripts package, using the following command:

sudo yum install -y initscripts

Start the MySQL daemon with the following command:

sudo service mysqld start

Connect and verify the root user can connect to the database. At this point, you’ve not set the root user’s password and should use the following syntax:

mysql -uroot

It should connect and display:

Welcome to the MySQL monitor.  Commands end with ; or \g.
Your …
[Read more]
MySQL Workbench Keys

As I teach students how to create tables in MySQL Workbench, it’s always important to review the meaning of the checkbox keys. Then, I need to remind them that every table requires a natural key from our prior discussion on normalization. I explain that a natural key is a compound candidate key (made up of two or more column values), and that it naturally defines uniqueness for each row in a table.

Then, we discuss surrogate keys, which are typically ID column keys. I explain that surrogate keys are driven by sequences in the database. While a number of databases disclose the name of sequences, MySQL treats the sequence as an attribute of the table. In Object-Oriented Analysis and Design (OOAD), that makes the sequence a member of the table by composition rather than aggregation. Surrogate keys are also unique in the table but should never be used to determine uniqueness like the natural key. Surrogate keys are also candidate keys, like a …

[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]
Showing entries 1 to 10 of 122
10 Older Entries »