Cannot Execute Stored Procedure

Discussion of open issues, suggestions and bugs regarding database management and administration tools for MySQL
Post Reply
Franz_F
Posts: 4
Joined: Fri 18 Jan 2019 11:57

Cannot Execute Stored Procedure

Post by Franz_F » Fri 18 Jan 2019 12:28

Hi there,
i'm new to MySQL but Senior Programmer in Oracle PLSQL, i'm looking for a tool to develop database side object (S.proc, Func, ecc.) and i found DbForge for MySql to be a good candidate..but ..

i had a problem with executing a Stored Procedure (really basic simple Insert statement) in Debug mode (or not) .it gave me always this kind of error with in a dialog windows

FUNCTION m99_dml_franztest does not exist

for sure the procedure (not a function) exists and it is executable already tested with a 'call' script

I've already read a similar post regarding a problem when debugging a stored procedure, so i have already activated the log option (Enviroment/Output section) and this is prat the output:


18/01/2019 13:21:26 | warning | DbObjectDescriber: Describe objects.
18/01/2019 13:21:26 | info | DbObjectDescriber: Obtain root object: SIC.
18/01/2019 13:21:26 | info | DescribeObjectInformationComplex: Decribe root object: SIC, object type: Procedure, group: ProcedureOptions.
18/01/2019 13:21:26 | info | Output: SHOW CREATE PROCEDURE SIC.M99_DML_FRANZTEST
Execution time: 0,00190226369626688 second(s)

CLR failed to load assembly Devart.DbForge.Resources.resources, Version=6.1.1318.0, Culture=en-US, PublicKeyToken=09af7300eec23701;
CLR failed to load assembly Devart.DbForge.Resources.resources, Version=6.1.1318.0, Culture=en-US, PublicKeyToken=09af7300eec23701;
CLR failed to load assembly Devart.DbForge.Resources.resources, Version=6.1.1318.0, Culture=en, PublicKeyToken=09af7300eec23701;
CLR failed to load assembly Devart.DbForge.Resources.resources, Version=6.1.1318.0, Culture=en, PublicKeyToken=09af7300eec23701;
18/01/2019 13:21:26 | info | DbObjectDescriber: Describe objects count: 1.
18/01/2019 13:21:26 | info | Output: SELECT routine_name FROM information_schema.routines WHERE LOWER(routine_schema) = LOWER('SIC') AND routine_name = 'M99_DML_FRANZTEST' AND routine_type = 'PROCEDURE'
Execution time: 0,00353664414971137 second(s)

