TUniStoredProc Error Returned With "UUID" command in Proc

sashki · Post by **sashki** » Fri 25 Nov 2011 17:48

Hey guys,

I have encountered a very strange error with the "TUniStoredProc" component when linked to a stored procedure where the stored procedure contains code which generates a table with multiple records containing GUID values obtained by the MySQL "UUID" command.

The stored procedure will typically contain the following statement:

NOTE: The example's "Staff" table below contains multiple records, so multiple GUID values will be required to be generated for the destination table's resulting column on the fly.

Also, the "UUID" command forms part of a concatenated string to represent the default GUID format of Windows GUID's, which is pre-and post fixed with curly brackets and which contain uppercase characters.

Code: Select all

INSERT INTO Test(EmployeeID, Name, Surname)
SELECT (SELECT CONCAT('{', UPPER(UUID())), '}'), Name, Surname
FROM Staff;

As you can see, the above SQL statement will generate the table "Test" with all the records from the "Staff" table, but the "EmployeeID" is generated with GUID values on the fly, as returned by the concatenated "UUID" command.

When testing the above code within the stored procedure on the server itself, everything works as expected, with each record entry in our test table containing a unique GUID value as returned by the "UUID" command.

But when you try to execute this exact same stored procedure with the "TUniStoredProc" component from within Delphi, you get a primary key violation on the "EmployeeID" field ??

Simply by executing this command, the primary key violation is generated:

Code: Select all

UniStoredProc1.Execute;

For some reason, when you try to execute this server-side stored procedure using this UniDAC stored procedure component, it is as if the "UUID" command is not generating the required unique GUID values for each added record entry as it would when you execute the exact same routine on the server itself.

The only thing I'm left with, is that for some reason when executing this stored procedure using the "TUniStoredProc" component, something else is happening with the "UUID" command within the statement that doesn't want to function correctly, compared to executing it directly on the server??

When you take the EXACT SAME query, and you stuff it into a normal "TUniQuery" component and execute the query with "Query1.Execute", success is achieved as well, but NOT using the stored procedure component linking to the stored procedure on the server.

Does anybody have any idea why this is happening?? Is this a "strange" bug on this component I uncovered, or do I need to set some property value on the component itself to get it to execute this command successfully??

I can't understand how this is affected by being called with the "TUniStoredProc" component. It's not like the component itself is executing the actual routine, but merely invoking it on the server itself??

Does anyone have an idea how do you get this to work by using the "TUniStoredProc" component, or did I uncover I bug in this component??

Thanks.

AndreyZ · Post by **AndreyZ** » Mon 28 Nov 2011 11:03

Hello,

I cannot reproduce the problem. Please specify the following:
- a script to create the Test table;
- the exact version of UniDAC. You can learn it from the About sheet of TUniConnection Editor;
- the exact version of MySQL server you are using. You can learn it from the Info sheet of TUniConnection Editor.

sashki · Post by **sashki** » Fri 02 Dec 2011 09:49

If you can provide me with some email address, I can email a zip file containing a little test to reproduce this error.

The UniDAC version I'm using is Ver. 3.50.0.12 for Delphi 2010.
The MySQL version I'm using is Ver. 5.1.51 Community Edition

Try executing the stored procedure with a test app compiled with Delphi using the "UniStoredProc.Execute"component command. You should be presented with "Duplicate Key" error message.

Now, execute or step through this stored procedure on the server itself, or with a MySQL debugger tool such as "Debugger for MySQL", and the stored procedure is executed successfully, as well as the desired result is achieved, which are random unique GUID values for the primary key column in the destination table.

Using a compiled application with the "TUniStoredProc" component to execute this exact same stored procedure, produces a "Duplicate Key" error for the primary key field.

Here is the code from the SQL script itself to create the necessary test data:

Code: Select all

