Monday, March 23, 2009

V6 DECODE64 not working properly

It turns out that after all these years, V6's built-in DECODE64 and ENCODE64 actually do not work properly with some binary data.

proc COMPARE_BASE64 {} {
set code {2lIU1ZNw5BYvKi79j4L/+GGmjAQK7uiBQG7elDdKZcE=}
set vgn_result [DECODE64 $code]; #VGN built-in function
set tcl_result [::base64::decode $code]; #TclLib tcl-only implementation
return [join [list \\
[BIN2HEX $vgn_result] \\
[BIN2HEX $tcl_result] \\
[string compare $vgn_result $tcl_result ] \\
] "\\r\\n"]
}
proc BIN2HEX { text } { binary scan $text H* result; return $result }
COMPARE_BASE64

--- result ----
da52145370e4162f2a2efd8f82fff861a68c040aeee881406e94374a65c1
da5214d59370e4162f2a2efd8f82fff861a68c040aeee881406ede94374a65c1
1

If you need to encode/decode base64 for use in non-vignette systems make sure you use tcllib's base64.

This was tested on Vignette's V6 but I am sure it's true for Storyserver 4.2 and V5 as well.

Thursday, March 19, 2009

MySQL indexes behaving badly

If you have a MySQL query that for no apparent reason stops working or no longer behaves as it used to, the first thing to do is EXPLAIN it. If the explain is unreasonable (for example, using different indexes then it used to or using an index but still scanning millions or rows) then you should try to CHECK TABLE.

One thing that I accidently found out was that CHECK TABLE also updates the key statistics. This made many problems magically disappear.

A few things that should be noted:
  1. CHECK TABLE will only update key statistics for MyISAM tables
  2. , for InnoDB you must use OPTIMIZE TABLE.
  3. If you use OPTIMIZE TABLE to update the key statistics you should be aware that OPTIMIZE TABLE does other (good) things as well, so it usually takes much longer and it locks your table while doing it. Also, for InnoDB it actually re-builds the table using ALTER TABLE.
  4. The CHECK TABLE have several options. Only the MEDIUM (the default) or EXTENDED update the key statistics. (i think).
So, if your indexes stop working, or just mis-behave, try to check them.