Searching for fields in MySQL-MariaDB databases

Tags:

This morning I have had a problem with #MySQL, and I got a little bit “strange”, not because of the error but for the field that forced the error. I was creating a stored procedure for builder a crosstable query (using crosstab utility), and after executing it, i got this:

Error 1364 de campo sin valor por defecto

Error 1364, field doesnt have a default value

The case is that I verified that all databases I work with had a “comment” field. To find a solution, I took advantage of a query inside Common_schema, and I built this small utility: search for a field in all databases.

CREATE DEFINER=`root`@`localhost` PROCEDURE `TablasConCampo`(IN `campo` VARCHAR(50))
LANGUAGE SQL
NOT DETERMINISTIC
CONTAINS SQL
SQL SECURITY DEFINER
COMMENT ''
BEGIN
SELECT
TABLE_SCHEMA,
TABLE_NAME,
COLUMN_NAME,
COLUMN_TYPE,
CHARACTER_SET_NAME,
COLLATION_NAME
FROM
INFORMATION_SCHEMA.COLUMNS
WHERE
COLUMN_NAME like campo;
END

Now, I run the stored procedure trying to find the “comment” field, and here was my results:

TablasConCampo

TablasConCampo

So, one of this fields were causing the error. Because I was working with a stored procedure, I test to modify “mysql.proc“, allowing NULL, instead of the default “no null”.

ALTER TABLE `proc` CHANGE COLUMN `comment` `comment` TEXT NULL COLLATE 'utf8_bin' AFTER `sql_mode`;

Running again the stored procedure confirms that error was solved!

I hope you like it, and maybe, this procedure can be used by you!