Hadoop 1.21 Name node startup issue

While I was trying to poke around in hadoop environment I noticed that namenode wouldn’t start up. After doing some research I found that its the IPv6 issue. So I summarized steps I took to resolve the issue

 

 

 

hadoop jps 1

 

hadoop start 2

 

 

We are starting the hadoop stack

hadoop missing name node 3

we can see the name node seems like it did not start as we can see in jps list

first step is make sure you can ssh local machine (127.0.0.1)

hadoop ssh port activity

I tried tee see port activity looks like entire hadoop stack is using Ip6. we can change this behavior by changing hadoop-env.sh file in conf directory

 

set hadoop to use ipv4

 

export HADOOP_OPTS=-Djava.net.preferIPv4Stack=true

stop all hadoop services and start hadoop stak

hadoop start 5 all

 

 

Standard SQL Server Error log procedure

we spend lot of time trying to look at log files when we run any particular SQl files have a standard frame work to collect these errors and store in table, amkes the application management simple at same time provides some insights into exceptions over the time

IF EXISTS (SELECT 1 FROM sys.tables WHERE name = 'wm_ErrorLog')
DROP TABLE [wm_ErrorLog]
IF EXISTS (SELECT * FROM sys.objects WHERE name = 'WMSp_ErrorLog_Ins_Error' AND type ='P')
DROP PROCEDURE [WMSp_ErrorLog_Ins_Error]
CREATE TABLE [wm_ErrorLog] (
    [Error_id] INT IDENTITY(1,1)
	,[Error_Number] [INT] NOT NULL
	,[Error_procedure] VARCHAR(max) NOT NULL
	,[Error_Message] [VARCHAR](MAX) NULL
	,[Error_Line] INT
	,[Error_State] INT 
	,[Error_Severity] INT 
	,[SPID] [INT] NULL
	,[Program_Name] [VARCHAR](255) NULL
	,[Client_Address] [VARCHAR](255) NULL
	,[Authentication] [VARCHAR](50) NULL
	,[Error_User_Application] [VARCHAR](100) NULL
	,[Error_Date] [DATETIME] NULL
	,[Error_User_System] [sysname] NOT NULL
	) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO

SET ANSI_PADDING OFF
GO

ALTER TABLE wm_ErrorLog ADD CONSTRAINT [dfltErrorLog_error_date] DEFAULT(GETDATE())
FOR [Error_Date]
GO

ALTER TABLE wm_ErrorLog ADD CONSTRAINT [dfltErrorLog_error_user_system] DEFAULT(SUSER_SNAME())
FOR [Error_User_System]
GO

ALTER TABLE [wm_ErrorLog] ADD CONSTRAINT wmpk_wm_errorlog_id PRIMARY KEY (error_id)

GO


Create  PROCEDURE [WMSp_ErrorLog_Ins_Error] (
	 @Error_Number INT = NULL
	,@Error_procedure SYSNAME = 'Ad hoc sql'
	,@Error_Message VARCHAR(4000) = NULL
	,@UserID INT = NULL
	)
AS
BEGIN
	BEGIN TRY
		INSERT INTO wm_ErrorLog (
			[Error_Number]
			,[Error_procedure]
			,[Error_Message]
			,[Error_line]
			,[Error_state]
			,[Error_severity]
			,[SPID]
			,[Program_Name]
			,[Client_Address]
			,[Authentication]
			,[Error_User_System]
			,[Error_User_Application]
			)
		SELECT [Error_Number] = ISNULL(@Error_Number, ERROR_NUMBER())
			,[Error_procedure] = COALESCE(ERROR_PROCEDURE(),@Error_procedure)
			,[Error_Message] = ISNULL(@Error_Message, ERROR_MESSAGE())
			,[Error_line] = ERROR_LINE()
			,[Error_state] =ERROR_STATE()
			,[Error_severity] = ERROR_SEVERITY()
			,[SPID] = @@SPID
			,[Program_Name] = ses.program_name
			,[Client_Address] = con.client_net_address
			,[Authentication] = con.auth_scheme
			,[Error_User_System] = SUSER_SNAME()
			,[Error_User_Application] = @UserID
		FROM sys.dm_exec_sessions ses 
		LEFT JOIN sys.dm_exec_connections con ON con.session_id = ses.session_id
		WHERE ses.session_id= @@SPID
	END TRY

	BEGIN CATCH
		-- We even failed at the log entry so let's get basic  
		INSERT INTO wm_ErrorLog (
			ERROR_NUMBER
			,ERROR_procedure
			,ERROR_MESSAGE
			)
		VALUES (
			- 100
			,OBJECT_NAME(@@PROCID)
			,ERROR_MESSAGE()
			)
	END CATCH
