MySQL on Linux – Checking Log files

Background

I am am On-Call this week.

As we have a System Reconfiguration slated for late hours when changes that includes system downtime are often scheduled to occur, my manager checked-in with me to see whether I was OK being assigned the Task.

I said Yes, and so here I find myself gauging my actual readiness.

 

On-Call Check Preparedness

Our team lead has written a very nice detailed document that covers each database platform( DB/2, Oracle, MySQL on Linux and SQL Server on Windows).

 

Outline

As all Databases that run on Linux have pretty much the system administrative steps, here is a condense straightforward list:

  1. Initiate SSH to Linux Host
  2. Authenticate
  3. sudo to DB Engine User
  4. Connect to DB Instance
  5. Change context to problematic database
  6. Review Error Logs
  7. Review System Metrics ( Disk / Memory / and CPU )

 

MySQL on Linux

SSH to Linux

Putty

  1. Hostname = Hostname
  2. Port = 22
  3. Connection Type :- SSH

Image

 

Authenticate

Steps

  1. Login as :- <username>
  2. Password :- <password>

Image

Authenticated

Image

 

Confirm user running mysql

Code


ps aux | grep 'sql' | grep -v 'grep'

Output

Explanation

  1. There are two mysql processes
    • /usr/bin/mysql_safe
      • user :- root
    • /usr/sbin/mysqld
      • user :- mysql
      • daemon process :- mysqld
      • basedir :- /usr
      • datadir :- /data/mysql
      • user :- myssql/err_log/mysql-err.log
      • port :- 3306
  2. What is the difference between the two processes
    • /usr/bin/mysql_safe
      • It is the bootstrap process
      • It ensures that mysqld is running and restarted if knocked down
    • /usr/sbin/mysqld
      • Actual mysql daemon process

sudo to DB Engine User

We are interested in the actual daemon and so we will target mysql.

sudo command

Syntax

sudo su - [account]

Code

sudo su - mysql

Output

Entered password

 

validate sudo user

Syntax

env | grep "USER"

Output

Connect to mysql

Code


mysql -h localhost

Output

 

Get Configuration Information – log file

Code


show global variables like '%log%'

Output

Explanation

variable_name value meaning
general_log OFF Debug Mode. Should only be used during brief troubleshooting sessions.  Logs all connections and query submissions.
general_log_file /logs/mysql/mysqld.log  Log file
log OFF  In MySQL v5.1.29, deprecated in favor of general-log
log_error  /logs/mysql/err_log/mysql-err.log Errors and Startup messages
log_output FILE This option determines the destination for general query log and slow query log output. The option value can be given as one or more of the words TABLE, FILE, or NONE. TABLE select logging to the general_log and slow_log tables in the mysql database as a destination. FILE selects logging to log files as a destination. NONE disables logging. ( Link )
log_queries_not_using_indexes OFF To include queries that do not use indexes for row lookups in the statements written to the slow query log
log_throttle_queries_not_using_indexes OFF  To include queries that do not use indexes for row lookups in the statements written to the slow query log, enable the log_queries_not_using_indexessystem variable. When such queries are logged, the slow query log may grow quickly. It is possible to put a rate limit on these queries by setting the log_throttle_queries_not_using_indexes system variable. By default, this variable is 0, which means there is no limit. Positive values impose a per-minute limit on logging of queries that do not use indexes. The first such query opens a 60-second window within which the server logs queries up to the given limit, then suppresses additional queries. If there are suppressed queries when the window ends, the server logs a summary that indicates how many there were and the aggregate time spent in them. The next 60-second window begins when the server logs the next query that does not use indexes.
( Link )
log_slow_queries OFF  Slow query log.
Deprecated in favor of slow_query_log ( 5.1.29 ).
Please see here and here.
log_warnings 2  Print out warnings such as Aborted connection… to the error log. This option is enabled (1) by default. To disable it, use –log-warnings=0. Specifying the option without a level value increments the current value by 1. Enabling this option by setting it greater than 0 is recommended, for example, if you use replication (you get more information about what is happening, such as messages about network failures and reconnections). If the value is greater than 1, aborted connections are written to the error log, and access-denied errors for new connection attempts are written.
( Link )
long_query_time 2  If a query takes longer than this many seconds, the server increments the Slow_queries status variable. If the slow query log is enabled, the query is logged to the slow query log file. This value is measured in real time, not CPU time, so a query that is under the threshold on a lightly loaded system might be above the threshold on a heavily loaded one. The minimum and default values of long_query_time are 0 and 10, respectively.
slow_query_log OFF Whether the slow query log is enabled. The value can be 0 (or OFF) to disable the log or 1 (or ON) to enable the log.

The destination for log output is controlled by the log_output system variable; if that value is NONE, no log entries are written even if the log is enabled.

slow_query_log_file /logs/mysql/slow-queries.log  File name to log Slow query

 

 

More Concise Queries

Query -01

show global variables where variable_name in 
( 
      'general_log'
    , 'general_log_file'
    , 'log_error'
    , 'log_warnings'
    , 'long_query_time'
    , 'slow_query_log'
    , 'slow_query_log_file'
);

Output -01

View Log files

mysql_err.log

tail


tail /logs/mysql/err_log/mysql-err.log

output

Explanation

  1. Not much bad going on

 

