Mani's Blog

August 31, 2011

MySQL: Plugin ‘InnoDB’ registration as a STORAGE ENGINE failed.

Filed under: MySQL — mani @ 9:27 pm

Problem: After restoring the databases, Initially I started  mysql with incorrect and  ran into error in the error log.

110831 21:20:00 [ERROR] Plugin ‘InnoDB’ init function returned error.
110831 21:20:00 [ERROR] Plugin ‘InnoDB’ registration as a STORAGE ENGINE failed.
110831 21:20:00 [ERROR] Unknown/unsupported storage engine: INNODB
110831 21:20:00 [ERROR] Aborting

110831 21:20:00 [Note] /usr/sbin/mysqld: Shutdown complete

110831 21:20:00 mysqld_safe mysqld from pid file /local/mysql/data/mysqld.pid ended

Solution: Go to the data directory and remove log files look like ib_logfile0, ib_logfile1 and ib_logfile2.  Then start mysql, this fixed the issue.

/local/mysql/data#/etc/init.d/mysql start
Starting MySQL……….. SUCCESS!

Advertisements

August 25, 2011

False Alarm on MEM “InnoDB Tablespace Cannot Automatically Expand”

Filed under: MySQL — mani @ 1:09 am

Issue

MEM is throwing false alerts regarding the InnoDB autoextend capabilities. We got couple of alert from MySQL Enterprise Dashboard saying “InnoDB Tablespace Cannot Automatically Expand”.   When we check the DB my.cnf configured correctly.

Following solution was applied to correct the issue.

Cause

The issue is caused when the connection pool is being exhausted after reading the initial variable values. A connection will be attempted to evaluate the SQL function locate() for the rule, since it does not exist in the JAVA code. The attempt fails since a connection cannot be established, the function defaults to false causing the rule to fail, and so a false alert is sent.

This was repeated in test cases to justify the cause of the false alerts.

Solution

Increased default.maxActive from 70 to 150.

  1. Open the file #INSTALL_DIR/apache-tomcat/webapps/ROOT/WEB-INF/config.properties in your favourite text editor.
  2. Locate the line “default.maxActive=70”.
  3. Change the line to be “default.maxActive=150” (or a larger number if required, but 200+ should not be needed).
  4. Save the changes to the file.
  5. Restart the tomcat service (example is for Linux/Unix installs):
shell>  ./mysqlmonitorctl.sh restart

August 24, 2011

Archivelog backup failed

Filed under: Oracle — mani @ 7:29 pm

Archive log backup has been failed as below:

Following steps fixed the issue for me.

crosscheck archivelog all;
delete noprompt obsolete;
delete noprompt expired;

Error Output Log

Recovery Manager: Release 11.1.0.7.0 – Production on Tue Aug 23 14:45:13 2011

Copyright (c) 1982, 2007, Oracle.  All rights reserved.

RMAN>
connected to target database: XNODBMSG (DBID=2208710694)

RMAN>
connected to recovery catalog database

RMAN>
echo set on

RMAN> set command id to ‘BKP_XNODBMSG_ARCHL_082311024502’;
executing command: SET COMMAND ID

RMAN> backup device type disk tag ‘BKP_XNODBMSG_ARCHL_082311024502’ archivelog all not backed up delete all input;
Starting backup at 23-AUG-11
current log archived
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=2992 instance=XNODBMSG2 device type=DISK
skipping archived log file /arch/XNODBMSG/1_24329_692743270.dbf; already backed on 02-AUG-11
skipping archived log file /arch/XNODBMSG/1_24330_692743270.dbf; already backed on 02-AUG-11
skipping archived log file /arch/XNODBMSG/1_24331_692743270.dbf; already backed on 02-AUG-11
skipping archived log file /arch/XNODBMSG/1_24332_692743270.dbf; already backed on 02-AUG-11
skipping archived log file /arch/XNODBMSG/1_24333_692743270.dbf; already backed on 02-AUG-11
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of backup command at 08/23/2011 14:45:54
ORA-19563: archived log header validation failed for file /arch/XNODBMSG/1_26015_692743270.dbf

RMAN> exit;

Recovery Manager complete.

August 9, 2011

Drop Database in Oracle

Filed under: Oracle — mani @ 5:54 pm

Steps to drop database in Oracle.

BEWARE that this task cannot be undone.

shutdown abort;

startup mount exclusive restrict;

drop database;

exit;

August 5, 2011

Troubleshooting Materialized View Replication in Oracle

Filed under: Oracle — mani @ 10:31 pm

