Monday 25 May 2015

Updating Glassfish 4 in 64-bit OS

I wanted to upgrade from Glassfish 4.0 to 4.1 and "pkg image-update" didn't work for me since I was using 64 bit OS without 32-bit support libraries. There might be other problems as well: like described by SO question http://stackoverflow.com/questions/17033118/glassfish-updatetool-linux-64-bit-issue. I was surprised that Glassfish's pkg tool supports only 32 bit systems - and to run them on 64 bit OS-es, you have to install compatibility libraries...

We can fix that! Following the guidelines given in this blog post and updates from this forum post, we can run pkg using OS's built-in 64-bit python. All modified files I've put in this github poject. Here are the steps:
  1. Hit the "pkg" tool: /opt/glassfish/bin/pkg (it should fail)
  2. mv /opt/glassfish/pkg/bin/pkg /opt/glassfish/pkg/bin/pkg.orig
  3. Download modified pkg script and copy to /opt/glassfish/pkg/bin/pkg
  4. mkdir /opt/glassfish/pkg/custom-lib
  5. cp -r /opt/glassfish/pkg/vendor-packages/pkg /opt/glassfish/pkg/custom-lib
  6. Install the dependencies so that we can compile the _actions.c:
    apt-get install python-dev gcc python-cherrypy python-mako python-openssl python-ply python-pycurl python-simplejson
  7. Download _actions.c (you can clone the project if you have git installed on the server)
  8. gcc -I/usr/include/python2.7 -shared -fpic -O2 _actions.c -o _actions.so
  9. cp _actions.so /opt/glassfish/pkg/custom-lib/pkg/actions/_actions.so
  10. download __init__.py from the project folder
  11. cp __init__.py /opt/glassfish/pkg/custom-lib/pkg/actions/__init__.py
  12. enjoy
Hope this helps! This worked for me - hopefully it will work for you as well.

Thursday 31 October 2013

Glassfish 4 behind (nginx) proxy

Problem: You want to put a proxy server (in this particular case it is nginx) in front of Glassfish 4 application server, but it ruins remote IP and scheme detection.

Solution consists of two main parts: (1) enabling "Auth Pass Through" property for HTTP connector and (2) configuring proxy server to add "Porxy-IP" and "Proxy-keysize" headers that will identify request scheme and remote IP address. Both header names are hard-coded and are not configurable.

First task is simple: open glassfish administration console and go to "Configurations" -> "server-config" -> "Network Config" -> "Network Listeners" -> "http-listener-1" and choose "HTTP" tab. Scroll all the way down and find "Auth Pass Through" property and enable it... "Save". No restart is needed (horray)!.

Second task - header configuration for nginx:

server {
        # listen 443;
        # ...

        # Glassfish-specific headers to properly resolve scheme and remote IP
        proxy_set_header        Proxy-IP  $remote_addr;
        proxy_set_header        Proxy-keysize 256;
        
        # ...
}

After changes, restart will be required. "Proxy-IP" header will be used in order to detect remote IP address, while having "Proxy-keysize" header greater than zero will force Glassfish to report schema as "https" (not "http"). It must be noted, however, that for non-SSL configuration only "Proxy-IP" header should be appended (otherwise non-SSL connections will be considered as secure):

server {
        # listen 80;
        # ...

        # Glassfish-specific headers to properly resolve remote IP and scheme
        proxy_set_header        Proxy-IP  $remote_addr;
        
        # ...
}

Important: Also consider setting proper proxy address in "General" tab in order to avoid security risks.

BTW, here's description for Apache's mod_proxy: http://www.manorrock.com/online/wiki/glassfish/UpgradeToGlassfish3

Thursday 17 October 2013

Finding all modules with (possibly) broken dependencies

Have you ever wondered which SQL stored procedures, functions, triggers or views might reference deleted objects? If yes, then this little script might come in handy for you. Script below will find even invalid cross-database dependencies. It was a bit tricky to solve problems which where caused by omitted schema names, but that has been done (at least I hope so).

Limitation:This won't find dependencies that are called via dynamic SQL (e.g. EXEC) or functions like OPENQUERY.

