MySQL Bugs: #11302: Insert/update in stored procedure fails

Bug #11302	Insert/update in stored procedure fails
Submitted:	14 Jun 2005 0:34	Modified:	14 Sep 2005 16:01
Reporter:	Peter Brawley (Basic Quality Contributor)	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server	Severity:	S1 (Critical)
Version:	5.0.9	OS:	Windows (winxp)
Assigned to:	Per-Erik Martin	CPU Architecture:	Any

Description:
Bug 9486 is marked closed, but persists in 5.0.6:

CREATE TABLE airports (
code char(3) NOT NULL,
city char(100) default NULL,
latitude float NOT NULL,
longitude float NOT NULL,
PRIMARY KEY (code)
) ENGINE=MyISAM;

INSERT INTO airports VALUES ('JFK', 'New York, NY', 40.75, -73.97);
INSERT INTO airports VALUES ('LAX', 'Los Angeles, CA', 34.05, -118.22);
INSERT INTO airports VALUES ('LHR', 'London, England', 51.5, -0.45);
INSERT INTO airports VALUES ('HEL', 'Helsinki, Finland', 60.17, 24.97);
INSERT INTO airports VALUES ('CDG', 'Paris, France', 48.86, 2.33);
INSERT INTO airports VALUES ('STL', 'St Louis, MO', 38.63, -90.2);
INSERT INTO airports VALUES ('ARN', 'Stockholm, Sweden', 59.33, 18.05); 

SET GLOBAL log_bin_trust_routine_creators=TRUE; 
DROP FUNCTION IF EXISTS GeoDistKM; 
DELIMITER |
CREATE FUNCTION GeoDistKM( lat1 FLOAT, lon1 FLOAT, lat2 FLOAT, lon2 FLOAT ) RETURNS float
BEGIN
DECLARE pi, q1, q2, q3 FLOAT;
DECLARE rads FLOAT DEFAULT 0;
SET pi = PI();
SET lat1 = lat1 * pi / 180;
SET lon1 = lon1 * pi / 180;
SET lat2 = lat2 * pi / 180;
SET lon2 = lon2 * pi / 180;
SET q1 = COS(lon1-lon2);
SET q2 = COS(lat1-lat2);
SET q3 = COS(lat1+lat2);
SET rads = ACOS( 0.5*((1.0+q1)*q2 - (1.0-q1)*q3) ); 
RETURN 6378.388 * rads;
END;
|
DELIMITER ; 

CREATE TABLE flights (
  id INT PRIMARY KEY AUTO_INCREMENT,
  depart CHAR(3),
  arrive CHAR(3),
  distance DECIMAL(10,2),
  direction DECIMAL(10,2)
) ENGINE=MYISAM;

INSERT INTO flights
  SELECT
  NULL,
  depart.code,
  arrive.code,
  ROUND(GeoDistKM(depart.latitude,depart.longitude,arrive.latitude,arrive.longitude),2) AS Km,
  ROUND(DEGREES(ATAN(arrive.latitude-depart.latitude,arrive.longitude-depart.longitude)),2)
  FROM airports AS depart
  INNER JOIN airports AS arrive ON depart.code <> arrive.code
  HAVING Km <= 6000;
  
CREATE TABLE routes (
  id INT PRIMARY KEY AUTO_INCREMENT,
  depart CHAR(3),
  arrive CHAR(3),
  hops SMALLINT,
  route CHAR(50),
  distance DECIMAL(10,2),
  direction DECIMAL(10,2)
) ENGINE=MYISAM; 

CREATE OR REPLACE VIEW nexthops AS
  SELECT 
    routes.depart,
    flights.arrive,
    routes.hops+1 AS hops,
    CONCAT(routes.route, ',', flights.arrive) AS route,
    MIN(routes.distance + flights.distance) AS distance,
    routes.direction
  FROM routes INNER JOIN flights 
    ON routes.arrive = flights.depart
    AND LOCATE(flights.arrive,routes.route) = 0
  WHERE flights.direction+360>routes.direction+270 
    AND flights.direction+360<routes.direction+450
  GROUP BY depart,arrive;
  
SET GLOBAL log_bin_trust_routine_creators=TRUE; 
DROP PROCEDURE IF EXISTS BuildRoutes;
DELIMITER |
CREATE PROCEDURE BuildRoutes()
BEGIN
  DECLARE rows INT DEFAULT 0;
  TRUNCATE routes;

  -- STEP 1, LISTING 5: SEED ROUTES WITH 1-HOP FLIGHTS
  INSERT INTO routes
    SELECT 
      NULL,
      depart,
      arrive,
      1,
      CONCAT(depart,',',arrive),
      distance,
      direction
  FROM flights;
  SET rows = ROW_COUNT();

  WHILE (rows > 0) DO

    -- STEP 2, LISTING 8: ADD NEXT SET OF ROUTES
    INSERT INTO routes
      SELECT 
        NULL,
        nexthops.depart,
        nexthops.arrive,
        nexthops.hops,
        nexthops.route,
        nexthops.distance,
        nexthops.direction 
      FROM nexthops
      LEFT JOIN routes ON nexthops.depart = routes.depart 
            AND nexthops.arrive = routes.arrive
      WHERE routes.depart IS NULL AND routes.arrive IS NULL;
    SET rows = ROW_COUNT();

  END WHILE;