Scope "59f90733-4d68-4fdf-82a7-f0fcbf5460aa" (Devart.DbForge.DeclarationRepository.BuiltInScope) is added successfully.
CLR failed to load assembly Devart.DbForge.Resources.resources, Version=6.1.1318.0, Culture=en-US, PublicKeyToken=09af7300eec23701;
CLR failed to load assembly Devart.DbForge.Resources.resources, Version=6.1.1318.0, Culture=en-US, PublicKeyToken=09af7300eec23701;
CLR failed to load assembly Devart.DbForge.Resources.resources, Version=6.1.1318.0, Culture=en, PublicKeyToken=09af7300eec23701;
CLR failed to load assembly Devart.DbForge.Resources.resources, Version=6.1.1318.0, Culture=en, PublicKeyToken=09af7300eec23701;
CLR failed to load assembly Devart.DbForge.Resources.resources, Version=6.1.1318.0, Culture=en-US, PublicKeyToken=09af7300eec23701;
CLR failed to load assembly Devart.DbForge.Resources.resources, Version=6.1.1318.0, Culture=en-US, PublicKeyToken=09af7300eec23701;
CLR failed to load assembly Devart.DbForge.Resources.resources, Version=6.1.1318.0, Culture=en, PublicKeyToken=09af7300eec23701;
CLR failed to load assembly Devart.DbForge.Resources.resources, Version=6.1.1318.0, Culture=en, PublicKeyToken=09af7300eec23701;
Created TimeKeeper: 193401, Interval: 200, Feature: MemberList
Added TimeKeeper: 193401, Interval: 200, Feature: ParameterInfo
Added TimeKeeper: 193401, Interval: 200, Feature: QuickInfo
Added TimeKeeper: 193401, Interval: 200, Feature: ErrorInfo
Added TimeKeeper: 193401, Interval: 200, Feature: CodeSnippet
Added TimeKeeper: 193401, Interval: 200, Feature: CodeSnippetList
Added TimeKeeper: 193401, Interval: 200, Feature: SmartTag
Added TimeKeeper: 193401, Interval: 200, Feature: Navigation
Created TimeKeeper: 1740610, Interval: 1000, Feature: Highlight
Added TimeKeeper: 193401, Interval: 200, Feature: RefactorRename
18/01/2019 13:21:26 | info | SqlCompleteService.AddSqlCompleteHost: 7932458 (60945241)
CLR failed to load assembly Devart.DbForge.Resources.resources, Version=6.1.1318.0, Culture=en-US, PublicKeyToken=09af7300eec23701;
CLR failed to load assembly Devart.DbForge.Resources.resources, Version=6.1.1318.0, Culture=en-US, PublicKeyToken=09af7300eec23701;
CLR failed to load assembly Devart.DbForge.Resources.resources, Version=6.1.1318.0, Culture=en, PublicKeyToken=09af7300eec23701;
CLR failed to load assembly Devart.DbForge.Resources.resources, Version=6.1.1318.0, Culture=en, PublicKeyToken=09af7300eec23701;
18/01/2019 13:21:26 | warning | DbObjectDescriber: Describe objects.
18/01/2019 13:21:26 | info | DbObjectDescriber: Obtain root object: SIC.
Scope "Procedure:SIC.M99_DML_FRANZTEST(Asilo.DNA)" (Devart.DbForge.DeclarationRepository.ScriptScope) is added successfully.
18/01/2019 13:21:26 | 7 | info | SQL Complete >> Start describe server: 'Asilo.DNA'
18/01/2019 13:21:26 | info | Output: SET NAMES UTF8
Execution time: 0,00140119473740073 second(s)

18/01/2019 13:21:26 | info | Output: SET @@session.net_write_timeout = 72000, @@session.max_sort_length = CAST(IF(@@session.max_sort_length < 128, 1024, @@session.max_sort_length) AS SIGNED)
Execution time: 0,00376655052633398 second(s)

18/01/2019 13:21:26 | info | DbObjectDescriber: Describe object: SIC, group: Procedure.
18/01/2019 13:21:26 | info | Output: SELECT routine_name, is_deterministic, security_type, definer, created, last_altered, routine_comment, sql_data_access, external_language, sql_mode FROM information_schema.routines WHERE LOWER(routine_schema) = LOWER('SIC') AND ('Y' = '' OR routine_name IN ('M99_DML_FRANZTEST')) AND routine_type = 'PROCEDURE' ORDER BY routine_name
Execution time: 0,00381591692622227 second(s)

18/01/2019 13:21:26 | info | DescribeObjectInformationComplex: Decribe root object: SIC, object type: Procedure, group: ProcedureOptions.
18/01/2019 13:21:26 | info | Output: SHOW CREATE PROCEDURE SIC.M99_DML_FRANZTEST
Execution time: 0,00479442949543661 second(s)

