Pythoneer

A short time ago I found this very well done podcast: Hanselminutes by Scott Hanselman. In one of the latest episodes he was talking to Jonathan Zuck of ACT Online about Software Licensing. It’s great to get a quick overview about existing licenses and how to best use them. Since this is a huge topic you only get a short overview, but very understandable imho!
Especially interesting was what Jonathan said about the MySQL licensing model, which I had not properly understood all the way until now neither :-). He said since MySQL owns the rights of the software they can decide what license to sell a software under to a customer and

relief them of the burden of a GPL based license, by simply selling it to them commercially.

(starts at 27:30). That’s pretty well explained I think. It makes all this licensing stuff much simpler in my eyes.
Thanks Scott for your great podcasts!

The titles of the videos look very much like “must watch”. Found via ThinkPHP, thanks.

I have to deal with it now and a lot of other people too, judging by the utf8/unicode/encoding topics on the django mailing list. I have found this one thread quite interesting and looks like the problem solver, but may be I also just need to learn a bit more about the bits and pieces that make this whole thing work. This message obviously tells how to make mysql completely aware and well-handling utf8.
Next thing on the list the django setting parameter DEFAULT_CHARSET.

Read here how to change all your DB to utf8, pretty simple, but you gotta know it. Even when your app runs in latin1 only, it reduces a couple problems in case you forgot some check and utf8 is slipping through. I will let you know if I can prove that right. But since Django is trying to be unicode compliant, it’s easier to use it than to work around it.
It does at least already eliminate the problem of getting this error:

Illegal mix of collations (latin1_swedish_ci,IMPLICIT) and (utf8_general_ci,COERCIBLE)

this was discussed here.
In short:

ALTER TABLE tbl_name CONVERT TO CHARACTER SET utf8 COLLATE utf8_general_ci;

I was fighting four days now, with a threading problem, which are known to be hard to track. But I finally found it and learned that I actually had made a beginner’s mistake.

What happened?
From the front end I trigger via AJAX a view that again starts a thread that does some import work, that might take quite a while. This enables the user to keep going and have the import run without interrupting him/her. Every once in a while an asynchronous call checks on the state of the import.
And here lies the problem: while the thread is running and busy like a bee adding data in the DB the asynchronous call to check on the state also tries to run a query and that causes the following exception:

...
 File "/Users/cain/programming/django/trunk/django/db/backends/mysql/base.py",
line 42, in execute
   return self.cursor.execute(sql, params)
 File "/Library/Frameworks/Python.framework/Versions/2.4/lib/python2.4/site-packages/MySQLdb/cursors.py",
line 137, in execute
   self.errorhandler(self, exc, value)
 File "/Library/Frameworks/Python.framework/Versions/2.4/lib/python2.4/site-packages/MySQLdb/connections.py",
line 33, in defaulterrorhandler
   raise errorclass, errorvalue
ReferenceError: weakly-referenced object no longer exists

That really made me mad, as you can imagine. The problem was that I didn’t really know where to start debugging and seraching. Well, debugging was hard anyway, since it was multi threaded and I didn’t yet spend the time to get my WingIDE to run Django so I could debug it, but that’s another problem.

The problem and it’s solution
So after trying and a lot of thinking I found out that it had something to do with the connection. Finally I found out that the connection object was shared between the threads in the MySQLdb/cursors.py, so it seemed not to be thread-safe. The problem only occured when args was passed to the execute() method, so I dove deeper this way. And found out that the connection.literal() call was actually causing the problem. My guess now is that the connection got reseted by the main thread and was not available anymore, so that the exception above was thrown.
I found it. But too late, at this moment I thought I should upgrade MySQLdb, and I did. (I am working with the Django trunk, so I could not be more up to date on this front.) And after this upgrade the problem was solved. How could I forget to try and upgrade first? I got an excuse, I thought the problem was in Django, not in MySQLdb.
Now I only needed to see if the problem I had found was also the one that got fixed. Since I had learned some stuff about weakrefs and threading, etc. these days I more or less knew what to look for. And there it was.

from weakref import proxy
self.connection = proxy(connection)

That part in the __init__() method of BaseCursor was the bit that obviously solved my problem. I didn’t verify it, but I am pretty sure.

The upgrade
When it occured to me that I should upgrade MySQLdb, I checked my current version number of course.