References

  1. mysql
    • Show Variables
      • Log Destinations
        Link
      • Show Variables
        Link
    • Server System Variables
      • System Server Variables
        Link
    • log Warnings
      • MySQL 5.7 Reference Manual / .. / Communication Errors and Aborted Connections // B.5.2.11 Communication Errors and Aborted Connections
        Link
    • General Query & Slow Query Log Output
      • 5.4.1 Selecting General Query and Slow Query Log Output Destinations
        Link
    • log_slow_queries
      • server-options.html#option_mysqld_log-slow-queries
        Link
    • Slow Query Log
      • MySQL 5.7 Reference Manual /Slow Query Log
        Link
  2. mySQL VS mysqld
    • How to start mysqld through mysqld_safe
      Link
  3. pontikis.net
    • How and When To Enable MySQL Logs
      Link
  4. Unix Commands
    • ps
      • nixcraft
        • Warning: bad syntax, perhaps a bogus ‘-‘? See /usr/share/doc/procps-3.2.7/FAQ
          Link
        • Quick Shell Tip: Remove grep command while grepping something using ps command
          Link
    • sudo
      • StackExchange
        • Where does sudo get the currently logged in username from?
          Link

SQL Server – Image and Text Data – Day 1

Background

Experienced a SQL Server Instance crash on Tuesday night.

Yes, there is very little reason for SQL to crash.

 

Sql Error & Crash Logs

We have collected the SQL Server Instance crash logs and will post about them at a later time.

 

What Precipitated the Crash

Glad you asked.  As an aside there was really nothing in the logs that contains nuggets as to why the Instance crashed.

And, so happy that the lady that was running a Job reached out to us within the first hour of the next day.

I asked her for a quick write-up and she shared that she was loading data into one of the tables.  The data that was being loaded included PDF Images.

 

Table Definition

Here is the table’s structure


CREATE TABLE [dbo].[ten95C](
	[ten95C_id] [int] IDENTITY(1,1) NOT NULL,
	[ten95C_ssn] [char](9) NOT NULL,
	[ten95C_emp_id] [char](9) NOT NULL,
	[ten95C_emp_loc] [char](2) NOT NULL,
	[ten95C_tax_year] [smallint] NOT NULL,
	[ten95C_showable] [char](1) NULL,
	[ten95C_view_cnt] [int] NULL,
	[ten95C_view_last] [datetime2](6) NULL,
	[ten95C_notes] [varchar](50) NULL,
	[ten95C_create_ts] [datetime2](0) NOT NULL,
	[ten95C_pdf] [varbinary](max) NOT NULL,
        CONSTRAINT [pk_ten95C_id] PRIMARY KEY CLUSTERED 
        (
	   [ten95C_id] ASC
        ) 
        ON [PRIMARY]
) 
ON [PRIMARY] 
TEXTIMAGE_ON [PRIMARY]

GO

ALTER TABLE [dbo].[ten95C] ADD  DEFAULT ('') FOR [ten95C_ssn]
GO

ALTER TABLE [dbo].[ten95C] ADD  DEFAULT ((0)) FOR [ten95C_view_cnt]
GO

Explanation

Here is a quick structural overview:

  1. The Image Column is defined as [ten95C_pdf] [varbinary](max) 
    • Because FileStream is not explicitly noted we are storing images using the classic method
  2. The table itself is saved on the Primary File Group
  3. The Text Image is also saved on the Primary File Group

 

Table Metrics

Using SSMS and choosing digging deep into the Table, here is what the “Table Properties” look like

tableproperties-dbo-ten95c

 

Explanation

Quick Notes

  1. Again both Table and Text Filegroup is Primary
  2. Data Space is 81, 499 MB or 82 GB
  3. Index is 7.875 MB
  4. Row count is 147112 or 150 thousand

 

Optimization Choices

There are a couple of alternate pathways that we can evaluate to potentially achieve better performance.

  1. Storage
    • Currently, data, indexes, and LOB data are all saved in the same file groups
    • We should consider placing them on different filegroups .  And, targeting those filegroups in on their own physical drives
    • Also, will using File Streams offer better performance
  2. Client End Tooling
    1. The front-end tool is Java.
    2. And, the DB interface is Hibernate
    3. Will revisit optimization choices for Hibernate in a later post

 

Approach

In this post, we will go with creating a new file group dedicated to LOB data.

 

FileGroup

FileGroup – Add file groups


USE [master]
GO

ALTER DATABASE [rbpivr1_20161111]
	ADD FILEGROUP [FileGroupIndexes]
GO

ALTER DATABASE [rbpivr1_20161111] 
	ADD FILEGROUP [FileGroupTextAndImage]
GO

 

 

FileGroup – Add files to new file groups

FileGroup – Add files to new file groups – Indexes


USE [master]
GO

exec xp_create_subdir N'Z:\Microsoft\SQLServer\Indexes\rbpivr1\'
go

ALTER DATABASE [rbpivr1_20161111] 
ADD FILE 
	( 
		  NAME = N'rbpivr1_indexes_01'
		, FILENAME = N'Z:\Microsoft\SQLServer\Indexes\rbpivr1\rbpivr1_indexes_01.ndf' 
		, SIZE = 4096KB 
		, FILEGROWTH = 200MB 
	) 
	TO FILEGROUP [FileGroupIndexes]

GO

 

FileGroup – Add files to new file groups – TextAndImage


USE [master]
GO

exec xp_create_subdir N'Z:\Microsoft\SQLServer\TextAndImage\rbpivr1\'
go

