MySQL Bugs: #29480: #1105

Bug #29480	#1105 - Unknown error
Submitted:	2 Jul 2007 9:54	Modified:	31 Jul 2007 15:21
Reporter:	Dmitry Labutin	Email Updates:
Status:	Duplicate	Impact on me:	None
Category:	MySQL Server: Errors	Severity:	S2 (Serious)
Version:	5.0.41	OS:	Any (Linux, Windows)
Assigned to:	Assigned Account	CPU Architecture:	Any
Tags:	#1105, unknown error

Description:
SQL:  

INSERT INTO obj( id, parent_id, obj_class, obj_owner, date_created, date_modified, cnt, tree_level, tree_prior, tree_order, rg, wg, ag, dg, user_created, user_modified ) 
SELECT NULL , 5679, obj_class, obj_owner, now( ) , now( ) , 0, 3, 500180, '/500200/499690/500180/', rg, wg, ag, dg, 402, 402
FROM obj
WHERE id =1;

#1105 - Unknown error 

How to repeat:
USE test;
DROP TABLE IF EXISTS `obj`;
CREATE TABLE `obj` (
  `id` int(10) unsigned NOT NULL auto_increment,
  `parent_id` int(10) unsigned NOT NULL default '0',
  `obj_class` int(10) unsigned NOT NULL default '0',
  `obj_owner` int(10) unsigned NOT NULL default '0',
  `date_created` datetime NOT NULL default '0000-00-00 00:00:00',
  `date_modified` datetime NOT NULL default '0000-00-00 00:00:00',
  `cnt` int(10) unsigned NOT NULL default '0',
  `tree_level` int(10) unsigned NOT NULL default '0',
  `tree_prior` int(6) unsigned zerofill NOT NULL default '000000',
  `tree_order` varchar(255) NOT NULL default '',
  `rg` int(10) unsigned NOT NULL default '0',
  `wg` int(10) unsigned NOT NULL default '0',
  `ag` int(10) unsigned NOT NULL default '0',
  `dg` int(10) unsigned NOT NULL default '0',
  `user_created` int(10) unsigned NOT NULL default '0',
  `user_modified` int(10) unsigned NOT NULL default '0',
  `params` mediumtext NOT NULL,
  `valid` tinyint(3) unsigned NOT NULL default '1',
  PRIMARY KEY  (`id`),
  KEY `obj_class` (`obj_class`),
  KEY `tree_order` (`tree_order`),
  KEY `obj_class_2` (`obj_class`,`tree_order`),
  KEY `parent_id` (`parent_id`)
) ENGINE=MyISAM DEFAULT CHARSET=cp1251;

INSERT INTO `obj` VALUES (1,0,901,402,'2004-10-11 23:31:33','2007-05-03 15:59:12',13,1,500200,'/500200/',11,2,2,2,402,402,'',1);
insert into obj (id,parent_id,obj_class,obj_owner,date_created,date_modified,cnt,tree_level,tree_prior,tree_order,rg,wg,ag,dg, user_created,user_modified) select	null,5679,obj_class,obj_owner,now(),now(),0,3,500180,'/500200/499690/500180/',rg,wg,ag,dg,402,402 from obj where id=1;

Thank you for the report.

I can not repeat described behaviour in my environment. Please provide your configuration file.

This is my.ini

[client]
port=3306

[mysql]

default-character-set=latin1

[mysqld]

port=3306
basedir="C:/Program Files/MySQL/MySQL Server 5.0/"
datadir="D:/server/mysqldata/"
default-character-set=latin1
sql-mode="STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION"
max_connections=100
query_cache_size=0
table_cache=256
tmp_table_size=18M
thread_cache_size=8
myisam_max_sort_file_size=100G
myisam_max_extra_sort_file_size=100G
myisam_sort_buffer_size=35M
key_buffer_size=25M
read_buffer_size=64K
read_rnd_buffer_size=256K
sort_buffer_size=256K

skip-innodb
innodb_additional_mem_pool_size=2M
innodb_flush_log_at_trx_commit=1
innodb_log_buffer_size=1M
innodb_buffer_pool_size=47M
innodb_log_file_size=24M
innodb_thread_concurrency=8

mysql> insert into obj
    -> (id,parent_id,obj_class,obj_owner,date_created,date_modified,cnt,tree_level,tree_prior,
    -> tree_order,rg,wg,ag,dg, user_created,user_modified)
    -> select   null,5679,obj_class,obj_owner,now(),now(),0,3,500180,'/500200/499690/500180/',rg,wg,
    -> ag,dg,402,402 from obj where id=1;
ERROR 1105 (HY000): Unknown error
mysql> show warnings;
+---------+------+---------------------------------------------------------------+
| Level   | Code | Message                                                       |
+---------+------+---------------------------------------------------------------+
| Warning | 1364 | Field 'params' doesn't have a default value                   |
| Error   | 1105 | Unknown error                                                 |
| Warning | 1196 | Some non-transactional changed tables couldn't be rolled back |
+---------+------+---------------------------------------------------------------+
3 rows in set (0.01 sec)

Thank you for the report.

Verified as described on Linux using last development sources.

To repeat issue query "SET sql_mode='STRICT_TRANS_TABLES';" before running test.

Bug is not exists in version 5.0.37

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/30926

ChangeSet@1.2533, 2007-07-14 03:07:33+05:00, gshchepa@gleb.loc +3 -0
  Fixed bug #29480.
  To repeat this bug the value of the global sql_mode variable
  should be set to STRICT_TRANS_TABLES. Also some of table fields
  have to be defined as NOT NULL without the default values.
  After the first successful INSERT into that table, next statement
  should be INSERT or INSERT SELECT with an implicit assignment
  to some field defined without default value.
  The last statement hung instead of the error reporting.
  
  Definition of the `STRICT_TRANS_TABLES' mode:
   For non-transactional storage engines, a statement aborts if the
   error occurs in the first row to be inserted or updated. (When the
   error occurs in the first row, the statement can be aborted to
   leave the table unchanged, just as for a transactional table.)
   Errors in rows after the first do not abort the statement, because
   the table has already been changed by the first row. Instead, bad
   data values are adjusted and result in warnings rather than
   errors. In other words, with `STRICT_TRANS_TABLES', a wrong value
   causes MySQL to roll back all updates done so far, if that can be
   done without changing the table. But once the table has been
   changed, further errors result in adjustments and warnings.
  
  The write_record functions sets the thd->no_trans_update.stmt flag
  value to TRUE after the successful insert into a table.
  This flag is used by multi-insert statements to ignore "default
  value" errors during all subsequent inserts of that multi-insert
  statement.
  
  However, this flag was not reset to FALSE after the completion of
  the invocation of an INSERT statement, and the next INSERT or
  INSERT SELECT statement called neither send_ok nor net_send_error
  function in case of "default value" error.

most probably, it will be fixed by a patch for BUG#27417
(at least, it'll need to be re-verified)

test case

Attachment: 29480.test (application/octet-stream, text), 469 bytes.

As discussed with Gleb, the fix most probably requires the patch for bug#27417.

This bug was fixed by the patch for bug #27417.