Quickly create selinux policies using audit2allow

Recently I was configuring MySQL in a high availability configuration when I encountered problems with getting my keepalived health check script to work.

I have two MySQL servers configured in Master/Master replication with a VIP (keepalived) which floats between the two servers. We only write to one of the masters using the VIP. The goal is to have a fail over of the VIP occur if the primary server becomes unreachable.

I created my health check script and configured Keepalived to use the script to check on Mysql. Below is snippet of code from my keepalived.conf config file. I would test the fail over by shutting down Mysql to force a fail over of the VIP to occur however the fail over was not occurring. When I would run keepalived as root from the console the VIP fail over process would work. I started to suspect a permissions or selinux issue.

vrrp_script check_mysql {
script /opt/mysql/check.py
interval 2
timeout 3
}

track_script {
check_mysql
}

Introduce audit2allow, this tool reads the audit logs and creates selinux allow policies off of failed audits.

yum install /usr/bin/audit2allow 

I grep the audit.log file to find failures. Then wrote down context which was being denied.

grep check.py /var/log/audit/audit.log 

After finding all the denied contexts I used audit2allow to create allow polices.

grep keepalived_t /var/log/audit/audit.log | audit2allow -M keepalived_t
grep root_t /var/log/audit/audit.log | audit2allow -M root_t
grep tmp_t /var/log/audit/audit.log | audit2allow -M tmp_t
grep mysqld_port_t /var/log/audit/audit.log | audit2allow -M mysqld_port_t

semodule -i keepalived_t.pp
semodule -i root_t.pp
semodule -i tmp_t.pp
semodule -i mysqld_port_t.pp

After creating the allow polices the health checking script would run successfully and a VIP fail over would occur in the event MySQL went down.

Create SQL SERVER Linked SERVER to HADOOP

I recently attended a SQL SATURDAY precon in Minneapolis. The precon was an introduction to Hadoop for SQL users. The introduction got me interested enough to give Hadoop another try. In my spare time between last weekend and this I have been installing, configuring and playing around with Hadoop. My initial thoughts are that Hadoop is defiantly production ready despite what you might read from some analysts. Hortonworks Ambari made installing Hortonworks nodes painless.

You may not be aware of this but it is possible to query hadoop right from SQL SERVER using a linked server. In this tutorial I go through the steps needed to setup a linked server between Hadoop and SQL SERVER.

This tutorial was written using SQL SERVER 2012 and a three node Hortonworks cluster running HDFS 2.7, MapReduce2 2.7, YARN 2.7, and Hive 1.2. The Hortonworks cluster is running on CentOS 7.1.

Let’s get started, log into the Hadoop cluster via ssh. On the Linux cluster create a new user and add that user to the hadoop group.

shell> adduser sqlserver
shell> passwd sqlserver

Add user sqlserver to hadoop group

shell> usermod -a -G hadoop sqlserver

I ran through part of the HIVE tutorial and used their Group lens data set for sample data.

ssh into one of the Hadoop nodes and perform the following steps to load data into Hadoop and create a table.

shell> su hdfs
shell> wget http://files.grouplens.org/datasets/movielens/ml-100k.zip
shell> unzip ml-100k.zip

shell> hive
hive>CREATE TABLE u_data (
  userid INT,
  movieid INT,
  rating INT,
  unixtime STRING)
ROW FORMAT DELIMITED
FIELDS TERMINATED BY '\t'
STORED AS TEXTFILE;

hive>LOAD DATA LOCAL INPATH '/home/hdfs/ml-100k/u.data' INTO TABLE u_data;
hive> exit

You should now have data loaded into the Hive table we just created in Hadoop.

Next, download the Microsoft HIVE ODBC driver and install it on your Microsoft SQL SERVER. Select the 32bit or 64bit driver which appropriate for your SQL SERVER.
Microsoft HIVE ODBC driver

Configure the ODBC driver using the “sqlserver” username and password you created earlier.