--CREATE PROCEDURE [dbo].[FindAllModulesWithMissingReferences]
--AS
/*****************************************************************************
Find all SQL modules which contain a reference to invalid objects.

Changes:
16.10.2013. (A. Grabovskis) Initial definition
******************************************************************************/
BEGIN
 -- SET NOCOUNT ON added to prevent extra result sets from interfering with SELECT statements.
 SET NOCOUNT ON;
 
 -- first, we gather all references to objects
 CREATE TABLE #References (
  ReferencedBy varchar(517)
  ,ReferencedEntity varchar(517)
 )
 
 DECLARE @SQL nvarchar(max) = '
  USE [?];
 
  SELECT DB_NAME() + ''.'' 
    + SCHEMA_NAME(o.schema_id) + ''.'' 
    + o.[Name] 
    AS ReferencedBy
   ,COALESCE(d.referenced_database_name, DB_NAME()) + ''.'' 
    + COALESCE(d.referenced_schema_name, SCHEMA_NAME(ref.schema_id), SCHEMA_NAME(o.schema_id)) + ''.'' 
    + d.referenced_entity_name 
    AS ReferencedEntity
  FROM sys.sql_expression_dependencies AS d
  INNER JOIN sys.objects AS o ON o.object_id = d.referencing_id
  LEFT JOIN sys.objects AS ref ON ref.object_id = d.referenced_id
  WHERE is_ambiguous = 0
   AND referenced_server_name IS NULL';
 
 INSERT INTO #References
 EXEC sp_MSForEachDB @SQL;

 -- Now we must gather all objects that are created in each and every database
 SET @SQL = '
  USE [?];
  
  SELECT DB_NAME() + ''.'' + SCHEMA_NAME(schema_id) + ''.'' +OBJECT_NAME(object_id)
  FROM sys.objects
 ';
 
 CREATE TABLE #Modules (
  EntityPath varchar(517)
 )
 
 INSERT INTO #Modules
 EXEC sp_MSForEachDB @SQL

 --SELECT * FROM #References ORDER BY ReferencedBy
 
 --SELECT * FROM #Modules
 
 SELECT r.*
 FROM #References as r
 LEFT JOIN #Modules as m ON m.EntityPath = r.ReferencedEntity
 WHERE m.EntityPath IS NULL
 ORDER BY ReferencedBy
 
 DROP TABLE #References
 DROP TABLE #Modules
END

P.S. I do not guarantee 100% accuracy of the script, but I would be glad if you would give your feedback here in comments, if something does not work as expected (e.g. valid references are identified as invalid or vice verse).

Friday 3 August 2012

Server Side Tracing Microsoft SQL Server Events

Consider a situation where you must find out about user error messages on your production server. You can use profiler, but overhead is not welcomed. Solution is server side tracing whose overhead is significantly reduced (in fact one such trace is running by default). Server side tracing can collect the same data as profiler can.

Example of creating server-side trace can be found here. So here is the script that enables tracing "User Error Message" events (script has been slightly modified):

/*****************************************************************************
This script starts server-side trace.
******************************************************************************/
-- Declare variables 
DECLARE @rc INT 
DECLARE @TraceID INT 
DECLARE @maxFileSize bigint 
DECLARE @fileName NVARCHAR(128) 
DECLARE @on bit 

-- Set values 
SET @maxFileSize = 5 -- in MB 
SET @fileName = N'D:\UserErrorMessageTrace' -- location must be writeable for SQL server
SET @on = 1 

-- Create trace 
EXEC @rc = sp_trace_create @TraceID output, 0, @fileName, @maxFileSize, NULL  

-- If error end process 
IF (@rc != 0) 
 SELECT 'Could not create trace. Error code: ' + CAST(@rc as varchar(10))
ELSE BEGIN
 -- Declare event ID to trace
 DECLARE @EventId int = 162 -- 'User Error Message'

 -- Set columns to collect
 EXEC sp_trace_setevent @TraceID, @EventId,  1, @on 
 EXEC sp_trace_setevent @TraceID, @EventId,  4, @on 
 EXEC sp_trace_setevent @TraceID, @EventId,  6, @on 
 EXEC sp_trace_setevent @TraceID, @EventId,  7, @on 
 EXEC sp_trace_setevent @TraceID, @EventId,  8, @on 
 EXEC sp_trace_setevent @TraceID, @EventId, 11, @on 
 EXEC sp_trace_setevent @TraceID, @EventId, 12, @on 
 EXEC sp_trace_setevent @TraceID, @EventId, 14, @on 
 EXEC sp_trace_setevent @TraceID, @EventId, 20, @on 
 EXEC sp_trace_setevent @TraceID, @EventId, 27, @on 
 EXEC sp_trace_setevent @TraceID, @EventId, 30, @on 
 EXEC sp_trace_setevent @TraceID, @EventId, 31, @on 
 EXEC sp_trace_setevent @TraceID, @EventId, 35, @on 
 EXEC sp_trace_setevent @TraceID, @EventId, 51, @on 
 EXEC sp_trace_setevent @TraceID, @EventId, 64, @on 

 -- Set Filters 
 EXEC sp_trace_setfilter @TraceID, 31, 0, 1, 5701 -- exclude language change messages
 EXEC sp_trace_setfilter @TraceID, 31, 0, 1, 5703 -- exclude context change messages

 -- Start the trace 
 EXEC sp_trace_setstatus @TraceID, 1 
   
 -- Report success
 SELECT 'Started trace with ID = ' + cast(@TraceID as varchar(10))
