ANSI Standards – Database – Substring

Background Reviewing some SQL and wanted to point out a tiny dissimilarity between database platforms. Data Our test table is Oracle's SCOTT.EMP Here is what the data looks like :- Substring/Substr The HIREDATE is the only well formatted fixed length string. Its format is DD-MMM-YY. Query Oracle SQL Output Explanation Oracle relies on Substr to … Continue reading ANSI Standards – Database – Substring

MySQL – Basic User Information

Background Once one is connected to MySQL, it is good to review basic user information. Functions Here are some available functions for reviewing one's own information. Outline username user() current_user Review Permissions show grants Username There are a few functions that basically do the same thing. They are all return the current user's name. The … Continue reading MySQL – Basic User Information

DBeaver – Table Relationships

Background Just wanting to make sure that I am properly tracking table relationships in MySQL.   Code INFORMATION_SCHEMA INFORMATION_SCHEMA.KEY_COLUMN_USAGE Code Sample Output   Tools DBeaver Outline Steps to follow to track visually through DBeaver Launch DBeaver Connect to MySQL Instance Navigate to Database Tables <Specific Table> Double Click on the selected table On the right … Continue reading DBeaver – Table Relationships

MySQL :- Error – “You are using safe update mode and you tried to update a table without a WHERE that uses a KEY column”

Background As I tested out the code from our last blog, discovered a nice MySQL safety net. BTW, that blog is here.   Error Error Text The error reads:   Error Image Troubleshooting Review code and identify DML operations ( Update, Delete ) where the key column is not being filtered on. Here are possible … Continue reading MySQL :- Error – “You are using safe update mode and you tried to update a table without a WHERE that uses a KEY column”

Transact SQL – STRING_AGG

Background In our post on MySQL - Information_schema.statistics we spoke glowingly of the GROUP_CONCAT Function. I really could not find a way around using it in MySQL. I ran into some difficulties using it and as with problems one just googles for workarounds. BTW, the MySQL post is here. SQL Server Introduction It seems that … Continue reading Transact SQL – STRING_AGG

MySQL – Binaries Logs – Review Settings

Background A quick documentation on how to start reviewing Binary Log settings for a MySQL Instance. Review Review Account mysql is running under ps aux Code Output Explanation User mysql Process ID 4400 Program Name /apps/mysql/sbin/mysqld Base dir /apps/mysql Data dir /data/mysql plugin-dir /usr/lib64/mysql/plugin user mysql log-error /apps/mysql/log/mysql-err.log pid-file /var/run/mysql/mysqld.pid socket /var/run/mysql/mysql.sock port 3306 sudo … Continue reading MySQL – Binaries Logs – Review Settings