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

Friday, June 19, 2015

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.

Thursday, June 18, 2015

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.