Bug #91253 After model synchronization, stored procedures dosen't work

• Submitted: 14 Jun 2018 11:48
• Modified: 27 Jun 2018 11:58
• Reporter: Akshara M
• Status: Can't repeat
• Category: MySQL Workbench
• Severity: S2 (Serious)
• Version: 6.3.10 CE build 12092614 (64 bit)
• OS: Microsoft Windows (2012 Server R2 Datacenter)
• CPU Architecture: x86 (Intel Xeon 2Ghz)
• Tags: model synchronization, Stored procedure error

[14 Jun 2018 11:48] Akshara M

Description:
When we update the stored procedure in model and synchornize it with Live server, most of the stored procedures fail to execute.

I need to manually modify them in workbench by adding something like # anywhere in the proc and hit apply changes to make it work. 

I have added a sample SP in How to repeat section. This SP gives 
"Error Code: 1411. Incorrect datetime value: '4:00 PM' for function str_to_date"
error when executed just after model sync.

How to repeat:
CREATE DEFINER=`root`@`localhost` PROCEDURE `GetVehiclesForSchedule`(IN Schedule_Id int)
BEGIN
declare ScheduleStartTime varchar(10);
declare TotalJourneyDuration int;
declare Entity_Id int;

select ScheduleTime, Duration, EntityId  into ScheduleStartTime, TotalJourneyDuration, Entity_Id   from info_schedule where ScheduleId=Schedule_Id;

DROP TABLE if exists VehicleList;
CREATE TEMPORARY TABLE VehicleList (VehicleId int);
insert into VehicleList(VehicleId)
select ve.VehicleId from info_vehicle ve
inner join mapp_vehicleschedule vs on vs.VehicleId=ve.VehicleId and (vs.ToDate is null or vs.ToDate >= current_timestamp())
inner join info_schedule sc on sc.ScheduleId=vs.ScheduleId
where ((str_to_date(sc.ScheduleTime, '%h:%i %p')
 BETWEEN  str_to_date(ScheduleStartTime, '%h:%i %p') 
 and DATE_ADD(str_to_date(ScheduleStartTime, '%h:%i %p'),INTERVAL TotalJourneyDuration minute))) ;

select ve.*,vt.Name as VehicleType from info_vehicle ve
inner join master_vehicletype vt on ve.VehicleTypeId=vt.VehicleTypeId 
left join VehicleList vl on vl.VehicleId=ve.VehicleId
where vl.VehicleId is null and ve.EntityId =Entity_Id;

END

[14 Jun 2018 17:09] Miguel Solorzano

Thank you for the bug report. Which exactly WorkBench are you using 6.3.10 or 8.0.11 rc development version, which exactly server version are you using 5.7.22 ? and which exactly Windows version?. Thanks.

[15 Jun 2018 9:32] Akshara M

MySQL Community Server (GPL) version 5.7.22-log - Compiled for Win64(x86_64)
MySQL Workbench Community (GPL) for Windows version 6.3.10 CE build 12092614 (64 bit)
Cairo Version: 1.10.2
OS: Microsoft Windows Server 2012 R2 Datacenter
CPU: Intel(R) Xeon(R) CPU, 3.8 GiB RAM

[15 Jun 2018 9:39] Akshara M

Hope the data provided helps.

Thanks.

[15 Jun 2018 9:41] Akshara M

.

[18 Jun 2018 13:35] Chiranjeevi Battula

Hello Akshara,

Thank you for the feedback.
I could not repeat the issue at our end using with MySQL Workbench 8.0.11 version on Windows 10.
If you can provide more information, feel free to add it to this bug and change the status back to 'Open'.

Thank you for your interest in MySQL.

Thanks,
Chiranjeevi.

[26 Jun 2018 11:42] Akshara M

I tried with Workbench 8.0.11 still I have the same problem. MySQL server 5.7.22-log. And now I am having problems with backup and restore. Both fails.

[27 Jun 2018 11:58] Akshara M

After investigating all the procedures we have used, we could see a pattern.

This issue occurs when STR_TO_DATE function is used in SP.
Ex: STR_TO_DATE(s.ScheduleTime,'%l:%i %p') -- ScheduleTime will have varchar  value like 4:00 PM.

If I add a space or # in the SP and apply changes it starts working.