Wednesday, September 17, 2008

MAX_ROWS

How well do you know MAX_ROWS?


mysql> CREATE TABLE cube2 ( cube INT(1)) MAX_ROWS=2;
Query OK, 0 rows affected (0.12 sec)


So I inserted a row INSERT INTO cube2 values(3); and then another with INSERT INTO cub2(4);. So what happened when I tried to insert a third row?

Did the server complain I was trying to fit a third row in a two row table? Did one of the existing values in the table get replaced?

Nope. The server gladly took the third row.

MAX_ROWS is usually used with MyISAM tables to change the row pointer size from the default to access more disk space. Using a MAX_ROW value absurdly lower than this pointer size is politely ignored by the server.

Drat! I I thought I had a cute trick to use on a certification exam.

4 comments:

jedy said...

It will not be ignored by the server and will make server decrease the size of pointer so reduce the table's capacity.

mysql>select count(*) from cube2;
+----------+
| count(*) |
+----------+
| 65535 |
+----------+
1 row in set (0.00 sec)

mysql>insert into cube2 values (1);
ERROR 1114 (HY000): The table 'cube2' is full

Rob said...

MAX_ROWS is in the current study guide, though I don't recall any exam questions. I suggest adding one asking what will happen when a third row is added (meaning is the number hard).

Jeremy Cole said...

Heh, it's good to see that you're continuing the trend of asking "trick" questions in certification, which has to be the dumbest thing I've ever heard. Many MySQL folks complained loudly about the trick questions when they first appeared, but no one has ever taken it seriously. I guess it's too hard to test practical knowledge.

Might as well just make sure we memorize every stupid side effect of every silly feature so we can answer all the "trick" questions. That definitely makes the certification worthwhile. Heh.

Dave Stokes said...

Jeremy, I probably should not have used the words 'trick question'. This example could be part of an interesting lab based on using MAX_ROW_SIZE with MyIsam tables.

And hopefully you will be more impressed with the next round of certification exams that feature performance based testing heavily to test the application of your knowledge.