Elias Chatzigeorgiou

Fixing PRIMARY KEY constraint issues in SQL Server 2005 Replication

2012-08-18T08:47:00.003-07:00

Wednesday, January 7, 2009(copied from: http://bassplayerdoc.blogspot.gr/2009/01/fixing-primary-key-constraint-issues-in.html)

Fixing PRIMARY KEY constraint issues in SQL Server 2005 Replication

I must admit that I had to stay away from replication for quite a while since most of the work I've done in my previous job has a lot to do with disaster recovery. Nonetheless, I'm presented with new challenges every single day which I need to face. I got notified for an error regarding a transactional replication issue between two SQL Server 2005 instances. The error went something like this

Replication-Replication Distribution Subsystem: agent INSTANCE-db-Publication-SUBSCRIBER_INSTANCE-21 failed. Violation of PRIMARY KEY constraint 'PK_Whatever'. Cannot insert duplicate key in object 'dbo.table1'

The error message simply says that replication between the publisher and the subscriber could not push thru due to an existing record in the subscription that is causing a violation of the PRIMARY KEY constraint. Either a record is manually inserted in the subscriber or its just an annoying application which is not properly configured. Here's how you can fix this

Open Replication Monitor and check for the publication that is causing the error. You should see some error messages in the details that display something about the transaction sequence number, publisher id and command id values
Next, you retrieve the command associated with the transaction sequence number using the system stored procedure sp_browsereplcmds. This will return a result set of the replicated commands stored in the Distribution database at the Distributor. The query should look something like this

EXEC Distribution..sp_browsereplcmds @xact_seqno_start = '0x000B5103000043B7000700000000' ,@xact_seqno_end = '0x000B5103000043B7000700000000',@command_id =2 , @publisher_database_id = 1

This will give you an idea on the possible INSERT/UPDATE/DELETE statement that is inside this transaction. Based on the error message, we are looking at either an INSERT or an UPDATE statement.

Next, run a SELECT query on the subscriber instance against the table specified in the subscription passing the values you retrieved from the sp_browsereplcmds system stored procedure.
If the record already exists, simply delete the record since it will be written over by the replication (of course, this is not always the case and would depend on your business requirements)

This is just an overview of what you can do when you see such errors in replication. There are a few more articles and tips to help you get by from this website by Paul Ibison

vSphere Client over SSH Tunnel

2012-02-09T09:30:00.000-08:00

ports 443, 902 and 903 forwarded to the Service Console IP on the host.

--BEGIN OF VMWARE SECRECY--

vshpere would NOT accept either localhost or 127.0.0.X in the login prompt !

you need to add a line to the C:\WINDOWS\system32\drivers\etc\hosts file:

127.0.0.1 esxserver

--END OF VMWARE SECRECY--

for more details, you may check:

TCP and UDP Ports required to access vCenter Server, ESX hosts, and other network components

GRUB2 hangs on "Welcome to GRUB!"

2012-01-04T09:31:00.001-08:00

GRUB2 hangs on "Welcome to GRUB!"

Roberto Bifulco — Sat, 09/11/2010 - 21:42 ( http://robertobifulco.it/node/336 )

edited by Elias Chatzigeorgiou :) — Wed, 04/01/2012 - 19:30 ( http://elias-chatzigeorgiou.blogspot.com/2012/01/grub2-hangs-on-welcome-to-grub.html )

Today i played with debian "squeeze", that is currently under test and is the next Debian release. I encountered some problems while installing GRUB2 using the Debian Installer from a USB pen, infact i had to switch to a kubuntu live edition in order to install grub2 manually to enable the boot procedure without the live media (that in my case was again a USB pen).

Unfortunately on system reboot i found GRUB2 hang on the "Welcome to GRUB!" message. After some research on Google i came with this BUG: http://bugs.debian.org/cgi-bin/bugreport.cgi?bug=594967

The problem is in the GRUB graphical console, so it was sufficient to:

boot the system again through the live distribution

mount the linux partition (e.g. /dev/sda5)

mkdir /mnt/sda5
mount /dev/sda5 /mnt/sda5

mount --bind /dev, /sys and /proc so that you will retain them after the chroot

mount --bind /dev /mnt/sda5/dev
mount --bind /sys /mnt/sda5/sys
mount --bind /proc /mnt/sda5/proc

do the chroot

chroot /mnt/sda5

modify the configuration of GRUB2 (/etc/deafult/grub) to uncomment GRUB_TERMINAL=console

update the GRUB configuration (update-grub)

reboot the system

Booting GRUB from text console solved the problem, and now the system is correctly working.

Here you can found a guide to configure/install GRUB from a live distribution, in case you need it: https://wiki.ubuntu.com/Grub2 (Recover GRUB2 via Live CD paragraph)

otrs -- batch create new customers

2010-08-16T02:49:00.000-07:00

In case you don't know, OTRS is a ticketing system for with many features to manage customer telephone calls and e-mails. For more info see http://www.otrs.org/

Working with version 2.4 I realized that there was no option for batch customer import. On the other hand the APIs where sitting there, and there was even a script for user creation (otrs.adduser). Thus I decided to roll-out my own otrs.addCustomerUser:

Enjoy:

#!/usr/bin/perl -w
# --
# otrs.addCustomerUser - Add User from CLI
# Copyright (C) elias chatzigeorgiou (echatzig@gmail.com)
# --
# based on: otrs.addUser,v 1.17 2009/04/23 13:47:27
# --
# This program is free software; you can redistribute it and/or modify
# it under the terms of the GNU AFFERO General Public License as published by
# the Free Software Foundation; either version 3 of the License, or
# any later version.
#
# This program is distributed in the hope that it will be useful,
# but WITHOUT ANY WARRANTY; without even the implied warranty of
# MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
# GNU General Public License for more details.
#
# You should have received a copy of the GNU Affero General Public License
# along with this program; if not, write to the Free Software
# Foundation, Inc., 59 Temple Place, Suite 330, Boston, MA  02111-1307  USA
# or see http://www.gnu.org/licenses/agpl.txt.
# --

use strict;
use warnings;

use File::Basename;
use FindBin qw($RealBin);
use lib dirname($RealBin);
use lib dirname($RealBin) . "/Kernel/cpan-lib";

use vars qw (%opts);
use Getopt::Std;
getopt( 'flpge', \%opts );
unless ( $ARGV[0] ) {
    print
        "$FindBin::Script [-f firstname] [-l lastname] [-p password] [-g groupname] [-e email] username\n";
    print "\tif you define -g with a valid group name then the user will be added that group\n";
    print "\n";
    exit;
}

use Kernel::Config;
use Kernel::System::Encode;
use Kernel::System::Log;
use Kernel::System::Time;
use Kernel::System::Main;
use Kernel::System::DB;
use Kernel::System::CustomerUser;

# create common objects
my %CommonObject = ();
$CommonObject{ConfigObject} = Kernel::Config->new(%CommonObject);
$CommonObject{EncodeObject} = Kernel::System::Encode->new(%CommonObject);
$CommonObject{LogObject}  = Kernel::System::Log->new( %CommonObject, LogPrefix => 'otrs.addUser' );
$CommonObject{TimeObject} = Kernel::System::Time->new(%CommonObject);
$CommonObject{MainObject} = Kernel::System::Main->new(%CommonObject);
$CommonObject{DBObject}   = Kernel::System::DB->new(%CommonObject);
$CommonObject{UserObject} = Kernel::System::CustomerUser->new(%CommonObject);

my %Param;
undef %Param;

#user id of the person adding the record
$Param{UserID} = '1';

#Validrecord
$Param{ValidID} = '1';

$Param{Source}        = 'CustomerUser';
$Param{UserFirstname} = $opts{f};
$Param{UserLastname}  = $opts{l};
$Param{UserCustomerID}= $ARGV[0];
$Param{UserLogin}     = $ARGV[0];
$Param{UserPassword}  = $opts{p};
$Param{UserEmail}     = $opts{e};

=item CustomerUserAdd()

to add new customer users

    my $UserLogin = $CustomerUserObject->CustomerUserAdd(
        Source         => 'CustomerUser', # CustomerUser source config
        UserFirstname  => 'Huber',
        UserLastname   => 'Manfred',
        UserCustomerID => 'A124',
        UserLogin      => 'mhuber',
        UserPassword   => 'some-pass', # not required
        UserEmail      => 'email@example.com',
        ValidID        => 1,
        UserID         => 123,
    );

=cut

if ( $Param{UID} = $CommonObject{UserObject}->CustomerUserAdd( %Param, ChangeUserID => 1 ) ) {
    print "User added. user  id is $Param{UID}\n";
}

exit(0);