Bug #41099 CREATE/ALTER TABLE fails when using NDBCLUSTER engine and CHARSET=utf8

• Submitted: 28 Nov 2008 14:34
• Modified: 1 Dec 2008 11:10
• Reporter: Laurent Baum
• Status: Not a Bug
• Category: MySQL Cluster: Cluster (NDB) storage engine
• Severity: S2 (Serious)
• Version: 6.2.15
• OS: Linux (CentOS release 5.2 (Final) 2.6.18-92.1.18.el5)
• CPU Architecture: Any

[28 Nov 2008 14:34] Laurent Baum

Description:
mysqld  Ver 5.1.23-ndb-6.2.15-cluster-gpl for redhat-linux-gnu on i686 (MySQL Cluster Server (GPL))

MySQL distrib mysql-5.1.23 ndb-6.2.15, for redhat-linux-gnu (i686)

We cannot create a table with the ndbcluster engine and utf8 charset that has >1 columns of the type text or mediumtext.

How to repeat:
CREATE DATABASE example;
use example;
DROP TABLE IF EXISTS `menu_router`;
CREATE TABLE `menu_router` (
  `path` varchar(255) NOT NULL default '',
  `load_functions` text NOT NULL,
  `to_arg_functions` text NOT NULL,
  `access_callback` varchar(255) NOT NULL default '',
  `access_arguments` text,
  `page_callback` varchar(255) NOT NULL default '',
  `page_arguments` text,
  `fit` int(11) NOT NULL default '0',
  `number_parts` smallint(6) NOT NULL default '0',
  `tab_parent` varchar(255) NOT NULL default '',
  `tab_root` varchar(255) NOT NULL default '',
  `title` varchar(255) NOT NULL default '',
  `title_callback` varchar(255) NOT NULL default '',
  `title_arguments` varchar(255) NOT NULL default '',
  `type` int(11) NOT NULL default '0',
  `block_callback` varchar(255) NOT NULL default '',
  `description` text NOT NULL,
  `position` varchar(255) NOT NULL default '',
  `weight` int(11) NOT NULL default '0',
  `file` mediumtext,
  PRIMARY KEY  (`path`),
  KEY `fit` (`fit`),
  KEY `tab_parent` (`tab_parent`)
) ENGINE=NDBCLUSTER DEFAULT CHARSET=utf8;

produces the following error:

ERROR 1005 (HY000): Can't create table 'example.menu_router' (errno: 140)

Suggested fix:
It creates the table if only one column is of the type text or mediumtext.

e.g.

CREATE DATABASE example;
use example;
DROP TABLE IF EXISTS `menu_router`;
CREATE TABLE `menu_router` (
  `path` varchar(255) NOT NULL default '',
  `load_functions` text NOT NULL,
  `access_callback` varchar(255) NOT NULL default '',
  `page_callback` varchar(255) NOT NULL default '',
  `fit` int(11) NOT NULL default '0',
  `number_parts` smallint(6) NOT NULL default '0',
  `tab_parent` varchar(255) NOT NULL default '',
  `tab_root` varchar(255) NOT NULL default '',
  `title` varchar(255) NOT NULL default '',
  `title_callback` varchar(255) NOT NULL default '',
  `title_arguments` varchar(255) NOT NULL default '',
  `type` int(11) NOT NULL default '0',
  `block_callback` varchar(255) NOT NULL default '',
  `position` varchar(255) NOT NULL default '',
  `weight` int(11) NOT NULL default '0',
  PRIMARY KEY  (`path`),
  KEY `fit` (`fit`),
  KEY `tab_parent` (`tab_parent`)
) ENGINE=NDBCLUSTER DEFAULT CHARSET=utf8;

Query OK, 0 rows affected (3.89 sec)

Other workaround is to change ENGINE to MyISAM or change DEFAULT CHARSET to latin1.

[1 Dec 2008 11:10] Sveta Smirnova

Thank you for taking the time to write to us, but this is not a bug. Please double-check the documentation available at http://dev.mysql.com/doc/ and the instructions on
how to report a bug at http://bugs.mysql.com/how-to-report.php

Pleas read about data storage limitations for cluster at http://dev.mysql.com/doc/refman/5.0/en/mysql-cluster-limitations-database-objects.html Also please note each column with UTF8 character set requires 3 times more bytes than same column using latin1 character set.