Well, that was a huge waste of time

I noticed my picture gallery was showing a couple of albums twice, and when you clicked either of them, you got an error page. After trying about a million different things from various FAQs and from a guy who was being very helpful in the forums, I finally discovered something strange: the table g2_itemattributesmap had many duplicate entries. I deleted one of the dups (and manually reentered one, because there was no primary key or other way to uniquely identify one of the rows), one of the duplicate albums stopped showing twice, and when you clicked on it it worked correctly. So I did the same with another row, and suddenly the main page of the gallery stopped working. Oops. But in for a penny, in for a pound, so I continued on with the other duplicates and when I finished everything was working great.

I’m a little concerned about how this might have happened before. One thing I’ve noticed using Gallery is that the developers mainly seem to work in MySQL, and PostgreSQL (which I use) appears to be an after thought. And if there is one thing I know about MySQL, it’s that it implements a smaller subset of SQL than PostgreSQL, and it does some incredibly bizarre things that violate the SQL standard, especially when it comes to nulls. So I’m not surprised that MySQL-focussed developers don’t add unique contraints to a database table where the presence of duplicates causes things to break badly. I wouldn’t be surprised if MySQL doesn’t even support unique contraints correctly.