END
GO

Above scripts creates two objects a table and procedure

Here is how we should plan to use

BEGIN TRY
RAISERROR(‘error’,16,1)
END TRY
BEGIN CATCH
EXECUTE [WMSp_ErrorLog_Ins_Error]
END CATCH

SELECT * FROM wm_errorlog

Sql error log

How to Fix :: OLE DB provider “SQLNCLI10″ for linked server returned message “Query timeout expired”.

by default a remote query (Query using Linked Server) expires in 10 mins. We can find this value in SQL Server using below queries

 

sys config remote procedure

 

SELECT * FROM sys.sysconfigures
WHERE comment ='remote query timeout'

--or 

SELECT * FROM sys.configurations
WHERE name='remote query timeout (s)'

We can find more details in MS doc

http://msdn.microsoft.com/en-us/library/ms189631.aspx

in order to change it we have to sp_configure and we don’t need to restart the database engine

 

sp_configure 'remote query timeout',3600
go 
reconfigure with override 
go 

This will set the time out to 3600 seconds/ 1 hr

Setup login.sql in sqlplus windows envrionment

it would be tiresome to enter environment variables each time you login to sqlplus, good thing is sqlplus allows us to use a script (login.sql) which has the environment variables defined each time we login (user profile)  https://docs.oracle.com/cd/E11882_01/server.112/e16604/ch_two.htm#i1133044 .  Once you define the sqlpath sqlplus looks for login.sql in the path directory and executes it once you login successful. This can be also configured at global level called site profile it allows the DBA to set default env for all the users. so when a user connects through sqlplus first the global site profile (glogin.sql) is executed and then the user profile (login.sql) is executed which is defined in SQLPATH environment variable in Windows

 

sqlpath

 

here %USERPROFILE% is an environment variable has default location of the user who is logged into the machine  C:\USERS\Username

we have to make sure we have login.sql available in that location

 

sql plus login sql

 

you can see below sqlprompt has changed to what we defined in login.sql  (‘&_user.@&_connect_identifier.>’)

sqlprompt sqlplus

 

 

Get SQL Agent job status

I was working on a script to get the agent job status so i can trigger a necessary action based on the job status. So I thought I I will share that here

Example here I have two sql agent jobs these are simple jobs

sql ajent job 1

Here is the procedure to start with

USE [msdb]
GO

