5 Solutions for MySQL Error 1235

I got stuck for hours the other day with this problem. I’ve used triggers for years in SQL Server without a problem, however, in MySQL the implementation is a bit iffy.

One problem I ran into yesterday while trying to restore a database was error 1235:

ERROR 1235 (42000) at line 1408: This version of MySQL doesn’t yet support ‘multiple triggers with the same action time and event for one table’

I scoured Google for about an hour, and came to the conclusion that there is multiple reasons this error may appear. I explain these reasons and provide solutions for them below.

First of all, I suggest you get a list of all triggers by running the following command:

SELECT trigger_schema, trigger_name
FROM information_schema.triggers
WHERE trigger_schema = 'NAME_OF_YOUR_DATABASE';

Reason #1:  You can’t combine both BEFORE/AFTER with INSERT/UPDATE/DELETE

This PDF describes this issue. The problem is simple: You can’t have BEFORE_INSERT and AFTER_INSERT for the same table. You may have been updating a column BEFORE insert and updating some other table AFTER insert with the ID. I suggest you move some of this logic (maybe the BEFORE trigger) to your code.

Reason #2: AFTER_DELETE sometimes fails with error 1235

You should avoid using AFTER_DELETE triggers. Move the logic to a BEFORE_DELETE trigger if you are going to use the OLD variable. No idea why this happens, maybe it is my specific MySQL version.

Reason #3: You can’t have triggers with the same name (duplicate triggers)

Sometimes you get another error code when doing this, but other times you get the same error 1235 with no explanation. Run the “show triggers” query I stated above and look for any triggers with the same name. Always run DROP TRIGGER before creating/modifying a trigger.

Reason #4: You can’t reference the SAME TABLE you are updating/inserting to in a trigger

For example, if you wanted to set the default password for a user through a trigger upon creating a new user record, you might have tried to do this: UPDATE users SET password = ‘newPassword’;

The correct way to do this is to set the variable in the BEFORE_INSERT trigger. Example: SET NEW.password = ‘newPassword’;

Reason #5: When exporting a database (as an SQL script) you may get database definers in the script

In other words, if you export a MySQL database to a .sql file, the actual file may contain things like TRIGGER ‘mydbname’.'trigger_name’. So if you try to restore it to a database with a different name it would fail. I would have expected another error code because the database it is referencing is NOT the one I am restoring to. Anyway, you receive error 1235 for this as well.

Quick fix: Open the .sql file and replace all mentions of `EXPORTED_DB_NAME`. with a blank string (i.e. replace with nothing).

Summary / TLDR:

Do not use BEFORE/UPDATE triggers on the same table for a same function (e.g. INSERT). Do not use AFTER_DELETE triggers, use BEFORE_DELETE instead. Do not have duplicate trigger names. Do not reference the same table you are updating or inserting to in the trigger, use OLD and NEW instead with SET instead of a subquery. If you are restoring a database from a backup, check that the backup does not contain mentions to the explicit database name (when restoring to a different database name).

Please remember: When creating an INSERT trigger you can only use the NEW variable, when creating an UPDATE trigger you can use OLD and NEW, and in a DELETE trigger you can only reference OLD.

How to develop good software

As a software developer, I’ve come across many horrors in coding over the years. It’s no secret that most developers code things fast and ugly to get stuff out the door. But something strange happened to me recently; I think I am finally becoming a better coder.

I’ve been developing an ERP/MRP/CRM by myself for the past 3+ years and I’m finally reaching the release date. I’ve been testing the product with two clients for the past year, and naturally, many bugs have popped up out of nowhere, many features have been requested, and so on. You know… the typical software development cycle plagued with inadequacies.

However, I’ve finally learned a valuable piece of information I’d like to share:

Features, not rules.

I went in with a bunch of rules for the last meeting I had with my clients. They had to follow these rules to use the new features in my system correctly. They needed to follow certain processes in an orderly manner, they needed to avoid placing certain characters at the start of some files, they needed to use the correct extensions, they needed to avoid using certain half-baked process, they needed to process files at the end of the month and not in-between, etc.

Naturally, they were a bit taken aback. They are not computer-savvy, and they have a hard time grasping new things. “Why?” kept comming up all the time. I tried to explain that this is natural in a system so big, there are certain rules you must learn to avoid entering bad data, or messing up parts of the system, etc.

Halfway through the training I realised I was doing things the wrong way. Whenever you have to write down more than 3 rules for a process, it is almost always better to make the system handle all possible exceptions instead, and inform the user appropriately when they have made a mistake. It is easy to do this with forms for example, you write down some regex rules (or use JQuery Validate), you add extra back-end validation, you use a “form/validation” class, etc. However, when the processing takes place in the back-end (e.g. a file parser that changes content in the database), it is easy to expect too much of the users.

I left the meeting with all the rules I had written down scratched out and a page full of tasks to be performed.

I think it is vital for us to embrace this. A system must be able to handle anything thrown at it, it must be able to validate incorrect data, file extensions, and any other exception you may think of. It seems natural, most of you are thinking “this is so obvious, why write a blog post about it?”. Why? Well, because I keep seeing these kinds of mistakes all over the place. I’m sure many developers out there still think “oh, I’ll just explain the user how to do it”. Then you get a ton of calls of users asking why this or that is not working, and you have to explain that there is a certain process to be followed.

The answer is simple: Think before you code, and make whatever you code “think”.

How to deploy a static page to Heroku, the easy way!

I wanted to upload a simple website based on HTML, CSS and JS files, with a few images, and a favicon. So I registered at Heroku, bought a domain name, followed the Heroku instructions on how to set up the “Toolbelt” and GIT.

