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.
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.
Comments