ALTER DATABASE [rbpivr1_20161111]
ADD FILE
(
NAME = N'rbpivr1_text_image_01'
, FILENAME = N'Z:\Microsoft\SQLServer\TextAndImage\rbpivr1\rbpivr1_TextAndImage_01.ndf'
, SIZE = 4096KB
, FILEGROWTH = 200MB
)
TO FILEGROUP [FileGroupTextAndImage]

GO

Table

Let us create new table and be sure to target the indexes and text column on the Index FileGroup and Image & Text file group accordingly.

SET ANSI_NULLS OFF
GO

SET QUOTED_IDENTIFIER ON
GO

if schema_id('fgTI') is null
begin

	exec('create schema [fgTI] authorization [dbo]')

end
;

if object_id('[fgTI].[ten95C]') is null
begin

	CREATE TABLE [fgTI].[ten95C]
	(
		[ten95C_ssn] [char](9) NOT NULL,
		[ten95C_emp_id] [char](9) NOT NULL,
		[ten95C_emp_loc] [char](2) NOT NULL,
		[ten95C_tax_year] [smallint] NOT NULL,
		[ten95C_showable] [char](1) NULL,
		[ten95C_view_cnt] [int] NULL,
		[ten95C_view_last] [datetime2](6) NULL,
		[ten95C_notes] [varchar](50) NULL,
		[ten95C_create_ts] [datetime2](6) NOT NULL,
		[ten95C_pdf] [varbinary](max) NOT NULL

		, CONSTRAINT [pk_ten95C] PRIMARY KEY CLUSTERED 
		(
			[ten95C_ssn] ASC,
			[ten95C_emp_id] ASC,
			[ten95C_emp_loc] ASC,
			[ten95C_tax_year] ASC,
			[ten95C_create_ts] ASC
		)
		WITH 
		(
			  PAD_INDEX = OFF
			, STATISTICS_NORECOMPUTE = OFF
			, IGNORE_DUP_KEY = OFF
			, ALLOW_ROW_LOCKS = ON
			, ALLOW_PAGE_LOCKS = ON
		) 
		ON [PRIMARY]
	
		, CONSTRAINT [ten95C_tax] UNIQUE NONCLUSTERED 
		(
			[ten95C_emp_id] ASC,
			[ten95C_emp_loc] ASC,
			[ten95C_tax_year] ASC,
			[ten95C_create_ts] ASC
		)
		WITH 
		(
			  PAD_INDEX = OFF
			, STATISTICS_NORECOMPUTE = OFF
			, IGNORE_DUP_KEY = OFF
			, ALLOW_ROW_LOCKS = ON
			, ALLOW_PAGE_LOCKS = ON
		) 
		--ON [PRIMARY]
		ON [FileGroupIndexes]

	) 
	ON [PRIMARY] 

	--By default Text and Image and saved on FileGroup- Primary
	--TEXTIMAGE_ON [PRIMARY]
	TEXTIMAGE_ON [FileGroupTextAndImage]

	ALTER TABLE [fgTI].[ten95C] 
		ADD  DEFAULT ('') FOR [ten95C_ssn]

	ALTER TABLE [fgTI].[ten95C] 
		ADD  DEFAULT ((0)) FOR [ten95C_view_cnt]

end
go

Explanation

  1. We kept the Primary Key and accordingly the table in the primary file group
  2. Our lone index is targeting the newly created FileGroupIndexes filegroup
  3. And, the TextImage_On is targeting FileGroupTextAndImage

 

 

Transition Data

To transition data we have a lot of options, such as :

  1. ETL Tools
    • SSIS anyone
  2. BulkCopy
    • Bulk Insert
    • BCP
  3. Transact SQL
    • Merge Statement

 

Transact SQL – Merge Statement


use [rbpivr1]
go

set XACT_ABORT on
set nocount on

declare @id int
declare @idLen int
declare @idAsString varchar(60)
declare @spaceLength varchar(10)
declare @BLOCK_LENGTH_OF_SPACE tinyint
declare @idLow bigint
declare @idHigh bigint


declare @idLowBlock bigint
declare @idHighBlock bigint
declare @iBlockSize bigint
declare @commit bit

declare @log varchar(600)

set @idLow = 1
set @idHigh = 1E4

set @BLOCK_LENGTH_OF_SPACE = 4

set @idLowBlock = @idLow
set @iBlockSize = 1E3
set @commit =1

truncate table  [rbpivr1_20161111].[fgTI].[ten95C];

