All posts by naveed

Fixing Broken GTID enabled replication ERROR 1858 (HY000):

Broken replication because of duplicate values MySQL 5.6 onward :

In non GTID enabled replication , we use to skip duplicate errors in case of Last_Errno: 1062 . But once we enable GTID we will have to deal it differently. The procedure is to follow the following simple steps

Let use assume two server  A as master B as slave.

  •  Check  slave status as shown below

mysql> show slave status\G
*************************** 1. row ***************************
 Slave_IO_State: Waiting for master to send event
 Master_Host: A
 Master_User: svc-mysql-repl
 Master_Port: 3306
 Connect_Retry: 60
 Master_Log_File: mysql-bin.000167
 Read_Master_Log_Pos: 72611417
 Relay_Log_File: relay-log.000364
 Relay_Log_Pos: 776
 Relay_Master_Log_File: mysql-bin.000161
 Slave_IO_Running: Yes
 Slave_SQL_Running: No
 Replicate_Do_DB:
 Replicate_Ignore_DB: admindb
 Replicate_Do_Table:
 Replicate_Ignore_Table: mysql.ibbackup_binlog_marker,mysql.backup_history,mysql.backup_progress,mysql.backup_sbt_history,mysql.inventory
 Replicate_Wild_Do_Table:
 Replicate_Wild_Ignore_Table:
 Last_Errno: 1062
 Last_Error: Error 'Duplicate entry '21555' for key 'PRIMARY'' on query. Default database: 'test'. Query: 'INSERT INTO auditlogs (text, category, datetime, username)
VALUES ('Starting application Web version 5.4', 'ACTION', '2017-07-26 09:49:41', '')'
 Skip_Counter: 0
 Exec_Master_Log_Pos: 611
 Relay_Log_Space: 5064799212
 Until_Condition: None
 Until_Log_File:
 Until_Log_Pos: 0
 Master_SSL_Allowed: No
 Master_SSL_CA_File:
 Master_SSL_CA_Path:
 Master_SSL_Cert:
 Master_SSL_Cipher:
 Master_SSL_Key:
 Seconds_Behind_Master: NULL
Master_SSL_Verify_Server_Cert: No
 Last_IO_Errno: 0
 Last_IO_Error:
 Last_SQL_Errno: 1062
 Last_Error: Error 'Duplicate entry '21555' for key 'PRIMARY'' on query. Default database: 'test'. Query: 'INSERT INTO auditlogs (text, category, datetime, username)
VALUES ('Starting application Web version 5.4', 'ACTION', '2017-07-26 09:49:41', '')'
 Replicate_Ignore_Server_Ids:
 Master_Server_Id: 1
 Master_UUID: 4b7a0a70-146b-11e7-80f7-941882781532
 Master_Info_File: /opsrt12p/mysql/data/master.info
 SQL_Delay: 0
 SQL_Remaining_Delay: NULL
 Slave_SQL_Running_State:
 Master_Retry_Count: 86400
 Master_Bind:
 Last_IO_Error_Timestamp:
 Last_SQL_Error_Timestamp: 170727 08:59:35
 Master_SSL_Crl:
 Master_SSL_Crlpath:
 Retrieved_Gtid_Set: 4b7a0a70-146b-11e7-80f7-941882781532:1-87044138
 Executed_Gtid_Set: 4b7a0a70-146b-11e7-80f7-941882781532:1-82186600
 Auto_Position: 0
 Replicate_Rewrite_DB:
 Channel_Name:
 Master_TLS_Version:
1 row in set (0.00 sec)

In above example we can see two Gtid values which are

Executed_Gtid_Set : represents the last executed statement , The set of global transaction IDs written in the binary log. This is the same as the value for the global gtid_executed system variable on this server.

In above example we have following last executed Gtid value.

Executed_Gtid_Set: 4b7a0a70-146b-11e7-80f7-941882781532:1-82186600

and we have following master binlog file and position

 Master_Log_File: mysql-bin.000167 

 Read_Master_Log_Pos: 72611417

 

  •  If we try to skip the counter we will get following .
mysql> stop slave;

Query OK, 0 rows affected (0.00 sec) 

mysql> set global sql_slave_skip_counter=1;

ERROR 1858 (HY000): sql_slave_skip_counter can not be set when the server is running with @@GLOBAL.GTID_MODE = ON. 
Instead, for each transaction that you want to skip, generate an empty transaction with the same GTID as the transaction

 

  • Now we should check the entry int the mentioned binary log  at specific position  on server A (master server) and we need to check how many more steps needs to be executed from the perticular Gtid. using following commands.
    • create an temporary txt file using mysqlbinlog utility as shown
bash-4.2$ /apps/mysql/opsrt11p/install/bin/mysqlbinlog --base64-output=decode-rows --verbose mysql-bin.000167> /tmp/temp-binlog.txt
  •  Now view the temp-binlog.txt using less or any other utility  and find out set of statement executed under 4b7a0a70-146b-11e7-80f7-941882781532:1-82186600 Gtid at step number 82186600 there may be few more steps to be executed therefore we need to skip step number 82186600 and move to next step of the statement under particular Gtid. 
  • Skip to next step of Gtid using following statement.
mysql> set GTID_NEXT="4b7a0a70-146b-11e7-80f7-941882781532:82186601;

Query OK, 0 rows affected (0.00 sec)

mysql> start slave;
  • Now do show slave status again and you will see statement has been skipped and there are no duplicate errors any more.

 

Thanks for visiting , please dont forget to leave you feedback

Regards

Raja Naveed

 

 

Split file into multiple files using split command line utility on unix / linux

