Tech Notes:- Got fatal error 1236 from master when reading data from binary log: 'Cannot replicate anonymous transaction when AUTO_POSITION = 1

 Last_IO_Errno: 1236
                Last_IO_Error: Got fatal error 1236 from master when reading data from binary log: 'Cannot replicate anonymous transaction when AUTO_POSITION = 1, at file ../binlogs/mysql-bin.000006, position 194.; the first event '' at 4, the last event read from '../binlogs/mysql-bin.000006' at 259, the last byte read from '../binlogs/mysql-bin.000006' at 259.'
               Last_SQL_Errno: 1396
 Master_UUID: ab7c6739-be64-11ea-b478-080027fd2c56

Retrieved_Gtid_Set: ab7c6739-be64-11ea-b478-080027fd2c56:1-29

            Executed_Gtid_Set: 846dc749-bf34-11ea-852d-08002749f9b4:1-1032,

ab7c6739-be64-11ea-b478-080027fd2c56:1-4:30-114202

On master check the binary log 'mysql-bin.000006' at position 194

mysql> show binlog events in 'mysql-bin.000006' from 4 limit 5;
+------------------+-----+----------------+-----------+-------------+---------------------------------------------------------------------------------------------------------------+
| Log_name         | Pos | Event_type     | Server_id | End_log_pos | Info                                                                                                          |
+------------------+-----+----------------+-----------+-------------+---------------------------------------------------------------------------------------------------------------+
| mysql-bin.000006 |   4 | Format_desc    |        55 |         123 | Server ver: 5.7.30-33-log, Binlog ver: 4                                                                      |
| mysql-bin.000006 | 123 | Previous_gtids |        55 |         194 | ab7c6739-be64-11ea-b478-080027fd2c56:1-29                                                                                                    |

+------------------+-----+----------------+-----------+-------------+---------------------------------------------------------------------------------------------------------------+

The solution is to skip the transactions associated with GTID ab7c6739-be64-11ea-b478-080027fd2c56:1-29 
after making sure that this has been replicated already if not, we need to find and apply the transactions manually.

SET GTID_NEXT='ab7c6739-be64-11ea-b478-080027fd2c56:1';
BEGIN;COMMIT;
SET GTID_NEXT='ab7c6739-be64-11ea-b478-080027fd2c56:2';
BEGIN;COMMIT;
SET GTID_NEXT='ab7c6739-be64-11ea-b478-080027fd2c56:3';
BEGIN;COMMIT;
SET GTID_NEXT='ab7c6739-be64-11ea-b478-080027fd2c56:4';
BEGIN;COMMIT;
SET GTID_NEXT='ab7c6739-be64-11ea-b478-080027fd2c56:5';
BEGIN;COMMIT;
SET GTID_NEXT='ab7c6739-be64-11ea-b478-080027fd2c56:6';
BEGIN;COMMIT;
SET GTID_NEXT='ab7c6739-be64-11ea-b478-080027fd2c56:7';
BEGIN;COMMIT;
SET GTID_NEXT='ab7c6739-be64-11ea-b478-080027fd2c56:8';
BEGIN;COMMIT;
SET GTID_NEXT='ab7c6739-be64-11ea-b478-080027fd2c56:9';
BEGIN;COMMIT;
SET GTID_NEXT='ab7c6739-be64-11ea-b478-080027fd2c56:10';
BEGIN;COMMIT;
SET GTID_NEXT='ab7c6739-be64-11ea-b478-080027fd2c56:11';
BEGIN;COMMIT;
SET GTID_NEXT='ab7c6739-be64-11ea-b478-080027fd2c56:12';
BEGIN;COMMIT;
SET GTID_NEXT='ab7c6739-be64-11ea-b478-080027fd2c56:13';
BEGIN;COMMIT;
SET GTID_NEXT='ab7c6739-be64-11ea-b478-080027fd2c56:14';
BEGIN;COMMIT;
SET GTID_NEXT='ab7c6739-be64-11ea-b478-080027fd2c56:15';
BEGIN;COMMIT;
SET GTID_NEXT='ab7c6739-be64-11ea-b478-080027fd2c56:16';
BEGIN;COMMIT;
SET GTID_NEXT='ab7c6739-be64-11ea-b478-080027fd2c56:17';
BEGIN;COMMIT;
SET GTID_NEXT='ab7c6739-be64-11ea-b478-080027fd2c56:18';
BEGIN;COMMIT;
SET GTID_NEXT='ab7c6739-be64-11ea-b478-080027fd2c56:19';
BEGIN;COMMIT;
SET GTID_NEXT='ab7c6739-be64-11ea-b478-080027fd2c56:20';
BEGIN;COMMIT;
SET GTID_NEXT='ab7c6739-be64-11ea-b478-080027fd2c56:21';
BEGIN;COMMIT;
SET GTID_NEXT='ab7c6739-be64-11ea-b478-080027fd2c56:22';
BEGIN;COMMIT;
SET GTID_NEXT='ab7c6739-be64-11ea-b478-080027fd2c56:23';
BEGIN;COMMIT;
SET GTID_NEXT='ab7c6739-be64-11ea-b478-080027fd2c56:24';
BEGIN;COMMIT;
SET GTID_NEXT='ab7c6739-be64-11ea-b478-080027fd2c56:25';
BEGIN;COMMIT;
SET GTID_NEXT='ab7c6739-be64-11ea-b478-080027fd2c56:26';
BEGIN;COMMIT;
SET GTID_NEXT='ab7c6739-be64-11ea-b478-080027fd2c56:27';
BEGIN;COMMIT;
SET GTID_NEXT='ab7c6739-be64-11ea-b478-080027fd2c56:28';
BEGIN;COMMIT;
SET GTID_NEXT='ab7c6739-be64-11ea-b478-080027fd2c56:29';
BEGIN;COMMIT;
SET GTID_NEXT='AUTOMATIC';

