MySQL Bugs: #63972: SQLException Field doesn't have a default value when field is not null text type

Bug #63972	SQLException Field doesn't have a default value when field is not null text type
Submitted:	9 Jan 2012 20:20	Modified:	18 Dec 2017 23:10
Reporter:	Péter Balogh	Email Updates:
Status:	No Feedback	Impact on me:	None
Category:	MySQL Server	Severity:	S3 (Non-critical)
Version:	5.1.18	OS:	Windows
Assigned to:		CPU Architecture:	Any
Tags:	jdbc text default null

Description:
If a table contains text type fields with NOT NULL but no explicit default value, an insert fails if called from the jdbc driver.
The is serious, since mysqldump does not include the default empty value for not null text fields. If we set the field explicitly to empty default value after a full dump and import, the exception does not occur.

How to repeat:
CREATE TABLE IF NOT EXISTS `def_test` (
`id` int(11) NOT NULL auto_increment,
`value` text collate utf8_hungarian_ci NOT NULL,
`deleted` tinyint(1) NOT NULL default 1,
PRIMARY KEY (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_hungarian_ci AUTO_INCREMENT=1 ;

Execute the following query through jdbc driver for the exception
INSERT INTO `def_test` (`deleted`) VALUES (0);

Changing category, this is either a mysqldump of mysql server issue. The JDBC driver uses sql_mode STRICT_TRANS_TABLES. If STRICT_TRANS_TABLES enforces this constraint, then it should be supported by mysqldump dumping the empty default value. The JDBC driver can do nothing to work around this issue, other than having the user add a sql_mode that isn't STRICT_TRANS_TABLES which breaks JDBC compatibility as well as data integrity.

TEXT column can NOT have DEFAULT value defined (see http://dev.mysql.com/doc/refman/5.5/en/create-table.html):

"BLOB and TEXT columns cannot be assigned a default value."

So, you have to provide value for this column, even if it is empty string, '', explicitly, at least while you are in strict mode.

mysqldump dumps '' value if it is there:

...
DROP TABLE IF EXISTS `def_test`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `def_test` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `value` text COLLATE utf8_hungarian_ci NOT NULL,
  `deleted` tinyint(1) NOT NULL DEFAULT '1',
  PRIMARY KEY (`id`)
) ENGINE=MyISAM AUTO_INCREMENT=2 DEFAULT CHARSET=utf8 COLLATE=utf8_hungarian_ci;
/*!40101 SET character_set_client = @saved_cs_client */;

--
-- Dumping data for table `def_test`
--

LOCK TABLES `def_test` WRITE;
/*!40000 ALTER TABLE `def_test` DISABLE KEYS */;
INSERT INTO `def_test` VALUES (1,'',0);
/*!40000 ALTER TABLE `def_test` ENABLE KEYS */;
UNLOCK TABLES;
...

This is NOT a bug.

So it's normal, that after running the following query, the jdbc driver works as expected, and the INSERT happens?
ALTER TABLE def_test MODIFY value TEXT NOT NULL DEFAULT '';
And if I dump the altered database, and reload it, the query fails?

I'm not sure, in which component, but I think it's not the correct behavior.

What exact server version, x.y.z, are you working with? 

I can not run that ALTER successfully on recent enough 5.5.19, for example (again, because TEXT column can NOT have default values):

C:\Program Files\MySQL\MySQL Server 5.5\bin>mysql -uroot -proot -P3312 test
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 24
Server version: 5.5.19 MySQL Community Server (GPL)

Copyright (c) 2000, 2011, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> CREATE TABLE `def_test` (
    ->   `id` int(11) NOT NULL AUTO_INCREMENT,
    ->   `value` text COLLATE utf8_hungarian_ci NOT NULL,
    ->   `deleted` tinyint(1) NOT NULL DEFAULT '1',
    ->   PRIMARY KEY (`id`)
    -> ) ENGINE=MyISAM AUTO_INCREMENT=2 DEFAULT CHARSET=utf8 COLLATE=utf8_hungar
ian_ci;
Query OK, 0 rows affected (0.59 sec)

mysql> ALTER TABLE def_test MODIFY value TEXT NOT NULL DEFAULT '';
ERROR 1101 (42000): BLOB/TEXT column 'value' can't have a default value

No feedback was provided for this bug for over a month, so it is
being suspended automatically. If you are able to provide the
information that was originally requested, please do so and change
the status of the bug back to "Open".

I have the same behaviour using MySql Workbench on Windows with a remote MySQL Server running version 5.6.25.1 on Debian. 

My current test case: 

CREATE TABLE `myTable` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `modified` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT 'last modified',
  `status` enum('INACTIVE','NEW','PROCESSING','PROCESSED','ARCHIVED','DELETED') NOT NULL DEFAULT 'INACTIVE',
  `ref_id` int(10) unsigned NOT NULL,
  `name` varchar(255) NOT NULL COMMENT 'file name',
  `type` varchar(63) NOT NULL DEFAULT 'CSV' COMMENT 'file type',
  `content` mediumtext COMMENT 'file content',
  `path` varchar(1024) DEFAULT NULL COMMENT 'file path',
  `processed` datetime DEFAULT NULL,
  `tasks` tinyint(3) unsigned NOT NULL DEFAULT '0',
  `errors` tinyint(3) unsigned NOT NULL DEFAULT '0',
  `messages` text NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1

-- --------------------------------------------------------------------

CREATE TRIGGER `myTable_before_insert` BEFORE INSERT ON `myTable`
FOR EACH ROW
BEGIN
	SET NEW.messages='';
END

-- --------------------------------------------------------------------

CREATE PROCEDURE `testProc`(
	 p_ref_id 		INT
	,p_file_name		VARCHAR(255)
	,p_file_type		VARCHAR(63)
	,p_file_content		MEDIUMTEXT		-- the content of the attached file
)
BEGIN
	DECLARE p_sqlexception		BOOLEAN 	DEFAULT FALSE;
	DECLARE CONTINUE HANDLER FOR SQLEXCEPTION
	BEGIN
	GET DIAGNOSTICS CONDITION 1 @sqlstate = RETURNED_SQLSTATE,@errno = MYSQL_ERRNO, @text = MESSAGE_TEXT;
		SET p_sqlexception=TRUE;
		SET @full_error = CONCAT('SQLERROR (CONTINUED): ', COALESCE(@errno,''), ' (', COALESCE(@sqlstate,''), ') ', COALESCE(@text,''));
		INSERT INTO logs.error_log (processor,msg_type,msg) VALUES ('testProc','SQLEXCEPTION',@full_error);
		SELECT 'SQLEXCEPTION' AS ERROR,CONCAT('testProc',' ',@full_error) AS msg;
	END;

	INSERT INTO myTable 	(  ref_id,      `name`,     `type`,       content, `status`)
		VALUES 		(p_ref_id,p_file_name,p_file_type ,p_file_content,  'NEW')
		ON DUPLICATE KEY UPDATE modified = NOW();
END

-- --------------------------------------------------------------------

CALL testProc(1234, 'test file', 'csv', 'hello world');

causes: "SQLERROR (CONTINUED): 1364 (HY000) Field 'messages' doesn't have a default value"

Please try latest release version 5.1 isn't more supported. Thanks.

No feedback was provided for this bug for over a month, so it is
being suspended automatically. If you are able to provide the
information that was originally requested, please do so and change
the status of the bug back to "Open".