Tag Archives: CREATE TABLE

TYPE= disappears again (MySQL 5.4.4)

I like the 5.4 developments, overall. It has useful stuff and is being developed and released a reasonable pace. Good progress. While perusing the MySQL 5.4.4 changelog, one particular change drew my attention, since it’s been (re)appearing since 2006. It’s the removal of the TYPE= keyword which was obsoleted since MySQL 4.1 in favour of the ENGINE= syntax in CREATE/ALTER TABLE.

While on the surface it may seem ok to remove the obsolete keyword, there are quite a few apps out there that use it, and that cannot be changed. So these will now be unable to use MySQL 5.4 or beyond. I filed this as a bug in 2006, MySQL bug#17501. If you’re interested in the “history of reappearance”, take a peek at the comments and their timeline. I just put in a new comment to note the 5.4.4 change.

I suppose that a new developer comes along and reckon that removing this keyword is a good idea. But really, why do we need to remove one keyword from the parser? Because that’s all it is. And removing it really does break apps.
Let’s not. Again. Please! And this time please put a comment in the parser source files, referring to the bug#, so that it doesn’t get recycled at a later date. Please just leave it in.

Good Practice / Bad Practice: CREATE TABLE and the Storage Engine

When you write your create table statements, always make sure that you make them non-ambiguous. That way even though other servers might have different configurations, you make sure your table will be created in the same way.
Imagine for instance you are developing an application on a development server, nicely storing all the scripts you need to create the same database on your production server. If the same script creates a table differently on both servers, that might cause you a lot of headache later on. At Open Query, we strive to minimise (or preferrably eliminate) headaches.

One of the parts of the create table statement that has the largest impact is the storage engine specification. When you omit the storage engine from the create table statement, your table is automatically created with the default storage engine type configured for the server. Since the storage engine is a very important choice when designing your tables, you want to make sure that it is always the correct type.

Here’s an example: instead of writing CREATE TABLE city (city_id int, city_name varchar(100)) you should write: CREATE TABLE city (city_id int, city_name varchar(100)) ENGINE=InnoDB

It is a simple adjustment, but it will save you from possible problems if you just make a habit out of specifying the storage engine.

But wait, there one more thing! It’s also very important you have sql_mode=NO_ENGINE_SUBSTITUTION in your my.cnf, otherwise your table may still silently become a MyISAM table if your desired engine (for any reason) is disabled in the binary, configuration, or at runtime. With this setting error, such a situation will cause an error – so you know for sure.

In our good practice / bad practice series, we will provide you with byte/bite sized pieces of advice on what we consider good (or bad) practice in MySQL-land. The topics can be just about anything, so expect random things to come up. Also, the level of advancedness greatly varies. A topic might be a no-brainer for some, a reminder for others and a revelation for a third person. We strive to tender to all of you!