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

SQL Server :- Error – “Cannot resolve the collation conflict between “Latin1_General_100_BIN2” and “Latin1_General_CI_AS” in the equal to operation”

Background

Error during SQL Server Upgrade; specifically upgrade from MS SQL Server 2016 SP1 to Sp2.

 

Error


2018-05-18 22:27:31.36 spid4s      Creating procedure sp_sqlagent_get_perf_counters...
2018-05-18 22:27:31.37 spid4s      Error: 468, Severity: 16, State: 9.
2018-05-18 22:27:31.37 spid4s      Cannot resolve the collation conflict between "Latin1_General_100_BIN2" and "Latin1_General_CI_AS" in the equal to operation.
2018-05-18 22:27:31.38 spid4s      Error: 912, Severity: 21, State: 2.
2018-05-18 22:27:31.38 spid4s      Script level upgrade for database 'master' failed because upgrade step 'msdb110_upgrade.sql' encountered error 200, state 7, severity 25. This is a serious error condition which might interfere with regular operation and the database will be taken offline. If the error happened during upgrade of the 'master' database, it will prevent the entire SQL Server instance from starting. Examine the previous errorlog entries for errors, take the appropriate corrective actions and re-start the database so that the script upgrade steps run to completion.
2018-05-18 22:27:31.38 spid4s      Error: 3417, Severity: 21, State: 3.
2018-05-18 22:27:31.38 spid4s      Cannot recover the master database. SQL Server is unable to run. Restore master from a full backup, repair it, or rebuild it. For more information about how to rebuild the master database, see SQL Server Books Online.
2018-05-18 22:27:31.38 spid4s      SQL Server shutdown has been initiated
2018-05-18 22:27:31.39 spid4s      SQL Trace was stopped due to server shutdown. Trace ID = '1'. This

Troubleshooting

Error Text

The key error entry reads:

Cannot resolve the collation conflict between “Latin1_General_100_BIN2” and “Latin1_General_CI_AS” in the equal to operation.

Review Collation

Compare SQL Instance Collation against System Database Collation

SQL


; with cteCollationSQLInstance
(
      [name]
    , [collation]
)
as
(
    select 
          [name] 
            = cast
                (
                    serverproperty('servername')
                    as sysname
                )

        , [collation]
            = cast
              (
                serverproperty('collation')
                   as sysname
              )

)

, cteCollationDatabaseSystem
(
      [dbid]
    , [name]
    , [collation]
)
as
(
    select 
              tblSD.[database_id]
            , tblSD.[name]
            , tblSD.collation_name

    from   sys.databases tblSD

    where  tblSD.[database_id] <= 4
)
select 
          [collationSqlInstance]	
            = cteCSI.[collation]

        , [dbid]
            = cteCDS.[dbid]

        , [database]
            = cteCDS.[name]

        , [collationDatabase]
            = cteCDS.[collation]

from   cteCollationDatabaseSystem cteCDS

cross apply cteCollationSQLInstance cteCSI

 

Output

Remediation

Collation

Revert System Databases Collation to Match SQL Instance Collation

Outline

  1. Uninstall SQL Server Service Pack
    • If change was discovered post SQL Service Pack ( SP ) install, please remove SP
  2. SQL Instance Service
    • Stop SQL Server Instance
    • Start SQL Server Instance from command line issuing change collation
    • Wait for collation to be changed
    • Stop SQL Server Instance
    • Restart SQL Server Instance through Services Applet
  3. Review Collation Settings

Steps

Uninstall SQL Server Service Pack

Using Control Panel, Programs & Features, please uninstall Service Pack

Change Collation
Script

Script – Sample


rem set SQL Server Binary Folder
set _FLD="E:\Program Files\Microsoft SQL Server\MSSQL13.MSSQLSERVER\MSSQL\Binn\"
 
rem Collation to Binary
set "_COLLATION=Latin1_General_100_BIN2"

rem Start SQL Server and pass in argument -q for new collation 
%_FLD%\sqlservr -m -T4022 -q%_COLLATION% 

Review Collation
Script – Sample


; with cteCollationSQLInstance
(
      [name]
    , [collation]
)
as
(
    select 
          [name] 
            = cast
                (
                    serverproperty('servername')
                    as sysname
                )

        , [collation]
            = cast
              (
                serverproperty('collation')
                   as sysname
              )

)

