Error while grant execution can break replication in mysql 5.6

This looks like a bug in MySQL 5.6 : user  with GRANT privileges can  cause all replicas to break,  if the grant executed errors.

How?

 Let me explain . Lets assume iam trying to issue a grant replication client privilege to an user as seen below:

mysql> grant replication client on mine.*  to user@'10.29.1%';
ERROR 1221 (HY000): Incorrect usage of DB GRANT and GLOBAL PRIVILEGES

 The above grant failed coz the global privilege was supposed to be granted on *.*.
 Hence I re-execute by making corrections :

mysql> grant replication client on *.* to user@'10.29.1%';
Query OK, 0 rows affected (0.10 sec)
mysql> flush privileges;

All i did was corrected a grant statement. However this broke the replication of mysql database.

and Its a bug as per mysql : 68892.

 

Explaination:

 

Due to this replication bug, any GRANT command that errors out will break the mysql replication.

Then hence  skip the offending event on ALL replicas (or inject an empty transaction if GTID in use). The reason  is that MySQL 5.6 writes the following in the binlog when your grant fails:

#120009 01:02:10 server id 1 end_log_pos 422 CRC32 0x4d815dbc
# Incident: LOST_EVENTS
RELOAD DATABASE; # Shall generate syntax error
# at 422

This is intended to avoid any grant that fails on the master due to insufficient privileges, to succeed on the replicas instead (due to the fact that replication thread runs with SUPER privileges). To avoid that, replication is stopped with an error.
Concluding that any user on 5.6 which has GRANT privileges can break the replication if while executing any GRANT it error.


Cheers.

Performance issue during responsibility change in 11i

If you see performance issue during responsibility changing after launching form then perform following .

1.     Check the explain plan which  query is picking using (connect as apps ) :

Set pages 200

Set lines 200

Explain plan for

select distinct furg.responsibility_id, furg.responsibility_application_id, furg.security_group_id, furg.start_date, furg.end_date from fnd_user_resp_groups furg, fnd_responsibility fr where furg.user_id = :1 and furg.responsibility_id = fr.responsibility_id and furg.responsibility_application_id = fr.application_id and furg.start_date <= sysdate and (furg.end_date is null or furg.end_date >= sysdate) and fr.start_date <= sysdate and  (fr.end_date is null or fr.end_date >= sysdate) order by furg.responsibility_id, furg.responsibility_application_id, furg.security_group_id;

@?/rdbms/admin/utlxpls

/

2.     For the bad performance it will be using WF_LOCAL_USER_ROLES_N7  . for the query to perform good it should pick WF_LOCAL_USER_ROLES_U1

3.     We can either fix it by rebuilding the index or by just doing gather stats on index WF_LOCAL_USER_ROLES_N7
Option a : For rebuilding index use : connect as apps and run create_WF_LOCAL_USER_ROLES_N7.sql

drop index applsys.WF_LOCAL_USER_ROLES_N7;

CREATE INDEX APPLSYS.WF_LOCAL_USER_ROLES_N7 ON APPLSYS.WF_LOCAL_USER_ROLES

(ROLE_ORIG_SYSTEM_ID, ROLE_ORIG_SYSTEM)

  INITRANS   11

  MAXTRANS   255

LOGGING

