Known Limitation of MySQL Cluster NDB 7.3 and MySQL Cluster NDB 7.4

Masteramuk

Rate me:

0.00/5 (No votes)

11 Nov 2015CPOL2 min read

Basic rules for using MySQL Cluster (NDB) from MyISAM or InnoDB

Introduction

This tip is about MySQL Cluster NDB version 7.3 and 7.4 issues when converting from MyISAM or InnoDB to NDB for clustering implementation. This tip lists the basic rules that the developers and database administrators shall use when creating MyISAM or InnoDB database, table, SQL statement, etc.

The Basic Rules

The rules shall be used as guidance in software development to avoid issue when exporting MyISAM and InnoDB into NDB format for clustering purposes. Developer/DBA shall apply as close as possible all the rules. If any of the rules are avoided, MySQL will throw errors or failed to convert DB into supported cluster DB in MySQL.

The basic rules are:

Do not use temporary table.
Do not used key (primary or foreign) that exceed 3072 bytes width (length); equivalent to 3072 characters or 3KB data
Do not use TEXT or BLOB as key or unique identifier or index
Avoid indexing FULLTEXT (such as name, address, etc.)
Avoid using HASH column/data as key or index
Ensure the Auto_Increment column is also a key.
Ensure the foreign key is referred to a table primary or unique key.
Do not implement spatial indexes.
Used only Latin-1 character for logging
Avoid using READ UNCOMMITTED, REPEATABLE READ, and SERIALIZABLE. NDB only support READ COMMITTED.
Avoid having BLOB and TEXT as part of table column. If need to have either those two types:
1. Avoid having multiple query on same table at the same time.
2. Avoid table scan and query the entire table including the TEXT/BLOB field.
3. Avoid query the entire table using primary or unique key
4. Avoid query and extracting the TEXT/BLOB field
Database and table name shall not more than 63 characters.
Number of database, table and index in a single cluster shall not more than 20320.
Number of column (attributes), key and index in a single table shall not be more than 512.
The maximum number of row size shall not exceed 14000 bytes (14Kb).
Avoid using PREFIXs

There is also space limitation. Please read http://dev.mysql.com/doc/refman/5.6/en/mysql-cluster.html for a complete list of space of limitation.

References

History

November 12^th, 2015 - First version of the tip/trick

License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Written By

Masteramuk

Architect

Malaysia

I’m a UiTM graduated with major in multimedia (B.Sc (hons) IT) in 1999. Prior to that, I received my Diploma in Computer Sciences (major in software engineering) from the same university in 1994. Currently, I’m pursuing Master degree in Software Security and waiting for my viva session.

In terms of experience, I’ve more than 14 years of challenging experience. I started as lecturer in IT and software engineering at Stamford College. 6 months later, to improve my skill and knowledge, I joined PKDP as MIS executives responsible on support and maintenance of logistics application running on Informix 4GL. Since then, I've joined few others company such as IB Technologies Sdn Bhd, Mesiniaga Berhad, MIMOS Berhad and Matrix Power Network Sdn Bhd.

I gain lots of experience developing various types of applications using various tools and programming language. Beside application development, I had the opportunity to become project manager and also lead a team for a few months due to departure of my department head to another cluster. In addition, I’ve serve as member of IP mining and panel in IP Disclosure part of the task is to improve an IP or evaluate the feasibility of an IP. On top of that, I’ve been presented in few security and software development conferences and published papers locally and international.