Find & Replace Partial Strings

I recently had a request to remove part of a text field from MySQL, JSON data but stored as text in MySQL 5.6.

Has this been Mongo, this could have been a pretty quick update of the data within an array:

1. Find the data
   

   db.accounts.find({ “vendor.account_number”: { $exists: true }}, {“vendor”:1,”timestamp”:1}).forEach(printjson)

2. Update the data
   

   db.accounts.update(
   { “vendor.account_number”: { $exists: true }},
   { $set:{“vendor.name”: “XXXXXX”,”vendor.account_number”: “XXXXXX”}}, {multi:true}
   )

3. 

On  MySQL, this was a bit trickier. I was able to pull out the segment to be updated using SUBSTRING.  Once I was able to identify the SUBSTRING values, I used REPLACE with SUBSTRING_INDEX for the substitution “from” values.

• SUBSTRING_INDEX Syntax: SUBSTRING_INDEX(str,delim,count)
  • My data had variable length fields, but were consistent on the prefix for the data, and the character immediately at the end of the section:
  • SUBSTRING_INDEX(request, ‘”vendor”:{“name”:”‘, -1)
    • str = request (my column)
    • delim = “vendor”:{“name”:”‘  — name was potentially used in more locations, so the longer string
    • count = -1

      • “If count is positive, everything to the left of the final delimiter (counting from the left) is returned. If count is negative, everything to the right of the final delimiter (counting from the right) is returned”

• REPLACE Syntax: REPLACE(str,from_str,to_str)
  • Since my data varied on each record, I had to create a new “from string” to match.  I set the update on the same column twice as I was running multiple modifications.
    • REPLACE(request, SUBSTRING_INDEX(SUBSTRING_INDEX(request, ‘”vendor”:{“name”:”‘, -1),'”,’,1), ‘XXXXXXXX’)
      • str = request — my column
      • from_str = SUBSTRING_INDEX(SUBSTRING_INDEX(request, ‘”vendor”:{“name”:”‘, -1),'”,’,1) — my string subsection, variable by row
      • to_str = ‘XXXXXXXX’ — my replacement/”to” value

1. Find the data
   

   SELECT id
   , SUBSTRING_INDEX(SUBSTRING_INDEX(request, ‘”vendor”:{“name”:”‘, -1),'”,’,1) AS vendor_name_part
   , SUBSTRING_INDEX(SUBSTRING_INDEX(request, ‘”account_number”:”‘, -1),'”}’,1) AS account_number_part
   , request
   FROM call_logs
   WHERE request LIKE ‘%vendor%account_number%’;

2. Update the data
   

   UPDATE call_logs
   SET request = REPLACE(request, SUBSTRING_INDEX(SUBSTRING_INDEX(request, ‘”vendor”:{“name”:”‘, -1),'”,’,1), ‘XXXXXXXX’),
   request = REPLACE(request, SUBSTRING_INDEX(SUBSTRING_INDEX(request, ‘”account_number”:”‘, -1),'”}’,1), ‘XXXXXXXX’)
   WHERE request LIKE ‘%vendor%account_number%’;

Before the update:

After the update:

Capturing Blocking

Blocking in databases is a result of locks taken in the database in order to help maintain concurrency among multiple transactions, indicating transaction intent.  A potential side effect of these locks can be blocking (and in some cases deadlocks!), where transactions are waiting for other transactions to release a lock resource.  Often we can see this result in slower performance for users.  (some of this is affected by transaction isolation levels, which would be a whole different post).

Similar to capturing deadlocks & foreign key errors, blocking can be captured.  I think in this case SQL Server has the leg up on tools here.

MySQL

On the MySQL side, I look for long running transactions, not necessarily only blocking transactions.  Here I use the Percona Monitoring Plugin for Nagios, using pmp-check-mysql-innodb , checking for all three options idle_blocker_duration, waiter_count and max_duration.

SQL Server

I’d like to thank Kendra at sqlworkbooks.com for explaining the (native!) tools in SQL Server to capture blocking in her online training class Troubleshooting Blocking and Deadlocks for Beginners (free through Dec 2017, recommended!).

Unlike the xml_deadlock_report, the blocked_process_report does not appear to be captured by default in the system_health extended event.  Like deadlocks, a custom Extended Event can be created & enabled to capture blocked processes, choosing “blocked_process_report” from the event library under Select Events to capture.  Once a blocker is captured, you can doubleclick to open the report and view information on the resources affected & (partial) queries.

Additionally, there is a handy open source SQL Server Blocked Process Report Viewer that creates a stored procedure which creates the lead & blocker trees for viewing the blocked processes in an easier to read tree format based based on the blockers captured in the extended events session.

The extended event captures blocking sessions, but doesn’t notify on occurrence.  An SQL Server Agent alert condition can be setup for “@performance_condition=N’General Statistics|Processes blocked||>|0′” to notify when a blocking event has occurred.

 

Capturing Foreign Key Errors

Looking further into capturing certain error conditions such as yesterday’s Capturing Deadlocks post, next up was looking for foreign key errors.

MySQL

Similar to capturing Deadlocks in MySQL, the most recent foreign key error can be viewed in SHOW ENGINE INNODB STATUS.  Similarly, the Percona Toolkit can be used to capture & log the foreign key errors using pt-fk-error-logger.  The setup again is similar to capturing deadlocks.

• Install the Percona toolkit using your standard process
• For the initial setup, I create a new tools database & tools user for this setup
• The below block will create the percona.deadlocks table if it does not exist, and run the logger in daemon mode, checking every 60 seconds.
• 1. Additional options

     pt-fk-error-logger h=<hostname>,u=perconaadm,P=3306 --ask-pass --dest D=percona,t=foreign_key_errlog --daemonize --interval 60s

  • Additional options notes
    • –ask-pass will cause a prompt for the password to be entered twice, password will not be visible in the process list.
    • –run-time 5m – This will run the tool in Daemon mode for 5 minutes, then exit.
    • –create-dest-table is optional (if you’ve already created the table!)
• I have scripted this out to check the daemon is running.

The logging of the error contains the timestamp of the error and the error message including the constraints violated and the failing statement.

 SQL Server

I was not able to find a corresponding automatic capture for foreign key errors, we rely on the application to catch the foreign key errors and log for further review.

Capturing Deadlocks

The last few months I’ve been taking a number of online & in person SQL Server training classes & meetups, living in New England there are several options within a couple of hours drive to attend SQL Saturdays.

Going over the training and some recent patching of my MySQL environment has reaffirmed a that there is overlap between SQL Server & MySQL, even if the tools may vary.  Capturing Deadlocks for example!

MySQL

On the MySQL side, the most recent deadlock can be viewed using SHOW ENGINE INNODB STATUS.  Downside is that depending on the volume of your deadlocks, that gives you no history.  To address this, I use a combination of tools from Percona:

1. The Deadlock Logger from the Percona Toolkit to capture deadlocks on a regular basis and log them to a table within my database.
   • Install the toolkit using your standard process
   • For the initial setup, I create a new tools database & tools user for this setup
   • The below block will create the percona.deadlocks table if it does not exist, and run the logger in daemon mode, checking every 60 seconds.

     pt-deadlock-logger h=<hostname>,u=perconaadm --ask-pass --dest D=percona,t=deadlocks  --create-dest-table --daemonize --interval 60s

     • Additional options notes
       • –ask-pass will cause a prompt for the password to be entered twice, password will not be visible in process list.
       • –run-time 5m – This will run the tool in Daemon mode for 5 minutes, then exit.
       • –create-dest-table is optional (if you’ve already created the table!)
   • I have scripted this out to check the daemon is running.
2. The pmp-check-mysql-deadlocks Nagios plugin from Percona Monitoring Plugins to send the team a Nagios alert if a deadlock occurs.
   • Install the plugins using your standard process
   • We have an additional user created with permissions to read the deadlocks table
   • Configure check in Nagios

SQL Server

On the SQL Server side, deadlocks (xml_deadlock_report) may be captured in the Extended Events system_health session(2012+), but depending on the volume of events, these could be overwritten.  An extended event specific to capture xml_deadlock_report specifically can be created to have a longer history.

View Deadlocks under system_health:

1. In SQL Server Management Studio, expand Management –> Extended Events –> Sessions –> system_health.
2. Right click on package0.event_file, choose “View Target Data…”
3. Click “Filters” on the Extended Events toolbar, and filter for:
   1. Field: Name, Value: xml_deadlock_report
   2. Click OK to apply filter
   3. 
4. The deadlock graph is viewable under the Deadlock tab: 

A SQL Server Agent alert could be considered to enabled alerts when deadlock thresholds are met (@performance_condition=N’Locks|Number of Deadlocks/sec|Database|>|0′), but we use an outside monitoring tool for our notifications.