Sunday, December 26, 2010

Aborted connection 31084472 to db: ‘ms’ user: ‘login’ host: `x.x.x.12′ (Got timeout reading communication packets)

ERROR Message :
081016 23:25:44 Aborted connection 31084472 to db: ‘ms’ user: ‘login’ host: `x.x.x.12′ (Got timeout reading communication packets)
081016 23:25:44 Aborted connection 31084391 to db: ‘ms’ user: ‘login’ host: `x.x.x.13′ (Got timeout reading communication packets)
081016 23:25:44 Aborted connection 31084488 to db: ‘ms’ user: ‘login’ host: `x.x.x.15′ (Got timeout reading communication packets)
081016 23:25:44 Aborted connection 31084509 to db: ‘ms’ user: ‘login’ host: `x.x.x.15′ (Got timeout reading communication packets)
081016 23:25:45 Aborted connection 31084518 to db: ‘ms’ user: ‘login’ host: `x.x.x..11′ (Got an error reading communication packets)
The below are the Reasons to get the above error :
Aborted connections messages appear in the error log, the cause can be any of the following:
1) The client program did not call mysql_close() before exiting.
2) The client had been sleeping more than wait_timeout or interactive_timeout seconds without issuing any requests to the server.
3) The client program ended abruptly in the middle of a data transfer.
When any of these things happen, the server increments the Aborted_clients status variable.
The server increments the Aborted_connects status variable when the following things happen:
1) A client doesn’t have privileges to connect to a database.
2) A client uses an incorrect password.
3) A connection packet doesn’t contain the right information.
4) It takes more than connect_timeout seconds to get a connect packet.

-Suresh Kuna

Fatal error: Can’t change to run as user ‘mysql’ ; Please check that the user exists!

Problem Statement:

I got the error while starting the server, and is it not able to start as mysql user.
It throws the below error

080915 19:58:10 mysqld started
Fatal error: Can’t change to run as user ‘mysql’ ; Please check that the user exists!
080915 19:58:10 Aborting

080915 19:58:10 /usr/local/mysql4.1.3/bin/mysqld: Shutdown complete

080915 19:58:10 mysqld ended

080915 20:03:59 mysqld started
Fatal error: Can’t change to run as user ‘mysql’ ; Please check that the user exists!
080915 20:03:59 Aborting

080915 20:03:59 /usr/local/mysql4.1.3/bin/mysqld: Shutdown complete

080915 20:03:59 mysqld ended

080915 20:09:07 mysqld started
Fatal error: Can’t change to run as user ‘mysql’ ; Please check that the user exists!
080915 20:09:07 Aborting

080915 20:09:07 /usr/local/mysql4.1.3/bin/mysqld: Shutdown complete

080915 20:09:07 mysqld ended