CREATE TABLE `systemlog` (
  `SystemLogNo` varchar(38) NOT NULL COMMENT 'This column is used for transaction consolidation purposes.',
  `LogType` int(11) NOT NULL DEFAULT '0',
  `LogAction` varchar(50) NOT NULL DEFAULT '',
  `StaffNoAuth` varchar(38) DEFAULT NULL,
  `StaffNoUser` varchar(38) DEFAULT NULL,
  `LogDate` date NOT NULL DEFAULT '0000-00-00',
  `LogTime` time NOT NULL DEFAULT '00:00:00',
  `ChangeID` varchar(38) NOT NULL COMMENT 'This column will indicate any record data changes that took place.',
  PRIMARY KEY (`SystemLogNo`) USING HASH,
  UNIQUE KEY `idxChangeID` (`ChangeID`) USING HASH
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

INSERT INTO `systemlog` (`SystemLogNo`, `LogType`, `LogAction`, `StaffNoAuth`, `StaffNoUser`, `LogDate`, `LogTime`, `ChangeID`) VALUES 
  ('{0027E9BA-55F9-4526-BE9E-B75A9EB012D8}',11,'POS Main - Setup','{3286ABE8-6B70-4B02-BBC9-1982468595D2}','','2011-11-08','23:36:58','{34B69B4E-9E41-45B8-AD08-95823618A48E}'),
  ('{11AF7539-FD9F-42B4-99F2-792F6A597A76}',11,'POS Setup - Setup','{3286ABE8-6B70-4B02-BBC9-1982468595D2}','','2011-11-27','02:03:51','{06BDAD6C-C388-4BAB-9532-A24C4E6CD3A6}'),
  ('{146C1437-CBDD-4B32-A404-9994BDAFD3F8}',11,'Admin Controls - Bulk Sheet','','{3286ABE8-6B70-4B02-BBC9-1982468595D2}','2011-11-10','14:43:04','{E5EB932E-67F5-4A99-A216-39085D3870C3}'),
  ('{3C8037BE-8462-462B-9DB8-BA08B9CF8E7F}',7,'User Logged-In','','{3286ABE8-6B70-4B02-BBC9-1982468595D2}','2011-11-08','09:49:55','{9253580A-733C-4FBC-A99E-2800553F97A1}'),
  ('{508E199D-B809-423F-93C1-0A6CA1CE6265}',11,'POS Setup - Setup','{3286ABE8-6B70-4B02-BBC9-1982468595D2}','','2011-11-20','08:49:33','{A98248B9-9198-42B7-8A14-05D98A94F557}'),
  ('{5BBCCF83-0C69-42D2-8B02-D312E428FA4A}',11,'Admin Controls - Fruit & Veg Sheet','','{3286ABE8-6B70-4B02-BBC9-1982468595D2}','2011-11-08','09:49:56','{8F224AC0-4568-4AC9-B730-A6B720A53EA6}'),
  ('{6C35DE77-7232-406D-A6E4-CC2E551B37F7}',11,'POS Main - Setup','{3286ABE8-6B70-4B02-BBC9-1982468595D2}','','2011-11-20','08:49:30','{4A84A12A-31B8-4EC2-AEEA-C04D3899CCD7}'),
  ('{808CDEF7-CF9E-4D53-928B-84C00C10668F}',7,'User Logged-In','','{3286ABE8-6B70-4B02-BBC9-1982468595D2}','2011-11-08','23:38:30','{4344ECE8-76E0-44DD-ABBC-482EC1E7C522}'),
  ('{8DA515B8-1A41-4B64-AF9D-73434AF7F579}',7,'User Logged-In','','{3286ABE8-6B70-4B02-BBC9-1982468595D2}','2011-11-06','15:39:02','{E1990223-4B79-49BB-9BF6-7450E034B223}'),
  ('{9BDA766C-1228-4614-B0FC-2D4D7A6C2853}',7,'User Logged-In','','{3286ABE8-6B70-4B02-BBC9-1982468595D2}','2011-11-15','17:02:09','{B8674043-8FDD-46DF-98A5-D3A37C7E5595}'),
  ('{9CEEB1DC-A77A-4B05-B3B8-88394FBFDBB4}',11,'Admin Files - Exit Admin System','{3286ABE8-6B70-4B02-BBC9-1982468595D2}','','2011-11-28','18:50:10','{A5AEE646-4670-41B9-867D-79ED859DABB5}'),
  ('{A0F4D9C7-2E35-4A0D-A275-B0AC1336F7D4}',11,'Admin Files - Exit Admin System','','{3286ABE8-6B70-4B02-BBC9-1982468595D2}','2011-11-10','14:43:10','{8F58B5DE-0746-4946-A352-110CE8AAACAD}'),
  ('{AC30124F-018D-4C72-BCF5-1E791E8B6E7A}',11,'POS Setup - Setup','{3286ABE8-6B70-4B02-BBC9-1982468595D2}','','2011-11-08','23:37:03','{23E8E247-DD4D-42FE-93DA-C040B3D1073D}'),
  ('{B67D65EA-F0CA-4257-9C1F-F69DB6A489A0}',11,'POS Main - Setup','{3286ABE8-6B70-4B02-BBC9-1982468595D2}','','2011-11-04','12:25:28','{38FD397B-C846-4A8E-B8F5-8526D0B8AA92}'),
  ('{BDD782BA-3CCA-41F7-AB40-8C3198958A4E}',7,'User Logged-In','','{3286ABE8-6B70-4B02-BBC9-1982468595D2}','2011-11-10','14:41:47','{974F8C00-F095-4F1B-A584-A7100B84F72A}'),
  ('{BE28717A-35B9-47C1-8866-EE1B0486346B}',11,'Admin Files - Exit Admin System','{3286ABE8-6B70-4B02-BBC9-1982468595D2}','','2011-11-10','14:39:36','{BF83D521-A94E-4949-961A-CB1E553BDD4E}'),
  ('{C22E5798-D4DB-4498-A17F-23907EB0FC99}',11,'POS Setup - Setup','{3286ABE8-6B70-4B02-BBC9-1982468595D2}','','2011-11-04','12:25:31','{B875C2FD-9B42-4715-AFE6-83950C028172}'),
  ('{C3A951D8-6754-4B1F-B766-9C87CF1CB32E}',11,'POS Main - Setup','{3286ABE8-6B70-4B02-BBC9-1982468595D2}','','2011-11-27','02:03:48','{F1457088-73C8-4B03-A8D9-9673AAE95312}'),
  ('{D61C634A-1925-4E1F-B959-47FD96336806}',11,'POS Main - Waiters','{3286ABE8-6B70-4B02-BBC9-1982468595D2}','','2011-11-04','12:26:24','{6DB1E488-776D-487E-86AA-3C688029B909}'),
  ('{EFF73E38-293A-44B0-96C9-A006815C3630}',11,'Admin Reports - PLU Listings','','{3286ABE8-6B70-4B02-BBC9-1982468595D2}','2011-11-10','14:42:18','{5E4AF069-8D59-4B07-97EC-D03DBB253AAC}'),
  ('{F8C29CAE-AF94-453E-A824-F8759832CFA9}',11,'Admin Reports - PLU Listings','','{3286ABE8-6B70-4B02-BBC9-1982468595D2}','2011-11-10','14:42:13','{58B634C6-B227-430E-8B08-86D0B6BECA04}');
  
CREATE TABLE `systemlogbackup` (
  `SystemLogNo` varchar(38) NOT NULL COMMENT 'This column is used for transaction consolidation purposes.',
  `LogType` int(11) NOT NULL DEFAULT '0',
  PRIMARY KEY (`SystemLogNo`) USING HASH
) ENGINE=InnoDB DEFAULT CHARSET=latin1; 

CREATE DEFINER = 'root'@'localhost' PROCEDURE `SetSystemLogBackup`()
    NOT DETERMINISTIC
    CONTAINS SQL
    SQL SECURITY DEFINER
    COMMENT ''
BEGIN
  INSERT INTO systemlogbackup(SystemLogNo, LogType)
  SELECT (SELECT CONCAT('{', UUID(), '}')), LogType FROM systemlog;
END;

Thank You.

AndreyZ · Post by **AndreyZ** » Mon 05 Dec 2011 10:29

We have checked this problem. Your stored procedure cannot be executed in any tool that we tried. For example, you can check it with MySQL Command Line Client that is supplied with MySQL. This is not a UniDAC problem. Please write about this problem to the MySQL support.

sashki · Post by **sashki** » Mon 05 Dec 2011 11:01

Hi,

I am able to run this stored procedure using the tool "Debugger For MySQL". I just ran that stored procedure 3-times in a row successfully.

Please can you check it again and try to run the stored procudure with "Debugger for MySQL". Here is the link to the website for this tool:

http://www.mydebugger.com

As I say, I'm getting the exact desired result by using this tool to execute this stored procedure.

Thank you.

AndreyZ · Post by **AndreyZ** » Tue 06 Dec 2011 06:41

We reproduced this problem in Debugger for MySQL (version 1.2.1.1588). Debugger for MySQL can execute your stored procedure only in the debug mode. If you open the SQL window (the F11 hotkey) and try executing the following code:

Code: Select all

CALL SetSystemLogBackup;

, you will get the "Duplicate entry for key 'Primary'" error. This is MySQL behaviour and we cannot influence it. Please write about this problem to the MySQL support.

sashki · Post by **sashki** » Tue 06 Dec 2011 08:20

If you think this is a MySQL problem, how come when you execute the exact same SQL statement with a TUniQuery component, it works 100%?

Take the exact same query you have within the stored procedure:

Code: Select all

INSERT INTO systemlogbackup(SystemLogNo, LogType)
SELECT (SELECT CONCAT('{', UUID(), '}')), LogType FROM systemlog;

Now create a new project in Delphi with a blank form and drop a TUniConnection, TMySQLProvider and a TUniQuery component onto the form, then assign the above SQL statement into the "SQL" property of the TUniQuery component. Connect to the database you linking to where the "systemlogbackup" table resides for this test.

Now create an "OnClick" event handler of the TButton component, then just put the following code on your "OnClick" event handler.

Code: Select all

UniQuery1.Execute;

Without stepping through code, just click the button to execute this SQL command through the TUniQuery component. On my side, when I do this, it also works as expected. No duplicate keys or anything. Unique GUID values are generated for every record inserted into my test table.

No duplicate keys are generated. All primary key GUID values are unique with no error being returned whatsoever.

Can you please try and explain this behaviour then? Surely if this was a MySQL error after all, I shouldn't be able to generate my test table with data this way, let alone having unique GUID values generated for my primary key field??

If for some reason you are still unable to duplicate this, please provide me with an email address I can send you my code and compiled test application for you to look at.

I know I sound persistant about this, but I'm getting unique and valid GUID values generated on my side by implementing the above example, and I'm therefore confused as to why it would be a MySQL error.

If MySQL was the sole culprit, I should not be able to generate this test data this way and should this method fail on me as well....which it's not. I'm currently looking at this right in front me generating the unique GUID values for my primary key field time and time again...

Thanks.

AndreyZ · Post by **AndreyZ** » Tue 06 Dec 2011 11:53

We have thoroughly checked this question. You must not use the SELECT statement in the stored procedure when calling the UUID function. To solve the problem, you should use the following stored procedure:

Code: Select all

CREATE DEFINER = 'root'@'localhost'
PROCEDURE test.SetSystemLogBackup()
BEGIN
  INSERT INTO systemlogbackup (SystemLogNo, LogType)
  SELECT concat('{', uuid(), '}'), LogType FROM systemlog;
END

This is MySQL behaviour and we cannot influence it.

sashki · Post by **sashki** » Fri 09 Dec 2011 18:35

I now see what you mean.

So the bug lies within the "SELECT" part itself before "UUID()" when executed within a stored procedure.

I just tested it without the "SELECT" before the "UUID()", and it now seems to be working within the stored procedure as expected.

I'll report this bug to MySQL.

Thanks alot for your feedback and help!

AndreyZ · Post by **AndreyZ** » Mon 12 Dec 2011 17:00

Feel free to contact us if you have any further questions about UniDAC.