Thursday, July 2, 2015

Caused by error 2341: 'Internal program error (failed ndbrequire)(Internal error...

Symptom: (mysql-5.6.24 with ndb-7.4.6)


[mysql@localhost ndb_data]$ tail -10 ndb_11_out.log
2015-07-02 15:08:42 [ndbd] INFO     -- Grant nodes to start phase: 5, nodes: 0000000000001800
2015-07-02 15:08:42 [ndbd] INFO     -- NDB start phase 4 completed
2015-07-02 15:08:42 [ndbd] INFO     -- As master we will wait for other nodes to reach the state waitpoint52 as well
2015-07-02 15:08:42 [ndbd] INFO     -- Node 12 have reached completion of NDB start phase 4
2015-07-02 15:08:42 [ndbd] INFO     -- Start NDB start phase 5 (only to DBDIH)
2015-07-02 15:08:47 [ndbd] INFO     -- /export/home/pb2/build/sb_0-14878975-1427908966.28/mysql-cluster-com-7.4.6/storage/ndb/src/kernel/blocks/backup/Backup.cpp
2015-07-02 15:08:47 [ndbd] INFO     -- BACKUP (Line: 3846) 0x00000002
2015-07-02 15:08:47 [ndbd] INFO     -- Error handler shutting down system
2015-07-02 15:08:47 [ndbd] INFO     -- Error handler shutdown completed - exiting
2015-07-02 15:08:47 [ndbd] ALERT    -- Node 11: Forced node shutdown completed. Occured during startphase 5. Caused by error 2341: 'Internal program error (failed ndbrequire)(Internal error, programming error or missing error message, please report a bug). Temporary error, restart node'.



[mysql@localhost ndb_data]$ more ndb_11_error.log
Current byte-offset of file-pointer is: 1067

Time: Thursday 2 July 2015 - 15:08:47
Status: Temporary error, restart node
Message: Internal program error (failed ndbrequire) (Internal error, programming error or missing error message, please report a bug)
Error: 2341
Error data: Backup.cpp
Error object: BACKUP (Line: 3846) 0x00000002
Program: ndbd
Pid: 25840
Version: mysql-5.6.24 ndb-7.4.6
Trace: /var/local/mysql/server5-ndb/ndb_data/ndb_11_trace.log.2 [t1..t1]
***EOM***

Root Cause:

And I found it was caused by some files inside "datadir" not owned by "mysql" any more, it was changed to "root".
After change permission back to "mysql", this problem resolved. 

[mysql@localhost server2-ndb]$ find . -exec ls -l {} \; |grep root
-rw-r--r--. 1 root  root   1244 Jul  1 15:11 T52F0.Data
-rw-r--r--. 1 root  root   1244 Jul  1 15:11 T52F1.Data
-rw-r--r--. 1 root root 1244 Jul  1 15:11 ./ndb_data/ndb_11_fs/LCP/1/T52F1.Data
-rw-r--r--. 1 root root 1244 Jul  1 15:11 ./ndb_data/ndb_11_fs/LCP/1/T52F0.Data


http://bugs.mysql.com/bug.php?id=70217

Tuesday, June 30, 2015

2015-06-30 21:19:47 [MgmtSrvr] WARNING -- Failed to allocate nodeid for API at 192.168.1.200. Returned error: 'No free node id found for mysqld(API).'

Symptom:

2015-06-30 21:19:47 [MgmtSrvr] WARNING  -- Failed to allocate nodeid for API at 192.168.1.200. Returned error: 'No free node id found for mysqld(API).'
2015-06-30 21:19:47 [MgmtSrvr] WARNING  -- Failed to allocate nodeid for API at 192.168.1.200. Returned error: 'No free node id found for mysqld(API).'
2015-06-30 21:19:47 [MgmtSrvr] INFO     -- Node 11: Communication to Node 21 opened
2015-06-30 21:19:47 [MgmtSrvr] INFO     -- Node 11: Communication to Node 22 opened
2015-06-30 21:19:47 [MgmtSrvr] INFO     -- Node 12: Prepare arbitrator node 1 [ticket=53b7000181e3e3ab]
2015-06-30 21:19:47 [MgmtSrvr] WARNING  -- Failed to allocate nodeid for API at 192.168.1.200. Returned error: 'No free node id found for mysqld(API).'
2015-06-30 21:19:47 [MgmtSrvr] WARNING  -- Failed to allocate nodeid for API at 192.168.1.200. Returned error: 'No free node id found for mysqld(API).'
2015-06-30 21:19:47 [MgmtSrvr] INFO     -- Node 11: Started arbitrator node 1 [ticket=53b7000181e3e3ab]
2015-06-30 21:19:47 [MgmtSrvr] WARNING  -- Failed to allocate nodeid for API at 192.168.1.200. Returned error: 'No free node id found for mysqld(API).'
2015-06-30 21:19:47 [MgmtSrvr] WARNING  -- Failed to allocate nodeid for API at 192.168.1.200. Returned error: 'No free node id found for mysqld(API).' - Repeated 2 times

