September 30, 2006
Guide to Incompatibilities when Upgrading MySQL to Version 5.0
The obvious follow-up post to the Guide to Incompatibilities when Upgrading MySQL to Version 4.1
is a look at the incompatibilities when moving to version 5.0.
Again, I emphasize the importance of being educated about how upgrading MySQL may affect your application. These are the obvious, flagged items, but I recommend reading all of the 5.0 release notes.
Here are the items from the MySQL 5.0 release notes that are marked Warning: Incompatible change:
- C API change: mysql_shutdown() now requires a second argument. This is a source-level incompatibility that affects how you compile client programs; it does not affect the ability of compiled clients to communicate with older servers. (5.0.1)
- SHOW STATUS now shows the session (thread-specific) status variables and SHOW GLOBAL STATUS shows the status variables for the whole server. (5.0.2)
- A consequence of the change in handling of the DECIMAL and NUMERIC fixed-point data types is that the server is more strict to follow standard SQL. For example, a data type of DECIMAL(3,1) stores a maximum value of 99.9. Previously, the server allowed larger numbers to be stored. That is, it stored a value such as 100.0 as 100.0. Now the server clips 100.0 to the maximum allowable value of 99.9. If you have tables that were created before MySQL 5.0.3 and that contain floating-point data not strictly legal for the data type, you should alter the data types of those columns. (5.0.3 & 4.1.2)
- For user-defined functions, exact-value decimal arguments such as 1.3 or DECIMAL column values were passed as REAL_RESULT values prior to MySQL 5.0.3. As of 5.0.3, they are passed as strings with a type of DECIMAL_RESULT. (5.0.3)
- The C API ER_WARN_DATA_TRUNCATED warning symbol was renamed to WARN_DATA_TRUNCATED. (5.0.3)
- MyISAM and InnoDB tables created with DECIMAL columns in MySQL 5.0.3 to 5.0.5 will appear corrupt after an upgrade to MySQL 5.0.6. Dump such tables with mysqldump before upgrading, and then reload them after upgrading. (The same incompatibility will occur for these tables created in MySQL 5.0.6 after a downgrade to MySQL 5.0.3 to 5.0.5.) (5.0.6)
- The behavior of LOAD DATA INFILE and SELECT ... INTO OUTFILE has changed when the FIELDS TERMINATED BY and FIELDS ENCLOSED BY values both are empty. Formerly, a column was read or written the display width of the column. For example, INT(4) was read or written using a field with a width of 4. Now columns are read and written using a field width wide enough to hold all values in the field. However, data files written before this change was made might not be reloaded correctly with LOAD DATA INFILE for MySQL 4.1.12 and up. This change also affects data files read by mysqlimport and written by mysqldump --tab, which use LOAD DATA INFILE and SELECT ... INTO OUTFILE. (5.0.6 & 4.1.12)
- Previously, conversion of DATETIME values to numeric form by adding zero produced a result in YYYYMMDDHHMMSS format. The result of DATETIME+0 is now in YYYYMMDDHHMMSS.000000 format. (5.0.8 & 4.1.13)
- The namespace for triggers has changed. Previously, trigger names had to be unique per table. Now they must be unique within the schema (database). An implication of this change is that DROP TRIGGER syntax now uses a schema name instead of a table name (schema name is optional and, if omitted, the current schema will be used). (5.0.10)
- Beginning with MySQL 5.0.12, natural joins and joins with USING, including outer join variants, are processed according to the SQL:2003 standard. The changes include elimination of redundant output columns for NATURAL joins and joins specified with a USING clause and proper ordering of output columns. The precedence of the comma operator also now is lower compared to JOIN. (5.0.12)
- A lock wait timeout caused InnoDB to roll back the entire current transaction. Now it rolls back only the most recent SQL statement. (5.0.13)
- For BINARY columns, the pad value and how it is handled has changed. The pad value for inserts now is 0x00 rather than space, and there is no stripping of the pad value for selects. (5.0.15)
- The CHAR() function now returns a binary string rather than a string in the connection character set. An optional USING charset clause may be used to produce a result in a specific character set instead. Also, arguments larger than 256 produce multiple characters. They are no longer interpreted modulo 256 to produce a single character each. (5.0.15)
- The InnoDB storage engine no longer ignores trailing spaces when comparing BINARY or VARBINARY column values. This means that (for example) the binary values 'a' and 'a ' are now regarded as unequal any time they are compared, as they are in MyISAM tables. (5.0.19)
Posted by mike at 8:51 PM
Guide to Incompatibilities when Upgrading MySQL to Version 4.1
For folks who are looking at upgrading MySQL to a newer version there is good documentation in the MySQL docs (for example, upgrading from 4.0. to 4.1). My (limited) experience is that folks tend to focus on moving the data between versions, and don't always think about the other ramifications involved in upgrading to a new major version of MySQL.
You'll note that in the upgrade instructions the first step is focused on the behavior of MySQL:
Check the items in the change lists found later in this section to see whether any of them might affect your applications. Note particularly any that are marked Incompatible change. These result in incompatibilities with earlier versions of MySQL and you should consider the implications of these incompatibilities before you upgrade. Note particularly the items under "Server Changes" that related to changes in character set support.
The placement of this item first on the list is significant. The effort to accomodate behavioral changes in MySQL may be more significant than ensuring the data is properly copied between versions. I bumped into one of these recently when going through an upgrade from 4.0 to 4.1. The data appeared to have transferred correctly but in testing the application I started getting periodic internal server errors. Several hours of debugging and I was at the SQL layer looking at a problem caused by this:
Warning: Incompatible change: TIMESTAMP is now returned as a string of type 'YYYY-MM-DD HH:MM:SS' and different timestamp lengths are not supported.
The trick is, unless you are the only coder and have a small code base that you understand very well t's not clear how these changes will affect your application. You might think that a timestamp formatting change won't make a difference, but it does.
So in an effort to at least be aware of what to be on the lookout for, here's the complete list of incompatibility warnings from the MySQL 4.1 docs. I want to note that I'm not sure on the criteria for a compatibility change warning, and just because these items are flagged as compatibility changes doesn't mean you won't be affected by some of the other changes or bug fixes. So these are the biggies, but it's wise to take a few minutes and read all of the release notes to see if anything else raises a red flag for you.
Se here are the items pulled from the MySQL 4.1 release docs that are marked Warning: Incompatible change, followed by the full version number in parens:
- TIMESTAMP is now returned as a string of type 'YYYY-MM-DD HH:MM:SS' and different timestamp lengths are not supported. (4.1.0)
- Renamed the C API mysql_prepare_result() function to mysql_get_metadata() because the old name was confusing. (4.1.1)
- Client authentication now is based on 41-byte passwords in the user table, not 45-byte passwords as in 4.1.0. Any 45-byte passwords created for 4.1.0 must be reset after running the mysql_fix_privilege_tables script. (4.1.1)
- Handling of the FLOAT and DOUBLE floating-point data types is more strict to follow standard SQL. For example, a data type of FLOAT(3,1) stores a maximum value of 99.9. Previously, the server allowed larger numbers to be stored. That is, it stored a value such as 100.0 as 100.0. Now the server clips 100.0 to the maximum allowable value of 99.9. If you have tables that were created before MySQL 4.1.2 and that contain floating-point data not strictly legal for the column type, you should alter the data types of those columns. (4.1.2)
- String comparison now works according to the SQL standard. Because we have that 'a' = 'a ' then from it must follow that 'a' > 'a\t'. (The latter was not the case before MySQL 4.1.2.) To implement it, we had to change how storage engines compare strings internally. As a side effect, if you have a table where a CHAR or VARCHAR column in some row has a value with the last character less than ASCII(32), you have to repair this table. CHECK TABLES tells you if this problem exists. (4.1.2)
- Renamed 10 prepared statements C API functions. (4.1.2)
- The signature of the mysql_stmt_prepare() function was changed to int mysql_stmt_prepare(MYSQL_STMT *stmt, const char *query, unsigned long length). To create a MYSQL_STMT handle, you should use the mysql_stmt_init() function, not mysql_stmt_prepare(). (4.1.2)
- The Type output column for SHOW TABLE STATUS now is labeled Engine. (4.1.2)
- C API change: mysql_shutdown() now requires a second argument. This is a source-level incompatibility that affects how you compile client programs; it does not affect the ability of compiled clients to communicate with older servers. (4.1.3)
- The timezone system variable has been removed and replaced by system_time_zone. (4.1.3)
- The behavior of LOAD DATA INFILE and SELECT ... INTO OUTFILE has changed when the FIELDS TERMINATED BY and FIELDS ENCLOSED BY values both are empty. Formerly, a column was read or written the display width of the column. For example, INT(4) was read or written using a field with a width of 4. Now columns are read and written using a field width wide enough to hold all values in the field. However, data files written before this change was made might not be reloaded correctly with LOAD DATA INFILE for MySQL 4.1.12 and up. This change also affects data files read by mysqlimport and written by mysqldump --tab, which use LOAD DATA INFILE and SELECT ... INTO OUTFILE. (4.1.12)
- Previously, conversion of DATETIME values to numeric form by adding zero produced a result in YYYYMMDDHHMMSS format. The result of DATETIME+0 is now in YYYYMMDDHHMMSS.000000 format. (4.1.13)
Hope that helps someone. It provides a nice snapshot of what to be worried about when going back to upgrading that application to work with MySQL 4.1.
Posted by mike at 7:28 AM
September 28, 2006
Bare Bones OS for MySQL?
While laying in bed last night thinking about getting started on a project that requires getting a fresh MySQL clustered database up and running I got to thinking. What is the best OS for MySQL from a minimalistic point of view? If you're starting from scratch to set up a server, or farm of servers, to run MySQL and can choose any OS, what do you choose?
I'm going to venture that it's a Unix variant, perhaps a flavor of Linux? There's a lot to think about in terms of what types of data and how the application will use the database, but from a general point of view where would one start? Let's suppose that the hardware is your typical generic AMD/Intel with high-end SCSI disks.
I'm thinking about things like:
- minimal OS footprint (only necessary applications/libraries)
- minimal OS processes
- a kernel optimized for MySQL-specific functions, minized for everything else
My experience running MySQL is on Solaris, Red Hat/Fedora Core Linux, Gentoo Linux, and Mac OS X. I'd guess that RedHat/Fedora will automatically get thrown out because they are built for the masses and while you can install minimally, the kernel is built to support everything under the sun. If I'm running on generic AMD/Intel hardward OS X isn't an option and I'm inclined to rule out Solaris.
That leaves me with Gentoo, or considering something I haven't used before. Perhaps FreeBSD? The popular Linux flavors these days seem to be Ubuntu and CentOS. Have little experience with either, but it doesn't seem like the buzz on the street about either is centered on performance or minimalism.
Now that I've actually thrown this all down into sentences I wonder if it's even worth the trouble of finding and paring down an OS to find the most optimal footprint? The tradeoff is that I'll be in new territory, introduce any number of uncertainties, and in the end wish I would have stuck with something I know or that is well-used.
But it's always good to be thinking about this. Someday (unless I missed it) there will be a MySQL-optimized Linux/Unix installation that has everything you need, but nothing more. I wonder what Pogo Linux was doing for the MySQL appliance they were shipping a few years back (don't see any information about the appliance on their site now). Looks like the Pogo systems ship with either Red Hat, Fedora or SuSE. Perhaps they didn't do anything special, but the press releases seem to indicate the system was tuned for maximum performance.
I'll have to spend some time diging around Peter's blog to see what information is there. I'm pretty sure there was a presentation in one of the recent conferences comparing Linux distributions for MySQL performance.
Posted by mike at 8:43 AM
September 22, 2006
Damian Conway in Boston this Coming Monday
Damian Conway (wikipedia), the Perl guru and geek entertainer, is at MIT on Monday, September 25th to speak to the local Perl user's group. More information can be found at the Boston.pm site. Will be attending with a few of my Perl buddies.
I've seen/heard Damian speak many times and it's always a treat. There's usually some Perl-related pieces, but the presentations seem to be more language agnostic.. This visit it sounds like he's giving two talks, opentalk 2.0 and The DaVinci Codebase. I haven't seen either of them.
Hope to see you there.
Posted by mike at 9:53 PM
September 14, 2006
Historic Wiring Sculpture
I took this photo last fall after my father and I ripped a good chunk of the electrical wiring out of our house and re-ran new electrical circuits for the basement, 1st, and 3rd floors. The photo has been on my desktop ready for posting since last fall so I figured I better post it and get on with things before a full year passes.
The wiring sculpture is really just a stack of some of the junk we pulled from the floors, crawlspaces, walls, ceilings and attic. It also serves as a historical record of the technology used to provide electricity through the house. We pulled mostly knob & tube and BX but there were places where other "alternative" methods had been used keep things connected.
The brown box in the back is filled with ceramic knobs from the knob and tube. There were even a few things that dad thought were interesting enough to take to put in the Electrical Technology Department Museum at NDSCS.
Posted by mike at 10:16 PM
September 13, 2006
Get Rid of Verizon Advertizing in Email from Blackberry
Ever since I got my Blackberry (a few months ago) I've refused to use it for sending email because every time you send a message it adds this line to the bottom of the email:
Sent from my Verizon Wireless BlackBerry
Yes, very annoying and unprofessional, not to mention I'm not a big fan of Verizon and would rather not promote their business. I just assumed it was "part of the deal" and was a message tagged on somewhere down in the mail server layer.
Today I stumbled into the Blackberry Internet Service homepage, which has a login to access your Blackberry account. I used my Verizon/BB email account authentication and got this nice web-based interface to the mail as well as configuration. It was just by curiosity that I clicked on the "Options" link where I discovered that the message above is configurable. My new configuration . . . blank. It's not important for folks to know what device I'm using to respond to their message. It is important to not have Verizon advertizing on all of my messages.
Man it feels good to take that away from Verizon.
Posted by mike at 4:51 PM
Supporting UTF-8 in Application-generated Email
If you are sending email from an application and need to tell the email client to use UTF-8 encoding to properly display the message add this header to your email message:
Content-type: text/plain; charset=UTF-8
I won't tell you how long it took me to figure that out. I knew it was just a matter of adding a header to the message before sendmail pushed it out to the next hop, but figuring out the exact syntax took forever. Googling turned up a lot of unhelpful messages, or information on how to do this programmattically in various language packages. This was an instance where I actually did want to see an email header in the search results, most of the time real snips of header (http or smtp) are irrelevant and annoying.
Posted by mike at 4:24 PM
September 1, 2006
Rebuilding a Hardwood Floor: Goodbye Creaky Entryway
For the past year we, and anyone who's been to our place, have had to endure some extreme creakiness when walking through the front door and across front foyer of the house. The hardwood floors are almost 100 years old and (as we learned) had never been repaired except for the occasional resurfacing.
Last week one of my younger brothers, Dave, who happens to be a carpenter that specializes in finish woodworking, was in town with his family for a vacation. At a minimum I wanted him to look at it and give me an idea of what kind of work it would take. Besides being horribly creaky everywhere there was a decent size bump right inside the door where three pieces of the flooring were buckling up an inch or so.
After looking at it for a bit he (and I) were convinced that it wouldn't take more than an hour to rip up the floor, a few more hours for cleaning up the floor from all the dirt, and then an hour or two to put it back together.
Turns out the floor of the entryway was originally built in a way that we could start right near the door and pull up lengths of hardwood moving into the foyer about 5 feet without having to disturb the more serious part of the hardwood floor that extends into the dining and living room.
After I drained the water in the heating system (forced hot water) and pulled off the radiator Dave pulled up hardwood one piece at a time. We numbered each board as it came up to ensure we could put them back in the same order and have them fit correctly. There was so much gunk that had built up over 100 years that each piece of wood (24 total) took 10-20 minutes to clean up. Floor varnish mixed with dirt and sand makes for some tough scraping.
As we pulled the nails from the hardwood it was obvious from the age of the nails that this was the first time these boards had been pulled up since they were originally installed in 1910.
Once the boards were all up and cleaned Dave put down some swaths of liquid nail and we slipped the hardwood back together one piece at a time. By the time we finished it was after 10pm. Everyone in the house (2 wives, 5 children, 2 cats) was very nice about letting us keep up the loud hammering to get the job done.
The end result is a solid, flat, non-creaky entryway to our house. We've even had some of the regulars comment on how noticeable the difference is.
Posted by mike at 8:11 AM