080915 20:12:21 mysqld started
080915 20:12:21 InnoDB: Started; log sequence number 0 43644
/usr/local/mysql/bin/mysqld: ready for connections.
Version: ‘4.1.3-beta-standard’ socket: ‘/tmp/mysql3311.sock’ port: 3311
080915 20:12:21 Slave SQL thread initialized, starting replication in log ‘hawk-bin.000027′ at position 301237442, relay log
‘./menus-relay-bin.000006′ position: 4
080915 20:12:21 Slave I/O thread: connected to master ‘replicate@x.x.x.90:3306′, replication started in log ‘hawk-bin.
000027′ at position 301237442
080915 20:12:21 Error reading packet from server: Client requested master to start replication from impossible position (ser
ver_errno=1236)
080915 20:12:21 Got fatal error 1236: ‘Client requested master to start replication from impossible position’ from master wh
en reading data from binary log
080915 20:12:21 Slave I/O thread exiting, read up to log ‘hawk-bin.000027′, position 301237442
080915 20:13:07 Aborted connection 4 to db: ‘unconnected’ user: ‘root’ host: `localhost’ (Got an error writing communication
packets)
080915 20:13:07 /usr/local/mysql/bin/mysqld: Normal shutdown

080915 20:13:07 Error reading relay log event: slave SQL thread was killed
080915 20:13:07 InnoDB: Starting shutdown…
080915 20:13:09 InnoDB: Shutdown completed; log sequence number 0 43644
080915 20:13:09 /usr/local/mysql/bin/mysqld: Shutdown complete

080915 20:13:09 mysqld ended

080915 20:23:30 mysqld started
080915 20:23:31 InnoDB: Started; log sequence number 0 43644
/usr/local/mysql/bin/mysqld: ready for connections.
Version: ‘4.1.3-beta-standard’ socket: ‘/tmp/mysql3311.sock’ port: 3311
080915 20:23:31 Slave SQL thread initialized, starting replication in log ‘hawk-bin.000027′ at position 301237442, relay log
‘./menus-relay-bin.000008′ position: 4
080915 20:23:31 Slave I/O thread: connected to master ‘replicate@x.x.x.90:3306′, replication started in log ‘hawk-bin.
000027′ at position 301237442
080915 20:23:31 Error reading packet from server: Client requested master to start replication from impossible position (ser
ver_errno=1236)
080915 20:23:31 Got fatal error 1236: ‘Client requested master to start replication from impossible position’ from master wh
en reading data from binary log
080915 20:23:31 Slave I/O thread exiting, read up to log ‘hawk-bin.000027′, position 301237442
080915 20:34:49 Aborted connection 3 to db: ‘unconnected’ user: ‘root’ host: `localhost’ (Got an error reading communication
packets)
080915 20:35:56 Error reading relay log event: slave SQL thread was killed
080915 20:35:59 Slave SQL thread initialized, starting replication in log ‘hawk-bin.000027′ at position 301237442, relay log
‘./menus-relay-bin.000009′ position: 4

Solution :
The issue was solve by running the server with “sudo - mysql” before the mysql start up script.

sudo -u mysql $BASEDIR/bin/mysqld –defaults-file=$DEFAULT –basedir=$BASEDIR –datadir=$DATADIR \
–user=mysql –pid-file=$PIDFILE 2>&1

Could not parse relay log event entry.

If a slave show the below error then

1) If the slaves relay-log is corrupted, the run the change master to get the slave in sync with master.

2) we have to import a fresh dump from master to get the slave in sync with master if the master’s binlog is corrupted.

Show slave status :

mysql> show slave status\G
*************************** 1. row ***************************
Master_Host: 10.x.x.39
Master_User: replicate
Master_Port: 3307
Connect_retry: 60
Master_Log_File: db-master.077
Read_Master_Log_Pos: 7400184
Relay_Log_File: db-slave-relay-bin.010
Relay_Log_Pos: 150588765
Relay_Master_Log_File: db-master.076
Slave_IO_Running: Yes
Slave_SQL_Running: No
Replicate_do_db: broadbandnew,archive
Replicate_ignore_db:
Last_errno: 0
Last_error: Could not parse relay log event entry. The possible reasons are: the master’s binary log is corrupted (you can check this by running ‘mysqlbinlog’ on the binary log), the slave’s relay log is corrupted (you can check this by running ‘mysqlbinlog’ on the relay log), a network problem, or a bug in the master’s or slave’s MySQL code. If you want to check the master’s binary log or slave’s relay log, you will be able to know their names by issuing ‘SHOW SLAVE STATUS’ on this slave.
Skip_counter: 0
Exec_master_log_pos: 150588671
Relay_log_space: 157989718
1 row in set (0.00 sec)

Duplicate entry for key 1′ on query

If the slave show the following error then skip the same as below.

mysql> show slave status\G
*************************** 1. row ***************************
Master_Host: x.x.4.3
Master_User: replicate
Master_Port: 3307
Connect_retry: 60
Master_Log_File: master.077
Read_Master_Log_Pos: 7394212
Relay_Log_File: slave- relay-bin.010
Relay_Log_Pos: 150588010
Relay_Master_Log_File: master.076
Slave_IO_Running: Yes
Slave_SQL_Running: No
Replicate_do_db: bandnew,archive
Replicate_ignore_db:
Last_errno: 1062
Last_error: Error ‘Duplicate entry ‘122453106710.31.87.80-mdazaz123-2008-10-21 03:38:23′ for key 1′ on query. Default database: ‘bandnew’. Query:

‘insert into br_info(UI_ID,UI_USER,CD_CAF,BI_PRODUCT,BI_STIME,BI_ETIME,BI_TIMEUTILIZED,BI_OPENINGBAL,BI_CLBAL,

BI_BILLEDAMT,BI_USERIP,BI_SERVICENAME,BI_BYTESIN,BI_BYTESOUT,BI_BYTES,BI_REMARKS,BI_PUBLICIP,BI_SAMIP) values

(’122453106710.31.87.80′,’mdz123′,’Default’,'D4NL’,'2008-10-21 03:38:23′,

‘2008-10-21 06:10:24′,152,197.09,195.08,’2.01′,’10.31.87.80′,’F',295137740,125414454,420552194,’D',’118.9.4.47′,’210.1.80.3′)’
Skip_counter: 0
Exec_master_log_pos: 150587868
Relay_log_space: 157983746
1 row in set (0.00 sec)

mysql> set global sql_slave_skip_counter=1;start slave;show slave status\G
Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

*************************** 1. row ***************************
Master_Host: x.x.4.3
Master_User: replicate
Master_Port: 3307
Connect_retry: 60
Master_Log_File: master.077
Read_Master_Log_Pos: 7394212
Relay_Log_File: slave- relay-bin.010
Relay_Log_Pos: 150588010
Relay_Master_Log_File: master.076
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Replicate_do_db: bandnew,archive
Replicate_ignore_db:
Last_errno: 0
Last_error:
Skip_counter: 1
Exec_master_log_pos: 150587868
Relay_log_space: 157983746
1 row in set (0.00 sec)

Error: log file /mysql/data/mysqld/ib_logfile0 is of different size

ProblemStatement:

I have got the below error while starting the mysql after configuring a new slave with the tall ball of the master data directory.

081022 6:24:41 InnoDB: Warning: shutting down a not properly started
InnoDB: or created database!
081022 6:24:41 /usr/local/mysql4020/libexec/mysqld: Shutdown Complete

InnoDB: Error: log file /mysql/data/mysqld/ib_logfile0 is of different size 0 209715200 bytes
InnoDB: than specified in the .cnf file 0 104857600 bytes!
081022 6:24:42 Can’t init databases
081022 6:24:42 Aborting

Solution :

The same has been resolved by changing the below variable in the cnf file as same in the master config file

innodb_log_file_size = 200M

Error: ‘Access denied for user ‘replicate’@'10.0.0.8′ (using password: YES)’

Problem Statement:

we got the below error message while setting the replication.

Error: ‘Access denied for user ‘replicate’@'10.0.0.8′ (using password: YES)’.

Solution :

<18:09:58>root@6.83: ~#mysql -u root -p -h10.0.0.55 -P3306

If the error is password error then edit the password error.
Error: ‘Access denied for user ‘replicate’@'10.0.0.8′ (using password: YES)’

<18:09:58>root@6.83: ~#vi /etc/my.cnf (To change password, edit the cnf file)
<18:09:58>root@6.83: ~#less /data/mysqldata/err.log

mysql> change master to master_password=’xxxxx’;

mysql> change master to master_password=’replicate’,master_user=’replicate’,master_host=’10.0.0.55′,master_port=3306,master_log_file=’narnia-bin.000030′,master_log_pos=271266680;

mysql> stop slave;
mysql> start slave;

mysql> show slave status\G
*************************** 1. row ***************************
Slave_IO_State: Connecting to master
Master_Host: 10.0.0.55
Master_User: replicate
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: arnia-bin.000030
Read_Master_Log_Pos: 271260976
Relay_Log_File: relay-bin.000004
Relay_Log_Pos: 4
Relay_Master_Log_File: arnia-bin.000030
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Replicate_Do_DB:
Replicate_Ignore_DB:
Replicate_Do_Table: mail.appearance_new,pop.hostcnt
Replicate_Ignore_Table:
Replicate_Wild_Do_Table:
Replicate_Wild_Ignore_Table:
Last_Errno: 0
Last_Error:
Skip_Counter: 0
Exec_Master_Log_Pos: 271266680
Relay_Log_Space: 4
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
1 row in set (0.00 sec)

Got fatal error 1236: ‘Client requested master to start replication from impossible position’ from master when reading data from binary log

