How THP and Kernel Version effects MySQL Stability

During last year , i have faced multiple stall issue. MySQL Service was getting stalled any time, without leaving any clue in any logs. MySQL Service was getting restarted after 15 Minute of stall. We (Me and My Team ) were clueless.

Initially ,we were under impression, we may hit any bug. We gone through all MySQL bug tried multiple options.

1. Moving Redo Logs to magnetic Disk

2. optimizing flushing method/Thread

3. Optimizing all Database (Recreate using mysqldump and restore)

Still no Luck , we were still clueless.

We move forward and tried capturing all information using pt-stalk.

pt-stalk --user= --ask-pass --collect --daemonize --run-time=10 --sleep=10 --cycles=3 –dest= --log=

On Next failure we analyzed and still unable to find RCA. We were under impression that there are some queries which causing this behavior . We changed our focus and try to optimize all possible. Still we were facing random downtime. MySQL got stall and We need to restart as it stops responding.

We used oprofile with pt-stalk which lead us to issues with THP. We also got clue from Oliver's blog post

Debugging a MySQL stall

We disabled THP, one system got stable while anothe was still stalling randonly. We started using perf to get more deep in system calls.

% time     seconds  usecs/call     calls    errors syscall

------ ----------- ----------- --------- --------- ----------------

 70.81 1122.971901       27982     40132     14704 futex

 19.98  316.848873     1460133       217         6 restart_syscall

  5.70   90.315204       52448      1722           io_getevents

It lead us to futex Bug which was in kernel-2.6.32-504. According to Blooger

“The impact of this kernel bug is very simple: user processes can deadlock and hang in seemingly impossible situations. A futex wait call (and anything using a futex wait) can stay blocked forever, even though it had been properly woken up by someone.  If you are lucky you may also find soft lockup messages in your dmesg logs. If you are not that lucky (like us, for example), you'll spend a couple of months of someone's time trying to find the fault in your code, when there is nothing there to find.”
It was leaving is clueless every time. First we tested same by using strace. Which resumed MySQL process. Later we upgrdaed our Kernel version and MySQL service start working perfectly fine.
Detailed discussion about this bug is available at

https://groups.google.com/forum/#!searchin/mechanical-sympathy/futex/mechanical-sympathy/QbmpZxp6C64/BonaHiVbEmsJ



Learning

1. THP is not good for database.  
2. Linux expertise always for troubleshooting.
3. Start thinking outside of database for troubleshooting(MySQL always doesn't hit Bug :) )

MySQL: ERROR 1345 (HY000): EXPLAIN/SHOW can not be issued; lacking privileges for underlying table

While performing select on view one of db user got below error

ERROR 1345 (HY000): EXPLAIN/SHOW can not be issued; lacking privileges for underlying table

Tough user was having select access on required database.  I was surprise with error.  After trying for some time, I got answer. This error occurs in versions after MySQL 5.5.16.  After MySQL version upgraded to MySQL 5.5.19, occurrences of this error started.  I got to know, it’s an intended change of behavior.

Simply having the SELECT privilege on the view/tables should not be enough to see the information returned by EXPLAIN SELECT - e.g. the view's structure.

Now users also require the “SHOW VIEW” privilege on the view in order to do EXPLAIN SELECT on View.

MySQL and MS SQL Datatype Comparison

While working on Database migration between MS SQL and MySQL(MSSQL-> MySQL) ,i compared different datatype   available in MS SQL and MySQL. Below is complete list.

Data Type Comparison Between MySQL and MS SQL Server
MySQL	MS SQL	Comments
Integer
TINYINT(1)	BIT
TINYINT	TINYINT	Tinyint in MySQL is always unsigned
SMALLINT	SMALLINT
MEDIUMINT, INT	INT
BIGINT	BIGINT
Decimal
DECIMAL(M,D)	decimal(p,s)	Default precision is 18. The maximum precision is 38. The default scale is 0.
Floating Data types
FLOAT(N)	float(n)
FLOAT(M,D)	float(24)
DOUBLE(M,D); REAL(M,D)	float(53)
DOUBLE(M,D)	smallmoney, money
Date and Time Data Types
DDATETIME	datetime2*	It supports dates from 0001-01-01 through 9999-01-01
DATE	date*	It supports dates from 0001-01-01 to 9999-12-31
TIME	time2*	Time range is 00:00:00.0000000 through 23:59:59.9999999
TIMESTAMP	smalldatetime	smalldatetime value range is 1900-01-01 through 2079-06-06
YEAR	Smallint	-
String Data type
CHAR	nchar(n), uniqueinentifier	nchar allows 4000 characters
VARCHAR, TINYTEXT, TEXT(M), MEDIUMTEXT, LONGTEXT	nvarchar(n|max)	nvarchar allows 4000 characters but max indicates maximum storage size of 2^31-1 bytes.
Binary Data type
BINARY(M)	binary(n)	binary allows 8000 bytes
VARBINARY(M), TINYBLOB, BLOB, MEDIUMBLOB, LONGBLOB	varbinary(n|max)	nvarbinary allows 8000 bytes but max indicates maximum storage size of 2^31-1 bytes.
BLOB	sql_variant	-
LONGBLOB	image	-

MySQL 5.6 Installation Error and solution

While installing MySQL 5.6  on Linux faced one error today.

Can't locate Data/Dumper.pm in @INC (@INC contains: /usr/local/lib64/perl5 /usr/local/share/perl5 /usr/lib64/perl5/vendor_perl /usr/share/perl5/vendor_perl /usr/lib64/perl5 /usr/share/perl5 .) at /usr/bin/mysql_install_db line 42.

BEGIN failed--compilation aborted at /usr/bin/mysql_install_db line 42. 

This error was coming due to unavailability of package perl(Data::Dumper)'. While perl was up to date i was getting this error.

Solution :

Install perl "perl(Data::Dumper)" package on machine.

First try installating perl-devel by using below command.

yum install perl perl-devel

If still the same error you need to install Data:Dumper module of perl. Install module by using below command.

yum install 'perl(Data::Dumper)'

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.

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

Comparison of Date functions between SQL Server and MySQL

In our support life we all are working of multiple RDBMS at same. It's tough to remember all function available in different RDBMS. We try to run same query which was running in another RDBMS :). Here i am comparing the date function available in MS SQL Server and MySQL. Hope this will help.

SQL SERVER	MYSQL
DATEADD(day, 1, GETDATE())	DATE_ADD(NOW(), INTERVAL 1 DAY)
DATEDIFF(day, GETDATE(), GETDATE()-1)	DATEDIFF(NOW(), NOW() – INTERVAL 1 DAY)
DATENAME(month, GETDATE())	DATE_FORMAT(NOW(), ‘%M’) MONTHNAME(NOW())
DATENAME(weekday, GETDATE())	DATE_FORMAT(NOW(), ‘%W’) DAYNAME(NOW())
DATEPART(month, GETDATE())	DATE_FORMAT(NOW(), ‘%m’)
DAY(GETDATE())	DATE_FORMAT(NOW(), ‘%d’) DAY(NOW())) DAYOFMONTH(NOW())
GETDATE()	NOW() SYSDATE() CURRENT_TIMESTAMP CURRENT_TIMESTAMP()
GETDATE() + 1	NOW() + INTERVAL 1 DAY CURRENT_TIMESTAMP + INTERVAL 1 DAY
GETUTCDATE()	UTC_TIMESTAMP()
MONTH(GETDATE())	MONTH(NOW())
YEAR(GETDATE())	YEAR(NOW())