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

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. :)