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, 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
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!