LOCAL (

  PARTITION FND_USR

    LOGGING

    NOCOMPRESS

    TABLESPACE APPLSYSX

    PCTFREE    10

    INITRANS   11

    MAXTRANS   255

    STORAGE    (

                INITIAL          40K

                NEXT             40K

                MINEXTENTS       1

                MAXEXTENTS       2147483645

                PCTINCREASE      0

                FREELISTS        1

                FREELIST GROUPS  1

                BUFFER_POOL      DEFAULT

               ),

  PARTITION FND_RESP

    LOGGING

    NOCOMPRESS

    TABLESPACE APPS_TS_TX_DATA

    PCTFREE    10

    INITRANS   11

    MAXTRANS   255

    STORAGE    (

                INITIAL          40K

                NEXT             8K

                MINEXTENTS       1

                MAXEXTENTS       2147483645

                PCTINCREASE      0

                BUFFER_POOL      DEFAULT

               ),

  PARTITION POS

    LOGGING

    NOCOMPRESS

    TABLESPACE APPLSYSD

    PCTFREE    10

    INITRANS   11

    MAXTRANS   255

    STORAGE    (

                INITIAL          40K

                NEXT             40K

                MINEXTENTS       1

                MAXEXTENTS       2147483645

                PCTINCREASE      0

                FREELISTS        1

                FREELIST GROUPS  1

                BUFFER_POOL      DEFAULT

               ),

  PARTITION AMV_APPR

    LOGGING

    NOCOMPRESS

    TABLESPACE APPLSYSX

    PCTFREE    10

    INITRANS   11

    MAXTRANS   255

    STORAGE    (

                INITIAL          40K

                NEXT             8K

                MINEXTENTS       1

                MAXEXTENTS       2147483645

                PCTINCREASE      0

                FREELISTS        1

                FREELIST GROUPS  1

                BUFFER_POOL      DEFAULT

               ),

  PARTITION ENG_LIST

    LOGGING

    NOCOMPRESS

    TABLESPACE APPS_TS_TX_DATA

    PCTFREE    10

    INITRANS   11

    MAXTRANS   255

    STORAGE    (

                INITIAL          40K

                NEXT             8K

                MINEXTENTS       1

                MAXEXTENTS       2147483645

                PCTINCREASE      0

                BUFFER_POOL      DEFAULT

               ),

  PARTITION HZ_PARTY

    LOGGING

    NOCOMPRESS

    TABLESPACE APPS_TS_TX_DATA

    PCTFREE    10

    INITRANS   11

    MAXTRANS   255

    STORAGE    (

                INITIAL          40K

                NEXT             8K

                MINEXTENTS       1

                MAXEXTENTS       2147483645

                PCTINCREASE      0

                BUFFER_POOL      DEFAULT

               ),

  PARTITION GBX

    LOGGING

    NOCOMPRESS

    TABLESPACE APPS_TS_TX_DATA

    PCTFREE    10

    INITRANS   11

    MAXTRANS   255

    STORAGE    (

                INITIAL          40K

                NEXT             8K

                MINEXTENTS       1

                MAXEXTENTS       2147483645

                PCTINCREASE      0

                BUFFER_POOL      DEFAULT

               ),

  PARTITION PQH_ROLE

    LOGGING

    NOCOMPRESS

    TABLESPACE APPS_TS_TX_DATA

    PCTFREE    10

    INITRANS   11

    MAXTRANS   255

    STORAGE    (

                INITIAL          40K

                NEXT             8K

                MINEXTENTS       1

                MAXEXTENTS       2147483645

                PCTINCREASE      0

                BUFFER_POOL      DEFAULT

               ),

  PARTITION WF_LOCAL_ROLES

    LOGGING

    NOCOMPRESS

    TABLESPACE APPS_TS_TX_DATA

    PCTFREE    10

    INITRANS   11

    MAXTRANS   255

    STORAGE    (

                INITIAL          40K

                NEXT             8K

                MINEXTENTS       1

                MAXEXTENTS       2147483645

                PCTINCREASE      0

                BUFFER_POOL      DEFAULT

               ),

  PARTITION PER_ROLE

    LOGGING

    NOCOMPRESS

    TABLESPACE APPS_TS_TX_DATA

    PCTFREE    10

    INITRANS   11

    MAXTRANS   255

    STORAGE    (

                INITIAL          40K

                NEXT             8K

                MINEXTENTS       1

                MAXEXTENTS       2147483645

                PCTINCREASE      0

                BUFFER_POOL      DEFAULT

               ),

  PARTITION AMV_CHN

    LOGGING

    NOCOMPRESS

    TABLESPACE APPS_TS_TX_DATA

    PCTFREE    10

    INITRANS   11

    MAXTRANS   255

    STORAGE    (

                INITIAL          40K

                NEXT             8K

                MINEXTENTS       1

                MAXEXTENTS       2147483645

                PCTINCREASE      0

                BUFFER_POOL      DEFAULT

               ),

  PARTITION HZ_GROUP

    LOGGING

    NOCOMPRESS

    TABLESPACE APPLSYSX

    PCTFREE    10

    INITRANS   11

    MAXTRANS   255

    STORAGE    (

                INITIAL          40K

                NEXT             8K

                MINEXTENTS       1

                MAXEXTENTS       2147483645

                PCTINCREASE      0

                FREELISTS        1

                FREELIST GROUPS  1

                BUFFER_POOL      DEFAULT

               ),

  PARTITION HTB_SEC

    LOGGING

    NOCOMPRESS

    TABLESPACE APPS_TS_TX_DATA

    PCTFREE    10

    INITRANS   11

    MAXTRANS   255

    STORAGE    (

                INITIAL          40K

                NEXT             8K

                MINEXTENTS       1

                MAXEXTENTS       2147483645

                PCTINCREASE      0

                BUFFER_POOL      DEFAULT

               ),

  PARTITION UMX

    LOGGING

    NOCOMPRESS

    TABLESPACE APPLSYSX

    PCTFREE    10

    INITRANS   11

    MAXTRANS   255

    STORAGE    (

                INITIAL          40K

                NEXT             8K

                MINEXTENTS       1

                MAXEXTENTS       2147483645

                PCTINCREASE      0

                FREELISTS        1

                FREELIST GROUPS  1

                BUFFER_POOL      DEFAULT

               )

)