END

Trace events and columns

So... which events can you trace and what columns those events support? There are some system views that contain all required info. All traceable events can be found by querying sys.trace_events view, columns are stored in sys.trace_columns view and event column configuration is in sys.trace_event_bindings view. In order not to write all those sp_trace_setevent one by one, use this query that returns all columns for particular event and corresponding EXEC statement (replace "164" with event ID of your interest):

SELECT 'EXEC sp_trace_setevent @TraceID, @EventId, ' 
  + cast(c.trace_column_id AS VARCHAR(10)) 
  + ', @on'
 ,*
FROM sys.trace_event_bindings AS b
INNER JOIN sys.trace_columns AS c ON c.trace_column_id = b.trace_column_id
WHERE b.trace_event_id = 164

Check active traces

In order to check currently active traces, you can use sys.fn_trace_getinfo system function (refer to the "Tables Returned" section for explanation of returned values). Enabled columns can be retrieved using fn_trace_geteventinfo system function. In order to retrieve more human-understandable data (with column names), execute this query:

SELECT DISTINCT c.*
FROM fn_trace_geteventinfo(1) AS i
INNER JOIN sys.trace_columns AS c ON c.trace_column_id = i.columnid

Start/Stop/Close trace and load data

Manage trace lifecycle with sp_trace_setstatus system function and load trace data using sys.fn_trace_gettable function. See examples here

Friday 27 July 2012

Configure Microsoft SQL Server to Report Events to E-mail

For this I had to combine 3 blog articles:

Step 1: Configure Database Mail

Follow the instructions here: http://www.mssqltips.com/sqlservertip/1100/setting-up-database-mail-for-sql-2005/

Summary of steps (with minor variations):

  1. Select "Configure Database Mail" by left clicking on "Database Mail"
  2. Next
  3. "Manage Database Mail accounts and profiles"... Next
  4. Create a new profile... Next
  5. Configure Profile:
    1. Profile name: "SQLAlerts"
    2. Add..
      1. New Account
        1. Account name: "SQLAlerts"
        2. E-mail address: mysqlserver@example.com (this will be shown in "sender" e-mail field)
        3. Display Name: My SQL Server (this will be shown as display name in "sender" e-mail field)
        4. Server Name: smtp.example.com (outgoing SMTP server)
        5. ... configure authentication
        6. OK
      2. OK
    3. Next
    4. Finish
    5. Close

Step 2: Create new Operator and Alert

Corresponding blog post: http://www.mssqltips.com/sqlservertip/1523/how-to-setup-sql-server-alerts-and-email-operator-notifications/

Summary of steps:

  1. Under "SQL Server Agent" right click on Operators and select "New Operator..."
    1. Name: "DBA"
    2. E-mail name: your-email-here@example.com
    3. OK
  2. Right click on "SQL Server Agent" and choose "Properties"
    1. Choose "Alert System" on the left hand side
    2. Check "Enable mail profile"
    3. If you have more than one mail profile configured, then choose the desired one
    4. OK
  3. Right click on "SQL Server Agent" and choose "Restart"
  4. Under "SQL Server Agent" right click on "Alerts" and select "New Alert..."
    1. Under "General" tab:
      1. Name: "14 - Insufficient Permission"
      2. Severity: "14 - Insufficient Permission"
    2. Under "Response" tab:
      1. Check "Notify operators"
      2. Check E-mail options of those operators that should be notified about an error
    3. Under "Options" tab
      1. Check "E-mail" checkbox in order to include error description in the mail text

This is the point where your alert sending has been configured, and you're wondering "Why I'm not getting error reports about insufficient permissions?!" Here comes the third step:

Step 3: Alter messages

Corresponding blog article: http://www.simple-talk.com/sql/database-administration/sql-server-alerts-soup-to-nuts/

In order to SQL Server Agent detect errors, they must be written to Application Event Log, but most of them are not. That can be changed using "sp_altermessage" database engine stored procedure. For errors that you would like to receive, execute this query:

sp_altermessage 229, 'WITH_LOG', 'true';

