Sunday, February 2, 2014

Max Allowed Packet Error in MySQL Replication

 Last week one of my friend called and Ask for help. He told me he is getting below error in his MySQL setup.


 Last_IO_Error: Got fatal error 1236 from master when reading data from binary log: 'log event entry exceeded max_allowed_packet; Increase max_allowed_packet on master; the first event 'mysql_binary_log.000050' at 658752953, the last event read from '/mysql/logs/binary/mysql_binary_log.000050' at 658752953, the last byte read from '/mysql/logs/binary/mysql_binary_log.000050' at 658752972.'

The first solution of it to increase size of max_allowed_packet to start replication.   To configure this variable you need to execute below command.

Check the current Size.

mysql> show variables like 'max%allowed%';
+--------------------+---------+
| Variable_name      | Value   |
+--------------------+---------+
| max_allowed_packet | 4194304 |
+--------------------+---------+
1 row in set (0.08 sec)

mysql>

To increase size execute below commands.

mysql>
mysql> set global max_allowed_packet=41943040
    -> ;
Query OK, 0 rows affected (0.04 sec)

mysql>

Check size by login in another MySQL session.

mysql> show variables like '%allowed%'
    -> ;
+--------------------------+------------+
| Variable_name            | Value      |
+--------------------------+------------+
| max_allowed_packet       | 41943040   |
| slave_max_allowed_packet | 1073741824 |
+--------------------------+------------+
2 rows in set (0.00 sec)

mysql>

It was a simple solution to start work.  Still I was keen to know details /possible reason of this.  I asked  for some more details from him and found current bin log format is configured to “row”. In case of row based binary log size of binary log is more than from mixed/statement based logging.
In his scenario it was due to a big transection which generated 2GB log and replication stopped. Set your binlog format to mixed to avoid these situations.

Saturday, February 1, 2014

Changing Owner in MySQL Procedure



Being a DBA our regular job is related to keep production high available and secure. To secure database we majorly gives rights using IP. In best practice we are never supposed to create user from open to all network (%).
We should create user by using his/her/it connecting ip.  Command should be like below.
Grant "Required rights"  on "databasename"."object Name"  to "username"@’Connection IP’ identified by ‘ "password"’;
Routines in MySQL give additional security. User will not be able to see table name /Actual data. For access on routines (Stored procedure /Function call routines in MySQL), we need to provide some additional rights.
Execute: - To execute procedure/function
Create routine: - To Create procedure/Function
Alter routines: - To Alter routines.
With procedure additional security has been provided by using definer. A definer in MySQL is a user who creates MySQL procedure.   As user is always created by using IP/IP Range so in case any change in network ip will make routines unusable. Users will not be able to execute those procedures. To use these routines you need to change definer. You can change definer by using different ways

1.       Drop procedure and create new procedure
2.       Change definer in mysql.proc table.
Second option is easy to use. Below is the command to change definer.
Update mysql.proc set definer=’New definer’ where definer=’old definer’

Update mysql.proc  set definer=   ‘anil@54.254.24.132’ where definer= ‘anil@localhost’.

It will change definer of stored procedure and you can procedures again.

Happy Developing  J