Configuring Lucee to take over http error handling instead of IIS

By default, Lucee seems to be configured with it's http://boncode.net/connector/webdocs/Tomcat_Connector.htm configured to hand-off all non-200 response codes to IIS to display using whatever setting you've enabled in web.config

Which is great... except, you need detailed errors enabled for your site in order for Lucee to render its own error messages... which means any request for a static file, like a missing image ( or static non cfc /cfm ) will get handled directly by IIS... and show DETAILED errors... no good!

Just read in the Lucee forum the following from @bilal: https://groups.google.com/forum/#!searchin/lucee/error$20messages$20iis$20%7Csort:relevance/lucee/nSahG_wXzPk/Ck545qZbCwAJ

Step 0) Throw a 500 in Lucee. IIS will take over with a bland 500 error stripe message. Poop!

Step 1) Skip IIS Custom Errors

In C:\Windows\BonCodeAJP13.settings file, add 'SkipIISCustomErrors' ( See Tomcat Connector Docs for details )

<SkipIISCustomErrors>True</SkipIISCustomErrors>

This will let you control status codes other than 200 with custom messages from Lucee. IIS will still be able to use the standard 404-custom error page for non-cfm related error handling, e.g. when a non-existing image file is requested etc.

Step 2) Set IIS errors to return "Custom Error Pages"

Step 3) Throw a 500 in Lucee. You should see your error ( And you should render it properly for security )

Sql server full text search performance slow with additional conditions or clauses

We had a performance problem with SQL Server (2012 Web). When we have additional where conditions to our full-text Contains search it becomes over 1000x slower, the execution plan changes to a nested loop around the full-text search, and "Actual Rebinds" on the "Table Valued function [FulltextMatch]" went from 1 to 3628.

We noticed that it only happens when full-text searching newly uploaded data; and furthermore, when the full-text search had an additional where clause selecting only the batch of newly inserted records.


Diag 1. Notice the "Table Valued function [FulltextMatch]" is inside a nested loop, with 3628 rebinds. This isn't going to end well.

Our first thought: The full text index hasn't done an incremental update; so we did a Full repopulate index... No Change in performance.

Then I finally found an article that seemed relevant: "First: Make sure that your statistics for the table's index are up to date."

So, I looked up how to do it: 

https://msdn.microsoft.com/en-us/library/ms187348.aspx
https://msdn.microsoft.com/en-CA/library/ms174384.aspx

Steps:
#1) Check when the tables index statistics were last updated on one of the table's indexes:

DBCC SHOW_STATISTICS ( "Table_Name_Here", PK_Table_Name_Here_Index) ;

And found that the date the stats were last updated was before  we had inserted our new 3000 record set into the table. ( The index stats showed there was about ~3000 fewer rows than "actual" )

#2) We updated the index stats manually via:

UPDATE STATISTICS Table_Name_Here

And now, our full-text queries are all lightning fast, regardless of any added where clause and in the execution plan, our "Actual Rebinds" on the "Table Valued function [FulltextMatch]" went back to 1.


Diag 2.  "Table Valued function [FulltextMatch]" now with a rebind count of 1 ( Its not inside a nested loop any more )

And now, to answer the underlying question: 
Why weren't the tables index stats recalculated after the new data was entered? We're assuming at this point that it wasn't a sufficiently large insert to trigger the stats recalculation ( we inserted around 3% new rows ).

Perhaps in this case, the table just needs to have its stats recalculated manually since its a relatively small insert, but has a really high impact on full text search speed when not completed.

How to specify a single subdomain for a non-wildcard SSL certificate

Thanks:

http://blog.goodcoresoft.com/host-name-ssl-bindings-iis7/

Although not very well documented, IIS does support multiple host headers for SSL bindings. Following are the steps to configure it.

