Bug #9991 Server hands on multi-update with view

• Submitted: 19 Apr 2005 6:57
• Modified: 20 Apr 2005 0:44
• Reporter: Dmitry L
• Status: Can't repeat
• Category: MySQL Server
• Severity: S2 (Serious)
• Version: 5.0.4
• OS: Windows (win2k)
• CPU Architecture: Any

[19 Apr 2005 6:57] Dmitry L

Description:
Server hands on multi-update with view

How to repeat:
Tables structure

CREATE TABLE `objects_hier` (
  `id` int(10) unsigned NOT NULL auto_increment,
  `pid` int(10) unsigned NOT NULL default '0',
  `flags_0` int(10) unsigned NOT NULL default '0',
  `flags_1` int(10) unsigned NOT NULL default '0',
  `flags_2` int(10) unsigned NOT NULL default '0',
  `proto` smallint(6) unsigned NOT NULL default '0',
  `sort` smallint(6) NOT NULL default '0',
  `template` int(10) unsigned default NULL,
  `map` smallint(6) unsigned NOT NULL default '0',
  `mapped_from` smallint(6) unsigned default NULL,
  `lnk4` int(10) unsigned default NULL,
  `crt` double(15,4) unsigned NOT NULL default '0.0000',
  `mdf` double(15,4) unsigned NOT NULL default '0.0000',
  `path` bigint(20) unsigned default NULL,
  `owner` int(10) unsigned default NULL,
  `size` int(10) unsigned NOT NULL default '100',
  `title` varchar(255) default NULL,
  `remark` varchar(255) default NULL,
  `xml` mediumtext,
  PRIMARY KEY  (`id`,`map`),
  KEY `proto` (`proto`),
  KEY `title` (`title`),
  KEY `pid` (`pid`),
  KEY `id` (`id`,`mapped_from`),
  KEY `lnk4` (`lnk4`,`map`),
  KEY `sort` (`sort`),
  KEY `path` (`path`),
  KEY `owner` (`owner`),
  KEY `size` (`size`),
  KEY `template` (`template`,`map`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
---------------------------------------
CREATE TABLE `objects_path` (
  `id` int(10) unsigned NOT NULL default '0',
  `pid` int(10) unsigned NOT NULL default '0',
  `map` smallint(6) unsigned NOT NULL default '0',
  `level` tinyint(4) unsigned NOT NULL default '0',
  `title` varchar(255) default NULL,
  PRIMARY KEY  (`id`,`map`,`level`),
  KEY `pid` (`pid`),
  KEY `level` (`level`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8
--------------------------------------
CREATE TABLE `objects_access` (
  `master` int(10) unsigned NOT NULL default '0',
  `map` smallint(6) unsigned NOT NULL default '0',
  `slave` int(10) unsigned NOT NULL default '0',
  `access` int(10) unsigned NOT NULL default '0',
  UNIQUE KEY `access_u` (`master`,`map`,`slave`),
  KEY `access_slave` (`slave`,`map`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8

----------------------------------------
CREATE OR REPLACE VIEW registry AS 
SELECT oh.*, a.master, a.slave, a.map as _mm, a.access, op.level
FROM objects_hier oh, objects_access a, objects_path op
WHERE slave = oh.id
and a.map = oh.map
and op.id = oh.id
and op.pid = oh.id 
and op.map = oh.map

Fill with any data

-- stupid update (no-meaning) but it hangs server
UPDATE objects_hier oh, registry r
SET oh.lnk4 = NULL WHERE oh.lnk4 is null;

[19 Apr 2005 7:00] Dmitry L

sorry, misstyped, OS:win2k

[19 Apr 2005 7:16] Dmitry L

In addition:
When SELECT in view defenition contains row constructor
such as
CREATE OR REPLACE VIEW registry AS 
SELECT oh.*, a.master, a.slave, a.map as _mm, a.access, op.level
FROM objects_hier oh, objects_access a, objects_path op
WHERE slave = oh.id
and a.map = oh.map
AND row(oh.id, oh.id, oh.map) = row(op.id, op.pid, op.map)

Server hangs and goes down ([root@localhost:3306] ERROR 2013: Lost connection to MySQL server during query) on CREATE

[20 Apr 2005 0:44] MySQL Verification Team

I wasn't able to repeat with a Windows server 5.0.5 BK source:

    -> and op.pid = oh.id
    -> and op.map = oh.map;
Query OK, 0 rows affected (0.06 sec)

mysql> UPDATE objects_hier oh, registry r
    -> SET oh.lnk4 = NULL WHERE oh.lnk4 is null;
Query OK, 0 rows affected (0.06 sec)
Rows matched: 0  Changed: 0  Warnings: 0

mysql> CREATE OR REPLACE VIEW registry AS
    -> SELECT oh.*, a.master, a.slave, a.map as _mm, a.access, op.level
    -> FROM objects_hier oh, objects_access a, objects_path op
    -> WHERE slave = oh.id
    -> and a.map = oh.map
    -> AND row(oh.id, oh.id, oh.map) = row(op.id, op.pid, op.map);
Query OK, 0 rows affected (0.03 sec)

mysql> select version();
+------------------+
| version()        |
+------------------+
| 5.0.5-beta-debug |
+------------------+
1 row in set (0.02 sec)

mysql>

[20 Apr 2005 5:09] Dmitry L

Thx, I'll wait for 5.0.5 release and check this again