Showing entries 1 to 10 of 42614
10 Older Entries »
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.

So, here’s a quick supplement to what’s already there. It assumes you created an example table based on my prior blog post that looks like this:

+----+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| id | struct                                                                                                                                                                                          | …
[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]
MySQL JSON Tricks

Are they really tricks or simply basic techniques combined to create a solution. Before writing these mechanics for using native MySQL to create a compound JSON object, let me point out that the easiest way to get one is to use the MySQL Node.js library, as shown recently in my “Is SQL Programming” blog post.

Moving data from a relational model output to a JSON structure isn’t as simple as a delimited list of columns in a SQL query. Let’s look at it in stages based on the MySQL Server 12.18.2 Functions that create JSON values.

Here’s how you return single row as a JSON object, which is quite straightforward:

SELECT JSON_OBJECT('first_name',c.first_name,'last_name',c.last_name) AS json_result
FROM   contact c
WHERE …
[Read more]
JDBC Request: Cannot load JDBC driver class’com.mysql.jdbc.Driver’ solution

 

Just  quick post to document an troublesome error when running Jmeter on Mysql

JDBC Request: Cannot load JDBC driver class’com.mysql.jdbc.Driver’ solution

 

Should be pretty simple, it’s just a question of putting the mysql-connector-java jar file, available at https://dev.mysql.com/downloads/connector/j/, into the classpath for Jmeter. The easiest way for me is to put this into the jmx file:

      <stringProp name=“TestPlan.user_define_classpath”> /Users/kyle.hailey/jars/mysql-connector-java-8.0.29.jar,/home/ec2-user/jars/mysql-connector-java-8.0.29.jar</stringProp>

Notice there is a space ” ” before the first jar file.  Apparently the space  was enough to cause the error. After removing the space , jmeter connected successfully to …

[Read more]
Find All Table’s Current AUTO_INCREMENT Value

I recently wanted to check all tables’ current AUTO_INCREMENT value (if any) for a database dump structure. I wasn’t sure if a tables’ Data Definition Language (DDL) statement contained the current AUTO_INCREMENT value or not. So after I loaded the dump file, I learned of a query to use and find out…

The Newsletter for PHP and MySQL Developers

Receive a copy of my ebook, “10 MySQL Tips For Everyone”, absolutely free when you subscribe to the OpenLampTech newsletter.

Image by  …

[Read more]
MySQL at OpenExpo Madrid 2022

MySQL Community Team is Silver sponsor of OpenExpo Madrid 2022.

MySQL Performance : Benchmark kit (BMK-kit)

The following is a short HOWTO about deployment and use of Benchmark-kit (BMK-kit). The main idea of this kit is to simplify your life in running various MySQL benchmark workloads with less blood and minimal potential errors.

Generally as simple as the following :

$ bash /BMK/sb_exec/sb11-Prepare_50M_8tab-InnoDB.sh 32   # prepare data

$ for users in 1 2 4 8 16 32 64 128 256 512 1024
do   
  # run OLTP_RW for 5min each load level..
  bash /BMK/sb_exec/sb11-OLTP_RW_50M_8tab-uniform-ps-trx.sh $users 300
  sleep 15
done

Read more... (35 min remaining to read)

How to Setup High Availability and Replication inside OCI for MySQL HeatWave

High availability and replication is a must for any production environment in order to have a back-up process in place with minimum interruption in case of failure. In this article, I'll explain how to configure this architecture for MySQL HeatWave on OCI.

OpenLampTech issue #31 – Substack Repost

I could barely stuff this week’s OpenLampTech issue #31 into the email client to send out. It is that full!

The Newsletter for PHP and MySQL Developers

Receive a copy of my ebook, “10 MySQL Tips For Everyone”, absolutely free when you subscribe to the OpenLampTech newsletter.

In this week’s OpenLampTech issue #31, we are covering articles on:

  • Drupal PWAs
  • What is a WordPress Developer?
  • PHP OOP concepts and definitions
  • WordPress contact form plugin
  • 7 pieces of Linux advice
  • And much, much more.

Original and curated content. PHP, MySQL, and the LAMP stack. All in one place. Every week. …

[Read more]
A graph a day, keeps the doctor away ! – Full Table Scans

Full table scans can be problematic for performance. Certainly if the scanned tables are large. The worst case is when full table scans are involved in joins and particularly when the scanned table is not the first one (this was dramatic before MySQL 8.0 as Block Nested Loop was used) !

A full table scans means that MySQL was not able to use an index (no index or no filters using it).

Effects

When Full Table Scans happen (depending of the size of course), a lot of data gets pulled into the Buffer Pool and maybe other important data from the working set is pulled out. Most of the time that new data in the Buffer Pool might even not be required by the application, what a waste of resources !

You then understand that another side effect of Full Table Scans is the increase of I/O operations.

The most noticeable symptoms of Full Table Scans are:

  • increase of CPU usage
  • increase of …
[Read more]
Showing entries 1 to 10 of 42614
10 Older Entries »