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;

MySQL – Stored Procedure – List Index Columns

Background

Wanted to start writing Stored Procedures against MySQL Databases.

Since we started talking about Indexes in MySQL, thought that will be

familiar grounds to start from.

 

Code

listIndexColumns

 


-- Create database dbUtility if it does not exist
CREATE database IF NOT EXISTS dbUtility;

-- use database dbUtility
use dbUtility;

-- Change delimiter from default of ; to //
delimiter //


DROP PROCEDURE IF EXISTS listIndexColumns;
//


-- Create Stored Procedure listIndexColumns
-- Parameter is dbname
create procedure listIndexColumns
(
	dbname nvarchar(100) 
)
begin

    /*
		Declare local variables
    */
	declare varID integer;
    declare varIDMax integer;
    
    declare varDBName NVARCHAR(100);
    declare varDBNameCurrent NVARCHAR(100);
    
    declare varTable NVARCHAR(100);
    declare varIndex NVARCHAR(100);
    declare varColumnList NVARCHAR(4000);


	declare varSeq integer;
    declare varColumn Nvarchar(100);
    declare varColumnID integer;
    declare varColumnIDMax integer;
 
    declare varColSeparator varchar(30);
    
    /*
		Drop Temporary tables if left over from previous run
    */
	drop temporary table if exists tblIndex;

	drop temporary table if exists tblIndexColumn;

    /*
       Create temporary table tblIndex
    */
	create temporary table tblIndex
	(
		id int not null 
		   auto_increment 
		   primary key

		 , tableName varchar(255) not null
		  
		 , indexName varchar(255) not null 
	
         , columnList varchar(4000) null
         
    )
	;

    /*
       Create temporary table tblIndex Column
    */
	create temporary table tblIndexColumn
	(
		   columnID   smallint not null 

         , columnName nvarchar(100) null
         
    )
	;

    /*
		set separator
    */
	set varColSeparator := ' / ';
        
    -- get Current Database
	set varDBNameCurrent := database();
    
    /*
		If dbName is passed in, then please use it
    */
    if (
			   ( dbName is null) 
            or ( dbName = '')
	   ) then 		
        
		set varDBName = varDBNameCurrent;        

   else
   
		set varDBName = dbName;
    
    end if;
    
    /*
		Get Tables & Index in current database
    */
	insert into tblIndex
	(
		   tableName 
		 , indexName  
	)
	select distinct
				TABLE_NAME
			  , iNDEX_NAME

	from   INFORMATION_SCHEMA.STATISTICS

	where  TABLE_SCHEMA = varDBName

    order by
    
		   	TABLE_NAME
            
		  ,	case 
              when INDEX_NAME = 'PRIMARY' then ''
              else INDEX_NAME
            end  
            
		  , iNDEX_NAME
   ;


    /* Reset table counter */
	set varID := 1;
    
    /* Count number of Tables / Indexes */
    set varIDMax := ( 
                        select max(id) 
                        
                        from tblIndex
					);

    /* Reset column list */
    set varColumnList = null;

    /*
		Iterate Tables & Indexes in current database
    */
	WHILE  varID <= varIDMax DO

        /*
			Get Table and Index Name into local variable
        */
		select 
              tableName, indexName
        
        into  varTable, varIndex
        
        from  tblIndex tblI
        
        where  tblI.id = varID
        ;

        /*
			Clear Temporary table use for caching
        */
        
        /*
			delete 
			
			from   tblIndexColumn
            
			;
        
        */
        
        truncate table tblIndexColumn;
        

        /*
			Get Columns for current table / index
        */
		insert into tblIndexColumn
		(
		      columnID 
            , columnName
		)
		select 
                     tblS.SEQ_IN_INDEX
                   , tblS.COLUMN_NAME
 		 
		from   INFORMATION_SCHEMA.STATISTICS tblS

		where  TABLE_SCHEMA = varDBName

		and    TABLE_NAME   = varTable

		and    INDEX_NAME   = varIndex
		;
        

    
		/*
			Reset Column ID
        */
		set varColumnID := 1;

        /*
			Reset Column List
        */
		set varColumnList := '';

    
		/*
			Get Number of Columns for Context Table / Index
        */
		set varColumnIDMax := 
                ( 
					select max(columnID) 
                
					from tblIndexColumn
				);

        /*
			Iterate Column List
        */
		while (varColumnID <= varColumnIDMax) do
			
		
            set varColumn := null;
            
			select 
					columnName
			INTO 
					varColumn
				   
			from   tblIndexColumn tblIC

			where  tblIC.columnID = varColumnID
		
			;  		
	   
			/*
				Add Column to Column List
			*/
			set varColumnList := Concat(
											  varColumnList
                                              
                                            , varColumn
                                            
                                            , case
                                                when (varColumnID < varColumnIDMax )
                                                     then varColSeparator
                                                else ''
                                              end  
										);


			/*
				Update Column Pointer
			*/
			set varColumnID := varColumnID + 1;
			
			
        end while
        ;
        

		/*
			Save Column List
		*/
		update tblIndex
          
        set    columnList = varColumnList
          
        where  id = varID
		; 
          
		
		/*
			Go to next table/index
        */
	    SET varID := varID + 1;
  

	END WHILE;
  
  
    /*
		Get resultset
    */
    select 
    	   tblI.id

		 , tblI.tableName 
		  
		 , tblI.indexName
	
         , tblI.columnList

    from   tblIndex tblI
   
	;
    
    
    /*
		Clean up - Drop Temporary Tables
    */
    
	drop temporary table if exists tblIndex;

	drop temporary table if exists tblIndexColumn;