NOPARALLEL;

4.      >takes 10 mins
Option b: for gather stats for index : Connect as apps and run EXEC DBMS_STATS.gather_index_stats('APPLSYS', 'WF_LOCAL_USER_ROLES_N7', estimate_percent => 15); >less than 1 min

5.     Please run step1 to verify the plan

Note : Try performing option b first,since  it will take only 1 min to complete, if te issue still persists, then use option a as well.

Performance tuning tip for slow running sql

The below script will help in finding out if any index creation/tuning is required on a oracle table for a long running sql script.

Using the sid, fetch the sql_id from gv$sql , before running this script ( sql_id is the input for the script):



set echo on
set verify on
set serveroutput on
set linesize 120
undefine sql_id
spool sta.out
execute dbms_workload_repository.create_snapshot(flush_level=>'ALL');
select min(snap_id) from dba_hist_snapshot;
select max(snap_id) from dba_hist_snapshot;
DECLARE
l_sql_tune_task_id VARCHAR2(100);
BEGIN
l_sql_tune_task_id := DBMS_SQLTUNE.CREATE_TUNING_TASK(
begin_snap=>&beginsnap,
end_snap=>&endsnap,
sql_id=>'&&sql_id',
scope=>'COMPREHENSIVE',
time_limit=>3600,
task_name=>'&&sql_id._tuning_task',
description=>'Tuning task for statement ' || '&&sql_id');
DBMS_OUTPUT.put_line('l_sql_tune_task_id: ' || l_sql_tune_task_id);
END;
/

BEGIN
DBMS_SQLTUNE.EXECUTE_TUNING_TASK( task_name => '&&sql_id._tuning_task');
END;
/

SET LONG 10000
SET LONGCHUNKSIZE 10000
SET LINESIZE 100
SELECT DBMS_SQLTUNE.REPORT_TUNING_TASK( '&&sql_id._tuning_task') from DUAL;
--
--
-- exec dbms_sqltune.drop_tuning_task(task_name=>'&&sql_id._tuning_task');
--
--
spool off