ndb cluster table is full

Question

I'm using ndb cluster au to install with simpletest and high write load opstions. The cluster started successfully. Then I edited create-minimal-mysql.sql file by replacing InnoDB with NDBCLUSTER and run it. It show:
ERROR 1114(HY000) at line 1703: The table 'UserGroup' is full.

Here's my config.ini file on the management node config directory:

#
# Configuration file for MyCluster
#

[NDB_MGMD DEFAULT]
Portnumber=1186

[NDB_MGMD]  
NodeId=49  
HostName=192.168.5.4  
DataDir=/home/admin/MySQL_Cluster/49/  
Portnumber=1186  

[TCP DEFAULT]  
SendBufferMemory=8M  
ReceiveBufferMemory=8M    

[NDBD DEFAULT]
BackupMaxWriteSize=1M  
BackupDataBufferSize=16M  
BackupLogBufferSize=4M  
BackupMemory=20M  
BackupReportFrequency=10  
MemReportFrequency=30  
LogLevelStartup=15  
LogLevelShutdown=15  
LogLevelCheckpoint=8  
LogLevelNodeRestart=15  
DataMemory=1146M  
IndexMemory=184M  
MaxNoOfTables=4096  
MaxNoOfTriggers=3500  
MaxNoOfAttributes=25000  
NoOfReplicas=2  
StringMemory=25  
DiskPageBufferMemory=64M  
SharedGlobalMemory=20M  
LongMessageBuffer=32M  
MaxNoOfConcurrentTransactions=16384  
BatchSizePerLocalScan=512  
FragmentLogFileSize=64M  
NoOfFragmentLogFiles=16  
RedoBuffer=64M  
MaxNoOfExecutionThreads=2  
StopOnError=false  
LockPagesInMainMemory=1  
TimeBetweenEpochsTimeout=32000  
TimeBetweenWatchdogCheckInitial=60000  
TransactionInactiveTimeout=60000  
HeartbeatIntervalDbDb=15000  
HeartbeatIntervalDbApi=15000  

[NDBD]  
NodeId=1  
HostName=192.168.5.4  
DataDir=/home/admin/MySQL_Cluster/1/  

[NDBD]  
NodeId=2  
HostName=192.168.5.75  
DataDir=/home/admin/MySQL_Cluster/2/  

[MYSQLD DEFAULT]  

[MYSQLD]  
NodeId=52  
HostName=192.168.5.4  

[MYSQLD]  
NodeId=55  
HostName=192.168.5.75  

[API]
NodeId=50
HostName=192.168.5.4

[API]  
NodeId=51  
HostName=192.168.5.4  

[API]  
NodeId=53  
HostName=192.168.5.75  

[API]  
NodeId=54  
HostName=192.168.5.75

The report in management node:

Node 1: Data usage is 0%(48 32K pages of total 64000)  
Node 1: Index usage is 0%(468 8K pages of total 64032)  
Node 2: Data usage is 0%(48 32K pages of total 64000)  
Node 2: Index usage is 0%(468 8K pages of total 64032)

And the configuration:

Connected to Management Server at: localhost:1186  
Cluster Configuration  
---------------------  
[ndbd(NDB)]     2 node(s)  
id=1    @192.168.5.4  (mysql-5.6.11 ndb-7.3.2, Nodegroup: 0, Master)  
id=2    @192.168.5.75  (mysql-5.6.11 ndb-7.3.2, Nodegroup: 0)  

[ndb_mgmd(MGM)] 1 node(s)  
id=49   @192.168.5.4  (mysql-5.6.11 ndb-7.3.2)  

[mysqld(API)]   6 node(s)  
id=50 (not connected, accepting connect from 192.168.5.4)  
id=51 (not connected, accepting connect from 192.168.5.4)  
id=52   @192.168.5.4  (mysql-5.6.11 ndb-7.3.2)  
id=53 (not connected, accepting connect from 192.168.5.75)  
id=54 (not connected, accepting connect from 192.168.5.75)  
id=55   @192.168.5.75  (mysql-5.6.11 ndb-7.3.2)

What should I do???

Please run `ndb_mgm -e "all report memoryusage"` on your management server and include the results in your question. — Luc van Donkersgoed, Aug 10 '13 at 08:18
Sorry! I've included the report result, but I don't think the datamemory is the problem. — supper aban_89, Aug 11 '13 at 07:27
Could you also run `ndb_mgm -e "show"` and include those results? — Luc van Donkersgoed, Aug 11 '13 at 07:32
And just the sake of completeness, could you copy the exact error you're seeing and past it here? Most importantly: is there an error code shown? — Luc van Donkersgoed, Aug 11 '13 at 08:09
I ran create-minimal-mysql.sql again and included result in the question. Thanks! — supper aban_89, Aug 11 '13 at 08:22

Luc van Donkersgoed · Accepted Answer · 2013-08-11T15:30:48.080

There are a number of possible reasons for the 1114 error.

The first thing to look for is if the nodes have write permissions in their data folders. Is the user that runs ndbd on data node 1 the owner of the /home/admin/MySQL_Cluster/1/ folder? Also check this on node 2.

Next, check the hardware requirements of your nodes, as per http://dev.mysql.com/doc/refman/5.5/en/faqs-mysql-cluster.html#qandaitem-B-10-1-13. Most importantly, make sure all nodes have an equal amount of RAM. Also, check if the amount of RAM is sufficient to store all data and indexes (you need about 2,5GB per NDB node for your config, but 3GB would be better).

