MySQL Cluster测试过程中的错误汇总--ERROR 1296 HY000 等等

分享一下我老师大神的人工智能教程！零基础，通俗易懂！http://blog.csdn.net/jiangjunshow

也欢迎大家转载本篇文章。分享知识，造福人民，实现我们中华民族伟大复兴！

               

参考资料：

http://dev.mysql.com/doc/refman/5.1/en/mysql-cluster-privilege-distribution.html

 http://www.clusterdb.com/mysql-cluster/sharing-user-credentials-between-mysql-servers-with-cluster/

 http://blog.chinaunix.net/uid-20639775-id-294484.html

-- =============================================================================
【1】查看表空间数据文件大小
-- =============================================================================  
mysql>
SELECT TABLESPACE_NAME, FILE_NAME, EXTENT_SIZE*TOTAL_EXTENTS/1024/1024 AS TOTAL_MB, EXTENT_SIZE*FREE_EXTENTS/1024/1024 AS FREE_MB, EXTRA FROM information_schema.FILES WHERE FILE_TYPE="DATAFILE";
+-----------------+------------+----------------+----------------+----------------+
| TABLESPACE_NAME | FILE_NAME  | TOTAL_MB       | FREE_MB        | EXTRA          |
+-----------------+------------+----------------+----------------+----------------+
| ts_1            | data_2.dat | 10240.00000000 | 10146.00000000 | CLUSTER_NODE=4 |
| ts_1            | data_2.dat | 10240.00000000 | 10146.00000000 | CLUSTER_NODE=5 |
| ts_1            | data_2.dat | 10240.00000000 | 10146.00000000 | CLUSTER_NODE=6 |
| ts_1            | data_2.dat | 10240.00000000 | 10162.00000000 | CLUSTER_NODE=7 |
| ts_1            | data_1.dat |   512.00000000 |    19.00000000 | CLUSTER_NODE=4 |
| ts_1            | data_1.dat |   512.00000000 |    19.00000000 | CLUSTER_NODE=5 |
| ts_1            | data_1.dat |   512.00000000 |    16.00000000 | CLUSTER_NODE=6 |
| ts_1            | data_1.dat |   512.00000000 |     0.00000000 | CLUSTER_NODE=7 |
+-----------------+------------+----------------+----------------+----------------+  
 
 
-- ============================
【2】：建库报错


mysql> CREATE DATABASE zhang;
Query OK, 1 row affected, 2 warnings (0.01 sec)


mysql> 
mysql> 
mysql> 
mysql> show warnings;
+---------+------+----------------------------------------------------------------------------------+
| Level   | Code | Message                                                                          |
+---------+------+----------------------------------------------------------------------------------+
| Warning | 1296 | Got error 4009 'Cluster Failure' from NDB. Could not acquire global schema lock  |
| Warning | 1296 | Got error 4009 'Cluster Failure' from Could not log query '%s' on other mysqld's |
+---------+------+----------------------------------------------------------------------------------+
2 rows in set (0.00 sec)


【ok】安全模式启动
/usr/local/mysql/bin/mysqld_safe &




-- ==============================
【3】ERROR 1296 (HY000): Got error 157 'Unknown error code' from NDBCLUSTER
mysql> use bg;
Database changed
mysql> 
mysql> 
mysql> show create table bgt1;
ERROR 1296 (HY000): Got error 157 'Unknown error code' from NDBCLUSTER
mysql> 
mysql> 
mysql> 
mysql> 
mysql> 
mysql> show create table bgt1;
ERROR 1296 (HY000): Got error 157 'Unknown error code' from NDBCLUSTER
mysql> 
mysql> 
mysql> create table bgt2(id int,name varchar(20))engine=ndb;
ERROR 1005 (HY000): Can't create table 'bg.bgt2' (errno: 157)




【ok】之后关闭sql节点，重新启动，搞定了！
[root@banggo data]# /etc/rc.d/init.d/mysqld stop
Shutting down MySQL....                                    [确定]
[root@banggo data]# /etc/rc.d/init.d/mysqld start
Starting MySQL...                                          [确定]
[root@banggo data]# mysql
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 2
Server version: 5.5.19-ndb-7.2.4-gpl MySQL Cluster Community Server (GPL)


Copyright (c) 2000, 2011, Oracle and/or its affiliates. All rights reserved.


Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.


Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.


