http://venublog.com/2008/02/08/mysql-myisam-and-merge-tables-incompatibility-ii/
Few months back we noticed the incompatibility change related to MyISAM that was introduced in 4.1.23 and recent 5.0 and 5.1 MySQL versions which breaks when one wants to upgrade to 4.1.23 or latest 5.0 or 5.1 when the MyISAM or Merga table is created with 4.0 or earlier 4.1 version.
I reported a bug to MySQL team way back; and looks like they now pushed a patch to 5.1.
This happens even with Merge tables and looks like merge tables are overlooked in that bug report and I am not 100% sure if the patch fixes the merge table issue. This bug is triggered only when the MyISAM table is created with a key column and Merge is associated with that table.
For example, consider the following MyISAM and Merge tables in 4.1:
1
2
3
4
5
6
7
8
9
10
11
12
|
mysql
>
CREATE
TABLE
IF
NOT
EXISTS
base_t1
(
->
id
int
NOT
NULL
PRIMARY
KEY
->
)
TYPE
=
MyISAM
;
Query
OK
,
0
rows
affected
(
0.00
sec
)
mysql
>
mysql
>
CREATE
TABLE
IF
NOT
EXISTS
merge_t1
(
->
id
int
NOT
NULL
PRIMARY
KEY
->
)
TYPE
=
MRG_MyISAM
INSERT_METHOD
=
LAST
UNION
=
(
base_t1
)
;
Query
OK
,
0
rows
affected
(
0.01
sec
)
|
and when it comes to 5.0 or 5.1; the merge table completely fails to open the table files.
1
2
3
4
5
6
7
8
9
10
11
12
|
mysql
>
desc
base_t1
;
+
--
--
--
-
+
--
--
--
--
-
+
--
--
--
+
--
--
-
+
--
--
--
--
-
+
--
--
--
-
+
|
Field
|
Type
|
Null
|
Key
|
Default
|
Extra
|
+
--
--
--
-
+
--
--
--
--
-
+
--
--
--
+
--
--
-
+
--
--
--
--
-
+
--
--
--
-
+
|
id
|
int
(
11
)
|
|
PRI
|
0
|
|
+
--
--
--
-
+
--
--
--
--
-
+
--
--
--
+
--
--
-
+
--
--
--
--
-
+
--
--
--
-
+
1
row
in
set
(
0.00
sec
)
mysql
>
desc
merge_t1
;
ERROR
1168
(
HY000
)
:
Unable
to
open
underlying
table
which
is
differently
defined
or
of
non
-
MyISAM
type
or
doesn'
t
exist
|
So, the only option is to drop and re-create the tables back in the latest MySQL version. In case if the MyISAM table had blob/text fields; then you need to drop and re-create both MyISAM and Merge tables. Even though you can re-create the merge table, the table creation works; but again it fails when you try to do any operation on the table, so one needs to fix MyISAM as well.
1
2
3
4
5
6
7
8
9
10
|
mysql
>
create
table
mt4
(
id
int
)
TYPE
=
MRG_MYISAM
INSERT_METHOD
=
LAST
UNION
=
(
t4
)
;
Query
OK
,
0
rows
affected
,
1
warning
(
0.00
sec
)
mysql
>
select
*
from
mt3
;
Empty
set
(
0.00
sec
)
mysql
>
select
*
from
mt4
;
ERROR
1168
(
HY000
)
:
Unable
to
open
underlying
table
which
is
differently
defined
or
of
non
-
MyISAM
type
|
I mad a patch in the internal Yahoo! builds to overcome from this issue and hopefully MySQL will address both the issues as lot of people might start migrating from the existing old tables (even though it is always a good idea to re-create the tables and populate the data when there is major version change)