Friday, March 18, 2011

Using SELECT MAX(ID)+1 in an insert statement

I am working with the following Knowledge Tree table in MySQL - it holds reference data.
CREATE TABLE `document_types_lookup` (
    `id` INT(10) NOT NULL,
    `name` VARCHAR(100),
    `disabled` BIT DEFAULT'0' NOT NULL
);

ALTER TABLE `document_types_lookup` ADD PRIMARY KEY (`id`);
I needed to insert a few thousand rows, but since there is no auto-increment field for the ID, I needed SQL that could increment ID for me - using SQL only, no script. I found a bug report (Allow INSERT...SELECT MAX(x)+1 from the same table) that had the below snippet that used select (max(id)+1) to increment ID for an insert.
insert into foo(lfd) select (max(lfd)+1) from foo;
And while I could get that statement by itself to work (inserting only ID into a record), I could not not adapt it to add the rest of the record values.
insert into
   document_types_lookup(id, name, disabled)
values
   (select (max(id)+1) from document_types_lookup, 'a name', false);
I always got the below error.
Executing:

insert into document_types_lookup(id, name, disabled) values (select (max(id)+1) from document_types_lookup, 'a name', false)

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'select (max(id)+1) from document_types_lookup, 'a name', false)' at line 1
[Error Code: 1064]
[SQL State: 42000]

So, my work-around is to use two statements instead of one: an insert followed by an update, as below.
insert into
   document_types_lookup(id)
   select (max(id)+1) from document_types_lookup;

update
   document_types_lookup
set
   name = 'CORP_HMN_000_CA_',
   disabled = false
where
   name is null;
OK, see any problems with this script? You should - it's a dangerous script for two big reasons. Firstly, it isn't thread safe, i.e. using select (max(id)+1) in an insert is dangerous if there are multiple people updating the table at the same time. You have no guarantee that between the time you have selected max ID + 1 and the time the insert actually happens, a new record might have been inserted by someone else, meaning that when your insert statement finally hits, your max ID + 1 will not be unique anymore. You can solve this by locking the table before your insert and releasing it afterwards. The second reason that this is a dangerous script is because it assumes there will only ever be one record where name is null; it does this as I could think of no other way to determine what record I just inserted.

So, this script works only because a) I knew I was the only person who would be updating the table at that time and b) I knew name would have a value for all records other than the one I had just inserted.

Monday, March 14, 2011

UltraEdit macro for HTML/XML - auto-create closing tags

Here is another pair of UltraEdit macros that I use quite frequently - these two help me with creating container tags for HTML or XML; specifically, they automate the creation of the opening and closing tag: all I need to do is type out the tag name and run the macro with the cursor within (or at the left/right boundary of) the tag name.

For example, if I type out xmlTag and run the first macro, I will be left with the following: <xmlTag>|</xmlTag>. The I bar (|) indicates where the cursor is left when the macro finishes.
InsertMode
ColumnModeOff
HexOff
UltraEditReOn
Clipboard 9
IfSel
Cut
"<"
Paste
">"
Else
IfSel
Else
SelectWord
EndIf
StartSelect
Cut
"<"
Paste
">"
EndIf
Clipboard 0
Paste
Clipboard 9
"</"
Paste
">"
Clipboard 0
The second macro is the same except that it will paste whatever is in the clipboard at the time and leave the cursor at the end of the closing tag. For example, if I type and cut the following text: text in the clipboard, then type out xmlTag and run the second macro, I will be left with the following: <xmlTag>text in the clipboard</xmlTag>|. The I bar (|) indicates where the cursor is left when the macro finishes.
InsertMode
ColumnModeOff
HexOff
UltraEditReOn
Clipboard 9
IfSel
Cut
"<"
Paste
">"
Else
IfSel
Else
SelectWord
EndIf
StartSelect
Cut
"<"
Paste
">"
EndIf
Clipboard 0
Paste
Clipboard 9
"</"
Paste
">"
Clipboard 0
Note that both the macros above include instructions that are from another common UltraEdit macro I use, Alt+x to cut currently selected token (selected or if cursor is in the token).
IfSel
Else
SelectWord
EndIf
StartSelect
Cut

Friday, March 11, 2011

dotCMS thumbnail error: java.lang.IncompatibleClassChangeError

In our dotCMS 1.7 environments, we found - on just one environment (PROD, of all places!) - that image thumbnails were broken. For example, referencing the image itself worked fine: http://example.com/path/to/image.jpg, but referencing the thumbnail fails: http://example.com/thumbnail?inode=60732&w=75&h=50.

