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

Photoshop: Saved Gif Turns Red When Adding Text

Sometimes you search something on google, and the most trivial answers do not show up. So I figured as a self reminder, I’ll post this here.

Does your background turn to a red tint when you add text to a GIF image saved as a PSD?
Well, do this to fix:

Open the .GIF in Photoshop. Covert the image to RGB (Image > Mode > RGB). Gifs are Indexed color and need to be converted to RGB. You’ll then be able to add layers and edit…then save as .PSD. or whatever format you choose.

Hackintosh doesn’t connect via ethernet

Does your Mac Ethernet/Wireless keep giving you a fake 169.254.x.x ip address?

Recently I started running Mac OS X Leopard (10.5.8) on my spare computer. It worked fine for a few days until one day when I changed my router, my Mac stopped connecting to the router. It would repeatedly get a 169.254.x.x ip address through DHCP. If I set the IP address manually, it would show that it assigned the IP address, yet it would still not “properly” be connected in the background.

So here is what I did to fix this problem. Open up Terminal using Spotlight. And type the command:

sudo ifconfig en0 ether 00:11:22:33:44:55

It will then ask you for your password. That’s it.

Explanation:

sudo: Gives you administrative power

ifconfig: the program that interacts with all your connectivity configurations

en0: the interface’s name, it could also be en1, en2, etc. (depending on the amount of network wired/wireless cards you have)

00:11:22:33:44:55: the mac address, pick whatever hex combination (i.e. only use characters 0-9 and a-z)

Please share your experiences.

Helpful Linux Tips

Command line

Download all specified extension files from an html page:

wget -r -t1 -N -np -A.mp3 http://google.com/music/audio/

-np dont ascend to parent
-r recursive
-l1 level DONT NEED
-N timestamping
-nd no directories DONT NEED
-t 1 = tries
-H span across hosts

To remove quotas, edit /etc/fstab and remove grpquota,usrquota,
then execute the remount, replacing /home with the name:

mount -o remount /home

Convert unix timestamp to readable format in Bash

date -d @1280565192

Kill multiple processes using grep:

kill -9 `ps aux | grep perl | grep nobody | awk '{print $2}'`

Xargs: handle spaces and punctuation properly:

xargs -0

Using ack and sed, edit files in place

sed -i 's/replacestring/replacedwiththis/g' `~/bin/ack --php "searchstring" -l`

Rename doesn’t support renaming with a dash/hyphen, so we must use this forloop/mv hack:

for i in ./*foo*;do mv -- "$i" "${i//test test2/test - test2}";done

Find Command

COPY files less than 24 hours old to /some/other/directory

find . -type f -ctime -1 | xargs -I {} cp {} /some/other/directory

MOVE files less than 24 hours old to /some/other/directory

find . -type f -ctime -1 | xargs -I {} mv {} /some/other/directory

Scan files for certain text

find dir/ -name "*.txt" -exec grep -Hn "md5_func" {} \;

Find all directories and sub-directories that are empty.

find ./ -type d -empty

MySQL

Using mysql from command line, here’s how to save results to an outfile (in interactive mode):

SELECT * INTO outfile '/tmp/sql.out' FROM tablename WHERE condition  = '1';

Using mysql from command line, here’s how to save resultset to an outfile (using a sql file):

mysql database -u username -p < batch.sql > sql.out