Bug #27180 #1030 - Got error 1 from storage engine with big tables
Submitted: 15 Mar 2007 15:49 Modified: 9 Jan 2008 20:37
Reporter: Stefan Novak Email Updates:
Status: Verified Impact on me:
None 
Category:MySQL Server: Federated storage engine Severity:S1 (Critical)
Version:mysql Ver 14.12 Distrib 5.0.24a OS:Linux (Linux Ubuntu)
Assigned to: Assigned Account CPU Architecture:Any

[15 Mar 2007 15:49] Stefan Novak
Description:
I use the federated storage engine to link tables form a remote server to a lokal server. 
everything works fine until i want to link a big table. The table is 2Gb big and when i make a 
SELECT *
FROM `messwerte_aironet_log`
LIMIT 0 , 30

MySQL meldet: Dokumentation
#1030 - Got error 1 from storage engine 

Is there a limitation in Table Sizes with federated tables?

How to repeat:
Just include a big table with the federated engine to a local database.
[15 Mar 2007 17:10] MySQL Verification Team
Thank you for the bug report. Could you please test with latest released
version 5.0.37 and comment the results. Thanks in advance.
[21 Mar 2007 16:03] Stefan Novak
Ok I've tested it with the latest release.

I never get an error message like this again but working with big tables in federated is not really working for me.

The Problems I have:
----- First Problem -----
The query on the remote(federated machine): 
select * from messwerte_aironet_log where kundenid=127 and date = "2006-04-11";
Results in a query on the real machine:
SELECT `id` , `date` , `time` , `kundenid` , `newin` , `newout` , `oldin` , `oldout` , `deltain` , `deltaout`
FROM `aironet_log`
WHERE (
`date` LIKE '2006-04-12%'
)
AND (
`kundenid` = '127'
)

Dont know why a date selection is now a like. It probably works but with really poor Performance.

----- Secound Problem -----

Selecting anything from the remote machine on field without an index results in a select * from table;
My table is 2Gb big and the Linux-Server hangs up with can't allocate more memory with an memory cunsumption of 4Gb!

----- Third Problem ------

select * from table LIMIT 1,100; also gets a select * from table @ the remote machine.

Sorry for my bad english :)
[22 Mar 2007 13:11] MySQL Verification Team
Thank you for the feedback. Could you please provide the script to
create the table mentioned in your last comment. I will fill them
and to test what are you reporting regarding the performance. Thanks
in advance.
[22 Mar 2007 13:38] Stefan Novak
the create table is:

CREATE TABLE `aironet_log` (
  `id` int(11) unsigned NOT NULL auto_increment,
  `date` date NOT NULL default '0000-00-00',
  `time` time NOT NULL default '00:00:00',
  `kundenid` int(11) NOT NULL default '0',
  `newin` int(11) unsigned NOT NULL default '0',
  `newout` int(11) unsigned NOT NULL default '0',
  `oldin` int(11) unsigned NOT NULL default '0',
  `oldout` int(11) unsigned NOT NULL default '0',
  `deltain` int(11) unsigned NOT NULL default '0',
  `deltaout` int(11) unsigned NOT NULL default '0',
  PRIMARY KEY  (`id`),
  KEY `date_kundenid` (`date`,`kundenid`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=49831967 ;

the table has 20.000.000 entries and is 1,2 GB big.
[2 Apr 2007 15:43] MySQL Verification Team
Thank you for the feedback.

Remote machine:

mysql> select count(*) from aironet_log;
+----------+
| count(*) |
+----------+
| 20000000 | 
+----------+
1 row in set (0.06 sec)

Federated machine:

mysql> select count(*) from aironet_log limit 0, 30;
ERROR 5 (HY000): Out of memory (Needed 2841072 bytes)
mysql>
[9 Jan 2008 20:37] Stefan Novak
is it planned to fix this issue in near future?
as at it is now, the federated tables are not very usefull..
[25 Apr 2009 0:53] Roel Van de Paar
If you run into this, please try the following on the machine where the table is defined:

mysql> ALTER TABLE your_table_name_goes_here ENGINE=your_storage_engine_for_this_table;

Replace 'your_table_name_goes_here' with your actual table name, and replace 'your_storage_engine_for_this_table' with the actual storage engine (like InnoDB/MyISAM). You can use 'SHOW CREATE TABLE tablename;' (on the machine where the table is defined) to see what storage engine the table is using.

This will rebuild the table completely, and has shown to resolve the issue in at least one case. 

Please notice that while ALTER TABLE is executing, the original table is readable by other sessions. Updates and writes to the table are stalled until the new table is ready, and then are automatically redirected to the new table without any failed updates.