MySQL Bugs: #1626: can't load a table of type HEAP from master;

Bug #1626	can't load a table of type HEAP from master;
Submitted:	22 Oct 2003 8:43	Modified:	22 Oct 2003 9:07
Reporter:	jean-francois beleyn	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: Replication	Severity:	S3 (Non-critical)
Version:	4.0.12	OS:	Linux (Linux on sparc64)
Assigned to:		CPU Architecture:	Any

Description:

load table $heaptable from master gives a "ERROR 1189: Net error reading from master", when $heaptable is a table of type HEAP.

the transfer itself is successful, but because of this error a "LOAD DATA FROM MASTER;"  ll give the same error after transefering this table and won't transfer the other tables/databases

How to repeat:
On the master:

mysql> use gianfranco
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql> DROP TABLE IF EXISTS forum_sessions;
Query OK, 0 rows affected (0.00 sec)

mysql> CREATE TABLE forum_sessions (
    ->   session_id char(32) NOT NULL default '',
    ->   session_user_id mediumint(8) NOT NULL default '0',
    ->   session_start int(11) NOT NULL default '0',
    ->   session_time int(11) NOT NULL default '0',
    ->   session_ip char(8) NOT NULL default '0',
    ->   session_page int(11) NOT NULL default '0',
    ->   session_logged_in tinyint(1) NOT NULL default '0',
    ->   PRIMARY KEY  (session_id),
    ->   KEY session_user_id (session_user_id),
    ->   KEY session_id_ip_user_id (session_id,session_ip,session_user_id)
    -> ) TYPE=HEAP MAX_ROWS=500;
Query OK, 0 rows affected (0.02 sec)

On the slave:
mysql> use gianfranco;
Database changed
mysql> load table forum_sessions from master;
ERROR 1189: Net error reading from master

With the same table, but not a the HEAP type:

On the master:

mysql> DROP TABLE IF EXISTS forum_sessions;
Query OK, 0 rows affected (0.08 sec)

mysql> CREATE TABLE forum_sessions (
    -> session_id char(32) NOT NULL default '',
    -> session_user_id mediumint(8) NOT NULL default '0',
    -> session_start int(11) NOT NULL default '0',
    -> session_time int(11) NOT NULL default '0',
    -> session_ip char(8) NOT NULL default '0',
    -> session_page int(11) NOT NULL default '0',
    -> session_logged_in tinyint(1) NOT NULL default '0',
    -> PRIMARY KEY  (session_id),
    -> KEY session_user_id (session_user_id),
    -> KEY session_id_ip_user_id (session_id,session_ip,session_user_id)
    -> );
Query OK, 0 rows affected (0.01 sec)

On the slave:

mysql> drop table forum_sessions ;
ERROR 2006: MySQL server has gone away
No connection. Trying to reconnect...
Connection id:    26
Current database: gianfranco

Query OK, 0 rows affected (0.00 sec)

mysql> load table forum_sessions from master;
Query OK, 0 rows affected (0.01 sec)

Thank you for your bug report. This issue has been addressed in the
documentation. The updated documentation will appear on our website
shortly, and will be included in the next release of the relevant
product(s).

Bonjour Jean-François,

Yes, it's a deficiency (a known bug which can't be fixed shortly) of LOAD DATA FROM MASTER and LOAD TABLE FROM MASTER: they only work with MyISAM tables. It is documented in our manual in the section of LOAD DATA FROM MASTER (http://www.mysql.com/doc/en/LOAD_DATA_FROM_MASTER.html) but unfortunately not in the section of LOAD TABLE FROM MASTER (so I just fixed this).
LOAD TABLE/DATA FROM MASTER is usually used for replication; and as HEAP tables are in-memory tables (content lost at shutdown; only the structure survives shutdowns), they are a problem when used with replication (when the slave shuts down while it's replicating master's queries using HEAP tables, it should find a way to save the HEAP table's content on disk to be able to re-use it when it restarts). Solving this is on our TODO.

Cordialement,
Guilhem