This particular query will enable reporting messages like "The SELECT permission was denied on the object 'mytable', database 'bydb', schema 'dbo'" to application log (and with that - e-mail).

You can explore messages that you could be interested in with this query:

The SELECT permission was denied on the object 'prkad', database 'person', schema 'dbo'

Thursday 3 May 2012

Binding spring form using bean with map attribute retrieved using hibernate

Another one exception bit me today. Spring forms nicely support maps as bean properties using 'mapProperty[key].mapElementProperty' paths, but each time I tried to submit form, exception was thrown (see below). As it turns out, problem was caused by hibernate which returned bean proxy. When setting map element property values after form submit, Spring tries to find out what type of elements map contains using generics information (... using some kind of magic), but in the end it didn't work. Not sure, but I suppose it could be because javassist can't handle generics because of type erasure. Solution was simple - modify DAO method which returned editable object (getById(id)) so that it would not return a hibernate-managed object, but a cloned object. As soon as those changes were applied, everything started to work.
java.lang.NullPointerException
 at org.springframework.beans.BeanWrapperImpl.createDefaultPropertyValue(BeanWrapperImpl.java:620)
 at org.springframework.beans.BeanWrapperImpl.setDefaultValue(BeanWrapperImpl.java:614)
 at org.springframework.beans.BeanWrapperImpl.getNestedBeanWrapper(BeanWrapperImpl.java:579)
 at org.springframework.beans.BeanWrapperImpl.getBeanWrapperForPropertyPath(BeanWrapperImpl.java:553)
 at org.springframework.beans.BeanWrapperImpl.setPropertyValue(BeanWrapperImpl.java:914)
 at org.springframework.beans.AbstractPropertyAccessor.setPropertyValues(AbstractPropertyAccessor.java:76)
 at org.springframework.validation.DataBinder.applyPropertyValues(DataBinder.java:692)
 at org.springframework.validation.DataBinder.doBind(DataBinder.java:588)
 at org.springframework.web.bind.WebDataBinder.doBind(WebDataBinder.java:191)
 at org.springframework.web.portlet.bind.PortletRequestDataBinder.bind(PortletRequestDataBinder.java:113)
 at org.springframework.web.portlet.mvc.annotation.AnnotationMethodHandlerAdapter$PortletHandlerMethodInvoker.doBind(AnnotationMethodHandlerAdapter.java:570)
 at org.springframework.web.bind.annotation.support.HandlerMethodInvoker.doBind(HandlerMethodInvoker.java:813)
 at org.springframework.web.bind.annotation.support.HandlerMethodInvoker.resolveHandlerArguments(HandlerMethodInvoker.java:367)
 at org.springframework.web.bind.annotation.support.HandlerMethodInvoker.invokeHandlerMethod(HandlerMethodInvoker.java:171)
 at org.springframework.web.portlet.mvc.annotation.AnnotationMethodHandlerAdapter.invokeHandlerMethod(AnnotationMethodHandlerAdapter.java:362)
 at org.springframework.web.portlet.mvc.annotation.AnnotationMethodHandlerAdapter.doHandle(AnnotationMethodHandlerAdapter.java:349)
 at org.springframework.web.portlet.mvc.annotation.AnnotationMethodHandlerAdapter.handleAction(AnnotationMethodHandlerAdapter.java:283)
 at org.springframework.web.portlet.DispatcherPortlet.doActionService(DispatcherPortlet.java:641)
 at org.springframework.web.portlet.FrameworkPortlet.processRequest(FrameworkPortlet.java:519)
 at org.springframework.web.portlet.FrameworkPortlet.processAction(FrameworkPortlet.java:460)
 at org.jasig.portal.portlet.container.FilterChainImpl.doFilter(FilterChainImpl.java:130)
 at org.jasig.portal.portlet.container.FilterChainImpl.processFilter(FilterChainImpl.java:92)
 at org.jasig.portal.portlet.container.FilterManagerImpl.processFilter(FilterManagerImpl.java:119)
 at org.apache.pluto.container.driver.PortletServlet.dispatch(PortletServlet.java:359)
 at org.apache.pluto.container.driver.PortletServlet.doPost(PortletServlet.java:267)
 at javax.servlet.http.HttpServlet.service(HttpServlet.java:637)
 at javax.servlet.http.HttpServlet.service(HttpServlet.java:717)
 at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:290)
 at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:206)
 at org.apache.catalina.core.ApplicationDispatcher.invoke(ApplicationDispatcher.java:646)
 at org.apache.catalina.core.ApplicationDispatcher.doInclude(ApplicationDispatcher.java:551)
 at org.apache.catalina.core.ApplicationDispatcher.include(ApplicationDispatcher.java:488)
 at org.apache.pluto.driver.container.DefaultPortletInvokerService.invoke(DefaultPortletInvokerService.java:233)
 at org.apache.pluto.driver.container.DefaultPortletInvokerService.action(DefaultPortletInvokerService.java:101)
 at sun.reflect.GeneratedMethodAccessor295.invoke(Unknown Source)
 at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25)
 at java.lang.reflect.Method.invoke(Method.java:597)
 at org.springframework.aop.support.AopUtils.invokeJoinpointUsingReflection(AopUtils.java:318)
 at org.springframework.aop.framework.ReflectiveMethodInvocation.invokeJoinpoint(ReflectiveMethodInvocation.java:183)
 at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:150)
 at org.springframework.aop.interceptor.ExposeInvocationInterceptor.invoke(ExposeInvocationInterceptor.java:90)
 at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:172)
 at org.springframework.aop.aspectj.MethodInvocationProceedingJoinPoint.proceed(MethodInvocationProceedingJoinPoint.java:80)
 at org.jasig.portal.portlet.dao.jpa.ThreadContextClassLoaderAspect.doThreadContextClassLoaderUpdate(ThreadContextClassLoaderAspect.java:56)
 at sun.reflect.GeneratedMethodAccessor105.invoke(Unknown Source)
 at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25)
 at java.lang.reflect.Method.invoke(Method.java:597)
 at org.springframework.aop.aspectj.AbstractAspectJAdvice.invokeAdviceMethodWithGivenArgs(AbstractAspectJAdvice.java:621)
 at org.springframework.aop.aspectj.AbstractAspectJAdvice.invokeAdviceMethod(AbstractAspectJAdvice.java:610)
 at org.springframework.aop.aspectj.AspectJAroundAdvice.invoke(AspectJAroundAdvice.java:65)
 at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:172)
 at org.springframework.aop.framework.JdkDynamicAopProxy.invoke(JdkDynamicAopProxy.java:202)
 at $Proxy207.action(Unknown Source)
 at org.apache.pluto.container.impl.PortletContainerImpl.doAction(PortletContainerImpl.java:251)
 at org.jasig.portal.portlet.rendering.PortletRendererImpl.doAction(PortletRendererImpl.java:165)
 at org.jasig.portal.portlet.rendering.worker.PortletActionExecutionWorker.callInternal(PortletActionExecutionWorker.java:46)
 at org.jasig.portal.portlet.rendering.worker.PortletActionExecutionWorker.callInternal(PortletActionExecutionWorker.java:31)
 at org.jasig.portal.portlet.rendering.worker.PortletExecutionWorker$1.call(PortletExecutionWorker.java:145)
 at java.util.concurrent.FutureTask$Sync.innerRun(FutureTask.java:303)
 at java.util.concurrent.FutureTask.run(FutureTask.java:138)
 at java.util.concurrent.ThreadPoolExecutor$Worker.runTask(ThreadPoolExecutor.java:886)
 at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:908)
 at java.lang.Thread.run(Thread.java:662)

