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.
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.
DELETE FROM tablename rather than
TRUNCATE TABLE tablename.
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.
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 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: [backups] FTP command: Client "X.X.X.X", "STOR ./var/lib/mysql/test/items.MYD"
Sep 11 07:22:23 unknown ftp.info vsftpd: [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
To fix this, you must change the local directory on upload
and then call