Log into SQL Management Studio and configure the linked server using the data source with the DSN used for the ODBC.

Configure the Hadoop username and password.

select *
from openquery (HIVE, 'select userid, movieid, rating from default.u_data')
where userid = 196;

If everything was configured correctly you should be able to query Hadoop from SQL SERVER.

CentOS 7 Join Active Directory Domain

Before you begin ensure that the DNS on the Linux computer you wish to join to the domain is pointed to a the Active Directory server. Active Directory relies heavily on DNS to function.

STEP 1. Ensure the following packages are installed

yum -y install realmd sssd oddjob 
oddjob-mkhomedir adcli samba-common 

STEP 2. From the computer you will join to the domain run realm discover to verify connectivity to the domain controllers.

[root@test02 ~] realm discover LAB.NET
lab.net
  type: kerberos
  realm-name: LAB.NET
  domain-name: lab.net
  configured: kerberos-member
  server-software: active-directory
  client-software: sssd
  required-package: oddjob
  required-package: oddjob-mkhomedir
  required-package: sssd
  required-package: adcli
  required-package: samba-common
  login-formats: %U
  login-policy: allow-realm-logins

STEP 3. Join Active Directory domain, you must use an account which has privileges to join a computer the domain.

[root@test02 ~] realm join -U adminuser LAB.NET

STEP 4. Verify you can retrieve directory information for user

[root@test02 ~] id LAB\\ktest
uid=522401118(ktest) gid=522400513(domain users) 
groups=522400513(domain users)

STEP 5. Verify the ability to perform a su to an Active Directory user

[root@test02 ~] su - ktest
Last login: Sun Sep 20 05:21:42 CDT 2015 on pts/0
[ktest@test02 ~]$

STEP 6. To remove the requirement of fully qualifying the Active Directory username edit the sssd.conf file. After this change you will not be required to use DOMAIN\\ when logging in as an Active Directory user.

[root@test02 ~] vi /etc/sssd/sssd.conf
use_fully_qualified_names = False
[root@test02 ~] systemctl restart sssd 

SQL SERVER Query tuning

Recently I encountered an interesting issue with the SQL SERVER query engine. I had received a high CPU alert from the SQL SERVER 2008 server. I logged in and looked at the query plan cache to see which queries were causing load on the server. The query below quickly caught my attention. Each time the query below ran it was performing 207,651 logical reads on the server. I thought this was interesting in that the query seemed to be very selective with the WHERE clause placed on it. In addition, I noticed that there were extra parentheses around the filters in the WHERE clause. Upon removing these parentheses the query went from 207,651 logical reads to 4.

Original query:

set statistics io on
SELECT table1.brcd, table2.Pkt_x, table2.PKT_NBR 
FROM table1 WITH(NOLOCK) INNER JOIN table2 WITH(NOLOCK) ON table1.PKT_NB = table2.PKT_NB 
WHERE (((table2.Pkt_x)=1) AND ((table2.PKT_NBR)=5630));

(1 row(s) affected)
Table 'table2'. Scan count 9, logical reads 207651, physical reads 0, 
read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'Worktable'. Scan count 0, logical reads 0, physical reads 0, 
read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'table1'. Scan count 1, logical reads 4, physical reads 0, 
read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

I gathered the following information and forwarded it to the customer. They replied saying that the query was generated by an Microsoft Access database and they had refactored the query, removing the extra parentheses.

Refactored query:

SELECT table1.brcd, table2.Pkt_x, table2.PKT_NBR
FROM table1 WITH(NOLOCK) INNER JOIN table2 WITH(NOLOCK) ON table1.PKT_NB = table2.PKT_NB
WHERE table2.Pkt_x=1 and table2.PKT_NBR='5630'

Table 'table1'. Scan count 1, logical reads 4, physical reads 0, 
read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'table2'. Scan count 1, logical reads 4, physical reads 0, 
read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