In the logs, I noticed the below error.
[07/03/11 04:39:53:053 EST] ERROR [/].[ThumbnailServlet]: Servlet.service() for
servlet ThumbnailServlet threw exception
java.lang.IncompatibleClassChangeError: Found class com.sun.image.codec.jpeg.JPEGImageEncoder, but interface was expected
at com.dotmarketing.util.Thumbnail.resizeImage(Thumbnail.java:114)
at com.dotmarketing.servlets.image.ThumbnailImage.service(ThumbnailImage.java:203)
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 com.dotmarketing.filters.CMSFilter.doFilter(CMSFilter.java:104)
at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:235)
After some Googling, I found the below description of the error on this page: 'Interface was expected' for JPEGImageEncoder.
Root Cause
If you found this error appears in the log file, the biggest possibility and maybe the only reason that this exception being thrown is you are using the OpenJDK instead of Sun JDK. Well, the exception message is obvious to tell you that the com.sun.image.codec.jpeg.JPEGImageEncoder is a class in OpenJDK whereas it is implemented as an interface in Sun JDK.
I then confirmed that PROD did indeed have a different JRE than the other environments. On DEV:
java -version
java version "1.6.0_21"
Java(TM) SE Runtime Environment (build 1.6.0_21-b06)
Java HotSpot(TM) Client VM (build 17.0-b16, mixed mode, sharing)
But on PROD:
java -version
java version "1.6.0_17"
OpenJDK Runtime Environment (IcedTea6 1.7.5) (rhel-1.16.b17.el5-x86_64)
OpenJDK 64-Bit Server VM (build 14.0-b16, mixed mode)
So... we had the wrong JDK. Overnight, we installed Java HotSpot on PROD, changed PATH and JAVA_HOME to point to the new JRA, and the issue was resolved.

I reported this issue on the dotCMS Yahoo group here: Thumbnails not showing - java.lang.IncompatibleClassChangeError. In a response to that post, Maria Ahues Bouza noted that dotCMS only supports Sun Java (Oracle now I guess) as it's defined on their technology requirements page.

Thursday, March 10, 2011

DOS Batch Script to upload file to multiple dotCMS environments via cadaver

Update: Friday 11 March 2011, 11:56:08 AM, added instructions for use with Eclipse. I also announced this script on the dotCMS Yahoo Group.
Friday 11 March 2011, 03:20:15 PM added section about STATUS_ACCESS_VIOLATION error.

In dotCMS, one of the most tedious activities for a developer is to use the dotCMS Admin UI to navigate to a file (.vtl for example), open it for editing, make your change and then publish the change. Depending on how snappy your environment is, everything bar the actual editing can take a minute or more. Now multiply that by the 30, 40, 50, 100 edits you might need to make in one work day, especially during a test-debug cycle.

It is for this reason that in my current project I have used .vtl files everywhere possible to store content/code that is a developer's responsibility. This means two things.
  1. It is very easy to use Cadaver and a script to upload new and changed files.
  2. I can use my favourite text editor or IDE to edit the VTL files.
    1. Eclipse has a plugin for editing Velocity files: Veloeclipse. Unfortunately, apart from syntax highlighting and HTML auto-complete, none of the other functions seem to work since Galileo (at least), like auto-complete for macros or Velocity/Java objects.
    2. UltraEdit has a wordfile for Velocity on its extras page (to allow for syntax highlighting).
Personally, I edit VTLs in UltraEdit and upload them using Cadaver scripts from Cygwin - and now from UltraEdit directly. Thanks to UltraEdit's ability to run DOS commands with a keyboard shortcut, I can upload the VTL file to all the dotCMS environments at the same time, without even alt+tabbing to Cygwin.