A script may be programmed to generate the above statements
Now we should be able to start the slave
start slave;

Tech Notes:- log file at /var/log/ansible.log is not writeable and we cannot create it, aborting

This is one of the errors that we encounter after installing ansible and trying to run our first playbook as a non-root user. To fix this all that we need to do is to create a group called ansible , add the user who is executing the playbook to the ansible group, then touch the file /var/log/asnible.log with root as the owner and ansible as the group.Permission should be 775 which is read write and execute for  owner and group and read and execute for others.

# sudo touch /var/log/ansible.log

#sudo groupadd ansible

# sudo chown root:ansible /var/log/ansible.log

# sudo chmod 775 /var/log/ansible.log

# sudo usermod sudeep -aG ansible

You need to signout and sign in before executing the playbook.

Tech Notes:- Mysql Replication Error 'Got fatal error 1236 from master when reading data from binary log: 'Could not find first log file name in binary log index file''

I kept my slave mysql db down for a week and then when I switched on the slave db server and checked the replication status, I started seeing this error.



Last_IO_Errno: 1236

Last_IO_Error: Got fatal error 1236 from master when reading data from binary log: 'Could not find first log file name in binary log index file'



The most probable reason for this error is that the binary log required for the replication by slave servers no longer exists on the master database server. This could have happened because someone might have manually deleted the binary files on the MySQL master either by removing the files from the file system or using the purge binary logs command.

Another reason could be that a variable has been set in the conf file to expire the logs. In my case, it was this one and the variable was set to 2 days

expire_logs_days=2

Let's have a detailed look at SHOW SLAVE STATUS and SHOW BINARY LOGS on master

mysql> show slave status\G
*************************** 1. row ***************************
               Slave_IO_State:
                  Master_Host: 172.25.1.20
                  Master_User: slave
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000023
          Read_Master_Log_Pos: 94955731
               Relay_Log_File: mysqld-relay-bin.000007
                Relay_Log_Pos: 4
        Relay_Master_Log_File: mysql-bin.000023
             Slave_IO_Running: No
            Slave_SQL_Running: Yes

mysql> SHOW BINARY LOGS;

+------------------+-----------+

| Log_name         | File_size |

+------------------+-----------+

| mysql-bin.000029 |      1315 |

+------------------+-----------+

1 row in set (0.21 sec)

So reading it all to gether, slave is looking for the master log file mysql-bin.000023 at position 94955731. But the master is at  mysql-bin.000029  and I dont have the file mysql-bin.000023. So this basically means that the mysql slave stopped replcation while at mysql-bin.000023.

How do we solve this?

The simplest way as well as the advisable approach is to dump the mysql from the master, restore it to slave and then restart the replication. But that may take time if the database is huge and there is no way to take the dump without impacting production performance.

If its a test system and you are ok with losing some data in replication, the best bet would be to set the position of the slave to the start of the new available master file.  

STOP SLAVE;

CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000029', MASTER_LOG_POS=4;

START SLAVE;

You may need to skip some positions sometimes. If you have patience and time, the best option is to examine the events in the log and point to the position accordingly. You can do it using the command below

sudo mysqlbinlog /var/lib/mysql/mysql-bin.000029 | more

Tech Notes:- Windows Update Not Working Windows Update Error 80070422, 80244019, or 8DDD0018

While tweaking the OS for better performance most of us end up disabling services which are required. The most common mistake that we make is with respect to Windows update and then we end up with errors like 80070422, 80244019, or 8DDD0018.In most cases the updates will start working once you enable the following services

Background Intelligent Transfer Service
Windows Event Log
Windows Update
Software Licensing
Windows Modules Installer  

Tech Notes--Using Tail..

I found this command very handy in numerous occasions both in Unix/Linux and Windows.Tail command is used mainly for monitoring logs, especially real time monitoring.In unix/linux, its as easy as opening a shell and typing

tail -f .
Example:- tail -f /var/log/message

It will start displaying you the system log messages on screen with details, like time, machine name, process related info or warning or message.This will continuously display messages.To stop it we can use "Ctlr + c"

There is a windows equivalent. This is not part of OS. You need to install "Windows Server 2003 Resource Kit Tools"

1. Go to the Microsoft Windows Server 2003 download section at http://www.microsoft.com/windowsserver2003/downloads/tools/default.mspx. Or, if that link does not work, visit http://www.microsoft.com/ and search for "Windows 2003". Once there, choose the "Downloads -> Tools" link.

2. Select the link "Windows Server 2003 Resource Kit Tools".

3. Click the "Download" link.Follow the instructions to download and install the product. The default install directory is "C:\Program Files\Windows Resource Kits\Tools\".

Once the Windows 2003 Resource Kit is installed, you may need to reboot your machine for the directory "C:\Program Files\Windows Resource Kits\Tools\" to be added to your path.


I found this command useful to tail IIS logs real time to check traffic and troubleshoot connectivity issues from remote host.

To get help regarding the command enter the following
tail /?

This tool can also be used for different purposes other than real time log monitoring. For example to see the last ten line of windows update log
C:\>c:
C:\>cd \windows
C:\>tail -10 "Windows Update.log"

Other options:

C:\>tail -n "Windows Update.log"

Display the last 'n' lines from file Windows Update.log. For example, view the last 20 lines in the Windows Setup log:

C:\>c:
C:\>cd \windows
C:\>tail -20 "Windows Update.log"