Problem statement:

The below is problem I faced in a well running replication system.

Error Message :
081016 18:34:43 Error reading packet from server: Client requested master to start replication from impossible position (ser
ver_errno=1236)
081016 18:34:43 Got fatal error 1236: ‘Client requested master to start replication from impossible position’ from master when reading data from binary log
081016 18:34:43 Slave I/O thread exiting, read up to log ‘lot-bin.000065′, position 13664242
081016 18:35:04 Error reading relay log event: slave SQL thread was killed
081016 18:45:40 Slave SQL thread initialized, starting replication in log ‘lot-bin.000065′ at position 13664242, relay log’./mx5-relay-bin.000001′ position: 13664284
081016 18:45:40 Slave I/O thread: connected to master ‘replicate@10.0.4:3306′, replication started in log ‘lot-bin.
000065′ at position 13664242

Solution :
1) If the value of Read_Master_Log_Pos has a normal value, and slave asked weird position, this could be some internal bug. Now I see only a single way, but it could be difficult: if you have –log-slave-updates, use slave binary logs to find the last executed statement and it’s position in master logs, and CHANGE MASTER to that position.
2) Go onto the master execute
SHOW MASTER STATUS
Look at the output and find the log that the slave is pointing to. Look at the File size field.
Next look at the slave output from the slave reporting the issue. Look at Exec_Master_Log_Pos, is that value greater then the File Size on the master if so issue
CHANGE MASTER TO MASTER_LOG_FILE=[NEXT FILE], MASTER_LOG_POS=4;
slave start;

ERROR 1251: Client does not support authentication protocol

Problem Statement:

I got the below error while trying to accessing the upgraded mysqld server with a old client.

Error :

ERROR 1251: Client does not support authentication protocol
requested by server; consider upgrading MySQL client

Solution :

I have resolved the above my updating the password as below.

update user set password=old_password(’new_password’) where user=’user_name’ and host=’10.0.0.2′;

flush privileges;

and then try to access the mysqld server and now you will be able to connect to the server succesfully.

Can’t get CREATE TABLE (Can’t open file: ‘Tory_Map.MYI’. (errno: 145))

Error Message :
/usr/local/mysql/bin/mysqldump: Can’t get CREATE TABLE for table `Tory_Map` (Can’t open file: ‘Tory_Map.MYI’. (errno: 145))

Solution :
1) Do a show table status as below:
mysql> show table status like ‘Tory_Map’\G
*************************** 1. row ***************************
Name: Tory_Map
Type: NULL
Row_format: NULL
Rows: NULL
Avg_row_length: NULL
Data_length: NULL
Max_data_length: NULL
Index_length: NULL
Data_free: NULL
Auto_increment: NULL
Create_time: NULL
Update_time: NULL
Check_time: NULL
Create_options: NULL
Comment: Can’t open file: ‘Tory_Map.MYI’. (errno: 145)
1 row in set (0.00 sec)

2) and then do a check table as below.
mysql> check table Tory_Map;
+——————+——-+———-+——————————-+
| Table | Op | Msg_type | Msg_text |
+——————+——-+———-+——————————-+
| doc.Tory_Map | check | warning | Table is marked as crashed |
| doc.Tory_Map | check | error | Found 3670180 keys of 3670179 |
| doc.Tory_Map | check | error | Corrupt |
+——————+——-+———-+——————————-+
3 rows in set (20 min 1.65 sec)

3) If the table is marked as crashed then do a repair of that table as below.
mysql> repair table Tory_Map;

+——————+——–+———-+———-+
| Table | Op | Msg_type | Msg_text |
+——————+——–+———-+———-+
| doc.Tory_Map | repair | status | OK |
+——————+——–+———-+———-+
1 row in set (1 min 42.77 sec)

and if you do a check table now, the result has to be ok as below.
mysql> check table Tory_Map;
+——————+——-+———-+———-+
| Table | Op | Msg_type | Msg_text |
+——————+——-+———-+———-+
| doc.Tory_Map | check | status | OK |
+——————+——-+———-+———-+
1 row in set (6 min 3.33 sec)

Replication resumed, Apparent master shutdown, Failed reading log event

The slave server is getting this error in error log.