begin tran

	delete 
	from [rbpivr1_20161111].[fgTI].[ten95C]

	set identity_insert [rbpivr1_20161111].[fgTI].[ten95C] on
	
	set @id =1
	
	while (
			(@idLowBlock <= @idHigh) ) begin set @idHighBlock = @idLowBlock + @iBlockSize set @idLen = len(@id) set @spaceLength = @BLOCK_LENGTH_OF_SPACE - @idLen set @idAsString = replicate(' ', @spaceLength) + cast(@id as varchar(10)) set @log = 'Retrieving data from ' + @idAsString + ') ' + cast(@idLowBlock as varchar(10)) + ' ' + cast(@idHighBlock as varchar(10)) print @log merge [rbpivr1_20161111].[fgTI].[ten95C] tblTarget using ( select [ten95C_id] ,[ten95C_ssn] ,[ten95C_emp_id] ,[ten95C_emp_loc] ,[ten95C_tax_year] ,[ten95C_showable] ,[ten95C_view_cnt] ,[ten95C_view_last] ,[ten95C_notes] ,[ten95C_create_ts] ,[ten95C_pdf] from [rbpivr1].[dbo].[ten95C] tbl95C with (nolock) where ( ( tbl95C.[ten95C_id] between @idLowBlock and @idHighBlock) ) ) tblSource ON ( ( tblTarget.[ten95C_id] = tblSource.[ten95C_id]) ) WHEN NOT MATCHED BY TARGET THEN INSERT ( [ten95C_id] ,[ten95C_ssn] ,[ten95C_emp_id] ,[ten95C_emp_loc] ,[ten95C_tax_year] ,[ten95C_showable] ,[ten95C_view_cnt] ,[ten95C_view_last] ,[ten95C_notes] ,[ten95C_create_ts] ,[ten95C_pdf] ) VALUES ( tblSource.[ten95C_id] ,tblSource.[ten95C_ssn] ,tblSource.[ten95C_emp_id] ,tblSource.[ten95C_emp_loc] ,tblSource.[ten95C_tax_year] ,tblSource.[ten95C_showable] ,tblSource.[ten95C_view_cnt] ,tblSource.[ten95C_view_last] ,tblSource.[ten95C_notes] ,tblSource.[ten95C_create_ts] ,tblSource.[ten95C_pdf] ) ; set @log = 'Posted data' + @idAsString + ') ' + cast(@idLowBlock as varchar(10)) + ' ' + cast(@idHighBlock as varchar(10)) print @log set @idLowBlock = @idHighBlock set @id =@id +1 end ---block work while (@@TRANCOUNT > 0)
begin

	if (@commit = 1)
	begin
		print 'commit'
		commit tran
	end

	if (@commit = 0)
	begin
		print 'rollback'			
		rollback tran
	end

end

set identity_insert [rbpivr1_20161111].[fgTI].[ten95C] off;


 

Introspection

Let us compare our original design with the slight modification we made

 

Table Allocation Sizes


declare @tableName sysname

set @tableName = 'ten95C'

; with cte
(
	  	  [objectName]
		, [index_id]
		, [index_name]
		, [filegroup]
		, [fileGroupType]
		, [allocationUnitType]
		, [data_space_id]
		, [numberOfPages]
		, [totalPagesInMB]
		, [usedPagesInMB]
		, [dataPagesInMB]

)
as
(
	SELECT 

		[objectName]
		=   quoteName(tblSS.[name])
		  + '.'
		  + quoteName(tblSO.[name])

		, [index_id]
			= PA.index_id

		, [index_name]
			= tblSI.[name]

		, [filegroup]
			= FG.[name] 

		, [fileGroupType]
			= FG.[type_desc]

		, [allocationUnitType]
			= AU.[type_desc]

		, FG.[data_space_id]

		, [numberOfPages]
			= (sum(AU.[total_pages]))

		, [totalPagesInMB]
			= (sum(AU.[total_pages]) * 8 ) / 1024

		, [usedPagesInMB]
			= (sum(AU.[used_pages]) * 8) / 1024

		, [dataPagesInMB]
			= (sum(AU.[data_pages] * 8 ) / 1024)

	FROM sys.filegroups FG 

	INNER JOIN sys.allocation_units AU 
		ON AU.data_space_id = FG.data_space_id 

	INNER JOIN sys.partitions PA 
		ON PA.partition_id = AU.container_id 

	INNER JOIN sys.objects tblSO
		ON PA.object_id = tblSO.object_id


	INNER JOIN sys.indexes tblSI
		ON  PA.object_id = tblSI.object_id
		AND PA.index_id = tblSI.index_id

	INNER JOIN sys.schemas tblSS
		ON tblSO.schema_id = tblSS.schema_id

	WHERE  tblSO.[type] = 'U'

	and    tblSO.[name] = isNull(@tableName, tblSO.[name])
	
	group by

		quoteName(tblSS.[name])
		  + '.'
		  + quoteName(tblSO.[name])

		, PA.index_id

		, tblSI.[name]

		, FG.[name] 

		, FG.[type_desc]

		, AU.[type_desc]

		, FG.[data_space_id]

)
select 

		  [objectName]
		--, [index_id]
		, [index_name]
		, [filegroup]
		--, [fileGroupType]
		, [allocationUnitType]
		--, [data_space_id]
		, [numberOfPages]
		, [totalPagesInMB]
		, [usedPagesInMB]
		, [dataPagesInMB]

from   cte

order by
		[index_id]

Output

Original

tableproperties-dbo-ten95c

Revised

tableproperties-fgti-ten95c

Explanation
  1. In our original design
    • All data is targeted at the primary FileGroup
  2. In the revised design
    • LOB Data
      • Is the biggest storage hoarder, but now it is in its own FileGroup [fileGroupTextAndImage]
    • PRIMARY
      • Regular table data and the Primary Key is all that remains on the PRIMARY file group
    • Indexes
      • Our two indexes are placed next to each other in the Index FileGroup

Summary

This is Day 1 and so we will only deep into our LOB (Image) datatype.

A lot of these posts are just to disarm areas we do not deal with everyday.

Why you can’t be a good .Net Developer // Postgres and MySQL

Day Jobs

Day jobs aside, there are some people that have had a thing or two to say in response to an Industry’s rant on “Why you can’t be a good .Net developer“.

Here are some of them….

  1. Enrico, Italian programmer in Sweden.
    On Being a Good .NET Developer
    Link
  2. Ted Neward
    It is too possible
    Link
  3. Ayende @ Rehein
    Why you can’t be a good .Net Developer
    Link

 

Ayende Rahien & Oren Eini

I read through a couple of Oren Eini’s other postings and found out his take on Databases quite promising.