, cteCollationDatabaseSystem
(
      [dbid]
    , [name]
    , [collation]
)
as
(
    select 
              tblSD.[database_id]
            , tblSD.[name]
            , tblSD.collation_name

    from   sys.databases tblSD

    where  tblSD.[database_id] <= 4
)
select 
          [collationSqlInstance]	
            = cteCSI.[collation]

        , [dbid]
            = cteCDS.[dbid]

        , [database]
            = cteCDS.[name]

        , [collationDatabase]
            = cteCDS.[collation]

from   cteCollationDatabaseSystem cteCDS

cross apply cteCollationSQLInstance cteCSI

Output

Summary

There is so much we learnt through this exercise

  1. SQL Server Service Pack uninstall actually works
    • That it works on this occasion does not mean one should thread that road without very, very careful forethought, reason, and arguments
  2. Discovered unlisted SQL Server Error Message
    • Error 200
      • Script level upgrade for database ‘master’ failed because upgrade step ‘msdb110_upgrade.sql’ encountered error 200, state 7, severity 25. This is a serious error condition which might interfere with regular operation and the database will be taken offline. If the error happened during upgrade of the ‘master’ database, it will prevent the entire SQL Server instance from starting. Examine the previous errorlog entries for errors, take the appropriate corrective actions and re-start the database so that the script upgrade steps run to completion.
      • sys.messages
        • Reviewed sys.messages and noticed 196, 197, 198, 199, 201, 202, 203, 204
        • Noticed that 200 is jumped over
  3. SQL Server Collation
    • Instance collation can be different than system’s database; specifically master database
      • Consider possible ramification

 

Listening

Listening to Sir Elton John

Sad Song
Link

Vidocq Society

Wikipedia

Link

The Vidocq Society is a members-only crime-solving club that meets on the third Thursday of every month in Philadelphia, Pennsylvania.

The Vidocq Society is named for Eugène François Vidocq, the ground-breaking 19th century French detective who helped police by using the psychology of the criminal to solve “cold case” homicides. Vidocq was a former criminal himself, and used his knowledge of the criminal mind to look at murder from the psychological perspective of the perpetrator. At meetings, Vidocq Society Members (VSMs) listen to local law enforcement officials from around the world who bring in cold cases for review.

VSMs are forensic professionals; current and former FBI profilers, homicide investigators, scientists, psychologists, prosecutors and coroners who use their experience to provide justice for investigations that have gone cold. Members are selected by committee invitation only, pay a $100 annual fee, and commit to attend at least one meeting per year.

The Society was formed in 1990 by William Fleisher, Richard Walter, and Frank Bender. It solved its first case in 1991, clearing an innocent man of involvement in the murder of Huey Cox in Little Rock, Arkansas.

Vidocq will only consider cases that meet certain requirements: they must be unsolved deaths more than two years old, the victims cannot have been involved in criminal activity such as prostitution or drug dealing, and the case must be formally presented to them by the appropriate law enforcement agency. The Society does not charge for its services, and pays for the travel expenses of the law enforcement agents who come to present cases.

The Society was chronicled in a 2002 episode of The New Detectives entitled “Collective Justice”, and was also a plot point in the finale of the 2007–08 season of Law and Order: Special Victims Unit.

In 2010 it became the subject of a book, The Murder Room by Michael Capuzzo. In A Question of Guilt, a book in the Nancy Drew and Hardy Boys Super Mystery series, Nancy Drew and Frank and Joe Hardy, working on opposite sides of the same case, approach the Vidocq Society for help.

 

Artifacts

  1. Audio
    • YouTube
      • The Vidocq Society ( Closing Segment )
        Link
    • NPR
      • Fresh Air
        • The Vidocq Society: Solving Murders Over Lunch
          Link

Quotes

  1. Our only Client is the truth
  2. Types of Killers
    • Anger Retaliatory
    • Power Reassurance
    • Power Assertive
    • Anger Excitation
  3. Pathology
    • Further Pathology
    • Downward Growth
    • Coach

The New Detectives

RadiListing

  1. WikiVisually
    • List of The New Detectives episodes [show article only]
      Link

 