/****** Object:  StoredProcedure [dbo].[SpGetJobStatus]    Script Date: 11/20/2014 9:25:38 PM ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

Create PROCEDURE [dbo].[SpGetJobStatus] (
	@Jobname SYSNAME
	,@state INT OUTPUT
	)
AS
DECLARE @job_owner SYSNAME
DECLARE @job_id UNIQUEIDENTIFIER
DECLARE @RowCount INT
DECLARE @Jobs AS TABLE (
	RowCounter INT IDENTITY(1, 1)
	,job_id UNIQUEIDENTIFIER
	,job_owner SYSNAME
	,Jobname SYSNAME
	)

INSERT INTO @Jobs (
	job_id
	,job_owner
	,Jobname
	)
SELECT job_id
	,sl.NAME
	,sj.NAME
FROM msdb.dbo.sysjobs sj
INNER JOIN sys.syslogins sl ON sj.owner_sid = sl.sid
WHERE sj.NAME = @Jobname

--Or @Jobname IS NULL
SET @RowCount = @@ROWCOUNT

DECLARE @xp_results TABLE (
	job_id UNIQUEIDENTIFIER NOT NULL
	,last_run_date INT NOT NULL
	,last_run_time INT NOT NULL
	,next_run_date INT NOT NULL
	,next_run_time INT NOT NULL
	,next_run_schedule_id INT NOT NULL
	,requested_to_run INT NOT NULL
	,-- BOOL  
	request_source INT NOT NULL
	,request_source_id SYSNAME COLLATE database_default NULL
	,running INT NOT NULL
	,-- BOOL  
	current_step INT NOT NULL
	,current_retry_attempt INT NOT NULL
	,job_state INT NOT NULL
	)

WHILE @ROWCOUNT > 0
BEGIN
	INSERT INTO @xp_results
	EXECUTE master.dbo.xp_sqlagent_enum_jobs 1
		,@job_owner
		,@job_id

	SET @RowCount = @RowCount - 1
END

SET @state = (
		SELECT job_state
		FROM @xp_results results
		INNER JOIN msdb.dbo.sysjobs sj ON results.job_id = sj.job_id
		WHERE sj.NAME = @Jobname
		)

SELECT sj.NAME
	,job_state
	,CASE job_state
		WHEN 1
			THEN 'Executing'
		WHEN 2
			THEN 'Waiting for thread'
		WHEN 3
			THEN 'Between retries'
		WHEN 4
			THEN 'Idle'
		WHEN 5
			THEN 'Suspended'
		WHEN 6
			THEN '<unknown>'
		WHEN 7
			THEN 'Performing completion actions'
		ELSE ''
		END job_state_description
FROM @xp_results results
INNER JOIN msdb.dbo.sysjobs sj ON results.job_id = sj.job_id
WHERE sj.NAME = @Jobname

RETURN @state

Procedure has one input parameter and one output parameter

Input parameter is the job name and output parameter will provide us the job state

agent job status

EXEC [dbo].[SpGetJobStatus] ‘job1′,null

Below script will help us get the job state to a local variable

DECLARE @jobstate INT 

EXEC [dbo].[SpGetJobStatus] 'job1',@state = @jobstate OUTPUT

SELECT @jobstate AS jobstate 

Here is the script that polls on above procedure to get the job state, while loop breaks only when job becomes idle

DECLARE @jobstate INT

EXEC [dbo].[SpGetJobStatus] 'job1'
	,@state = @jobstate OUTPUT

WHILE (@jobstate != 4)
BEGIN
	EXEC [dbo].[SpGetJobStatus] 'job1'
		,@state = @jobstate OUTPUT

	WAITFOR DELAY '00:00:05'
END

SELECT @jobstate
	-- Do something job has finished 
	-- job1 is idle start another job 

We can use above step before start first step in second job as seen below, when second job runs it waits until job 1 is finished

agent job step

Quick tip: Validating powershell parameters

Although powershell offers a great way of validating the parameters using advanced functions we can extend this functionality to make simple scripts robust as they can be

http://technet.microsoft.com/en-us/library/hh847743.aspx

for example here in this snipet we can validate the input string using a pattern and length

1
2
3
4
5
6
7
8
9
 Param
          (
            [parameter(Mandatory=$true)]
            [ValidateLength(5,10)]
            [String]
            $str
          ) 
$str

Above script will make sure input string length is at least 5 charcters upt 10 characters max

validate input
input string test less than 5 charecters script throws an exception

validate input 1

[ValidatePattern(“[A-Z][A-Z][A-Z][A-Z][A-Z][A-Z]”)]

adding this as a validation attriubute this will make sure string only contains letters a to z

 Param
          (
            [parameter(Mandatory=$true)]
            [ValidateLength(5,10)]
	    [ValidatePattern("[A-Z][A-Z][A-Z][A-Z][A-Z][A-Z]")]
            [String]
            $str
          ) 

$str

validate pattern

 

we can also validate from a script this what I am really interested

below is a simple function that checks if a parameter is null or empty if its not empty does it has any whitespaces similar a static method .Net 4 exposes IsNullOrWhiteSpace

 

function Isnullorempty ($str) 
{
IF( $str -eq $null ) 
 {  $true }  
elseif ( $str.ToString().Trim().Length -eq  0 ){ $true}
else {$false}
}

We are going to embed this script in validation script

 Param
          (
            [parameter(Mandatory=$true)]
            [ValidateLength(5,10)]
 	    [ValidateScript({ IF( $_ -eq $null) 
		{$false }  
	    elseif ($_.ToString().Trim().Length -eq  0 )
		{$false}
	    else {$true}} )]
            [String]$str
          ) 

$str

 

If run with blank white sure it throws an error

PS C:\Powershell> .\powershell.ps1 ” ”
C:\Powershell\powershell.ps1 : Cannot validate argument on parameter ‘str’.
The ” IF( $_ -eq $null)
{$false }
elseif ($_.ToString().Trim().Length -eq 0 )
{$false}
else {$true}” validation script for the argument with value ”
” did not return true. Determine why the validation script
failed and then try the command again.
At line:1 char:19
+ .\powershell.ps1 ” ”
+ ~~~~~~~~~~~~~~~~~~~~~~~~~~
+ CategoryInfo : InvalidData: (:) [powershell.ps1], ParameterBind
ingValidationException
+ FullyQualifiedErrorId : ParameterArgumentValidationError,powershell.ps1

PS C:\Powershell> .\powershell.ps1  test1
test1