Description:
When we have a query updating rows from a view based on a table with 5 million rows, the update query fails with an error that the total number of locks exceeds the lock table size.
How to repeat:
1. Connect to the MySQL server as 'root'
2. Create table tb2 by executing the following query:
create table tb2 (
f59 numeric (0) unsigned,
f60 numeric (64) unsigned,
f61 numeric (0) zerofill,
f62 numeric (64) zerofill,
f63 numeric (0) unsigned zerofill,
f64 numeric (64) unsigned zerofill,
f65 numeric (0,0),
f66 numeric (63,63),
f67 numeric (0,0) unsigned,
f68 numeric (63,63) unsigned,
f69 numeric (0,0) zerofill,
f70 numeric (63,63) zerofill,
f71 numeric (0,0) unsigned zerofill,
f72 numeric (63,63) unsigned zerofill,
f73 real,
f74 real unsigned,
f75 real zerofill,
f76 real unsigned zerofill,
f77 double DEFAULT 7.7,
f78 double unsigned DEFAULT 7.7,
f79 double zerofill DEFAULT 7.7,
f80 double unsigned zerofill DEFAULT 8.8,
f81 float not null DEFAULT 8.8,
f82 float unsigned not null DEFAULT 8.8,
f83 float zerofill not null DEFAULT 8.8,
f84 float unsigned zerofill not null DEFAULT 8.8,
f85 float(0) not null DEFAULT 8.8,
f86 float(23) not null DEFAULT 8.8,
f87 float(0) unsigned not null DEFAULT 8.8,
f88 float(23) unsigned not null DEFAULT 8.8,
f89 float(0) zerofill not null DEFAULT 8.8,
f90 float(23) zerofill not null DEFAULT 8.8,
f91 float(0) unsigned zerofill not null DEFAULT 8.8,
f92 float(23) unsigned zerofill not null DEFAULT 8.8,
f93 float(24) not null DEFAULT 8.8,
f94 float(53) not null DEFAULT 8.8,
f95 float(24) unsigned not null DEFAULT 8.8,
f96 float(53) unsigned not null DEFAULT 8.8,
f97 float(24) zerofill not null DEFAULT 8.8,
f98 float(53) zerofill not null DEFAULT 8.8,
f99 float(24) unsigned zerofill not null DEFAULT 8.8,
f100 float(53) unsigned zerofill not null DEFAULT 8.8,
f101 date not null DEFAULT 20,
f102 time not null DEFAULT 20,
f103 datetime not null DEFAULT '2/2/2',
f104 timestamp not null DEFAULT 20001231235959,
f105 year not null DEFAULT 2000,
f106 year(3) not null DEFAULT 2000,
f107 year(4) not null DEFAULT 2000,
f108 enum("1enum","2enum") not null DEFAULT 1,
f109 set("1set","2set") not null DEFAULT 1
)//
2. Populate the table with 5 million rows
3. Create a view as follows:
CREATE VIEW test.v_4_402004 AS SELECT f59,f60,f61 FROM tb2 //
4. Execute a update query as follows:
UPDATE test.v_4_402004 SET f59 = 891 WHERE f60 = 105 //
Expected Results : The update query should succeed and complete without any errors
Actual Results : The update query fails with an error for total number of locks exceeding the lock table size
ERROR 1206: The total number of locks exceeds the lock table size