MySQL trigger error: Explicit or implicit commit is not allowed in stored function or trigger.

I haven’t seen this documented in MySQL docs, so I’ll share this little hidden nuissance. When compiling a trigger, MySQL throws the following error: Explicit or implicit commit is not allowed in stored function or trigger.

What does it mean?
The code inside the trigger is doing a commit. Looking at the code for the trigger alone is not enough, you must check all procedure/function calls, because the code inside any calls could really be (part of) the problem.

Problem?
The problem is usually we look in our code and find that there are no “explicit” commits. The problem is that there is a implicit commit happening somewhere and it is hard to pinpoint where if you didn’t know this one hidden fact:

Depending on version and storage engine, TRUNCATE can cause the table to be dropped and recreated. This provides a much more efficient way of deleting all rows from a table, but it does perform an implicit COMMIT. You might want to use DELETE instead of TRUNCATE.

Moral
DELETE FROM tablename rather than TRUNCATE TABLE tablename.

Avoid for (var x in array) when using jQuery/PrototypeJS

I was looking to make my code look more readable by “cleverly” using for (var x in array) loops instead of for (var x=0; x < array.length; x++), even though the shorter for loops are not supposed to be used with arrays, but used only with objects.

Turns out that jquery/prototypeJS put in extra hidden variables inside the array. If for whatever reason you are not using a JavaScript framework/library, you can use cross-browser shortcut: for (var x in array). However, be cautioned JavaScript experts are particularly annoyed by it.

Coldfusion 9 to 9.0.1 Update Error: “Variable ENABLEIMPLICITUDFREGISTRATION is undefined.” or Data Source page blank

If you see the following error in Coldfusion Administrator -> Server Settings -> Settings:
Variable ENABLEIMPLICITUDFREGISTRATION is undefined.

Or, when trying to add a Data Source, such as Microsoft Access (with Unicode), you see a blank page.

This is most likely because you have upgraded from a previous CF9 installation to either CF 9.0.1 or the CHF1 (hotfix 1 for 9.0.0 and 9.0.1). Sometimes you think you haven’t done an upgrade, but during the install process, pay particular attention, the CF9 Installer goes through a migration process, make sure to skip that.

It is actually easy to port over old settings by manually copying from one CF Admin panel to another, rather than try to figure out how to fix this bug. I’d like to add: there is no known fix, it is an open issue in CF Bug Tracker.

Filezilla FTP Server: “filename invalid” fix OR vsFTPD: “Could not create file”

Filezilla FTP Server can give a cryptic “filename invalid” message. Example:

(000004)9/1/2010 2:17:25 AM - backups (X.X.X.X)> STOR /var/lib/mysql/test/items.MYD
(000004)9/1/2010 2:17:26 AM - backups (X.X.X.X)> 550 Filename invalid

Or, vsFTPD can give a similar cryptic message “could not create file” message. Example:

Sep 11 07:22:23 unknown ftp.info vsftpd[8035]: [backups] FTP command: Client "X.X.X.X", "STOR ./var/lib/mysql/test/items.MYD"
Sep 11 07:22:23 unknown ftp.info vsftpd[8035]: [backups] FTP response: Client "X.X.X.X", "553 Could not create file."

It means that the filename specified (on upload) cannot be stored on the server because of invalid characters. The most common culprit is when you are trying to use the manual ftp command line, and calling
put /var/local/mysql/test/items.MYD

To fix this, you must change the local directory on upload
lcd /var/local/mysql/test/
and then call
put items.MYD