More MySQL Idiocities
I’ve been working on a small MySQL project on the side for a while now, and today I decided it was ready to go live, even though not all functionality I want is in place, but knowing myself, I’d probably just let it sit on the backburner for a month or two while I work on paying projects.
So here’s an interesting one involving views in MySQL. When I took it live, I noticed strange error messages and no content. After some digging around, I found one particular line saying the following:
Unable to connect for user ‘cameron’ @’localhost’
I thought, “Interesting, that database doesn’t belong to that user… Come to think of it, that username doesn’t even exist”.
Apparently when you create a view, any other user accessing it has to connect as the user who created it. In my case, I created the view on my workstation, so it tried to connect under the username, “cameron”, which didn’t exist on the actual server. Pretty damn retarded if you ask me.
March 7th, 2008 at 2:23 pm
Yup, I’ve been bitten by that before, too.
Here’s the workaround:
mysqldump db | sed s/’ DEFINER=`.*`’// > db.mysql
i.e. it strips out the DEFINER from the CREATE VIEW statement, so when you load the dump, the owner is set to the user you load it as.
March 10th, 2008 at 3:30 pm
Thanks for the workaround. Unfortunately, it’s too late for MySQL. After running into stuff like this over and over again while working on a series of small MySQL projects, I refuse to use it again.
Basically what it comes down to is this:
- MyISAM sucks, but it’s fast.
- InnoDB is slower at some things, but it supports transactions and many other features I use all the time. However, it still suffers from most of the quirks that MyISAM does, and transactions don’t always work like they should (try BEGIN; CREATE TABLE; ROLLBACK; …).
- Falcon is slow as hell from all of the benchmarks I’ve seen so far… I haven’t actually tested it.
- Maria? What the hell is this! Why do they always create new engines to “fix” the things that are “broken” in the previous one!?