end;

//


 

Lab


set @database = 'sakila';

call dbUtility.listIndexColumns(@database);

 

GitHub

Availed through GitHub here:

DanielAdeniji/MySQL.listIndexColumns
Link

 

Listening

Listening to my favorite person in the world when I am trying…

Joshua Radin
No Envy, No Fear
Link

 

Lyrics

Some are reaching, few are there
Want to reign from a hero’s chair
Some are scared to fly so high
Well this is how we have to try

When your sister turns to leave
Only when she’s most in need
Take away the cause of pain
By showing her we’re all the same

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 – information_schema.tables

Background

With SQL being a lingua-franca for quering data it should not be a far stretch to note that the various governing bodies have agreed on standards on how to expose dictionary.

 

Mysql

For instance, if we want to see which tables are in a database we can launch our favorite GUI SQL Editor or just access MySQL’s Client query tool eponymous named mysql.

 

information_schema.tables

SQL

Sample


select 
       TABLE_CATALOG, TABLE_SCHEMA, TABLE_NAME
     , TABLE_TYPE, ENGINE
     , TABLE_ROWS, AVG_ROW_LENGTH 

from information_schema.tables 

where TABLE_TYPE not in ("SYSTEM VIEW")  

LIMIT 10


 

Output

Explanation

  1. Database
    • def
  2. Schema
    • audit_repository
  3. Table
  4. Table Type
    • Available Options
      • SYSTEM VIEW
      • BASE TABLE
  5. Engine
    • Available Options
      • Memory – System Tables
      • InnoDB
      • MyISAM
  6. TABLE ROWS
    • Number of records in table
  7. AVG ROW LENGTH
    • Based on column data type and used size allocation

 

Comparison with Other Databases

SQL Server

  1. Able to use double-quotes in query
    • We are able to use double-quotes (“) to delimit column values
  2. Limit N
    • In Transact SQL we use top N.
    • On the other hand, in MySQL we use LIMIT N

 

show tables

Objective

List tables and views that fits specified name format.

SQL

Syntax


show full tables from [database] like [name] where TABLE_TYPE in [BASE_TABLE | VIEW ];

Sample


show full tables from drupal like 'role%';

 

Output

 

describe

Objective

Display table’s structure.

SQL

Syntax


describe [table];

Sample


describe drupal.role;

Output

 

References

  1. MySQL
    • Show Tables
      • Show Tables Syntax
        Link
    • Show Commands
      • Extensions to SHOW Statements
        Link

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

MySQL on Linux – Checking Log files

Background

I am am On-Call this week.

As we have a System Reconfiguration slated for late hours when changes that includes system downtime are often scheduled to occur, my manager checked-in with me to see whether I was OK being assigned the Task.

I said Yes, and so here I find myself gauging my actual readiness.

 

On-Call Check Preparedness

Our team lead has written a very nice detailed document that covers each database platform( DB/2, Oracle, MySQL on Linux and SQL Server on Windows).

 

Outline

As all Databases that run on Linux have pretty much the system administrative steps, here is a condense straightforward list:

  1. Initiate SSH to Linux Host
  2. Authenticate
  3. sudo to DB Engine User
  4. Connect to DB Instance
  5. Change context to problematic database
  6. Review Error Logs
  7. Review System Metrics ( Disk / Memory / and CPU )

 

MySQL on Linux

SSH to Linux

Putty

  1. Hostname = Hostname
  2. Port = 22
  3. Connection Type :- SSH

Image

 

Authenticate

Steps

  1. Login as :- <username>
  2. Password :- <password>

Image

Authenticated

Image

 

Confirm user running mysql

Code


ps aux | grep 'sql' | grep -v 'grep'

Output

Explanation

  1. There are two mysql processes
    • /usr/bin/mysql_safe
      • user :- root
    • /usr/sbin/mysqld
      • user :- mysql
      • daemon process :- mysqld
      • basedir :- /usr
      • datadir :- /data/mysql
      • user :- myssql/err_log/mysql-err.log
      • port :- 3306
  2. What is the difference between the two processes
    • /usr/bin/mysql_safe
      • It is the bootstrap process
      • It ensures that mysqld is running and restarted if knocked down
    • /usr/sbin/mysqld
      • Actual mysql daemon process

