mysql存储过程执行时报未知列错误-6ren

mysql存储过程执行时报未知列错误

转载作者：行者123 更新时间：2023-11-30 21:27:44

<分区>

我写了一个小的存储过程来在我的私有(private) redmine 中存储 redmine 问题和时间日志条目。该过程已存储，但当我调用它时，它给出了 SQL error (1054): Unknown column 'uid' in 'field list' 错误。报错提示的列存在，类型匹配。

重写代码，重新部署数据库

当第二个插入从 SP 中移除时，效果很好。

delimiter $$
DROP PROCEDURE if exists redmine.insert_fingerprint_workday_and_hour $$

CREATE DEFINER=`root`@`%` PROCEDURE `insert_fingerprint_workday_and_hour`(
    IN `userid` INT(11) )
LANGUAGE SQL
NOT DETERMINISTIC
MODIFIES SQL DATA
SQL SECURITY DEFINER
COMMENT ''

BEGIN

set @uid=userid;

INSERT INTO issues (
tracker_id,
project_id,
subject,
description,
due_date,
category_id,
status_id,
assigned_to_id,
priority_id,
fixed_version_id,
author_id,
lock_version,
created_on,
updated_on,
start_date,
done_ratio,
estimated_hours,
parent_id,
root_id,
lft,
rgt,
is_private,
closed_on ) 
 VALUES
(
  "4",                      -- tracker id
  "1",                          -- project id
  "teszt subject",      -- subject  
  "teszt description",  -- description 
  NULL,                         -- due_date
  NULL,                         -- category_id
  "7",                          -- status id
  uid,              -- assigned_to_id
  "2",                          -- priority_id
  NULL,                     -- fixed_version_id
  uid,                  --  author_id
  "0",                          -- lock_version
  NOW(),                    -- created_on
  NOW(),                        -- updated_on
  date(now()),              -- start_date
  "0",                      -- done_ratio
  NULL,                     -- estimated_hours
  NULL,                     -- parent_id
  "1",                      -- root_id
  "1",                      -- lft
  "2",                      -- rgt
  "0",                      -- is_private
  now()                     -- closed_on
  );

  set @ujissue=LAST_INSERT_ID();

  insert into time_entries 
  (project_id,user_id,issue_id,hours,activity_id,spent_on,tyear,tmonth,tweek,created_on,updated_on)
  values
  (
  "1",                      -- project_id
  uid,                  -- user_id
  ujissue,                  -- issue_id
  "8",                      -- hours
  "10",                     -- activity_id
  date(now()),              -- spent_on
  year(now()),              -- tyear
  month(now()),         -- tmonth
  week(now()),              -- tweek
  now(),                        -- created_on
  now()
  );

  END $$

  DELIMITER ;

SQL> CALL redmine.insert_fingerprint_workday_and_hour('7');
/* SQL error (1054): Unknown column 'uid' in 'field list' */