Wednesday 28 March 2012

Centring DIV elements

Here's a HTML structure and required CSS:

<div id="container">
  <div class="centered">This is centered div</div>
</div>
#container { text-align:center;}
.centered { margin:0 auto; width:80%; } /* Set the width according to your needs */

Applying "margin: 0 auto;" would be sufficient in a world without IE. In order to get centred div element in IE, you have to set "text-align: center;" for parent element. That parent element could be another div (just like in example), body element (then style definition would be "body {text-align: center}") or anything else. There's also a lot more detailed explanation of this approach here, but I'm too lazy to read the whole article :(.

Already knew this? Good for you! I did have to spend some time reading discussions and long descriptions about this, but didn't find a single concise "here's a solution - copy, paste, and enjoy" answer.

Are there any drawbacks of this method? Haven't found such yet. If you know something that I'm not aware of, please, let me know this in comments.

Are there any alternatives? Yes:

  • I've seen (and used) alternatives where DIV element is being included into a table which is centred. But it is not recommended approach since page structure should be built using DIVs. I know - it's a challenging task, but that's an idea I've got from different discussions.
  • Also I've found a discussion where a fixed-width DIV gets positioned at the centre of parent element and then a negative margin is applied (haven't tried this one, but looks promising). Here's a CSS:
    .centered{
      width: 500px;
      position: relative;
      left: 50%;
      margin-left: -250px; /* half the width of the div */
    }