Root Causes:

Possibility 1) Not able [mysqld] defined in config.ini for mysql API node.

Possibility 2) MySQLD startup too faster before following lines appear in the ndb_mgmd log:

2015-06-30 22:23:57 [MgmtSrvr] INFO     -- Node 11: Started (mysql-5.6.24 ndb-7.4.6)
2015-06-30 22:23:57 [MgmtSrvr] INFO     -- Node 12: Started (mysql-5.6.24 ndb-7.4.6)
2015-06-30 22:23:57 [MgmtSrvr] INFO     -- Node 12: Node 1: API mysql-5.6.24 ndb-7.4.6
2015-06-30 22:23:57 [MgmtSrvr] INFO     -- Node 11: Node 1: API mysql-5.6.24 ndb-7.4.6
2015-06-30 22:23:57 [MgmtSrvr] INFO     -- Node 12: Communication to Node 21 opened
2015-06-30 22:23:57 [MgmtSrvr] INFO     -- Node 12: Communication to Node 22 opened
2015-06-30 22:23:57 [MgmtSrvr] INFO     -- Node 12: Prepare arbitrator node 1 [ticket=55fa0001821ea407]
2015-06-30 22:23:57 [MgmtSrvr] INFO     -- Node 11: Started arbitrator node 1 [ticket=55fa0001821ea407]
2015-06-30 22:23:58 [MgmtSrvr] INFO     -- Node 11: Communication to Node 21 opened
2015-06-30 22:23:58 [MgmtSrvr] INFO     -- Node 11: Communication to Node 22 opened

Distributed MySQL Privileges for MySQL Cluster

MySQL Cluster supports distribution of MySQL users and privileges across all SQL nodes in a MySQL Cluster. This support is not enabled by default; you should follow the procedure outlined in this section in order to do so.

[mysql@myhost1 mysqlc]$ mysql -u root -S ../server1-api/mysql.sock
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 2
Server version: 5.6.24-ndb-7.4.6-cluster-commercial-advanced MySQL Cluster Server - Advanced Edition (Commercial)

Copyright (c) 2000, 2015, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

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

mysql> SELECT ROUTINE_NAME, ROUTINE_SCHEMA, ROUTINE_TYPE
    ->     FROM INFORMATION_SCHEMA.ROUTINES
    ->     WHERE ROUTINE_NAME LIKE 'mysql_cluster%'
    ->     ORDER BY ROUTINE_TYPE;
Empty set (0.06 sec)

mysql> SELECT TABLE_NAME, ENGINE FROM INFORMATION_SCHEMA.TABLES
    -> WHERE TABLE_SCHEMA = 'mysql' AND TABLE_NAME LIKE '%backup'
    -> ORDER BY ENGINE;
Empty set (0.01 sec)

mysql> source share/ndb_dist_priv.sql
Query OK, 0 rows affected, 1 warning (0.01 sec)

Query OK, 0 rows affected, 1 warning (0.00 sec)

Query OK, 0 rows affected, 1 warning (0.00 sec)

Query OK, 0 rows affected, 1 warning (0.00 sec)

Query OK, 0 rows affected, 1 warning (0.00 sec)

Query OK, 0 rows affected, 1 warning (0.00 sec)

Query OK, 0 rows affected, 1 warning (0.00 sec)

Query OK, 0 rows affected (0.02 sec)