18/01/2019 13:21:26 | info | DbObjectDescriber: Describe objects count: 1.
CLR failed to load assembly Devart.DbForge.Resources.resources, Version=6.1.1318.0, Culture=en-US, PublicKeyToken=09af7300eec23701;
CLR failed to load assembly Devart.DbForge.Resources.resources, Version=6.1.1318.0, Culture=en-US, PublicKeyToken=09af7300eec23701;
CLR failed to load assembly Devart.DbForge.Resources.resources, Version=6.1.1318.0, Culture=en, PublicKeyToken=09af7300eec23701;
CLR failed to load assembly Devart.DbForge.Resources.resources, Version=6.1.1318.0, Culture=en, PublicKeyToken=09af7300eec23701;
18/01/2019 13:21:26 | 7 | info | SQL Complete >> Finish describe server. Time: 00:00:00.0703541
Scope "DataSource:192.168.150.211/User:dbafranz" (Devart.DbForge.DeclarationRepository.ServerScope) is added successfully.
CLR failed to load assembly Devart.DbForge.Resources.MySql.resources, Version=8.1.22.0, Culture=en-US, PublicKeyToken=09af7300eec23701;
CLR failed to load assembly Devart.DbForge.Resources.MySql.resources, Version=8.1.22.0, Culture=en-US, PublicKeyToken=09af7300eec23701;
CLR failed to load assembly Devart.DbForge.Resources.MySql.resources, Version=8.1.22.0, Culture=en, PublicKeyToken=09af7300eec23701;
CLR failed to load assembly Devart.DbForge.Resources.MySql.resources, Version=8.1.22.0, Culture=en, PublicKeyToken=09af7300eec23701;
CLR failed to load assembly Devart.DbForge.Resources.resources, Version=6.1.1318.0, Culture=en-US, PublicKeyToken=09af7300eec23701;
CLR failed to load assembly Devart.DbForge.Resources.resources, Version=6.1.1318.0, Culture=en-US, PublicKeyToken=09af7300eec23701;
CLR failed to load assembly Devart.DbForge.Resources.resources, Version=6.1.1318.0, Culture=en, PublicKeyToken=09af7300eec23701;
CLR failed to load assembly Devart.DbForge.Resources.resources, Version=6.1.1318.0, Culture=en, PublicKeyToken=09af7300eec23701;
18/01/2019 13:21:26 | warning | DbObjectDescriber: Describe objects.
18/01/2019 13:21:26 | info | DbObjectDescriber: Obtain root object: SIC.
18/01/2019 13:21:26 | info | DbObjectDescriber: Describe objects count: 1.
18/01/2019 13:21:26 | info | Comparison: Create schema comparison.
18/01/2019 13:21:26 | info | Mapping: Clear mapping.
18/01/2019 13:21:26 | info | : Start comparison work.
Operation type: Compare
Source provider type: List
Source provider encoding: System.Text.UTF8Encoding
Source server version: 80013
Target provider type: List
Target provider encoding: System.Text.UTF8Encoding
Target server version: 80013
Options snapshot:
CLR failed to load assembly Devart.DbForge.Resources.MySql.resources, Version=8.1.22.0, Culture=en-US, PublicKeyToken=09af7300eec23701;
CLR failed to load assembly Devart.DbForge.Resources.MySql.resources, Version=8.1.22.0, Culture=en-US, PublicKeyToken=09af7300eec23701;
CLR failed to load assembly Devart.DbForge.Resources.MySql.resources, Version=8.1.22.0, Culture=en, PublicKeyToken=09af7300eec23701;
CLR failed to load assembly Devart.DbForge.Resources.MySql.resources, Version=8.1.22.0, Culture=en, PublicKeyToken=09af7300eec23701;
18/01/2019 13:21:26 | info | : Comparison done.
18/01/2019 13:21:26 | info | Start generating sync actions
18/01/2019 13:21:26 | info | Find source cycles
18/01/2019 13:21:26 | info | Find target cycles
18/01/2019 13:21:26 | info | Finish generating sync actions. Actions Count 1.
CLR failed to load assembly Devart.DbForge.Resources.resources, Version=6.1.1318.0, Culture=en-US, PublicKeyToken=09af7300eec23701;
CLR failed to load assembly Devart.DbForge.Resources.resources, Version=6.1.1318.0, Culture=en-US, PublicKeyToken=09af7300eec23701;
CLR failed to load assembly Devart.DbForge.Resources.resources, Version=6.1.1318.0, Culture=en, PublicKeyToken=09af7300eec23701;
CLR failed to load assembly Devart.DbForge.Resources.resources, Version=6.1.1318.0, Culture=en, PublicKeyToken=09af7300eec23701;
18/01/2019 13:21:26 | 7 | info | SQL Complete >> Start describe object. Name='SIC' Type='Database'
18/01/2019 13:21:26 | info | Output: SHOW CREATE PROCEDURE sic.m99_dml_franztest
PROCEDURE m99_dml_franztest does not exist

