Find maximum size from all LOB columns in a MySQL database

- January 28, 2021

Quick Links

1. Create Procedure: usp_max_blob_in_db
2. Execute Procedure: usp_max_blob_in_db

This article will mention a stored procedure that will help you to find maximum size from all LOB columns in a MySQL database.

At the time of this writing, the LOB column in MySQL are any of the data type: 'blob', 'mediumblob', 'longblob', 'text', 'mediumtext' and 'longtext'.

You can execute the below SQL procedure to find the maximum size in bytes.

The stored procedure calling part contains a parameter called @p_raw_format, which acts as follows:

-- Format Parameter: @p_raw_format

-- Values: (0) or Any -> Result in human readable form [KB, MB, GB], (1) -> Result in raw bytes.

Create Procedure

Create the stored procedure usp_max_blob_in_db in the database where you need to find the maximum bytes in any LOB column.

SET @old_sql_notes = @@session.sql_notes;

SET SESSION sql_notes = 0;

DROP PROCEDURE IF EXISTS usp_max_blob_in_db;

DELIMITER $$

CREATE PROCEDURE usp_max_blob_in_db(OUT p_max_blob_byte_size BIGINT UNSIGNED)
BEGIN
	
	DECLARE no_more_lob_column boolean;
	DECLARE is_cursor_open boolean;
	
	DECLARE no_blob_column CONDITION FOR SQLSTATE '40001';

	DECLARE l_max_blob_byte_size BIGINT UNSIGNED;
	
	DECLARE l_table_name VARCHAR(64);
	DECLARE l_column_name VARCHAR(64);
	
	
	DECLARE lob_col_name_cursor cursor for
	select tab.table_name,
		   col.column_name
	from information_schema.tables as tab
	inner join information_schema.columns as col
		on col.table_schema = tab.table_schema
		and col.table_name = tab.table_name
	where tab.table_schema = DATABASE()
		and tab.table_type = 'BASE TABLE'
		and col.data_type in ('blob', 'mediumblob', 'longblob', 'text', 'mediumtext', 'longtext')
	order by tab.table_name, col.column_name
	;
	
	
	DECLARE EXIT HANDLER FOR SQLWARNING
    BEGIN
        ROLLBACK;  -- rollback any changes made in the transaction
		-- close the open cursor
		IF (is_cursor_open) THEN 
			close lob_col_name_cursor;
			SET is_cursor_open = false;
		END IF;
		SELECT 'A warning has occurred, operation rollbacked and the stored procedure was terminated' as Message; -- Convey Message
        RESIGNAL;  -- raise again the sql warning to the caller
    END;
	
    DECLARE EXIT HANDLER FOR SQLEXCEPTION
    BEGIN
        ROLLBACK;  -- rollback any changes made in the transaction
		-- close the open cursor
		IF (is_cursor_open) THEN 
			close lob_col_name_cursor;
			SET is_cursor_open = false;
		END IF;
		SELECT 'An error has occurred, operation rollbacked and the stored procedure was terminated'as Message; -- Convey Message
        RESIGNAL;  -- raise again the sql exception to the caller
    END;
	
	DECLARE CONTINUE HANDLER FOR NOT FOUND
	BEGIN
		set no_more_lob_column = true;
		SELECT 1 INTO @handler_invoked FROM (SELECT 1) AS t;
	END;
	
	IF NOT EXISTS(select 1
	from information_schema.tables as tab
	inner join information_schema.columns as col
		on col.table_schema = tab.table_schema
		and col.table_name = tab.table_name
	where tab.table_schema = DATABASE()
		and tab.table_type = 'BASE TABLE'
		and col.data_type in ('blob', 'mediumblob', 'longblob', 'text','mediumtext','longtext')) THEN
			SIGNAL no_blob_column 
				SET MESSAGE_TEXT = 'No LOB column found!!!';
	END IF
	;
	
	SET l_max_blob_byte_size = 0;
	
	-- Calculate maximum LOB size in the database.
	
	open lob_col_name_cursor;
	
	SET is_cursor_open = true;
	
	lob_col_name_cursor_loop: loop

		fetch lob_col_name_cursor
		into l_table_name, l_column_name;

		if no_more_lob_column then
			leave lob_col_name_cursor_loop;
		end if;
		
		-- select l_table_name, l_column_name;
		
		SET @tmp_blob_byte_size = 0;
		
		SET @sql_query = '';
		SET @sql_query = CONCAT('SELECT MAX(LENGTH(',l_column_name,' )) INTO @tmp_blob_byte_size FROM ',l_table_name);
		PREPARE stmt FROM @sql_query;
		EXECUTE stmt;
		DEALLOCATE PREPARE stmt;
		SET @sql_query = '';
		
		IF (@tmp_blob_byte_size > l_max_blob_byte_size) THEN
			SET l_max_blob_byte_size = @tmp_blob_byte_size;
		END IF;

	end loop lob_col_name_cursor_loop;
	
	IF (is_cursor_open) THEN 
		close lob_col_name_cursor;
		SET is_cursor_open = false;
	END IF;
	
	-- Calculated maximum LOB size in the database.
	
	select l_max_blob_byte_size INTO p_max_blob_byte_size;

END
$$

DELIMITER ;

SET SESSION sql_notes = @old_sql_notes;

Execute Procedure

Execute the stored procedure usp_max_blob_in_db.

SET @old_sql_notes = @@session.sql_notes;

SET SESSION sql_notes = 0;

CALL usp_max_blob_in_db(@v_max_blob_byte_size);

SET @KB_FORMAT = 1024;
SET @MB_FORMAT = (1024 * 1024);
SET @GB_FORMAT = (1024 * 1024 * 1024);

SET @ROUND_PRECISION = 2;

-- Format Parameter: @p_raw_format
-- Values: (0) or Any -> Result in human readable form [KB, MB, GB], (1) -> Result in raw bytes.
	
SET @p_raw_format = 0;

SELECT 
	CASE 
		WHEN @p_raw_format = 1 THEN concat(@v_max_blob_byte_size, ' B')
	ELSE
		CASE 
			WHEN @v_max_blob_byte_size < 1024 THEN concat(@v_max_blob_byte_size, ' B')
			WHEN @v_max_blob_byte_size > 1024 and @v_max_blob_byte_size < 1024 * 1024 THEN concat(ROUND(@v_max_blob_byte_size/@KB_FORMAT, @ROUND_PRECISION), ' KB')
			WHEN @v_max_blob_byte_size > 1024 * 1024 and @v_max_blob_byte_size < 1024 * 1024 * 1024 THEN concat(ROUND(@v_max_blob_byte_size/@MB_FORMAT, @ROUND_PRECISION), ' MB')
			WHEN @v_max_blob_byte_size > 1024 * 1024 * 1024 THEN concat(ROUND(@v_max_blob_byte_size/@GB_FORMAT, @ROUND_PRECISION), ' GB')
		END
	END 
as max_bin_or_text_lob_size
;

DROP PROCEDURE IF EXISTS usp_max_blob_in_db;

SET SESSION sql_notes = @old_sql_notes;

Search This Blog

MARKS TUTORIAL