Planet MySQL

Planet MySQL - https://planet.mysql.com
  1. I’ve had some questions from people using MySQL Cluster GPL and wanting to move to using MySQL Cluster Carrier Grade Edition, i.e., they want to use MySQL Cluster Manager, MCM, to make their lives much easier, in particular, upgrading (as well as config change ease and backup history). All I want to do here is to share with you my personal experience on migrating what’s considered a ‘wild’ NDB Cluster to a MCM managed cluster. It’s just as simple to follow the manual chapter Importing a Cluster into MySQL Cluster Manager so at least you can see how I did it, and it might help someone. [ If you’re not migrating but just looking for further information on NDB Cluster, and came across this post, please please PLEASE look at the MySQL Cluster: Getting Started page, in particular the Evaluation Guide. ] So, what’s the scenario: NDB 7.3 (migrating to 7.4) Centos 6.5 4 vm’s / 4 servers: 2x datanodes (dn1 & dn2), 2x sqlnodes (sn1 & sn2) & 1x mgmt node (sn1). Technical objective: To upgrade NDB to 7.4 (or follow the procedure to go from/to any ‘consecutive’ versions). Personal objective: Share that this process can be done, how it’s done, and share the issues that I came across in my non-production environment. Let’s take for granted that your wild cluster is installed and working with a similar configuration to: Installed as o.s. root user (and all files are owned:grouped as such…). mysql o.s. user exists. tarball install / binaries are all found at /usr/local Datadirs are at /opt/mysql/738: mgm_data (for management node logs), ndbd_data (for datanode info) & data (for sqlnodes). config.ini & my.cnf are found at $BASEDIR/conf (created this dir myself ‘cos I’m like that). All other things are default, i.e. sqlnode / datanode / mgmtnode ports, sockets are located in datadirs, etc. There are some changes in the config files, as would be in a prod env. And the cluster setup looks something like this # ndb_mgm -e show Connected to Management Server at: localhost:1186 Cluster Configuration --------------------- [ndbd(NDB)] 2 node(s) id=3 @10.0.0.12 (mysql-5.6.22 ndb-7.3.8, Nodegroup: 0, *) id=4 @10.0.0.13 (mysql-5.6.22 ndb-7.3.8, Nodegroup: 0) [ndb_mgmd(MGM)] 1 node(s) id=1 @10.0.0.10 (mysql-5.6.22 ndb-7.3.8) [mysqld(API)] 5 node(s) id=10 @10.0.0.10 (mysql-5.6.22 ndb-7.3.8) id=11 @10.0.0.11 (mysql-5.6.22 ndb-7.3.8) id=12 (not connected, accepting connect from any host) id=13 (not connected, accepting connect from any host) id=14 (not connected, accepting connect from any host) Getting ready: Remember, all I’m doing here is sharing my experience of following Importing a Cluster into MySQL Cluster Manager so hopefully it will help someone see it in a different env, and for a specific use (upgrade, go CGE, etc.). Preparing wild cluster: In order to import the wild cluster into MCM, we need to ‘shape’or ‘tame’ the wild cluster, that is, to adjust certain configuration not normally found nor done in a cluster environment, in order to allow the import process to be successful. On both of the sqlnodes, we need a mcmd user, other wise it’s going to be impossible for MCM to manage them: mysql> CREATE USER 'mcmd'@'localhost' IDENTIFIED BY 'super'; mysql> GRANT ALL PRIVILEGES ON *.* TO 'mcmd'@'localhost' WITH GRANT OPTION; Every node of the wild cluster has been started with its node ID specified with the –ndb-nodeid option at the command line: # ps -ef | grep ndb root 3025 1 0 Aug16 ? 00:00:25 ndbmtd -c 10.0.0.10 --initial root 3026 3025 8 Aug16 ? 01:24:30 ndbmtd -c 10.0.0.10 --initial And also management node has to be started without caching the config. Changing / restarting each process (both sqlnodes & dnodes) can be done in this point making both changes at the same time / same restart: # ndb_mgmd --configdir=/usr/local/mysql-cluster-gpl-7.3.8-linux-glibc2.5-x86_64/conf \ -f /usr/local/mysql-cluster-gpl-7.3.8-linux-glibc2.5-x86_64/conf/config.ini \ --config-cache=false --ndb-nodeid=1 MySQL Cluster Management Server mysql-5.6.22 ndb-7.3.8 2017-08-17 08:35:57 [MgmtSrvr] INFO -- Skipping check of config directory since config cache is disabled. # ps -ef | grep ndb root 3560 1 0 08:42 ? 00:00:00 ndbmtd --ndb-nodeid=3 -c 10.0.0.10 root 3561 3560 8 08:42 ? 00:00:48 ndbmtd --ndb-nodeid=3 -c 10.0.0.10 root 3694 1 0 08:38 ? 00:00:00 ndbmtd --ndb-nodeid=4 -c 10.0.0.10 root 3695 3694 8 08:38 ? 00:00:16 ndbmtd --ndb-nodeid=4 -c 10.0.0.10 # ps -ef | grep mysqld mysql 3755 2975 7 08:49 pts/0 00:00:00 mysqld --defaults-file=/usr/local/mysql/conf/my.cnf --user=mysql --ndb-nodeid=10 mysql 3694 2984 2 08:50 pts/0 00:00:01 mysqld --defaults-file=/usr/local/mysql/conf/my.cnf --user=mysql --ndb-nodeid=11 Ok, all well and good up to here. Just a word of warning, the MCM daemon, mcmd, that runs on all servers of a cluster platform, can not be run as the os user ‘root’, hence, normally is run as ‘mysql’. This means that all the other processes of our wild cluster should be run as ‘mysql’ too. Other wise, the mcmd daemon can’t bring under control these processes (stop/start, etc.) This also impacts the file permissions (data files, pid files, logs, directories) so maybe it’s a good time to do that now, as we’re talking about a production environment. SOOOOOOO, when we stop the management node to restart it, let’s make that change, first file & directory perms, and then starting the process itself. As you probably won’t have separate binaries for each process, and the management node(s) will/should be sharing binaries with the sqlnodes, changing ownership of the files / dir’s shouln’t be too much of a problem. eg. On sn1 (for the management node restart only): # cd /opt/mysql/738 # chown -R mysql:mysql . # ls -lrt total 16 drwxr-xr-x. 7 mysql mysql 4096 ago 17 11:40 data drwxr-xr-x. 2 mysql mysql 4096 ago 17 13:01 mgm_data # chown -R mysql:mysql . # ls -lrt total 180 -rw-r--r--. 1 mysql mysql 2496 ene 9 2015 README -rw-r--r--. 1 mysql mysql 17987 ene 9 2015 COPYING -rw-r--r--. 1 mysql mysql 101821 ene 9 2015 INSTALL-BINARY drwxr-xr-x. 4 mysql mysql 4096 ago 16 15:25 include drwxr-xr-x. 2 mysql mysql 4096 ago 16 15:26 bin drwxr-xr-x. 3 mysql mysql 4096 ago 16 15:26 lib drwxr-xr-x. 32 mysql mysql 4096 ago 16 15:26 share drwxr-xr-x. 2 mysql mysql 4096 ago 16 15:26 support-files drwxr-xr-x. 2 mysql mysql 4096 ago 16 15:26 docs drwxr-xr-x. 3 mysql mysql 4096 ago 16 15:26 data drwxr-xr-x. 4 mysql mysql 4096 ago 16 15:26 man drwxr-xr-x. 4 mysql mysql 4096 ago 16 15:26 sql-bench drwxr-xr-x. 2 mysql mysql 4096 ago 16 15:26 scripts drwxr-xr-x. 10 mysql mysql 4096 ago 16 15:26 mysql-test -rw-r--r--. 1 mysql mysql 943 ago 16 16:04 my.cnf -rw-r--r--. 1 mysql mysql 943 ago 16 16:04 my-new.cnf drwxr-xr-x. 2 mysql mysql 4096 ago 16 16:19 conf # pwd /usr/local/mysql Now it’s time to kill the dnode angel process in preparation for MCM to control the processes (otherwise when MCM stops that process, the angel process tries to restart it, out of MCM’s control and that’s when we hit problems): # ps -ef | grep ndb root 3560 1 0 08:42 ? 00:00:02 ndbmtd --ndb-nodeid=3 -c 10.0.0.10 root 3561 3560 8 08:42 ? 00:08:32 ndbmtd --ndb-nodeid=3 -c 10.0.0.10 # kill -9 3560 # ps -ef | grep ndb root 3561 1 8 08:42 ? 00:08:38 ndbmtd --ndb-nodeid=3 -c 10.0.0.10 # ps aux | grep ndb root 3561 8.6 63.9 1007296 474120 ? SLl 08:42 8:38 ndbmtd --ndb-nodeid=3 -c 10.0.0.10 Do on both dnodes. Now we have to adust the .pid files within the dnode’s datadir as it holds the angel process ID: # cd /opt/mysql/738/ndbd_data/ # ls -lrt -rw-r--r--. 1 mysql mysql 4 Aug 17 13:20 ndb_3.pid # sed -i 's/3560/3561/' ndb_3.pid # more ndb_3.pid 3561 Again, to be done on both dnodes. Now for the sqlnodes, we have to rename the actual name of the pid file, not the contents : # cd /opt/mysql/738/data/ # ls -lrt *.pid -rw-rw----. 1 mysql mysql 5 ago 17 08:49 sn1.pid # more *.pid 3755 # ps -ef | grep mysqld | grep -v grep mysql 3755 2975 1 08:49 pts/0 00:02:27 mysqld --defaults-file=/usr/local/mysql/conf/my.cnf --user=mysql --ndb-nodeid=10 Time to copy, not replace, the .pid file to have a cluster-ready naming convention (ndb_.pid): # cp sn1.pid ndb_10.pid On the other sqlnode: # cd /opt/mysql/738/data/ # cp sn2.pid ndb_11.pid Now time to create the MCM-managed Cluster to import into. First up, we’ll need an MCM datadir, to store the datadirs, logs, etc. (if you want to change this later, it’s so much easier from MCM, using the “set” command, so just do it then): # mkdir /opt/mysql/748/mcm_data # cd /opt/mysql/748/mcm_data # chown -R mysql:mysql . As mcmd needs to run as mysql, change permissions of the binaries and also add the manager-directory of your choice: # cd /usr/local/mcm-1.4.0-cluster-7.4.8-linux-glibc2.5-x86-64bit/mcm1.4.0/etc # chgrp -R mysql . # vi mcmd.ini    ..  manager-directory = /opt/mysql/748/mcm_data Let’s make it easier in our env to execute all this: # su - mysql # vi .bash_profile .. export PATH=$PATH:/usr/local/mcm-1.4.0-cluster-7.4.8-linux-glibc2.5-x86-64bit/mcm1.4.0/bin And let’s start MySQL Cluster Manager, i.e. mcmd, as the o.s. user ‘mysql’: # mcmd --defaults-file=/usr/local/mcm-1.4.0-cluster-7.4.8-linux-glibc2.5-x86-64bit/mcm1.4.0/etc/mcmd.ini --daemon Ok. All working fine. Let’s create the cluster to import into. Ok, so I used the o.s. root user to create everything. I suppose I’m used to it, but feel free to do it however you see fit. It won’t make any difference to mcmd as that’s running as mysql so carry on: # sudo -i # export PATH=$PATH:/usr/local/mcm-1.4.0-cluster-7.4.8-linux-glibc2.5-x86-64bit/mcm1.4.0/bin # mcm MCM needs a site with all the hosts that make up the cluster. By the way, if you forget a command, or want to see what else is there, “list commands;”: mcm> create site --hosts=10.0.0.10,10.0.0.11,10.0.0.12,10.0.0.13 mysite; Add the path to the cluster binaries that we’re using now: mcm> add package --basedir=/usr/local/mysql-cluster-gpl-7.3.8-linux-glibc2.5-x86_64 cluster738; A little test here, thinking, “if mcm is so clever, maybe it can detect that we’re in 7.3.8 and we can use 7.4.8 and create the cluster in that version to import into, and we’ve upgraded and imported in one foul swoop!“. Alas, although that’s a nice idea, but after creating the cluster with the 748 package for import, and adding processes / nodes to the cluster, upon running the dryrun config check, it errors out : mcm> add package --basedir=/usr/local/mcm-1.4.0-cluster-7.4.8-linux-glibc2.5-x86-64bit/cluster cluster748; mcm> import config --dryrun mycluster; ERROR 5307 (00MGR): Imported process version 7.3.8 does not match configured process mysqld 11 version 7.4.8 for cluster mycluster So, back to the 7.3.8 binaries / package: mcm> add package --basedir=/usr/local/mysql-cluster-gpl-7.3.8-linux-glibc2.5-x86_64 cluster738; mcm> create cluster --import --package=cluster738 --processhosts=ndb_mgmd:1@10.0.0.10,ndbmtd:3@10.0.0.12,ndbmtd:4@10.0.0.13, mysqld:10@10.0.0.10,mysqld:11@10.0.0.11,ndbapi:12@*,ndbapi:13@*,ndbapi:14@* mycluster; Now, this seems simple right? Ok, well it is, but you have to match the processhosts to those that appear in the “ndb_mgm -e show” out put, i.e. ndbapi/mysqld api entries that all appear there. So if you have 8 rows returned from that, you’ll need 8 entries in the –processhosts option. It will complain otherwise. show status -r mycluster; +--------+----------+-----------+--------+-----------+------------+ | NodeId | Process | Host | Status | Nodegroup | Package | +--------+----------+-----------+--------+-----------+------------+ | 1 | ndb_mgmd | 10.0.0.10 | import | | cluster738 | | 3 | ndbmtd | 10.0.0.12 | import | n/a | cluster738 | | 4 | ndbmtd | 10.0.0.13 | import | n/a | cluster738 | | 10 | mysqld | 10.0.0.10 | import | | cluster738 | | 11 | mysqld | 10.0.0.11 | import | | cluster738 | | 12 | ndbapi | * | import | | | | 13 | ndbapi | * | import | | | | 14 | ndbapi | * | import | | | +--------+----------+-----------+--------+-----------+------------+ 8 rows in set (0,05 sec) Let’s do a test now we’ve got the 7.3.8 binaries assigned to the site and a shell cluster created: mcm> import config --dryrun mycluster; ERROR 5321 (00MGR): No permissions for pid 3700 running on sn1 This means that processes are being executed / run by someone who isn’t the mcmd user, eg. root. Now I need to go to each process and restart it as mysql (kill angel processes, etc.). Also remember that mcmd can’t be run by root. As we fixed that at the beginning of this post (DIDN’T WE?) Well, I hope you don’t get that one. +---------------------------------------------------------------------------+ | Command result | +---------------------------------------------------------------------------+ | Import checks passed. Please check log for settings that will be applied. | +---------------------------------------------------------------------------+ 1 row in set (5.55 sec) Now that all the checks are passed, it leads me to think: what about all the personalized config that I have in the config.ini & my.cnf files. Well, we could run: mcm> set DataMemory:ndbmtd=20M, IndexMemory:ndbmtd=10M, DiskPageBufferMemory:ndbmtd=4M, StringMemory:ndbmtd=5, MaxNoOfConcurrentOperations:ndbmtd=2K, MaxNoOfConcurrentTransactions:ndbmtd=2K, SharedGlobalMemory:ndbmtd=500K, MaxParallelScansPerFragment:ndbmtd=16, MaxNoOfAttributes:ndbmtd=100, MaxNoOfTables:ndbmtd=20, MaxNoOfOrderedIndexes:ndbmtd=20, HeartbeatIntervalDbDb:ndbmtd=500, HeartbeatIntervalDbApi:ndbmtd=500, TransactionInactiveTimeout:ndbmtd=500, LockPagesInMainMemory:ndbmtd=1, ODirect:ndbmtd=1, MaxNoOfExecutionThreads:ndbmtd=4, RedoBuffer:ndbmtd=32M mycluster ; +-----------------------------------+ | Command result | +-----------------------------------+ | Cluster reconfigured successfully | +-----------------------------------+ 1 row in set (0.60 sec) But there is no need, because MCM will do all that for you. As the refman mentions, we’ll need to go to the MCM manager directory and it’s in there: # cd /opt/mysql/748/mcm_data/clusters/mycluster/tmp # ls -lrt -rw-rw-rw-. 1 mysql mysql 3284 Aug 17 13:43 import_config.a6905b23_225_3.mcm mcm> import config mycluster; +--------------------------------------------------------------------------------------------+ | Command result | +--------------------------------------------------------------------------------------------+ | Configuration imported successfully. Please manually verify the settings before proceeding | +--------------------------------------------------------------------------------------------+ 1 row in set (5.58 sec) Now to import: mcm> import cluster mycluster; +-------------------------------+ | Command result | +-------------------------------+ | Cluster imported successfully | +-------------------------------+ 1 row in set (3.04 sec) Let’s make sure the Status has changed from import: mcm> show status -r mycluster; +--------+----------+-----------+---------+-----------+------------+ | NodeId | Process | Host | Status | Nodegroup | Package | +--------+----------+-----------+---------+-----------+------------+ | 1 | ndb_mgmd | 10.0.0.10 | running | | cluster738 | | 3 | ndbmtd | 10.0.0.12 | running | 0 | cluster738 | | 4 | ndbmtd | 10.0.0.13 | running | 0 | cluster738 | | 10 | mysqld | 10.0.0.10 | running | | cluster738 | | 11 | mysqld | 10.0.0.11 | running | | cluster738 | | 12 | ndbapi | * | added | | | | 13 | ndbapi | * | added | | | | 14 | ndbapi | * | added | | | +--------+----------+-----------+---------+-----------+------------+ 8 rows in set (0.06 sec) I know you’re probably eager to see if MCM 1.4 autotune works for 7.3 NDB’s. Well, it doesn’t I’m afraid: mcm> autotune --dryrun --writeload=low realtime mycluster; ERROR 5402 (00MGR): Autotuning is not supported for cluster version 7.3.8 Upgrade time: mcm> list packages mysite; +------------+---------------------------------------------------------------------+-----------------------------------------+ | Package | Path | Hosts | +------------+---------------------------------------------------------------------+-----------------------------------------+ | cluster738 | /usr/local/mysql-cluster-gpl-7.3.8-linux-glibc2.5-x86_64 | 10.0.0.10,10.0.0.11,10.0.0.12,10.0.0.13 | +------------+---------------------------------------------------------------------+-----------------------------------------+ mcm> add package --basedir=/usr/local/mcm-1.4.0-cluster-7.4.8-linux-glibc2.5-x86-64bit/cluster cluster748; mcm> list packages mysite; +------------+---------------------------------------------------------------------+-----------------------------------------+ | Package | Path | Hosts | +------------+---------------------------------------------------------------------+-----------------------------------------+ | cluster738 | /usr/local/mysql-cluster-gpl-7.3.8-linux-glibc2.5-x86_64 | 10.0.0.10,10.0.0.11,10.0.0.12,10.0.0.13 | | cluster748 | /usr/local/mcm-1.4.0-cluster-7.4.8-linux-glibc2.5-x86-64bit/cluster | 10.0.0.10,10.0.0.11,10.0.0.12,10.0.0.13 | +------------+---------------------------------------------------------------------+-----------------------------------------+ mcm> upgrade cluster --package=cluster748 mycluster; In another window: mcm> show status -r mycluster; +--------+----------+-----------+---------+-----------+------------+ | NodeId | Process | Host | Status | Nodegroup | Package | +--------+----------+-----------+---------+-----------+------------+ | 1 | ndb_mgmd | 10.0.0.10 | running | | cluster748 | | 3 | ndbmtd | 10.0.0.12 | stopped | 0 | cluster738 | | 4 | ndbmtd | 10.0.0.13 | running | 0 | cluster738 | | 10 | mysqld | 10.0.0.10 | running | | cluster738 | | 11 | mysqld | 10.0.0.11 | running | | cluster738 | | 12 | ndbapi | * | added | | | | 13 | ndbapi | * | added | | | | 14 | ndbapi | * | added | | | +--------+----------+-----------+---------+-----------+------------+ 8 rows in set (0.05 sec) Ok, all going well: mcm> show status -r mycluster; +--------+----------+-----------+----------+-----------+------------+ | NodeId | Process | Host | Status | Nodegroup | Package | +--------+----------+-----------+----------+-----------+------------+ | 1 | ndb_mgmd | 10.0.0.10 | running | | cluster748 | | 3 | ndbmtd | 10.0.0.12 | running | 0 | cluster748 | | 4 | ndbmtd | 10.0.0.13 | starting | 0 | cluster748 | | 10 | mysqld | 10.0.0.10 | running | | cluster738 | | 11 | mysqld | 10.0.0.11 | running | | cluster738 | | 12 | ndbapi | * | added | | | | 13 | ndbapi | * | added | | | | 14 | ndbapi | * | added | | | +--------+----------+-----------+----------+-----------+------------+ 8 rows in set (0.08 sec) But, life is never as good as it is in fairy tales: ERROR 7006 (00MGR): Process error: Node 10 : 17-08-17 17:18:13 4449 [Note] NDB Util: Starting... 2017-08-17 17:18:13 4449 [Note] NDB Util: Wait for server start completed 2017-08-17 17:18:13 4449 [ERROR] Aborting 2017-08-17 17:18:13 4449 [Note] Binlog end 2017-08-17 17:18:13 4449 [Note] NDB Util: Stop 2017-08-17 17:18:13 4449 [Note] NDB Util: Wakeup 2017-08-17 17:18:13 4449 [Note] NDB Index Stat: Starting... 2017-08-17 17:18:13 4449 [Note] NDB Index Stat: Wait for server start completed mcm> show status -r mycluster; +--------+----------+-----------+---------+-----------+------------+ | NodeId | Process | Host | Status | Nodegroup | Package | +--------+----------+-----------+---------+-----------+------------+ | 1 | ndb_mgmd | 10.0.0.10 | running | | cluster748 | | 3 | ndbmtd | 10.0.0.12 | running | 0 | cluster748 | | 4 | ndbmtd | 10.0.0.13 | running | 0 | cluster748 | | 10 | mysqld | 10.0.0.10 | failed | | cluster748 | | 11 | mysqld | 10.0.0.11 | running | | cluster738 | | 12 | ndbapi | * | added | | | | 13 | ndbapi | * | added | | | | 14 | ndbapi | * | added | | | +--------+----------+-----------+---------+-----------+------------+ 8 rows in set (0.05 sec) On sn1, the error log mysqld_738.err reads: 2017-08-17 17:25:20 4518 [Note] NDB Util: Wait for server start completed 2017-08-17 17:25:20 4518 [ERROR] Aborting The mcmd.log: 2017-08-17 17:25:20 4518 [Note] NDB Util: Stop 2017-08-17 17:25:20 4518 [Note] NDB Util: Wakeup 2017-08-17 17:25:20 4518 [Note] NDB Index Stat: Starting... 2017-08-17 17:25:20 4518 [Note] NDB Index Stat: Wait for server start completed 2017-08-17 17:25:24.452: (message) [T0x1b1a050 CMGR ]: Got new message mgr_cluster_process_status {a6905b23 396 0} 10 failed 2017-08-17 17:25:24.457: (message) [T0x1b1a050 CMGR ]: Got new message x_trans {a6905b23 397 0} abort_trans pc=19 2017-08-17 17:25:24.459: (message) [T0x1b1a050 CMGR ]: Got new message mgr_process_operationstatus {0 0 0} 10 failed 2017-08-17 17:25:24.461: (message) [T0x1b1a050 CMGR ]: req_id 80 Operation finished with failure for configversion {a6905b23 385 3} 2017-08-17 17:25:24.466: (warning) [T0x1b1a050 CMGR ]: Operation failed : 7006 Process error: Node 10 : 17-08-17 17:25:20 4518 [Note] NDB Util: Starting... 2017-08-17 17:25:20 4518 [Note] NDB Util: Wait for server start completed 2017-08-17 17:25:20 4518 [ERROR] Aborting And reviewing the my.cnf, the following needed to be changed as they reference the old binaries. But most importantly, StopOnError=0 is required. That was my gotcha! set lc_messages_dir:mysqld:10=/usr/local/mcm-1.4.0-cluster-7.4.8-linux-glibc2.5-x86-64bit/cluster/share mycluster; set lc_messages_dir:mysqld:11=/usr/local/mcm-1.4.0-cluster-7.4.8-linux-glibc2.5-x86-64bit/cluster/share mycluster; set StopOnError:ndbmtd=0 mycluster; This last command restarts the cluster, without upgrading it, leaving us: mcm> show status -r mycluster; +--------+----------+-----------+---------+-----------+------------+ | NodeId | Process | Host | Status | Nodegroup | Package | +--------+----------+-----------+---------+-----------+------------+ | 1 | ndb_mgmd | 10.0.0.10 | running | | cluster748 | | 3 | ndbmtd | 10.0.0.12 | running | 0 | cluster748 | | 4 | ndbmtd | 10.0.0.13 | running | 0 | cluster748 | | 10 | mysqld | 10.0.0.10 | running | | cluster748 | | 11 | mysqld | 10.0.0.11 | running | | cluster748 | | 12 | ndbapi | * | added | | | | 13 | ndbapi | * | added | | | | 14 | ndbapi | * | added | | | +--------+----------+-----------+---------+-----------+------------+ 8 rows in set (0.05 sec) Looks like it’s upgraded. We know it hasn’t been able to run the checks and upgrade process, so let’s do it properly (and remember in the future that we need to review our config.ini and params): mcm> upgrade cluster --package=cluster748 mycluster; +-------------------------------+ | Command result | +-------------------------------+ | Cluster upgraded successfully | +-------------------------------+ 1 row in set (1 min 53.72 sec) Whilst MCM upgrades, it normally restarts each process in it’s correct order and one by one, on it’s own, without any need for human intervention. However, as the upgrade process had previously been run, and failed at the sqlnode step, this still needed to be carried out, hence, when it stopped and started each sqlnode, it did it twice, ensuring that the changed configuration we adjusted is reflected into the MCM config. mcm> show status -r mycluster; +--------+----------+-----------+---------+-----------+------------+ | NodeId | Process | Host | Status | Nodegroup | Package | +--------+----------+-----------+---------+-----------+------------+ | 1 | ndb_mgmd | 10.0.0.10 | running | | cluster748 | | 3 | ndbmtd | 10.0.0.12 | running | 0 | cluster748 | | 4 | ndbmtd | 10.0.0.13 | running | 0 | cluster748 | | 10 | mysqld | 10.0.0.10 | running | | cluster748 | | 11 | mysqld | 10.0.0.11 | running | | cluster748 | | 12 | ndbapi | * | added | | | | 13 | ndbapi | * | added | | | | 14 | ndbapi | * | added | | | +--------+----------+-----------+---------+-----------+------------+ 8 rows in set (0.01 sec) And we’re done. Imported from the wild into an mcm-managed env, and upgraded with mcm. So much simpler. Happy mcm’ing!
  2. Dear MySQL users, MySQL Connector/Net 6.10.3 rc is the fourth release which supports Scaffold-DbContext, that enables the creation of corresponding model classes from an existing database that are compatible with Entity Framework (EF) Core 1.1. To download MySQL Connector/Net 6.10.3 rc, see the “Development Releases” tab at http://dev.mysql.com/downloads/connector/net/ Changes in MySQL Connector/Net 6.10.3 (2017-08-18, Release Candidate) Known limitation of this release: The use of the SSL protocol is restricted to TCP connections. Connections using Unix sockets, named pipes, and shared memory do not support SSL mode. Functionality Added or Changed * The following methods are available for use with EF Core in asynchronous command and connection operations: + Microsoft.EntityFrameworkCore.DbContext.AddAsync + Microsoft.EntityFrameworkCore.DbContext.AddRangeAsync + Microsoft.EntityFrameworkCore.DbContext.FindAsync + Microsoft.EntityFrameworkCore.DbContext.SaveChangesAsync + Microsoft.EntityFrameworkCore.Infrastructure. DatabaseFacade.EnsureDeletedAsync + Microsoft.EntityFrameworkCore.Infrastructure. DatabaseFacade.EnsureCreatedAsync + Microsoft.EntityFrameworkCore.DbContext.ToListAsync * Support for connections using Unix sockets was extended to include MySQL servers deployed on the Linux host operating system in .NET Core scenarios. To specify a Unix socket connection, set the value of the Server connection-string option to the path of the socket file and the Protocol option to unix. For example: "server=/path/to/socket;protocol=unix;user=root;password=mypass" * The AutoEnlist and IncludeSecurityAsserts connection-string options are not appropriate for use by applications that target .NET Core and now return an error when used. * EF Core: Support for explicit loading was added. Explicit loading is an object-relational mapper (O/RM) pattern introduced in EF Core 1.1.0, which enables .NET developers to explicitly load related data from the database at a later time. * EF Core: Support for scaffolding a DbContext from multiple databases was added. With multiple databases (or schemas of tables) specified, the resulting entity classes are created within a single context. EF Core CLI usage example: dotnet ef dbcontext scaffold "connection-string" MySql.Data.EntityFrameworkCore --schema world --schema sakila Package Manager Console (Visual Studio) usage example: Scaffold-DbContext "connection-string" MySql.Data.EntityFrameworkCore -Schemas world,sakila * The following connection-string options are not currently supported for use by applications that target .NET Core and now return an error when used: + SharedMemoryName + IntegratedSecurity + PipeName + Logging + UseUsageAdvisor + UsePerformanceMonitor + InteractiveSession + Replication Bugs Fixed * EF Core: Some methods in the DbContext class were not supported for use with asynchronous operations. (Bug #26448321, Bug #84814) Nuget packages are available at: https://www.nuget.org/packages/MySql.Data/6.10.3-rchttps://www.nuget.org/packages/MySql.Web/6.10.3-rchttps://www.nuget.org/packages/MySql.Data.Entity/6.10.3-rchttps://www.nuget.org/packages/MySql.Data.EntityFrameworkCore/6.10.3-rchttps://www.nuget.org/packages/MySql.Data.EntityFrameworkCore.Design/6.10.3-rc Enjoy and thanks for the support! On behalf of the MySQL Release Team, Nawaz Nazeer Ahamed
  3. Join Percona Chief Evangelist Colin Charles as he covers happenings, gives pointers and provides musings on the open source database community. Has a week passed already? Welcome back to the second column. A lot of time has been spent neck deep in getting speakers accepted and scheduled for Percona Live Open Source Database Conference Europe 2017 in Dublin, as well organizing the conference sponsors. Percona Live Europe Dublin At the time of writing, we are six weeks away from the conference, so a little over a month! Have you registered yet? We have 12 tutorials that cover a wide range of topics: ProxySQL (from the author Rene Cannao), Orchestrator (from the author Shlomi Noach), practical Couchbase (to name a few). If we did a technology word cloud, the coverage includes MongoDB, Docker, Elastic, Percona Monitoring and Management (PMM), Percona XtraDB Cluster 5.7, MySQL InnoDB Cluster and Group Replication. In addition to that, if you’re a MySQL beginner (or thinking of a career change) there is a six-hour boot camp titled MySQL in a Nutshell (Part 1 and Part 2)!. Come prepared with your laptop, and leave a MySQL DBA! Sessions are scheduled, and most of the content is already online: check out day 1, and day 2. We have 104 sessions scheduled, so there’s plenty to choose from. Remember that you have till 7:00 a.m. UTC-1, August 16th, 2017 to book the group rate at the event venue for €250/night. Use code PERCON. Releases orchestrator/raft: Pre-release 3.0 is available. I’m a huge fan of Orchestrator, and now you can setup high availability for orchestrator via the Raft consensus protocol. MariaDB 10.0.32 is out, and it comes with a new Percona XtraDB, Percona TokuDB and a new InnoDB. You’ll want this release if you’re using TokuDB, as it merges from TokuDB 5.6.36-82.1 (which fixes the two issues problem). If you encountered the TokuDB problems above, you’ll want to look at MariaDB 10.1.26. One surprise hidden in the release notes: MariaDB Backup is now a stable/GA release. Have you used it yet? Link List dbKoda is a next generation database development and administration tool now available for MongoDB, from Guy Harrison’s company (of MySQL Stored Procedure Programming book fame). An Adventure in InnoDB Table Compression (for read-only tables). If you read Korean, there’s an interview with me in their press. In MariaDB 10.2, you really want to remove any XtraDB options you have in your my.cnf. This includes options like innodb_flush_method = ALL_O_DIRECT. Kristian Nielsen writes passionately about MariaDB Server’s InnoDB merges, and how a merge error introduced extra fsync() calls. If you’re interested in how the sausage is made, some interesting questions were asked as of late: How was this records/fanout logic derived for the “no statistics” case in MySQL’s Query Planner? (and a little more). In addition, what is the history of lf_hash (with the associated paper “Split-Ordered Lists: Lock-Free Extensible Hash Tables” by Ori Shalev and Nir Shavit that you can easily search for). I look forward to feedback/tips via e-mail at colin.charles@percona.com or I’m @bytebot on Twitter.
  4. Dear MySQL users, MySQL Connector/Python 2.1.7 GA is a fifth GA version of 2.1 release series of the pure Python database driver for MySQL. It can be used for production environments. MySQL Connector/Python version 2.1.7 GA is compatible with MySQL Server versions 5.5 and greater. Python 2.6 and greater as well as Python 3.4 and greater are supported. Python 2.4, 2.5, and 3.1, 3.2, 3.3 are not supported. MySQL Connector/Python 2.1.7 is available for download from: http://dev.mysql.com/downloads/connector/python/#downloads MySQL Connector/Python 2.1.7 (Commercial) will be available for download on the My Oracle Support (MOS) website. This release will be available on eDelivery (OSDC) in next month’s upload cycle. The ChangeLog file included in the distribution contains a brief summary of changes in MySQL Connector/Python 2.1.7. For a more complete list of changes, see below or online at: http://dev.mysql.com/doc/relnotes/connector-python/en/ Enjoy! Changes in MySQL Connector/Python 2.1.7 (2017-08-18, General Availability) * Functionality Added or Changed * Bugs Fixed Functionality Added or Changed * Connector/Python is now compatible with Django 1.11. Thanks to Brian Tyndall for the patch for some of the work. (Bug #25965922, Bug #86105, Bug #26257499, Bug#86652) * Connector/Python now attempts to connect using TLS by default if the server supports secure connections. (Bug#21947091) Bugs Fixed * Prepared statements did not work with a MySQL 8.0 server. (Bug #26376334) * With a connection character set of utf8mb4, multiple-row insert operations failed with an error of LookupError: unknown encoding: utf8mb4. (Bug #24659561, Bug #82948) * Creating a Connection instance with use_pure=True could lead to the underlying socket not being closed if the user name or password were invalid. Thanks to Vilnis Termanis for the patch. (Bug #24342757, Bug #82324) * For cursors created with named_tuple=True, MySQLCursorNamedTuple objects could leak memory. Thanks to Piotr Jurkiewicz for the patch on which this fix was based. (Bug #22880163, Bug #80621) * The C Extension leaked memory if used to execute INSERT statements that inserted Unicode strings. (Bug #22825962, Bug #79887) * The escape_string() method leaked memory. (Bug #22810810, Bug #79903) * With Python 2.x, for a call to encode('utf8') on a bytestring that was serialized from unicode, Python attempted to decode the string using the 'ascii' codec and encode it back using 'utf8'. The result was encoding failure for bytestrings that contain non-ASCII characters. (Bug #22564149, Bug #79993) Documentation ——————– Online:http://dev.mysql.com/doc/connector-python/en/index.html The source distribution includes the manual in various formats under the docs/ folder. Reporting Bugs ——————– We welcome and appreciate your feedback and bug reports:http://bugs.mysql.com/ On Behalf of the MySQL/ORACLE RE Team, Gipson Pulla
  5. This Log Buffer Edition covers from various blogs covering the technologies and news of Oracle, SQL Server and MySQL. Oracle: Storage Server: datasets, snapshots and performance A Different Cause for enq: TM – contention ODA X6-2M – How to create your own ACFS file system Oracle New Version Numbering KBHS-00600: Internal Error, Arguments [1] [kbhshtCreateDataBucket] Error During Backup To Oracle Cloud SQL Server: Using Database Master Keys in SQL Server SQL Server Management Studio Tips Sqlcmd is not just a script execution tool. It gives us access to internal variables and commands that we can use to automate code execution. Additional SERVERPROPERTY properties in SQL Server Simple SQL: Random Thoughts My SQL: The danger of no Primary Key when replicating in RBR (and a partial protection with MariaDB 10.1) What’s New With MySQL Replication in MySQL 8.0 Docker Secrets and MySQL Password Management More Details about InnoDB Compression Levels (innodb_compression_level) Extending the Power of MariaDB ColumnStore with User Defined Functions