MySQL:- Temporary Tables

Background Was reading through some documentation and it touched on temporary tables in MySQL. Sample Here is a sample code. Outline Create database\schema if it does not exist Begin new transaction using start transaction Create temporary table ( accountTransaction ) create temporary table ( accountBalance ) Add data into temporary table ( accountTransaction ) Summary … Continue reading MySQL:- Temporary Tables

MySQL:- Functions

Background Wanted to cover a couple of MySQL System Functions.   System Functions   Category Use Function Name Syntax Sample Data Date and Time Functions Current Date CurDate curdate() 2020-08-24 current_date current_date() 2020-08-24 Current Time CurTime curtime() 09:27:01 current_time current_time() 09:27:01 Day Function Day of the Month day day(curdate()) 24 Day Name dayname dayname(curdate()) Monday … Continue reading MySQL:- Functions

MySQL – Temporary Table & Common Table Expression ( CTE ) – Error – “Can’t reopen table” ( Error 1137 )

Background There is a small error that one can run into when using Temporary Tables and Common Table Expression ( CTE ). Error Error - Can't reopen table Error Image Error Text Scenario Common Table Expression SQL Result   Temporary Table & Common Table Expression Query:- Successful SQL Result Query - Failed SQL Output Output … Continue reading MySQL – Temporary Table & Common Table Expression ( CTE ) – Error – “Can’t reopen table” ( Error 1137 )

MySQL – Workbench – Error – “You are using safe update mode and you tried to update a table without a WHERE that uses a KEY column.” ( Error Code: 1175 )

Background Using MySQL's Workbench to run a simple query. But, ran into an easy to remediate error. Query SQL Error Error Image Error Text Remediate Outline SQL Use SQL_SAFE_UPDATE Set SQL_SAFE_UPDATE off       SQL Statements SET SQL_SAFE_UPDATES on GUI SQL Workbench SQL Use SQL_SAFE_UPDATE Outline Use SQL_SAFE_UPDATE Set SQL_SAFE_UPDATE off       … Continue reading MySQL – Workbench – Error – “You are using safe update mode and you tried to update a table without a WHERE that uses a KEY column.” ( Error Code: 1175 )

MySQL – Insert/Ignore

Background In our last post, we touched on a duplicate key error. Here is another quick workaround around the same issue. Lineage MySQL – Insert/On Duplicate Key Update Link Error Error Message Text Image Entity Diagram Table Definition SQL Code Offending Code SQL Clean up Code Outline Add Ignore clause to insert statement SQL Output … Continue reading MySQL – Insert/Ignore

MySQL – Connectivity – Error – “Unable to load authentication plugin ‘caching_sha2_password’. “

Background Another error encountered while trying to connect to a MySQL instance. Error Error Text Unable to load authentication plugin 'caching_sha2_password'. Error Image Troubleshooting Outline Review Installation Configuration Options Options Authentication Method Use Strong Password Encryption for Authentication Our Choice Use Legacy Authentication Method Review Available MySQL Driver Versions Generic Review the versions of MySQL … Continue reading MySQL – Connectivity – Error – “Unable to load authentication plugin ‘caching_sha2_password’. “

MySQL – Connectivity – Error – “Public Key Retrieval is not allowed”

Background Trying to connect to a MySQL instance, but ran into an error. Error Error Text Public Key Retrieval is not allowed Error Image Remediation Outline General Set Attribute Set attribute AllowPublicKeyRetrieval to true DBeaver Window - Connect to Database Connection Settings Tab - "Driver Properties" Toggle Property "AllowPublicKeyRetrieval" Change from false to true Images … Continue reading MySQL – Connectivity – Error – “Public Key Retrieval is not allowed”

MySQL – Sample – Sakila – Adding new customer record

Background Wanted to see how hard it will be to play around with the sakila MySQL Database. Objective Our objective will be to add a couple of records to the sakila.customer table. Because the table has a couple of foreign keys, we need to get a handle to valid entries in those tables, as well. … Continue reading MySQL – Sample – Sakila – Adding new customer record

Hadoop/Cloudera [CDH]/Hive v2 – Installation

Pre-requisites There are quite a few pre-requisites that should be met prior to installing and running Hive. They include: Have in a place a base Hadoop install (HDFS, Map Reduce v1 or v2).  A working demo is available @https://danieladeniji.wordpress.com/2013/05/12/hadoopcloudera-v4-2-1-installation-on-centos/ Having one of supported databases installed.  This database is needed by the metastore.  A mySQL implementation is … Continue reading Hadoop/Cloudera [CDH]/Hive v2 – Installation