Using autoexpect to write expect script

I was trying to write an expect script at the bash prompt and no matter what I tried it wouldn't work.

I was setting up a little script that Anisble would call to create the "encrypted" login-path as seen here:
https://dev.mysql.com/doc/mysql-utilities/1.5/en/mysql-utils-intro-connspec-mylogin.cnf.html

I finally discovered there is a little program called "autoexpect" on my CentOS Linux system that will write it for me.

By entering in:
autoexpect <command>

...it goes through the questions the prompt is asking me and auto generates the script for me.

Thanks to this site:
https://likegeeks.com/expect-command/

Here is what it generated for me:

#!/bin/expect -f
set timeout -1
spawn mysql_config_editor set --login-path=/root/mypath --host=localhost --user=root --password
match_max 100000
expect -exact "Enter password: "
send -- "SuperSecurePassword\r"
expect -exact "\r
WARNING : '/root/mypath' path already exists and will be overwritten. \r
 Continue? (Press y|Y for Yes, any other key for No) : "
send -- "y\r"
expect eof

This little script actually works for both creating the file the first time and replacing it. The first time you run it, the system won't actually generate the WARNING about being overwritten but since the script is expecting, it will exit with a complaint but it still finishes that part of the script as desired. 

Pam authentication plugin dialog.dll and case sensitive

I setup a MySQL Server to use the Pam authentication plugin as described in these two blog posts:

https://www.percona.com/doc/percona-server/LATEST/management/pam_plugin.html

https://www.percona.com/blog/2017/04/21/how-to-setup-and-troubleshoot-percona-pam-with-ldap-for-external-authentication/

I came across a couple issues.

1. The username for logging into MySQL with Active Directory credentials is case sensitive. I kept trying to login with lowercase and kept getting "ERROR 1045 (28000): Access denied for user...". After switching to uppercase it finally worked.

2. I wasn't able to login from my Windows system using a GUI tool like MySQL workbench. It would error when trying to logon mention "dialog". I downloaded and installed MariabDB onto my Windows System. I found the dialog.dll file and copied it.
Copied from here:
C:\Program Files\MariaDB 10.3\lib\plugin\dialog.dll
To this location:
C:\Program Files\MySQL\MySQL Server 5.7\lib\plugin\dialog.dll

How to grant view access to stored procedures

I have a client that uses a lot of triggers and stored procedures. Normally when I create a MySQL application user, that user has limited privileges such as SELECT, EXECUTE, SHOW VIEW, Insert, Update, Delete. When you login with this user and try to view the code inside of a stored procedure or a trigger, you will not be able to see it. It will show up as null. It is interesting that MySQL has a privilege for CREATE ROUTINE, ALTER ROUTINE but there isn't any "SHOW ROUTINE" privilege.

The work around to allow a user to view the code inside a stored procedure is to grant SELECT access to the mysql.proc table. Here is an example:

GRANT Select ON mysql.proc  TO 'MyUser'@'%';

If the user wants "Read Only" access but also needs to see triggers then I would grant the user these privileges:

GRANT Select, execute, SHOW VIEW, Trigger ON `MyDatabase`.* TO 'MyUser'@'%';

Be sure the user already exists and has a password before running these grant statements! On MySQL version before 5.7, running a grant statement like this without a password or password hash will auto create the user with a blank password.

Fixing definers for users that do not exist

Far too often, I get called to troubleshoot a problem because writes or some functionality is no longer working. When you manage thousands of database servers with thousands of databases all with different applications, you run into this problem now and again. When a developer or other DBA is terminated, their MySQL user will also get dropped on all our databases. Sometimes they have created views, events, stored procedures, functions, triggers and the definer for those objects became the user that created them by default. Unless you have monitoring step up to let you know when this happens or some kind of process to fix or prevent it, this can become very wide spread.

Fixing stored procedures, events and functions can be very easy because you can directly manipulate the values in the mysql.events table and mysql.proc table.


 UPDATE `mysql`.`proc` p SET definer = 'root@localhost' WHERE definer='me@%';

 UPDATE `mysql`.`event` p SET definer = 'root@localhost' WHERE definer='me@%';

https://dev.mysql.com/doc/refman/5.6/en//stored-routines-privileges.html

The MySQL manual warns against this with this text:

"The server manipulates the mysql.proc table in response to statements that create, alter, or drop stored routines. It is not supported that the server will notice manual manipulation of this table."

I've tested this by creating a basic stored procedure and changing the definer:

------------------------------------------------------------------
GRANT ALL PRIVILEGES ON *.* TO 'me'@'%' IDENTIFIED BY 'testPASS!';