18/01/2019 13:21:27 | info | Output: SHOW CREATE FUNCTION sic.m99_dml_franztest
FUNCTION m99_dml_franztest does not exist

dbforgemysql.exe Information: 0 : 13:21: dbForge Studio 2019 for MySQL: SuppressException: A: False, T: False, S: False, M: False, R: False
Exception:
Type = Devart.Data.MySql.MySqlException
Message = FUNCTION m99_dml_franztest does not exist
Source = Devart.DbForge.Data.MySql
StackTrace =
at m3.ae()

at m3.o()

at ps.bx(m7[]& A_0, Int32& A_1, Boolean A_2)

at ps.c0(Byte[] A_0, Int32 A_1, Boolean A_2, String A_3)

at i0.o()

at Devart.Data.MySql.MySqlCommand.InternalExecute(CommandBehavior behavior, IDisposable stmt, Int32 startRecord, Int32 maxRecords)

at Devart.Common.DbCommandBase.ExecuteDbDataReader(CommandBehavior behavior, Boolean nonQuery)

at Devart.Data.MySql.MySqlCommand.ExecuteReader()

at Devart.Data.MySql.MySqlCommand.DescribeProcedure(String name)

at Devart.DbForge.ProcedureExecutor.UxMyAXLE4t(IDbCommandExtension )

at Devart.DbForge.ProcedureExecutor.Prepare()

at Devart.DbForge.DbObjectUIHelper.ExecuteProcedure(IDbProcedure dbProcedure)

at Devart.DbForge.DbObjects.DbProcedurePopupProvider.DhCeWYSQb6(ICommand , CommandStatus& , Object )

at Devart.Shell.Common.CommandTargetHelper.InvokeCommand(ICommand command, Object instance)

at Devart.Shell.CommandBarService.InvokeCommand(ICommand command, ICollection objects, ICommandTarget primaryCommandTarget)

at Devart.Shell.CommandBarBase.OnItemClicked(IInternalBarControl control)

at Devart.Shell.CommandBarButton.Button_Click(Object sender, ItemClickEventArgs e)

at DevExpress.XtraBars.BarItem.OnClick(BarItemLink link)

at DevExpress.XtraBars.BarButtonItem.OnClick(BarItemLink link)

at DevExpress.XtraBars.BarItemLink.OnLinkClick()

at DevExpress.XtraBars.BarButtonItemLink.OnLinkAction(BarLinkAction action, Object actionArgs)

at DevExpress.XtraBars.ViewInfo.BarSelectionInfo.ClickLink(BarItemLink link)

at DevExpress.XtraBars.ViewInfo.BarSelectionInfo.UnPressLink(BarItemLink link)

at DevExpress.XtraBars.Controls.CustomLinksControl.OnMouseUp(MouseEventArgs e)

at System.Windows.Forms.Control.WmMouseUp(Message& m, MouseButtons button, Int32 clicks)

at System.Windows.Forms.Control.WndProc(Message& m)

at DevExpress.XtraBars.Controls.CustomControl.WndProc(Message& msg)

