Error: MySQL Workbench Hangs When Connecting to Database

Problem: Whenever I click on my Local Connection to get access to my MySQL Database, Workbench attempts to open it then freezes.

Solution: It’s not ideal, but I have to delete the Local Instance and create a new one. It works for me after that.

Right-click on the Local Instance and select “Delete Connection.” Then Press the “+” button next to “MySQL Connections.”

Fill in the Connection Name (any name you want) and Password (the password we created earlier during the MySQL installation). Then, test the connection using the Test Connection button at the bottom. If the connection is successful, press OK.

Node.js MySQL Tutorial

This tutorial assumes you have Node.js, MySQL, and MySQL Workbench installed

Create a new Node application

Create the package.json and install the necessary dependencies

cd project-folder
npm init
npm i express express-handlebars mysql

2. Create a new MySQL Database

Create a new Schema. I will call mine Practice

3. Set up your server file

Quickly set up your server file and initiate your MySQL Connection in order to make sure the connection is running successfully before we go any further.

const express = require("express");
const mysql = require("mysql");

const app = express();

app.use(express.urlencoded({ extended: false }));
app.use(express.json({ extended: true }));

const mysqlConnection = mysql.createConnection({
    host: "localhost",
    user: "root",
    password: "password",
    database: "Practice",
    multipleStatements: true
})

mysqlConnection.connect((err) => {
    if (!err) {
        console.log("Connection Successful");
    } else {
        console.log("Connection Failed");
        console.log(err.message);
    }
})

app.listen(3000);

Run your Node app

node index.js OR nodemon index.js

If your Terminal logs “Connection Successful”, move to Step 4

4. Move MySQL Connection to separate file

This is not required; it’s simply for organization’s sake

At the same level as the server file, make a new file. I’ll call mine connection.js

This will be the contents of the file:

const mysql = require("mysql");

const mysqlConnection = mysql.createConnection({
   host: "localhost",
   user: "root",
   password: "josh-mysql",
   database: "Practice",
   multipleStatements: true
});

mysqlConnection.connect((err) => {
   if (!err) {
      console.log("Connection Successful");
   } else {
      console.log(err.message);
   }
});

module.exports = mysqlConnection;

In the server file, import the MySQL connection from the connection.js file:

const mysqlConnection = require("./connection");

5. Create a table in the database

I’m going to open up my Practice Schema and find the Tables. Then, I’m going to right-click on Tables and create a new Table by clicking “Create Table.” Give your table a name and whatever properties you would like. Then, press Apply.

My **projects** table have properties of id and name

6. Create a route and query the database

In my server file, I’m going to handle GET requests to the root route. When a user requests the root route, I will query the MySQL database for all of the items in our projects table and send back the items in the response.

app.get("/", function(req, res){
   mysqlConnection.query("SELECT * from projects", (err, rows, fields) => {
      if (!err) {
          res.send(rows);
      } else {
         console.log(err);
      }
   })
});

Let’s test out our route. Run your server with node index.js, open up Postman and send a GET request to the root route.

Since we haven’t created any records into our projects table, this request should return an empty array.

That’s it! You’ve set up a basic Node server with a MySQL database.

What is Data Science?

Data Mining is the application of specific algorithms for extracting patterns from data

For decades, data mining was being done with Statistics. In the 1990s, when Computer Science was getting exponentially popular, people started doing Data Mining with Computer Science.

Data Mining + Computer Science = Data Science

Data Science is an interdisciplinary field that uses scientific methods, processes, algorithms and systems to extract insights from data.

Fun fact: IBM pioneered the first relational database

There are three main career specializations within the Data Science field:

Data Engineer
Data Scientist
Machine Learning Expert

The Data Engineer comes first in the process of creating value from data. It is the Data Engineer’s job to collect and store data. Then comes the Data Scientist; it is his/her job to take this data, clean it and explore/visualize it (w/ statistics, graphs, charts, etc.). After that, the Machine Learning Expert can apply intelligent algorithms to the data in order to extract insights from it.

Data Science Workflow

Data Scientist’s take a systematic approach to answering questions with data. It usually follows this pattern:

Formulate Question
- clear; scientifically testable
Gather Data
Clean Data
- remove missing, incomplete, inaccurate data
Explore & Visualize
- helps to better understand the data
Train Algorithm
Evaluate
- did the results answer our question?

Setting up MySQL on Mac

MySQL is a relational database management system. It is one of the most popular RDMS’s out there today.

It is open-source, but it’s also backed by Oracle so you can use it without any commercial license or you can purchase premium services & support from Oracle

Installing MySQL on macOS

Go to this link. You should see a page with the title MySQL Community Server. Choose whichever version you want to download; you can just download the first version in the list if you’re not sure.

Open the .dmg Installer and go through the setup screens in the modal popup. When it asks for a password for your root, choose a fairly robust password and write it down somewhere because you’ll be using it in any MySQL applications.

To check if the installation was successful, go to your computer’s System Preferences and if you see the MySQL icon in one of the rows, your installation was successful.