So I thought I was all set up: I created a folder with an index.html page, with the text “Coming Soon…”. I set up the repository, and tried to commit to Heroku, only to receive a message similar to “This is not a code repository” or something similar. Heroku is primarily aimed at apps running on frameworks, like Ruby on Rails, Django (Python), node.js, or… ahem… PHP.

So it seemed incredibly dumb that it can handle all these frameworks, but it doesn’t let me upload a static site! So I searched about, and found out how to do this by setting a demo (barebones) ruby app, as well as a Sinatra variation. However, this didn’t convince me. First of all I didn’t want to place my code in a “public” folder. No. I was probably being a bit picky, but when I expect things to work a certain way, I get annoyed when barriers are put up in my way.

I finally figured out that Heroku allows you to publish PHP sites as well, with NO configuration needed! That’s right, just create your .php files, and it will figure out that the Heroku app should run PHP for this site, and it will auto-configure the app to run on the latest version of PHP/Apache.

So here is how you do it:

Rename your index.html to home.html or something similar.

Create an index.php file with the following code:

<?php include_once("home.html"); ?>

That’s it!

Now go to your app folder in CMD (or Bash), and commit your code to Heroku using the following:

git add .
git commit -m 'Change this to a meaningful description'
git push heroku master

 And finally, you should get the following:

-----> Heroku receiving push
-----> PHP app detected
-----> Bundling Apache version 2.2.22
-----> Bundling PHP version 5.3.10
-----> Discovering process types
Default types for PHP -> web -----> Compiled slug size: 9.5MB
-----> Launching... done, v4

Congratulations! You have published a static site to Heroku with one line of PHP code :)

From a screenshot to a CSS ‘image’

I must say I was pretty impressed when I saw the Mona Lisa rendered using pure CSS. While talking about it with a friend, I told him I thought it had been done using an automated process, where a program would read an image as an array of pixels, and would output an HTML file containing a CSS section with a bunch of box-shadows that “represent” the image.

Why “represent”? Well, the amount of CSS required to represent each pixel in the image would be massive. Therefore, the better approach is to take a pixel from each 5×5 pixel block, which will represent that block, and use a CSS box shadow which will converge (fade) into nearby pixels. Therefore, giving an approximate representation of that the full image looked like.

So I set about doing a Python program that would do this. Shortly after finishing the Python version, my friend pointed me towards this file which does a similar thing using PHP. Oh well, at least I had fun coding it.

So, my program basically allows you to take a screenshot of your desktop and convert it into a CSS version of the image, or you can take a png/jpg file and do the same thing.

The output (example below) is pretty fuzzy, but I’m thinking I can probably improve it a bit by tuning the spaces/pixels, or by getting a better “sample” of the overall colour that predominates in a 5×5 pixel section of the image, instead of just using the first pixel in the section.

Get the code from Github, and if you have any suggestions, feel free to leave a comment below (or on Github).

Screenshot

My initial experience with ASP.NET MVC3 and Razor

A couple of weeks ago I was asked to develop a mobile application with data access in ASP.NET.

Naturally, I did a bit of research, tried out a few things, and finally came to the conclusion that .NET framework 4.0 was the best choice.

So I got down straight to development, using Jquery Mobile Framework, ASP.NET with .NET 4.0,  MVC3, ADO.NET Entity Framework 4.1, and ASPX (web forms).

It was easy enough to set up, but when it came down to implementing user controls, I found it a nightmare. Passing parameters to the user control whilst using a MultiView was giving me a headache.

The issue here is that web forms are not naturally set up to work via AJAX calls, and Jquery Mobile Framework relies entirely on AJAX calls. So I decided to try out the Razor layout engine instead of web forms.

I had no experience using the Razor layout engine, but after a couple of hours, I found it delicious to use. I could code up pages much faster than in web forms. And the integration with controllers and models in the MVC3 framework was just, natural, it all fit into place beautifully.

Naturally, I found a few issues learning the basics of the MVC3 framework, forms authentication, the Razor layout engine, and accessing data from a database. Tutorials seemed to be all over the place, but I came across a few good ones which I will share with you.

If you are getting started with MVC3 + Razor + Data access (using DBContext in MVC3), follow these tutorials, and you’ll be a ‘pro’ in a couple of days (providing you have enough experience).

Razor Layout Engine:

Learn the basics of the Razor layout syntax:

http://msdn.microsoft.com/en-us/gg618477

Learn how “sections” work in Razor:

http://weblogs.asp.net/scottgu/archive/2010/12/30/asp-net-mvc-3-layouts-and-sections-with-razor.aspx

Learn how to implement partial views in Razor (which can be used as ‘user controls’):

http://rachelappel.com/razor/partial-views-in-asp-net-mvc-3-w-the-razor-view-engine/

MVC3:

Learn how a basic MVC3 application works in .NET:

http://www.asp.net/mvc/tutorials/creating-a-mvc-3-application-with-razor-and-unobtrusive-javascript

Learn how to integrate data access (database first) using MVC3 and EF 4.1:

http://msdn.microsoft.com/en-us/data/gg685489

(If you want to create models, but use a MSSQL database that is not on your machine, and you don’t want to have it in the App_Data folder, just follow the steps, then delete the database from the app_data folder, and change the data source in the web.config to point to the correct location).

Writing your own queries to access the database instead of relying solely on those from the model:

http://blogs.msdn.com/b/adonet/archive/2011/02/04/using-dbcontext-in-ef-feature-ctp5-part-10-raw-sql-queries.aspx

So that’s it for now, I thought it would be good to have a central location for these tutorials as they served me well whilst I learned the basics of MVC3 + EF4.1 + Razor, so I hope they are useful to you as well.

If you have any queries, please let me know in the comment section below.