MySQL Cluster測試過程中的錯誤匯總--ERROR 1296 (HY000)等等
========================================
【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
-- =============================================================================
mysql> insert into UserMvpbak select * from UserMvp limit 940000,3000000;
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction
mysql>
[ok]
mysql> replace into UserMvpbak select * from UserMvp limit 940000,800000;
Query OK, 755105 rows affected (2 min 44.54 sec)
Records: 755105 Duplicates: 0 Warnings: 0
-- =============================================================================
【11】Node 4: Forced node shutdown completed. Occured during startphase 0. Initiated by signal 11.
-- =============================================================================
2012-08-15 12:35:38 [ndbd] INFO -- Start initiated (mysql-5.5.25 ndb-7.2.7)
NDBFS/AsyncFile: Allocating 310392 for In/Deflate buffer
2012-08-15 12:35:45 [ndbd] WARNING -- Ndb kernel thread 0 is stuck in: Polling for Receive elapsed=159
2012-08-15 12:35:45 [ndbd] INFO -- timerHandlingLab now: 498814955 sent: 498814782 diff: 173
2012-08-15 12:35:45 [ndbd] INFO -- Watchdog: User time: 28 System time: 497
2012-08-15 12:35:47 [ndbd] INFO -- timerHandlingLab now: 498816970 sent: 498816803 diff: 167
2012-08-15 12:35:49 [ndbd] INFO -- Watchdog: User time: 39 System time: 741
2012-08-15 12:35:49 [ndbd] WARNING -- Watchdog: Warning overslept 262 ms, expected 100 ms.
2012-08-15 12:35:49 [ndbd] INFO -- timerHandlingLab now: 498818963 sent: 498818779 diff: 184
Adding 5201Mb to ZONE_LO (1152,166408)
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
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
NDBFS/AsyncFile: Allocating 310392 for In/Deflate buffer
NDBFS/AsyncFile: Allocating 310392 for In/Deflate buffer
NDBFS/AsyncFile: Allocating 310392 for In/Deflate buffer
2012-08-15 12:35:56 [ndbd] WARNING -- Ndb kernel thread 0 is stuck in: Allocating memory elapsed=6002
2012-08-15 12:35:56 [ndbd] INFO -- Watchdog: User time: 61 System time: 1179
2012-08-15 12:36:02 [ndbd] WARNING -- Ndb kernel thread 0 is stuck in: Allocating memory elapsed=12006
2012-08-15 12:36:02 [ndbd] INFO -- Watchdog: User time: 64 System time: 1763
2012-08-15 12:36:08 [ndbd] WARNING -- Ndb kernel thread 0 is stuck in: Allocating memory elapsed=18030
2012-08-15 12:36:08 [ndbd] INFO -- Watchdog: User time: 70 System time: 1957
2012-08-15 12:36:12 [ndbd] ALERT -- Node 4: Forced node shutdown completed. Occured during startphase 0. Initiated by signal 11.
[ok] 注釋如下三個參數，啟動ok！
#MaxNoOfConcurrentTransactions=100000
#MaxNoOfConcurrentOperations=10000000
#MaxNoOfLocalOperations=11000000
-- =============================================================================
【12】啟動管理節點報錯
-- =============================================================================
[root@banggo mysql-cluster]# ndb_mgmd -f /usr/local/mysql/cluster-conf/config.ini --reload
MySQL Cluster Management Server mysql-5.5.25 ndb-7.2.7
2012-08-15 21:36:54 [MgmtSrvr] ERROR -- at line 18: Illegal value 128 for parameter LockPagesInMainMemory.
Legal values are between 0 and 2
2012-08-15 21:36:54 [MgmtSrvr] ERROR -- at line 18: Could not parse name-value pair in config file.
2012-08-15 21:36:54 [MgmtSrvr] ERROR -- Could not load configuration from '/usr/local/mysql/cluster-conf/config.ini'
2012-08-15 21:36:54 [MgmtSrvr] ERROR -- Could not determine which nodeid to use for this node. Specify it with --ndb-nodeid=<nodeid> on command line
[root@banggo mysql-cluster]#
[root@banggo mysql-cluster]#
【ok】LockPagesInMainMemory值設置太大了！改成默認的1吧。
-- =============================================================================
【13】 1528 錯誤
-- =============================================================================
mysql> CREATE LOGFILE GROUP lg_1
-> ADD UNDOFILE 'undo_2.log'
-> INITIAL_SIZE 634217728
-> UNDO_BUFFER_SIZE 134217728
-> ENGINE NDBCLUSTER;
ERROR 1528 (HY000): Failed to create LOGFILE GROUP
mysql>
mysql>
mysql>
mysql> show warnings;
+---------+------+---------------------------------------------------+
| Level | Code | Message |
+---------+------+---------------------------------------------------+
| Warning | 1296 | Got error 1504 'Out of logbuffer memory' from NDB |
| Error | 1528 | Failed to create LOGFILE GROUP |
+---------+------+---------------------------------------------------+
2 rows in set (0.00 sec)
【ok】
# UNDO_BUFFER_SIZE不能超過config.ini的配置RedoBuffer=32M大小，否則創建失敗，
CREATE LOGFILE GROUP lg_1
ADD UNDOFILE 'undo_1.log'
INITIAL_SIZE 634217728
UNDO_BUFFER_SIZE 33554432
ENGINE NDBCLUSTER;
CREATE TABLESPACE ts_1
ADD DATAFILE 'data_10.dat'
USE LOGFILE GROUP lg_1
INITIAL_SIZE 32212254720
EXTENT_SIZE 33554432
ENGINE NDBCLUSTER;
執行結束之後，需要去數據目錄/var/lib/mysql-cluster/裡面看看undo文件和data文件，如果存在，那麼就證明建立成功了。
可以建立2個表空間
CREATE TABLESPACE ts_2
ADD DATAFILE 'data_20.dat'
USE LOGFILE GROUP lg_1
INITIAL_SIZE 10737418240
EXTENT_SIZE 33554432
ENGINE NDBCLUSTER;
create table bguserdb.mcbak like test.MvpCard;
alter table bguserdb.mcbak tablespace ts_1 storage disk ENGINE=ndbcluster DEFAULT CHARSET=utf8;
insert into bguserdb.mcbak select * from test.MvpCard;
CREATE TABLESPACE ts_1
ADD DATAFILE 'data_10.dat‘ USE LOGFILE GROUP lg_1 INITIAL_SIZE 32212254720
EXTENT_SIZE 33554432 ENGINE NDBCLUSTER;
-- =============================================================================
【14】 1528 錯誤
-- =============================================================================
mysql> insert into bguserdb.MvpCard select * from test.MvpCard;
ERROR 1297 (HY000): Got temporary error 410 'REDO log files overloaded (decrease TimeBetweenLocalCheckpoints or increase NoOfFragmentLogFiles)' from NDBCLUSTER
mysql>
【】增加2個參數的值
TimeBetweenLocalCheckpoints=30
NoOfFragmentLogFiles=128
-- =============================================================================
【15】 1005 錯誤
-- =============================================================================
mysql> create table bguserdb.mcbak like test.MvpCard;
Query OK, 0 rows affected (0.07 sec)
mysql> alter table bguserdb.mcbak tablespace ts_1 storage disk ENGINE=ndbcluster DEFAULT CHARSET=utf8;
ERROR 1005 (HY000): Can't create table 'bguserdb.#sql-14ab_2' (errno: 140)
mysql> show errors;
+-------+------+--------------------------------------------------------+
| Level | Code | Message |
+-------+------+--------------------------------------------------------+
| Error | 1005 | Can't create table 'bguserdb.#sql-14ab_2' (errno: 140) |
+-------+------+--------------------------------------------------------+
1 row in set (0.00 sec)
mysql> show warnings;
+---------+------+--------------------------------------------------------+
| Level | Code | Message |
+---------+------+--------------------------------------------------------+
| Warning | 1296 | Got error 755 'Invalid tablespace' from NDB |
| Error | 1005 | Can't create table 'bguserdb.#sql-14ab_2' (errno: 140) |
+---------+------+--------------------------------------------------------+
2 rows in set (0.00 sec)
mysql>
【ok】重新建立表空間：
CREATE LOGFILE GROUP lg_1
ADD UNDOFILE 'undo_1.log'
INITIAL_SIZE 334217728
UNDO_BUFFER_SIZE 33554432
ENGINE NDBCLUSTER;
CREATE TABLESPACE ts_1 ADD DATAFILE 'data_11.dat' USE LOGFILE GROUP lg_1 INITIAL_SIZE 4294967296 EXTENT_SIZE 33554432 ENGINE NDBCLUSTER;
alter table bguserdb.MvpCard tablespace ts_1 storage disk ENGINE=ndbcluster DEFAULT CHARSET=utf8;
insert into bguserdb.MvpCard select * from test.MvpCard limit 0,100000;
-- =============================================================================
【16】 1005 錯誤
-- =============================================================================
mysql> alter table order_infobak TABLESPACE ts_1 STORAGE DISK ENGINE=ndbcluster DEFAULT CHARSET=utf8;
ERROR 1005 (HY000): Can't create table 'openshop.#sql-711_3' (errno: 851)
mysql> show warnings;
+---------+------+---------------------------------------------------------------------------------------------------------------------+
| Level | Code | Message |
+---------+------+---------------------------------------------------------------------------------------------------------------------+
| Warning | 1296 | Got error 851 'Maximum 8052 bytes of FIXED columns supported, use varchar or COLUMN_FORMAT DYNMIC instead' from NDB |
| Error | 1005 | Can't create table 'openshop.#sql-711_3' (errno: 851) |
+---------+------+---------------------------------------------------------------------------------------------------------------------+
2 rows in set (0.00 sec)
CREATE TABLE `order_xxxx` (
`order_sn` varchar(20) NOT NULL DEFAULT '' COMMENT '訂單編號，唯一鍵',
`xx` tinyint(3) NOT NULL DEFAULT '1' COMMENT '交易類型 1：款到發貨 2：貨到付款 3：擔保交易',
`xx` tinyint(1) DEFAULT '0' COMMENT '訂單類型 0，正常訂單 1，補發訂單 2，換貨訂單',
`xxxxxx` varchar(30) DEFAULT '' COMMENT '關聯換貨單原訂單號',
`xxxxxx` varchar(30) NOT NULL DEFAULT '' COMMENT '關聯退單編號（該項目用來存放換貨單時生成的退貨單所對應的退單號）',
`xxxx` varchar(30) DEFAULT NULL COMMENT '關聯退款單訂單號（該項目用來存放換貨時生成的退款單的對應退單號）',
`user_id` varchar(60) NOT NULL DEFAULT '' COMMENT '用戶ID，關聯user_users表的user_id',
`user_name` varchar(60) NOT NULL DEFAULT '' COMMENT '用戶名',
`use_level` int(6) DEFAULT '0' COMMENT '下單時會員等級',
`xx` int(6) DEFAULT '0' COMMENT '下單時的淘寶拍拍會員等級',
`aa` varchar(60) NOT NULL DEFAULT '' COMMENT '收貨人的姓名，用戶頁面填寫，默認取值於表user_address',
`order_status` tinyint(3) NOT NULL DEFAULT '0' COMMENT '訂單狀態 （0，未確認；1，已確認；2，已取消；3，無效；4，退貨；5，鎖定；6，解鎖；7，完成；8，拒收；9，已合並；10，已拆分；）',
`aa` tinyint(3) NOT NULL DEFAULT '0' COMMENT '支付總狀態（0，未付款；1，部分付款；2，已付款；3，已結算）',
`cc` tinyint(3) NOT NULL DEFAULT '0' COMMENT '發貨總狀態（0，未發貨；1，備貨中；2，部分發貨；3，已發貨；4，部分收貨；5，客戶已收貨）',
`cc` int(11) NOT NULL DEFAULT '0' COMMENT '訂單鎖定狀態（0，未鎖定；1，已鎖定）',
`ee` tinyint(1) NOT NULL DEFAULT '0' COMMENT '處理狀態 （0 正常 1 掛起）',
`tt` tinyint(1) NOT NULL DEFAULT '0' COMMENT '問題單狀態 （0 否 1 是）',
`notice_status` tinyint(1) NOT NULL DEFAULT '0' COMMENT '通知收款狀態 （0 否 1 是）',
`timeout_status` tinyint(1) NOT NULL DEFAULT '0' COMMENT '是否超時單 （0 正常 1 超時）',
`depot_status` tinyint(3) NOT NULL DEFAULT '0' COMMENT '分倉發貨狀態（0，未分倉 1，已分倉未通知 2，已分倉已通知）',
`return_status` tinyint(1) NOT NULL DEFAULT '0' COMMENT '0未退貨、1部分退貨申請中、2退貨申請中、3已部分退貨、4已退貨',
`refund_status` tinyint(1) NOT NULL DEFAULT '0' COMMENT '0未退款、1部分退款申請中、2退款申請中、3已部分退款、4已退款',
`is_order_print` tinyint(1) NOT NULL DEFAULT '0' COMMENT '是否要發票',
`money_treatment_type` tinyint(1) DEFAULT '1' COMMENT '錢款處理方式：1.補款 2,退款 3,不補不退',
`order_from` varchar(20) NOT NULL DEFAULT '' COMMENT '訂單來源',
`merge_from` varchar(255) DEFAULT NULL COMMENT '合並來源',
`split_to` varchar(255) DEFAULT NULL COMMENT '訂單拆分為',
`split_from` varchar(255) DEFAULT '' COMMENT '由XX訂單拆分生成',
`add_time` datetime DEFAULT NULL COMMENT '訂單生成時間',
`confirm_time` datetime DEFAULT NULL COMMENT '訂單確認時間',
`finish_time` datetime DEFAULT NULL COMMENT '訂單完成時間',
`clear_time` datetime DEFAULT NULL COMMENT '訂單結算時間',
`question_time` datetime DEFAULT NULL COMMENT '設為問題單時間',
`lock_time` datetime DEFAULT NULL COMMENT '訂單鎖定時間',
`update_time` datetime DEFAULT NULL COMMENT '訂單最後更新時間',
`notice_time` datetime DEFAULT NULL COMMENT '通知收款時間',
`order_out_sn` varchar(255) NOT NULL DEFAULT '' COMMENT '外部交易號',
`beneficiary_id` mediumint(8) NOT NULL DEFAULT '0' COMMENT '受益人ID，如果開啟提成功能',
`from_ad` smallint(5) NOT NULL DEFAULT '0' COMMENT '訂單由某廣告帶來的廣告id，應該取值於ad',
`pr_ids` varchar(255) NOT NULL DEFAULT '' COMMENT '參加了哪些促銷',
`pr_name` varchar(255) NOT NULL DEFAULT '' COMMENT '參加促銷的名字(多個)',
`referer` varchar(255) NOT NULL DEFAULT '' COMMENT '訂單的來源媒體',
`how_oos` varchar(120) NOT NULL DEFAULT '' COMMENT '缺貨處理方式，等待所有商品備齊後再發； 取消訂單；與店主協商',
`hhh` varchar(30) NOT NULL DEFAULT '' COMMENT '通過活動購買的商品的代號；GROUP_BUY是團購AUCTION，是拍賣；SNATCH，奪寶奇兵；正常普通產品該處為空',
`extension_id` mediumint(8) NOT NULL DEFAULT '0' COMMENT '通過活動購買的物品的id，取值goods_activity；如果是正常普通商品，該處為0',
`is_separate` tinyint(1) NOT NULL DEFAULT '0' COMMENT '0，未分成或等待分成；1，已分成；2，取消分成；',
`parent_id` mediumint(8) NOT NULL DEFAULT '0' COMMENT '能獲得推薦分成的用戶id，id取值於表users',
`outlet_type` tinyint(1) NOT NULL DEFAULT '0' COMMENT '代銷類型(0無;1是代銷;2是CPS)',
`jjj` varchar(250) DEFAULT NULL COMMENT 'CPS來源',
`inv_payee` varchar(120) NOT NULL DEFAULT '' COMMENT '發票抬頭，用戶頁面填寫',
`inv_content` varchar(120) NOT NULL DEFAULT '' COMMENT '發票內容，用戶頁面選擇，取值shop_config的code字段的值為invoice_content的value',
`inv_type` varchar(60) NOT NULL DEFAULT '' COMMENT '發票類型，用戶頁面選擇，取值shop_config的code字段的值為invoice_type的value',
`tax` decimal(10,2) NOT NULL DEFAULT '0.00' COMMENT '發票稅額',
`invoice_status` tinyint(1) NOT NULL DEFAULT '0' COMMENT '發票狀態',
`delivery_station_id` int(10) NOT NULL DEFAULT '0' COMMENT '發票ID',
......
`goods_count` int(11) NOT NULL DEFAULT '0' COMMENT '訂單商品總數',
`discount` decimal(10,2) NOT NULL DEFAULT '0.00' COMMENT '折扣金額',
`confirm_lasttime` datetime DEFAULT NULL COMMENT '訂單確認期限',
`complete_lasttime` datetime DEFAULT NULL COMMENT '訂單完成最後期限',
`settled_lasttime` datetime DEFAULT NULL COMMENT '結算最後期限',
`all_process_lasttime` datetime DEFAULT NULL COMMENT '訂單全程處理期限',
`urgent_level` tinyint(3) NOT NULL DEFAULT '0' COMMENT '訂單加急等級',
`period_series` int(8) NOT NULL DEFAULT '0' COMMENT '周期系列ID',
`to_buyer` varchar(255) NOT NULL COMMENT '商家給客戶的留言,當該字段有值時可以在訂單查詢看到',
`chased_or_not` tinyint(1) NOT NULL DEFAULT '0' COMMENT '是否已追單（0 否 1 是）',
`is_agent` tinyint(2) DEFAULT '-1' COMMENT '是否代理換貨',
`is_update` tinyint(1) DEFAULT '0' COMMENT '是否更新(ERP用)',
`update_type` varchar(255) DEFAULT NULL COMMENT '更新類型（ERP用）',
`last_update_time` datetime DEFAULT NULL COMMENT '最後更新時間（ERP用）',
`is_return` tinyint(1) NOT NULL DEFAULT '0' COMMENT '是否已退款（暫不使用）',
`return_fee` decimal(10,2) NOT NULL DEFAULT '0.00' COMMENT '退款金額（暫不使用）',
`integral_money` decimal(10,2) NOT NULL DEFAULT '0.00' COMMENT '使用積分金額（暫不使用）',
`wait_pay` tinyint(1) NOT NULL DEFAULT '0' COMMENT '是否等待買家付款訂單（0 否 1 是）（暫不使用）',
`tel` varchar(60) NOT NULL DEFAULT '' COMMENT '收貨人的電話號碼',
`mobile` varchar(60) NOT NULL DEFAULT '' COMMENT '收貨人的手機號碼',
`email` varchar(60) NOT NULL DEFAULT '' COMMENT '收貨人的電子郵件',
`total_market_fee` decimal(10,2) NOT NULL DEFAULT '0.00',
`real_shipping_total_fee` decimal(10,2) NOT NULL DEFAULT '0.00' COMMENT '真實的總快遞費用,不免郵',
`cancel_code` varchar(20) NOT NULL DEFAULT '' COMMENT '取消原因編碼',
`cancel_reason` varchar(20) NOT NULL DEFAULT '' COMMENT '取消原因名稱',
`order_category` int(11) DEFAULT '1' COMMENT '訂單種類 1：零售 2：物資領用 3：其它出庫',
`reason` varchar(5) NOT NULL DEFAULT 'S' COMMENT '原因： S(零售) ；J (物資領用 )； T(其它出庫 )；',
.......
PRIMARY KEY (`order_sn`),
KEY `user_id` (`user_id`),
KEY `order_from` (`order_from`),
KEY `order_out_sn` (`order_out_sn`),
KEY `order_status` (`order_status`),
KEY `addtime` (`add_time`),
KEY `is_update` (`is_update`),
KEY `relating_return_sn` (`relating_return_sn`),
KEY `confirm_lasttime` (`confirm_lasttime`),
KEY `complete_lasttime` (`complete_lasttime`),
KEY `settled_lasttime` (`settled_lasttime`),
KEY `all_process_lasttime` (`all_process_lasttime`),
KEY `in_consignee` (`consignee`),
KEY `in_last_update_time` (`last_update_time`),
KEY `in_source_code` (`source_code`)
) TABLESPACE ts_1 STORAGE DISK ENGINE=ndbcluster DEFAULT CHARSET=utf8 COMMENT='信息表';
ERROR 1005 (HY000): Can't create table 'openshop.order_info' (errno: 851)
mysql> show warnings;
+---------+------+---------------------------------------------------------------------------------------------------------------------+
| Level | Code | Message |
+---------+------+---------------------------------------------------------------------------------------------------------------------+
| Warning | 1296 | Got error 851 'Maximum 8052 bytes of FIXED columns supported, use varchar or COLUMN_FORMAT DYNMIC instead' from NDB |
| Error | 1005 | Can't create table 'openshop.order_info' (errno: 851) |
+---------+------+---------------------------------------------------------------------------------------------------------------------+
2 rows in set (0.00 sec)
[ok] ndb對表的varchar總長度有限制。
-- =============================================================================
【17】 1297 錯誤
-- =============================================================================
mysql> alter table ERP_BULK_ETL_FLAGE TABLESPACE ts_1 STORAGE DISK ENGINE=ndbcluster DEFAULT CHARSET=utf8 comment '';
ERROR 1297 (HY000): Got temporary error 1501 'Out of undo space' from NDBCLUSTER
mysql>
mysql> show errors;
+-------+------+--------------------------------------------------------------+
| Level | Code | Message |
+-------+------+--------------------------------------------------------------+
| Error | 1297 | Got temporary error 1501 'Out of undo space' from NDBCLUSTER |
| Error | 1296 | Got error 4350 'Transaction already aborted' from NDBCLUSTER |
| Error | 1180 | Got error 4350 during COMMIT |
+-------+------+--------------------------------------------------------------+
3 rows in set (0.00 sec)
mysql>
[ok]
1 增加數據文件，數據文件空間可能不夠了。
alter tablespace ts_1 add datafile 'data_12.dat' initial_size 10737418240 engine NDBCLUSTER;
alter tablespace ts_1 add datafile 'data_13.dat' initial_size 10737418240 engine NDBCLUSTER;
alter tablespace ts_1 add datafile 'data_14.dat' initial_size 10737418240 engine NDBCLUSTER;
2 增加undo文件
alter LOGFILE GROUP lg_1 ADD UNDOFILE 'undo_21.log' ENGINE NDBCLUSTER;
-- =============================================================================
【18】 1297 錯誤
-- =============================================================================
mysql> alter table account_adjust_users TABLESPACE ts_1 STORAGE DISK ENGINE=ndbcluster DEFAULT CHARSET=utf8 comment '';
ERROR 1297 (HY000): Got temporary error 1501 'Out of undo space' from NDBCLUSTER
mysql> show warnings;
+---------+------+--------------------------------------------------------------+
| Level | Code | Message |
+---------+------+--------------------------------------------------------------+
| Warning | 1297 | Got temporary error 1501 'Out of undo space' from NDB |
| Error | 1297 | Got temporary error 1501 'Out of undo space' from NDBCLUSTER |
| Warning | 1296 | Got error 4350 'Transaction already aborted' from NDB |
| Error | 1296 | Got error 4350 'Transaction already aborted' from NDBCLUSTER |
| Error | 1180 | Got error 4350 during COMMIT |
+---------+------+--------------------------------------------------------------+
5 rows in set (0.00 sec)
mysql>
-- =============================================================================
【19】 1297 錯誤
-- =============================================================================
ndb_mgm> Node 7: Forced node shutdown completed. Occured during startphase 4. Caused by error 2303: 'System error, node killed during node restart by other node(Internal error, programming error or missing error message, please report a bug). Temporary error, restart node'.
Node 4: Forced node shutdown completed. Occured during startphase 4. Caused by error 2308: 'Another node failed during system restart, please investigate error(s) on other node(s)(Restart error). Temporary error, restart node'.
Node 5: Forced node shutdown completed. Occured during startphase 4. Caused by error 2308: 'Another node failed during system restart, please investigate error(s) on other node(s)(Restart error). Temporary error, restart node'.
Node 6: Forced node shutdown completed. Occured during startphase 4. Caused by error 2308: 'Another node failed during system restart, please investigate error(s) on other node(s)(Restart error). Temporary error, restart node'.
【ok1】baidu，google之，沒有得到相似的案列，看到一個emporary error, restart node的提示，不得已 restart node 節點
重新在4個data 節點，執行 ndbd --initial，等待2分鐘，搞定！oh，my god，不知道其中的原理，但是data node確實是起來了。
ndb_mgm> Node 4: Started (version 7.2.7)
Node 7: Started (version 7.2.7)
Node 5: Started (version 7.2.7)
Node 6: Started (version 7.2.7)
啟動sql節點，執行檢查數據，my god，原來的數據文件都無法顯示了。
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";
Empty set (0.00 sec)
mysql>
【ok2】第一次ndbd不成功，之後，再重新啟動以一次ndbd，
-- =============================================================================
【20】 Out of undo space
-- =============================================================================
mysql> insert into MvpCard select * from zzbak_MvpCard limit 0,10000;
ERROR 1297 (HY000): Got temporary error 1501 'Out of undo space' from NDBCLUSTER
mysql> show warnings;
+---------+------+--------------------------------------------------------------+
| Level | Code | Message |
+---------+------+--------------------------------------------------------------+
| Warning | 1297 | Got temporary error 1501 'Out of undo space' from NDB |
| Error | 1297 | Got temporary error 1501 'Out of undo space' from NDBCLUSTER |
+---------+------+--------------------------------------------------------------+
2 rows in set (0.00 sec)
【ok】參考網址 http://forums.mysql.com/read.php?25,413217,413217
I add another undo log file ,it's ok now ,but counld some one tell me how can I know the usage of my undo log file ?
SELECT FILE_NAME FROM information_schema.files WHERE FILE_TYPE='UNDO LOG'\G;
執行：alter LOGFILE GROUP lg_1 ADD UNDOFILE 'undo_21.log' UNDO_BUFFER_SIZE 33554432 ENGINE NDBCLUSTER;
mysql> alter LOGFILE GROUP lg_1 ADD UNDOFILE 'undo_21.log' ENGINE NDBCLUSTER;
Query OK, 0 rows affected (4.33 sec)
mysql> SELECT FILE_NAME FROM information_schema.files WHERE FILE_TYPE='UNDO LOG';
+-------------+
| FILE_NAME |
+-------------+
| undo_2.log |
| undo_2.log |
| undo_2.log |
| undo_2.log |
| undo_21.log |
| undo_21.log |
| undo_21.log |
| undo_21.log |
| NULL |
+-------------+
9 rows in set (0.05 sec)
mysql>SELECT FILE_NAME FROM information_schema.files WHERE FILE_TYPE='UNDO LOG'\G;
mysql> insert into MvpCard select * from zzbak_MvpCard limit 0,10000;
Query OK, 10000 rows affected (0.80 sec)
Records: 10000 Duplicates: 0 Warnings: 0
-- =============================================================================
【21】 後台日志報錯，批量insert數據
-- =============================================================================
2012-08-16 22:38:05 [ndbd] ERROR -- c_gcp_list.seize() failed: gci: 38285338476552 nodes: 0000000000000000000000000000000000000000000000000000000000016400
2012-08-16 22:38:05 [ndbd] WARNING -- ACK wo/ gcp record (gci: 8914/8) ref: 0fa2000e from: 0fa2000e
2012-08-16 22:38:05 [ndbd] WARNING -- ACK wo/ gcp record (gci: 8914/8) ref: 0fa2000a from: 0fa2000a
2012-08-16 22:38:05 [ndbd] WARNING -- ACK wo/ gcp record (gci: 8914/8) ref: 0fa20010 from: 0fa20010
2012-08-16 22:38:05 [ndbd] ERROR -- c_gcp_list.seize() failed: gci: 38285338476553 nodes: 0000000000000000000000000000000000000000000000000000000000016400
2012-08-16 22:38:05 [ndbd] WARNING -- ACK wo/ gcp record (gci: 8914/9) ref: 0fa2000e from: 0fa2000e
2012-08-16 22:38:05 [ndbd] WARNING -- ACK wo/ gcp record (gci: 8914/9) ref: 0fa2000a from: 0fa2000a
2012-08-16 22:38:05 [ndbd] ERROR -- c_gcp_list.seize() failed: gci: 38289633443840 nodes: 0000000000000000000000000000000000000000000000000000000000016400
2012-08-16 22:38:05 [ndbd] WARNING -- ACK wo/ gcp record (gci: 8914/9) ref: 0fa20010 from: 0fa20010
2012-08-16 22:38:05 [ndbd] WARNING -- ACK wo/ gcp record (gci: 8915/0) ref: 0fa2000a from: 0fa2000a
2012-08-16 22:38:05 [ndbd] WARNING -- ACK wo/ gcp record (gci: 8915/0) ref: 0fa20010 from: 0fa20010
2012-08-16 22:38:05 [ndbd] WARNING -- ACK wo/ gcp record (gci: 8915/0) ref: 0fa2000e from: 0fa2000e
【ok】減少insert的數量，一次批量從100W減少到50W，ok，不會報異常信息了。
-- =============================================================================
【22】 第4個data節點意外down了
-- =============================================================================
ndb_mgm> Node 7: Forced node shutdown completed. Caused by error 2305: 'Node lost connection to other nodes and can not form a unpartitioned cluster, please investigate if there are error(s) on other node(s)(Arbitration error). Temporary error, restart node'.
ndb_mgm>
【ok】執行ndbd 重新啟動起來
-- =============================================================================
【22】 管理台老是報警，詭異
-- =============================================================================
ndb_mgm> Node 7: Data usage increased to 80%(64016 32K pages of total 80000)
Node 4: Data usage increased to 80%(64007 32K pages of total 80000)
Node 5: Data usage increased to 80%(64014 32K pages of total 80000)
Node 6: Data usage increased to 80%(64001 32K pages of total 80000)
Node 4: Data usage increased to 90%(72005 32K pages of total 80000)
Node 5: Data usage increased to 90%(72014 32K pages of total 80000)
Node 7: Data usage increased to 90%(72010 32K pages of total 80000)
Node 6: Data usage increased to 90%(72009 32K pages of total 80000)
Node 6: Data usage decreased to 89%(71772 32K pages of total 80000)
Node 7: Data usage decreased to 89%(71271 32K pages of total 80000)
Node 4: Data usage decreased to 89%(71215 32K pages of total 80000)
Node 5: Data usage decreased to 88%(70607 32K pages of total 80000)
Node 5: Data usage decreased to 79%(63850 32K pages of total 80000)
Node 7: Data usage decreased to 79%(63673 32K pages of total 80000)
Node 4: Data usage decreased to 78%(62947 32K pages of total 80000)
Node 6: Data usage decreased to 78%(62932 32K pages of total 80000)
-- =============================================================================
【23】 ERROR 1297 (HY000)
-- =============================================================================
mysql> insert into bguserdb.UserPoints_2012 select * from test.UserPoints_2012 limit 1200000,10000;
ERROR 1297 (HY000): Got temporary error 899 'Rowid already allocated' from NDBCLUSTER
mysql>
【ok】網上都說需要調大datamemory，但是我的是磁盤表，應該不是這個原因，不得已只有restart cluster了試試了，還好搞定了！
mysql> replace into bguserdb.UserPoints_2012 select * from test.UserPoints_2012 limit 1200000,300000;
Query OK, 300000 rows affected (59.97 sec)
Records: 300000 Duplicates: 0 Warnings: 0
-- =============================================================================
【24】 ERROR -- c_gcp_list.seize() failed:
-- =============================================================================
2012-08-19 17:00:24 [ndbd] WARNING -- ACK wo/ gcp record (gci: 233322/6) ref: 0fa2000e from: 0fa2000e
2012-08-19 17:00:24 [ndbd] WARNING -- ACK wo/ gcp record (gci: 233322/6) ref: 0fa20010 from: 0fa20010
2012-08-19 17:00:24 [ndbd] WARNING -- ACK wo/ gcp record (gci: 233322/6) ref: 0fa2000a from: 0fa2000a
2012-08-19 17:00:25 [ndbd] ERROR -- c_gcp_list.seize() failed: gci: 1002110359437319 nodes: 0000000000000000000000000000000000000000000000000000000000015400
2012-08-19 17:00:25 [ndbd] WARNING -- ACK wo/ gcp record (gci: 233322/7) ref: 0fa20010 from: 0fa20010
2012-08-19 17:00:25 [ndbd] WARNING -- ACK wo/ gcp record (gci: 233322/7) ref: 0fa2000e from: 0fa2000e
2012-08-19 17:00:25 [ndbd] WARNING -- ACK wo/ gcp record (gci: 233322/7) ref: 0fa2000a from: 0fa2000a
2012-08-19 17:00:25 [ndbd] ERROR -- c_gcp_list.seize() failed: gci: 1002110359437320 nodes: 0000000000000000000000000000000000000000000000000000000000015400
2012-08-19 17:00:25 [ndbd] WARNING -- ACK wo/ gcp record (gci: 233322/8) ref: 0fa2000e from: 0fa2000e
2012-08-19 17:00:25 [ndbd] WARNING -- ACK wo/ gcp record (gci: 233322/8) ref: 0fa2000a from: 0fa2000a
2012-08-19 17:00:25 [ndbd] WARNING -- ACK wo/ gcp record (gci: 233322/8) ref: 0fa20010 from: 0fa20010
2012-08-19 17:00:25 [ndbd] ERROR -- c_gcp_list.seize() failed: gci: 1002110359437321 nodes: 0000000000000000000000000000000000000000000000000000000000015400
2012-08-19 17:00:25 [ndbd] WARNING -- ACK wo/ gcp record (gci: 233322/9) ref: 0fa20010 from: 0fa20010
2012-08-19 17:00:25 [ndbd] WARNING -- ACK wo/ gcp record (gci: 233322/9) ref: 0fa2000e from: 0fa2000e
2012-08-19 17:00:25 [ndbd] WARNING -- ACK wo/ gcp record (gci: 233322/9) ref: 0fa2000a from: 0fa2000a
2012-08-19 17:00:25 [ndbd] ERROR -- c_gcp_list.seize() failed: gci: 1002114654404608 nodes: 0000000000000000000000000000000000000000000000000000000000015400
2012-08-19 17:01:05 [ndbd] WARNING -- ACK wo/ gcp record (gci: 233322/1) ref: 0fa2000c from: 0fa2000c
2012-08-19 17:01:05 [ndbd] WARNING -- ACK wo/ gcp record (gci: 233322/2) ref: 0fa2000c from: 0fa2000c
2012-08-19 17:01:05 [ndbd] WARNING -- ACK wo/ gcp record (gci: 233322/3) ref: 0fa2000c from: 0fa2000c
2012-08-19 17:01:05 [ndbd] WARNING -- ACK wo/ gcp record (gci: 233322/4) ref: 0fa2000c from: 0fa2000c
2012-08-19 17:01:05 [ndbd] WARNING -- ACK wo/ gcp record (gci: 233322/5) ref: 0fa2000c from: 0fa2000c
2012-08-19 17:01:05 [ndbd] WARNING -- ACK wo/ gcp record (gci: 233322/6) ref: 0fa2000c from: 0fa2000c
2012-08-19 17:01:05 [ndbd] WARNING -- ACK wo/ gcp record (gci: 233322/7) ref: 0fa2000c from: 0fa2000c
2012-08-19 17:01:05 [ndbd] WARNING -- ACK wo/ gcp record (gci: 233322/8) ref: 0fa2000c from: 0fa2000c
2012-08-19 17:01:05 [ndbd] WARNING -- ACK wo/ gcp record (gci: 233322/9) ref: 0fa2000c from: 0fa2000c
2012-08-19 17:01:05 [ndbd] WARNING -- ACK wo/ gcp record (gci: 233323/0) ref: 0fa2000c from: 0fa2000c
-- =============================================================================
【24】 ERROR 1114 (HY000): The table 'card_coin' is full
-- =============================================================================
mysql> insert into bgcarddb.card_coin select * from zbakbgcarddb.card_coin limit 0,300000;
ERROR 1114 (HY000): The table 'card_coin' is full
mysql>
mysql> show warnings;
+---------+------+------------------------------------------------------------------------------------------------+
| Level | Code | Message |
+---------+------+------------------------------------------------------------------------------------------------+
| Warning | 1296 | Got error 902 'Out of memory in Ndb Kernel, ordered index data (increase DataMemory)' from NDB |
| Error | 1114 | The table 'card_coin' is full |
+---------+------+------------------------------------------------------------------------------------------------+
2 rows in set (0.00 sec)
mysql>
導入大量數據的時候或者執行大量的insert或者update操作的時候，經常會出現如下報錯：
ERROR 1297 (HY000) at line 1: Got temporary error 1218 'Send Buffers overloaded in NDB kernel' from NDBCLUSTER
這個報錯的原因有幾個，
第一、是參數SendBufferMemory設置過小，
第二、和mysqld的二進制日志有關系，
第三、和網絡環境有關系
解決辦法：查看網絡環境是否OK ，是否出現雙工模式部隊，是否都是千兆網或者百兆網，加大SendBufferMemory的設置，一般2M就夠了，
設置TotalSendBufferMemory = 256M，最後如果還是出現如此報錯，建議關閉mysql的二進制日志，在my.cnf中注釋掉相關的參數就可以了，比如：
#log-bin=mysql-bin
-- =============================================================================
【25】 修改了config.in之後，data node 啟動，需要 ndbd --initial重新加載之後，
原有的*.dat數據文件無效，啟動不起來，sql節點啟動之後，show tables; 原來的磁盤表都不見了，詭異啊！
-- =============================================================================
1 修改config.inf
2 ndb_mgmd -f /usr/local/mysql/cluster-conf/config.ini --reload 重新加載管理節點
3 ndbd --initial 重新啟動data node
4 service mysqld start
5 use bu; show tables; 原來的表不存在了，丟失了。
看的文檔是說，原有的數據文件加載不了，我想問的是：
1 如果我已經導入了很多數據，這個時候發現自己的配置參數不合理，我要修改配置參數，能不能在線修改並生效。
2 如果1失敗，我能否在重新啟動管理節點之後，啟動data node的時候，能加載進去以前的數據呢？
-- =============================================================================
【25】 批量insert 500W記錄報錯
-- =============================================================================
mysql> insert into bgcarddb.card_package_20120120 select * from zbakbgcarddb.card_package_20120120 ;
ERROR 1297 (HY000): Got temporary error 233 'Out of operation records in transaction coordinator (increase MaxNoOfConcurrentOperations)' from NDBCLUSTER
mysql>
mysql> insert into bgcarddb.card_package select * from zbakbgcarddb.card_package limit 0,5000000;
ERROR 1297 (HY000): Got temporary error 233 'Out of operation records in transaction coordinator (increase MaxNoOfConcurrentOperations)' from NDBCLUSTER
mysql>
-- =============================================================================
【26】 批量insert 500W記錄報錯
-- =============================================================================
2012-08-26 00:37:25 [ndbd] INFO -- findNeighbours from: 4861 old (left: 5 right: 5) new (65535 65535)
2012-08-26 00:37:25 [ndbd] INFO -- Watchdog: User time: 976564 System time: 1189718
2012-08-26 00:37:25 [ndbd] WARNING -- Watchdog: Warning overslept 410 ms, expected 100 ms.
2012-08-26 00:37:25 [ndbd] INFO -- Arbitrator decided to shutdown this node
2012-08-26 00:37:25 [ndbd] INFO -- QMGR (Line: 5975) 0x00000002
2012-08-26 00:37:25 [ndbd] INFO -- Error handler shutting down system
2012-08-26 00:37:25 [ndbd] INFO -- Error handler shutdown completed - exiting
2012-08-26 00:37:38 [ndbd] ALERT -- Node 6: Forced node shutdown completed. Caused by error 2305: 'Node lost connection to other nodes and can not form a unpartitioned cluster, please investigate if there are error(s) on other node(s)(Arbitration error). Temporary error, restart node'.
[ok]：google之，大部分的方案都是 restart data node。
而且一般的ndbd啟動無效，需要ndbd --initial 重新加載才行。
-- =============================================================================
【27】 導入數據錯誤；
-- =============================================================================
CREATE TABLE `pp_order` (
`Id` int(11) NOT NULL AUTO_INCREMENT,
`pid` varchar(255) DEFAULT NULL,
`itemCode` varchar(255) DEFAULT NULL,
`itemLocalCode` varchar(255) DEFAULT NULL,
`stockLocalCode` varchar(255) DEFAULT NULL,
`itemDetailLink` varchar(255) DEFAULT NULL,
`itemName` varchar(255) DEFAULT NULL,
`itemPicLink` varchar(255) DEFAULT NULL,
`itemRetailPrice` varchar(255) DEFAULT NULL,
`itemDealPrice` varchar(255) DEFAULT NULL,
`stockAttr` varchar(255) DEFAULT NULL,
`itemAdjustPrice` varchar(255) DEFAULT NULL,
`itemDealCount` varchar(255) DEFAULT NULL,
`itemDealState` varchar(255) DEFAULT NULL,
`account` varchar(255) DEFAULT NULL,
`itemFlag` varchar(255) DEFAULT NULL,
`refundState` varchar(255) DEFAULT NULL,
`refundStateDesc` varchar(255) DEFAULT NULL,
`availableAction` varchar(255) DEFAULT NULL,
PRIMARY KEY (`Id`),
KEY `dealCode` (`pid`(30))
) TABLESPACE ts_1 STORAGE DISK DEFAULT CHARSET=utf8;
mysql> ERROR 1005 (HY000): Can't create table 'bgapidb.pp_order' (errno: 851)
mysql> show warnings;
+---------+------+---------------------------------------------------------------------------------------------------------------------+
| Level | Code | Message |
+---------+------+---------------------------------------------------------------------------------------------------------------------+
| Warning | 1296 | Got error 851 'Maximum 8052 bytes of FIXED columns supported, use varchar or COLUMN_FORMAT DYNMIC instead' from NDB |
| Error | 1005 | Can't create table 'bgapidb.pp_order' (errno: 851) |
+---------+------+---------------------------------------------------------------------------------------------------------------------+
2 rows in set (0.00 sec)
[OK]，磁盤表的varchar列的占據的總長度不能多於8052B，所以合理設計字段的長度。搞定
mysql> CREATE TABLE bgapidb.`pp_order` (
-> `Id` INT(11) NOT NULL AUTO_INCREMENT,
-> `pid` VARCHAR(30) DEFAULT NULL,
-> `itemCode` VARCHAR(40) DEFAULT NULL,
-> `itemLocalCode` VARCHAR(16) DEFAULT NULL,
-> `stockLocalCode` VARCHAR(16) DEFAULT NULL,
-> `itemDetailLink` VARCHAR(100) DEFAULT NULL,
-> `itemName` VARCHAR(100) DEFAULT NULL,
-> `itemPicLink` VARCHAR(100) DEFAULT NULL,
-> `itemRetailPrice` VARCHAR(10) DEFAULT NULL,
-> `itemDealPrice` VARCHAR(10) DEFAULT NULL,
-> `stockAttr` VARCHAR(120) DEFAULT NULL,
-> `itemAdjustPrice` VARCHAR(10) DEFAULT NULL,
-> `itemDealCount` VARCHAR(10) DEFAULT NULL,
-> `itemDealState` VARCHAR(30) DEFAULT NULL,
-> `account` VARCHAR(10) DEFAULT NULL,
-> `itemFlag` VARCHAR(10) DEFAULT NULL,
-> `refundState` VARCHAR(32) DEFAULT NULL,
-> `refundStateDesc` VARCHAR(32) DEFAULT NULL,
-> `availableAction` VARCHAR(60) DEFAULT NULL,
-> PRIMARY KEY (`Id`),
-> KEY `dealCode` (`pid`(30))
-> ) TABLESPACE ts_1 STORAGE DISK ENGINE=NDBCLUSTER DEFAULT CHARSET=utf8;
Query OK, 0 rows affected (0.24 sec)
-- =============================================================================
【28】 Got temporary error 4010
-- =============================================================================
mysql> insert into bgapidb.top_deliverysend_queue select * from zbakapi.top_deliverysend_queue limit 0,200000;
ERROR 1297 (HY000): Got temporary error 4010 'Node failure caused abort of transaction' from NDBCLUSTER
【ok】內存不足，清空掉一些無用的大表，釋放內存從60%降到30%
ndb_mgm> all report memory;
Node 4: Data usage is 30%(39215 32K pages of total 128000)
Node 4: Index usage is 16%(18575 8K pages of total 115232)
Node 5: Data usage is 30%(39196 32K pages of total 128000)
Node 5: Index usage is 16%(18575 8K pages of total 115232)
Node 6: Data usage is 30%(39489 32K pages of total 128000)
Node 6: Index usage is 16%(18554 8K pages of total 115232)
CREATE TABLE `top_trade` (
`Id` int(11) NOT NULL AUTO_INCREMENT,
`seller_nick` varchar(255) DEFAULT NULL,
`buyer_nick` varchar(255) DEFAULT NULL,
`title` varchar(255) DEFAULT NULL,
`type` varchar(255) DEFAULT NULL,
`created` varchar(20) DEFAULT NULL,
`iid` varchar(60) DEFAULT NULL,
`price` decimal(10,2) DEFAULT NULL,
`pic_path` varchar(255) DEFAULT NULL,
`num` int(5) DEFAULT NULL,
`tid` varchar(20) DEFAULT NULL,
`buyer_message` varchar(255) DEFAULT NULL,
`sid` varchar(255) DEFAULT NULL,
`shipping_type` varchar(50) DEFAULT NULL,
`alipay_no` varchar(60) DEFAULT NULL,
`payment` decimal(10,2) DEFAULT NULL,
`discount_fee` decimal(10,2) DEFAULT NULL,
`adjust_fee` decimal(10,2) DEFAULT NULL,
`snapshot_url` varchar(255) DEFAULT NULL,
`snapshot` varchar(255) DEFAULT NULL,
`status` varchar(60) DEFAULT NULL,
`seller_rate` varchar(20) DEFAULT NULL,
`buyer_rate` varchar(20) DEFAULT NULL,
`buyer_memo` varchar(255) DEFAULT NULL,
`seller_memo` varchar(255) DEFAULT NULL,
`pay_time` varchar(20) DEFAULT NULL,
`end_time` varchar(20) DEFAULT NULL,
`modified` varchar(20) DEFAULT NULL,
`buyer_obtain_point_fee` int(10) DEFAULT NULL,
`point_fee` int(10) DEFAULT NULL,
`real_point_fee` int(10) DEFAULT NULL,
`total_fee` decimal(10,2) DEFAULT NULL,
`post_fee` decimal(10,2) DEFAULT NULL,
`buyer_alipay_no` varchar(100) DEFAULT NULL,
`receiver_name` varchar(100) DEFAULT NULL,
`receiver_state` varchar(60) DEFAULT NULL,
`receiver_city` varchar(60) DEFAULT NULL,
`receiver_district` varchar(80) DEFAULT NULL,
`receiver_address` varchar(255) DEFAULT NULL,
`receiver_zip` varchar(10) DEFAULT NULL,
`receiver_mobile` varchar(100) DEFAULT NULL,
`receiver_phone` varchar(100) DEFAULT NULL,
`consign_time` varchar(20) DEFAULT NULL,
`buyer_email` varchar(255) DEFAULT NULL,
`commission_fee` decimal(10,2) DEFAULT NULL,
`seller_alipay_no` varchar(100) DEFAULT NULL,
`seller_mobile` varchar(50) DEFAULT NULL,
`seller_phone` varchar(50) DEFAULT NULL,
`seller_name` varchar(30) DEFAULT NULL,
`seller_email` varchar(60) DEFAULT NULL,
`available_confirm_fee` decimal(10,2) DEFAULT NULL,
`has_post_fee` varchar(20) DEFAULT NULL,
`received_payment` decimal(10,2) DEFAULT NULL,
`cod_fee` decimal(10,2) DEFAULT NULL,
`timeout_action_time` varchar(20) DEFAULT NULL,
`is_3d` varchar(20) DEFAULT NULL,
`is_lgtype` tinyint(3) DEFAULT NULL COMMENT '是否需要物流寶發貨標識',
`is_brand_sale` tinyint(3) DEFAULT NULL COMMENT '是否品牌特賣訂單',
`is_force_wlb` tinyint(3) DEFAULT NULL COMMENT '是否強制使用物流寶發貨',
`outer_order_sn` varchar(255) DEFAULT NULL,
`is_change` tinyint(3) NOT NULL DEFAULT '1',
`is_os` tinyint(3) NOT NULL DEFAULT '0',
`channel_code` varchar(20) NOT NULL DEFAULT '',
`channel_name` varchar(255) DEFAULT NULL,
PRIMARY KEY (`Id`),
KEY `tid` (`tid`),
KEY `change_chl_status` (`channel_code`(10),`status`(24),`is_change`),
KEY `is_os` (`is_os`),
KEY `created` (`created`)
) TABLESPACE ts_1 STORAGE DISK ENGINE=ndbcluster DEFAULT CHARSET=utf8;
ERROR 1118 (42000): Row size too large. The maximum row size for the used table type, not counting BLOBs, is 14000. You have to change some columns to TEXT or BLOBs
-- =============================================================================
【29】 Got temporary error 899 'Rowid already allocated' from NDB
-- =============================================================================
mysql> create table bglogdb.t1(id int, n varchar(30)) TABLESPACE ts_1 STORAGE DISK ENGINE=ndbcluster;
ERROR 1005 (HY000): Can't create table 'bglogdb.t1' (errno: 899)
mysql> show warnings;
+---------+------+------------------------------------------------------------+
| Level | Code | Message |
+---------+------+------------------------------------------------------------+
| Warning | 1297 | Got temporary error 899 'Rowid already allocated' from NDB |
| Error | 1005 | Can't create table 'bglogdb.t1' (errno: 899) |
+---------+------+------------------------------------------------------------+
2 rows in set (0.00 sec)
【ok】，google之，沒有找到辦法，無奈從新啟動cluster系統，建表成功。
-- =============================================================================
【30】 Got temporary error 899 'Rowid already allocated' from NDB
-- =============================================================================
CREATE TABLE bglogdb.`nc_api_logs` (
`id` int(11) NOT NULL AUTO_INCREMENT COMMENT '主鍵ID',
`ymd` datetime NOT NULL COMMENT '請求日期',
`request_url` varchar(200) NOT NULL COMMENT '請求URL',
`post_data` text COMMENT '提交參數',
`return_data` text COMMENT '返回參數',
`ipaddr` varchar(20) DEFAULT NULL COMMENT '發送請求IP',
`status` char(3) DEFAULT NULL COMMENT '請求狀態:0 已請求 返回異常 1 已請求 正常返回 ',
`created` datetime DEFAULT NULL COMMENT '該記錄創建時間',
`modified` datetime DEFAULT NULL COMMENT '記錄修改時間',
`api_type` varchar(55) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=ndbcluster DEFAULT CHARSET=utf8;
ERROR 1005 (HY000): Can't create table 'bglogdb.nc_api_logs' (errno: 708)
mysql> show warnings;
+---------+------+------------------------------------------------------------------------------------------+
| Level | Code | Message |
+---------+------+------------------------------------------------------------------------------------------+
| Warning | 1296 | Got error 708 'No more attribute metadata records (increase MaxNoOfAttributes)' from NDB |
| Error | 1005 | Can't create table 'bglogdb.nc_api_logs' (errno: 708) |
+---------+------+------------------------------------------------------------------------------------------+
2 rows in set (0.00 sec)
【ok】，將MaxNoOfAttributes值增加到24760，搞定。
-- ==================================================================================================================
【31】 ERROR : Got temporary error 1234 'REDO log files overloaded (increase disk hardware)' from NDBCLUSTER
-- ==================================================================================================================
[root@banggo ~]# time mysqlslap --engine=ndbcluster --auto-generate-sql-write-number=100000 --auto-generate-sql-guid-primary --concurrency=200,400,600 --number-of-queries=1000000 --iterations=2 --number-char-cols=10 --number-int-cols=10 --auto-generate-sql --create-schema=ndb –-commint=1000 --auto-generate-sql-load-type=write
mysqlslap: Cannot run query INSERT INTO t1 VALUES (uuid(),412380163,1002100976,271523606,1458696239,1603135659,696200156,1997806484,42367420,1574962246,
1628033484,'5QF0PnnE7cfW1qKl2J245d0XcXRDB6THOlHqOwwXZc1aQDKLOEPTrHsJgCOsIu1aXAGv6DQXpJ67PISpOlJ723QbFrRPlK08WG3STL0IYyIxGmnYOyfI15t9oKaPYa',
'Z2jcNCpNNMLhJTJy1jog2idvrQ8PrgsPbbt
ynbD2xaj833zwmncgTDcKXkmNGWgr5Zpk82nxSM6NH5JwkncEtCaOoCf5iJwfteOtY3r1PpGjmSfWF7k0KBP0F45yO2','T9Ytu4olxEQEpCiKjpRRqfW7gm3lcJf5SBrF8f05tRBaXtuEbEaPt4OZiRJI
nqO727M2mF7YaJ654tCX7mw0iCQHgdbJRrQLyhNdwN9Mi7ResXAz9yssBI1jnc2EKK','SAtrnhEo3ooNJguTY4JOoMap5LHzRdCvo5fRnlGiQ45lcs68wPYcoyPlBilfCpQ0uxJZjR89X5m8xs8lHhLxX
ejjLKEP9YHvWcL54T6RasROkc9t9Nrg1QFEeLffuw','QGZnDT8JwbBOrns0g1NNZHhpXJAByw7P4Y4H15cygfEXQ6NzzfEYwwDJ7GW6CWNHuJQwO3mwasJtqbkp6yL2uRD2jk0v6F40QXojQQX0Irl8IT
xOSBIEeX8L788DF5','6acmH2DouXXgYp9T5TYNk2nsFutO00LyaxkrrxgdL3HttJEqofEOh1Hxn2Dn2addNMK6BsjYwtPhCYXsBdHkWaZISmxWCbzRpKZ04HZ0AqZEE56PiNADpbNHMKgQnf',
'ucrJdwc2oekMszRzG1EI64uLD8itaLPKyGgRC8Ttf5FX4aMDRTXQPPnePTOsjpCh5k0AlL3OQAnNk2c3XzTwHHQieqaLfnulNun8FiOas4xS6PNTOtPh2jOgayu5dg',
'itbTtiBIGvE5FKWfpvY6Saps6FjsLCZ3x0Zrac1jOFofbBIGyiMQG3BFJmzgqOjoGGXrlOQ9fEo6H7M7pcQyfT5y7wexLMddW8K7YmG3tx9AwNAdrcRN5YLCLs1i6f',
'BSfYc4As73h96dOGFGsifp7stSku2iZDB5dn9oXGr5Pxiq5xyxgTefDX7yQ9YrMjwsM5gmMXrfLZLQNBGvgmauIhkci8RwkGo6LKkbkR75tSNGYX34IdySAa4kjXgR',
'nLP1h2SPTbMEwmWS9PYR2Nn4prgOFLrui9vF3OhYr3nnh9FrbgATvyQkh69wJsjt1e9wuqTel8PR9ZIcDKy8IGksEtoamXRf2ScoAxQwxkFyHPRMlPMwZ6onsSDesh')
ERROR : Got temporary error 1234 'REDO log files overloaded (increase disk hardware)' from NDBCLUSTER
【ok】google很久，增加了那2個值，但是啟動起來還是報很多錯誤，後來想到一個外招，換個庫名字，--create-schema=ndb12，搞定。
-- ==================================================================================================================
【32】 ERROR 1114 (HY000): The table '' is full
-- ==================================================================================================================
mysql> replace into bgapidb.yi_refund select * from zbakapi.yi_refund;
ERROR 1114 (HY000): The table '' is full
mysql>
mysql>
mysql>
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" and TABLESPACE_NAME='ts_1' order by UPDATE_TIME desc;
+-----------------+-------------+----------------+------------+----------------+
| TABLESPACE_NAME | FILE_NAME | TOTAL_MB | FREE_MB | EXTRA |
+-----------------+-------------+----------------+------------+----------------+
| ts_1 | data_11.dat | 20480.00000000 | 0.00000000 | CLUSTER_NODE=4 |
+-----------------+-------------+----------------+------------+----------------+
1 row in set (0.01 sec)
Dbspj::execSTTOR() inst:0 phase=1
2012-12-06 10:21:39 [ndbd] INFO -- Start phase 1 completed
2012-12-06 10:26:57 [ndbd] INFO -- Watchdog: User time: 3406 System time: 15757
2012-12-06 10:26:57 [ndbd] WARNING -- Watchdog: Warning overslept 213 ms, expected 100 ms.
2012-12-06 10:30:51 [ndbd] INFO -- Watchdog: User time: 5174 System time: 19204
2012-12-06 10:30:51 [ndbd] WARNING -- Watchdog: Warning overslept 219 ms, expected 100 ms.
2012-12-06 10:32:42 [ndbd] INFO -- Watchdog: User time: 5997 System time: 20911
2012-12-06 10:32:42 [ndbd] WARNING -- Watchdog: Warning overslept 208 ms, expected 100 ms.
【ok】網絡狀況不是很好，修改參數值：
heartbeatintervaldbdb=10000 heartbeatintervaldbapi=10000
#Redo log
FragmentLogFileSize=512M
InitFragmentLogFiles=SPARSE
NoOfFragmentLogFiles=256
RedoBuffer=256M
其中修改FragmentLogFileSize值需要 ndbd --initial 啟動
Error data: Invalid file size for redo logfile, size only changable with --initial
Error object: DBLQH (Line: 15200) 0x00000002