Solarwinds SDK script unmanage nodes

Solarwinds offers a Powershell SDK to manipulate nodes programmatically. The Solarwinds SDK can be downloaded here.

I have created a demonstration script will unmanage a node for 2 hours so that maintenance can be performed on the node.

Setup connection to Solarwinds application server.

$secpasswd = ConvertTo-SecureString "password" -AsPlainText -Force
$mycreds = New-Object System.Management.Automation.PSCredential("LAB\username01", $secpasswd)

Search for the node named ‘SQL01.lab.net’ and set the node as unmanaged for 2 hours. This is very useful if you have planned maintenance which happens once per week and want to programmatically unmanage a node each week.

$swis = Connect-Swis -Hostname swserver.lab.net -Credential $mycreds
$uris = Get-SwisData $swis "SELECT Uri FROM Orion.Nodes where Caption='SQL01.lab.net'"
$uris | ForEach-Object { Set-SwisObject $swis $_ @{Status=9;Unmanaged=$true;UnmanageFrom=[DateTime]::UtcNow;UnmanageUntil=[DateTime]::UtcNow.AddHours(2)} }

Adding nodes to rundeck

I am still gaining operational knowledge of rundeck. Rundeck is an awesome job scheduling tool. Recently I was required to setup a job which is scheduled to run on a remote node. To perform this task you must edit the resource.xml file under the project directory. For this to work it is required that you setup ssh key pairs between the two servers. Check out this link from Digital Ocean on setting up ssh key pairs

/var/rundeck/projects/[projectname]/etc/resources.xml

Sample node added to the resources.xml file

<project>
  <node name="servername" description="Dev MySQL" tags="" hostname="servername" osArch="amd64" osFamily="unix" osName="Linux" osVersion="2.6.32-504.8.1.el6.x86_64" username="userAccount"/>
</project>

After adding the node to rundeck you must restart the service for the node to be recognized.

service rundeckd restart

Talend Open Studio and Kerberos authentication integration

Recently I was working to integrate Talend Open Studio into our Windows domain environment via Kerberos authentication. Rather then use the built in tMSSQLConnection component which uses the jTDS driver behind the scenes. I have decided to use the native JDBC Microsoft SQL SERVER JDBC Microsoft SQL SERVER driver.. The Microsoft JDBC driver supports more features then the tMSSqlConnection_1 connector. For example, MSSQL SERVER Always On is supported with the parameter multiSubnetFailover=true or routing to the readonly version of a database applicationIntent=ReadOnly.

Connection string for Active Directory kerberos authenticaiton:

jdbc:sqlserver://mysqlserver01.LAB.NET;database=DBA;integratedSecurity=true;authenticationScheme=JavaKerberos

However, one of the requirements of Kerberos authentication is that you acquire a ticket from the key distribution center (KDC) server. If you have not acquired a ticket from the KDC server you will receive an error from Talend Open studio like the one below.

Starting job cdc_test at 13:54 12/08/2015.