BTW, he blogs under the pseudonym, Ayende Rahien.

 

Evan Klitzke

In a recent post, Ayende summarizes and reflects on Evan Klitzke’s post on “Why Uber Engineering switched from Postgres to MySQL“.

 

  1. WHY UBER ENGINEERING SWITCHED FROM POSTGRES TO MYSQL
    2016-07-26
    Link
  2. Migrating Uber from MySQL to PostgreSQL
    2013-03-13
    Link

Back to Real

Enrico’s Take

“I never compromise on excellence. It’s just that with some teams, the way to get there is longer than with others.

To me the solution isn’t to run away from beginners. It’s to inspire and mentor them so that they won’t stay beginners forever and instead go on to do the same for other people. That applies as much to .NET as it does to any other platform or language.

If you aren’t the type of person who has the time or the interest to raise the lowest common denominator, that’s perfectly fine. I do believe you’re better off moving somewhere else where your ambitions aren’t being held back by inexperienced team members. As for myself, I’ll stay behind — teaching.”

Informatica – PowerCenter Express – Datatype – XML

Background

As I dig more into Informatica and MySQL, found out that we were not properly copying XML column from SQL Server into MySQL.

 

Table Structure

Here is our table structure.

MSSQL

Admin-CourseManagerUserLog-MSSQL

MySQL

Admin-CourseManagerUserLog-MYSQL

 

Informatica

Original Table Structure

Physical Data Object – Admin.CourseManagerUserLog

PhysicalDataObject

Explanation:

Later on, was cognizant that our XML Column, details, precision or size is 0

 

Mappings

Mappings

Explanation:

Later on, noticed that for the details column, the arrow is not full.

 

Mappings - Later

 

Run Mappings

Run the mapping.

And, things ran well.  Was not until later that I found that the details column on the destination, MySQL, was empty.

 

Create View on the Source

Here is a workaround.

In the view, add new columns to track the unsupported XML Column.

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

if object_id('[Admin].[CourseManagerUserLog_XMLColumnConverted]') is null
begin

	exec('create view [Admin].[CourseManagerUserLog_XMLColumnConverted] as select 1/0 as [shell] ')

end
go

alter view [Admin].[CourseManagerUserLog_XMLColumnConverted]
as

SELECT
		    [id]
		  , [username]
		  , [resourceID]
		  , [status]
		  , [message]
		  , [details]
		  , [detailsAsVarcharMax]
				= cast(	[details] as varchar(max))	
		  , [detailsAsVarchar]
				= cast(	[details] as varchar(8000))	
		  , [userID]
		  , [refID]
		  , [record_created]
		  , [appointmentID]

from   [Admin].[CourseManagerUserLog]
GO

GRANT SELECT ON [Admin].[CourseManagerUserLog_XMLColumnConverted] TO [public]

go

 

Informatica

Resource

Resource

Explanation

  1. details, XML Column, comes in xml.  But, size is 0
  2. detailsAsVarchar, varchar column, size is 8000 ( max size in MS SQL Server )
  3. detailsAsVarcharMax [varchar(max) ], size is 0, as well

 

Mappings

After AutoLink

After AutoLink ….

Mappings-After-Autolink

Those columns with matching names are Auto Linked.

 

After Column – detailsAsVarchar is linked to details

And, then we manually linked the columns whose names are not matched.

Here in:

  1. detailsAsVarchar (MSSQL) right-arrow details (MySQL)

Mappings - Later - Details - 0448PM

MySQL – Datatype – Data Definition Language (DDL) – GUIDs

Forward

Unfortunately, MySQL does not currently have a native datatype support for GUIDs.

Some of the other RDBMS, have concise native support for GUIDs.

i.e MSSQL has uniqueIdentifier.

 

Lab

From Googling, here is a workable workaround.

DDL

Create Table – datatypeGUID.person

Outline

  1. GUIDs are 32 characters plus the separating hyphens ( – )

Code



create schema if not exists datatypeGUID;

drop table if exists datatypeGUID.person;

create table if not exists datatypeGUID.person
(

	  personID 			varchar(36) not null
      
    , `name`			varchar(120) not null
    , dateofBirth		datetime null
    , gender            char(1) null

	-- Insert
	, addedBy   		varchar(120) not null
							-- default  SESSION_USER()
                            
    , dateAdded         datetime not null
							default CURRENT_TIMESTAMP
                            
	-- Update
	, modifiedBy   		varchar(120) null
                            
    , dateModified      datetime null
    
    , CONSTRAINT `PK_DatatypeGUID.person`
         PRIMARY KEY  CLUSTERED
	 (
	   personID
         )
        
   , UNIQUE `uniqueName`		
	(
	    `name`
        )
        
)
;

DDL – Trigger

Outline

  1. Unfortunately, MySQL does not allow one to use functions when defining column defaults.
  2. And, so one has to regulate those definitions to Triggers

Code

DDL – Trigger – datatypeGUID.TR_datatypeGUID.person_Insert

DROP TRIGGER IF exists datatypeGUID.`TR_datatypeGUID.person_Insert`;

DELIMITER $$

CREATE TRIGGER datatypeGUID.`TR_datatypeGUID.person_Insert`
BEFORE INSERT ON datatypeGUID.person
FOR EACH ROW
BEGIN

	/*
		If Person ID is null, then set uuid()
	*/		
	if 	( NEW.personID is null ) then
        
		set NEW.personID = uuid();
            
	end if;

	/*
		If Added By is null, then set to current system user
	*/		
	if 	( NEW.addedBy is null ) then
        
		set NEW.addedBy = USER();
            
	end if;     
    
