Bug #33946 Join on Federated tables with Unique index gives error 1430 from storage engine
Submitted: 20 Jan 2008 6:28 Modified: 16 Apr 2008 0:34
Reporter: Chris Calender Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: Federated storage engine Severity:S3 (Non-critical)
Version:5.0.54 OS:Any
Assigned to: Sergey Vojtovich CPU Architecture:Any
Tags: 1430, federated, IS NOT NULL, join, unique index
Triage: D3 (Medium) / R3 (Medium) / E3 (Medium)

[20 Jan 2008 6:28] Chris Calender
Description:
A certain join on 2 federated tables, one of which contains a unique index, leads to the following error:

ERROR 1030 (HY000): Got error 1430 from storage engine

Note that this does not seem related to a "reserved" word (as in bug #28269).

Here is the query:

SELECT * 
FROM CLIENT CL INNER JOIN STOCK_MSISDN SM ON SM.ID_CLIENT = CL.ID_CLIENT 
WHERE CL.NUM_HIGHDEAL IS NOT NULL;

ERROR 1030 (HY000): Got error 1430 from storage engine

Nothing is in the error log.

How to repeat:
On System A:

CREATE TABLE `CLIENT` (
  `ID_CLIENT` bigint(20),
  `NUM_HIGHDEAL` varchar(8),
  `ID_CONTRAT` bigint(20),
  `ID_BZH` varchar(50),
  `ETAT` tinyint(4),
  `CIVILITE` tinyint(4),
  `CIVILITEFAC` tinyint(4),
  `NOM` varchar(255),
  `PRENOM` varchar(255),
  `BIRTHDAY` date,
  `CYCLE` tinyint(4),
  PRIMARY KEY  (`ID_CLIENT`),
  UNIQUE KEY `NUM_HIGHDEAL_2` (`NUM_HIGHDEAL`),
  KEY `ID_BZH` (`ID_BZH`),
  KEY `CIVILITE` (`CIVILITE`),
  KEY `CIVILITEFAC` (`CIVILITEFAC`),
  KEY `ID_CONTRAT` (`ID_CONTRAT`),
  KEY `ETAT` (`ETAT`),
  KEY `MAX_ABO` (`NOM`,`PRENOM`,`BIRTHDAY`),
  KEY `CYCLE` (`CYCLE`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

CREATE TABLE `STOCK_MSISDN` (
  `MSISDN` int(10),
  `NUM_ABO` int(8),
  `ID_ENT` int(11),
  `ID_CLIENT` bigint(20),
  `ADD_OPT_DISE` tinyint(1),
  `RIO` char(12),
  PRIMARY KEY  (`MSISDN`),
  KEY `INDEX_CLIENT` (`ID_CLIENT`),
  KEY `INDEX_ENTITE` (`ID_ENT`),
  KEY `NUM_ABO` (`NUM_ABO`),
  KEY `IDX_ADD_OPT_DISE` (`ADD_OPT_DISE`),
  KEY `RIO` (`RIO`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

insert into client (ID_CLIENT, NUM_HIGHDEAL) values (1, 'aaa'), (2, 'bbb');
INSERT INTO STOCK_MSISDN (MSISDN, ID_CLIENT, RIO) VALUES (1, 1, 1), (2, 2, 2);

On System B:

CREATE TABLE `CLIENT` (
  `ID_CLIENT` bigint(20),
  `NUM_HIGHDEAL` varchar(8),
  `ID_CONTRAT` bigint(20),
  `ID_BZH` varchar(50),
  `ETAT` tinyint(4),
  `CIVILITE` tinyint(4),
  `CIVILITEFAC` tinyint(4),
  `NOM` varchar(255),
  `PRENOM` varchar(255),
  `BIRTHDAY` date,
  `CYCLE` tinyint(4),
  PRIMARY KEY  (`ID_CLIENT`),
  UNIQUE KEY `NUM_HIGHDEAL_2` (`NUM_HIGHDEAL`),
  KEY `ID_BZH` (`ID_BZH`),
  KEY `CIVILITE` (`CIVILITE`),
  KEY `CIVILITEFAC` (`CIVILITEFAC`),
  KEY `ID_CONTRAT` (`ID_CONTRAT`),
  KEY `ETAT` (`ETAT`),
  KEY `MAX_ABO` (`NOM`,`PRENOM`,`BIRTHDAY`),
  KEY `CYCLE` (`CYCLE`)
) ENGINE=FEDERATED
DEFAULT CHARSET=latin1
CONNECTION='mysql://root:mysql@localhost:3306/fed/CLIENT';

CREATE TABLE `STOCK_MSISDN` (
  `MSISDN` int(10),
  `NUM_ABO` int(8),
  `ID_ENT` int(11),
  `ID_CLIENT` bigint(20),
  `ADD_OPT_DISE` tinyint(1),
  `RIO` char(12),
  PRIMARY KEY  (`MSISDN`),
  KEY `INDEX_CLIENT` (`ID_CLIENT`),
  KEY `INDEX_ENTITE` (`ID_ENT`),
  KEY `NUM_ABO` (`NUM_ABO`),
  KEY `IDX_ADD_OPT_DISE` (`ADD_OPT_DISE`),
  KEY `RIO` (`RIO`)
) ENGINE=FEDERATED
DEFAULT CHARSET=latin1
CONNECTION='mysql://root:mysql@localhost:3306/fed/STOCK_MSISDN';

SELECT * 
FROM CLIENT CL INNER JOIN STOCK_MSISDN SM ON SM.ID_CLIENT = CL.ID_CLIENT 
WHERE CL.NUM_HIGHDEAL IS NOT NULL;

ERROR 1030 (HY000): Got error 1430 from storage engine

Suggested fix:
I beleieve it has something to do with the fact the 'NUM_HIGHDEAL' column, and perhaps it's index.  Also, the WHERE ... IS NOT NULL is required for the query to reproduce the error.
[7 Feb 2008 11:20] Sergey Vojtovich
BUG#33947 was marked as duplicate.
[7 Feb 2008 11:58] Bugs System
A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/41863

ChangeSet@1.2570, 2008-02-07 15:57:06+04:00, svoj@mysql.com +3 -0
  BUG#33946 - Join on Federated tables with Unique index gives error 1430
              from storage engine
  
  Federated may crash a server, return wrong result set, return
  "ERROR 1030 (HY000): Got error 1430 from storage engine" message
  when local (engine=federated) table has a key against nullable
  column.
  
  The problem was wrong implementation of function that creates
  WHERE clause for remote query from key.
[13 Feb 2008 14:42] Sergey Vojtovich
BUG#33953 was marked as duplicate.
[14 Feb 2008 11:49] Bugs System
A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/42271

ChangeSet@1.2570, 2008-02-14 15:47:29+04:00, svoj@mysql.com +3 -0
  BUG#33946 - Join on Federated tables with Unique index gives error 1430
              from storage engine
  
  Federated may crash a server, return wrong result set, return
  "ERROR 1030 (HY000): Got error 1430 from storage engine" message
  when local (engine=federated) table has a key against nullable
  column.
  
  The problem was wrong implementation of function that creates
  WHERE clause for remote query from key.
[14 Feb 2008 12:42] Bugs System
A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/42277

ChangeSet@1.2571, 2008-02-14 16:27:01+04:00, svoj@mysql.com +3 -0
  BUG#33946 - Join on Federated tables with Unique index gives error 1430
              from storage engine
  
  Federated may crash a server, return wrong result set, return
  "ERROR 1030 (HY000): Got error 1430 from storage engine" message
  when local (engine=federated) table has a key against nullable
  column.
  
  The problem was wrong implementation of function that creates
  WHERE clause for remote query from key.
[19 Mar 2008 17:52] Sergey Vojtovich
BUG#34775 was marked as duplicate.
[27 Mar 2008 11:18] Bugs System
Pushed into 5.1.24-rc
[27 Mar 2008 11:21] Bugs System
Pushed into 5.0.60
[27 Mar 2008 17:49] Bugs System
Pushed into 6.0.5-alpha
[2 Apr 2008 17:57] Jon Stephens
Pushed into 5.1.23-ndb-6.3.11.
[16 Apr 2008 0:34] Paul Dubois
Noted in 5.0.60, 5.1.24, 6.0.5 changelogs.

For a FEDERATED table with an index on a nullable column, accessing
the table could crash a server, return an incorrect result set, or
return ERROR 1030 (HY000): Got error 1430 from storage engine.