Double click the MySQL icon to open a modal window in which you can start or stop your MySQL Server.

Installing MySQL Workbench

MySQL Workbench is a GUI tool that lets you manage your MySQL databases much more simply.

Go to this link and select MySQL Workbench from the list of Community Downloads.

There will only be one Installer option so press Download, then choose “No thanks, just start my download” and the download will start.

Open the .dmg Installer and drag MySQL workbench into the Applications folder. That’s it, you’ve set it up!

When you open MySQL Workbench, you may see this message (“MySQL Workbench could not detect any MySQL server running. This means that MySQL is not installed or is not running”).

Since we know we installed MySQL earlier, this unwanted message is displaying because MySQL server is not running on our computer.

To start the server on your computer, go to your computer’s System Preferences, double click the MySQL icon, and you should see this page.

Press “Start MySQL Server” to start your server. Close MySQL Workbench and reopen the application. Now, you should see:

This is what we want to see. Press on this button. *If you press it and you get an error, delete the connection and create a new one.

How to Create a New Connection

Press the “+” button.

When you press on the Local Connection now, it should work.

Errors Connecting to MySQL in Node

ER_NOT_SUPPORTED_AUTH_MODE: Client does not support authentication protocol requested by server; consider upgrading MySQL client

In MySQL Workbench, open a blank Query page and execute these commands (Query > Execute (All or Selection)):

ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY 'password';
flush privileges;

This is what it will look like in Workbench:

ER_ACCESS_DENIED_ERROR: Access denied for user ‘root’@’localhost’ (using password: YES)

In the part of your Node application where you define the connection to the MySQL database, set password to an empty string:

const connection = mysql.createConnection({
    host: 'localhost',
    user: 'root',
    password: ''
});

After you re-run your server, the error will go away, but you may be left with a new error– the error below:

ER_MUST_CHANGE_PASSWORD_LOGIN: Your password has expired. To log in you must change it using a client that supports expired passwords.

Execute the following query in MYSQL Workbench (change new-password to your own new password):

ALTER USER `root`@`localhost` IDENTIFIED BY 'new-password',
       `root`@`localhost` PASSWORD EXPIRE NEVER;
flush privileges;

What is a Content Delivery Network (CDN)?

A Content Delivery Network (CDN) is a system of geographically distributed servers that accelerates internet content delivery speeds.

Websites are hosted on servers. Servers are located in specific physical locations. If a user is in a location that is far from the server, they will experience load times that are longer than that of a user that is near the server. CDNs help to remedy this geographic speed inconsistency.

The CDN consists of 2 components: the origin server and the cache server(s). The origin server is, as the name suggests, the original server onto which the website was deployed. The cache servers, or edge servers, are servers around the world on which the original server’s content is duplicated.

So how does a CDN work? When a user makes a request to a website that is hosted on a server across the world, the user will receive that website’s pages from the closest cache server instead of having to wait for the long trip to the origin server.

How Content Delivery Networks Work | Beginners Guide to CDN

CDNs are most effective in speeding the delivery of content from high traffic websites with global reach. For example, YouTube has its own CDN which makes it fast for everyone all over the world. Netflix uses an algorithm to push its most viewed shows across its CDN; that’s why more popular shows load faster on the platform.

Connecting to MongoDB Atlas with Mongoose

In MongoDB Atlas on your Cluster page, click Connect > Connect your application > Copy the connection string

Create config folder. Inside, create default.json. With the config package, we can create global values that we can use throughout our application

In default.json:

{
   "mongoURI": "mongodb+srv://username:<password>@devconnector.cc6yr.mongodb.net/<dbname>?retryWrites=true&w=majority"
}

We use curly braces because this is json formatting

Replace <password> with your password. Replace <dbname> with your database’s name.

We could put our connection logic in the server file, but in order to not clutter it up, we can put it in a separate file within the config folder

We’ll call the file db.js (you can call it whatever you want) and this is what goes inside of it:

const mongoose = require('mongoose');
const config = require('config');
const db = config.get('mongoURI');

const connectDB = async () => {
    try {
        await mongoose.connect(db, { useNewUrlParser: true });
        console.log("MongoDB connected...");
    } catch(err) {
        console.log(err.message);
        process.exit(1);
    }
}

module.exports = connectDB;

We add { useNewUrlParser: true } to our connect function in order to get rid of this error:

“(node:58034) DeprecationWarning: current URL string parser is deprecated, and will be removed in a future version. To use the new parser, pass option { useNewUrlParser: true } to MongoClient.connect.”

Then, in our server file we can just import and call the connectDB() function:

const express = require("express");
const connectDB = require('./config/db');

const app = express();

connectDB();

app.get('/', (req, res) => res.send('API Running'));

const PORT = process.env.PORT || 5000;

app.listen(PORT, () => console.log(`Server started on port ${PORT}`));

About Github Pages

Github Pages is a free solution for deploying your websites to the Internet.