Materialized gets broken for many reasons.  Here are the simple steps to diagnose and trouble Materialized view and its replication.

  1. Verify the MV replication job is broken.
    SQL> select job from dba_jobs where broken='Y';
           JOB
    ----------
        63
  1. Check how much MV Log left to be refreshed.
    SQL> SELECT COUNT(*) FROM    MLOG$_PROFILE;
      COUNT(*)
    ----------
           235
  2. Connect as MV owner and run the job.
    SQL> begin
      2  dbms_job.run(63);
      3  end;
      4  /
    PL/SQL procedure successfully completed.
  3. Verify the broken job is fixed. It should return no rows.
    SQL> select job from dba_jobs where broken='Y';
  4. Verify that the mlog is cleared
    SQL> SELECT COUNT(*) FROM    MLOG$_PROFILE;
      COUNT(*)
    ----------
         0
  5. Sometime the replication is broken due unique violation
    Errors in file /local/opt/oracle/diag/rdbms/xnb3cont/XNB3CONT/trace/XNB3CONT_ora_10946.trc:
    ORA-12012: error on auto execute of job 63
    ORA-12008: error in materialized view refresh path
    ORA-00001: unique constraint (XN_CONTACTS.PROFILE_EMAIL_L_P) violated
    ORA-06512: at "SYS.DBMS_SNAPSHOT", line 2545
    ORA-06512: at "SYS.DBMS_SNAPSHOT", line 2751
    ORA-06512: at "SYS.DBMS_IREFRESH", line 685
    ORA-06512: at "SYS.DBMS_REFRESH", line 195
    ORA-06512: at line 1
    Mon Jul 11 22:06:18 2011
  6. Drop the unique index
    SQL> DROP INDEX "XN_CONTACTS"."PROFILE_EMAIL_L_P";
    Index dropped.
  7. Create non-unique index.
    SQL> CREATE INDEX "XN_CONTACTS"."PROFILE_EMAIL_L_P" ON "XN_CONTACTS"."PROFILE" (LOWER("EMAIL")) TABLESPACE "XN_CONTACT_IDX" PCTFREE 10 INITRANS 2 MAXTRANS 255 STORAGE ( INITIAL 64K BUFFER_POOL DEFAULT) parallel 8 nologging;
    Index created.
    SQL> alter INDEX "XN_CONTACTS"."PROFILE_EMAIL_L_P" noparallel;
    Index altered.
  8. Connect to MV owner and run the job that was broken.
    SQL> connect xn_contacts
    Enter password:
    Connected.
    SQL> begin
      2  dbms_job.run(63);
      3  end;
      4  /
    PL/SQL procedure successfully completed.
    SQL>
  9. In case if you need to disable dbms_job schedule.
    exec dbms_ijob.broken(63,true);

Error_code: 1032; handler error HA_ERR_KEY_NOT_FOUND;

Filed under: MySQL — mani @ 10:13 pm
Tags:

I ran into this error while rebuilding slave off Master DB hot backup. The replication is stopped due to this error.

Here is the complete error
“Problem Description: Could not execute Delete_rows event on table inv002.invitation; Can’t find record in ‘invitation’, Error_code: 1032; handler error HA_ERR_KEY_NOT_FOUND; the event’s master log binary-logs.000408, end_log_pos 4359444”

Later realized that event on the slave is enable obviously since it got refreshed from Master. General practice for event is “Enable only on Master and disable it on Slave”. This will avoid the conflict.

Disabling event resolved the issue.

(root@localhost) [inv001]> show events\G
*************************** 1. row ***************************
Db: inv001
Name: delete_old_invitations
Definer: root@localhost
Time zone: SYSTEM
Type: RECURRING
Execute at: NULL
Interval value: 5
Interval field: MINUTE
Starts: 2011-05-04 22:55:19
Ends: NULL
Status: ENABLED
Originator: 2000
character_set_client: utf8
collation_connection: utf8_general_ci
Database Collation: utf8_general_ci
1 row in set (0.00 sec)

(root@localhost) [inv001]> alter event delete_old_invitations disable;
Query OK, 0 rows affected (0.00 sec)

(root@localhost) [inv001]> show events\G
*************************** 1. row ***************************
Db: inv001
Name: delete_old_invitations
Definer: root@localhost
Time zone: SYSTEM
Type: RECURRING
Execute at: NULL
Interval value: 5
Interval field: MINUTE
Starts: 2011-05-04 22:55:19
Ends: NULL
Status: DISABLED
Originator: 2001
character_set_client: utf8
collation_connection: utf8_general_ci
Database Collation: utf8_general_ci
1 row in set (0.00 sec)

August 2, 2011

innobackupex-1.5.1:: Warning: Ignored unrecognized line 2 in options : ‘xtrabackup: Error: Please set parameter ‘datadir’

Filed under: MySQL — mani @ 12:15 am

Ran into this error while restoring(apply log) the database.

Realized that my.cnf file is not under default location /etc/my.cnf.  I had to specify the location explicitly as below.

innobackupex-1.5.1 –defaults-file=/local/mysql/enterprise/monitor/mysql/my.cnf –apply-log ./

Blog at WordPress.com.