Bug #50700 mysqld crashes
Submitted: 28 Jan 2010 20:30 Modified: 28 Jan 2013 16:24
Reporter: Himanshu Kanda Email Updates:
Status: Can't repeat Impact on me:
None 
Category:MySQL Cluster: Cluster (NDB) storage engine Severity:S2 (Serious)
Version:mysql-5.1-telco-7.0 OS:Linux (xenU #2 SMP Wed Aug 15 17:27:36 SAST 2007 x86_64 x86_64 x86_64 GNU/Linux)
Assigned to: CPU Architecture:Any
Tags: 7.0.9

[28 Jan 2010 20:30] Himanshu Kanda
Description:
whenever I try to apply schema with some foreign key, mysqld daemon just crashes and exits. When I checked error.log, it shows some stack trace where it crashed but expected behaviour is that parser should  just ignore any FOREIGN keys and mysqld process should never crash.

Snippet from error.log during the crash can be found on http://pastie.org/799370

How to repeat:
I am using following schema file and my default engine is ndbcluster:

BEGIN;
CREATE TABLE `polls_poll` (
    `id` integer AUTO_INCREMENT NOT NULL PRIMARY KEY,
    `question` varchar(200) NOT NULL,
    `pub_date` datetime NOT NULL
)
;
CREATE TABLE `polls_choice` (
    `id` integer AUTO_INCREMENT NOT NULL PRIMARY KEY,
    `poll_id` integer NOT NULL,
    `choice` varchar(200) NOT NULL,
    `votes` integer NOT NULL
)
;
ALTER TABLE `polls_choice` ADD CONSTRAINT `poll_id_refs_id_5d896c23` FOREIGN KEY (`poll_id`) REFERENCES `polls_poll` (`id`);
COMMIT;

----------------------------------------------------------------------
/etc/my.cnf:
[MYSQLD]
default-storage-engine=NDBCLUSTER
init_connect='SET collation_connection = utf8_general_ci'
init_connect='SET NAMES utf8'
default-character-set=utf8
character-set-server = utf8
collation-server = utf8_general_ci
ndbcluster
ndb-force-send=1
ndb-use-exact-count=0
ndb-extra-logging=6
ndb-autoincrement-prefetch-sz=256
ndb_wait_setup=120
engine-condition-pushdown=1
user=mysql
log-error=error.log

#REPLICATION SPECIFIC - GENERAL
#server-id must be unique across all mysql servers participating in replication.
#server-id=X
#REPLICATION SPECIFIC - MASTER
#log-bin=binlog
#LOGS
log-err=error.log
#log
#log-slow-queries
#OTHER THINGS, BUFFERS ETC
key_buffer = 256M
#max_allowed_packet = 16M
sort_buffer_size = 512K
read_buffer_size = 256K
read_rnd_buffer_size = 512K
#thread_cache_size=1024
#myisam_sort_buffer_size = 8M
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
----------------------------------------------------------------------
config.ini(on management node)

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

# Management process options:
[NDB_MGMD DEFAULT]
PortNumber=1186
DataDir=/var/lib/mysql-cluster/data

[NDB_MGMD]
Id=1
HostName=<ip 0..> 
LogDestination=FILE:filename=ndb_1_cluster.log,maxsize=10000000,maxfiles=6
ArbitrationRank=1

[NDBD DEFAULT]
NoOfReplicas=2
DataDir=/var/lib/mysql-cluster/data
#FileSystemPathDD=/var/lib/mysql-cluster/data
DataMemory=1979M
IndexMemory=248M
LockPagesInMainMemory=1

MaxNoOfConcurrentOperations=100000

StringMemory=25
MaxNoOfTables=4096
MaxNoOfOrderedIndexes=2048
MaxNoOfUniqueHashIndexes=512
MaxNoOfAttributes=24576
DiskCheckpointSpeedInRestart=100M
FragmentLogFileSize=256M
InitFragmentLogFiles=FULL
NoOfFragmentLogFiles=12
RedoBuffer=32M

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=60000

### TransactionInactiveTimeout  - should be enabled in Production 
TransactionInactiveTimeout=30000
### 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=16M

BatchSizePerLocalScan=512

# Options for data node "A":
[NDBD]
Id=2
HostName=<ip 1..> 
LockExecuteThreadToCPU=1
LockMaintThreadsToCPU=0

# Options for data node "B":
[NDBD]
Id=3
HostName=<ip 2..> 
LockExecuteThreadToCPU=1
LockMaintThreadsToCPU=0

#[NDBD]
#Id=4
#Hostname=

#[NDBD]
#Id=5
#Hostname=

[MYSQLD DEFAULT]
BatchSize=512

# SQL node options:
[mysqld]
Id=6
HostName=<ip 1..> 
[mysqld]
Id=7
HostName=<ip 2..> 
[mysqld]
[mysqld]

Suggested fix:
No idea but as per suggested on IRC channel I should not use any Foreign Key in schema to be applied
[28 Jan 2013 16:24] MySQL Verification Team
Hello Himanshu,

I can not repeat described behavior on reported version(with the exact config.ini/mt.cnf).. Is it still repeatable? Please reopen, if needed

++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++

--------------------------------

ndb_mgm> show
Cluster Configuration
---------------------
[ndbd(NDB)]     2 node(s)
id=2    @127.0.0.1  (mysql-5.1.39 ndb-7.0.9, Nodegroup: 0, Master)
id=3    @127.0.0.1  (mysql-5.1.39 ndb-7.0.9, Nodegroup: 0)

[ndb_mgmd(MGM)] 1 node(s)
id=1    @127.0.0.1  (mysql-5.1.39 ndb-7.0.9)

[mysqld(API)]   4 node(s)
id=6    @127.0.0.1  (mysql-5.1.39 ndb-7.0.9)
id=7 (not connected, accepting connect from localhost)
id=8 (not connected, accepting connect from any host)
id=9 (not connected, accepting connect from any host)

ndb_mgm>

--------------------------------

[ushastry@cluster-repo mysql-cluster-com-7_0_9]$ bin/mysql -u root -p test
Enter password:
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 3
Server version: 5.1.39-ndb-7.0.9-cluster-com MySQL Cluster Server (Commercial)

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

mysql> select version();
+------------------------------+
| version()                    |
+------------------------------+
| 5.1.39-ndb-7.0.9-cluster-com |
+------------------------------+
1 row in set (0.00 sec)

mysql> select now();
+---------------------+
| now()               |
+---------------------+
| 2013-01-29 14:18:11 |
+---------------------+
1 row in set (0.00 sec)

mysql> BEGIN;
Query OK, 0 rows affected (0.00 sec)

mysql> CREATE TABLE `polls_poll` (
    ->     `id` integer AUTO_INCREMENT NOT NULL PRIMARY KEY,
    ->     `question` varchar(200) NOT NULL,
    ->     `pub_date` datetime NOT NULL
    -> )
    -> ;
Query OK, 0 rows affected (0.99 sec)

mysql>
mysql> show create table polls_poll\G
*************************** 1. row ***************************
       Table: polls_poll
Create Table: CREATE TABLE `polls_poll` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `question` varchar(200) NOT NULL,
  `pub_date` datetime NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=ndbcluster DEFAULT CHARSET=utf8
1 row in set (0.00 sec)

mysql> CREATE TABLE `polls_choice` (
    ->     `id` integer AUTO_INCREMENT NOT NULL PRIMARY KEY,
    ->     `poll_id` integer NOT NULL,
    ->     `choice` varchar(200) NOT NULL,
    ->     `votes` integer NOT NULL
    -> )
    -> ;
Query OK, 0 rows affected (1.04 sec)

mysql> show create table polls_choice\G
*************************** 1. row ***************************
       Table: polls_choice
Create Table: CREATE TABLE `polls_choice` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `poll_id` int(11) NOT NULL,
  `choice` varchar(200) NOT NULL,
  `votes` int(11) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=ndbcluster DEFAULT CHARSET=utf8
1 row in set (0.00 sec)

mysql> ALTER TABLE `polls_choice` ADD CONSTRAINT `poll_id_refs_id_5d896c23` FOREIGN KEY (`poll_id`) REFERENCES `polls_poll` (`id`);
Query OK, 0 rows affected (2.23 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> COMMIT;
Query OK, 0 rows affected (0.00 sec)

mysql>
mysql> show tables;
+----------------+
| Tables_in_test |
+----------------+
| polls_choice   |
| polls_poll     |
+----------------+
2 rows in set (0.00 sec)

mysql> select now();
+---------------------+
| now()               |
+---------------------+
| 2013-01-29 14:28:53 |
+---------------------+
1 row in set (0.00 sec)

mysql>

## Error log

130129 14:11:32 mysqld_safe Starting mysqld daemon with databases from /data/ushastry/cluster/mysql-cluster-com-7_0_9/data/
130129 14:11:32 [Warning] Changed limits: max_open_files: 1024  max_connections: 200  table_cache: 407
130129 14:11:32 [Note] Plugin 'FEDERATED' is disabled.
130129 14:11:32 [Note] NDB: NodeID is 6, management server 'localhost:1186'
130129 14:11:32 [Note] NDB[0]: NodeID: 6, all storage nodes connected
130129 14:11:32 [Warning] NDB: server id set to zero will cause any other mysqld with bin log to log with wrong server id
130129 14:11:32 [Note] Starting Cluster Binlog Thread
130129 14:11:32 [Note] Event Scheduler: Loaded 0 events
130129 14:11:33 [Note] NDB: Creating mysql.ndb_schema
130129 14:11:33 [Note] NDB: Flushing mysql.ndb_schema
130129 14:11:34 [Note] NDB Binlog: CREATE TABLE Event: REPL$mysql/ndb_schema
130129 14:11:34 [Note] NDB Binlog: logging ./mysql/ndb_schema (UPDATED,USE_WRITE)
130129 14:11:34 [Note] NDB: Creating mysql.ndb_apply_status
130129 14:11:34 [Note] NDB: Flushing mysql.ndb_apply_status
130129 14:11:34 [Note] NDB Binlog: CREATE TABLE Event: REPL$mysql/ndb_apply_status
130129 14:11:34 [Note] NDB Binlog: logging ./mysql/ndb_apply_status (UPDATED,USE_WRITE)
2013-01-29 14:11:34 [NdbApi] INFO     -- Flushing incomplete GCI:s < 44/7
2013-01-29 14:11:34 [NdbApi] INFO     -- Flushing incomplete GCI:s < 44/7
130129 14:11:34 [Note] NDB Binlog: starting log at epoch 44/7
130129 14:11:34 [Note] NDB Binlog: ndb tables writable
130129 14:11:34 [Note] /data/ushastry/cluster/mysql-cluster-com-7_0_9/bin/mysqld: ready for connections.
Version: '5.1.39-ndb-7.0.9-cluster-com'  socket: '/tmp/mysql.sock'  port: 3306  MySQL Cluster Server (Commercial)
130129 14:12:50 [Note] NDB Binlog: CREATE TABLE Event: REPL$test/polls_poll
130129 14:13:24 [Note] NDB Binlog: CREATE TABLE Event: REPL$test/polls_choice
130129 14:13:56 [Note] NDB Binlog: RENAME Event: REPL$test/polls_choice
130129 14:17:55 [Note] tid 2: node[3] transaction_hint=0, transaction_no_hint=1
130129 14:27:44 [Note] NDB Binlog: CREATE TABLE Event: REPL$test/polls_poll
130129 14:28:14 [Note] NDB Binlog: CREATE TABLE Event: REPL$test/polls_choice
130129 14:28:39 [Note] NDB Binlog: RENAME Event: REPL$test/polls_choice

++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++