END;    
$$

delimiter ;



DML

DML – Add Data

Code


truncate table datatypeGUID.person;

insert into datatypeGUID.person
(
	`name`, dateofBirth, gender
)
/*
	Jim Jackson (basketball) - Wikipedia, the free encyclopedia
	James Arthur Jackson (born October 14, 1970) is an American retired professional basketball player. 
*/    
select 'Jimmy Jackson', '1970-10-14', 'M'
union
/*
    Kendall Cedric Gill (born May 25, 1968) is an American retired professional basketball player, who now works as a television basketball analyst.
    https://en.wikipedia.org/wiki/Kendall_Gill
*/
select 'Kendall Cedric Gill', '1968-05-25', 'M'


DML – Query Data

Code


select *
from   datatypeGUID.person

Output

getData

Informatica – PowerCenter Express – Data Sync – MSSQL to MySQL ( Using MySQL ODBC Driver )

Background

In our last post we spoke about copying data from MSSQL to MySQL via using MySQL JDBC Driver.

In this post, we will cover using an ODBC Driver.

 

Lab

ODBC Drivers

Availability

MySQL’s ODBC Driver is available here.

As of June 28th, 2016, the current MySQL ODBC Driver is 5.3.6.

Our targeted machine’s bitness is 64 bit, and we will reach for 64-bit MSI Installer.

DownloadConnectorODBC-GeneralRelease

Install

Please install the downloaded installer.

Wizard

Welcome

License Agreement

LicenseAgreement

 

Setup Type – Custom

SetupType

Setup Wizard

Drive C:

ChangeCurrentDestinationFolder-DriveC

 

Drive D:

ChangeCurrentDestinationFolder-DriveD

Custom Setup

CustomSetup

Wizard Completed

WizardCompleted

 

Administrate

Please install the downloaded installer.

Configure New Data Source

Let us configure a new Data Source

Review List of Installed Drivers

We want to confirm that the MySQL ODBC N.M Drivers from Oracle are installed.

To do so, we will access the Drivers Tab.

Drivers

 

User DSN

Current List

Here are the currently configured User Data Sources

UserDSN-Before

Add New Data Source

Add a new Data Source by clicking the Add button

Create New Data Source

The “Create New Data Source” window appears and the list of drivers is displayed…

CreateNewDataSource - SelectADriver

Please select “MySQL ODBC 5.3 ANSI Driver“.

 

Create New Data Source

Please specify a name for the Data Source, the DB Server’s Hostname and MySQL Port Number.

As well, as the user credentials.

MySQLConnector-ODBC

Validate Connectivity

Click the “Test” button to validate connectivity.

MySQLConnector-ODBC-TestConnection

List – Upon DSN Creation

Once the DSN is created, it should show up in the list of “User Data Sources”.

UserDSN-After

 

Informatica

Register DSN

Let us register the created DSN with Informatica

Preferences

Access Preferences via the menu item Window\Preferences

Connections – Available Connections

On the left panel, Access Connections option
And, on the right panel, access Databases, ODBC
Click on the Add button

Preferences-Connections

Database Connection

  1. Name :- DBDevMySQLNative_x64_User
  2. ID :- DBDevMySQLNative_x64_User
  3. Type :- ODBC

 

DatabaseConnection

Connection Details

  1. User Name
  2. Password
  3. Connection String  ( Name of the created DSN )

ConnectionDetails

 

Relational Data Object

Let us create a new Relational Data Object and bind it to our created Data Source

Select a Wizard

Here we choose to create a “Relational Data Object”.

SelectAWizard - RelationalDataObject

Choose Connections – Available Connections

And, we choose our ODBC Data Source.

ChooseConnection

Relational Data Object – Completed

Upon choosing the Data Source Connection, Resource, and specifying an indicative name we end up with the scree shown below.

RelationalDataSource-Completed

 

Mapping

Create a new mapping

CreateAMapping

 

Add Mapping – Source

AddSource

 

Add Mapping – Destination

AddDestination

 

AutoLink

Initiate AutoLink

AutoLink-Begin

AutoLink – Source Selected

AutoLink-Middle

AutoLink – Destination Selected

AutoLink-Apply

Click OK button to apply AutoLink

Validate Mapping

Validate Mapping

Mapping-Validation

 

Errors

Errors – ODBC Driver Manager/Configuration

If the steps above are not taken there are a couple of bobby traps one might run into.

32-Bit ODBC Drive or System ODBC DSN

If one tries to use a 32 bit ODBC DSN or one tries to use a DSN created as a System DSN and not a User DSN, you might get the error noted below.

Textual

Error returned during ODBC access.

  1. ErrorCode :- -1
  2. Reason :- [Microsoft][ODBC Driver Manager] The specified DSN contains an architecture mismatch between the Driver and application.

 

Image

TestConnection-ConnectionFailed-x64-ArchitectureMismatch

 

Connection Failed – Invalid JDBC connection string Missing dbURL

While creating a new Database Connection within Informatica Preferences we received the error pasted below.

Note that the error is that we had not yet entered the name of the ODBC Data Source in the “Connection String” entry box.

Textual

Invalid JDBC connection string Missing dbURL.

Image

InvalidJDBCConnectionStringMissingdbURL

 

Implication

It is interesting to note that though we are creating an ODBC Connection, the error message reads “Invalid JDBC connection string…“.

The implication is thus that the same codeline is used for JDBC/ODBC; ODBC likely relies on the JDBC/ODBC Bridge.

 

