Showing entries 1 to 10 of 98
10 Older Entries »
Displaying posts with tag: MySQL Developer (reset)
Parametric Queries

In 2021, I wrote a MySQL example for my class on the usefulness of Common Table Expressions (CTEs). When discussing the original post, I would comment on how you could extend the last example to build a parametric reporting table.

Somebody finally asked for a concrete example. So, this explains how to build a sample MySQL parametric query by leveraging a filter cross join and tests the parameter use with a Python script.

You can build this in any database you prefer but I used a studentdb database with the sakila sample database installed. I’ve granted privileges to both databases to the student user. The following SQL is required for the example:

-- Conditionally drop the levels table.
DROP TABLE IF EXISTS levels;
 
-- Create the levels list.
CREATE TABLE levels
( level_id       int unsigned primary key auto_increment
, …
[Read more]
Ruby+MySQL on Ubuntu

This post goes through installing and configuring Ruby and Ruby on Rails for MySQL. The first step requires updating the Ubuntu OS:

sudo apt-get update

Interestingly, I found that the man-db service had inadvertently stopped. It raised the following error:

E: dpkg was interrupted, you must manually run 'sudo dpkg --configure -a' to correct the problem. 

You run this command to find the problem with the dpkg utility:

sudo dpkg --configure -a

It returned:

Setting up man-db (2.10.2-1) ...
Updating database of manual pages ...
man-db.service is a disabled or a static unit not running, not starting it.

The following command started the man-db service:

sudo systemctl start man-db.service

Next, you install the prerequisite packages with this command:

sudo apt-get install -y git-core zlib1g-dev build-essential libssl-dev libreadline-dev libyaml-dev …
[Read more]
SQL 1 v.s. SQL 3

In Alan Beaulieu’s wonderful book Learning SQL: Generate, Manipulate, and Retrieve Data, he uses a SQL 1 to demonstrates a CROSS JOIN in MySQL counting from 1 to 100. My students always find it difficult to read because the subqueries take so much space it makes it difficult to see the process, for example he gives this SQL 1 solution:

SELECT ones.x + tens.x + 1 AS counter
FROM
 (SELECT 0 AS x UNION ALL
  SELECT 1 AS x UNION ALL
  SELECT 2 AS x UNION ALL
  SELECT 3 AS x UNION ALL
  SELECT 4 AS x UNION ALL
  SELECT 5 AS x UNION ALL
  SELECT 6 AS x UNION ALL
  SELECT 7 AS x UNION ALL
  SELECT 8 AS x UNION ALL
  SELECT 9 AS x ) ones CROSS JOIN
 (SELECT 0 AS x UNION ALL
  SELECT 10 AS x UNION ALL
  SELECT 20 AS x UNION ALL
  SELECT 30 AS x UNION ALL
  SELECT 40 AS x UNION ALL
  SELECT 50 AS x UNION ALL
  SELECT 60 AS x UNION ALL
  SELECT 70 AS x UNION ALL
  SELECT 80 AS x UNION ALL
  SELECT 90 AS x ) tens
ORDER BY counter;

While anybody with a …

[Read more]
MySQL on Ubuntu

Working with my students to create an Ubuntu virtual environment for Python development with the MySQL database. After completing the general provisioning covered in this older post, I’d recommend you create a python symbolic link before installing the MySQL-Python driver.

sudo ln -s /usr/bin/python3 /usr/bin/python

You install the Python development driver with the following:

sudo apt-get -y install python3-mysql.connector

Create a python_connect.py file to test your Python deployment’s ability to connect to the MySQL database:

#!/usr/bin/python

# Import the library.
import mysql.connector
from mysql.connector import errorcode
 
try:
  # Open connection.
  cnx = mysql.connector.connect(user='student', password='student',
                                host='localhost', …
[Read more]
MySQL Workbench Fits

My students wanted an illustration of where MySQL Workbench fits within the MySQL database. So, I overlaid it in this drawing from my old book on comparative SQL syntax for Oracle and MySQL. Anybody else have a cool alternative illustration?

The idea is the lightening bolt transmits an interactive call and reading a script file submits a call paradigm.

As always, I hope this helps those looking to understand things.

MySQL Posts Summary

Here’s a quick catalog for my students of PowerShell, JavaScript, and Python examples connecting to MySQL:

[Read more]
MySQL @SQL_MODE

Installing MySQL Workbench 8 on Windows, we discovered that the default configuration no longer sets ONLY_FULL_GROUP_BY as part of the default SQL_MODE parameter value. While I’ve written a stored function to set the SQL_MODE parameter value for a session, some students didn’t understand that such a call is only valid in the scope of a connection to the database server. They felt the function didn’t work because they didn’t understand the difference between connecting to the MySQL CLI and clicking the lightening bolt in MySQL Workbench.

So, here are the instructions to reset the default SQL_MODE parameter value for Windows. You need to edit the setting in the my.ini file, which is in the C:\ProgramData\MySQL\MySQL Server 8.0 directory. The default installation will have the following:

# Set the SQL mode to strict
sql-mode="STRICT_TRANS_TABLES,NO_ENGINE_SUBSTITUTION"

You need to change it to the following in an …

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