Query OK, 0 rows affected (0.02 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

mysql> SELECT ROUTINE_NAME, ROUTINE_SCHEMA, ROUTINE_TYPE
    ->     FROM INFORMATION_SCHEMA.ROUTINES
    ->     WHERE ROUTINE_NAME LIKE 'mysql_cluster%'
    ->     ORDER BY ROUTINE_TYPE;
+---------------------------------------------+----------------+--------------+
| ROUTINE_NAME                                | ROUTINE_SCHEMA | ROUTINE_TYPE |
+---------------------------------------------+----------------+--------------+
| mysql_cluster_privileges_are_distributed    | mysql          | FUNCTION     |
| mysql_cluster_backup_privileges             | mysql          | PROCEDURE    |
| mysql_cluster_move_grant_tables             | mysql          | PROCEDURE    |
| mysql_cluster_move_privileges               | mysql          | PROCEDURE    |
| mysql_cluster_restore_local_privileges      | mysql          | PROCEDURE    |
| mysql_cluster_restore_privileges            | mysql          | PROCEDURE    |
| mysql_cluster_restore_privileges_from_local | mysql          | PROCEDURE    |
+---------------------------------------------+----------------+--------------+
7 rows in set (0.00 sec)

mysql> CALL mysql.mysql_cluster_move_privileges();
2015-06-30 20:49:00 21146 [Note] NDB Binlog: RENAME Event: REPL$mysql/ndb_user_backup
2015-06-30 20:49:02 21146 [Note] NDB Binlog: RENAME Event: REPL$mysql/ndb_db_backup
2015-06-30 20:49:03 21146 [Note] NDB Binlog: RENAME Event: REPL$mysql/ndb_tables_priv_backup
2015-06-30 20:49:04 21146 [Note] NDB Binlog: RENAME Event: REPL$mysql/ndb_columns_priv_backup
2015-06-30 20:49:05 21146 [Note] NDB Binlog: RENAME Event: REPL$mysql/ndb_procs_priv_backup
2015-06-30 20:49:06 21146 [Note] NDB Binlog: RENAME Event: REPL$mysql/ndb_proxies_priv_backup
Query OK, 2 rows affected (15.04 sec)

mysql> SELECT CONCAT(
    ->    'Conversion ',
    ->    IF(mysql.mysql_cluster_privileges_are_distributed(), 'succeeded', 'failed'),
    ->    '.')
    ->    AS Result;
+-----------------------+
| Result                |
+-----------------------+
| Conversion succeeded. |
+-----------------------+
1 row in set (0.01 sec)

mysql> SELECT TABLE_NAME, ENGINE FROM INFORMATION_SCHEMA.TABLES
    -> WHERE TABLE_SCHEMA = 'mysql' AND TABLE_NAME LIKE '%backup'
    -> ORDER BY ENGINE;
+-------------------------+------------+
| TABLE_NAME              | ENGINE     |
+-------------------------+------------+
| db_backup               | MyISAM     |
| user_backup             | MyISAM     |
| columns_priv_backup     | MyISAM     |
| tables_priv_backup      | MyISAM     |
| proxies_priv_backup     | MyISAM     |
| procs_priv_backup       | MyISAM     |
| ndb_columns_priv_backup | ndbcluster |
| ndb_user_backup         | ndbcluster |
| ndb_tables_priv_backup  | ndbcluster |
| ndb_proxies_priv_backup | ndbcluster |
| ndb_procs_priv_backup   | ndbcluster |
| ndb_db_backup           | ndbcluster |
+-------------------------+------------+
12 rows in set (0.00 sec)


[mysql@myhost1 mysqlc]$ mysql -u root -S ../server2-api/mysql.sock
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 5
Server version: 5.6.24-ndb-7.4.6-cluster-commercial-advanced MySQL Cluster Server - Advanced Edition (Commercial)

Copyright (c) 2000, 2015, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

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

mysql> select table_name,engine from information_schema.tables where table_schema='mysql' and engine='ndbcluster';
+-------------------------+------------+
| table_name              | engine     |
+-------------------------+------------+
| columns_priv            | ndbcluster |
| db                      | ndbcluster |
| ndb_apply_status        | ndbcluster |
| ndb_columns_priv_backup | ndbcluster |
| ndb_db_backup           | ndbcluster |
| ndb_index_stat_head     | ndbcluster |
| ndb_index_stat_sample   | ndbcluster |
| ndb_procs_priv_backup   | ndbcluster |
| ndb_proxies_priv_backup | ndbcluster |
| ndb_tables_priv_backup  | ndbcluster |
| ndb_user_backup         | ndbcluster |
| procs_priv              | ndbcluster |
| proxies_priv            | ndbcluster |
| tables_priv             | ndbcluster |
| user                    | ndbcluster |
+-------------------------+------------+
15 rows in set (0.00 sec)

mysql> quit
Bye

Saturday, June 27, 2015

Create Window Native Authentication MySQL Users

Ensure following plugin has been loaded via my.ini:

# Enable Windows Authentication
plugin-load=authentication_windows.dll

Create MySQL Users
C:\Users\Administrator>mysql -u donghua -p
Enter password: ********
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 25
Server version: 5.6.25-enterprise-commercial-advanced-log MySQL Enterprise Server - Advanced Edition (Commercial)

Copyright (c) 2000, 2015, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

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

mysql> CREATE USER Administrator
    ->   IDENTIFIED WITH authentication_windows
    ->   AS 'Administrators';
Query OK, 0 rows affected (0.00 sec)

mysql> grant all on *.* to Administrator@'%';
Query OK, 0 rows affected (0.00 sec)

mysql> exit
Bye

Verify MySQL Users

C:\Users\Administrator>mysql --user=Administrator
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 28
Server version: 5.6.25-enterprise-commercial-advanced-log MySQL Enterprise Server - Advanced Edition (Commercial)

Copyright (c) 2000, 2015, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

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

mysql> select current_user();
+-----------------+
| current_user()  |
+-----------------+
| Administrator@% |
+-----------------+
1 row in set (0.00 sec)


mysql>

Tuesday, June 23, 2015

Demo to produce identity gap in SQL Server

C:\Users\Administrator>sqlcmd -S  VMMDB01\PROD -W -e -i d:\ident.sql

USE AdventureWorks2012;
IF OBJECT_ID ('dbo.t1', 'U') IS NOT NULL
   DROP TABLE t1;

Changed database context to 'AdventureWorks2012'.
CREATE TABLE t1 (id int IDENTITY(1,1), name char(20))

insert into t1 (name) values ('a')


(1 rows affected)
SELECT @@IDENTITY AS 'Identity'

Identity
--------
1

(1 rows affected)
begin transaction
insert into t1 (name) values ('a');
rollback transaction;


(1 rows affected)
SELECT @@IDENTITY AS 'Identity';

Identity
--------
2

(1 rows affected)
insert into t1 (name) values ('a');


(1 rows affected)
SELECT @@IDENTITY AS 'Identity';

Identity
--------
3

(1 rows affected)
select * from t1;

id name
-- ----
1 a
3 a

(2 rows affected)


/******************************
USE AdventureWorks2012;
IF OBJECT_ID ('dbo.t1', 'U') IS NOT NULL
   DROP TABLE t1;
GO
CREATE TABLE t1 (id int IDENTITY(1,1), name char(20))
GO
insert into t1 (name) values ('a')
GO
SELECT @@IDENTITY AS 'Identity'
GO
begin transaction
insert into t1 (name) values ('a');
rollback transaction;
GO
SELECT @@IDENTITY AS 'Identity';
GO
insert into t1 (name) values ('a');
GO
SELECT @@IDENTITY AS 'Identity';
GO
select * from t1;

GO
******************************/

Saturday, June 20, 2015

Install SQL Server 2016 (CTP2) on Windows Server 2016 (CTP)–Step by Step Guide

ScreenHunter_245 Jun. 20 11.12

ScreenHunter_246 Jun. 20 11.13ScreenHunter_247 Jun. 20 11.14ScreenHunter_248 Jun. 20 11.14ScreenHunter_249 Jun. 20 11.16ScreenHunter_251 Jun. 20 11.16ScreenHunter_252 Jun. 20 11.16ScreenHunter_254 Jun. 20 11.17ScreenHunter_255 Jun. 20 11.19ScreenHunter_259 Jun. 20 11.35ScreenHunter_261 Jun. 20 11.37ScreenHunter_262 Jun. 20 11.37ScreenHunter_263 Jun. 20 11.38ScreenHunter_267 Jun. 20 11.38ScreenHunter_268 Jun. 20 11.38ScreenHunter_269 Jun. 20 11.39ScreenHunter_270 Jun. 20 11.39ScreenHunter_271 Jun. 20 11.39ScreenHunter_272 Jun. 20 11.41ScreenHunter_273 Jun. 20 11.57

ScreenHunter_274 Jun. 20 12.03

Using “MySQL Migration Toolkit” to migrate from Oracle to MySQL

Prepare software (these are discontinued/obsoleted software)

http://downloads.mysql.com/archives/migration/

http://www.oracle.com/technetwork/database/enterprise-edition/jdbc-112010-090769.html

http://www.oracle.com/technetwork/java/javasebusiness/downloads/java-archive-downloads-javase5-419410.html

Install the JDK5, extract MT tools into a folder, and copy jdbc drivers into it’s java folder:

image

Start Migration by Clicking MySQLMigrationTool.exe

 

ScreenHunter_215 Jun. 19 23.39

ScreenHunter_216 Jun. 19 23.39

Select source and target database (Oracle 12c and MySQL 5.6 used in this example)

ScreenHunter_217 Jun. 19 23.39ScreenHunter_218 Jun. 19 23.39ScreenHunter_219 Jun. 19 23.39

ScreenHunter_220 Jun. 19 23.39

Select Schema to migrate

ScreenHunter_221 Jun. 19 23.40ScreenHunter_224 Jun. 19 23.40ScreenHunter_225 Jun. 19 23.40ScreenHunter_226 Jun. 19 23.41ScreenHunter_227 Jun. 19 23.41ScreenHunter_228 Jun. 19 23.41ScreenHunter_229 Jun. 19 23.41ScreenHunter_230 Jun. 19 23.42

ScreenHunter_231 Jun. 19 23.42

Schema Creation

ScreenHunter_234 Jun. 19 23.42ScreenHunter_236 Jun. 19 23.43

Transfer the data

ScreenHunter_237 Jun. 19 23.43ScreenHunter_238 Jun. 19 23.43ScreenHunter_239 Jun. 19 23.43ScreenHunter_240 Jun. 19 23.43

Verify the data

ScreenHunter_241 Jun. 19 23.45ScreenHunter_242 Jun. 19 23.45