» tagged pages
» logout

sorted by: recent | see : popular
Content Tagged with User:marc + MySQL

When MySQL Attacks!!!

The Setup

Imagine, if you will, the following scenario:

  • You design a whole new database schema for your cool new scalable web-application. You’re using MySQL and the InnoDB datbase engine for everything, because your schema is so cool it uses all sorts of foreign keys and transactions and the like.
  • You quickly set up MySQL and get your application going with your new schema on your development staging machine.
  • You get MySQL up and running on your live server, play around with it for a bit to make sure it’s working, and then set up a my.cnf file with all sorts of caching and security goodies in it.
  • You do a backup from your dev machine, restore it to the live server, and ta-daa!!! Your web application is up and running on your live server.

What you might not have noticed, especially if you – like me – have a few thousands rows of data, is that MySQL might have screwed you along the way and not really told you all that clearly.

The Problem

After a few days of operation on my live server, I started to notice a few weird things—foreign keys weren’t being enforced properly, and there were some values in the database that probably shouldn’t have been possible. I furrowed my brows and put it on my list of stuff to investigate.

Well, yesterday, I added a new table to the database, and it went something like this:


mysql> CREATE TABLE Fudgecicles
>(
>  id INTEGER AUTO_INCREMENT PRIMARY KEY,
>  value VARCHAR(255) NOT NULL,
>)
>ENGINE = InnoDB;

Query OK, 0 rows affected, 1 warning (0.10 sec)

Where did that warning come from?


mysql> SHOW WARNINGS;

It is here that MySQL tells me:


+---------+------+-----------------------------------------------------+
| Level   | Code | Message                                             |
+---------+------+-----------------------------------------------------+
| Warning | 1266 | Using storage engine MyISAM for table 'Fudgecicles' |
+---------+------+-----------------------------------------------------+

Augh! No! Bad! Bad, MySQL, Bad! Why on earth would it do that? I didn’t misspell InnoDB or even use “incorrect” casing in the name. There’s nothing wrong with the schema I specified and I’ve done this hundreds of times before.

Well, after some research, I then tried the following:


mysql> SHOW ENGINES;

And MySQL helpfully gave me the following:


+------------+---------+----------------------------------------------------------------+
| Engine     | Support | Comment                                                        |
+------------+---------+----------------------------------------------------------------+
| MyISAM     | DEFAULT | Default engine as of MySQL 3.23 with great performance         |
| MEMORY     | YES     | Hash based, stored in memory, useful for temporary tables      |
| InnoDB     | DISABLED| Supports transactions, row-level locking, and foreign keys     |
| BerkeleyDB | NO      | Supports transactions and page-level locking                   |
| BLACKHOLE  | NO      | /dev/null storage engine (anything you write to it disappears) |
| EXAMPLE    | NO      | Example storage engine                                         |
| ARCHIVE    | YES     | Archive storage engine                                         |
| CSV        | NO      | CSV storage engine                                             |
| ndbcluster | NO      | Clustered, fault-tolerant, memory-based tables                 |
| FEDERATED  | NO      | Federated MySQL storage engine                                 |
| MRG_MYISAM | YES     | Collection of identical MyISAM tables                          |
| ISAM       | NO      | Obsolete storage engine                                        |
+------------+---------+----------------------------------------------------------------+
12 rows in set (0.00 sec)


The InnoDB database engine had been disabled somewhere along the way and I hadn’t even noticed. It is enabled, by default, on the standard Linux and Mac OS X MySQL binaries that I’ve been downloading. So something changed along the way that made this all stop.

Far worse, I then began to worry about my existing tables, all of which we supposed to be InnoDB. Upon executing the following for each of them:


mysql> SHOW CREATE TABLE Fudgecicles;

I would see something like:


| Table       | Create Table
| Fudgecicles | CREATE TABLE `Fudgecicles` (
  `id` int(11) NOT NULL auto_increment,
  `value` varchar(255) NOT NULL,
  PRIMARY KEY  (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8

Sure enough, every single one of my tables was MyISAM, and not the InnoDB it was supposed to be.

The Reason

The problem turns out to be that InnoDB is somewhat finicky about my.cnf settings, and will frequently refuse to operate if settings change in this file in such a way that makes any existing data incompatible with the way it would write new data.

In my case, it turns out that changing the settings for the InnoDB binary data and log file sizes were somehow incompatible with the existing binary data and log files (ibdata1 and ib_logfileX). Upon starting the server, InnoDB finds this inconsistent state and simply refuses to start up. This is the first problem.

The second, and far more serious problem, is that MySQL just switches the database persistence engine on you and only provides a little warning. If you’re loading in thousands – if not tens of thousands – of rows, those warnings are easily lost in the scroll-a-thon that ensues. This is bad behaviour. MySQL should simply refuse to create your table if your selected persistence engine is not available.

The Solution

Fixing this problem involved three parts. The first, and easiest, is to get the InnoDB engine back. You have two choices:

  1. You can either revert to the original settings you had for the my.cnf file to remove the inconsistent state that is freaking out poor ole’ InnoDB.
  2. You can delete the idbdata1 and ib_logfileX files with the inconsistent sizes, thus removing in a different way the same inconsistent state, and allowing InnoDB to startup with your new (and presumably better) configuration values in my.cnf.

I chose the second method.

WARNING: Using this second method can result in data-loss if you’re not careful. Some database engines store things in the binary data and log files before writing them to the actual table files, and if they’re deleted, you might lose those changes. I only selected this path because all my tables were MyISAM, I had a full backup, and spent a good 10 minutes after deleting the files verifying that all data were correctly restored.

You then stop and restart the MySQL database engine, and InnoDB will be back. You can verify that all is well in InnoDB land by executing:


mysql> SHOW InnoDB STATUS;

And you will receive a gloriously long and detailed set of information on how things are going.

Part two of the process involved converting my tables back to InnoDB.


mysql>  ALTER TABLE FishSticks ENGINE = InnoDB;

This proves to be tedious and time consuming, as you cannot simply go from table A to table Z doing the conversion – because of the foreign keys and references, they have to be done in the right order. When one of the ALTER TABLE statements fails, you can find out what happened by re-executing the SHOW InnoDB STATUS command – it will tell you why it wouldn’t convert the table to InnoDB.

But, eventually, I got them all done. It was then that I noticed that none of the foreign keys were set up properly any more.

So, the last step of the process is to re-establish the FOREIGN KEYs. I did this by, for each foreign key in each table I had, executing the following commands:


mysql> ALTER TABLE FishSticks DROP KEY [dead foreign key name];
mysql> ALTER TABLE FishSticks ADD FOREIGN KEY (keyname) REFERENCES Table (fieldname);

The good news is that saving my database and getting back to all sorts of InnoDB goodness only took about an hour in total, for about 30 tables or so.

However, if MySQL had simply reported an error a few days earlier instead of blithely just switching tables types behind the scenes, I might have avoided this whole mess in the first place. Oh well, at least I learned a few neat little commands I can play around with now! Lesson learned!

Here’s to hoping that this article helps some other folks solve the same problem a bit quicker!

User:marc: Chipmunk Ninja Technical Articles

PHP Book Addenda I

As I sat down to edit “Core Web Application Programming with PHP and MySQL”, I would sometimes find errors in the text so blindingly obvious and stupid that I would question whether or not I was truly qualified to write such a book. And yet, after talking with some other people who write books (and recalling days when I wrote huge amounts of code), it seems that this is all common and with much proof-reading and the hard work of some friendly reviewers, I was able to write a book of extremely high quality.

Of course, that just meant I would be even more devastated when the first technical errors WERE found in the book.

There have been a couple, but they’re not that killer serious.

Chapter 21 Error

In Chapter 21, where the book discusses writing your own output handler, the constant in the $_SESSION array to check is HTTP_ACCEPT_ENCODING, without the letter ‘S’ on the word accept.

The Accompanying Source Code

There are a couple of errors in the source code, the most glaring of which DID get fixed, but never made it on to the shipping CD ROM (d’oh!). In the SimpleBlog sample, in the file lib/entrymanager.inc, the class DBManager is accidentally misspelled DBMananager. Just fix it and change it back to the correct spelling and the sample will compile fine under PHP 5.0.x (x <= 4).

The other problem with the samples is that some new things have appeared in newer versions of PHP 5.0.y (y >= 5). PHP now defines a class called InvalidArgumentException, which conflicts directly with the class I have defined using the same name. The easy fix for this problem is to simply change the name of the class slightly, to something like MyInvalidArgumentException or some such thing.

To save you the hassle of tracking down and fixing all of these problems, I have put a new copy of the book source code up on the chipmunkninja.com servers. You can download these from here: phpwasrcupdate_2005-12-01.zip.

As always, if you see any other problems or errors in the book, or just want to comment on it, please feel free to drop me some mail.

I remain chagrined, but I’ll get over it.

User:marc: Chipmunk Ninja Technical Articles

My Descent into Addiction

I never really intended for it to spiral out of control like that. I had just started writing my book (a programming book on designing and writing web applications using outrageously nerdy technologies – truly the “Great American Novel”), and found myself frequently needing a little pick-me-up that only mind-altering substances could provide. You wouldn’t think that writing a book would be all that hard – you either know a lot about something or learn about it, and then sit down and write about it in your chosen language (which I am currently pretending still qualifies as “English”). The material to be covered should be planned out well in advance and one can thus sit down and write, write, Write!

User:marc: wefwafeafasdf

My Descent into Addiction

I never really intended for it to spiral out of control like that. I had just started writing my book (a programming book on designing and writing web applications using outrageously nerdy technologies – truly the “Great American Novel”), and found myself frequently needing a little pick-me-up that only mind-altering substances could provide. You wouldn’t think that writing a book would be all that hard – you either know a lot about something or learn about it, and then sit down and write about it in your chosen language (which I am currently pretending still qualifies as “English”). The material to be covered should be planned out well in advance and one can thus sit down and write, write, Write!

User:marc: blargle

My Descent into Addiction

I never really intended for it to spiral out of control like that. I had just started writing my book (a programming book on designing and writing web applications using outrageously nerdy technologies – truly the “Great American Novel”), and found myself frequently needing a little pick-me-up that only mind-altering substances could provide. You wouldn’t think that writing a book would be all that hard – you either know a lot about something or learn about it, and then sit down and write about it in your chosen language (which I am currently pretending still qualifies as “English”). The material to be covered should be planned out well in advance and one can thus sit down and write, write, Write!

User:marc: Chipmunk Ninja Writing, Creative or Otherwise

My Descent into Addiction

I never really intended for it to spiral out of control like that. I had just started writing my book (a programming book on designing and writing web applications using outrageously nerdy technologies – truly the “Great American Novel”), and found myself frequently needing a little pick-me-up that only mind-altering substances could provide. You wouldn’t think that writing a book would be all that hard – you either know a lot about something or learn about it, and then sit down and write about it in your chosen language (which I am currently pretending still qualifies as “English”). The material to be covered should be planned out well in advance and one can thus sit down and write, write, Write!

Alas, it does not seem to work out that way. You sit down—and draw a complete blank. The notes that you have so carefully researched, planned out, and written up sit uselessly at your side why you wonder why the hell you’re writing a book in the first place. What possible qualifications do you, a computer nerd with no previous writing experience, have to write a six to eight hundred page book that people will use to write serious applications and perform non-trivial financial transactions?

And so, my writing day would begin. I actually do know what I’m talking about, and I know what I need to do. It can just take a bit of a kick to the derrière (definitelynot English) to get going. It is here where a little chemical boost comes in handy

I started out with maybe one hit a day. I would dope myself up, sit down, and churn out a good thirty-odd pages of writing. At the end of the day, I would go home, having not earned a single penny, but still possessed with a feeling of accomplishment – I was one chapter closer to the end of the book. Some days (it eventually grew to be “most days”) I would give myself a reward before going home, so that I still had some energy for the evening.

Then came the difficult patches. Chapters for which I was not entirely prepared when I sat down to write. I had done the research, and planned out a sequence of topics, but I would still find myself strangely requiring more time to fully convert them into the pages of text they would need to become. On these days, a little extra boost would be just what I needed to keep me going throughout the day. I was up to a good three times a day now, and the book was proceeding. Chapters 1-12 flew off my fingertips into the laptop.

I started running around the lake nearby. Spending most of the last decade in front of computers had left me a little less athletic than I would have liked. I learned that the huffing and puffing would suck ever so slightly less if I gave myself a little “present” before tying up the cross-trainers. Running around the lake became just another excuse to drug myself up a some point during the day.

It was in late November or early December that the earthquakes started. Seattle is known to lie on a number of earthquake faults, and is supposed to be only slightly less dangerous a place in which to live than California. One can only imagine the glee with which the local news media trumpet this fact on a weekly basis. When we are not being told that our neighbours are axe murdering child molesters, we are being warned that the if the city does not first fall into the ocean because of a monstrous quake causing horrible loss of life (at least a couple hundred people, including women and children!), the big pretty mountain nearby – which last erupted some zillions of years ago, thereby still qualifying it in the most vague of definitions as an “active volcano” – will suddenly explode and bury us all in hot ash and and lava while we sleep, and asphyxiate those who survive in a cloud of poison gas.

The earthquakes we had were all pretty mild, and would typically occur in the wee hours of the morning, perhaps around three or so. I would awaken to the bed shaking and a strong feeling of tremors. I would always look around to see what the cats were up to, and it was with some surprise that I would see them still fast asleep, usually on my wife. Her refusal to move them no matter how uncomfortable they made her sleep was always infinitely more pleasing to them than my insistence on chucking them off of me if they were sitting on my head. Looking outside the bedroom window, I would see no swinging of lampposts, and the trees were usually still in the night calm.

In the morning, I would ask my wife, “did you feel the earthquake, honey?” To which I would get a quizzical look and the inevitable “No. There was an earthquake? Are you sure?”. I was. At least for the first three or four over the first week. It was during the second week of regular quakes that she looked at me one morning at breakfast and announced: “I know what it is: I’m bouncing around in the bed and you’re feeling it shaking.” True, she does move around a lot when she sleeps (part of the self-contortions required to keep two cute cats happily sleeping), and our bed is an Ikea wooden bed that tends to send any energy sent its way straight back at the occupants.

So, for the the next two or three quakes, I found myself not worrying much about them. Still, to calm my nervous mind, I started looking up as soon as the I felt the quakes, and noticed that neither the wife nor the cats demonstrated even the slightest bit of evidence that she had been shifting around. Instead, I saw the usual slumbering mass shares my bed. Something wasn’t quite right.

It was not too long after the quakes that the dizzy spells began. Every day – without fail – I started feeling dizzy around eleven-thirty, twelve o’clock. I would find myself unable to concentrate much and further hits to try and improve my attention proved completely useless. I grew worried. Having had such good fortune in the early stages of writing, I had committed myself to a rather aggressive schedule with the publisher. Losing momentum was simply not an option.

Despite my best hopes to the contrary, the jogging (if you can really call it that – lumbering or lurching might be entirely more appropriate) didn’t seem to help much. The earthquakes, which were now clearly something I could self-diagnose as “night tremors”, and the dizzy spells continued unabated. My slouching over in uncomfortable chairs to type on my laptop sitting on wobbly, crappy tables that barely held much else was also causing my body to express some dismay with me in the form of a muscular stitch in my side.

I was dying. It was clear to me that my addiction was ballooning out of control and that it, combined with the other abuse I was heaping on my body – awful slouching and hour after hour in front of the most unforgiving laptop – was killing me methodically and painfully. I hadn’t been to the doctor for a physical in a good ten years (I just never got around to it) and it was patently obvious that I was sporting at least a dozen grapefruit sized tumours in various places in my body that were squeezing the life out of me.

In a desperate attempt to save myself and identify the source of the problem (instead of just doing the smart thing and going and seeing my damn doctor), I started trying to cut things out of my life to see what was doing the serious damage. I quit the drugs cold turkey, and while I couldn’t stop writing the book – this was a long-time dream on the cusp of being realised that I did not wish to jeopardise – I could at least stop hunching in such a quasimodoesque fashion and work on better posture.

To be sure, I decided to cut down on any sugar in my life (since that’s bad for you as well), and resolved to drink a glass of red wine (since that’s extremely good for you and only a fool doesn’t know that). If there was a magic recovery, I could work to identify the real culprit and if there was no recovery, I clearly was dying and would truly need to see the doctor.

I slept ¡como las muertas! for the next two weeks. Fifteen hours a day was insufficient. I would go to bed at ten in the evening, wake up approaching noon, and would usually take a nap sometime in the afternoon. During the few waking hours left in my day, I would write – now with better posture – and still exercise, since clearly that was not the source of my problems (although it sure did suck that much more without any chemical assistance). The classic symptoms of chemical withdrawal were playing out before me – extreme fatigue, lack of focus, and general dissatisfaction with life. I had officially become … a junkie.

Slowly, the dizzy spells went away. As did the “earthquakes”. After two weeks, I was back to feeling mostly normal, although I had resolved that a trip to the doctor would probably still be a good idea.

For the holiday season, we travelled to Germany to visit some dear friends; the husband is a doctor and the wife, a former executive, now just enjoys the good life being married to a doctor. We mentioned my recent lifestyle to her, at which point her eyes nearly popped out of her head.

“You drank HOW many doppio espressos every day?” The rise in her voice expressed both shock and consternation. “My brother went to the doctor recently with many health problems such as shivering, dizziness, and tremors, and the doctor told him to stop drinking coffee or he would be dead inside of three months.”

Awesome. I had officially taken grabbing a quick coffee to a whole new level – one that threatened my health and my life. The sad part is, I don’t even really like coffee. I used to avoid it entirely, disliking the bitter taste immensely. Three years ago, however, while studying italian in Rome, I found myself extremely fatigued one morning and couldn’t resist heading to the local “bar” for un caffè, a lovely pick me up that gave me the worst migraine for hours. To this date, I still cannot drink drip coffee, but a good Italian espresso is hard to beat for flavour and stimulation.

After coming back from Germany, I returned to the cafè where I did most of my writing. I did, however, cut down significantly on the caffeine. Instead of three or more double espressos or entire pots of tea, I would have a cup of decaffeinated tea or something else equally innocuous. I went and saw my doctor, who, after much poking, prodding, and drawing of all sorts of blood with various diabolical looking needles, told me there was nothing wrong with me, and if I’d just lose a few more pounds, would be the very model of a modern major general – or at least a healthy geek.

The book was finished after six total months of writing. A few chapters were quite rough – I caught the flu for two weeks in January (more withdrawal?) which was reflected quite clearly in the writing that occurred during that timeframe – but great feedback from reviewers and lots of time spent proofreading and reviewing helped me write a book that, while not perfect, is something of which I am quite proud.

I however, find myself continually having to be careful with the caffeine. In such small and concentrated doses, it is entirely too easy to throw back multiple espressos without even realising it. Why the American government spends billions “proving” that marijuana is bad for you (“wow, if you zap mice with painful electrodes whenever they don’t take the drugs, you can get them to consistently prefer taking drugs to getting zapped!”) while doing nothing about the caffeine addiction pandemic sweeping the nation is entirely unclear to me.

Which is why I will likely spend more of my life in cafés than in the corridors of power. At least I have my “escape” mechanism.

User:marc: Chipmunk Ninja Writing, Creative or Otherwise

My Descent into Addiction

I never really intended for it to spiral out of control like that. I had just started writing my book (a programming book on designing and writing web applications using outrageously nerdy technologies -- truly the "Great American Novel"), and found myself frequently needing a little pick-me-up that only mind-altering substances could provide. You wouldn't think that writing a book would be all that hard -- you either know a lot about something or learn about it, and then sit down and write about it in your chosen language (which I am currently pretending still qualifies as "English"). The material to be covered should be planned out well in advance and one can thus sit down and write, write, Write!

Alas, it does not seem to work out that way. You sit down -- and draw a complete blank. The notes that you have so carefully researched, planned out, and written up sit uselessly at your side why you wonder why the hell you're writing a book in the first place. What possible qualifications do you, a computer nerd with no previous writing experience, have to write a six to eight hundred page book that people will use to write serious applications and perform non-trivial financial transactions?

User:marc: Chipmunk Ninja Writing, Creative or Otherwise