mysql> use bg;
Database changed
mysql> show tables;
+--------------+
| Tables_in_bg |
+--------------+
| bgt1         |
+--------------+
1 row in set (0.03 sec)


mysql> desc bgt1;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id    | int(11)     | NO   | PRI | 0       |       |
| name  | varchar(20) | YES  |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+
2 rows in set (0.02 sec)












-- =============================================================================
【4】ERROR 1296 (HY000): Got error 157 'Unknown error code' from NDBCLUSTER
-- =============================================================================
2012-07-18 09:58:15 [MgmtSrvr] WARNING  -- Failed to allocate nodeid for API at 10.100.200.41. Returned eror: 'No free node id found for mysqld(API).'
2012-07-18 09:58:16 [MgmtSrvr] WARNING  -- Failed to allocate nodeid for API at 10.100.200.39. Returned eror: 'No free node id found for mysqld(API).'
2012-07-18 09:58:16 [MgmtSrvr] WARNING  -- Failed to allocate nodeid for API at 10.100.200.39. Returned eror: 'No free node id found for mysqld(API).'
2012-07-18 09:58:17 [MgmtSrvr] WARNING  -- Failed to allocate nodeid for API at 10.100.200.41. Returned eror: 'No free node id found for mysqld(API).'
2012-07-18 09:58:17 [MgmtSrvr] WARNING  -- Failed to allocate nodeid for API at 10.100.200.41. Returned eror: 'No free node id found for mysqld(API).'
2012-07-18 09:58:18 [MgmtSrvr] WARNING  -- Failed to allocate nodeid for API at 10.100.200.39. Returned eror: 'No free node id found for mysqld(API).'
2012-07-18 09:58:18 [MgmtSrvr] WARNING  -- Failed to allocate nodeid for API at 10.100.200.39. Returned eror: 'No free node id found for mysqld(API).'
2012-07-18 09:58:19 [MgmtSrvr] WARNING  -- Failed to allocate nodeid for API at 10.100.200.41. Returned eror: 'No free node id found for mysqld(API).'
2012-07-18 09:58:19 [MgmtSrvr] WARNING  -- Failed to allocate nodeid for API at 10.100.200.41. Returned eror: 'No free node id found for mysqld(API).'
2012-07-18 09:58:20 [MgmtSrvr] WARNING  -- Failed to allocate nodeid for API at 10.100.200.39. Returned eror: 'No free node id found for mysqld(API).'


【ok】：在config.ini里面加空的sql节点，以便自动扩展s
[API]
[API]






-- =============================================================================
【5】数据节点报错
-- =============================================================================
2012-07-18 23:34:48 [ndbd] INFO     -- Start initiated (mysql-5.5.19 ndb-7.2.4)
NDBFS/AsyncFile: Allocating 310392 for In/Deflate buffer
Adding 7164Mb to ZONE_LO (32896,229247)
Adding 4301Mb to ZONE_LO (262145,137607)
NDBFS/AsyncFile: Allocating 310392 for In/Deflate buffer
NDBFS/AsyncFile: Allocating 310392 for In/Deflate buffer
NDBFS/AsyncFile: Allocating 310392 for In/Deflate buffer
NDBFS/AsyncFile: Allocating 310392 for In/Deflate buffer
NDBFS/AsyncFile: Allocating 310392 for In/Deflate buffer
NDBFS/AsyncFile: Allocating 310392 for In/Deflate buffer
NDBFS/AsyncFile: Allocating 310392 for In/Deflate buffer
NDBFS/AsyncFile: Allocating 310392 for In/Deflate buffer
NDBFS/AsyncFile: Allocating 310392 for In/Deflate buffer
NDBFS/AsyncFile: Allocating 310392 for In/Deflate buffer
NDBFS/AsyncFile: Allocating 310392 for In/Deflate buffer
NDBFS/AsyncFile: Allocating 310392 for In/Deflate buffer
WOPool::init(61, 9)
RWPool::init(22, 14)
2012-07-18 23:35:00 [ndbd] INFO     -- timerHandlingLab now: 12658118985 sent: 12658118306 diff: 679
2012-07-18 23:36:09 [ndbd] WARNING  -- Ndb kernel thread 0 is stuck in: Allocating memory elapsed=60029
2012-07-18 23:36:09 [ndbd] INFO     -- Watchdog: User time: 244  System time: 6855
2012-07-18 23:36:35 [ndbd] ALERT    -- Node 4: Forced node shutdown completed. Occured during startphase 0. Initiated by signal 9.