[statistics] connecting to socket on port 4019
[statistics] connected
Exception in component tJDBCConnection_1
com.microsoft.sqlserver.jdbc.SQLServerException: Integrated authentication failed. ClientConnectionId:219f9461-59d8-41fa-8af6-483f4e2a86bd
	at com.microsoft.sqlserver.jdbc.SQLServerConnection.terminate(SQLServerConnection.java:1668)
	at com.microsoft.sqlserver.jdbc.KerbAuthentication.intAuthInit(KerbAuthentication.java:140)
	at com.microsoft.sqlserver.jdbc.KerbAuthentication.GenerateClientContext(KerbAuthentication.java:268)
	at com.microsoft.sqlserver.jdbc.SQLServerConnection.sendLogon(SQLServerConnection.java:2709)
	at com.microsoft.sqlserver.jdbc.SQLServerConnection.logon(SQLServerConnection.java:2243)
	at com.microsoft.sqlserver.jdbc.SQLServerConnection.access$000(SQLServerConnection.java:42)
	at com.microsoft.sqlserver.jdbc.SQLServerConnection$LogonCommand.doExecute(SQLServerConnection.java:2229)
	at com.microsoft.sqlserver.jdbc.TDSCommand.execute(IOBuffer.java:5696)
	at com.microsoft.sqlserver.jdbc.SQLServerConnection.executeCommand(SQLServerConnection.java:1716)
	at com.microsoft.sqlserver.jdbc.SQLServerConnection.connectHelper(SQLServerConnection.java:1327)
	at com.microsoft.sqlserver.jdbc.SQLServerConnection.login(SQLServerConnection.java:992)
	at com.microsoft.sqlserver.jdbc.SQLServerConnection.connect(SQLServerConnection.java:828)
	at com.microsoft.sqlserver.jdbc.SQLServerDriver.connect(SQLServerDriver.java:1012)
	at java.sql.DriverManager.getConnection(DriverManager.java:571)
	at java.sql.DriverManager.getConnection(DriverManager.java:215)
	at myfirsttalendjob.cdc_test_0_1.cdc_test.tJDBCConnection_1Process(cdc_test.java:552)
	at myfirsttalendjob.cdc_test_0_1.cdc_test$4.run(cdc_test.java:3492)
Caused by: javax.security.auth.login.LoginException: Unable to obtain Princpal Name for authentication 
	at com.sun.security.auth.module.Krb5LoginModule.promptForName(Krb5LoginModule.java:800)
	at com.sun.security.auth.module.Krb5LoginModule.attemptAuthentication(Krb5LoginModule.java:671)
	at com.sun.security.auth.module.Krb5LoginModule.login(Krb5LoginModule.java:584)
	at sun.reflect.GeneratedMethodAccessor2.invoke(Unknown Source)
	at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
	at java.lang.reflect.Method.invoke(Method.java:606)
	at javax.security.auth.login.LoginContext.invoke(LoginContext.java:762)
	at javax.security.auth.login.LoginContext.access$000(LoginContext.java:203)
	at javax.security.auth.login.LoginContext$4.run(LoginContext.java:690)
	at javax.security.auth.login.LoginContext$4.run(LoginContext.java:688)
	at java.security.AccessController.doPrivileged(Native Method)
	at javax.security.auth.login.LoginContext.invokePriv(LoginContext.java:687)
	at javax.security.auth.login.LoginContext.login(LoginContext.java:595)
	at com.microsoft.sqlserver.jdbc.KerbAuthentication.intAuthInit(KerbAuthentication.java:133)
	... 15 more
[statistics] disconnected
Job cdc_test ended at 13:54 12/08/2015. [exit code=1]

To work around this issue I created a Windows batch file. When I start up Talend using the batch file it asks me for my Windows password. I enter my Windows Active Directory password and a ticket is created from the KDC.

c:\program files\java\jre7\bin\kinit.exe tatroc@LAB.NET
C:\Talend_Ent\Talend-Studio-20141207_1530-V5.6.1\Talend-Studio-win-x86_64.exe -vm C:\Program Files\Java\jdk1.7.0_75\bin

The connection to the MSSQL SERVER using kerberos is now successful.

MySQL Performance Schema

The MySQL Performance Schema is an extremely valuable feature in MySQL 5.6 for finding queries which have a performance impact on the database. However, the performance schema is not enabled by default. The performance schema can be enabled through several different methods. Possible reasons why enabling the performance schema is a bad thing includes performance impact on the server. Percona has a good write up on the performance impact of enabling the Performance Schema. Therefore be careful when enabling the performance schema in a production environment as it can add a significant amount of overhead.

Setting the performance_schema directives in the mn.cnf file will ensure that the performance_schema is enabled at startup. One can also enable the performance_schema by running SQL queries after MySQL is up and running. This can be useful if you want to disable and enable the performance_schema to troubleshoot performance issues then turn them back off without having to restart MySQL.