>>> import MySQLdb
>>> MySQLdb.version_info
(1, 2, 0, 'final', 1)

Note: That is the version that has the threading problem!!!

So I upgraded to MySQLdb 1.2.1_p2 the version that fixes the problem above.

But I didn’t say it was easy to “just” upgrade:

>>> import MySQLdb
Traceback (most recent call last):
  File "", line 1, in ?
  File "/Library/Frameworks/Python.framework/Versions/2.4/lib/python2.4/site-packages/MySQLdb/__init__.py", line 34, in ?
    from sets import ImmutableSet
ImportError: cannot import name ImmutableSet

Grrrr …
But the problem and it’s solution were found quickly, even on a Django site http://code.djangoproject.com/wiki/InstallationPitfalls.
After removing the sets.py(c) it worked just fine.

>>> import MySQLdb
>>> MySQLdb.version_info
(1, 2, 1, 'final', 2)

Good luck threading away …

MySQL told me that it had a problem renaming a file. So I really thought I would go and help it by removing the file on the command line. But somehow I really felt that it didn’t really mean to rename a file. So I did a bit of googling and in the mysql bug tracker I found the problem.
The real problem occurs when you are using foreign key constraints with InnoDB. Even though I had only created an additional unique index on one of two columns where one of them was a foreign key. And when I tried to remove it mysql told me it had problems renaming a file :-(.
The solution was to remove the foreign key using ALTER TABLE tablename DROP FOREIGN KEY fkname. Now I was able to remove my index and then I added the foreign key constraint again.
Really ugly.

Now that I am a happy user of PHP5 and MySQL5 and using that for writing a new application, which fortunately involves using the newest technologies, I am finally able to solve one of the biggest issues I always had had with DB-driven applications: foreign key contraints.

Constraints and transactions
I am using InnoDB (I know those contraints had been possible before MySQL5) for that and the first time I am also using the foreign key constraints InnoDB provides. And it does make me happier :-). I don’t have to take care of updating and deleting all the referenced table rows myself, the DB’s storage engine does that for me, as I always had wanted it.

I had started out using MyISAM tables and triggers, but when I realized that I am programming the application using transactions I had to switch to InnoDB for it to have any effect at all. Transactions solve the next problem I always had, uncompleted or failed database modifications.
I upgraded my mind too and was able to drop the triggers, doh. Oh man, that was a long process, even though all this knowledge was available already in some corner of my mind.

Watch out
But I actually wanted to write down the one thing that I had been looking for for a while:
Create foreign keys only on columns with the exact same type!
Of course. Yes, of course. Makes sense, but I just wanted to have a go and try the foreign key constraint and didn’t watch out for the types, then I got the error:

ERROR 1005: Can’t create table (errno: 150)

And the manual just told me the standard things, but not this!

Won’t work
CREATE TABLE chapter (
chapter_id int(10) NOT NULL auto_increment,
title varchar(20) NOT NULL,
PRIMARY KEY (chapter_id)
) ENGINE=InnoDB;

CREATE TABLE subchapter (
subchapter_id int(11) unsigned NOT NULL auto_increment,
chapter_id int(11) unsigned NOT NULL,
title varchar(20) NOT NULL,
PRIMARY KEY (subchapter_id),
KEY chapter_id (chapter_id),
FOREIGN KEY (chapter_id) REFERENCES chapter(chapter_id)
ON UPDATE CASCADE ON DELETE CASCADE
) ENGINE=InnoDB;

The two columns chapter_id are of a different type, int(10) and int(11), and one is signed the other unsigned! Only if you get both types exactly right the creation of the foreign key constraint will be successful! Otherwise you get the meaningless error message mentioned above, which does not really help.

Will work
CREATE TABLE chapter (
chapter_id int(11) unsigned NOT NULL auto_increment,
title varchar(20) NOT NULL,
PRIMARY KEY (chapter_id)
) ENGINE=InnoDB;

CREATE TABLE subchapter (
subchapter_id int(11) unsigned NOT NULL auto_increment,
chapter_id int(11) unsigned NOT NULL,
title varchar(20) NOT NULL,
PRIMARY KEY (subchapter_id),
KEY chapter_id (chapter_id),
FOREIGN KEY (chapter_id) REFERENCES chapter(chapter_id)
ON UPDATE CASCADE ON DELETE CASCADE
) ENGINE=InnoDB;

Let’s see what’s coming after InnoDB …