【ok】：调整参数值
调小参数，内存超过了。




-- =============================================================================
【6】建立表空间报错
-- =============================================================================
mysql> CREATE LOGFILE GROUP lg_1 ADD UNDOFILE 'undo_1.log' INITIAL_SIZE 1024 M UNDO_BUFFER_SIZE 128 M ENGINE NDBCLUSTER;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'M UNDO_BUFFER_SIZE 128 M ENGINE NDBCLUSTER' at line 1
mysql> 


【ok】M不识别
-- CREATE LOGFILE GROUP lg_1 ADD UNDOFILE 'undo_1.log' INITIAL_SIZE 536870912 UNDO_BUFFER_SIZE 67108864 ENGINE NDBCLUSTER;
mysql> CREATE LOGFILE GROUP lg_1 ADD UNDOFILE 'undo_1.log' INITIAL_SIZE 536870912 UNDO_BUFFER_SIZE 67108864 ENGINE NDBCLUSTER;
Query OK, 0 rows affected (27.95 sec)


-- CREATE TABLESPACE ts_1  ADD DATAFILE 'data_1.dat' USE LOGFILE GROUP lg_1 INITIAL_SIZE 536870912 ENGINE NDBCLUSTER; 
mysql> CREATE TABLESPACE ts_1  ADD DATAFILE 'data_1.dat' USE LOGFILE GROUP lg_1 INITIAL_SIZE 536870912 ENGINE NDBCLUSTER; 
Query OK, 0 rows affected (28.79 sec)


 # 创建使用磁盘存储的表：
CREATE TABLE `bgtdisk` (    
  `Name` varchar(50) NOT NULL,
  `ProviderName` varchar(200) NOT NULL,
  PRIMARY KEY (`Name`)
) tablespace ts_1 storage disk ENGINE=ndbcluster DEFAULT CHARSET=utf8;




-- =============================================================================
【7】配置报错
-- =============================================================================
Caused by error 2353: 'Insufficent nodes for system restart(Restart error). Temporary error, restart node'.












-- =============================================================================
【8】ERROR 1528 (HY000): Failed to create LOGFILE GROUP
-- =============================================================================
mysql> CREATE LOGFILE GROUP lg_02 ADD UNDOFILE 'undo_02.log' INITIAL_SIZE 5368709120         UNDO_BUFFER_SIZE 67108864       ENGINE NDBCLUSTER; 
ERROR 1528 (HY000): Failed to create LOGFILE GROUP
mysql> 
mysql> 
mysql> 
mysql> show errors;
+-------+------+--------------------------------+
| Level | Code | Message                        |
+-------+------+--------------------------------+
| Error | 1528 | Failed to create LOGFILE GROUP |
+-------+------+--------------------------------+
1 row in set (0.00 sec)


mysql> 


[]解决办法：
原来现在的MYSQL只支持创建一个LOGFILE GROUP文件。
只有删掉原来的才可以创建新的。


-- =============================================================================
【9】ERROR 1114 (HY000): The table 'UserMvpbak' is full
-- =============================================================================
mysql> insert into UserMvpbak select * from UserMvp limit 800000,200000;
ERROR 1114 (HY000): The table 'UserMvpbak' is full
mysql> 
alter tablespace ts_1
         add datafile 'data_15.dat'
         initial_size 10737418240
           engine ndb;
  
【ok】原有的数据文件空间满了，需要增加新的数据文件   
mysql> alter tablespace ts_1
    ->      add datafile 'data_2.dat'
    ->      initial_size 10737418240
    ->      engine ndb;


Query OK, 0 rows affected (1 min 54.30 sec)


mysql>  insert into bguserdb.UserPoints_2012 select * from test.UserPoints_2012 limit 1200000,300000;
ERROR 1114 (HY000): The table 'UserPoints_2012' is full


alter tablespace ts_1
         add datafile 'data_16.dat'
         initial_size 10737418240
           engine ndb;




mysql> 


-- =============================================================================
【10】ERROR 1114 (HY000): The table 'UserMvpbak' is full
-- =============================================================================