Does your script fail immediately, or after inserting a number of rows? If it's able to write a few rows, than fails, the problem does not have to do with write permissions on the nodes.

Try to insert some data into the database manually (using mysql-client), and when it fails type show warnings, this should provide you with more info on the error you're getting.

While you're using the mysql client, run this script:

SELECT TABLE_SCHEMA, TABLE_NAME, TABLE_ROWS
FROM INFORMATION_SCHEMA.TABLES
WHERE ENGINE='ndbcluster';

It will show you if you have any large tables using ndb_engine.

Edit: Added information based on supper aban_89's comments.

It sounds like you are still working with the default configuration parameters. See the MySQL documentation: the default value for MaxNoOfTables is 128.

You should shut down the NDB management node, then restart it with your config file:

/usr/local/mysql/bin/ndb_mgmd --initial --reload --config-file=/usr/local/mysql/config.ini

After that, perform a rolling restart of your data nodes, by entering the NDB Management client and one by one restarting your NDB nodes. You should see output like this:

Connected to Management Server at: localhost:1186  
Cluster Configuration  
---------------------  
[ndbd(NDB)]     2 node(s)  
id=1    @192.168.5.4  (mysql-5.6.11 ndb-7.3.2, Nodegroup: 0, Master)  
id=2    @192.168.5.75  (mysql-5.6.11 ndb-7.3.2, Nodegroup: 0)  

[ndb_mgmd(MGM)] 1 node(s)  
id=49   @192.168.5.4  (mysql-5.6.11 ndb-7.3.2)  

[mysqld(API)]   6 node(s)  
id=50 (not connected, accepting connect from 192.168.5.4)  
id=51 (not connected, accepting connect from 192.168.5.4)  
id=52   @192.168.5.4  (mysql-5.6.11 ndb-7.3.2)  
id=53 (not connected, accepting connect from 192.168.5.75)  
id=54 (not connected, accepting connect from 192.168.5.75)  
id=55   @192.168.5.75  (mysql-5.6.11 ndb-7.3.2) 

ndb_mgm> 1 restart
Node 1: Node shutdown initiated
Node 1: Node shutdown completed, restarting, no start.
Node 1 is being restarted

ndb_mgm> Node 1: Start initiated (version 7.3.2)
Node 1: Started (version 7.3.2)
2 restart
Node 2: Node shutdown initiated
Node 2: Node shutdown completed, restarting, no start.
Node 2 is being restarted

ndb_mgm> Node 2: Start initiated (version 7.3.2)
Node 2: Started (version 7.3.2)

Once the rolling restart has been completed, your data nodes will use the new config and - hopefully - your troubles will be resolved.

Edit 2: elaborating based on comment #2

From this page: the script might simply be too large for your server. Try splitting it into separate files of about 1000 lines each, this should create 3 or maybe 4 files. (I used http://haitianschoolwithoutborders.org/liferay/create-minimal/create-minimal-mysql.sql for reference).

Don't forget to add use lportal; on top of every file, or to specify the database in your import command (e.g. mysql -uliferay -p lportal < minimal.1.sql).

I just run create-minimal-mysql.sql which simply create tables and indexes. And it fail when creating table UserGroup, and it created 127 tables before that. I also change the order of the 'create table' script. And the number of tables created before fail is still 127. And I think this is problem. Do you have any suggestion?? — supper aban_89, Aug 11 '13 at 10:45
Thanks for your help! I follow your step but nothing changed. I tried creating new tables in this database. Create 3 new table. No error. The default value of MaNoOfTables is 4096 when initial. So may be 127 is the limit of querry per script file, isn't it? — supper aban_89, Aug 11 '13 at 14:45
Thanks for your solution. I tried run the insert section of this file. More than 500 insert commands. No error. But error with 128 create tables command. And when I run create index script, it return the same error at the first command. I only want to find what is exactly the problem. Because my backup file is more than 70 MB. Can you help me? — supper aban_89, Aug 11 '13 at 17:17
And if you split the create tables section in two halves and run them separately? — Luc van Donkersgoed, Aug 11 '13 at 17:47
I'm sorry! The max number of table created with NDBCLUSTER engine option look like still 128. I created new table without this option. But the initial MaxNoOfTables is 4096(I ran 'ndb_config -q MaxNoOfTables' to check again). So how can I check the MaxNoOfTable value in each data node?? — supper aban_89, Aug 11 '13 at 18:36
I believe you can provide the nodeid with ndb_config. See http://dev.mysql.com/doc/refman/5.1/en/mysql-cluster-programs-ndb-config.html#option_ndb_config_nodeid — Luc van Donkersgoed, Aug 11 '13 at 19:32
I found the problem. I insert table with "engine=NDBCLUSTER". It fail. Type show warning. It is MaxNoOfOrderedIndexes. I increased this attribute to 4096. And no error. Thanks for your help. — supper aban_89, Aug 12 '13 at 13:05
Ah, good to hear you found the solution. I was kinda out of ideas.. Thanks for accepting my answer, even though you found it yourself. — Luc van Donkersgoed, Aug 12 '13 at 13:32

ndb cluster table is full

1 Answers1