|
UPDATE is handled the same way for JDBC and ODBC. The ODBC documentation clearly says that "Unlike the INSERT command, UPDATE and DELETE are supported in a simplified way" and explains that these commands are only achieved by sending to the external source a rephrased original command.
Therefore the "original command" must be the UPDATE command and cannot be an implied triggert update or issued in an EXEC procedure command.
Sorry about that. The only thing I can do is to specify these restrictions in the documentation.
|
|
I didn't realize that simple didn't include a simple update with variables in set and where clause.
Given this isn't a bug but a known limitation should I resubmit as a feature request? How difficult would it be to implement?
|
|
I may reconsider this issue. The problem with procedure might be handled. For instance:
CREATE OR REPLACE TABLE t1 (
|
id int not null,
|
nb bigint not null,
|
msg char(32))
|
ENGINE=CONNECT TABLE_TYPE=JDBC TABNAME=t2
|
CONNECTION='jdbc:mysql://localhost:3308/test?user=root&useSSL=false';
|
|
CREATE OR REPLACE PROCEDURE xup(IN n INT, IN c CHAR(32))
|
BEGIN
|
UPDATE t1 SET nb = n, msg = c WHERE id = 2;
|
END;
|
|
CALL xup(123,'Hello');
|
Here the issue is that in this case the received command to be rephrased is:
UPDATE t2 SET nb = NAME_CONST('n',123), msg = NAME_CONST('c',_latin1'Hello' COLLATE 'latin1_swedish_ci') WHERE id = 2
|
instead of
UPDATE t2 SET nb = 123, msg = 'Hello' WHERE id = 2
|
Here connect just change the table name t2 to t1 and send the command to the remote source. Note that if the remote source is a MariaDB server this still work because it understands the NAME_CONST function, but another source will generally fail.
Here what to do is to eliminate the extra NAME_CONST functions when rephrasing the command, which is not so easy, or having MariaDB not adding these functions that are only useful when copying procedures.
About triggers, could you give me a complete example of table definitions so I can test what happen in this case?
|
|
I just realized that variable substitution doesn't work with SELECT statement WHERE clauses either ... I thought it worked because I tested manually issuing a command but as soon as I did real code (with variables called in a loop) it fails.... hopefully related....
For example, if I have a simple stored function that takes a pv_id as a bigint and I call it from the console with values it gives the right answer, but if I call the same function in a loop with various pv_id values it appears to return the correct answer on the first call and null for all subsequent calls:
BEGIN
return (SELECT InventoryQuantity FROM ProductVariants WHERE Id = pv_id);
END
I'll post some real functions soon - of if you prefer can I can provide a dump of the database or access to it - it has only test data so far.
I don't need complex statement support, just variable substitution in WHERE clause of SELECT, and variable substitution in SET and WHERE clause of UPDATE. The first thing to get working would be from a stored function so long as I can call that from a trigger or scheduled event... ideally the variable substitution works for store proc, stored func, schedule event, and trigger.... but getting it working first with stored function will work so long as I can call it in a loop and pass params and get back results.
|
|
Olivier,
Code below... the 4 base tables I grabbed from SHOW table, the CONNECT I captured the actual command issued to create it... same with the trigger... let me know if it would be better for me to work up the simplest test case rather than this real use case... the issue is just with the last couple of statements in the trigger that use a SELECT with a variable in the WHERE clause and the UPDATE statement with variables of course. For some reason, the SELECT with variables does not return an error, and does appear to produce the right value on the first time through the loop but null on subsequent passes.
Let me know if I can generate any log files to help, or if you would like direct access to this database.... or anything else I can do to help. Thank you!
-- I grabbed these with SHOW TABLE
|
CREATE TABLE "Vendors" ( "Vendor_ID" varchar(20) NOT NULL, "Name" varchar(50) NOT NULL, "Default_Lead_Time" bigint(20) NOT NULL, "Order_Attention" varchar(50) NOT NULL, "Order_Address1" varchar(50) NOT NULL, "Order_Address2" varchar(50) DEFAULT NULL, "Order_City" varchar(35) NOT NULL, "Order_State" varchar(2) NOT NULL, "Order_Zip" varchar(10) NOT NULL, "Order_Country" varchar(30) DEFAULT NULL, "Order_Phone" varchar(30) NOT NULL, "Order_Email_TO" varchar(60) NOT NULL, "Order_Email_CC" varchar(60) DEFAULT NULL, "Order_Email_BCC" varchar(60) DEFAULT NULL, PRIMARY KEY ("Vendor_ID") ) ENGINE=InnoDB DEFAULT CHARSET=utf8
|
|
CREATE TABLE "Vendor_Items" ( "Vendor_Item_ID" bigint(20) NOT NULL COMMENT 'AUTOKEY', "Vendor_ID" varchar(20) NOT NULL, "Vendor_SKU" varchar(40) NOT NULL, "Vendor_Description" varchar(255) DEFAULT NULL, "Unit_Cost" double NOT NULL COMMENT 'CURRENCY', "MOQ" bigint(20) NOT NULL, "Special_Order" tinyint(1) NOT NULL, "Lead_Time" bigint(20) NOT NULL, "Units_In_Package" bigint(20) NOT NULL, "Reorder_Threshold" bigint(20) NOT NULL, "Reorder_Qty" bigint(20) NOT NULL, "Restocks_ProductVariantId" bigint(20) NOT NULL, "Inbound_Qty" bigint(20) NOT NULL, PRIMARY KEY ("Vendor_Item_ID"), UNIQUE KEY "Vendor_Items_ProductVariantId" ("Restocks_ProductVariantId"), KEY "Vendor_ID" ("Vendor_ID"), CONSTRAINT "Vendor_Items_ibfk_1" FOREIGN KEY ("Vendor_ID") REFERENCES "Vendors" ("Vendor_ID") ON UPDATE CASCADE ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='Inbound_Qty is INCREMENTED when PO Sent, and DECREMENTED by Receiving of goods\nCREATE UNIQUE INDEX Vendor_Items_ProductVariantId ON Vendor_Items (Restocks_ProductVariantId)\n'
|
|
CREATE TABLE "ROG" ( "ROG_ID" bigint(20) NOT NULL COMMENT 'AUTOKEY', "Purchase_Order_ID" bigint(20) NOT NULL COMMENT 'AUTOKEY', "Status" enum('New','Completed') NOT NULL, "Date_Received" date NOT NULL, "Received_By" varchar(60) NOT NULL, "Comments" text DEFAULT NULL COMMENT 'HTMLFIELD', PRIMARY KEY ("ROG_ID"), KEY "Purchase_Order_ID" ("Purchase_Order_ID"), CONSTRAINT "ROG_ibfk_1" FOREIGN KEY ("Purchase_Order_ID") REFERENCES "Purchase_Orders" ("Purchase_Order_ID") ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='ROG = Receipt of Goods. Status New, Completed > LOCKS + removes from INBOUND + ADDS to inventory + possibly modifies PO status to Short or Completed. Never let inbound go negative.'
|
|
CREATE TABLE "ROG_Lines" ( "ROG_Line_ID" bigint(20) NOT NULL COMMENT 'AUTOKEY', "ROG_ID" bigint(20) NOT NULL COMMENT 'AUTOKEY', "Purchase_Order_Line_ID" bigint(20) NOT NULL COMMENT 'AUTOKEY', "Vendor_Item_ID" bigint(20) NOT NULL COMMENT 'AUTOKEY', "Qty_Received" bigint(20) NOT NULL, PRIMARY KEY ("ROG_Line_ID"), KEY "ROG_ID" ("ROG_ID"), KEY "Vendor_Item_ID" ("Vendor_Item_ID"), KEY "Purchase_Order_Line_ID" ("Purchase_Order_Line_ID"), CONSTRAINT "ROG_Lines_ibfk_1" FOREIGN KEY ("ROG_ID") REFERENCES "ROG" ("ROG_ID") ON UPDATE CASCADE, CONSTRAINT "ROG_Lines_ibfk_2" FOREIGN KEY ("Vendor_Item_ID") REFERENCES "Vendor_Items" ("Vendor_Item_ID") ON UPDATE CASCADE, CONSTRAINT "ROG_Lines_ibfk_3" FOREIGN KEY ("Purchase_Order_Line_ID") REFERENCES "Purchase_Order_Lines" ("Purchase_Order_Line_ID") ) ENGINE=InnoDB DEFAULT CHARSET=utf8
|
|
|
-- this is the actual command issued
|
CREATE OR REPLACE TABLE ProductVariants ( `Id` BIGINT ,
|
`ProductId` BIGINT ,
|
`InventoryItemId` BIGINT ,
|
`ImageId` BIGINT ,
|
`Price` DOUBLE ,
|
`CompareAtPrice` VARCHAR(255) ,
|
`Title` VARCHAR(255) ,
|
`Grams` BIGINT ,
|
`Barcode` VARCHAR(255) ,
|
`Weight` BIGINT ,
|
`WeightUnit` VARCHAR(255) ,
|
`FulfillmentService` VARCHAR(255) ,
|
`InventoryManagement` VARCHAR(255) ,
|
`InventoryPolicy` VARCHAR(255) ,
|
`InventoryQuantity` BIGINT ,
|
`OldInventoryQuantity` BIGINT ,
|
`Option1` VARCHAR(255) ,
|
`Option2` VARCHAR(255) ,
|
`Option3` VARCHAR(255) ,
|
`Position` BIGINT ,
|
`RequiresShipping` BOOLEAN ,
|
`Sku` VARCHAR(255) ,
|
`Taxable` BOOLEAN ,
|
`CreatedAt` DATETIME ,
|
`UpdatedAt` DATETIME ) ENGINE=CONNECT TABLE_TYPE=JDBC TABNAME='ProductVariants' QUOTED=1 BLOCK_SIZE=500
|
CONNECTION='jdbc:shopify:AppId=0beb2a6debcc4dc9a50b19723881fdc7;Password=24d822c3e9c4ec42f6e0764243340cf5;ShopUrl=https://kiwi-diamond.myshopify.com;Logfile="/var/log/mysql/shopify.log";Verbosity=4;'
|
OPTION_LIST='USER=null,PASSWORD=null,Memory=2'
|
|
|
CREATE OR REPLACE TRIGGER `a_ROG_Before_Update` BEFORE UPDATE ON `ROG` FOR EACH ROW BEGIN
|
|
DECLARE v_ProductVariantId BIGINT;
|
DECLARE v_new_qty BIGINT;
|
|
IF (NEW.Status = 'Completed' AND OLD.Status != 'Completed') THEN
|
|
FOR item IN (SELECT Vendor_Item_ID, Qty_Received FROM ROG_Lines WHERE ROG_ID = NEW.ROG_ID) DO
|
|
UPDATE Vendor_Items
|
SET Inbound_Qty = GREATEST(0, Inbound_Qty - item.Qty_Received)
|
WHERE Vendor_Item_ID = item.Vendor_Item_ID;
|
|
SELECT Restocks_ProductVariantId INTO v_ProductVariantId
|
FROM Vendor_Items
|
WHERE Vendor_Item_ID = item.Vendor_Item_ID;
|
|
-- BEGIN ACCESS of CONNECT table - query with variable: produces right result on first call, null on subsequent
|
SET v_new_qty = (SELECT InventoryQuantity FROM ProductVariants WHERE Id = v_ProductVariantId) + item.Qty_Received;
|
|
-- simple UPDATE CONNECT table with 2 variables - produces error
|
|
UPDATE ProductVariants
|
SET InventoryQuantity = v_new_qty
|
WHERE Id = v_ProductVariantId;
|
|
|
END FOR;
|
|
END IF;
|
|
END
|
|
|
|
CSVs attached with sample data.
Test by doing an update on ROG and changing the status of a given row from New to Completed
ROG.csv ROG_Lines.csv Vendors.csv Vendor_Items.csv ProductVariants.csv
|
|
Dump file attached of entire database if that is easier... it is small and only contains test data.
test_db11615.sql.gz
|
|
To test, first issue this to set it back to New:
update ROG set Status = 'New' where ROG_ID = 1003
|
then issue this to try and mark it Completed - it will fail:
update ROG set Status = 'Completed' where ROG_ID = 1003
|
|
|
Not yet tried your samples (note that triggers cannot be created on CONNECT tables) but I made a simple one:
-- On 3308 (the remote source, another MariaDB server)
|
create or replace table animals (n int not null);
|
insert into animals values(0);
|
|
-- On 3306 (the local server)
|
CREATE OR REPLACE TABLE anum
|
ENGINE=CONNECT TABLE_TYPE=JDBC TABNAME=animals
|
CONNECTION='jdbc:mysql://localhost:3308/test?user=root';
|
|
-- This table cannot be a CONNECT table
|
create or replace table pets (
|
id int not null,
|
name char(32),
|
race char(32));
|
|
create or replace trigger add_one after insert on pets
|
for each row
|
begin
|
update anum set n = n + 1;
|
end;
|
|
insert into pets values(1,'Choupy','chat');
|
Here the received command to rephrase is:
update anum set n = n + 1
|
correctly rephrased as:
update animals set n = n + 1
|
and all works alright when it is sent to the remote source.
|
|
I'm working up a very simplified test case without triggers (just stored procedure/function) to start. I'll post it shortly.
|
|
FYI to clarify, I would never expect to put a trigger ON a connect table... that would not make sense to me at all since it is managed by the remote system. However a trigger on a innodb table that SELECTs from a connect table (with variables in its where clause) or updates values in a connect table (with variables in the SET and WHERE clauses) makes perfect sense to me. In this context (with my zero knowledge of mariadb internals) it would seem that whether the variables are in a stored procedure or trigger or scheduled event would not matter.
Test case coming in few minutes.
|
|
Simplified test case #1: no trigger, just attempt to update a connect table with variables in SET and WHERE clause.
-- ON REMOTE SYSTEM (Azure SQL server in this case):
|
|
create table testme (id bigint, qty bigint, name varchar(50), primary key (id))
|
insert into testme (id,qty,name) values (1,125,'nut'), (2, 150, 'bolt'), (3, 175,'screw')
|
|
-- IN MariaDB:
|
|
CREATE TABLE connect_testme ( `id` BIGINT NOT NULL , (TransactionSet.java:370)
|
`qty` BIGINT ,
|
`name` VARCHAR(50) ) ENGINE=CONNECT TABLE_TYPE=JDBC TABNAME='testme' BLOCK_SIZE=500 CONNECTION='jdbc:sqlserver://parasqltest.database.windows.net:1433;database=test6;encrypt=true;trustServerCertificate=false;hostNameInCertificate=*.database.windows.net;loginTimeout=30;
|
' OPTION_LIST='USER=testuser1@parasqltest,PASSWORD=Test33410,Memory=2'
|
|
CREATE TABLE `local_testme` (`id` bigint NOT NULL COMMENT '' , `qty` bigint COMMENT '' , `name` varchar(50) COMMENT '' , PRIMARY KEY (`id`) ) COMMENT ''
|
|
CREATE OR REPLACE PROCEDURE `test_read_loop` () LANGUAGE SQL
|
BEGIN
|
|
-- insert records from connect_testme in a loop
|
FOR rec IN (SELECT id, qty, name FROM connect_testme WHERE id >= 2) DO
|
insert into local_testme (id, qty, name) values (rec.id, rec.qty, rec.name);
|
END FOR;
|
|
END
|
|
call test_read_loop()
|
|
select * from local_testme
|
|
id qty name
|
2 150 bolt
|
3 175 screw
|
|
CORRECT SO FAR - with procedure not trigger AND with id >= 2 not id >= some_var (later test case)
|
|
CREATE OR REPLACE PROCEDURE `test_update_loop` (v_id bigint) LANGUAGE SQL
|
BEGIN
|
declare new_qty bigint;
|
|
FOR rec IN (SELECT id, qty, name FROM local_testme where id >= v_id) DO
|
|
set new_qty = rec.qty *2;
|
|
update connect_testme
|
set qty = new_qty
|
where id = rec.id;
|
|
END FOR;
|
|
END
|
|
call test_update_loop(1)
|
|
Got error 122 'ExecuteUpdate: com.microsoft.sqlserver.jdbc.SQLServerException: 'NAME_CONST' is not a recognized built-in function name. n=-1' from CONNECT
|
|
|
|
Test scenario #2: trigger with similar SELECT on connect_testme returns the first record correctly and null for subsequent records
CREATE OR REPLACE PROCEDURE `test_read_loop2` (v_qty bigint) LANGUAGE SQL
|
BEGIN
|
|
-- insert records from connect_testme in a loop
|
FOR rec IN (SELECT id, qty, name FROM connect_testme WHERE qty >= v_qty) DO
|
UPDATE local_testme
|
SET qty = qty + rec.qty
|
WHERE id = rec.id;
|
END FOR;
|
|
END
|
|
call test_read_loop2(150)
|
OK
|
|
id qty name
|
2 300 bolt
|
3 350 screw
|
|
(looks correct to me so far)
|
|
CREATE TABLE `trig_table` (`id` bigint NOT NULL COMMENT '' , `status` enum('NEW','DOIT') NOT NULL COMMENT '' , PRIMARY KEY (`id`) ) COMMENT ''
|
|
insert into trig_table(id, status) values (1, 'NEW')
|
|
CREATE OR REPLACE TRIGGER `trig_table_before_update` BEFORE UPDATE ON `trig_table` FOR EACH ROW
|
BEGIN
|
declare new_qty bigint;
|
|
if (NEW.status = 'DOIT' and OLD.status != 'DOIT') THEN
|
|
FOR rec IN (SELECT id, qty, name FROM local_testme) DO
|
|
set new_qty = (select qty from connect_testme where id = rec.id) + 9 ;
|
|
update local_testme set qty = new_qty where id = rec.id; -- we already know remote update will fail, now test read in trigger
|
|
END FOR;
|
END IF;
|
END
|
|
update trig_table set status = 'DOIT' where id = 1
|
|
select * from local_testme
|
|
id qty name
|
2 159 bolt
|
3 screw
|
|
WRONG RESULT - note that first row had 9 added to it, but second is now NULL
|
|
|
|
Hums, even for triggers, things fail when the commands use the NEW keyword because it is not replaced by the actual value in the query text.
By the way, you can see how the commands are sent to the external source by setting:
|
|
I have the following in my.cnf
general_log=1
general_log_file=/var/log/mysql/general.log
connect_xtrace=33
But I don't see any CONNECT queries in general.log
What else do I need to enable?
|
|
FYI the following stored proc DOES work correctly when called - even though it is very similar to the trigger one that gives wrong data (both are making multiple SELECTs to connect_testme with a different variable in where clause each time - but in the procedure it produces the right result.
CREATE OR REPLACE PROCEDURE `test_read_loop_3` () LANGUAGE SQL
|
BEGIN
|
|
declare new_qty bigint;
|
|
FOR rec IN (SELECT id, qty, name FROM local_testme where id in (2,3)) DO
|
|
set new_qty = (select qty from connect_testme where id = rec.id);
|
|
update local_testme set qty = new_qty where id = rec.id;
|
|
END FOR;
|
|
END
|
|
|
FYI the following also works correctly as a stored proc (did not test in trigger).
So I think hopefully there are just two problems:
1) the NAME_CONST within the UPDATE SET clause you identified above and
2) something going on strange with variables in triggers vs stored proc (have not tested scheduled events yet)
BEGIN
|
|
declare new_qty bigint;
|
|
FOR rec IN (SELECT id, qty, name FROM local_testme) DO
|
|
SET new_qty = rec.qty;
|
insert into connect_testme (id, qty, name) values (rec.id, new_qty, rec.name);
|
|
END FOR;
|
|
END
|
|
|
Hi Olivier,
Any idea if it will be possible to fix the NAME_CONST issue in the SET clause?
Also, my earlier post about the variables being handled correctly in a stored procedure was unfortunately wrong. The following code produces the correct value on the FIRST call in a loop, but NULL (for v_old_qty) on all subsequent calls:
SET v_old_qty = (SELECT InventoryQuantity FROM ProductVariants WHERE Id = v_pv_id);
|
I have tried with parameters being passed to procedure and even declaring local variables and assigning and then using those to see if that would help (it doesn't). I would be happy to provide the output of my log file, but I'm not seeing any connect info in it.
Robert
|
|
I'll work on this. Not so easy but feasible.
About triggers, following serg's suggestion, the following turnaround can be made. Instead of:
create or replace trigger addone before insert on pets
|
for each row
|
begin
|
update anum set n = n + 1 where genre = new.race;
|
end;
|
the following can be done:
create or replace procedure dot(in r char(32)) language sql
|
begin
|
declare gr char(34);
|
set gr = concat('''',r,'''');
|
update anum set n = n + 1 where genre = gr;
|
end;
|
|
create or replace trigger addone before insert on pets
|
for each row
|
begin
|
call dot(new.race);
|
end;
|
This works because queries are rewitten in procedures (with eventual NAME_CONST functions) but they are not rewritten at all for triggers.
Note that perhaps my triggers was not properly written because it worked fine when I did:
insert into pets values(1,'Choupy',1,'chat');
|
insert into pets values(2,'Biscotte',2,'chat');
|
insert into pets values(3,'Ficelle',2,'chien');
|
insert into pets values(4,'Buggy',1,'hamster');
|
but not when doing:
insert into pets values(1,'Choupy',1,'chat'),(2,'Biscotte',2,'chat'),(3,'Ficelle',2,'chien'),(4,'Buggy',1,'hamster');
|
Looking further, this seems CONNECT not doing properly the sending of commands when they come from one statement and it probably what explains the issues you have with loops.
|
|
I found why loops are not handled correctly by CONNECT, at least in my case. I did not try your example yet.
|
|
Awesome! To summarize my use cases were:
Within a trigger on some base table (innodb) calling the following in a LOOP (often a CURSOR FOR LOOP):
1. SELECT c1 FROM someConnectTable WHERE c2 = [VAR_REF]
2. UPDATE anotherConnectTable SET c3 = [VAR_REF] WHERE c4 = [VAR_REF2]
3. CALL MyStoredProcedure( [VAR_REF], [VAR_REF2] )
In the above cases [VAR_REF] changes on each loop pass, and could be an int or string, and could be a DECLARED variable, a rec.col1 reference (i.e. a reference to a record in a CURSOR FOR LOOP), or a NEW.colX or OLD.colX reference. Note someConnectTable and anotherConnectTable could be the SAME connect table in certain instances (e.g. changing an inventory quantity) but not in others.
Further, the same type of looping with variables WITHIN the stored procedure and/or by CALLing the stored procedure within a loop.
|
|
I did successfully test inserts done from a procedure:
create or replace procedure do_insert(in sx int) language sql
|
begin
|
declare nm char(34);
|
FOR rec IN (SELECT id, name FROM pets WHERE sex = sx) DO
|
set nm = concat('''',rec.name,'''');
|
insert into names values(rec.id,nm);
|
end for;
|
end;
|
Where names is a JDBC table. However, testing in DEBUG mode, the modifications I made are not used. So it should also work for you (unless your procedure is called from a trigger).
|
|
Yes, inserts are needed but not part of what I am currently working on so haven't tested insert with variables.
Do I understand that variables are handled differently in triggers vs stored procedures?
|
|
Hi Olivier,
Any luck fixing RE "I found why loops are not handled correctly by CONNECT, at least in my case. I did not try your example yet."
Thank you again!
|
|
RE "I found why loops are not handled correctly by CONNECT, at least in my case. I did not try your example yet."
Were you able to check for both stored procedure and trigger?
I have a call today and monday with two customers that are waiting on the loop variable fix and the UPDATE SET [NAME_CONST issue] - one without the other won't solve the issue. If both are fixed for procedure but not trigger, and I can call procedure from trigger, that gets a solution to my customers and is an acceptable first cut work around.
|
|
I will push shortly the fix for both the NAME_CONST problem and the loop problem. For triggers, this cannot be directly fixed. Therefore, triggers must not directly issue the commands but call a procedure that execute them. For example:
-- On the remote server
|
create table animals (n int not null, genre char(32)) engine=myisam;
|
insert into animals values(0,'chat'),(0,'chien'),(0,'hamster');
|
|
create table last_inserted (name char(32)) engine=myisam;
|
insert into last_inserted values('None');
|
|
-- On the local server
|
-- The JDBC tables
|
CREATE TABLE anum ENGINE=CONNECT TABLE_TYPE=JDBC TABNAME=animals
|
CONNECTION='jdbc:mariadb://localhost:3308/test?user=root&password=whatever';
|
|
CREATE TABLE lanm ENGINE=CONNECT TABLE_TYPE=JDBC TABNAME=last_inserted
|
CONNECTION='jdbc:mariadb://localhost:3308/test?user=root&password=whatever';
|
|
-- The table with triggers that cannot be a CONNECT table
|
create table pets (
|
id int not null,
|
name char(32),
|
sex int(1),
|
race char(32));
|
|
create procedure do_update(in r char(32), in x int) language sql
|
begin
|
declare gr char(34);
|
set gr = concat('''',r,'''');
|
update anum set n = n + x where genre = gr;
|
end;
|
|
create procedure do_it(in m char(32)) language sql
|
begin
|
declare nm char(34);
|
set nm = concat('''',m,'''');
|
update lanm set name = nm;
|
end;
|
|
create trigger ibp before insert on pets
|
for each row
|
begin
|
call do_update(new.race,1);
|
call do_it(new.name);
|
end;
|
|
create trigger dbp before delete on pets
|
for each row
|
begin
|
call do_update(old.race,-1);
|
end;
|
|
create trigger ubp before update on pets
|
for each row
|
begin
|
if new.race != old.race
|
then
|
begin
|
call do_update(old.race,-1);
|
call do_update(new.race,1);
|
end;
|
end if;
|
end;
|
|
set connect_xtrace='QUERY,STMT';
|
|
-- this was not correctly processed before fixing
|
insert into pets values(1,'Choupy',1,'chat'),(2,'Biscotte',2,'chat'),(3,'Ficelle',2,'chien'),4,'Buggy',1,'hamster');
|
|
delete from pets where name = 'Buggy';
|
update pets set race = 'chien' where name = 'Biscotte';
|
|
|
Thank you. Regarding "For triggers, this cannot be directly fixed." Does that mean it can't be fixed for the next push but it can be fixed eventually somehow?
|
|
It means that because MariaDB does not rewrite the statements used in triggers, CONNECT cannot use them as they contains variable names, such as NEW.race, whose value is unknown. It could only be fixed by MariaDB team forcing these statements to be rewritten replacing variables by their value.
By the way, I am also working on allowing triggers for CONNECT table.
|
|
Would someone know if there is a function call that could rewrite the statement?
Also, even if NEW.race wasn't handled but it handled "normal" variables correctly that would be great because super easy to code around by assigning SET myvar = NEW.race; and using myvar in statements. Possible or still a problem?
|
|
The statement not being rewritten would still contain "myvar" whose value would be unknown.
|
|
In addition, triggers are now allowed for CONNECT tables. (not documented yet)
|
|
Because we've covered so much here, I want to clarify what is and is not currently working:
FIXED and/or WORKING CURRENTLY
in stored procedures, stored functions, and scheduled events you can now:
1) use UPDATE in a loop with variables for both SET and WHERE clauses
2) use SELECT in a loop with variables in the WHERE clause [will "select * into Rec from connectT1" work when Rec is declared like: DECLARE Rec ROW TYPE OF connectT ?]
3) use of INSERT in a loop with variables in the VALUES clause
4) use of DELETE in a loop with variables in the WHERE clause
5) triggers on connect tables themselves (no use case right now for me)... do these support NEW.col and OLD.col?
NOT FIXED and/or NOT WORKING CURRENTLY
A) 1 thru 4 from above when code is run directly from trigger (but ok if trigger calls stored procedure)
Please confirm or correct my understanding - thanks!
|
|
The only point I didn't test yet is SELECT in a loop. The rest is Ok.
|
|
SELECT in a loop is super important as we can get values one by one in a loop from the remote system, do something to them, then write them back.
|
|
Ok, I tested it on one of your previous example with two mariadb servers:
-- ON 3308
|
create table testme (id bigint, qty bigint, name varchar(50), primary key (id));
|
insert into testme (id,qty,name) values (1,125,'nut'), (2, 150, 'bolt'), (3, 175,'screw');
|
|
-- ON 3306
|
-- Using discovery
|
CREATE TABLE connect_testme
|
ENGINE=CONNECT TABLE_TYPE=JDBC TABNAME='testme' BLOCK_SIZE=500
|
CONNECTION='jdbc:mariadb://localhost:3308/test?user=root&password=whatever';
|
|
-- Now a CONNECT table
|
CREATE TABLE local_testme (
|
id bigint NOT NULL,
|
qty bigint,
|
name varchar(50),
|
PRIMARY KEY (`id`))
|
ENGINE=CONNECT TABLE_TYPE=CSV HEADER=1;
|
|
-- I added a parameter
|
CREATE OR REPLACE PROCEDURE test_read_loop (IN v_id bigint) LANGUAGE SQL
|
BEGIN
|
-- insert records from connect_testme in a loop
|
FOR rec IN (SELECT id, qty, name FROM connect_testme WHERE id >= v_id) DO
|
insert into local_testme (id, qty, name) values (rec.id, rec.qty, rec.name);
|
END FOR;
|
END;
|
|
-- To see the local received cmd and the command sent to the remote table
|
SET connect_xtrace='STMT,QUERY';
|
|
call test_read_loop(2);
|
/*
|
Query=SELECT id, qty, name FROM testme WHERE id >= 2
|
Cmd=call test_read_loop(2)
|
Query=SELECT id, qty, name FROM testme WHERE id >= 2
|
Cmd=insert into local_testme (id, qty, name) values ( NAME_CONST('rec.id',2), NAME_CONST('rec.qty',150), NAME_CONST('rec.name',_latin1'bolt' COLLATE 'latin1_swedish_ci'))
|
000002182D8A1070 check_mode: cmdtype=5
|
Cmd=insert into local_testme (id, qty, name) values ( NAME_CONST('rec.id',3), NAME_CONST('rec.qty',175), NAME_CONST('rec.name',_latin1'screw' COLLATE 'latin1_swedish_ci'))
|
*/
|
select * from local_testme;
|
/*
|
+----+-----+-------+
|
| id | qty | name |
|
+----+-----+-------+
|
| 2 | 150 | bolt |
|
| 3 | 175 | screw |
|
+----+-----+-------+
|
*/
|
|
CREATE OR REPLACE PROCEDURE test_update_loop (v_id bigint) LANGUAGE SQL
|
BEGIN
|
declare new_qty bigint;
|
FOR rec IN (SELECT id, qty, name FROM local_testme where id >= v_id) DO
|
set new_qty = rec.qty *2;
|
update connect_testme set qty = new_qty where id = rec.id;
|
END FOR;
|
END;
|
|
call test_update_loop(1);
|
/*
|
Cmd=call test_update_loop(1)
|
Cmd=call test_update_loop(1)
|
Cmd=update connect_testme set qty = NAME_CONST('new_qty',300) where id = NAME_CONST('rec.id',2)
|
Command=update testme set qty = (300) where id = 2
|
Cmd=update connect_testme set qty = NAME_CONST('new_qty',350) where id = NAME_CONST('rec.id',3)
|
Command=update testme set qty = (350) where id = 3
|
*/
|
select * from connect_testme;
|
/*
|
+----+-----+-------+
|
| id | qty | name |
|
+----+-----+-------+
|
| 1 | 125 | nut |
|
| 2 | 300 | bolt |
|
| 3 | 350 | screw |
|
+----+-----+-------+
|
*/
|
Is that ok for you or should I make more elaborate tests?
|
|
It's going to need a little more elaborate test... I'll try to write it below as best I can [hopefully no typos]:
3 examples of SELECT in loop ... syntax A, B, C
-- SYNTAX A: SELECT from CONNECT in LOOP
|
DECLARE old_qty, new_qty BIGINT;
|
FOR rec IN (SELECT id, qty, name FROM local_testme where id >= v_id) DO
|
|
-- select needs to run 3 times or more to test
|
SET old_qty = (SELECT qty FROM connect_testme WHERE id = rec.id);
|
|
SET new_qty = old_qty + 10;
|
|
UPDATE connect_testme SET qty = new_qty WHERE id = rec.id;
|
END FOR;
|
|
|
-- SYNTAX B: SELECT INTO SIMPLE_VAR from CONNECT in LOOP
|
DECLARE old_qty, new_qty BIGINT;
|
FOR rec IN (SELECT id, qty, name FROM local_testme where id >= v_id) DO
|
|
-- select needs to run 3 times or more to test
|
SELECT qty INTO old_qty
|
FROM connect_testme
|
WHERE id = rec.id;
|
|
SET new_qty = old_qty + 10;
|
|
UPDATE connect_testme SET qty = new_qty WHERE id = rec.id;
|
END FOR;
|
|
|
-- SYNTAX C: SELECT INTO ROW_TYPE from CONNECT in LOOP using
|
DECLARE old_qty, new_qty BIGINT;
|
DECLARE conn_rec ROW TYPE OF connect_testme ;
|
|
FOR rec IN (SELECT id, qty, name FROM local_testme where id >= v_id) DO
|
|
-- select needs to run 3 times or more to test
|
SELECT * INTO conn_rec
|
FROM connect_testme
|
WHERE id = rec.id;
|
|
SET new_qty = conn_rec.qty + 10;
|
|
UPDATE connect_testme SET qty = new_qty WHERE id = rec.id;
|
END FOR;
|
|
|
|
I successfully tested the three syntax procedures.
I thought it were alright because unlike UPDATE or DELETE, the SELECT and INSERT commands do not depend on the sent rewritten command. This means BTW that triggers only doing INSERT, even based on SELECT, probably don't need to call a procedure to work.
|
|
Have these changes been pushed so they will make the 10.3.13 release? I know that is coming up soon.
|
|
Hi Olivier,
I just noticed that 10.2.22 does not have this mdev in its changelog
https://mariadb.com/kb/en/library/mariadb-10222-changelog/
Does this mean that 10.3.13 won't have it either?
|
|
Yes it does but I mistyped the MDEV number in my message (18192 instead of 18292)
|
|
Is there any way to rush it into 10.3.13?
That is the only version we can upgrade our customers to.
|
|
It will go into 10.3.13 when released.
|
|
I just looked at the change log for 10.3.13 and MDEV-18292 is not in there:
https://mariadb.com/kb/en/library/mariadb-10313-changelog/
|
|
Any advice on what I would need to do build a correct ha_connect.so lib (if that is the correct one) for
CentOS 7 64 bit ?
|
|
Don't know why the change log doesn't mention it but I downloaded and installed version 10.3.13 and it works alright concerning procedures and triggers.
|
|
Hummm. When I call my original use case posted here (as a stored procedure) I get the same error as before:
Table 'ProductVariants' is marked as crashed and should be repaired
|
ProductVariants is my most important CONNECT table
|
|
The following, on centOS7, in a stored procedure produces the error:
UPDATE ProductVariants
|
SET InventoryQuantity = v_new_qty
|
WHERE Id = v_ProductVariantId;
|
Guessing the patch wasn't included in all OS releases???
|
|
I tried to test your case in test_db11615.sql on the new downloaded version 10.3.13 but could not achieve it.
Some tables could not be created, for example:
1005: Can't create table `db11615`.`purchase_orders` (errno: 150
|
"Foreign key constraint is incorrectly formed")
|
All CREATE PROCEDURE failed with syntax error such as:
1064: You have an error in your SQL syntax; check the manual that
|
corresponds to your MariaDB server version for the right syntax to use
|
near '"PurchaseOrder_SendEmailVendor"(po_id BIGINT)
|
BEGIN
|
|
|
OK, so your patch IS in 10.3.13 but it does not work for my use case 
Here are the details:
Below is the "reference procedure" that is called from a trigger because the code cannot be within the trigger itself due to a CONNECT limitation:
CREATE PROCEDURE ProductVariants_AdjustQty (p_ProductVariantId BIGINT, p_qty_adj BIGINT)
|
BEGIN
|
|
DECLARE v_old_qty, v_new_qty BIGINT;
|
|
-- ProductVariants is a CONNECT JDBC table
|
|
SELECT InventoryQuantity INTO v_old_qty
|
FROM ProductVariants
|
WHERE Id = p_ProductVariantId ;
|
|
SET v_new_qty = v_old_qty + p_qty_adj;
|
|
UPDATE ProductVariants
|
SET InventoryQuantity = v_new_qty
|
WHERE Id = p_ProductVariantId;
|
|
END
|
If I call ProductVariants_AdjustQty manually like the following it DOES work and produces the right result:
call ProductVariants_AdjustQty(21375917195344, 5)
|
Also, if I call ProductVariants_AdjustQty from ANOTHER stored procedure in a loop as shown below, it also works and produces the right result:
CREATE PROCEDURE testmain
|
BEGIN
|
declare pvid bigint default 21375917195344;
|
declare qty bigint default 0;
|
|
WHILE (qty < 4) DO
|
SET qty = qty + 1;
|
call ProductVariants_AdjustQty(21375917195344, qty);
|
END WHILE;
|
END
|
However when when I take that exact same code from the testmain procedure and make it a trigger it generates the crashed table error message below:
"Table 'ProductVariants' is marked as crashed and should be repaired"
CREATE TRIGGER testme_trigger BEFORE UPDATE ON ROG FOR EACH ROW
|
BEGIN
|
declare pvid bigint default 21375917195344;
|
declare qty bigint default 0;
|
|
WHILE (qty < 4) DO
|
SET qty = qty + 1;
|
call ProductVariants_AdjustQty(21375917195344, qty);
|
END WHILE;
|
END
|
|
|
If I want to debug this in order to see what is wrong, I need a complete test case that includes all the tables to create, the triggers, the procedures etc.
|
|
When trying to execute ProductVariants_AdjustQty I get this error:
1296: Got error 174 'Connecting: java.sql.SQLException: No suitable driver found for
|
jdbc:shopify:Appld=0beb2a6debcc4dc9a50b19723881fdc7;
|
Password=24d822c3e9c4ec42f60764243340cf5;
|
ShopUri=https://kiwi-diamond.myshopify.com' from CONNECT
|
|
|
OK, I'll work on that Monday.
In the mean time, additional testing makes it seem like it just doesn't like being called from within a trigger regardless of variables passed. I hard coded the variables in the stored procedure... calling it directly or via another procedure works correctly. But calling it from a trigger produces the crashed table error.
I'm guessing this should be easy to reproduce.
If you don't find it immediately I'll work up a simple test case in the morning.
|
|
Regarding the dump example with ProductVariants_AdjustQty ... that will be too hard to set up... it uses a special driver that requires complex licensing.... better to test with a simple remote db and simple table for the connect part.
If you take one of your existing stored procedures that does an update on a connect table (one that works), and simply call that procedure from a trigger on a local table I'm guessing you'll see the error right away.
|
|
I have uploaded the file triggers.sql I was using to test my fix. It does call procedures from triggers and work alright. Can you test it on your system?
If so, just make a complete case that fails and I'll work with it.
|
|
I have uploaded the file trigger_test_3.sql that is a complete and simple test case for this.
A direct CALL to the stored procedure works, while calling the stored procedure from a trigger on a local table (not a connect table) fails.
trigger_test_3.sql
|
|
Indeed it failed. And just before it said "Column qty not found". I think I know why; when working on the fix I had met this and knew it was a potential error but unfortunately, after having successfully tested my first fix with the case I sent you, I forgot to look at this one.
It may be regarded at a CONNECT or MariaDB bug and comes from the fact that when called from a trigger the bitmap indicating what columns are used in a query does not include columns only used by the trigger.
I'll look at what I can do to avoid this.
|
|
Were you able to find a fix?
I'm would assume the trigger needs to be handled differently because each row value has both NEW and OLD values for each column that could be referenced (for the table the trigger is on).
|
|
I am working on it.
|
|
Fixing it and being sure it will work on all cases seems very difficult because MariaDB does not call CONNECT the same way depending on the procedure being directly called or executed from a trigger. I think this is a MariaDB issue rather than a CONNECT one, and trying to overcome this in all cases does not look feasible.
The problem comes from the target table being called several times, for instance in the a_test procedure the table MS_t1 is called twice per loop, once for reading and once for updating, this not being properly indicated to CONNECT in the case of trigger.
I will discuss this with the MariaDB developers trying to find a solution but I am afraid this can take time.
Meanwhile a turnaround can sometimes be to rewrite the procedure to avoid these multiple table calls. For instance, a_test can be rewritten as:
CREATE OR REPLACE procedure a_test (v_id bigint)
|
BEGIN
|
DECLARE v_old_qty, v_new_qty, v_inventory_qty_received BIGINT;
|
|
DECLARE items CURSOR FOR
|
SELECT t2.linked_id, t2.adj_qty
|
FROM t2
|
WHERE t2.linked_id = v_id;
|
|
SET v_inventory_qty_received = 0;
|
|
FOR item IN items DO
|
SET v_inventory_qty_received = v_inventory_qty_received + item.adj_qty;
|
END FOR;
|
|
UPDATE MS_t1
|
SET qty = qty + v_inventory_qty_received
|
WHERE id = v_id;
|
END;
|
This one works and, besides, is probably faster, the table MS_t1 being updated only once.
|
|
This fix works with all the existing test cases; hoping it will always work.
|
|
Hi Olivier, I am unclear on your previous message: does this mean you were able to fix the issue?
|
|
Yes I was. I just said that there might still be some cases where it might fail. Is is impossible to imagine all the procedures people can write. But it does fix all the cases that I tested. However, you will have to test the ProductVariants_AdjustQty one.
|
|
Great! So this doesn't solve the trigger re-write issue I assume.... meaning I still can't issue a simple UPDATE on a connect table from within a trigger, but I can, in theory, call a stored procedure that does. Right?
|
|
Of course you can. This fixes all issues including calling procedures from triggers.
|
ProductVariants.csv
Triggers.sql
trigger_test_3.sql