DROP TABLE t1;
CREATE TABLE `t1` (
`id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
`timestamp` timestamp NULL DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB;

DROP PROCEDURE IF EXISTS `WriteToTable`;

DELIMITER ;;
CREATE DEFINER=`me`@`%` PROCEDURE `WriteToTable`()
BEGIN
INSERT INTO `t1` (`id`, `timestamp`) VALUES (NULL, NULL);
END;;
DELIMITER ;

call WriteToTable();

DROP USER 'me'@'%';

SELECT * FROM `mysql`.`proc` p  WHERE  name = 'WriteToTable';

UPDATE `mysql`.`proc` p SET definer = 'root@localhost' WHERE definer='me@%' AND name = 'WriteToTable';

call WriteToTable();

------------------------------------------------------------------

When I run this in the same session that was used to update the mysql.proc table, it fails with error because me@% does not exist anymore. However, if I close the session and log back in again then it works fine.

For updating triggers and views, there isn't an easy way to do it without dropping and re-creating and object.

Possible Mitigation for system performance after 'meltdown' bug patching

This is an interesting read on database performance (not specific to MySQL) after patching to secure data on multi tenant system.

https://blog.appoptics.com/visualizing-meltdown-aws/

Copied from the above article:

Applications that make frequent systems calls to read/write data either over network sockets or from disk systems will need to be better tuned for batching. Incurring small I/O operations is now more costly, and engineers will need to optimize their code to reduce the frequency of such calls. Finding the sweet spot between larger batch sizes and latency is difficult and will require software that adapts for multiple variables simultaneously. It was promising to see that the Kafka consumer libraries were able to optimize for this dynamically as network call latency increased.

Using triggers to audit database changes

I cannot count on people on my team to inform me of changes they are making to our databases so I've had to create some very basic monitoring which checks the status of certain system variables at a certain interval and saves this into a few tables. I've added triggers to these tables to audit changes and deletions.

This is an old but useful (and free) way of keeping historical information on changes in a MySQL databases. The triggers copy the old and the new value to a generic change log table (sometimes called audit log). In this manner multiple tables can use the same change log table.

In this example I have two tables, one called MyCluster and one called Tag. The Tag table uses key/value to store data while the MyCluster has specific attributes stored in columns for each cluster. Here is an example table structure for the two tables (actual table has many more columns):

CREATE TABLE `MyCluster` (
  `Profile` varchar(100) NOT NULL COMMENT 'Account where data came from',
  `DBClusterIdentifier` varchar(255) NOT NULL DEFAULT '',
  `Endpoint` varchar(255) DEFAULT NULL COMMENT 'Cluster Writer End Point Address',
  `EndpointIPAddress` varchar(50) DEFAULT NULL COMMENT 'Cluster Writer End Point IP Address',
  `ReaderEndpoint` varchar(255) DEFAULT NULL COMMENT 'Cluster Reader End Point Address',
  `ReaderEndpointIPAddress` varchar(50) DEFAULT NULL COMMENT 'Cluster Reader End Point IP Address',
  `ClusterCreateTime` timestamp NULL DEFAULT NULL COMMENT 'UTC time',
  `CreateTime` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `UpdateTime` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00' ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`Profile`,`DBClusterIdentifier`),
  KEY `ix_Endpoint` (`Endpoint`),
  KEY `ix_EndpointIPAddress` (`EndpointIPAddress`),
  KEY `ix_ClusterCreateTime` (`ClusterCreateTime`),
  KEY `ix_ReaderEndpoint` (`ReaderEndpoint`),
  KEY `ix_ReaderEndpointIPAddress` (`ReaderEndpointIPAddress`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='Cluster Specific info';


CREATE TABLE `Tag` (
  `Profile` varchar(100) NOT NULL COMMENT 'Account where data came from',
  `Type` varchar(255) NOT NULL DEFAULT '',
  `Identifier` varchar(255) NOT NULL DEFAULT '',
  `Key` varchar(100) NOT NULL DEFAULT '',
  `Value` varchar(1000) DEFAULT NULL,
  `ResourceARN` varchar(255) DEFAULT NULL,
  `CreateTime` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `UpdateTime` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00' ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`Profile`,`Type`,`Identifier`,`Key`),
  KEY `ix_Identifier` (`Identifier`),
  KEY `ix_Type` (`Type`),
  KEY `ix_Key` (`Key`),
  KEY `ix_Value` (`Value`(255))
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='Instance Tag info.';

-- This is the audit table or log table:

CREATE TABLE `Audit` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `Profile` varchar(100) DEFAULT NULL,
  `Identifier` varchar(100) NOT NULL DEFAULT '',
  `TableName` varchar(100) DEFAULT NULL,
  `FieldName` varchar(100) DEFAULT NULL,
  `OldValue` varchar(100) DEFAULT NULL,
  `NewValue` varchar(100) DEFAULT NULL,
  `Type` varchar(100) DEFAULT NULL,
  `timestamp` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='Data comes from triggers on other tables';

-- Here are the example triggers...

-- Example of saving information from a change to column called EndpointIPAddress:

DROP TRIGGER IF EXISTS MyCluster_BU;

DELIMITER $$
CREATE DEFINER=`root`@`%` TRIGGER MyCluster_BU BEFORE UPDATE ON MyCluster
  FOR EACH ROW BEGIN
    IF (OLD.EndpointIPAddress <> NEW.EndpointIPAddress AND OLD.EndpointIPAddress <> '' AND NEW.EndpointIPAddress <> '') THEN
        INSERT INTO Audit (
        `Profile`,
        `Identifier`,
        `TableName`,
        `FieldName`,
        `OldValue`,
        `NewValue`,
        `Type`,
        `timestamp`
        )
        VALUES (
        OLD.Profile,
        OLD.DBClusterIdentifier,
        'MyCluster',
        'EndpointIPAddress',
        OLD.EndpointIPAddress,
        NEW.EndpointIPAddress,
        'change',
        NOW()
        );
    END IF;

END$$
DELIMITER ;

-- Example of saving information after a delete occurs:

DROP TRIGGER IF EXISTS MyCluster_AD;

DELIMITER $$

CREATE DEFINER=`root`@`%` TRIGGER MyCluster_AD
AFTER DELETE
   ON MyCluster FOR EACH ROW

BEGIN

  INSERT INTO Audit (
  `Profile`,
  `Identifier`,
  `TableName`,
  `FieldName`,
  `OldValue`,
  `NewValue`,
  `Type`,
  `timestamp`
  )
  VALUES (
  OLD.Profile,
  OLD.DBClusterIdentifier,
  'MyCluster',
  'EndPoint',
  OLD.EndPoint,
  '',
  'delete',
  NOW()
  );

END; $$

DELIMITER ;


-- Example of saving information after a delete occurs on a key value table. The key is StackName and the value can be anything:


DROP TRIGGER IF EXISTS Tag_AD;

DELIMITER $$

CREATE DEFINER=`root`@`%` TRIGGER Tag_AD
AFTER DELETE
   ON Tag FOR EACH ROW

BEGIN

IF (OLD.Key = 'StackName') THEN
  INSERT INTO Audit (
  `Profile`,
  `Identifier`,
  `TableName`,
  `FieldName`,
  `OldValue`,
  `NewValue`,
  `Type`,
  `timestamp`
  )
  VALUES (
  OLD.Profile,
  OLD.Identifier,
  'Tag',
  'StackName',
  OLD.Value,
  '',
  'delete',
  NOW()
  );
END IF;

END; $$

DELIMITER ;


-- Example of saving information from a change to key value. The key is StackName and the Value can be anything.

DROP TRIGGER IF EXISTS Tag_BU;

DELIMITER $$
CREATE DEFINER=`root`@`%` TRIGGER Tag_BU BEFORE UPDATE ON Tag
  FOR EACH ROW BEGIN
  IF (OLD.Key = 'StackName' AND OLD.VALUE <> NEW.VALUE) THEN
        INSERT INTO Audit (
        `Profile`,
        `Identifier`,
        `TableName`,
        `FieldName`,
        `OldValue`,
        `NewValue`,
        `Type`,
        `timestamp`
        )
        VALUES (
        OLD.Profile,
        OLD.Identifier,
        'Tag',
        'StackName',
        OLD.VALUE,
        NEW.VALUE,
        'change',
        NOW()
        );
    END IF;

END$$
DELIMITER ;


-- Now add some data into the tables, make changes and delete some rows.

INSERT INTO `MyCluster` (`Profile`, `DBClusterIdentifier`, `Endpoint`, `EndpointIPAddress`, `ReaderEndpoint`, `ReaderEndpointIPAddress`, `ClusterCreateTime`, `CreateTime`, `UpdateTime`) VALUES ('test', 'test', 'test', '123', NULL, NULL, NULL, CURRENT_TIMESTAMP, '0000-00-00 00:00:00');
UPDATE `MyCluster` SET `EndpointIPAddress` = '456' WHERE `Profile` = 'test' AND `DBClusterIdentifier` = 'test';
DELETE FROM `MyCluster` WHERE (`Profile` = 'test' AND `DBClusterIdentifier` = 'test');

INSERT INTO `Tag` (`Profile`, `Type`, `Identifier`, `Key`, `Value`, `ResourceARN`, `CreateTime`, `UpdateTime`) VALUES ('test', 'Cluster', '123', 'StackName', 'MyTest', NULL, CURRENT_TIMESTAMP, '0000-00-00 00:00:00');
UPDATE `Tag` SET `Value` = 'MyTestIsDone' WHERE `Profile` = 'test' AND `Type` = 'Cluster' AND `Identifier` = '123' AND `Key` = 'StackName';
DELETE FROM `Tag` WHERE (`Profile` = 'test' AND `Type` = 'Cluster' AND `Identifier` = '123' AND `Key` = 'StackName');

Values that were changed or Deleted for the columns that have triggers setup will now appear in the Audit table.


PROS:

1.     Easy to implement.
2.     Very simple triggers
3.     Only one table needed to keep history for any number of tables

CONS

1.     Data type for old and new values is very generic, all data no matter what type it originally was is stored as TEXT
2.     There are no foreign key constraints between the tables. The columns in the change log table can refer to anything. Without  constraints, there is nothing to stop accidental or intentional manipulating of the numbers to values that don’t exist in the source table.
3.     Triggers add additional overhead to the system which could slow performance
4.     Writing queries to revert data is not simple