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

  1. username
    • user()
    • current_user
  2. 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 functions are :-

  1. user()
  2. current_user

SQL

Sample


select
	  user() as 'user()'
	, current_user as 'current_user'

Output

userProfile.user.01.20190714.0812AM

 

Permissions

Let us quickly see what permissions we have.

There are functions for introspectively reviewing one own’s permission :-

  1. show grants
    • Show Grants ( for yourself )
    • Show Grants ( for another user )

Show Grants

Show Grants for Self

SQL
Sample

SHOW GRANTS

Output

showGrants.01.20190714.0817AM.PNG

Show Grants for Others

SQL
Sample

SHOW GRANTS for '{me}'@'%'

Output

showGrants.01.20190714.0817AM.PNG

 

DBeaver – Portable

Background

Needing to take a quick look at an embedded database and I did not have a client installed on the server and I did not have firewall rules in place to engage from my desktop.

DBeaver

DBeaver is my universal database client.

Installation

I did not feel like going through a normal install.

Portable

Sometimes I just want a lithe footprint use experience.

Artifacts

Googled for Dbeaver portable and arrived here :-

Here
here

Image

The current portable version is 6.1.2-12 and our targeted OS is MS Windows, 64-bits.

v6.1.2-12.platform.windows.x64.firefox.01.20190713.0940PM.PNG

 

Install

Ran the installer

Outline

  1. Window – Welcome
    • The initial screen does not have an actual name
    • In most installers, it is called the Welcome Screen
  2. Window – Notice of Non-Affiliation and Disclaimer
    • Acknowledges that Portapps is not associated with DBeaver
  3. Window – License Agreement
  4. Window – Select Destination Location
    • By default the destination location is the system drive (C:)
    • Please choose one of application drives ( D:, E:, Etc)
  5. Window – Ready to Install
  6. Window – Installing
  7. Window – Completing

Images

Image – Welcome

setup.01.20190713.0943PM.PNG

Image – Notice of Non-Affiliation and Disclaimer

noticeOfNonAffliation.02.20190713.0944PM.PNG

Image – License Agreement

licenseAgreement.03.20190713.0945PM.PNG

Image – Select Destination Location
Original

selectDestinationLocation.04.01.20190713.0945PM.PNG

Revised

selectDestinationLocation.04.02.20190713.0945PM.PNG

Image – Ready to Install

readyToInstall.05.20190713.0946PM.PNG

Installing

installing.06.20190713.0947PM.PNG

Completing

completing.07.01.20190713.0947PM.PNG

Use

To use, please navigate to the folder you selected during install.

Database Platform

MySQL
Outline
  1. Please choose the database platform you will be targeting
  2. DBeaver prompts you as to whether it is OK to download the Driver
    • Acquiescence
  3. Configure Data Source
    • Database Host Name
    • Database Host Port Number
    • Username
    • Password

 

Our targeted database platform is MySQL, chose that platform.

Driver
Driver – Download
mysql.driver.download.01.20190713.0950PM.PNG
Driver – Test

mysql.driver.connect.01.20190713.0951PM.PNG

Dedicated

Dedicated to Portapps, Inc. @ portapps.io

And, Dbeaver ( Serge Rider and the entire mean gang ); you guys do good work.  Actually great work.

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


set @schema := 'sakila';
set @table := 'inventory';

select 

         case

            when tblKCU.TABLE_NAME = @table then 'References'
            when REFERENCED_TABLE_NAME = @table then 'Referenced'

         end as 'isReferencingOrReferenced'
       , tblKCU.CONSTRAINT_NAME as 'constraint'
       , tblKCU.TABLE_NAME as 'table'
       , tblKCU.COLUMN_NAME as 'column'

       , tblKCU.REFERENCED_TABLE_NAME as 'refTable'
       , tblKCU.REFERENCED_COLUMN_NAME as 'refColumn'

       , tblKCU.ORDINAL_POSITION as 'colPos'

from   information_schema.KEY_COLUMN_USAGE tblKCU

where  tblKCU.TABLE_SCHEMA = @schema

/* Foreign Key Relationship */
and    (

             ( tblKCU.TABLE_NAME = @table )
          or ( tblKCU.REFERENCED_TABLE_NAME = @table )

       )

/* Skip Primary Key */
and    (

              ( tblKCU.TABLE_NAME is not null )
          and ( tblKCU.REFERENCED_TABLE_NAME is not null )

       )

order by       

         tblKCU.TABLE_NAME
       , tblKCU.COLUMN_NAME

       , tblKCU.REFERENCED_TABLE_NAME
       , tblKCU.REFERENCED_COLUMN_NAME

       , tblKCU.CONSTRAINT_NAME       

       , tblKCU.ORDINAL_POSITION

;

Output

 

Tools

DBeaver

Outline

Steps to follow to track visually through DBeaver

  1. Launch DBeaver
  2. Connect to MySQL Instance
  3. Navigate to Database Tables <Specific Table>
  4. Double Click on the selected table
  5. On the right panel, choose the “ER Diagram” Tab
  6. Review the shown tables and their relationships to each other

Image

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:


You are using safe update mode and you tried to update a table 
without a WHERE that uses a KEY column

 

Error Image

Troubleshooting

Review code and identify DML operations ( Update, Delete ) where the key column is not being filtered on.

Here are possible scenarios that will raise this error :-

  1. Delete from [table];
  2. Delete from [table] where [id] > 0;

 

Remediation

In our case we replaced


delete

from tblIndexColumn

with


truncate table tblColumn;

Workaround

SQL_SAFE_UPDATES


SET SQL_SAFE_UPDATES=0;