There are three types of GitHub Pages sites:

Project
User
Organization

Project sites are connected to an individual project hosted on GitHub. You can create an unlimited number of project sites. User and organization sites are connected to a specific GitHub account. You only get one user or organization site per Github account.

GitHub Pages source repositories have a recommended limit of 1GB.
GitHub Pages sites have a soft bandwidth limit of 100GB per month.
GitHub Pages sites have a soft limit of 10 builds per hour.

Github Pages publishes any static files that you push to your repository.

Create your own static files OR use a static site generator

You can create a site for yourself or your organization by creating a repository with Github Pages URL as its name and adding web content to its master branch

Benefits of Github Pages

Free!!
Can use custom domain
HTTPS support
Ingrained version control in your site

Reasons not to Use Github Pages

You probably think that Github Pages seems perfect, right? Well, it is… for certain types of websites. For small static websites, such as a portfolio website, Github Pages is a perfectly suitable option, but for more involved web applications with complex server implementation, Github Pages is rendered inadequate.

GitHub Pages sites shouldn’t be used for sensitive transactions like sending passwords or credit card numbers.

GitHub Pages is not intended for or allowed to be used as a free web hosting service to run your online business, e-commerce site, or any other website that is primarily directed at either facilitating commercial transactions or providing commercial software as a service (SaaS).

GitHub Pages does not support server-side languages such as PHP, Ruby, or Python.

Netlify

Netlify is a hosting platform that allows you to deploy static websites and front end applications (React, Angular, Vue). The free account offers a lot, but you can also get a pro account.

Netlify makes it simple to get set up and start deploying stuff by allowing you to login in through and deploy repositories from your Github, Gitlab, or Bitbucket account.

Deploying From the Browser

First, login to Netlify with one of these three version control platforms. Then, choose “New Site from Git” and on the next page choose your platform again. It will show you all of your repositories from your account. Choose the branch you want to deploy from (I recommend creating a separate deployment branch so you can still push to your master branch without it affecting your website).

Netlify CLI

Netlify also offers a CLI (command-line interface) tool that you can use to make deployments, etc. as opposed to using their simple online platform.

First, cd into your project directory and type this command: sudo npm i -g netlify-cli. Then, type netlify deploy. In your browser, Netlify CLI will ask for permission to authorize Netlify on your behalf; choose to authorize this. Then, close the window and return back to your Terminal. Answer the prompts on whether it’s a new site and which path to deploy from and you’re done! Netlify will generate a default domain for you to view your site on the Web.

Deploying a React App

We will be deploying the React App from Terminal. Make sure you already have the Netlify CLI installed globally on your system.

First, if you haven’t done so already, you want to build out your static assets using this command in your project directory: npm run build. This will put everything into a build folder. So, now if you ls your directory, you will see a folder called build.

Now you want to run the netlify deploy command (assuming you’ve already installed the CLI). Choose the build folder we created above as the path to deploy. That’s it! You’re site is deployed.

Using a Custom Domain

On the Netlify platform, choose the project which you’d like to give a custom domain. Then, press the “Domain Settings” button. Then press “Add Custom Domain.” Type in the custom domain name you’d like to use (after you’ve already boughten it, of course) and press “Add Domain.” In the Domain Registrar you use, go do any additional configuration / setup you need.

Netlify Forms

Netlify has a built-in form service called Netlify Forms that allows you to manage forms and submissions without any server-side code or JavaScript.

It’s extremely easy to implement this service in your HTML form. Just add the netlify attribute to any form and everything gets wired up automatically. Their bots find your forms by inspecting the HTML of your site before its published.

<form name="contact" netlify>
   <label>Name <input type="text" name="name" /></label>
   <button type="submit">Submit</button>
</form>

What is a Dynamic Website?

How is a dynamic page rendered? When the user requests a site, the server queries one or more databases for content, then passes result to templating engine which formats everything into an HTML file for user (dynamically rendered)

With each request from the end user, the HTML code the user gets back from the server is not always the same. The HTML markup you get back adjusts dynamically on the server

Dynamic: things change on the server and new requests can yield a different file
Static: the files never change, they sit on the server and you always get the same exact version

The server returns a dynamically generated HTML page. That page is not necessarily always the same because the HTML code and even the JS code can be generated on the server

The server is not involved in page rendering after serving the HTML/CSS/JS files. Although, page content can still be changed via JS in the browser)

The server side is not doing anything on the loaded page once it’s in the browser. Dynamic rendering is really about that first render when you send the request and get back a page

Dynamic does NOT mean that there’s no HTML page being served— it’s just built dynamically for each request

Finished page is served but needs to be generated first
Better for SEO because all the page’s content is available on first load
Users don’t have to wait for the data after page loads, but they do have to wait for page to load
Security tends to be easy to implement because it’s so common for dynamic sites
Requires a hosting service which supports the chosen server-side language (and version). Dynamic hosts tend to be more complex to setup and expensive

AWS EC2/ Elastic Beanstalk, Heroku