Split Command in Unix / Linux:

This utility / command in unix or linux helps to split the file into two or more files. We have multiple options to use the command as per our needs.

Syntax :

split [filename] [INPUT [PREFIX] ]

where
[filename] is the name of file we want to split.
[INPUT [PREFIX]]  is optional prefix for the splited files.
for example if we have file name called “abc” then usually we will keep Prefix as “abc” so that it can be divided into files are called abc0 , abc1, abc2 … abc[N]

OPTIONAL

-a, --suffix-length=N              use suffixes of length N (default 2)
-b, --bytes=SIZE              put SIZE bytes per output file
-C, --line-bytes=SIZE              put at most SIZE bytes of lines per output file
-d, --numeric-suffixes              use numeric suffixes instead of alphabetic
-l, --lines=NUMBER              put NUMBER lines per output file
 --verbose              print a diagnostic just before each output file is opened

Examples 1 :

Lets create a file which will contain 100 lines using following command

for i in {1..100} ; do echo "This is line number $i ">>test-split.txt; done
bash-4.1$ ls
test-split.txt

Now split the file into 10 files each will contain

split -l 10 test-split.txt test-split.txt -d

Note:  with -d option each file will have digit in suffix as shown below but if the number of files could go beyond 99 or more than 2 diget number we can us -a3 or -aN option for suffix length where N is length of suffix (default is -a2)

therefore file is split into 10 as shown below

bash-4.1$ ls
test-split.txt test-split.txt00 test-split.txt01 test-split.txt02 test-split.txt03 test-split.txt04 
test-split.txt05 test-split.txt06 test-split.txt07 test-split.txt08 test-split.txt09

Example 2.

Lets create a bigger file of 10M and split it with respect to size in MB.

bash-4.1$ ls -ltrh
total 10M  

-rw-r-----. 1 rmn users 10M Jul 20 15:13 test-split.txt

Now we split this 10M file into 10 equal size files of 1M appx using following command.

bash-4.1$ split -b 1M test-split.txt test-split.txt -d

File have been splited into 10 files as show below

bash-4.1$ ls -ltrh
total 20M
-rw-r-----. 1 rmn     users 10M Jul 20 15:13 test-split.txt
-rw-r-----. 1 rmn     users 1.0M Jul 20 15:17 test-split.txt00
-rw-r-----. 1 rmn     users 1.0M Jul 20 15:17 test-split.txt01
-rw-r-----. 1 rmn     users 1.0M Jul 20 15:17 test-split.txt02
-rw-r-----. 1 rmn      users 1.0M Jul 20 15:17 test-split.txt03
-rw-r-----. 1 rmn     users 1.0M Jul 20 15:17 test-split.txt04
-rw-r-----. 1 rmn     users 1.0M Jul 20 15:17 test-split.txt05
-rw-r-----. 1 rmn     users 1.0M Jul 20 15:17 test-split.txt06
-rw-r-----. 1 rmn     users 1.0M Jul 20 15:17 test-split.txt07
-rw-r-----. 1 rmn     users 1.0M Jul 20 15:17 test-split.txt08
-rw-r-----. 1 rmn     users 927K Jul 20 15:17 test-split.txt09

If you want to split the file in bytes then command will be as followed

bash-4.1$ split -b1000 test-split.txt test-split.txt -d

Finally how to join all back to make one single file ? it is very simple just use cat command line utility as shown belown

bash-4.1$ cat test-split.txt00 test-split.txt01 test-split.txt02 test-split.txt03 test-split.txt04 test-split.txt05 test-split.txt06 test-split.txt07 test-split.txt08 test-split.txt09 >> test-split.txt

 

Thanks for reading article I hope you liked it .. Keep visiting and don’t forget to give feedback

 

EMD upload error: Upload was successful but collections currently disabled – disk full

EMD upload error: Upload was successful but collections currently disabled – disk full

#Cause

  • The EMD disk system shows used percent at 99%.
  • The agent default for space required for upload files is 98%. The collections will stop when the space on the disk is beyond that.

if you find above error then do following

#Solution 1

 Delete old trc/ trm files from
OB/diag/rdbms/oracle_sid_DIRECTORY/ORACLE_SID_DIRECTORY/trace/.
Remember do not delete alert_ORACLESID.trc
now do following steps

1. emctl stop agent
2. emctl start agent
3. emctl upload

you should get following msg.

Oracle Enterprise Manager 11g Database Control Release 11.2.0.2.0
Copyright (c) 1996, 2010 Oracle Corporation.  All rights reserved.
—————————————————————
EMD upload completed successfully

#Solution 2

To implement the solution, please execute the following steps:
1. Update the AGENT OH/sysman/config/emd.properties with the parameter:
uploadMaxDiscUsedPct=99
uploadMaxDiskUsedPctFloor=99
2. Stop the agent: emctl stop agent
3. Start the agent: emctl start agent
4. Upload to the OMS: emctl upload

EMD upload error: uploadXMLFiles skipped :: OMS version not checked yet..

if you try
emctl upload  and get above error then do following
emctl stop dbconsole
go to directory $ORACLE_HOME/hostname_SID/sysman and do following steps
2. delete following files using these commands.
rm -r sysman/emd/state/*
rm -r sysman/emd/collection/*
rm -r sysman/emd/upload/*
rm sysman/emd/lastupld.xml
rm sysman/emd/agntstmp.txt
rm sysman/emd/blackouts.xml
rm sysman/emd/protocol.ini
3. emctl secure dbconsole
(reset passord of sysman user if needed)
4. emctl start dbconsole