call listIndexColumns();

SET SQL_SAFE_UPDATES=1;

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 in version 2017, MSFT played catch up.

And, added a similar function.

String_Agg

Code


select
        [object]
            = quoteName(tblSS.[name])
              + '.'
              + quoteName(tblSO.[name])

        , [indexID]
            = tblSI.[index_id]

        , [index]
            = tblSI.[name]

        , [indexType]
            = tblSI.[type_desc]

        , [columnList]
            = 
                STRING_AGG 
                (
                      concat
                      (
                          tblSC.[name]
                        , ''
                      )	

                    , ', '
                ) 
                WITHIN GROUP 
                (
                    ORDER BY 
                        tblSIC.[key_ordinal]
                )
            

from   sys.schemas tblSS

inner join sys.objects tblSO

        on   tblSS.[schema_id] = tblSO.[schema_id]
 
inner join sys.indexes tblSI

        on   tblSO.[object_id] = tblSI.[object_id]

inner join sys.index_columns tblSIC

        on   tblSI.[object_id] = tblSIC.[object_id]
        and  tblSI.[index_id]  = tblSIC.[index_id]

inner join sys.columns tblSC

        on   tblSIC.[object_id] = tblSC.[object_id]
        and  tblSIC.column_id = tblSC.column_id

/*
    Skip MS Shipped Objects
*/
where tblSO.is_ms_shipped = 0

/*
    Exclude Included Columns
    Only Include actual Key Columns
*/
and   tblSIC.[key_ordinal] > 0

group by

                quoteName(tblSS.[name])
              + '.'
              + quoteName(tblSO.[name])

        , tblSI.[index_id]

        , tblSI.[name]

        , tblSI.[type_desc]

order by

                quoteName(tblSS.[name])
              + '.'
              + quoteName(tblSO.[name])

        , tblSI.[index_id]

        , tblSI.[name]



Output

 

Crediting

Crediting Dan M for asking the question.

And, Martin Smith for ably.

Simulating group_concat MySQL function in Microsoft SQL Server 2005?
Link

 

References

  1. Microsoft
    • String_Agg
    • sys.index_columns
  2. Stack Overflow
    • Simulating group_concat MySQL function in Microsoft SQL Server 2005?
      Link

MySQL – information_schema.statistics

Background

As a quick follow-up to our discussion of metadata on tables, let us discuss indexes.

BTW, the discussion on tables is here.

INFORMATION_SCHEMA.STATISTICS

Code


select

		tblStat.TABLE_NAME as 'table'

	  , tblStat.INDEX_NAME as 'index'

	  , case tblStat.NON_UNIQUE
			   when 0 then 'No'
			   when 1 then 'Yes'
			   else '?'
		end as 'Unique'


	  , GROUP_CONCAT( COLUMN_NAME order by SEQ_IN_INDEX ) as colList


	  , tblStat.CARDINALITY


from  INFORMATION_SCHEMA.STATISTICS tblStat


where  tblStat.TABLE_SCHEMA = 'drupal'


group by

		 tblStat.TABLE_SCHEMA
	   , tblStat.TABLE_NAME
	   , tblStat.INDEX_NAME


order by

		 tblStat.TABLE_SCHEMA
	  ,  tblStat.TABLE_NAME
	  ,  tblStat.INDEX_NAME


LIMIT 25

;


Output

 

Comparison with Other Databases

  1. GROUP_CONCAT
    • The group_concat is very, very powerful and has few equal in other database platforms
    • Function Documentation

 

SHOW INDEX

To get a bit more information, specifically cardinality ( Number of unique values in each column combination ), we can issue “show index” against the table.

Code

Syntax


show index from [table] from [database];

Sample


show index from search_index from drupal;

Output

References

  1. Reference
    • INFORMATION_SCHEMA Tables
      • INFORMATION_SCHEMA STATISTICS Table
    • Show Index
    •  Group_Function
      • Group By Function
        Link
    • Group_Concat

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


$ ps aux | grep -v "grep" | grep "mysql"

Output

Explanation

  1. User
    • mysql
  2. Process ID
    • 4400
  3. Program Name
    • /apps/mysql/sbin/mysqld
  4. Base dir
    • /apps/mysql
  5. Data dir
    • /data/mysql
  6. plugin-dir
    • /usr/lib64/mysql/plugin
  7. user
    • mysql
  8. log-error
    • /apps/mysql/log/mysql-err.log
  9. pid-file
    • /var/run/mysql/mysqld.pid
  10. socket
    • /var/run/mysql/mysql.sock
  11. port
    • 3306

sudo to account mysql is running under

syntax


sudo su – mysql

confirm

Script


whoami

Output


Where is conf file?

/apps/mysql/bin

syntax


ls -la /apps/mysql/bin/*conf*

output

/etc

syntax


ls -la /etc/my*

output

Explanation

Matches found

  1. /etc/my.cnf

 

Configuration file

/etc/my.cnf

Syntax


cat /etc/my.cnf

Image

Explanation

  1. log_bin
    • log_bin=/logs/mysql/mysql-bin.log
  2.  max_binlog_size
    • max_binlog_size=100MB
  3.  expire_logs_days
    • expire_logs_days=1
  4. binlog_format
    • binlog_format=row

/logs/mysql/mysql-bin.log

Objective

Review /logs/mysql folder for mysql-bin.###### files.

Syntax


ls -la --block-size=M /logs/mysql/mysql-bin*

Image

/logs/mysql/mysql-bin.index

Objective

Review /logs/mysql/mysql-bin.index file for a listing of binary log files.

Syntax


cat /logs/mysql/mysql-bin.index

Image