vim /etc/my.cnf
performance_schema_consumer_events_statements_history_long=ON
performance_schema_consumer_events_statements_history=ON
performance_schema_consumer_events_stages_current=ON
performance_schema_consumer_events_stages_history=ON
performance_schema_consumer_events_stages_history_long=ON
performance_schema_consumer_events_waits_current=ON
performance_schema_consumer_events_waits_history=ON
performance_schema_consumer_events_waits_history_long=ON

performance_schema=ON

performance_schema_instrument='%=ON'

Consumers are setup in the setup_consumers table this specified where the data will go.

select * from performance_schema.setup_consumers;

View which objects have the performance schema setup on them.
The screen shot below shows that performance stats will be collected for all databases except for mysql, Performance_schema and information_schema.

The setup_actors table determines who to monitor, you can filter by host, user or role.

To view the current instruments which are enabled run the following query.

select * from setup_instruments

Instruments can be enabled by running an UPDATE statement on the table.

update setup_instruments
set enabled = 'yes', timed = 'yes'
where name = 'statement/sql/lock_tables'

After the performance_schema has been configured you will be able to view the sys tables for statistics collected on queries.

select * 
from sys.`x$statements_with_full_table_scans`

Here it is important to note the difference between the average rows sent and the average rows examined. This means that the query optimizer is examining many rows but only sending a a small number. If there is a large difference between the two. This may indicate that their is a need for an index.

Additional performance tables can be viewed which present statistics on number of temp tables created.

select * 
from sys.`x$statements_with_temp_tables`

select * 
from sys.`x$statement_analysis`

Reference: For more in depth informaiton on the the performance schema and A Visual Guide to the MySQL Performance Schema

Resolved: rundeck out of memory errors

I recently resolved an issue where rundeck would fail to run a Talend ETL job. Because rundeck and Talend both use the Java JVM I was unsure where the message was bubbling up from. After increasing -Xmx in the Talend job and rerunning the job still failed. I then increased -Xmx in /etc/rundeck/profile which resulted in the job completing successfully.

Below is the error message I received in the rundeck console.

Failed dispatching to node localhost: java.lang.OutOfMemoryError: Java heap space
14:10:16			Execution failed: 8155: [Workflow result: , step failures: {1=Dispatch failed on 1 nodes: [localhost: Unknown: java.lang.OutOfMemoryError: Java heap space]}, Node failures: {localhost=[Unknown: java.lang.OutOfMemoryError: Java heap space]}, flow control: Continue, status: failed

Increased the JVM memory settings from 1024m to 6144m.
vim /etc/rundeck/profile

RDECK_JVM="$RDECK_JVM -Xmx6144m -Xms256m -XX:MaxPermSize=256m -server"
#RDECK_JVM="$RDECK_JVM -Xmx1024m -Xms256m -XX:MaxPermSize=256m -server"

How to handle bulk UPDATE statements in large tables

Sometimes we are required to do mass database UPDATES. On very large tables this can become a problem. For example, I recently had a table with 80 million records in it which required a mass UPDATE. Instead of running one large UPDATE statement it is better to break the transactions into smaller batches. This will reduce the locking incurred on the table in question.

WHILE (2 > 1)
  BEGIN
    BEGIN TRANSACTION
     UPDATE TOP(1000) dbo.tbl_shipments
     SET ship_zone = 99999
     WHERE ship_zone = 0;
    
    IF @@ROWCOUNT = 0
      BEGIN
        COMMIT TRANSACTION
         BREAK
      END
    COMMIT TRANSACTION
    -- 1 second delay
    WAITFOR DELAY '00:00:00.200'
  END -- WHILE
GO

There is a really good article at http://sqlperformance.com/2013/03/io-subsystem/chunk-deletes which goes into depth about the issue. Although this article is focused on deletes it also applies to UPDATE statements.