Unable to Update Table from Stored Procedure in MySQL

user1983682

I have a table in a database, previously existing, no knowledge of any changes to said table, that is unable to be updated by a stored procedure as of late. The error received is "Error Code: 1615. Prepared statement needs to be re-prepared." The stored procedure, which I have slimmed down to try to deduce the issue is:

DELIMITER //

DROP PROCEDURE IF EXISTS db1.sp_change_dates;//

CREATE DEFINER=`serviceAdmin`@`%` PROCEDURE `db1.sp_change_dates`()
begin 
    SET @stmt_text = 'update db1.activity set createStamp = (createStamp + interval 1 day);';
    PREPARE update_stmt FROM @stmt_text;
    EXECUTE update_stmt;
    DEALLOCATE PREPARE update_stmt;
END

//
DELIMITER ;

The table definition is as follows:

   CREATE TABLE `activity` (
  `id` decimal(30,0) NOT NULL,
  `v` bigint(20) DEFAULT NULL,
  `createStamp` timestamp NULL DEFAULT NULL,
  `modifiedStamp` timestamp NULL DEFAULT NULL,
  `createdBy` decimal(30,0) DEFAULT NULL,
  `modifiedBy` decimal(30,0) DEFAULT NULL,
  `DEPRECATED_ownerOrg` decimal(30,0) DEFAULT NULL,
  `active` tinyint(1) NOT NULL,
  `testData` tinyint(1) NOT NULL DEFAULT '0',
  `activityCategoryOid` decimal(30,0) DEFAULT NULL,
  `activitySubCategoryOid` decimal(30,0) DEFAULT NULL,
  `organization` decimal(30,0) DEFAULT NULL,
  `rootActivityOid` decimal(30,0) DEFAULT NULL,
  `contact` decimal(30,0) DEFAULT NULL,
  `subject` varchar(255) DEFAULT NULL,
  `description` varchar(8096) DEFAULT NULL,
  `DEPRECATED_referencedItemobjectIdStr` varchar(255) DEFAULT NULL,
  `DEPRECATED_referencedItemClazzName` varchar(255) DEFAULT NULL,
  `DEPRECATED_referencedItemName` varchar(1000) DEFAULT NULL,
  `parentActivityOid` decimal(30,0) DEFAULT NULL,
  `fillInDate` timestamp NULL DEFAULT NULL,
  `fillInDateHour` datetime DEFAULT NULL,
  `fillInLocalDate` datetime DEFAULT NULL,
  `fillInLocalDateHour` datetime DEFAULT NULL,
  `priority_id` int(11) DEFAULT NULL,
  `providerId` decimal(30,0) DEFAULT NULL,
  `beginDate` datetime DEFAULT NULL,
  `beginDateHour` datetime DEFAULT NULL,
  `beginLocalDate` datetime DEFAULT NULL,
  `beginLocalDateHour` datetime DEFAULT NULL,
  `activityClassEnum` enum('Case','Task','Memo','Opportunity') DEFAULT NULL,
  `assignedToOid` decimal(30,0) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `idx_subject` (`subject`),
  KEY `idx_fillInDate` (`fillInDate`),
  KEY `idx_organization` (`organization`),
  KEY `idx_parentActivityOid` (`parentActivityOid`),
  KEY `idx_createdBy` (`createdBy`),
  KEY `idx_createStamp` (`createStamp`),
  KEY `idx_active` (`active`),
  KEY `idx_activityCategoryOid` (`activityCategoryOid`),
  KEY `idx_modifiedStamp` (`modifiedStamp`),
  KEY `idx_contact` (`contact`),
  KEY `idx_rootActivityOid` (`rootActivityOid`),
  KEY `idx_priorityWeight` (`priority_id`),
  KEY `idx_referencedItemobjectIdStr` (`DEPRECATED_referencedItemobjectIdStr`),
  KEY `idx_beginDate` (`beginDate`),
  KEY `idx_idx_beginDateHour` (`beginDateHour`),
  KEY `idx_idx_beginLocalDate` (`beginLocalDate`),
  KEY `idx_idx_beginLocalDateHour` (`beginLocalDateHour`),
  KEY `idx_idx_fillInDateHour` (`fillInDateHour`),
  KEY `idx_idx_fillInLocalDate` (`fillInLocalDate`),
  KEY `idx_idx_fillInLocalDateHour` (`fillInLocalDateHour`),
  KEY `idx_activitySubCategoryOid` (`activitySubCategoryOid`),
  KEY `idx_activityClassEnum` (`activityClassEnum`),
  KEY `IDX_id` (`id`),
  KEY `IDX_assignedTo_organization` (`assignedToOid`,`organization`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8

I tried this in another database (we have the same database for multiple clients) and was able to run the stored procedure without issue. The database with issue is running MySQL 5.6.34. I've seen a few bugs that seem similar to this issue, but none were without the introduction of another software or language (e.g. PHP). I am at a loss as to what to search for and will gladly provide more system information if that will help. I did not architect the database originally and am trying to pick up where another left off. Thanks in advance. My user's permissions are ALL PRIVILEGES on *.* WITH GRANT OPTION.

user1983682

I ended up solving this by increasing the table_definition_cache from 2000 to 7900.

Collected from the Internet

Please contact [email protected] to delete if infringement.

edited at
0

Comments

0 comments
Login to comment

Related

From Dev

Update table in a trigger with values from stored procedure

From Dev

Update from stored procedure

From Dev

Update table from another table using stored procedure

From Dev

Flexible Update Stored Procedure Mysql

From Dev

mysql stored procedure update query

From Dev

update query in MYSQL stored procedure

From Dev

unable to get result from MySql Stored Procedure/Function to JPQL

From Dev

Shuffle a table in MySQL with a Stored Procedure

From Dev

Update a Field From a Stored Procedure

From Dev

Update from stored procedure return

From Dev

How to use Table output from stored MYSQL Procedure

From Dev

Selecting all values from a table in mysql using a stored procedure

From Dev

MYSQL stored procedure create dynamic table from date

From Dev

MYSQL stored procedure for update variables are 0

From Dev

Unable to call stored procedure from linked server

From Dev

Unable to get data from stored procedure

From Dev

Unable to call Stored procedure from hibernate

From Dev

Unable to call stored procedure from linked server

From Dev

Insert data inside a table in mysql stored procedure

From Dev

Mysql - table name inparameter to stored procedure

From Dev

MySQL ALTER TABLE with arguments in stored procedure

From Dev

Passing a table as variable for looping in mysql stored procedure

From Dev

CSV String to Table using MySQL Stored Procedure

From Dev

Breaking a stored procedure string into temp table mysql

From Dev

Stored procedure that returns a table from 2 combined

From Dev

Insert into table from stored procedure with different versions

From Dev

Create a Table from a Passed In Parameter in a Stored Procedure

From Dev

How to Create Table from Stored Procedure?

From Dev

Insert into table from stored procedure with different versions

Related Related

  1. 1

    Update table in a trigger with values from stored procedure

  2. 2

    Update from stored procedure

  3. 3

    Update table from another table using stored procedure

  4. 4

    Flexible Update Stored Procedure Mysql

  5. 5

    mysql stored procedure update query

  6. 6

    update query in MYSQL stored procedure

  7. 7

    unable to get result from MySql Stored Procedure/Function to JPQL

  8. 8

    Shuffle a table in MySQL with a Stored Procedure

  9. 9

    Update a Field From a Stored Procedure

  10. 10

    Update from stored procedure return

  11. 11

    How to use Table output from stored MYSQL Procedure

  12. 12

    Selecting all values from a table in mysql using a stored procedure

  13. 13

    MYSQL stored procedure create dynamic table from date

  14. 14

    MYSQL stored procedure for update variables are 0

  15. 15

    Unable to call stored procedure from linked server

  16. 16

    Unable to get data from stored procedure

  17. 17

    Unable to call Stored procedure from hibernate

  18. 18

    Unable to call stored procedure from linked server

  19. 19

    Insert data inside a table in mysql stored procedure

  20. 20

    Mysql - table name inparameter to stored procedure

  21. 21

    MySQL ALTER TABLE with arguments in stored procedure

  22. 22

    Passing a table as variable for looping in mysql stored procedure

  23. 23

    CSV String to Table using MySQL Stored Procedure

  24. 24

    Breaking a stored procedure string into temp table mysql

  25. 25

    Stored procedure that returns a table from 2 combined

  26. 26

    Insert into table from stored procedure with different versions

  27. 27

    Create a Table from a Passed In Parameter in a Stored Procedure

  28. 28

    How to Create Table from Stored Procedure?

  29. 29

    Insert into table from stored procedure with different versions

HotTag

Archive