1. Install the SSL certificate on IIS (http://www.sslshopper.com/article-installing-an-ssl-certificate-in-windows-server-2008-iis-7.0.html)
2. From the start menu select “Run”.
3. Type “MMC” and hit enter; this will launch the Microsoft Management Console.
4. Select File –> Add / Remove Snap-in.
5. Select Certificates from the “Available snap-ins” list and hit the add button.
6. Select the “Computer Account” option when asked which certificates the snap-in will manage.
7. Click “OK”
8. Look in the appropriate folder the certificate was originally installed in, if the certificate is self signed it can be found under “Personal”.
9. Right click on the certificate name and select properties.
10. Change the friendly name such that it starts with *

And voila, now you can specify a host name in the bindings box, specifically for the single sub-domain your certificate is actually valid for

Make your own seamless background in Photoshop

All this time, I thought this was graphic designer dark-magic, and it turns out its mostly knowing how to use photoshop!

- Create a pattern in photoshop,
- Go to Filter -> Offset -> Set both sliders to 1/2 of your image size
- Use the Clone tool to fix the seam down the middle if it exists
- Voila!!

Totally thanks to: http://designshack.net/articles/css/create-seamless-web-background-textures-in-minutes/

CFWheels: SQL Counting rows with specific values

In SQL, you can count up rows that have a certain criteria, which means in one query you can count up multiple kinds of things:

select
   SUM(CASE WHEN dateResolved IS NULL THEN 1 ELSE 0 END) as itemsOutstanding,
   SUM(CASE WHEN dateResolved IS NOT NULL THEN 1 ELSE 0 END) as itemsResolved,
   COUNT(*) as total
from tableName
group by Year(InspectionDate) AS inspectedYear, Month(inspectionDate) AS inspectedMonth

Update the JRE on Railo

Railo's Excellent guide link is below, but for my reference; to summarize


1) Download the most current JRE (Which at the time of this blog post was Java SE 7u21)

http://www.oracle.com/technetwork/java/javase/downloads/index.html

2) Install it to an easy to remember folder, such as C:\jre\

3) Stop the Tomcat Railo service ( while it is not busy )

4) Rename Railo's JDK folder:  railo/jdk   --> railo/jdk_old   ( In my case, C:\railo\jdk )

5) Make a new railo/jdk folder

5) Copy the contents of C:\jre  from step 2 into the railo/jdk folder

6) Restart Tomcat Railo & Test your world. You can display the jre version in coldfusion via:

<cfoutput>#CreateObject("java", "java.lang.System").getProperty("java.version")#</cfoutput>

7) Recycle railo/jdk_old

https://github.com/getrailo/railo/wiki/Installation%3AInstallerDocumentation%3AMSUpgradingJRE

Railo SQL, Amazon Cloud and Time Zones

We've been working today with localizing time stamps, where a web server, a database server, and the client using the application are all in different time zones respectively.

Setup:

1. Select the time zone in Railo Admin to match the time zone of your server's OS.  Server Administrator tab -> Regional -> Time Zone pull down
2. Configure your data source's time zone. Datasource -> [ Pencil beside data source ] -> Time Zone.  ( For Amazon Web Services, all RDS instances are UTC )
3. In our case, we're specifying in the users account what time zone they are in, so we can use the underlying java.util.TimeZone

The test:

Doing a sql insert of railo's "#now()#" timestamp, and reading it again from the database should be the same value, as well, the now() timestamp should be the same in the database as returned from your browser when output. ( They should all be in sync.. and do their dark magic across time zones properly and seamlessly )
 

From Michael Offner See the Railo blog post here

"If Railo has set the the Timezone CST and the DB Server is running in PST you run into problems for a very simple reason. the Timestamp produced ({ts '2012-07-25 16:20:14'}) is based on the CST Timezone, this means it is "2012-07-25 16:20:14 CST", BUT the TimeZone information is not present in the timestamp.
so if the DB server is reading the string timestamp from the SQL String, it has no timezone information and the DB Server simply takes it own timezone for the timestamp, because it has no idea from whitch timezone the request is coming from, this means the db server interpret the timestamp as "2012-07-25 16:20:14 PST" what is 2 hours later.
then when you get this date back you get "2012-07-25 18:20:14 CST" what is exactly the same as "2012-07-25 16:20:14 PST", this means the failure happens when you send the string timestamp to the db server in a different timezone without the timezone information. this conversion problem does not happen when you get the date back via a select statement, simply because in this case the date is send in a timezone independent format."

Notes: 

• The dateTime datatype in sql server does not have time-zone information included: http://msdn.microsoft.com/en-us/library/ms187819.aspx