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

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>

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
******************************/

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

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:

Start Migration by Clicking MySQLMigrationTool.exe

 

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

Select Schema to migrate

Schema Creation

Transfer the data

Verify the data

Few ORA- errors during "alter index ... rebuild"

[oracle@vmxdb01 ~]$ grep ORA- move_index.log |sort|uniq ORA-00604: error occurred at recursive SQL level 1 ORA-01502: index 'SYS.I_WRI$_OPTSTAT_IND_OBJ#_ST' or partition of such index is ORA-02327: cannot create index on expression with datatype ADT ORA-02327: cannot create index on expression with datatype LOB ORA-02327: cannot create index on expression with datatype NAMED ARRAY TYPE ORA-28650: Primary index on an IOT cannot be rebuilt

ORA-00604 & ORA-01502

SQL> alter index SYS.I_SMB$CONFIG_PKEY rebuild; alter index SYS.I_SMB$CONFIG_PKEY rebuild * ERROR at line 1: ORA-00604: error occurred at recursive SQL level 1 ORA-01502: index 'SYS.I_WRI$_OPTSTAT_IND_OBJ#_ST' or partition of such index is in unusable state

To Fix:

SQL> alter index SYS.I_WRI$_OPTSTAT_IND_OBJ#_ST rebuild; Index altered. SQL> alter index SYS.I_SQL$TEXT_PKEY rebuild; Index altered.

ORA-28650: Primary index on an IOT cannot be rebuilt

SQL> alter index SYS.SQLLOG$_PKEY rebuild online; alter index SYS.SQLLOG$_PKEY rebuild online * ERROR at line 1: ORA-28650: Primary index on an IOT cannot be rebuilt

To Fix:

SQL> select table_name from dba_indexes where index_name='SQLLOG$_PKEY'; TABLE_NAME -------------------------------------------------------------------------------- SQLLOG$ SQL> alter table SQLLOG$ move tablespace sysaux online; -- online only work if no iot_overflow Table altered.

Few ORA- error while "alter table ....move"

[oracle@vmxdb01 ~]$ grep ORA- move_table.log |sort|uniq ORA-00997: illegal use of LONG datatype ORA-01735: invalid ALTER TABLE option ORA-14512: cannot perform operation on a clustered object ORA-25191: cannot reference overflow table of an index-organized table

ORA-00997: illegal use of LONG datatype

SQL> alter table SYS.WRI$_SQLSET_WORKSPACE_PLANS move; SQL> alter table SYS.WRI$_SQLSET_PLAN_LINES move; SQL> alter table SYS.WRI$_ADV_SQLT_PLANS move; SQL> alter table SYS.SQLOBJ$PLAN move;

To fix:

In this example, all tables are actually empty. For application tables, suggest to use copy or expdp/impdp.

ORA-01735: invalid ALTER TABLE option

SQL> alter table XDB.SYS_NT/ZrP7FfoSKngQ7ap6Ar7bw== move;

To fix:

SQL> alter table XDB."SYS_NT/ZrP7FfoSKngQ7ap6Ar7bw==" move; Table altered.

ORA-14512: cannot perform operation on a clustered object

SQL> alter table SYS.SMON_SCN_TIME move;

To fix:

SQL> select cluster_owner,cluster_name from dba_tables   2  where owner='SYS' and table_name='SMON_SCN_TIME'; CLUSTER_OWNER                  CLUSTER_NAME ------------------------------ ------------------------------ SYS                            SMON_SCN_TO_TIME_AUX SQL> select owner,table_name from dba_tables   2  where cluster_name='SMON_SCN_TO_TIME_AUX' and cluster_owner='SYS'; OWNER                          TABLE_NAME ------------------------------ ------------------------------ SYS                            SMON_SCN_TIME Expdp/impdp to create the object if not sys internal objects.

ORA-25191: cannot reference overflow table of an index-organized table

SQL> alter table SYS.SYS_IOT_OVER_18291 move; SQL> alter table SYS.SYS_IOT_OVER_17517 move; SQL> alter table SYS.SYS_IOT_OVER_7687 move; …

To fix:

SQL> select owner,table_name,iot_name from dba_tables where owner='SYS' and table_name='SYS_IOT_OVER_7687'; OWNER                          TABLE_NAME ------------------------------ ------------------------------ IOT_NAME -------------------------------------------------------------------------------- SYS                            SYS_IOT_OVER_7687 RECENT_RESOURCE_INCARNATIONS$ SQL> alter table RECENT_RESOURCE_INCARNATIONS$ move tablespace sysaux overflow tablespace sysaux; Table altered. SQL> select owner,table_name,iot_name from dba_tables where owner='SYS' and table_name='SYS_IOT_OVER_18291'; OWNER                          TABLE_NAME ------------------------------ ------------------------------ IOT_NAME -------------------------------------------------------------------------------- SYS                            SYS_IOT_OVER_18291 AQ$_SYS$SERVICE_METRICS_TAB_G SQL> alter table AQ$_SYS$SERVICE_METRICS_TAB_G move tablespace sysaux overflow tablespace sysaux; alter table AQ$_SYS$SERVICE_METRICS_TAB_G move tablespace sysaux overflow tablespace sysaux             * ERROR at line 1: ORA-24005: Inappropriate utilities used to perform DDL on AQ table SYS.AQ$_SYS$SERVICE_METRICS_TAB_G SQL> DECLARE   2    po dbms_aqadm.aq$_purge_options_t;   3  BEGIN   4    po.block := FALSE;   5    DBMS_AQADM.PURGE_QUEUE_TABLE(   6      queue_table => 'AQ$_SYS$SERVICE_METRICS_TAB_G',   7      purge_condition => NULL,     purge_options => po);   8    9  END;  10  / DECLARE * ERROR at line 1: ORA-24019: identifier for QUEUE_TABLE too long, should not be greater than 24 characters ORA-06512: at "SYS.DBMS_AQADM", line 1668 ORA-06512: at line 5 SQL> DECLARE   2    po dbms_aqadm.aq$_purge_options_t;   3  BEGIN   4    po.block := FALSE;   5    DBMS_AQADM.PURGE_QUEUE_TABLE(   6      queue_table => 'SYS$SERVICE_METRICS_TAB',   7      purge_condition => NULL,   8      purge_options => po);   9  END;  10  / PL/SQL procedure successfully completed.