sudo to DB Engine User

We are interested in the actual daemon and so we will target mysql.

sudo command

Syntax

sudo su - [account]

Code

sudo su - mysql

Output

Entered password

 

validate sudo user

Syntax

env | grep "USER"

Output

Connect to mysql

Code


mysql -h localhost

Output

 

Get Configuration Information – log file

Code


show global variables like '%log%'

Output

Explanation

variable_name value meaning
general_log OFF Debug Mode. Should only be used during brief troubleshooting sessions.  Logs all connections and query submissions.
general_log_file /logs/mysql/mysqld.log  Log file
log OFF  In MySQL v5.1.29, deprecated in favor of general-log
log_error  /logs/mysql/err_log/mysql-err.log Errors and Startup messages
log_output FILE This option determines the destination for general query log and slow query log output. The option value can be given as one or more of the words TABLE, FILE, or NONE. TABLE select logging to the general_log and slow_log tables in the mysql database as a destination. FILE selects logging to log files as a destination. NONE disables logging. ( Link )
log_queries_not_using_indexes OFF To include queries that do not use indexes for row lookups in the statements written to the slow query log
log_throttle_queries_not_using_indexes OFF  To include queries that do not use indexes for row lookups in the statements written to the slow query log, enable the log_queries_not_using_indexessystem variable. When such queries are logged, the slow query log may grow quickly. It is possible to put a rate limit on these queries by setting the log_throttle_queries_not_using_indexes system variable. By default, this variable is 0, which means there is no limit. Positive values impose a per-minute limit on logging of queries that do not use indexes. The first such query opens a 60-second window within which the server logs queries up to the given limit, then suppresses additional queries. If there are suppressed queries when the window ends, the server logs a summary that indicates how many there were and the aggregate time spent in them. The next 60-second window begins when the server logs the next query that does not use indexes.
( Link )
log_slow_queries OFF  Slow query log.
Deprecated in favor of slow_query_log ( 5.1.29 ).
Please see here and here.
log_warnings 2  Print out warnings such as Aborted connection… to the error log. This option is enabled (1) by default. To disable it, use –log-warnings=0. Specifying the option without a level value increments the current value by 1. Enabling this option by setting it greater than 0 is recommended, for example, if you use replication (you get more information about what is happening, such as messages about network failures and reconnections). If the value is greater than 1, aborted connections are written to the error log, and access-denied errors for new connection attempts are written.
( Link )
long_query_time 2  If a query takes longer than this many seconds, the server increments the Slow_queries status variable. If the slow query log is enabled, the query is logged to the slow query log file. This value is measured in real time, not CPU time, so a query that is under the threshold on a lightly loaded system might be above the threshold on a heavily loaded one. The minimum and default values of long_query_time are 0 and 10, respectively.
slow_query_log OFF Whether the slow query log is enabled. The value can be 0 (or OFF) to disable the log or 1 (or ON) to enable the log.

The destination for log output is controlled by the log_output system variable; if that value is NONE, no log entries are written even if the log is enabled.

slow_query_log_file /logs/mysql/slow-queries.log  File name to log Slow query

 

 

More Concise Queries

Query -01

show global variables where variable_name in 
( 
      'general_log'
    , 'general_log_file'
    , 'log_error'
    , 'log_warnings'
    , 'long_query_time'
    , 'slow_query_log'
    , 'slow_query_log_file'
);

Output -01

View Log files

mysql_err.log

tail


tail /logs/mysql/err_log/mysql-err.log

output

Explanation

  1. Not much bad going on

 

References

  1. mysql
    • Show Variables
      • Log Destinations
        Link
      • Show Variables
        Link
    • Server System Variables
      • System Server Variables
        Link
    • log Warnings
      • MySQL 5.7 Reference Manual / .. / Communication Errors and Aborted Connections // B.5.2.11 Communication Errors and Aborted Connections
        Link
    • General Query & Slow Query Log Output
      • 5.4.1 Selecting General Query and Slow Query Log Output Destinations
        Link
    • log_slow_queries
      • server-options.html#option_mysqld_log-slow-queries
        Link
    • Slow Query Log
      • MySQL 5.7 Reference Manual /Slow Query Log
        Link
  2. mySQL VS mysqld
    • How to start mysqld through mysqld_safe
      Link
  3. pontikis.net
    • How and When To Enable MySQL Logs
      Link
  4. Unix Commands
    • ps
      • nixcraft
        • Warning: bad syntax, perhaps a bogus ‘-‘? See /usr/share/doc/procps-3.2.7/FAQ
          Link
        • Quick Shell Tip: Remove grep command while grepping something using ps command
          Link
    • sudo
      • StackExchange
        • Where does sudo get the currently logged in username from?
          Link