END;
|
DELIMITER ;

ERROR 1099 (HY000): Table 'routes' was locked with a READ lock and can't be updated

  

How to repeat:
As above

Suggested fix:
Adding an update inside the WHILE loop gets rid of the 'locked' error, but then the INSERT inside the loop silently fails to add four rows from nexthops, so it produces an incorrect result.

That is, the INSERTs and UPDATEs ...

- produce a correct result of 42 routes when called iteratively and manually, 

- fail with error 1099 when called in BuildRoutes() as shown,

- fail silently on the 2nd pass through the loop, producing 38 instead of 42 rows, when the following code is added into the loop, after the code for STEP 2 in BuildRoutes():

    -- STEP 3, LISTING 10: UPDATE WITH SHORTER NEXTHOPS ROUTES IF ANY
    UPDATE routes,nexthops SET 
      routes.hops=nexthops.hops, 
      routes.route=nexthops.route, 
      routes.distance=nexthops.distance, 
      routes.direction=nexthops.direction 
      WHERE routes.arrive=nexthops.arrive
        AND routes.depart=nexthops.depart
        AND nexthops.distance < routes.distance;
    SET rows = rows + ROW_COUNT();

I tried to reproduce it on 5.0.7. All works fine.

Hi, Peter!

I was able to repeat both problems you have reported with latest bk snapshot of 5.0
(tested with debug build on Linux-x86).

After some investigation I believe that the first problem you observe
(ERROR 1099 (HY000): Table 'routes' was locked with a READ lock and can't be
updated) is yet another manifestation of bug #9565 (http://bugs.mysql.com/bug.php?id=9565). So I suggest you to track its progress.

The second problem (wrong result produced by modified routine) is separate and it seems previously unreported issue. I'll try to produce simplified test case for it.

Hi!

Unfortunately I was not able to reproduce the second problem using prepared statements
so simplified test case does not look that simplified:

DROP TABLE IF EXISTS `flights`;
CREATE TABLE `flights` ( 
  `id` int(11) NOT NULL auto_increment,
  `depart` char(3) default NULL,
  `arrive` char(3) default NULL,
  `distance` decimal(10,2) default NULL,
  `direction` decimal(10,2) default NULL,
  PRIMARY KEY  (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;
  
INSERT INTO `flights` VALUES
  (1,'LAX','JFK','3941.18','8.61'),
  (2,'LHR','JFK','5550.77','-171.68'),
  (3,'CDG','JFK','5837.46','-173.93'),
  (4,'STL','JFK','1408.11','7.44'),
  (5,'JFK','LAX','3941.18','-171.39'),
  (6,'STL','LAX','2553.38','-170.72'),
  (7,'JFK','LHR','5550.77','8.32'),
  (8,'HEL','LHR','1841.91','-161.17'),
  (9,'CDG','LHR','354.41','136.48'),
  (10,'ARN','LHR','1450.12','-157.06'),
  (11,'LHR','HEL','1841.91','18.83'),
  (12,'CDG','HEL','1912.96','26.54'),
  (13,'ARN','HEL','398.99','6.92'),
  (14,'JFK','CDG','5837.46','6.07'),
  (15,'LHR','CDG','354.41','-43.52'),
  (16,'HEL','CDG','1912.96','-153.46'),
  (17,'ARN','CDG','1545.23','-146.34'),
  (18,'JFK','STL','1408.11','-172.56'),
  (19,'LAX','STL','2553.38','9.28'),
  (20,'LHR','ARN','1450.12','22.94'),
  (21,'HEL','ARN','398.99','-173.08'),
  (22,'CDG','ARN','1545.23','33.66');

DROP TABLE IF EXISTS `routes`;
CREATE TABLE `routes` (
  `id` int(11) NOT NULL auto_increment,
  `depart` char(3) default NULL,
  `arrive` char(3) default NULL,
  `hops` smallint(6) default NULL,
  `route` char(50) default NULL,
  `distance` decimal(10,2) default NULL,
  `direction` decimal(10,2) default NULL,
  PRIMARY KEY  (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

CREATE OR REPLACE VIEW nexthops AS
  SELECT 
    routes.depart,
    flights.arrive,
    routes.hops+1 AS hops,
    CONCAT(routes.route, ',', flights.arrive) AS route,
    MIN(routes.distance + flights.distance) AS distance,
    routes.direction
  FROM routes INNER JOIN flights 
    ON routes.arrive = flights.depart
    AND LOCATE(flights.arrive,routes.route) = 0
  WHERE
    flights.direction+360>routes.direction+270
    AND flights.direction+360<routes.direction+450
  GROUP BY depart,arrive;

DROP PROCEDURE IF EXISTS BuildRoutes;
DELIMITER |
CREATE PROCEDURE BuildRoutes()
BEGIN
  DECLARE rows INT DEFAULT 0;
  TRUNCATE routes;

  -- STEP 1, LISTING 5: SEED ROUTES WITH 1-HOP FLIGHTS
  INSERT INTO routes
    SELECT
      NULL,
      depart,
      arrive,
      1,
      CONCAT(depart,',',arrive),
      distance,
      direction
  FROM flights;
  SET rows = ROW_COUNT();

  WHILE (rows > 0) DO

    -- DEBUG OUTPUT
    SELECT
        NULL,
        nexthops.depart,
        nexthops.arrive,
        nexthops.hops,
        nexthops.route,
        nexthops.distance,
        nexthops.direction
      FROM nexthops
      LEFT JOIN routes ON nexthops.depart = routes.depart
            AND nexthops.arrive = routes.arrive
      WHERE routes.depart IS NULL AND routes.arrive IS NULL;

    -- STEP 2, LISTING 8: ADD NEXT SET OF ROUTES
    INSERT INTO routes
      SELECT
        NULL,
        nexthops.depart,
        nexthops.arrive,
        nexthops.hops,
        nexthops.route,
        nexthops.distance,
        nexthops.direction
      FROM nexthops
      LEFT JOIN routes ON nexthops.depart = routes.depart
            AND nexthops.arrive = routes.arrive
      WHERE routes.depart IS NULL AND routes.arrive IS NULL;
    SET rows = ROW_COUNT();

    -- STEP 3, UPDATE WHICH ALLOWS TO CIRCUMVENT LOCKING BUG
    -- DOES NOTHING BY ITSELF
    UPDATE routes, nexthops SET
      routes.hops=0
    WHERE 0;

  END WHILE;
END;
|
DELIMITER ;

Calling BuildRoutes routine will fill rotes table with 38 records. But if you execute equivalent statements in manual mode you will get table with 42 records.
(Under equivalent sequence I mean:
   TRUNCATE routes;

 INSERT INTO routes
    SELECT
      NULL,
      depart,
      arrive,
      1,
      CONCAT(depart,',',arrive),
      distance,
      direction
  FROM flights;

  INSERT INTO routes
    SELECT
      NULL,
      nexthops.depart,
      nexthops.arrive,
      nexthops.hops,
      nexthops.route,
      nexthops.distance,
      nexthops.direction
    FROM nexthops
    LEFT JOIN routes ON nexthops.depart = routes.depart
          AND nexthops.arrive = routes.arrive
    WHERE routes.depart IS NULL AND routes.arrive IS NULL;

  SELECT
    NULL,
    nexthops.depart,
    nexthops.arrive,
    nexthops.hops,
    nexthops.route,
    nexthops.distance,
    nexthops.direction
  FROM nexthops
  LEFT JOIN routes ON nexthops.depart = routes.depart
        AND nexthops.arrive = routes.arrive
  WHERE routes.depart IS NULL AND routes.arrive IS NULL;

  INSERT INTO routes
    SELECT
      NULL,
      nexthops.depart,
      nexthops.arrive,
      nexthops.hops,
      nexthops.route,
      nexthops.distance,
      nexthops.direction
    FROM nexthops
    LEFT JOIN routes ON nexthops.depart = routes.depart
          AND nexthops.arrive = routes.arrive
    WHERE routes.depart IS NULL AND routes.arrive IS NULL;
)

TEST 1
Installed 5.0.7 beta
Copied db from 5.0.6
Truncated routes table, then ran BuildRoutes()
RESULT: incorrect -- 38 rows, should be 42 
(as can be proved easily by running the code manually)

TEST 2
Rebuilt BuildRoutes() in 5.0.7.
Truncates routes table, then ran Build Routes()
RESULT: ERROR 1099 (HY000): Table 'routes' was locked with a READ lock and can't be updated.

Contrary to the message posted by a tester, both problems remain in 5.0.7.

The bug no longer occurs in 5.0.9.

Thank you for your bug report. This issue has been committed to our
source repository of that product and will be incorporated into the
next release.

If necessary, you can access the source repository and build the latest
available version, including the bugfix, yourself. More information 
about accessing the source trees is available at
    http://www.mysql.com/doc/en/Installing_source_tree.html

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/14929

ChangeSet@1.2539, 2006-11-07 10:28:30+02:00, gkodinov@macbook.gmz +4 -0
  Bug #11302: getObject() returns a String for a sub-query of type datetime
   - When returning metadata for scalar subqueries the actual type of the
     column was calculated based on the value type, which limits the actual
     type of a scalar subselect to the set of (currently) 3 basic types : 
     integer, double precision or string. This columns of types other then
     the basic ones (e.g. date/time) to be reported having the corresponding
     basic type.
     Fixed by storing/returning information for the column type in addition
     to the result type.