081102 0:30:18 [Note] Slave: connected to master ‘replicate@10.0.1.5:3310′,replication resumed in log ‘central3310.000142′ at position 789529654
081102 0:30:18 [Note] Slave: received end packet from server, apparent master shutdown:
081102 0:30:18 [Note] Slave I/O thread: Failed reading log event, reconnecting to retry, log ‘central3310.000142′ position 789529654

Solution :

The problem has started when a new slave has been configured for the existing setup. The slave server-id is matching exactly with another slave server-id, so the replication is happening for some time and then stopping. Finally one server is getting crashed. Then stop one of the slaves, change the server-id and start the slave to rectify the same.

Error: data file /mysql/data/mysqld/ibdata1 is of a different size

If the Mysql is not starting and the error log consists of the below error after the configuration of a slave with the tar file of master.

081022 6:19:02 InnoDB: Warning: shutting down a not properly started
InnoDB: or created database!
081022 6:19:02 /usr/local/mysql4020/libexec/mysqld: Shutdown Complete

InnoDB: Error: data file /mysql/data/mysqld/ibdata1 is of a different size
InnoDB: 128000 pages (rounded down to MB)
InnoDB: than specified in the .cnf file 65536 pages!
InnoDB: Could not open or create data files.
InnoDB: If you tried to add new data files, and it failed here,
InnoDB: you should now edit innodb_data_file_path in my.cnf back
InnoDB: to what it was, and remove the new ibdata files InnoDB created
InnoDB: in this failed attempt. InnoDB only wrote those files full of
InnoDB: zeros, but did not yet use them in any way. But be careful: do not
InnoDB: remove old data files which contain your precious data!
081022 6:19:04 Can’t init databases
081022 6:19:04 Aborting

Then change the below variable as the same in the master cnf file

innodb_data_file_path =ibdata1:2000M;ibdata2:100M:autoextend
and start the mysqld server.

Lost connection to MySQL server

This type of error may occur in the below scenarios.

1) It indicates network connectivity trouble. If the error message includes “during query” then we have to look at the query and the number of rows being sent.
Then try increasing “net_read_timeout” variable from its default value.
2) It can happen when the client is attempting the initial connection to the server. If your “connect_timeout” value is set to only a few seconds, you can increase the same for resolving this issue.
3) It can occur when using BLOB values that are larger than “max_allowed_packet” and may get “packet too large” as error, then increase the “max_allowed_packet” value to resolve the same.

show procedure status or mysqldump with routines causing mysqld restart in 5.1.40 version

When we take a mysqldump with routines or show procedure status where Db=’dbname’;

The mysqld server got an error like “Lost connection to mysqld server”, when we login into server the will be running and it got restarted.

To fix this, upgrade the db by using mysql_upgrade and it got fixed for me.

MySQL Cluster Query Execution

MySQL Cluster executes the queries depending on the below type of scan’s it does

1. Primary key lookup
2. Unique key lookup
3. Ordered index scan (i.e., non-unique indexes that use T-trees)
4. Full table scan

Let’s say you have 4 data nodes in your cluster (NoOfReplicas=2). This means you have 2 node groups and each one has half the data. Cluster uses a hash on the primary key (unless you’ve controlled the partitioning using the 5.1 partitioning features). So for any table, half the rows are in one node group and half the rows are the in other node group.

Now for the 4 types of query execution. You can verify which type of execution is used with EXPLAIN. Here’s how each ones works:

1. Primary key lookup - the MySQL server can calculate the hash on the primary key and know which data node the relevant row resides in. Then the MySQL server contacts that data node and receives the row.
2. Unique key lookup - the MySQL server cannot know which data node the row might be stored in. So it contacts a pseudo-random node. That data node has a hidden table that maps the unique key values to the primary key. Then the hash on the primary key reveals where the row resides.
3. Ordered index scans are sent in parallel to all data nodes, where they search their local t-tree.
4. Full table scans are send in parallel to all data nodes, where they scan their primary fragment.

Summary: primary key lookups are best. If you have more than 2 nodes, throughput goes up because all nodes are actively serving different fragments of data. Ordered index lookup and full table scans are done in parallel, so more nodes leads to better performance.