Node failure caused abort of transaction after trying to import a "large" mysqldump file
Posted by: Alexander Economou
Date: September 01, 2010 02:55AM
Date: September 01, 2010 02:55AM
Greetings!
I am trying to import a ~500mbyte sql dump file to a new installation of mysql cluster (7.1.5-1)
The dump file was taken from another mysql (non cluster) server with the following command :
mysqldump -udata -p www_database_com --opt --lock-all-tables > database.sql
I changed the engine from myisam to ndbcluster in all the tables and then i created the database from the mysql api service.
When i am trying to import the dump file to the mysql cluster from node 7 i get the following error after some minutes:
mysql -uroot -p www_database_com < database.sql_ndb
Enter password:
ERROR 1297 (HY000) at line 941: Got temporary error 4010 'Node failure caused abort of transaction' from NDBCLUSTER
I am using 2 servers for ndb_mgmd and mysqld and another 2 for ndbd
(total 4 servers)
I have a private 100mbit network dedicated for mysql cluster
All the servers are of the same configuration
16G ram
3x500G sata disks in RAID-5 configuration
2xQuad-Core AMD Opteron(tm) Processor 2344 HE
my config.ini
[TCP DEFAULT]
SendBufferMemory=4M
ReceiveBufferMemory=4M
[NDB_MGMD DEFAULT]
PortNumber=1186
Datadir=/var/lib/mysql-cluster/
[NDB_MGMD]
Id=1
Hostname=172.18.77.1
LogDestination=FILE:filename=ndb_1_cluster.log,maxfiles=6
ArbitrationRank=1
[NDB_MGMD]
Id=2
Hostname=172.18.77.2
LogDestination=FILE:filename=ndb_2_cluster.log,maxfiles=6
ArbitrationRank=1
[NDBD DEFAULT]
NoOfReplicas=2
Datadir=/var/lib/mysql-cluster/
FileSystemPathDD=/var/lib/mysql-cluster/
#FileSystemPathUndoFiles=/var/lib/mysql-cluster/
#FileSystemPathDataFiles=/var/lib/mysql-cluster/
DataMemory=12139M
IndexMemory=1518M
LockPagesInMainMemory=1
MaxNoOfConcurrentOperations=100000
StringMemory=25
MaxNoOfTables=8192
MaxNoOfOrderedIndexes=2048
MaxNoOfUniqueHashIndexes=512
MaxNoOfAttributes=24576
MaxNoOfTriggers=14336
DiskCheckpointSpeedInRestart=10M
FragmentLogFileSize=256M
InitFragmentLogFiles=SPARSE
NoOfFragmentLogFiles=72
RedoBuffer=48M
TimeBetweenLocalCheckpoints=20
TimeBetweenGlobalCheckpoints=1000
TimeBetweenEpochs=100
MemReportFrequency=30
BackupReportFrequency=10
### Params for setting logging
LogLevelStartup=15
LogLevelShutdown=15
LogLevelCheckpoint=8
LogLevelNodeRestart=15
### Params for increasing Disk throughput
BackupMaxWriteSize=1M
BackupDataBufferSize=16M
BackupLogBufferSize=4M
BackupMemory=20M
#Reports indicates that odirect=1 can cause io errors (os err code 5) on some systems. You must test.
#ODirect=1
### Watchdog
TimeBetweenWatchdogCheckInitial=80000
### TransactionInactiveTimeout - should be enabled in Production
TransactionInactiveTimeout=80000
# Added this cause of ERROR 1297 (HY000) at line 612: Got temporary error 4010 'Node failure caused abort of transaction' from NDBCLUSTER
TransactionDeadLockDetectionTimeOut=80000
### CGE 6.3 - REALTIME EXTENSIONS
#RealTimeScheduler=1
#SchedulerExecutionTimer=80
#SchedulerSpinTimer=40
### DISK DATA
SharedGlobalMemory=20M
DiskPageBufferMemory=64M
### Multithreading
#MaxNoOfExecutionThreads=4
### Increasing the LongMessageBuffer b/c of a bug (20090903)
LongMessageBuffer=8M
BatchSizePerLocalScan=512
[NDBD]
Id=3
Hostname=172.18.77.3
[NDBD]
Id=4
Hostname=172.18.77.4
## BELOW ARE TWO (INACTIVE) SLOTS FOR DATA NODES TO ALLOW FOR GROWTH
#[NDBD]
#Id=5
#Hostname=
#[NDBD]
#Id=6
#Hostname=
[MYSQLD DEFAULT]
BatchSize=512
#BatchByteSize=2048K
#MaxScanBatchSize=2048K
[MYSQLD]
Id=7
Hostname=172.18.77.1
[MYSQLD]
Id=8
Hostname=172.18.77.1
[MYSQLD]
Id=9
Hostname=172.18.77.2
[MYSQLD]
Id=10
Hostname=172.18.77.2
### SLOTS FOR CMON (one for each ndb_mgmd)
[MYSQLD]
Hostname=172.18.77.1
[MYSQLD]
Hostname=172.18.77.2
### SLOTS (one for each ndb_mgmd) FOR HELPER APPLICATIONS SUCH AS ndb_show_tables etc
[MYSQLD]
Hostname=172.18.77.1
[MYSQLD]
Hostname=172.18.77.2
My my.cnf
[mysqld]
user=root
datadir=/var/lib/mysql-cluster/
pid-file=mysqld.pid
socket=/var/lib/mysql-cluster/mysql.sock
ndbcluster
ndb-connectstring="172.18.77.1:1186;172.18.77.2:1186"
#These are commented out for troubleshooting reasons
#ndb-cluster-connection-pool=1
#ndb-force-send=1
#ndb-use-exact-count=0
#ndb-extra-logging=1
#ndb-autoincrement-prefetch-sz=256
#engine-condition-pushdown=1
log-err=error.log
key_buffer = 256M
#max_allowed_packet = 16M
max_allowed_packet = 4M
sort_buffer_size = 512K
read_buffer_size = 256K
read_rnd_buffer_size = 512K
#thread_cache_size=1024
#myisam_sort_buffer_size = 8M
myisam_sort_buffer_size = 2M
memlock
sysdate_is_now
max-connections=200
thread-cache-size=128
query-cache-type = 0
query-cache-size = 0
table-open_cache=1024
table-cache=512
lower-case-table-names=0
[MYSQL]
socket=/var/lib/mysql-cluster/mysql.sock
[client]
socket=/var/lib/mysql-cluster/mysql.sock
My my.cnf in data nodes
[mysqld]
ndbcluster
ndb-connectstring=172.18.77.1,172.18.77.2
[mysql-cluster]
ndb-connectstring=172.18.77.1,172.18.77.2
About the logs now
------------------START------------ndb_3_out.log---------------------------------
2010-09-01 09:27:48 [ndbd] INFO -- Start phase 5 completed
2010-09-01 09:27:48 [ndbd] INFO -- Start phase 6 completed
m_active_buckets.set(0)
2010-09-01 09:27:48 [ndbd] INFO -- Start phase 7 completed
2010-09-01 09:27:48 [ndbd] INFO -- Start phase 8 completed
2010-09-01 09:27:48 [ndbd] INFO -- Start phase 9 completed
2010-09-01 09:27:48 [ndbd] INFO -- Start phase 100 completed
2010-09-01 09:27:48 [ndbd] INFO -- Start phase 101 completed
2010-09-01 09:27:48 [ndbd] INFO -- Node started
alloc_chunk(312334 16) -
tab: 51 frag: 1 increasing maxGciInLcp from 724 to 731
tab: 51 frag: 0 increasing maxGciInLcp from 743 to 752
tab: 51 frag: 1 increasing maxGciInLcp from 743 to 752
2010-09-01 09:47:56 [ndbd] ERROR -- c_gcp_list.seize() failed: gci: 4200478015496 nodes: 0000000000000000000000000000000000000000000000000000000000000280
2010-09-01 09:47:56 [ndbd] WARNING -- ACK wo/ gcp record (gci: 978/8) ref: 0fa20009 from: 00030009
2010-09-01 09:47:56 [ndbd] ERROR -- c_gcp_list.seize() failed: gci: 4200478015497 nodes: 0000000000000000000000000000000000000000000000000000000000000280
2010-09-01 09:47:56 [ndbd] WARNING -- ACK wo/ gcp record (gci: 978/9) ref: 0fa20009 from: 00030009
WARNING: timerHandlingLab now: 65637471 sent: 65637421 diff: 50
2010-09-01 09:47:56 [ndbd] ERROR -- c_gcp_list.seize() failed: gci: 4204772982784 nodes: 0000000000000000000000000000000000000000000000000000000000000280
2010-09-01 09:47:56 [ndbd] WARNING -- ACK wo/ gcp record (gci: 979/0) ref: 0fa20009 from: 00030009
2010-09-01 09:47:56 [ndbd] ERROR -- c_gcp_list.seize() failed: gci: 4204772982785 nodes: 0000000000000000000000000000000000000000000000000000000000000280
2010-09-01 09:47:56 [ndbd] WARNING -- ACK wo/ gcp record (gci: 979/1) ref: 0fa20009 from: 00030009
2010-09-01 09:47:56 [ndbd] ERROR -- c_gcp_list.seize() failed: gci: 4204772982786 nodes: 0000000000000000000000000000000000000000000000000000000000000280
2010-09-01 09:47:56 [ndbd] WARNING -- ACK wo/ gcp record (gci: 979/2) ref: 0fa20009 from: 00030009
WARNING: timerHandlingLab now: 67357839 sent: 67357789 diff: 50
--------------------END------------ndb_3_out.log---------------------------------
------------------START------------ndb_4_out.log---------------------------------
2010-09-01 09:26:11 [ndbd] INFO -- Start phase 6 completed
m_active_buckets.set(1)
2010-09-01 09:26:11 [ndbd] INFO -- Start phase 7 completed
2010-09-01 09:26:11 [ndbd] INFO -- Start phase 8 completed
2010-09-01 09:26:11 [ndbd] INFO -- Start phase 9 completed
2010-09-01 09:26:11 [ndbd] INFO -- Start phase 100 completed
2010-09-01 09:26:11 [ndbd] INFO -- Start phase 101 completed
2010-09-01 09:26:11 [ndbd] INFO -- Node started
alloc_chunk(312334 16) -
WARNING: timerHandlingLab now: 65225261 sent: 65225208 diff: 53
tab: 51 frag: 0 increasing maxGciInLcp from 743 to 752
2010-09-01 09:46:18 [ndbd] ERROR -- c_gcp_list.seize() failed: gci: 4200478015496 nodes: 0000000000000000000000000000000000000000000000000000000000000280
2010-09-01 09:46:18 [ndbd] WARNING -- ACK wo/ gcp record (gci: 978/8) ref: 0fa20009 from: 00040009
2010-09-01 09:46:18 [ndbd] ERROR -- c_gcp_list.seize() failed: gci: 4200478015497 nodes: 0000000000000000000000000000000000000000000000000000000000000280
2010-09-01 09:46:18 [ndbd] WARNING -- ACK wo/ gcp record (gci: 978/9) ref: 0fa20009 from: 00040009
2010-09-01 09:46:18 [ndbd] ERROR -- c_gcp_list.seize() failed: gci: 4204772982784 nodes: 0000000000000000000000000000000000000000000000000000000000000280
2010-09-01 09:46:18 [ndbd] WARNING -- ACK wo/ gcp record (gci: 979/0) ref: 0fa20009 from: 00040009
2010-09-01 09:46:18 [ndbd] ERROR -- c_gcp_list.seize() failed: gci: 4204772982785 nodes: 0000000000000000000000000000000000000000000000000000000000000280
2010-09-01 09:46:18 [ndbd] WARNING -- ACK wo/ gcp record (gci: 979/1) ref: 0fa20009 from: 00040009
2010-09-01 09:46:18 [ndbd] ERROR -- c_gcp_list.seize() failed: gci: 4204772982786 nodes: 0000000000000000000000000000000000000000000000000000000000000280
2010-09-01 09:46:18 [ndbd] WARNING -- ACK wo/ gcp record (gci: 979/2) ref: 0fa20009 from: 00040009
--------------------END------------ndb_4_out.log---------------------------------
-----------------------------MySQL-webmgm1.database.com.err----------------------
100901 09:32:06 mysqld_safe Starting mysqld daemon with databases from /var/lib/mysql-cluster/
100901 9:32:06 [Note] Plugin 'FEDERATED' is disabled.
100901 9:32:06 InnoDB: Started; log sequence number 0 44233
100901 9:32:06 [Note] NDB: NodeID is 7, management server '172.18.77.1:1186'
100901 9:32:07 [Note] NDB[0]: NodeID: 7, all storage nodes connected
100901 9:32:07 [Warning] NDB: server id set to zero will cause any other mysqld with bin log to log with wrong server id
100901 9:32:07 [Note] Starting Cluster Binlog Thread
100901 9:32:07 [Note] Event Scheduler: Loaded 0 events
100901 9:32:07 [Note] NDB Binlog: DISCOVER TABLE Event: REPL$mysql/ndb_schema
100901 9:32:07 [Note] NDB Binlog: logging ./mysql/ndb_schema (UPDATED,USE_WRITE)
100901 9:32:07 [Note] NDB Binlog: DISCOVER TABLE Event: REPL$mysql/ndb_apply_status
100901 9:32:07 [Note] NDB Binlog: logging ./mysql/ndb_apply_status (UPDATED,USE_WRITE)
2010-09-01 09:32:08 [NdbApi] INFO -- Flushing incomplete GCI:s < 190/2
2010-09-01 09:32:08 [NdbApi] INFO -- Flushing incomplete GCI:s < 190/2
100901 9:32:08 [Note] NDB Binlog: starting log at epoch 190/2
100901 9:32:08 [Note] NDB Binlog: ndb tables writable
100901 9:32:08 [Note] /usr/sbin/mysqld: ready for connections.
Version: '5.1.47-ndb-7.1.5-cluster-gpl' socket: '/var/lib/mysql-cluster/mysql.sock' port: 3306 MySQL Cluster Server (GPL)
100901 9:32:08 [Note] NDB Binlog: Node: 3, subscribe from node 9, Subscriber bitmask 0200
100901 9:32:08 [Note] NDB Binlog: Node: 4, subscribe from node 9, Subscriber bitmask 0200
100901 9:34:38 [Note] NDB Binlog: CREATE TABLE Event: REPL$www_database_com/404-log
100901 9:34:59 [Note] NDB Binlog: CREATE TABLE Event: REPL$www_database_com/alert_direct_msg
100901 9:35:03 [Note] NDB Binlog: CREATE TABLE Event: REPL$www_database_com/alert_replies
100901 9:35:06 [Note] NDB Binlog: CREATE TABLE Event: REPL$www_database_com/alerts-global-read-mem
100901 9:35:08 [Note] NDB Binlog: CREATE TABLE Event: REPL$www_database_com/alerts
100901 9:35:09 [Note] NDB Binlog: CREATE TABLE Event: REPL$www_database_com/applications-settings
100901 9:35:12 [Note] NDB Binlog: CREATE TABLE Event: REPL$www_database_com/applications
100901 9:35:14 [Note] NDB Binlog: CREATE TABLE Event: REPL$www_database_com/basic_user_index
100901 9:41:57 [Note] NDB Binlog: CREATE TABLE Event: REPL$www_database_com/basic_user_info-xxx
100901 9:41:59 [Note] NDB Binlog: CREATE TABLE Event: REPL$www_database_com/favorited-tweets
100901 9:42:03 [Note] NDB Binlog: CREATE TABLE Event: REPL$www_database_com/favorites-tags
100901 9:42:05 [Note] NDB Binlog: CREATE TABLE Event: REPL$www_database_com/favorites
100901 9:42:09 [Note] NDB Binlog: CREATE TABLE Event: REPL$www_database_com/followers-cache
100901 9:44:19 [Note] NDB Binlog: CREATE TABLE Event: REPL$www_database_com/followers
100901 9:44:20 [Note] NDB Binlog: CREATE TABLE Event: REPL$www_database_com/followfriday_reminder
100901 9:44:25 [Note] NDB Binlog: CREATE TABLE Event: REPL$www_database_com/group_categories
100901 9:44:26 [Note] NDB Binlog: CREATE TABLE Event: REPL$www_database_com/groups-autopost-cache
100901 9:44:29 [Note] NDB Binlog: CREATE TABLE Event: REPL$www_database_com/groups-autopost-keywords
100901 9:44:31 [Note] NDB Binlog: CREATE TABLE Event: REPL$www_database_com/groups-members-cache
100901 9:44:33 [Note] NDB Binlog: CREATE TABLE Event: REPL$www_database_com/groups-members
100901 9:44:35 [Note] NDB Binlog: CREATE TABLE Event: REPL$www_database_com/groups-tags-follow
100901 9:44:36 [Note] NDB Binlog: CREATE TABLE Event: REPL$www_database_com/groups-timeline-index
100901 9:44:43 [Note] NDB Binlog: CREATE TABLE Event: REPL$www_database_com/groups-timeline-xxx
100901 9:44:46 [Note] NDB Binlog: CREATE TABLE Event: REPL$www_database_com/groups
100901 9:44:48 [Note] NDB Binlog: CREATE TABLE Event: REPL$www_database_com/groups_tags
100901 9:44:50 [Note] NDB Binlog: CREATE TABLE Event: REPL$www_database_com/ignore_users
100901 9:44:51 [Note] NDB Binlog: CREATE TABLE Event: REPL$www_database_com/invitation-requests
100901 9:44:53 [Note] NDB Binlog: CREATE TABLE Event: REPL$www_database_com/invitations
100901 9:44:54 [Note] NDB Binlog: CREATE TABLE Event:
REPL$www_database_com/log-posts-per-day
100901 9:44:56 [Note] NDB Binlog: CREATE TABLE Event:
REPL$www_database_com/log-registered-online-hourly
100901 9:45:05 [Note] NDB Binlog: CREATE TABLE Event:
REPL$www_database_com/log-registered-online-tmp
100901 9:45:07 [Note] NDB Binlog: CREATE TABLE Event:
REPL$www_database_com/log-registered-online
100901 9:45:09 [Note] NDB Binlog: CREATE TABLE Event:
REPL$www_database_com/log-users-online
100901 9:46:55 [Note] NDB Binlog: CREATE TABLE Event:
REPL$www_database_com/mem-tweet-replies
100901 9:49:02 [Note] NDB Binlog: Node: 3, down, Subscriber bitmask 00
100901 9:49:02 [Note] NDB Binlog: Node: 4, down, Subscriber bitmask 00
100901 9:49:02 [Note] NDB Binlog: cluster failure for ./mysql/ndb_schema at epoch 972/0.
100901 9:49:02 [Note] NDB Binlog: cluster failure for ./mysql/ndb_apply_status at epoch 972/0.
100901 9:49:02 [Note] Restarting Cluster Binlog
100901 9:49:33 [Note] Restarting Cluster Binlog
100901 9:49:52 [Note] NDB Binlog: DISCOVER TABLE Event: REPL$mysql/ndb_schema
100901 9:49:52 [Note] NDB Binlog: logging ./mysql/ndb_schema (UPDATED,USE_WRITE)
100901 9:49:52 [Note] NDB Binlog: DISCOVER TABLE Event: REPL$mysql/ndb_apply_status
100901 9:49:52 [Note] NDB Binlog: logging ./mysql/ndb_apply_status (UPDATED,USE_WRITE)
2010-09-01 09:49:52 [NdbApi] INFO -- Flushing incomplete GCI:s < 1025/0
2010-09-01 09:49:52 [NdbApi] INFO -- Flushing incomplete GCI:s < 1025/0
100901 9:49:52 [Note] NDB Binlog: starting log at epoch 1025/0
100901 9:49:52 [Note] NDB Binlog: ndb tables writable
100901 9:49:52 [Note] NDB Binlog: Node: 3, subscribe from node 9, Subscriber bitmask 0200
100901 9:49:52 [Note] NDB Binlog: Node: 4, subscribe from node 9, Subscriber bitmask 0200
--------------------------------END-----------------------------------------------
-------------------START-------ndb_2_cluster.log----------------------------------
2010-09-01 09:48:54 [MgmtSrvr] INFO -- Node 3: Local checkpoint 20 completed
2010-09-01 09:48:55 [MgmtSrvr] INFO -- Node 3: Local checkpoint 21 started. Keep GCI = 933 oldest restorable GCI
= 961
2010-09-01 09:49:02 [MgmtSrvr] WARNING -- Node 3: Node 1 missed heartbeat 2
2010-09-01 09:49:03 [MgmtSrvr] WARNING -- Node 4: Node 1 missed heartbeat 2
2010-09-01 09:49:03 [MgmtSrvr] WARNING -- Node 4: Node 7 missed heartbeat 2
2010-09-01 09:49:03 [MgmtSrvr] WARNING -- Node 3: Node 1 missed heartbeat 3
2010-09-01 09:49:04 [MgmtSrvr] ALERT -- Node 3: Node 1 Disconnected
2010-09-01 09:49:04 [MgmtSrvr] INFO -- Node 3: Communication to Node 1 closed
2010-09-01 09:49:04 [MgmtSrvr] INFO -- Node 4: Communication to Node 1 closed
2010-09-01 09:49:04 [MgmtSrvr] ALERT -- Node 4: Node 1 Disconnected
2010-09-01 09:49:04 [MgmtSrvr] WARNING -- Node 4: Node 7 missed heartbeat 3
2010-09-01 09:49:05 [MgmtSrvr] INFO -- Node 4: Communication to Node 1 opened
2010-09-01 09:49:05 [MgmtSrvr] INFO -- Node 3: Communication to Node 1 opened
2010-09-01 09:49:06 [MgmtSrvr] INFO -- Node 3: Communication to Node 7 closed
2010-09-01 09:49:06 [MgmtSrvr] WARNING -- Node 4: Node 7 missed heartbeat 4
2010-09-01 09:49:06 [MgmtSrvr] ALERT -- Node 4: Node 7 declared dead due to missed heartbeat
2010-09-01 09:49:06 [MgmtSrvr] INFO -- Node 4: Communication to Node 7 closed
2010-09-01 09:49:06 [MgmtSrvr] INFO -- Node 4: Data usage is 0%(2190 32K pages of total 388448)
2010-09-01 09:49:06 [MgmtSrvr] INFO -- Node 4: Index usage is 0%(1511 8K pages of total 194336)
2010-09-01 09:49:16 [MgmtSrvr] INFO -- Node 3: Data usage is 0%(2148 32K pages of total 388448)
2010-09-01 09:49:16 [MgmtSrvr] INFO -- Node 3: Index usage is 0%(1511 8K pages of total 194336)
2010-09-01 09:49:35 [MgmtSrvr] INFO -- Node 3: Local checkpoint 21 completed
2010-09-01 09:49:36 [MgmtSrvr] INFO -- Node 4: Data usage is 0%(2148 32K pages of total 388448)
2010-09-01 09:49:36 [MgmtSrvr] INFO -- Node 4: Index usage is 0%(1511 8K pages of total 194336)
2010-09-01 09:49:46 [MgmtSrvr] INFO -- Node 3: Data usage is 0%(2148 32K pages of total 388448)
2010-09-01 09:49:46 [MgmtSrvr] INFO -- Node 3: Index usage is 0%(1511 8K pages of total 194336)
2010-09-01 09:49:50 [MgmtSrvr] INFO -- Node 4: Node 1 Connected
2010-09-01 09:49:50 [MgmtSrvr] ALERT -- Node 4: Node 7 Disconnected
2010-09-01 09:49:50 [MgmtSrvr] INFO -- Node 4: Node 1: API mysql-5.1.47 ndb-7.1.5
2010-09-01 09:49:51 [MgmtSrvr] INFO -- Node 3: Node 1 Connected
2010-09-01 09:49:51 [MgmtSrvr] ALERT -- Node 3: Node 7 Disconnected
2010-09-01 09:49:51 [MgmtSrvr] INFO -- Node 3: Node 1: API mysql-5.1.47 ndb-7.1.5
2010-09-01 09:49:52 [MgmtSrvr] INFO -- Node 4: Communication to Node 7 opened
2010-09-01 09:49:52 [MgmtSrvr] INFO -- Node 4: Node 7 Connected
2010-09-01 09:49:52 [MgmtSrvr] INFO -- Node 4: Node 7: API mysql-5.1.47 ndb-7.1.5
2010-09-01 09:49:53 [MgmtSrvr] INFO -- Node 3: Communication to Node 7 opened
2010-09-01 09:49:53 [MgmtSrvr] INFO -- Node 3: Node 7 Connected
2010-09-01 09:49:53 [MgmtSrvr] INFO -- Node 3: Node 7: API mysql-5.1.47 ndb-7.1.5
---------------------END-------ndb_2_cluster.log----------------------------------
Sorry for the big logs and thank you in advance :)
I am trying to import a ~500mbyte sql dump file to a new installation of mysql cluster (7.1.5-1)
The dump file was taken from another mysql (non cluster) server with the following command :
mysqldump -udata -p www_database_com --opt --lock-all-tables > database.sql
I changed the engine from myisam to ndbcluster in all the tables and then i created the database from the mysql api service.
When i am trying to import the dump file to the mysql cluster from node 7 i get the following error after some minutes:
mysql -uroot -p www_database_com < database.sql_ndb
Enter password:
ERROR 1297 (HY000) at line 941: Got temporary error 4010 'Node failure caused abort of transaction' from NDBCLUSTER
I am using 2 servers for ndb_mgmd and mysqld and another 2 for ndbd
(total 4 servers)
I have a private 100mbit network dedicated for mysql cluster
All the servers are of the same configuration
16G ram
3x500G sata disks in RAID-5 configuration
2xQuad-Core AMD Opteron(tm) Processor 2344 HE
my config.ini
[TCP DEFAULT]
SendBufferMemory=4M
ReceiveBufferMemory=4M
[NDB_MGMD DEFAULT]
PortNumber=1186
Datadir=/var/lib/mysql-cluster/
[NDB_MGMD]
Id=1
Hostname=172.18.77.1
LogDestination=FILE:filename=ndb_1_cluster.log,maxfiles=6
ArbitrationRank=1
[NDB_MGMD]
Id=2
Hostname=172.18.77.2
LogDestination=FILE:filename=ndb_2_cluster.log,maxfiles=6
ArbitrationRank=1
[NDBD DEFAULT]
NoOfReplicas=2
Datadir=/var/lib/mysql-cluster/
FileSystemPathDD=/var/lib/mysql-cluster/
#FileSystemPathUndoFiles=/var/lib/mysql-cluster/
#FileSystemPathDataFiles=/var/lib/mysql-cluster/
DataMemory=12139M
IndexMemory=1518M
LockPagesInMainMemory=1
MaxNoOfConcurrentOperations=100000
StringMemory=25
MaxNoOfTables=8192
MaxNoOfOrderedIndexes=2048
MaxNoOfUniqueHashIndexes=512
MaxNoOfAttributes=24576
MaxNoOfTriggers=14336
DiskCheckpointSpeedInRestart=10M
FragmentLogFileSize=256M
InitFragmentLogFiles=SPARSE
NoOfFragmentLogFiles=72
RedoBuffer=48M
TimeBetweenLocalCheckpoints=20
TimeBetweenGlobalCheckpoints=1000
TimeBetweenEpochs=100
MemReportFrequency=30
BackupReportFrequency=10
### Params for setting logging
LogLevelStartup=15
LogLevelShutdown=15
LogLevelCheckpoint=8
LogLevelNodeRestart=15
### Params for increasing Disk throughput
BackupMaxWriteSize=1M
BackupDataBufferSize=16M
BackupLogBufferSize=4M
BackupMemory=20M
#Reports indicates that odirect=1 can cause io errors (os err code 5) on some systems. You must test.
#ODirect=1
### Watchdog
TimeBetweenWatchdogCheckInitial=80000
### TransactionInactiveTimeout - should be enabled in Production
TransactionInactiveTimeout=80000
# Added this cause of ERROR 1297 (HY000) at line 612: Got temporary error 4010 'Node failure caused abort of transaction' from NDBCLUSTER
TransactionDeadLockDetectionTimeOut=80000
### CGE 6.3 - REALTIME EXTENSIONS
#RealTimeScheduler=1
#SchedulerExecutionTimer=80
#SchedulerSpinTimer=40
### DISK DATA
SharedGlobalMemory=20M
DiskPageBufferMemory=64M
### Multithreading
#MaxNoOfExecutionThreads=4
### Increasing the LongMessageBuffer b/c of a bug (20090903)
LongMessageBuffer=8M
BatchSizePerLocalScan=512
[NDBD]
Id=3
Hostname=172.18.77.3
[NDBD]
Id=4
Hostname=172.18.77.4
## BELOW ARE TWO (INACTIVE) SLOTS FOR DATA NODES TO ALLOW FOR GROWTH
#[NDBD]
#Id=5
#Hostname=
#[NDBD]
#Id=6
#Hostname=
[MYSQLD DEFAULT]
BatchSize=512
#BatchByteSize=2048K
#MaxScanBatchSize=2048K
[MYSQLD]
Id=7
Hostname=172.18.77.1
[MYSQLD]
Id=8
Hostname=172.18.77.1
[MYSQLD]
Id=9
Hostname=172.18.77.2
[MYSQLD]
Id=10
Hostname=172.18.77.2
### SLOTS FOR CMON (one for each ndb_mgmd)
[MYSQLD]
Hostname=172.18.77.1
[MYSQLD]
Hostname=172.18.77.2
### SLOTS (one for each ndb_mgmd) FOR HELPER APPLICATIONS SUCH AS ndb_show_tables etc
[MYSQLD]
Hostname=172.18.77.1
[MYSQLD]
Hostname=172.18.77.2
My my.cnf
[mysqld]
user=root
datadir=/var/lib/mysql-cluster/
pid-file=mysqld.pid
socket=/var/lib/mysql-cluster/mysql.sock
ndbcluster
ndb-connectstring="172.18.77.1:1186;172.18.77.2:1186"
#These are commented out for troubleshooting reasons
#ndb-cluster-connection-pool=1
#ndb-force-send=1
#ndb-use-exact-count=0
#ndb-extra-logging=1
#ndb-autoincrement-prefetch-sz=256
#engine-condition-pushdown=1
log-err=error.log
key_buffer = 256M
#max_allowed_packet = 16M
max_allowed_packet = 4M
sort_buffer_size = 512K
read_buffer_size = 256K
read_rnd_buffer_size = 512K
#thread_cache_size=1024
#myisam_sort_buffer_size = 8M
myisam_sort_buffer_size = 2M
memlock
sysdate_is_now
max-connections=200
thread-cache-size=128
query-cache-type = 0
query-cache-size = 0
table-open_cache=1024
table-cache=512
lower-case-table-names=0
[MYSQL]
socket=/var/lib/mysql-cluster/mysql.sock
[client]
socket=/var/lib/mysql-cluster/mysql.sock
My my.cnf in data nodes
[mysqld]
ndbcluster
ndb-connectstring=172.18.77.1,172.18.77.2
[mysql-cluster]
ndb-connectstring=172.18.77.1,172.18.77.2
About the logs now
------------------START------------ndb_3_out.log---------------------------------
2010-09-01 09:27:48 [ndbd] INFO -- Start phase 5 completed
2010-09-01 09:27:48 [ndbd] INFO -- Start phase 6 completed
m_active_buckets.set(0)
2010-09-01 09:27:48 [ndbd] INFO -- Start phase 7 completed
2010-09-01 09:27:48 [ndbd] INFO -- Start phase 8 completed
2010-09-01 09:27:48 [ndbd] INFO -- Start phase 9 completed
2010-09-01 09:27:48 [ndbd] INFO -- Start phase 100 completed
2010-09-01 09:27:48 [ndbd] INFO -- Start phase 101 completed
2010-09-01 09:27:48 [ndbd] INFO -- Node started
alloc_chunk(312334 16) -
tab: 51 frag: 1 increasing maxGciInLcp from 724 to 731
tab: 51 frag: 0 increasing maxGciInLcp from 743 to 752
tab: 51 frag: 1 increasing maxGciInLcp from 743 to 752
2010-09-01 09:47:56 [ndbd] ERROR -- c_gcp_list.seize() failed: gci: 4200478015496 nodes: 0000000000000000000000000000000000000000000000000000000000000280
2010-09-01 09:47:56 [ndbd] WARNING -- ACK wo/ gcp record (gci: 978/8) ref: 0fa20009 from: 00030009
2010-09-01 09:47:56 [ndbd] ERROR -- c_gcp_list.seize() failed: gci: 4200478015497 nodes: 0000000000000000000000000000000000000000000000000000000000000280
2010-09-01 09:47:56 [ndbd] WARNING -- ACK wo/ gcp record (gci: 978/9) ref: 0fa20009 from: 00030009
WARNING: timerHandlingLab now: 65637471 sent: 65637421 diff: 50
2010-09-01 09:47:56 [ndbd] ERROR -- c_gcp_list.seize() failed: gci: 4204772982784 nodes: 0000000000000000000000000000000000000000000000000000000000000280
2010-09-01 09:47:56 [ndbd] WARNING -- ACK wo/ gcp record (gci: 979/0) ref: 0fa20009 from: 00030009
2010-09-01 09:47:56 [ndbd] ERROR -- c_gcp_list.seize() failed: gci: 4204772982785 nodes: 0000000000000000000000000000000000000000000000000000000000000280
2010-09-01 09:47:56 [ndbd] WARNING -- ACK wo/ gcp record (gci: 979/1) ref: 0fa20009 from: 00030009
2010-09-01 09:47:56 [ndbd] ERROR -- c_gcp_list.seize() failed: gci: 4204772982786 nodes: 0000000000000000000000000000000000000000000000000000000000000280
2010-09-01 09:47:56 [ndbd] WARNING -- ACK wo/ gcp record (gci: 979/2) ref: 0fa20009 from: 00030009
WARNING: timerHandlingLab now: 67357839 sent: 67357789 diff: 50
--------------------END------------ndb_3_out.log---------------------------------
------------------START------------ndb_4_out.log---------------------------------
2010-09-01 09:26:11 [ndbd] INFO -- Start phase 6 completed
m_active_buckets.set(1)
2010-09-01 09:26:11 [ndbd] INFO -- Start phase 7 completed
2010-09-01 09:26:11 [ndbd] INFO -- Start phase 8 completed
2010-09-01 09:26:11 [ndbd] INFO -- Start phase 9 completed
2010-09-01 09:26:11 [ndbd] INFO -- Start phase 100 completed
2010-09-01 09:26:11 [ndbd] INFO -- Start phase 101 completed
2010-09-01 09:26:11 [ndbd] INFO -- Node started
alloc_chunk(312334 16) -
WARNING: timerHandlingLab now: 65225261 sent: 65225208 diff: 53
tab: 51 frag: 0 increasing maxGciInLcp from 743 to 752
2010-09-01 09:46:18 [ndbd] ERROR -- c_gcp_list.seize() failed: gci: 4200478015496 nodes: 0000000000000000000000000000000000000000000000000000000000000280
2010-09-01 09:46:18 [ndbd] WARNING -- ACK wo/ gcp record (gci: 978/8) ref: 0fa20009 from: 00040009
2010-09-01 09:46:18 [ndbd] ERROR -- c_gcp_list.seize() failed: gci: 4200478015497 nodes: 0000000000000000000000000000000000000000000000000000000000000280
2010-09-01 09:46:18 [ndbd] WARNING -- ACK wo/ gcp record (gci: 978/9) ref: 0fa20009 from: 00040009
2010-09-01 09:46:18 [ndbd] ERROR -- c_gcp_list.seize() failed: gci: 4204772982784 nodes: 0000000000000000000000000000000000000000000000000000000000000280
2010-09-01 09:46:18 [ndbd] WARNING -- ACK wo/ gcp record (gci: 979/0) ref: 0fa20009 from: 00040009
2010-09-01 09:46:18 [ndbd] ERROR -- c_gcp_list.seize() failed: gci: 4204772982785 nodes: 0000000000000000000000000000000000000000000000000000000000000280
2010-09-01 09:46:18 [ndbd] WARNING -- ACK wo/ gcp record (gci: 979/1) ref: 0fa20009 from: 00040009
2010-09-01 09:46:18 [ndbd] ERROR -- c_gcp_list.seize() failed: gci: 4204772982786 nodes: 0000000000000000000000000000000000000000000000000000000000000280
2010-09-01 09:46:18 [ndbd] WARNING -- ACK wo/ gcp record (gci: 979/2) ref: 0fa20009 from: 00040009
--------------------END------------ndb_4_out.log---------------------------------
-----------------------------MySQL-webmgm1.database.com.err----------------------
100901 09:32:06 mysqld_safe Starting mysqld daemon with databases from /var/lib/mysql-cluster/
100901 9:32:06 [Note] Plugin 'FEDERATED' is disabled.
100901 9:32:06 InnoDB: Started; log sequence number 0 44233
100901 9:32:06 [Note] NDB: NodeID is 7, management server '172.18.77.1:1186'
100901 9:32:07 [Note] NDB[0]: NodeID: 7, all storage nodes connected
100901 9:32:07 [Warning] NDB: server id set to zero will cause any other mysqld with bin log to log with wrong server id
100901 9:32:07 [Note] Starting Cluster Binlog Thread
100901 9:32:07 [Note] Event Scheduler: Loaded 0 events
100901 9:32:07 [Note] NDB Binlog: DISCOVER TABLE Event: REPL$mysql/ndb_schema
100901 9:32:07 [Note] NDB Binlog: logging ./mysql/ndb_schema (UPDATED,USE_WRITE)
100901 9:32:07 [Note] NDB Binlog: DISCOVER TABLE Event: REPL$mysql/ndb_apply_status
100901 9:32:07 [Note] NDB Binlog: logging ./mysql/ndb_apply_status (UPDATED,USE_WRITE)
2010-09-01 09:32:08 [NdbApi] INFO -- Flushing incomplete GCI:s < 190/2
2010-09-01 09:32:08 [NdbApi] INFO -- Flushing incomplete GCI:s < 190/2
100901 9:32:08 [Note] NDB Binlog: starting log at epoch 190/2
100901 9:32:08 [Note] NDB Binlog: ndb tables writable
100901 9:32:08 [Note] /usr/sbin/mysqld: ready for connections.
Version: '5.1.47-ndb-7.1.5-cluster-gpl' socket: '/var/lib/mysql-cluster/mysql.sock' port: 3306 MySQL Cluster Server (GPL)
100901 9:32:08 [Note] NDB Binlog: Node: 3, subscribe from node 9, Subscriber bitmask 0200
100901 9:32:08 [Note] NDB Binlog: Node: 4, subscribe from node 9, Subscriber bitmask 0200
100901 9:34:38 [Note] NDB Binlog: CREATE TABLE Event: REPL$www_database_com/404-log
100901 9:34:59 [Note] NDB Binlog: CREATE TABLE Event: REPL$www_database_com/alert_direct_msg
100901 9:35:03 [Note] NDB Binlog: CREATE TABLE Event: REPL$www_database_com/alert_replies
100901 9:35:06 [Note] NDB Binlog: CREATE TABLE Event: REPL$www_database_com/alerts-global-read-mem
100901 9:35:08 [Note] NDB Binlog: CREATE TABLE Event: REPL$www_database_com/alerts
100901 9:35:09 [Note] NDB Binlog: CREATE TABLE Event: REPL$www_database_com/applications-settings
100901 9:35:12 [Note] NDB Binlog: CREATE TABLE Event: REPL$www_database_com/applications
100901 9:35:14 [Note] NDB Binlog: CREATE TABLE Event: REPL$www_database_com/basic_user_index
100901 9:41:57 [Note] NDB Binlog: CREATE TABLE Event: REPL$www_database_com/basic_user_info-xxx
100901 9:41:59 [Note] NDB Binlog: CREATE TABLE Event: REPL$www_database_com/favorited-tweets
100901 9:42:03 [Note] NDB Binlog: CREATE TABLE Event: REPL$www_database_com/favorites-tags
100901 9:42:05 [Note] NDB Binlog: CREATE TABLE Event: REPL$www_database_com/favorites
100901 9:42:09 [Note] NDB Binlog: CREATE TABLE Event: REPL$www_database_com/followers-cache
100901 9:44:19 [Note] NDB Binlog: CREATE TABLE Event: REPL$www_database_com/followers
100901 9:44:20 [Note] NDB Binlog: CREATE TABLE Event: REPL$www_database_com/followfriday_reminder
100901 9:44:25 [Note] NDB Binlog: CREATE TABLE Event: REPL$www_database_com/group_categories
100901 9:44:26 [Note] NDB Binlog: CREATE TABLE Event: REPL$www_database_com/groups-autopost-cache
100901 9:44:29 [Note] NDB Binlog: CREATE TABLE Event: REPL$www_database_com/groups-autopost-keywords
100901 9:44:31 [Note] NDB Binlog: CREATE TABLE Event: REPL$www_database_com/groups-members-cache
100901 9:44:33 [Note] NDB Binlog: CREATE TABLE Event: REPL$www_database_com/groups-members
100901 9:44:35 [Note] NDB Binlog: CREATE TABLE Event: REPL$www_database_com/groups-tags-follow
100901 9:44:36 [Note] NDB Binlog: CREATE TABLE Event: REPL$www_database_com/groups-timeline-index
100901 9:44:43 [Note] NDB Binlog: CREATE TABLE Event: REPL$www_database_com/groups-timeline-xxx
100901 9:44:46 [Note] NDB Binlog: CREATE TABLE Event: REPL$www_database_com/groups
100901 9:44:48 [Note] NDB Binlog: CREATE TABLE Event: REPL$www_database_com/groups_tags
100901 9:44:50 [Note] NDB Binlog: CREATE TABLE Event: REPL$www_database_com/ignore_users
100901 9:44:51 [Note] NDB Binlog: CREATE TABLE Event: REPL$www_database_com/invitation-requests
100901 9:44:53 [Note] NDB Binlog: CREATE TABLE Event: REPL$www_database_com/invitations
100901 9:44:54 [Note] NDB Binlog: CREATE TABLE Event:
REPL$www_database_com/log-posts-per-day
100901 9:44:56 [Note] NDB Binlog: CREATE TABLE Event:
REPL$www_database_com/log-registered-online-hourly
100901 9:45:05 [Note] NDB Binlog: CREATE TABLE Event:
REPL$www_database_com/log-registered-online-tmp
100901 9:45:07 [Note] NDB Binlog: CREATE TABLE Event:
REPL$www_database_com/log-registered-online
100901 9:45:09 [Note] NDB Binlog: CREATE TABLE Event:
REPL$www_database_com/log-users-online
100901 9:46:55 [Note] NDB Binlog: CREATE TABLE Event:
REPL$www_database_com/mem-tweet-replies
100901 9:49:02 [Note] NDB Binlog: Node: 3, down, Subscriber bitmask 00
100901 9:49:02 [Note] NDB Binlog: Node: 4, down, Subscriber bitmask 00
100901 9:49:02 [Note] NDB Binlog: cluster failure for ./mysql/ndb_schema at epoch 972/0.
100901 9:49:02 [Note] NDB Binlog: cluster failure for ./mysql/ndb_apply_status at epoch 972/0.
100901 9:49:02 [Note] Restarting Cluster Binlog
100901 9:49:33 [Note] Restarting Cluster Binlog
100901 9:49:52 [Note] NDB Binlog: DISCOVER TABLE Event: REPL$mysql/ndb_schema
100901 9:49:52 [Note] NDB Binlog: logging ./mysql/ndb_schema (UPDATED,USE_WRITE)
100901 9:49:52 [Note] NDB Binlog: DISCOVER TABLE Event: REPL$mysql/ndb_apply_status
100901 9:49:52 [Note] NDB Binlog: logging ./mysql/ndb_apply_status (UPDATED,USE_WRITE)
2010-09-01 09:49:52 [NdbApi] INFO -- Flushing incomplete GCI:s < 1025/0
2010-09-01 09:49:52 [NdbApi] INFO -- Flushing incomplete GCI:s < 1025/0
100901 9:49:52 [Note] NDB Binlog: starting log at epoch 1025/0
100901 9:49:52 [Note] NDB Binlog: ndb tables writable
100901 9:49:52 [Note] NDB Binlog: Node: 3, subscribe from node 9, Subscriber bitmask 0200
100901 9:49:52 [Note] NDB Binlog: Node: 4, subscribe from node 9, Subscriber bitmask 0200
--------------------------------END-----------------------------------------------
-------------------START-------ndb_2_cluster.log----------------------------------
2010-09-01 09:48:54 [MgmtSrvr] INFO -- Node 3: Local checkpoint 20 completed
2010-09-01 09:48:55 [MgmtSrvr] INFO -- Node 3: Local checkpoint 21 started. Keep GCI = 933 oldest restorable GCI
= 961
2010-09-01 09:49:02 [MgmtSrvr] WARNING -- Node 3: Node 1 missed heartbeat 2
2010-09-01 09:49:03 [MgmtSrvr] WARNING -- Node 4: Node 1 missed heartbeat 2
2010-09-01 09:49:03 [MgmtSrvr] WARNING -- Node 4: Node 7 missed heartbeat 2
2010-09-01 09:49:03 [MgmtSrvr] WARNING -- Node 3: Node 1 missed heartbeat 3
2010-09-01 09:49:04 [MgmtSrvr] ALERT -- Node 3: Node 1 Disconnected
2010-09-01 09:49:04 [MgmtSrvr] INFO -- Node 3: Communication to Node 1 closed
2010-09-01 09:49:04 [MgmtSrvr] INFO -- Node 4: Communication to Node 1 closed
2010-09-01 09:49:04 [MgmtSrvr] ALERT -- Node 4: Node 1 Disconnected
2010-09-01 09:49:04 [MgmtSrvr] WARNING -- Node 4: Node 7 missed heartbeat 3
2010-09-01 09:49:05 [MgmtSrvr] INFO -- Node 4: Communication to Node 1 opened
2010-09-01 09:49:05 [MgmtSrvr] INFO -- Node 3: Communication to Node 1 opened
2010-09-01 09:49:06 [MgmtSrvr] INFO -- Node 3: Communication to Node 7 closed
2010-09-01 09:49:06 [MgmtSrvr] WARNING -- Node 4: Node 7 missed heartbeat 4
2010-09-01 09:49:06 [MgmtSrvr] ALERT -- Node 4: Node 7 declared dead due to missed heartbeat
2010-09-01 09:49:06 [MgmtSrvr] INFO -- Node 4: Communication to Node 7 closed
2010-09-01 09:49:06 [MgmtSrvr] INFO -- Node 4: Data usage is 0%(2190 32K pages of total 388448)
2010-09-01 09:49:06 [MgmtSrvr] INFO -- Node 4: Index usage is 0%(1511 8K pages of total 194336)
2010-09-01 09:49:16 [MgmtSrvr] INFO -- Node 3: Data usage is 0%(2148 32K pages of total 388448)
2010-09-01 09:49:16 [MgmtSrvr] INFO -- Node 3: Index usage is 0%(1511 8K pages of total 194336)
2010-09-01 09:49:35 [MgmtSrvr] INFO -- Node 3: Local checkpoint 21 completed
2010-09-01 09:49:36 [MgmtSrvr] INFO -- Node 4: Data usage is 0%(2148 32K pages of total 388448)
2010-09-01 09:49:36 [MgmtSrvr] INFO -- Node 4: Index usage is 0%(1511 8K pages of total 194336)
2010-09-01 09:49:46 [MgmtSrvr] INFO -- Node 3: Data usage is 0%(2148 32K pages of total 388448)
2010-09-01 09:49:46 [MgmtSrvr] INFO -- Node 3: Index usage is 0%(1511 8K pages of total 194336)
2010-09-01 09:49:50 [MgmtSrvr] INFO -- Node 4: Node 1 Connected
2010-09-01 09:49:50 [MgmtSrvr] ALERT -- Node 4: Node 7 Disconnected
2010-09-01 09:49:50 [MgmtSrvr] INFO -- Node 4: Node 1: API mysql-5.1.47 ndb-7.1.5
2010-09-01 09:49:51 [MgmtSrvr] INFO -- Node 3: Node 1 Connected
2010-09-01 09:49:51 [MgmtSrvr] ALERT -- Node 3: Node 7 Disconnected
2010-09-01 09:49:51 [MgmtSrvr] INFO -- Node 3: Node 1: API mysql-5.1.47 ndb-7.1.5
2010-09-01 09:49:52 [MgmtSrvr] INFO -- Node 4: Communication to Node 7 opened
2010-09-01 09:49:52 [MgmtSrvr] INFO -- Node 4: Node 7 Connected
2010-09-01 09:49:52 [MgmtSrvr] INFO -- Node 4: Node 7: API mysql-5.1.47 ndb-7.1.5
2010-09-01 09:49:53 [MgmtSrvr] INFO -- Node 3: Communication to Node 7 opened
2010-09-01 09:49:53 [MgmtSrvr] INFO -- Node 3: Node 7 Connected
2010-09-01 09:49:53 [MgmtSrvr] INFO -- Node 3: Node 7: API mysql-5.1.47 ndb-7.1.5
---------------------END-------ndb_2_cluster.log----------------------------------
Sorry for the big logs and thank you in advance :)
Subject
Views
Written By
Posted
Node failure caused abort of transaction after trying to import a "large" mysqldump file
6176
September 01, 2010 02:55AM
2058
September 01, 2010 10:47PM
2575
September 02, 2010 01:13AM
2242
September 04, 2010 04:13AM
Sorry, you can't reply to this topic. It has been closed.
Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.