Videos

  1. Collective Justice
    • Profile
      • Season 7 – Episode 18
      • Philadelphia’s Vidocq Society, named after an 18th century French detective, is one of the world’s most unusual crime-solving organizations – a members-only club made up of forensic professionals. These prominent sleuths solve a murder case from a missing shoe and successfully prove homicide without a body.
    • Videos
      • YouTube
    • Persons
      • Scott Dunn
        • Jim Dunn ( Father )
      • Alisha Hamilton
      • Tim Smith
  2. Hidden Obsessions
    • Profile
      • Season 9, Episode 1
      • Murder convictions of James Lawson and Calvin Parker.
    • Videos
      • YouTube
  3. The New Detectives: Season 7 – Ep 13 “Drawing Conclusions”
    • Profile
      • Season 7, Episode 13
    • Videos
      • YouTube
    • Participants
      • Segment #1
        • Thomas Donahue
        • Carry Scott
        • Darlene
        • John Winslow
  4. The New Detectives: Season 7 – Ep 14 – Stranger than fiction
    • Profile
      • Season 7, Episode 14
    • Videos
      • YouTube
    • Participants
      • Segment #1
        • George
      • Segment #2
        • Michael Fletcher
        • Leanne Fletcher
        • Susan Chrzanowski ( Judge )
        • Tom Henderson ( Crime Writer )
    • Story
      • Time
        • Dial M for Misconduct
          Link
  5. The New Detectives: Season 9 – Ep 2 – Blind Trust
    • Profile
      • Season 9, Episode 2
      • Segments
        • Segment #1
          • Often, a killer will build the victim’s trust before he or she attacks. In this episode, New Mexico police search for a missing teenager who disappeared from a local skating rink. Authorities suspect the two friends she was last seen with.
    • Videos
      • YouTube
    • Participants
      • Segment #1
        • Carly Martinez
      • Segment #2
    • Locations
      • Segment
        • New Mexico
    •  Story
      • Segments
        • Segment #1
          • ABC – KVIA
            • Family spreads message 20 years after Carly Martinez’ murder
              Link
  6. The New Detectives: Season 9 – Episode 10 – Murderous Attraction
    • Profile
      • Season 9, Episode 10
      • Segments
        • Segment #1
          • The most sacred of vows are sometimes not even enough to stop people who are determined to get what they want using any means necessary. In this episode, a nurse suspects a patient’s husband of attempted murder by poisoning
        • Segment 2
          • A man stages his depressed wife’s murder as a suicide.
    • Videos
      • YouTube
  7. Crimes of Passion
    • Videos
      • YouTube
    • Story
      • Segments
        • Segment #1
          • ESPN
            • Gone to Glory
              Link
    • Participants
      • Segment
        • Segment #1
          • Marlin Banks
          • Dennis
          • Stewart
          • Jaime Myer
  8. Marked for Death
    • Videos
      • YouTube
    • Participants
      • Calvin Stallworth
  9. Trial By Fire
    • Profile
      • Season 8, Episode 15
      • At a crime scene, anything left behind or moved out of place is considered a clue. But a fire can extinguish everything in its path, challenging forensic investigators at every turn.
    • Videos
      • YouTube
    • Participants
      • Segment #1
        • Adam Tallbridge
        • Todd Carpenter
        • Josh and Nathaniel ( Lathan ) Lindell
      • Segment #2
        • Victim
          • Becky Saunders
          • Rita Talbert
        • Others
          • Michael Calpret
    • Story
      • Segment 2
        • Murderpedia
          • Edwin Bernard KAPRAT III
            Link
    • Location
      • Lacross
  10. Deadly Dealings
    • Profile
      • Season 4, Episode 7
    • Videos
      • YouTube
    • Participants
      • Segment #1
      • Segment #2
        • Victim
          • Gerald & Vera Woodman
            • Sons
              • Neil Woodman
              • Stewart Woodman
            • Company
              • Manchester Products
        • Homick
          • Steve Homick
          • Robert Homick
    • Story
      • Segment #2
        • Wikipedia
          • Murders of Gerald and Vera Woodman – Wikipedia
            Link
    • Location
      • Los Angeles, CA
  11. Misplaced Loyalty
    • Videos
      • YouTube
    • Profile
      • Season 9, Episode 5

 

Quotes

  1. Collective Justice ( Season 7 – Episode 18 )
    • The Vidocq society says “It’s only Client is Truth
  2. NPR
    • The Vidocq Society: Solving Murders Over Lunch
      • Anger Retaliatory Type
        Link
        Richard Walter: “In terms of interviewing an anger-retaliatory type, one of the things that must be understood by the detectives is, contrary to popular opinion, the perpetrator does not feel any guilt for committing the killing. When they leave the killing site, they many times have a sense of well-being and a sense of relief and a sense of charm because they’ve just had 50 pounds of emotional baggage taken off their shoulders. Police don’t expect that.”

 

Stories

  1. The Vidocq Society
    • NPR
      • The Vidocq Society: Solving Murders Over Lunch
        Link

 

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