at System.Windows.Forms.NativeWindow.Callback(IntPtr hWnd, Int32 msg, IntPtr wparam, IntPtr lparam)
CLR failed to load assembly Devart.Shell.Resources.resources, Version=2.1.22.0, Culture=en-US, PublicKeyToken=09af7300eec23701;
CLR failed to load assembly Devart.Shell.Resources.resources, Version=2.1.22.0, Culture=en-US, PublicKeyToken=09af7300eec23701;
CLR failed to load assembly Devart.Shell.Resources.resources, Version=2.1.22.0, Culture=en, PublicKeyToken=09af7300eec23701;
CLR failed to load assembly Devart.Shell.Resources.resources, Version=2.1.22.0, Culture=en, PublicKeyToken=09af7300eec23701;
18/01/2019 13:21:27 | info | Output: SHOW DATABASES;
Execution time: 0,00175663281659642 second(s)

18/01/2019 13:21:27 | info | Output: SHOW VARIABLES;
Execution time: 0,00540904117404583 second(s)

18/01/2019 13:21:27 | info | Output: SELECT DISTINCT grantee
FROM information_schema.user_privileges;
Execution time: 0,00233809848385207 second(s)

18/01/2019 13:21:27 | info | Output: SELECT collation_name
FROM information_schema.collations
ORDER BY collation_name;
Execution time: 0,0072225511356564 second(s)

18/01/2019 13:21:27 | info | Output: SELECT character_set_name
, description
FROM information_schema.character_sets
ORDER BY character_set_name;
Execution time: 0,00443934403338297 second(s)

18/01/2019 13:21:27 | info | Output: SELECT tablespace_name
, tablespace_comment
FROM information_schema.tablespaces
ORDER BY tablespace_name;
Execution time: 0,00480853418111897 second(s)

18/01/2019 13:21:27 | info | Output: SELECT SRS_NAME, SRS_ID ,ORGANIZATION, DESCRIPTION
FROM INFORMATION_SCHEMA.ST_SPATIAL_REFERENCE_SYSTEMS
Execution time: 0,00339700776145591 second(s)

18/01/2019 13:21:27 | 7 | info | SQL Complete >> Finish describe object. Name='SIC' Type='Database' Time='00:00:00.2083632'
18/01/2019 13:21:27 | info | Output: SELECT t.table_name
, t.table_type
, CASE t.TABLE_TYPE WHEN 'VIEW' THEN '' ELSE t.TABLE_COMMENT END AS table_comment
, t.ENGINE
FROM information_schema.tables AS t
WHERE t.table_schema = 'SIC'
ORDER BY t.table_name;
Execution time: 0,00433109057077079 second(s)

18/01/2019 13:21:27 | info | Output: SELECT c.table_name
, c.column_name
, c.column_type
, c.column_comment
, c.is_nullable
, c.extra
, c.ordinal_position
FROM information_schema.columns AS c
WHERE c.table_schema = 'SIC'
ORDER BY c.table_name, c.ordinal_position;
Execution time: 0,00885975252623736 second(s)

18/01/2019 13:21:27 | info | Output: SELECT r.routine_name AS name, r.routine_type AS type,
CONCAT(DTD_IDENTIFIER, COALESCE(CONCAT(' CHARSET ', character_set_name), '')) AS returns, r.routine_comment AS comment
FROM information_schema.routines r
WHERE r.routine_schema = 'SIC' or r.routine_schema = lower('SIC')
ORDER BY r.routine_name
Execution time: 0,00365265518944885 second(s)

18/01/2019 13:21:27 | info | Output: SELECT
p.specific_schema,
p.specific_name,
p.parameter_name,
p.data_type,
p.numeric_scale,
p.numeric_precision,
p.character_maximum_length,
p.parameter_mode
FROM information_schema.parameters p
WHERE p.specific_schema = 'SIC' OR p.specific_schema = lower('SIC')
ORDER BY specific_name, p.ordinal_position
Execution time: 0,00805719591091057 second(s)

18/01/2019 13:21:27 | info | Output: SELECT t.trigger_name
, t.event_object_table
FROM information_schema.triggers AS t
WHERE t.trigger_schema = 'SIC'
ORDER BY t.trigger_name;
Execution time: 0,00256165775191762 second(s)

