| Bug #77367 | Error code1296 Got error 240 'Unknown error code' from NDBCLUSTER | ||
|---|---|---|---|
| Submitted: | 16 Jun 2015 2:58 | Modified: | 29 Jun 2015 2:07 |
| Reporter: | Bugs System | Email Updates: | |
| Status: | Closed | Impact on me: | |
| Category: | MySQL Cluster: Cluster (NDB) storage engine | Severity: | S1 (Critical) |
| Version: | mysql-cluster-gpl-7.4.6-linux-glibc2.5-x | OS: | Linux (2.6.18-308.e15) |
| Assigned to: | MySQL Verification Team | CPU Architecture: | Any |
| Tags: | insert fail with 2 data nodes up. | ||
[16 Jun 2015 3:02]
Bugs System
and in fact, in many other tables' Java Spring's trancsation, this error often happens about 3 tenths, after reexecuting these statements, these statements can succeed even starting both two data nodes.
[17 Jun 2015 6:34]
Bugs System
hi, everyone, It seems that I have found the reason: the mysql cluster doesn't make DB's procedure shareed by multi SQL node and multi users. And I have check mysql's offical document on "create procedure" statement syntax, it seems there is no storageengine syntax to make procedure public-shared as tables.
[17 Jun 2015 11:25]
MySQL Verification Team
Hi, I think you are mixing the terminology here a bit. Data node is the node running ndbd or ndbmtd. Management node is what's running ndb_mgmd and SQL node is where you are running mysqld. You said that if you are running more then one data node you have this problem. I believe you are mistaken and that you get this problem if you are running more then one SQL node. If this is true, that not working with more then one SQL then yes, it can happen. Thing is, as you already noticed, stored procedures are on one SQL node only so you have to manually create all stored procedures on all SQL nodes. This *only* affects calling the procedures. If you have 5 SQL nodes and SP only on one SQL node calling this procedure will work on only the node where it exist so on other sql nodes you will fail to call this procedure (as it is not there), but, when you call the procedure on the SQL node that has it it will work no matter how many SQL nodes you have running, and also data that stored procedure changes is visible from all other SQL nodes if data changed is in ndbcluster tables. kind regards Bogdan Kecman
[24 Jun 2015 9:07]
Bugs System
Dear pioneer Mr. Bogdan, Thank you for your reply, and recent days I am too busy to update this bug here, sorry for my delay reply. And sorry that I had mixed two problems here to make you misunderstood me. Of cource, I know the differences of data nodes and sql nodes and management noeds, and your answer on SP about sql nodes is correct I think. My faced problem is Java's trancsation will often reports illegal operation of error code 240 after I have stop 1 data node for a long time and restart the stop data node. So after I optimize some parameters according to MySQL cluster's great pioneers' document, it seems that I have solved the error 240. Because after I have optimized the parameters, yesterday and today I have tried to stop 1 data node(but for only a short time) and restart it, the error 240 doesn't appear again. It is better for me to try to stop 1 data node for a long time, for example over 7 hours and do bulk data operations while 1 data node stop. I will let you know the newest infomation several days latter after I tried. Thank you.
[29 Jun 2015 2:07]
Bugs System
It seems that one of the clusters' data node can not stop too long time, otherwise, even the stop data node restart up, and the data node status has not displayed as starting and so as normal running, when doing a java trancsation, the illegal operation error of 1296's 240 error will display to stop your business. But how long can a data node be stop to avoid the illegal operation 240 error? My Bogdan and other pioneers, can you tell me cause I couldn't find the answer on "how long can a data node be stop to avoid the illegal operation 240 error" .
Description: In order to save your time, I have searched in baidu.com and google.com search engines and use the perror command, but I cann't find the solution so I submit a bug here. 1, How error happens: when I started 2nd data node but not just only start 1st data node, in several SQL clients, I call a procedure to insert 2 tables and update 1 table, but I got the error message of "Error code1296 Got error 240 'Unknown error code' from NDBCLUSTER". And when execute a simmilar procudure in Spring's transaction code, it reports only " ERROR [org.hibernate.transaction.JDBCTransaction] - JDBC commit failed java.sql.SQLException: Got error 240 'Unknown error code' from NDBCLUSTER " 2, the return message of the return of perror command is "Illegal error code 1296", but this procedure can succeed in executing when only run one data node. there should be no any illegal operation in this procedure. How to repeat: 1, our SQL cluster structure is in 2 linux servers and 1 windows 2008 server: A, 1 mgm node in *.*.13.101(linux server); B, 1 mgm node in *.*.16..253(linux server) c, 1 ndb node in *.*.13.101(linux server); d, 1 ndb node in *.*.16..253(linux server) e, 1 SQL node in *.*.13.101(linux server); f, 1 SQL node in *.*.13.100(windows 2008 server); g, 1 SQL node in *.*.16.26(windows 7, but not started); 2, the detail 3 tables' DDL are: /* SQLyog Ultimate v12.08 (64 bit) MySQL - 5.6.24-ndb-7.4.6-cluster-gpl-log : Database - testpz ********************************************************************* */ /*!40101 SET NAMES utf8 */; /*!40101 SET SQL_MODE=''*/; /*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */; /*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */; /*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */; /*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */; CREATE DATABASE /*!32312 IF NOT EXISTS*/`testpz` /*!40100 DEFAULT CHARACTER SET utf8 */; USE `testpz`; /*Table structure for table `p2p_td_payment_detail` */ DROP TABLE IF EXISTS `p2p_td_payment_detail`; CREATE TABLE `p2p_td_payment_detail` ( `ID` varchar(50) NOT NULL COMMENT 'id', `C_USER` varchar(32) DEFAULT NULL COMMENT 'user ID', `C_TIME` datetime DEFAULT NULL COMMENT '...', `C_TYPE` varchar(20) DEFAULT NULL COMMENT '...', `C_ORDER` varchar(50) DEFAULT NULL COMMENT '...', `C_MONEY` double(14,2) DEFAULT NULL COMMENT '...', `C_REMAINDER_MONEY` double(14,2) DEFAULT NULL COMMENT '...', `C_TARGET` varchar(32) DEFAULT NULL COMMENT '...', `C_COMMENT` varchar(100) DEFAULT NULL COMMENT '...', `C_RELATEDID` varchar(40) DEFAULT NULL COMMENT '...', `C_PZID` varchar(40) DEFAULT NULL COMMENT '...', `C_SERIALNUMBER` varchar(40) DEFAULT NULL COMMENT '...', `C_ASKFORDATE` datetime DEFAULT NULL COMMENT '...', PRIMARY KEY (`ID`), KEY `FK88FD327958768445` (`C_USER`) USING BTREE, KEY `FK88FD32794317E30B` (`C_TARGET`) USING BTREE, CONSTRAINT `p2p_td_payment_detail_ibfk_1` FOREIGN KEY (`C_TARGET`) REFERENCES `p2p_td_user` (`ID`) ON DELETE NO ACTION ON UPDATE NO ACTION, CONSTRAINT `p2p_td_payment_detail_ibfk_2` FOREIGN KEY (`C_USER`) REFERENCES `p2p_td_user` (`ID`) ON DELETE NO ACTION ON UPDATE NO ACTION ) ENGINE=ndbcluster DEFAULT CHARSET=utf8 COMMENT='...'; /*Table structure for table `p2p_td_user_balance` */ DROP TABLE IF EXISTS `p2p_td_user_balance`; CREATE TABLE `p2p_td_user_balance` ( `ID` varchar(32) NOT NULL COMMENT '...', `USER_ID` varchar(32) NOT NULL COMMENT '...', `C_AVA_TOTAL_AMOUNT_MONEY` double(14,2) DEFAULT NULL COMMENT '...', `C_AVA_CAPITAL_MONEY` double(14,2) DEFAULT NULL COMMENT '...', `C_AVA_BAIL_MONEY` double(14,2) DEFAULT NULL COMMENT '...', `C_AVA_BALANCE_MONEY` double(14,2) DEFAULT NULL COMMENT '...', `C_AVA_TRANSIT_MONEY` double(14,2) DEFAULT NULL COMMENT '...', `C_FROZEN_MONEY` double(14,2) DEFAULT NULL COMMENT '...', PRIMARY KEY (`ID`) ) ENGINE=ndbcluster DEFAULT CHARSET=utf8 COMMENT='...'; /*Table structure for table `pz_tb_recharge` */ DROP TABLE IF EXISTS `pz_tb_recharge`; CREATE TABLE `pz_tb_recharge` ( `id` varchar(32) NOT NULL, `pzAccount` varchar(32) NOT NULL COMMENT '...', `accountName` varchar(50) NOT NULL COMMENT '...', `cardNo` varchar(20) DEFAULT NULL COMMENT '...', `transferAmount` double(14,2) NOT NULL COMMENT '...', `actualAmount` double(14,2) DEFAULT NULL COMMENT '...', `amountType` int(1) NOT NULL DEFAULT '0' COMMENT '...', `createDateTime` timestamp NULL DEFAULT CURRENT_TIMESTAMP COMMENT '...', `status` int(1) DEFAULT '0' COMMENT '...', `checkDateTime` timestamp NULL DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP COMMENT '...', `opratAdmin` varchar(100) DEFAULT NULL, `platformAccount` varchar(50) DEFAULT NULL COMMENT '...', `certificateFilePath` varchar(100) DEFAULT NULL COMMENT '...', `openingBank` varchar(100) DEFAULT NULL COMMENT '...', `bankcard_id` varchar(100) DEFAULT NULL, PRIMARY KEY (`id`), KEY `FK24431AA39A279E9A` (`pzAccount`) USING BTREE, CONSTRAINT `fk_1682_1739` FOREIGN KEY (`pzAccount`) REFERENCES `p2p_td_user` (`ID`) ON DELETE NO ACTION ON UPDATE NO ACTION ) ENGINE=ndbcluster DEFAULT CHARSET=utf8; /*!40101 SET SQL_MODE=@OLD_SQL_MODE */; /*!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS */; /*!40014 SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS */; /*!40111 SET SQL_NOTES=@OLD_SQL_NOTES */; 3, executing the procedure: DELIMITER $$ USE `testpz`$$ DROP PROCEDURE IF EXISTS `domywhile`$$ CREATE DEFINER=`root`@`192.168.16.%` PROCEDURE `domywhile`() BEGIN DECLARE vv1 INT DEFAULT 1; WHILE vv1 <= 100 DO SET vv1 = vv1 + 1; INSERT INTO pz_tb_recharge(id,pzAccount,accountName,cardNo,transferAmount,actualAmount,STATUS,checkDateTime,opratAdmin) VALUES (UUID(),'297edff84d6512c5014d657955bd000a','aaaa','132123123213213213','10','100','1',NOW(),'admin'); UPDATE p2p_td_user_balance AS pb,pz_tb_recharge AS pr SET C_AVA_BALANCE_MONEY=pb.C_AVA_BALANCE_MONEY+pr.actualAmount+pr.transferAmount WHERE pb.USER_ID='297edff84d6512c5014d657955bd000a'; INSERT INTO `p2p_td_payment_detail` (`ID`,`C_USER`,`C_TIME`,`C_TYPE`,`C_ORDER`,`C_MONEY`,`C_REMAINDER_MONEY`,`C_TARGET`,`C_COMMENT`,`C_RELATEDID`,`C_PZID`,`C_SERIALNUMBER`,`C_ASKFORDATE`) VALUES (UUID(),'297edff84d6512c5014d657955bd000a',NOW(),'207',NULL,-3.00,(SELECT C_AVA_BALANCE_MONEY FROM p2p_td_user_balance AS pb,p2p_td_payment_detail AS pd WHERE pd.C_USER='297edff84d6512c5014d657955bd000a' LIMIT 0,1),'297edff84d6512c5014d657955bd000a','扣除管理费','40288de44daced92014daced92730000','40288de44d9d5ca9014d9d94f1050002','T336577',NULL); END WHILE; COMMIT; END$$ DELIMITER ; 4, This procedure can succeed in executing when only run one data node of 13.101, but when start 2nd data node and 2nd mgm node of 16.253 node, this error happens, the error message is :Error code1296 Got error 240 'Unknown error code' from NDBCLUSTER.