Errors – Designer

Schema – MySQL – Nullable Primary Key

Somehow our primary key, id, came back as nullable

PrimaryKeyContainsNullableColumn

Error

Textual

Primary Key [PRIMARY] contains nullable column [id].

Image

PrimaryKeyContainsNullableColumn-cropped

Correction

Toggle the Nullable checkbox besides the Primary Key column, id.

DataStructure-After

 

Informatica – PowerCenter Express – Data Sync – MSSQL to MySQL ( Using MySQL JDBC Driver )

Background

A dated follow-up to our post on using Informatica – PowerCenter Express.

In that post we copied data between two MS SQL Server Instances.

In this post we will copy data from SQL Server to MySQL.

 

Outline

Here is a brief outline of the steps we will take:

  1. Select a Driver to use to communicate with MySQL
  2. If Driver is not yet downloaded, please do so
  3. Deploy Driver
  4. Configure Informatica Client & Server with newly installed driver
  5. Launch Informatica Desktop
  6. Design Project
    • Create new project
    • Physical Data Object
      • Create Physical Data Object to Source ( MS SQL Server )
      • Create Physical Data Object to Source ( MySQL )
    • Create Mapping
      • Link Data Sources to ( Source and  Destination )
    • Validate Mapping
    • Run Mapping

Lab

Select Driver to Connect to MySQL

We have to decide whether we want to connect to MySQL using an ODBC or JDBC Driver.

We chose to go with the Vendor’s own JDBC Driver.

 

Download Driver

Availability

The MySQL Java/JDBC Driver is available here.

DownloadConnectorJ-0606PM

 

OS Platform

As we are installing on MS Windows, and not Unix, we chose to go with Zip file packages, as opposed to Tar, if we were on Unix.

 

BeginYourDownload

 

Deploy Driver

Copy Jar file to the following locations

  1. <Informatica Home>\PCExpress\externaljdbcjars
    • Used by Server
  2. <Informatica Home>\PCExpress\client\externaljdbcjars
    • Used by Client

 

In our case, we copied the extracted mysql-connector-java-5.1.39-bin.jar to:

  1. D:\Informatica\PCExpress\client\externaljdbcjars
  2. D:\Informatica\PCExpress\externaljdbcjars

Configure Informatica Client & Server with MySQL Driver

Configure Informatica Server

Launch

From the MS Windows desktop menu, access Start \ Informatica PowerCenter Express \ Launch Informatica Administrator.

 

Login

The default browser opens in a new tab.  Our URL is http://localhost:7009/administrator/

InformaticaAdministratorUserAuthentication

 

Domain Navigator

The URL for local Domains appear.  That URL is http://dbdev:7009/administrator/#admin/Domain_Domain_localhost

Connections-New-Connection-Cropped

 

New Connection – Choose to Create New Connection

Access the “Domain” \ “Connections” tab.

And, through the “Actions” link drop down, select New \ Connection

Connections-AccessMenuActionNewConnections

 

 

New Connection – Select a connection type

Once New \ Connection is selected from the dropdown menu, the “New Connection” panel is displayed.

 

NewConnection-SelectAConnectionType

 

New Connection – Step 1 of 2

Here is our user credentials and MySQL JDBC Driver Class Name and MySQL Hostname and Port number.

  1. Username :- xferClient
  2. Password :- Enter password
  3. JDBC Driver Class Name :- com.mysql.jdbc.Driver
  4. Connection String :- jdbc:mysql://DBDEV:3306

 

 

NewConnection-Step1of2

 

New Connection – Test Connection

NewConnection-ConnectionTestedSuccessfully

New Connection – Step 2 of 2

NewConnection-Step2of2

 

 

Configure Informatica Developer

Launch

Let us configure Informatica Developer with our newly downloaded JDBC Driver.

If the Developer has not be launched, please do so now.

Access Preferences

Access the Preferences menu by navigating the menu – Window\Preferences.

Winddow-Preferences

 

Informatica \ Connections

Using the tree menu on the left panel, access Informatica \ Connections

 

Preferences-Connections-Before

 

Add new JDBC Connection

In the Connections window, click the Add button to add a new JDBC Connection

  1. Username :- xferClient
  2. Password :- Enter password
  3. JDBC Driver Class Name :- com.mysql.jdbc.Driver
  4. Connection String :- jdbc:mysql://DBDEV:3306

ConnectionDetails-ConnectionDetailsCompleted

 

Design Project

Launch Informatica Developer if not already running.

Create New Project

Connect To Repository

Using the Object Explorer, select and connect to the ModelRepository.

InformaticaDeveloper

Create New Project

Create a new project by using the menu items – File \ New \ Project

NewProject

 

Name Project

In the “New Project” window, enter the name you will like to give to the Project.

And, review and change the “Repository Service” if there is one.

CreateANewProject

 

Data Object – Source

Create Data Object

Right click on the Project and select New \ Data Object

New-DataObject

 

Select a wizard

From the “Select a wizard” window, please select “Relational Data Object”

SelectAWizard-RelationalDataObject

 

Relational Data Object – Select a connection

The “Relational Data Object” window appears and the user is prompted “Select a connection

RelationalDataObect-SelectAConnection

 

 

Relational Data Object – Choose Connection

The “Choose Connection” window appear ….

RelationalDataObect-ChooseConnection

 

Relational Data Object – Specify RDO Name

Specify the name of the Relational Data Object.

RelationalDataObect-SelectAResource-IsNextStep

 