18/01/2019 13:21:27 | info | Output: SELECT e.event_name
, e.event_comment
FROM information_schema.events AS e
WHERE e.event_schema = 'SIC'
ORDER BY e.event_name;
Execution time: 0,00238358609517771 second(s)

18/01/2019 13:21:27 | info | Output: SELECT tc.constraint_name
, tc.table_name
, tc.constraint_type
FROM information_schema.table_constraints AS tc
WHERE tc.table_schema = 'SIC'
ORDER BY tc.table_name, tc.constraint_name;
Execution time: 0,0211960632411792 second(s)

18/01/2019 13:21:27 | info | Output: SELECT kcu.constraint_name
, kcu.table_name
, kcu.column_name
, kcu.referenced_table_name
, kcu.referenced_column_name
FROM information_schema.key_column_usage AS kcu
WHERE kcu.constraint_schema = kcu.table_schema AND kcu.table_schema = 'SIC'
ORDER BY kcu.table_name, kcu.constraint_name, kcu.ordinal_position;
Execution time: 0,138732524735211 second(s)

Scope "Procedure:SIC.M99_DML_FRANZTEST(Asilo.DNA)" (Devart.DbForge.DeclarationRepository.ScriptScope) is added successfully.


In term of grant my user has full grant od database (i.e. SIC)

Any help would be appreciated ..

Thanks in advance

alexa
Devart Team
Posts: 2600
Joined: Fri 24 Jun 2011 14:17

Re: Cannot Execute Stored Procedure

Post by alexa » Fri 18 Jan 2019 15:17

We suspect this is due to case sensitivity between M99_DML_FRANZTEST and m99_dml_franztest.

Franz_F
Posts: 4
Joined: Fri 18 Jan 2019 11:57

Re: Cannot Execute Stored Procedure

Post by Franz_F » Sun 20 Jan 2019 16:34

Ok, i wrote the code once in uppercase...never lowecase ..its seems really strange ..what is your advice, there is a workaround or i'll never execute a stored procedure (and debug) within this product?
Maybe something connected with my configuration
RDBMS version MySql community edition 8.0.13 on CentOS
Client Win10 Professional Edition
Anyway thanks for you reply

Franz_F
Posts: 4
Joined: Fri 18 Jan 2019 11:57

Re: Cannot Execute Stored Procedure

Post by Franz_F » Mon 21 Jan 2019 12:56

I think i've found the issue, there something wrong with the upper case naming of the databse (i.e. SIC).
It's seems that when a db name is defined in upper case DbForge doesn't find correctly the referenced object.
I have defined another new db with the same name but in lowercase wich is different(i.e sic) and the dbforge database explorer show wrongly the object of the 'uppercased db' even if there's nothing in the 'lowercased db'.

More ..i've defined a new lowercased dbname (i.e. sik) containing the same object of SIC and everything works fine i can run and debug the stored procedure.

So i think, in my case, its a bug of DbForge, the execution and debug of SP doesn't work well with 'uppercased' dbname.

Need to be patched

alexa
Devart Team
Posts: 2600
Joined: Fri 24 Jun 2011 14:17

Re: Cannot Execute Stored Procedure

Post by alexa » Fri 25 Jan 2019 12:29

Thank you for the reply.

We will investigate this issue and will answer you as soon as possible.

Franz_F
Posts: 4
Joined: Fri 18 Jan 2019 11:57

Re: Cannot Execute Stored Procedure

Post by Franz_F » Thu 21 Feb 2019 15:24

Hi,
in my case i found that, with an older version of the database (i.e. MySQL 5.7), the debug engine and the Database Explorer works as expected. Fortunately my customer has this version in production enviroment.
This problem happened to me with latest version like 8.0.13.
I hope that Devart solve this critical bug....asap.

alexa
Devart Team
Posts: 2600
Joined: Fri 24 Jun 2011 14:17

Re: Cannot Execute Stored Procedure

Post by alexa » Wed 03 Apr 2019 14:47

Could you please let us know what's the value of the lower_case_table_names server variable? For correct working, it should be 1.

Post Reply