Prerequsites.
  1. You are using a local project (directory structure) to store files (such as VTLs, JS, CSS etc) which you then upload to dotCMS. Your project directory structure must match the folder structure you are using in dotCMS (if not, stop reading this post because my script will not help you). It would also be really good if you are using a source control system (good like you being off Santa's nice list if you don't).
  2. Have Cygwin installed and, um, be running Windows. :)
  3. Make sure that your Cygwin install includes Cadaver. Cadaver is now included with Cygwin, so it should already be there if you recently installed/updated Cygwin. Make sure by entering the command cadaver. If you see command not found, then grab cadaver and unpack it into /usr/bin.
  4. Make sure you have set up your .netrc file with credentials for each environment as described in my post Using webDAV and Cadaver with dotCMS.
  5. Save the script below as a DOS batch file - into the root of your project directory - and edit only the section underneath the line :: Change these to suit your project.
    • environmentLabelX and environmentX. Add as many environmentLabelX and environmentX pairs as you want - one for each environment you want files to be uploaded to. Make sure to increment X for each pair.
    • max. Edit the line set max=3 to make the number match the number of environments you have just defined.
    • PATH_SUFFIX. Within dotCMS, we have put all of our content inside a "root" directory that is not matched by our project directory structure. For example, our CSS file would be http://dev.example.com/rootDir/css/styles.css but in our project directory, this file would be found at PROJECT_DIR/css/styles.css. If you have such a root, store it in PATH_SUFFIX. Otherwise, leave it blank if you put all your files into the highest folder level within dotCMS.
  6. This script can be used by itself (it is a DOS Batch after all, and the only argument it needs is the absolute path to the file being uploaded) or in conjunction with an editor/IDE that knows how to execute external commands. Both UltaEdit and Eclipse can do this. Below are the instructions for using the script with UltraEdit or Eclipse.
    • To use it with UltraEdit, follow these instructions to add it to the Tool Configuration.
      1. In the menu bar, select Advanced > Tool Configuraiton > Insert.
      2. On the Command tab enter the following.
        • Menu item name: Upload current file with Cadaver.
        • Command line: C:\path\to\your\script.bat %f.
      3. On the Options tab, enter the following.
        • Program Type: DOS program.
        • Tick "Save active file"
      4. On the Output tab, enter the following.
        • Command Output (DOS Commands): Output to list box.
        • Tick "Show DOS box"
        • Tick "Capture Output"
        • Replace selected text with: No replace.
      5. Now, after you have edited a VTL file (or CSS, JS etc) in UltraEdit, select Advanced > Upload current file with Cadaver or just use the keyboard shortcut UltraEdit gives to each of those (a control+shift+x shortcut).
    • To use it with Eclipse, follow these instructions to add it as an external tool.
      1. In the menu bar, select Run > External Tools > External Tools Configuration.
      2. Click the icon for New Configuration.
      3. Enter name: Upload current file with Cadaver.
      4. Enter location: c:/path/to/your/batch.bat.
      5. Enter arguments: ${resource_loc}.
      6. Click close.
      7. In the menu bar, select Run > External Tools > Oragnize Favourites...
      8. Click add.
      9. Tick the Launch Configuration you just created.
      10. Click OK.
      11. If you have multiple External Tools Favourites, you can change the order in which they appear here. This will affect the keyboard combination you use to access each one - changing the number used for the last key, 1-9 etc.
      12. Click OK.
      13. Now, after you have edited a VTL file (or CSS, JS etc) in Eclipse, select Run > External Tools > Upload current file with Cadaver or just use the keyboard combination Eclipse gives to each of those (Alt, R, E, 1-9).
And finally, here is the DOS Batch file.
:: Change these to suit your project.

set environmentLabel1=DEV
set environmentLabel2=Staging
set environmentLabel3=PROD
set environment1=http://dev.example.com/webdav/autopub/dev.example.com
set environment2=http://staging.example.com/webdav/autopub/staging.example.com
set environment3=http://prod.example.com/webdav/autopub/prod.example.com
SET MAX=3
SET PATH_SUFFIX=rootDir/


:: Do not change below here.
SET WORKING_DIR=%cd%
SET WORKING_DIR=%WORKING_DIR:\=/%
SET FILENAME=%~nx1
SET FULL_PATH=%~dp1
SET FULL_PATH=%FULL_PATH:\=/%
SET RELATIVE_PATH=!FULL_PATH:%WORKING_DIR%/=!

echo File: %1
echo FTP file name: %FILENAME%
echo Using relative path: %RELATIVE_PATH%
echo Using full path: %FULL_PATH%

For /L %%i in (1,1,%MAX%) Do (
   echo.
   echo.
   echo.
   echo.
   echo =======================================================================
   echo Transferring to !ENV_LABEL_%%i!
   echo open !ENV_%%i! > %temp%\%~n0.cadaver
   echo lcd %FULL_PATH% >> %temp%\%~n0.cadaver
   echo cd %PATH_SUFFIX%%RELATIVE_PATH% >> %temp%\%~n0.cadaver
   echo mput %FILENAME% >> %temp%\%~n0.cadaver
   echo bye >> %temp%\%~n0.cadaver
   c:\cygwin\bin\cadaver.exe --tolerant < %temp%\%~n0.cadaver
)
EndLocal
So there you have it. A DOS Batch script that you can use to upload a file to multiple dotCMS environments at the same time. Use it by itself on the command line or use with a tool like UltraEdit or Eclipse that understands how to run external commands.

STATUS_ACCESS_VIOLATION

Occasionally, I see this error from running the script.
1 [unknown (0xCA8)] cadaver 7944 exception::handle: Exception: STATUS_ACCESS_VIOLATION
- 557182 [unknown (0xCA8)] cadaver 7944 open_stackdumpfile: Dumping stack trace to cadaver.exe.stackdump
dav:!> open http://staging.example.com/webdav/autopub/staging.example.com
It also leaves me with a cadaver.exe.stackdump just under 1MB. I can just re-run the command and clear up the stackdump, but I sure would like to know if there is a way to stop them from happening.