In the last post I covered some of the many reasons to migrate data from a spreadsheet to a database and now it is time to show how to do just that.  Moving data from a spreadsheet into MySQL can actually be pretty simple in many cases. Now I will not mislead you by telling you that all cases are easy but there is an easy way to make this migration.  And I highly recommend the following process when possible as it can save lots of time. More painful migrations will be covered in the future but this post is my favorite 'fast and easy' process.

This is a guide to taking data from a spreadsheet and easily moving that data into a MySQL database instance. The trick is the Import Wizard in MySQL Workbench that does a lot of the detail work for you. In a future post we will go into what you have to do when you are not …

CSV imports with MySQL Workbench, is super simple. Since CSV’s are probably the most common data interchange format, it goes without saying that importing CSV data into MySQL is a staple task for all DBA’s and Developers. Continue reading to learn how easy it is using MySQL Workbench…

Image by OpenClipart-Vectors from Pixabay

Self-Promotion:

If you enjoy the content written here, by all means, …

While you can download MySQL as a DMG package, a number of users would prefer to install it as a Docker instance. You won’t find the macOS downloads on the same web site as other downloads. You can use the following macOS download site.

After installing Docker on your macOS, you can pull a copy of the current MySQL Server with the following command:

docker pull mysql/mysql-server

You should create a mysql directory inside your ~/Documents directory with this command:

mkdir ~/Documents/mysql

Then, you should use the cd command to change into the ~/Documents/mysql directory and run this command:

pwd

It should return the following directory: …

While I thought my instructions were clear, it appears there should have been more in my examples for using the MySQL MSI. A key thing that happened is that students opted not to install:

Samples and Examples 8.0.22

Unfortunately, they may not have read the Preface of Alan Beaulieu’s Learning SQL, 3rd Edition where he explains how to manually download the files from the MySQL web site. Here are those, very clear, instructions (pg. XV) with my additions in italics for the MySQL Shell:

First, you will need to launch the mysql command-line client or the mysqlsh command-line shell, and provide a password, and then perform the following steps:

1. Go to https://dev.mysql.com/doc/index-other.html and download the files for the …

I’ve been experimenting with the mysqlsh since installing it last week. It’s been interesting. Overall, I’m totally impressed but I did find a problem with how it parses stored procedures.

First thought is always, is it my code? I checked the file by running it as a script file through MySQL Workbench. It ran perfectly in MySQL Workbench but failed repeatedly when run from the mysqlsh utility. Next step, reduce the code to a small test case, retest it, and log a bug if it is replicated. My test case in a test.sql file generates the following errors when run from the mysqlsh utility:

 MySQL  localhost:33060+ ssl  studentdb  SQL > source test.sql
Query OK, 0 rows affected (0.0003 sec)
ERROR: 1064: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the …

MySQL will be used for our online sections because the VMware instance and Docker configurations where too large to effectively download this term.

MySQL 8.0.21 Installation Steps

After you download the MySQL 8 MSI file, you will perform the following 24 steps to install MySQL on Windows 10. If you want a full developer install you must install Microsoft Excel and Visual Studio first.

1. The first thing you need to do is grant privileges to allow the MySQL Installer application to work in Windows 10. Click the Yes button to authorize the MySQL Installer to run.

1. The next thing you need to do is grant privileges to allow …

So, I admit the title for this post is a bit ambiguous. Not only is it the second post I’ve written in a short period of time, but it’s also focused on ProxySQL 2.0. As promised in the previous post, I’ve upgraded my testing environment to ProxySQL 2.0 and was interested to see if there are any differences in behaviour from the previous version. As it turns out, there are.

In the comments section of the previous post, you’ll find a comment by ProxySQL Founder and CEO, René Cannaò:

“Indeed ProxySQL doesn’t support caching_sha2_password authentication plugin, but since ProxySQL 2.0.3 (March 2019) a client connection using caching_sha2_password will be automatically switched to mysql_native_password.

With regards to “OPT_CHARSET_NAME=utf8”, the problem is that your backend it is not MySQL 8.0 …

The past couple of blog entries have been on Geographic Information Systems and Geometric Data.  Visualizing that data with MySQL Workbench makes it easier for me to see what the results really mean.

So how do you get there?

Start Workbench, create a new SQL Tab in your favorite scratch schema, and crate the table below. 

CREATE TABLE `test` (
  `id` INT NOT NULL AUTO_INCREMENT,
  `geom` GEOMETRY NULL,
  PRIMARY KEY (`id`));

Next add some data.

INSERT INTO `test` …

Tweet

The other day, I was reading a blog by Magnus Hagander about tracking foreign keys throughout a schema in PostgreSQL. I thought it was a good idea, so I decided to look at how you can track foreign key in MySQL.

The way I decided to do it was to start out with a table, then find all tables referencing the table by a foreign key. From this basic (and simple as it will be shown) query, it is possible to create a chain of relations. The key table for the queries is …

Peter Zaitsev of Percona posted a Tweet about an interactive book Select Star SQL which 'aims to be the best place on the internet for learning SQL'.  This interactive book uses a data set that can be downloaded or you could work the queries online.  I showed the website to a neighbor who was asking 'what is the best way to learn SQL'.   The neighbor wanted their own copy of the data and asked how do you turn a raw CSV file into a table.

Well, that can get complicated.
CSV
CSV files use commas to designate each field in the file.  This is a great idea until someone plants a comma in the middle of a field but it is sort of a least common denominator way of passing data between systems.

But a CSV file does not directly fit into a relational table.  You could …