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())

Some Useful SQL Server Commands

From last some time i am working with one of my SQL DBA friend. In this time i have learned something about SQL Server Also, 

Here are some of useful SQL DBA Commands. There all queries are using SQL Server DMV.

Get the information about CPU Consuming Queries

SELECT TOP ( 10 )
SUBSTRING(ST.text, ( QS.statement_start_offset / 2 ) + 1,
( ( CASE statement_end_offset
WHEN -1 THEN DATALENGTH(st.text)
ELSE QS.statement_end_offset
END - QS.statement_start_offset ) / 2 ) + 1)
AS statement_text ,
execution_count ,
total_worker_time / 1000 AS total_worker_time_ms ,
( total_worker_time / 1000 ) / execution_count
AS avg_worker_time_ms ,
total_logical_reads ,
total_logical_reads / execution_count AS avg_logical_reads ,
total_elapsed_time / 1000 AS total_elapsed_time_ms ,
( total_elapsed_time / 1000 ) / execution_count
AS avg_elapsed_time_ms ,
qp.query_plan
FROM sys.dm_exec_query_stats qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) st
CROSS APPLY sys.dm_exec_query_plan(qs.plan_handle) qp
ORDER BY total_worker_time DESC

Find Out Number of Logical CPU in System using SQL Server

SELECT cpu_countFROM sys.dm_os_sys_info
GO-- Identify Virtual Processors in for SQL Server 2000CREATE TABLE #TempTable([Index] VARCHAR(2000),[Name] VARCHAR(2000),[Internal_Value] VARCHAR(2000),[Character_Value] VARCHAR(2000)) ;INSERT INTO #TempTableEXEC xp_msver;SELECT Internal_Value AS VirtualCPUCountFROM #TempTableWHERE Name = 'ProcessorCount';DROP TABLE #TempTableGO

Recently Executed Query

SELECT
deqs.last_execution_time AS [Time], dest.TEXT AS [Query]

FROM sys.dm_exec_query_stats AS deqs

CROSS APPLY sys.dm_exec_sql_text(deqs.sql_handle) AS dest

ORDER BY deqs.last_execution_time DESC

Query To Find Out Full Details of Databases 

SELECT database_id,CONVERT(VARCHAR(25), DB.name) AS dbName,CONVERT(VARCHAR(10), DATABASEPROPERTYEX(name, 'status')) AS [Status],state_desc,
(SELECT COUNT(1) FROM sys.master_files WHERE DB_NAME(database_id) = DB.name AND type_desc = 'rows') AS DataFiles,
(SELECT SUM((size*8)/1024) FROM sys.master_files WHERE DB_NAME(database_id) = DB.name AND type_desc = 'rows') AS [Data MB],
(SELECT COUNT(1) FROM sys.master_files WHERE DB_NAME(database_id) = DB.name AND type_desc = 'log') AS LogFiles,
(SELECT SUM((size*8)/1024) FROM sys.master_files WHERE DB_NAME(database_id) = DB.name AND type_desc = 'log') AS [Log MB],user_access_desc AS [User access],recovery_model_desc AS [Recovery model],CASE compatibility_levelWHEN 60 THEN '60 (SQL Server 6.0)'WHEN 65 THEN '65 (SQL Server 6.5)'WHEN 70 THEN '70 (SQL Server 7.0)'WHEN 80 THEN '80 (SQL Server 2000)'WHEN 90 THEN '90 (SQL Server 2005)'WHEN 100 THEN '100 (SQL Server 2008)'END AS [compatibility level],CONVERT(VARCHAR(20), create_date, 103) + ' ' + CONVERT(VARCHAR(20), create_date, 108) AS [Creation date],-- last backupISNULL((SELECT TOP 1CASE TYPE WHEN 'D' THEN 'Full' WHEN 'I' THEN 'Differential' WHEN 'L' THEN 'Transaction log' END + ' – ' +LTRIM(ISNULL(STR(ABS(DATEDIFF(DAY, GETDATE(),Backup_finish_date))) + ' days ago', 'NEVER')) + ' – ' +CONVERT(VARCHAR(20), backup_start_date, 103) + ' ' + CONVERT(VARCHAR(20), backup_start_date, 108) + ' – ' +CONVERT(VARCHAR(20), backup_finish_date, 103) + ' ' + CONVERT(VARCHAR(20), backup_finish_date, 108) +' (' + CAST(DATEDIFF(second, BK.backup_start_date,BK.backup_finish_date) AS VARCHAR(4)) + ' '+ 'seconds)'FROM msdb..backupset BK WHERE BK.database_name = DB.name ORDER BY backup_set_id DESC),'-') AS [Last backup],CASE WHEN is_fulltext_enabled = 1 THEN 'Fulltext enabled' ELSE '' END AS [fulltext],CASE WHEN is_auto_close_on = 1 THEN 'autoclose' ELSE '' END AS [autoclose],page_verify_option_desc AS [page verify option],CASE WHEN is_read_only = 1 THEN 'read only' ELSE '' END AS [read only],CASE WHEN is_auto_shrink_on = 1 THEN 'autoshrink' ELSE '' END AS [autoshrink],CASE WHEN is_auto_create_stats_on = 1 THEN 'auto create statistics' ELSE '' END AS [auto create statistics],CASE WHEN is_auto_update_stats_on = 1 THEN 'auto update statistics' ELSE '' END AS [auto update statistics],CASE WHEN is_in_standby = 1 THEN 'standby' ELSE '' END AS [standby],CASE WHEN is_cleanly_shutdown = 1 THEN 'cleanly shutdown' ELSE '' END AS [cleanly shutdown]FROM sys.databases DBORDER BY dbName, [Last backup] DESC, NAME

Hope these will also help you. :)

 

Useful MySQL command line Shortcuts and commands

In our daily MySQL Process we need to do some task repeatedly. Below are some useful  command line shortcut to work with MySQL. 

\? = help 

\c = clear command, It Will cancel last executed command. 

\C = switch to another charset, It will help if you want to use another character set. Make sure you are choosing correct charcter Set.

\d = set delimiter (use something else after a statement instead of ;), It will help whenever you are  creating function and procedure.

\g = send command to server , it will help to execute command , Will work as delimiter

\G = send command to server, Will work as delimiter , It will show resultset in grid format.

With \g and \G you can generate "vertical" output with each column value on a separate line. This makes large outputs far more readable !

\h = help, to See the help content and commands

\p = print current command

\q = quit MySQL
\r = reconnect (or see which database you're in)

\R = change your mysql prompt (personalize your prompt, which can be funny)

\s = show status (which can be very informative)

\t = don't write to into outfile
\T = write into outfile
\u = use database

\w = don't show warnings after every statement
\W = show warnings after every statement
\# = rebuild completion hash
\. = execute an SQL scriptfile

select database()= To know current database.

Enjoy Short cuts :)