Relational Data Object – Select a Resource – Initial

By the “Resource” item, select the Browse button.

Here is the initial contents of the “Select a Resource” window.

RelationalDataObect-SelectAResource-InitialScreen

 

Relational Data Object – Select a Resource – Avail all Schemas

Uncheck the “Show Default Schema Only” checkbox.

Unchecking the “Show Default Schema Only” button, reveals all database objects.

RelationalDataObect-SelectAResource-Ongoing

 

Relational Data Object – Select a Resource – Object Selected

Our object, Admin.ErrorLog, is selected below.

RelationalDataObect-SelectAResource-Ongoing

 

Relational Data Object – New Relational Data Object – Form Filled Out

Here is what things look like once our choices are all filled out.

RelationalDataObect-CreateARelationalDataObject

 

Relational Data Object – Overview

Here is the Overview screen.

It shows the columns and corresponding datatype of those columns.

RelationalDataObect-PostCreation

 

Data Object – Destination

Choose To Create New Physical Data Object

Use the menu item to initiate creation of a new Physical Data Object.

NewPhysicalDataObject-Menu-20160628-1122AM

 

Relational Data Object – Select a connection

We are back at the initial contents of the “New Relational Data Object” screen.

We are being prompted to choose a connection

RelationalDataObject- SelectAConnection

 

Relational Data Object – Choose Connection

Aforementioned, we will like to use JDBC to connect to our target data source.

And, so we will choose the MySQL Data Source that we created earlier.

The name of that Data Source is DBDEVPlatform32JDBC.

RelationalDataObject - ChooseConnection

 

Relational Data Object – Select a resource

With our data source connected, we will go on to select a Resource, the specific Data Object that will be processing on the Target.

RelationalDataObject - SelectAResource

Please click the Browse button to review the list of objects available on the target.

 

Relational Data Object – Select a resource – Initial

Upon the “Select a Resource” window appearing, the resources listed will like be limited

SelectAResource-ShowDefaultSchemaOnly

 

Relational Data Object – Select a resource – “Show Default Schema Only” Unchecked

Once we uncheck “Show Default Schema Only“, we are able to see a fuller list of schemas and objects.

SelectAResource-ShowDefaultSchemaOnly ( Unchecked - Upon)

Relational Data Object – Select a resource – Resource Chosen

We choose the dblab.errorlog table that we created earlier.

SelectAResource-ShowDefaultSchemaOnly ( Unchecked )

Relational Data Object – Completed

Please review the chosen connections, resource, and change the name of the RDO to afford specificality.

RelationalDataObject - Completed

Mapping

Initiate New Mapping

Please initiate New Mapping by clicking on the menu items ( New \ Mapping )

1232PM-NewMapping-Menu

 

Specify New Mapping’s Name

Specify a name that allows us to identify which objects are being mapped

Before

1232PM-Mapping-CreateAMapping-Initial

After

1232PM-Mapping-CreateAMapping-After

 

Mapping Canvas

Empty Canvas

Here is our new empty canvas.

Mapping-Canvas-Empty

 

Add Source

Drag the source data object ( ErrorLog#Source ) from underneath Physical Data Objects\<Database>\<Object> to the canvas.

As this is our source, we will detail that our access will be “Read“.

AddToMapping-Read

 

Add Destination

Drag the destination data object ( ErrorLog#Destination ) from underneath Physical Data Objects\<Database>\<Object> to the canvas.

As we are now working on the destination, we will specify that our access type is for “Write“.

AddToMapping-Read

 

Mapping – Relational Data Objects

Here are the RDO Objects once the are dropped on the canvas.

RelationalDataObjects-BeforeLinking

 

Mapping – Relational Data Object – Initiate Menu

Select the Source Object and right click on it’s menu bar to get an insight of options available for that object.

RelationalDataObjects-Menu-Appear

Please select “Auto Link”.

Mapping – Auto Link
Mapping – AutoLink – Initial

AutoLink-20160628-1241PM

In the screenshot above, we have only selected the Source Object and so the “OK” and “Apply” buttons are disabled.

Mapping – AutoLink – Select Source & Destination

Please select the Source and Destination objects

AutoLink-20160628-1244PM

And, click OK or Apply to effect the changes.

Mapping – Validation

Mapping – Validation – Initiation

Please right click on the empty canvas and select the Validate option from the drop-down menu.

Mappings-Validate ( Cropped )

Mapping – Validation – Completion

If no problems are found, we are told so.

Mappings-Validate-NoProblemsFound

Mapping – Validation – Completion

 

Mapping – Run Mapping

Right click on the canvas, and chose to “Run Mapping

MappingInitiate-20160628-0105PM

Mapping Running

RunMapping

 

Errors

There are some errors to be on the lookout for:

Maximum Number of rows for a 24-hour period

It is possible that you might experience the error pasted below:

Mapping run completed with errors.
[MPSVCCMN_10082] The Mapping Service Module [MappingService] could not run the mapping due to the following error: [[DS_10192]
The Integration Service processed the maximum number of rows for a 24-hour period. Wait until the row limit resets at [2016-06-28 00:00:00.000].].

 

As we are running the Express, Informatica Developer for PowerCenter Express, edition and it is free and licensed for Development purpose, it is not suitable for large datasets.

 

References

cData Software

JDBC Data Sources

  1. Create Informatica Mappings From/To a JDBC Data Source for Cassandra
    Link
  2. Create Informatica Mappings From/To a JDBC Data Source for Marketo
    Link