I would like to create a conditional update on an audit table, such that if the update on the main table succeeds, then the update on the audit table is performed. Right now I have this

CREATE DEFINER=root@localhost PROCEDURE updateParent(
	IN inId INT(10), 
	IN inName varchar(75), 
	IN inEmail varchar(50), 
	IN inPhone varchar(8),
    IN inUsername varchar(50)
)
BEGIN

SET AUTOCOMMIT = 0;
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
START TRANSACTION;

	update parent
    set nameOfPerson = inName
			  ,email = inEmail 
              ,phonenr = inPhone
    where id = inId;
    
COMMIT;           
    
    SET @rowcount = ROW_COUNT();
    
    IF @rowcount > 0 THEN 
		INSERT INTO parent_h
		(parent_id, nameOfPerson, email, phonenr, opts, event, username)
		SELECT id, nameOfPerson, email, phonenr, ts, "U", inUsername
		from parent
		where id = inId;        
	END IF;
    
        
COMMIT;       
    
END

Occasionally, if the update is performed and zero rows are updated (no data was changed), then I do not want the audit table update to be performed. Are there any disadvantages of doing it this way? and are there any better ways? This is a web-application. UPDATE: I tried with an AFTER_UPDATE trigger. It seems like MySQL runs the trigger even if zero rows are updated on the table. That means I need to test each field to determine if any actual changes were made.

CREATE DEFINER=root@localhost TRIGGER parent_AFTER_UPDATE AFTER UPDATE   ON parent FOR EACH ROW BEGIN  

IF (  
	OLD.id  NEW.id or  
	OLD.nameOfPerson  NEW.nameOfPerson or  
	OLD.email  NEW.email or   
	OLD.phonenr  NEW.phonenr  
)   
THEN  
	INSERT INTO parent_h  
		(  
		parent_id,   
		nameOfPerson,   
		email,   
		phonenr,   
		opts,   
		event,   
		username  
		)  
		VALUES   
		(  
		NEW.id,  
		NEW.nameOfPerson, 
		NEW.email, 
		NEW.phonenr, 
		NEW.ts,  
		'U',  
		inUsername
		)  
	  ;  
END IF;  
    
END

An additional issue is that I need to add the userid of the principal performing the update, which is not available in the table update.

I am trying to create a stored procedure in MySQL 5.5.8. using connector 8.0.13. Once this procedure is debugged, it will have to be embedded into a c#.net application, to be created on the fly every time the program is set up. For now, I am doing it manually. For someone like me, with MS SQL, Oracle, and DB2 background, this is challenging. I honestly read MySQL dev docs and googled to get a hang of MySQL SQL syntax. What I gather is that: * I have to change delimiter to something like // * I should use if not exists just in case * Parameters do not start with @, and they have to be parenthesized * I end the block with the temporary delimiter // and restore it to ; afterwards This is what I arrived at: delimiter // create procedure if not exists logging.logEntry( in hostName varchar(512) ,in entryDateTime datetime ,in entryText varchar(1024) ,out return_value int ) begin insert into logging.log (hostName, entryDateTime, entryText) select hostName, entryDateTime, entryText; set return_value = last_insert_id(); select return_value; end// delimiter ; When I run the above code in SquirrelSQL, the server throws the following error: > Error: You have an error in your SQL syntax; check the manual that > corresponds to your MySQL server version for the right syntax to use > near 'if not exists loging.logEntry`( in hostName varchar(512) ,in > entryDat' at line 1 SQLState: 42000 ErrorCode: 1064 Here is what I tried: * Ran without DELIMITER * Removed if not exists * Removed backquotes around entity names * Inserted values() instead of select even though the standalone insert works I know that this is something totally obvious for a seasoned MySQL developer, but it escapes someone like me with a different background. Am I using any functionality that does not exist in 5.5.8 or are there bugs in my code?

SUB-TREE WITHIN A TREE in MySQL -- In my MYSQL Database COMPANY, I have a Table: Employee with recursive association, an employee can be boss of other employee. A self relationship of kind (SuperVisor (1)- SuperVisee (∞) ). Query to Create Table: CREATE TABLE IF NOT EXISTS Employee ( SSN varchar(64) NOT NULL, Name varchar(64) DEFAULT NULL, Designation varchar(128) NOT NULL, MSSN varchar(64) NOT NULL, PRIMARY KEY (SSN), CONSTRAINT FK_Manager_Employee FOREIGN KEY (MSSN) REFERENCES Employee(SSN) ) ENGINE=InnoDB DEFAULT CHARSET=latin1; I have inserted a set of tuples (Query): INSERT INTO Employee VALUES ("1", "A", "OWNER", "1"), ("2", "B", "BOSS", "1"), # Employees under OWNER ("3", "F", "BOSS", "1"), ("4", "C", "BOSS", "2"), # Employees under B ("5", "H", "BOSS", "2"), ("6", "L", "WORKER", "2"), ("7", "I", "BOSS", "2"), # Remaining Leaf nodes ("8", "K", "WORKER", "3"), # Employee under F ("9", "J", "WORKER", "7"), # Employee under I ("10","G", "WORKER", "5"), # Employee under H ("11","D", "WORKER", "4"), # Employee under C ("12","E", "WORKER", "4") The inserted rows has following **Tree-Hierarchical-Relationship**: A <---ROOT-OWNER /|\ / A \ B F //| \ \ // | \ K / | | \ I L H C / | / \ J G D E I written a query to find relationship: SELECT SUPERVISOR.name AS SuperVisor, GROUP_CONCAT(SUPERVISEE.name ORDER BY SUPERVISEE.name ) AS SuperVisee, COUNT(*) FROM Employee AS SUPERVISOR INNER JOIN Employee SUPERVISEE ON SUPERVISOR.SSN = SUPERVISEE.MSSN GROUP BY SuperVisor; And output is: +------------+------------+----------+ | SuperVisor | SuperVisee | COUNT(*) | +------------+------------+----------+ | A | A,B,F | 3 | | B | C,H,I,L | 4 | | C | D,E | 2 | | F | K | 1 | | H | G | 1 | | I | J | 1 | +------------+------------+----------+ 6 rows in set (0.00 sec) [**QUESTION**] Instead of complete Hierarchical Tree, I need a SUB-TREE from a point (selective) e.g.: If input argument is B then output should be as below... +------------+------------+----------+ | SuperVisor | SuperVisee | COUNT(*) | +------------+------------+----------+ | B | C,H,I,L | 4 | | C | D,E | 2 | | H | G | 1 | | I | J | 1 | +------------+------------+----------+ Please help me on this. If not query, a stored-procedure can be helpful. I tried, but all efforts were useless!

I have written a stored procedure which makes use of a temporary table. I know that in SQL Server, temporary tables are session-scoped. However, I have not been able to find definitive information on exactly what a session is capable of. In particular, if it is possible for this stored procedure to execute twice concurrently in a single session, a significantly higher isolation level is required for a transaction within that procedure due to the two executions now sharing a temporary table.

I am using Mysql 10.1.29-MariaDB Database to create a new table. What I am trying to do is to increment a number for each duplicate appearance of a value of company_name in another column. For example, for the table: provided order_placed column of both table should be null

`
# req 


+--------------------------------------------------+
|                        req                       |
+--------------------------------------------------+
| req_id | order_placed | contact_id | seq_records |
+--------+--------------+------------+-------------+
| 1      |         null |       1000 |        null |
+--------+--------------+------------+-------------+
| 2      |         null |       1002 |        null |
+--------+--------------+------------+-------------+
| 3      |         null |       1003 |        null |
+--------+--------------+------------+-------------+



+-------------------------------------------------------+
|                               contact                 |
+-------------------------------------------------------+
| contact_id | first_name | order_placed | company_name |  
+------------+------------+--------------+--------------+
| 1000       | dirt       |         null |         Asus | 
+------------+------------+--------------+--------------+
| 1002       | dammy      |         null |         Asus | 
+------------+------------+--------------+--------------+
| 1003       | samii      |         null |         Asus | 
+------------+------------+--------------+--------------+
| 1004       | xenon      |         null |       Lenova | 
+------------+------------+--------------+--------------+


CREATE TABLE req (
  req_id bigint(20) NOT NULL,
   order_placed char(1) COLLATE utf8_bin DEFAULT NULL,
   contact_id bigint(20) DEFAULT NULL,
   seq_records bigint(2) DEFAULT NULL,
  PRIMARY KEY (req_id),
  KEY contact_id (contact_id),
  CONSTRAINT req_ibfk_10 FOREIGN KEY (contact_id) REFERENCES 
  contact (contact_id)
) 
/*!40101 SET character_set_client = @saved_cs_client */;

# contact

CREATE TABLE contact (
  contact_id bigint(20) NOT NULL,
  first_name varchar(100) COLLATE utf8_bin NOT NULL,
  company_name varchar(100) COLLATE utf8_bin DEFAULT NULL,
  company_id varchar(100) COLLATE utf8_bin DEFAULT NULL,
  order_placed char(1) COLLATE utf8_bin DEFAULT NULL,
  PRIMARY KEY (contact_id),
  KEY index_name (contact_id),
)

` # query used

`
DELIMITER $$
DROP procedure IF EXISTS recordsequence $$
CREATE procedure recordsequence ()
BEGIN

declare companyname varchar(250);
declare recordcount integer default 0;
declare duplcount integer default 0;
DECLARE vfinished INTEGER DEFAULT 0;
declare icount int default 0;
DEClARE records_cursor CURSOR FOR
select c.company_name,count(c.company_name),r.opr_id from contact c, request r where c.contact_id=r.contact_id and r.order_placed is null  group by c.company_name;
-- declare NOT FOUND handler
DECLARE CONTINUE HANDLER
FOR NOT FOUND SET vfinished = 1;
OPEN records_cursor;
transfer_records: LOOP
FETCH records_cursor INTO companyname,duplcount;
IF vfinished = 1 THEN
LEAVE transfer_records;
END IF;

begin
set recordcount := duplcount;
set icount := 1;
DEClARE records_cursor1 CURSOR FOR
select c.contact_id,c.company_name from contact c, request r where c.company_name = companyname and c.contact_id=r.contact_id and r.order_placed is null group by c.company_name;
-- declare NOT FOUND handler
DECLARE CONTINUE HANDLER
FOR NOT FOUND SET vfinished = 1;
OPEN records_cursor1;
transfer_records1: LOOP
FETCH records_cursor INTO contactid,companyname;
IF vfinished = 1 THEN
LEAVE transfer_records1;
END IF;

begin

UPDATE contact set reorder_sequence = icount where contact_id = contactid;
set icount := icount + 1;
end;

END LOOP transfer_records1;

CLOSE records_cursor1;

if(recordcount == icount) THEN

select concat('company_name Updated successfully', companyname);

else
select concat('company_name count mismatches please check', companyname);
end if

end

END LOOP transfer_records;

CLOSE records_cursor;

End$$
DELIMITER ;

` the above query is to create a procedure for the steps below 1. To fetch records companyname and duplcount of the company names with the cursor. 2. To fetch contact id of each company names and start a loop for a update statement. 3. To update reorder_sequence table with values like the example given below # expected Result

`
Eg:  contact table

+--------------------------------------------------------+
|                         contact                        |
+--------------------------------------------------------+
| order_placed | contact_id | company_name | seq_records |
+--------------+------------+--------------+-------------+
| null         |       1002 |         Asus | 1           |
+--------------+------------+--------------+-------------+
| null         |       1003 |         Asus | 2           |
+--------------+------------+--------------+-------------+
| null         |       1005 |         Asus | 3           |
+--------------+------------+--------------+-------------+
| null         |       1006 |       Lenova | 1           |
+--------------+------------+--------------+-------------+

` Like the above example i have updated seq_records column with values according to the company_name column provided both order_placed column is null # error A syntax error occurred with code 1064 near second select statement.

I have a role ExecSP which I use to manage who can execute stored procedures. EXECUTE permission is granted on all SPs to this role. I also use SQL Server Data Tools (SSDT) to manage MS SQL Server database schema. In the file where my SP is defined, I have this: CREATE PROC SomeProc AS . . . GO GRANT EXECUTE ON SomeProc TO ExecSP However, when I apply the change to the target database, and do the schema comparison, it reports differences in SP permissions, where on the DB side I have this: GRANT EXECUTE ON SomeProc TO ExecSP AS dbo I've [tried to understand](https://learn.microsoft.com/en-us/sql/t-sql/statements/grant-object-permissions-transact-sql) what AS dbo part represents, but I couldn't. My questions: 1. Does it mean that the SP will be executed with dbo rights, regardless of who is executing it? (I guess/hope it doesn't.) 2. Why is this added automatically? 3. I want this SP to be executed with the rights the caller has - how do I achieve that?

Because I never know if my procedure might be called from other procedures that have an opened transaction, should I always check for existing transactions before opening a new one? It seems like a bit of boilerplate code that I need to repeat everywhere and it will make for worse readability. I'm trying to future proof against this error: > Transaction count after EXECUTE indicates a mismatching number of BEGIN and COMMIT statements. Previous count = 1, current count = 0. Example of boilerplate code Exception handling and nested transactions

***Environment:*** - ***DB Version:*** Oracle Database 12cR2 EE on Exadata - ***RAC:*** True - ***Problem:*** ORA-06508 on procedure. > One of our users had a DBA role privilege, we don't want such a big > privilege to be defined anymore. When we revoke the DBA role privilege > from this user, all procedures, packages and triggers became invalid. > This was something we expected at that moment and we accepted it as > normal. When we compiled all of them, there was no problem and we > reassigned the DBA role privilege to this user in order to postpone > this work until later. > > The problem starts from this point. We get an error when one of the > procedures wants to run. But the error is not constantly getting, for > example it was working correctly for the last 26 hours, but then it > got the error. When we drop / create or compile, the problem is > solved. However, the strange part is that when this problem occurs, > the procedure and dependencies all seem to be valid. The error is as > follows: > > > > > > > > Error raised in: PROCEDURE_NAME at line xxxx - ORA-06508: PL/SQL: > could not find program unit being called ***Line-xxxx:*** EXCEPTION WHEN OTHERS THEN ROLLBACK; R_CODE:= 400; R_MESSAGE:='Error raised in: '|| $$plsql_unit ||' at line ' || $$plsql_line || ' - '||sqlerrm; I could not find a suitable solution for this scenario on Oracle Support or on dba.stackexchange and I am still researching. Anyone have a good idea? Best Regards,

Oracle version: =============== CREATE OR replace PROCEDURE Item_reco_recordset (p_depositid IN NUMBER, p_b1 IN NUMBER, p_itemset OUT SYS_REFCURSOR) AS BEGIN OPEN p_itemset FOR SELECT * FROM pcwitem WHERE deposittransid = p_depositid AND ( ( Bitand(recostatecode, 1) > 0 AND Bitand(recostatecode, 5) != 0 ) OR ( Bitand(recostatecode, 2) > 0 AND Bitand(recostatecode, 6) != 0 ) OR ( p_b1 = 1 AND Bitand(recostatecode, 3) > 0 AND Bitand(recostatecode, 7) != 0 ) OR ( Bitand(recostatecode, 4) > 0 AND Bitand(recostatecode, 8) != 0 ) OR ( Bitand(recostatecode, 9) > 0 AND Bitand(recostatecode, 10) != 0 ) OR ( Bitand(recostatecode, 11) > 0 AND Bitand(recostatecode, 15) != 0 ) OR ( Bitand(recostatecode, 12) > 0 ) OR ( Bitand(recostatecode, 16) > 0 AND Bitand(recostatecode, 14) != 0 ) OR ( Bitand(recostatecode, 18) > 0 ) OR ( Bitand(recostatecode, 19) > 0 AND Bitand(recostatecode, 20) != 0 ) OR ( Bitand(recostatecode, 21) > 0 ) OR ( Bitand(recostatecode, 22) > 0 ) OR ( Bitand(recostatecode, 23) > 0 AND Bitand(recostatecode, 24) != 0 ) OR ( Bitand(recostatecode, 25) > 0 AND Bitand(recostatecode, 29) != 0 ) OR ( Bitand(recostatecode, 26) > 0 ) OR ( Bitand(recostatecode, 28) > 0 ) OR ( Bitand(recostatecode, 31) > 0 ) OR ( Bitand(recostatecode, 32) > 0 AND Bitand(recostatecode, 61) != 0 ) OR ( Bitand(recostatecode, 39) > 0 ) OR ( Bitand(recostatecode, 40) > 0 AND Bitand(recostatecode, 64) != 0 ) OR ( Bitand(recostatecode, 48) > 0 ) OR ( Bitand(recostatecode, 51) > 0 ) OR ( Bitand(recostatecode, 53) > 0 AND Bitand(recostatecode, 54) != 0 ) OR ( Bitand(recostatecode, 57) > 0 AND Bitand(recostatecode, 58) != 0 ) ); END item_reco_recordset; SQL Server Version (throws error -- bitand not a recognized built-in function): ---------- IF OBJECT_ID('Item_reco_recordset', 'P') IS NOT NULL DROP PROCEDURE Item_reco_recordset; GO CREATE PROCEDURE Item_reco_recordset (@p_depositid FLOAT, @p_b1 FLOAT ) AS BEGIN SET NOCOUNT ON; SELECT * FROM pcwitem WHERE deposittransid = @p_depositid AND ( ( Bitand(recostatecode, 1) > 0 AND Bitand(recostatecode, 5) != 0 ) OR ( Bitand(recostatecode, 2) > 0 AND Bitand(recostatecode, 6) != 0 ) OR ( @p_b1 = 1 AND Bitand(recostatecode, 3) > 0 AND Bitand(recostatecode, 7) != 0 ) OR ( Bitand(recostatecode, 4) > 0 AND Bitand(recostatecode, 8) != 0 ) OR ( Bitand(recostatecode, 9) > 0 AND Bitand(recostatecode, 10) != 0 ) OR ( Bitand(recostatecode, 11) > 0 AND Bitand(recostatecode, 15) != 0 ) OR ( Bitand(recostatecode, 12) > 0 ) OR ( Bitand(recostatecode, 16) > 0 AND Bitand(recostatecode, 14) != 0 ) OR ( Bitand(recostatecode, 18) > 0 ) OR ( Bitand(recostatecode, 19) > 0 AND Bitand(recostatecode, 20) != 0 ) OR ( Bitand(recostatecode, 21) > 0 ) OR ( Bitand(recostatecode, 22) > 0 ) OR ( Bitand(recostatecode, 23) > 0 AND Bitand(recostatecode, 24) != 0 ) OR ( Bitand(recostatecode, 25) > 0 AND Bitand(recostatecode, 29) != 0 ) OR ( Bitand(recostatecode, 26) > 0 ) OR ( Bitand(recostatecode, 28) > 0 ) OR ( Bitand(recostatecode, 31) > 0 ) OR ( Bitand(recostatecode, 32) > 0 AND Bitand(recostatecode, 61) != 0 ) OR ( Bitand(recostatecode, 39) > 0 ) OR ( Bitand(recostatecode, 40) > 0 AND Bitand(recostatecode, 64) != 0 ) OR ( Bitand(recostatecode, 48) > 0 ) OR ( Bitand(recostatecode, 51) > 0 ) OR ( Bitand(recostatecode, 53) > 0 AND Bitand(recostatecode, 54) != 0 ) OR ( Bitand(recostatecode, 57) > 0 AND Bitand(recostatecode, 58) != 0 ) ); END; item_reco_recordset;

Dump of the test database:

-- MariaDB dump 10.19  Distrib 10.9.6-MariaDB, for Linux (x86_64)
--
-- Host: localhost    Database: book
-- ------------------------------------------------------
-- Server version	10.9.6-MariaDB

--
-- Table structure for table publisher
--

CREATE TABLE publisher (
  ID int(10) unsigned NOT NULL AUTO_INCREMENT,
  PublisherName varchar(100) NOT NULL,
  PRIMARY KEY (ID),
  UNIQUE KEY publisher_UN (PublisherName)
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;


--
-- Dumping data for table publisher
--

LOCK TABLES publisher WRITE;

INSERT INTO publisher VALUES (1,'TestCase');

UNLOCK TABLES;

--
-- Dumping routines for database 'book'
--

DELIMITER ;;
CREATE  PROCEDURE ensurePublisher(
	IN v_PublisherName VARCHAR(100)
)
    MODIFIES SQL DATA
BEGIN
	DECLARE pubID INT unsigned;

	SELECT ID INTO pubID FROM publisher WHERE PublisherName = v_PublisherName LIMIT 1;

	IF ISNULL(pubID) THEN
	    INSERT INTO publisher (PublisherName) VALUES (v_PublisherName);
	END IF;
END ;;
DELIMITER ;

-- Dump completed on 2023-06-10 15:24:11

Doing a CALL ensurePublisher("TestCase"); query 100 times will return mysql_affected_rows() 1 even though duplicates won't INSERT (unique key on PublisherName). Is that intended behavior? --- Please note that this is a minimal example I came up with to show the issue I have. This one could easily just be INSERT IGNORE INTO.

A vendor application we're using utilizes multiple _processes_ (not threads) spread across multiple computers, one for each processing core available. When asked for a batch of calculations, the application distributes the load between these engines, which then go to the database for additional details necessary to perform the computations. As we add more cores -- and engines -- the simultaneous rush to the database causes a load spike so big, some engines start getting the ORA-12170: TNS:Connect timeout occurred error, which causes them to crash (I know, it shouldn't, but we cannot fix the vendor's code). For example, with the engine-count of 1386, about 300 engines dump cores, because the 16-core Oracle server cannot cope... There is no official way to cause the application to _stagger_ these engine-initializations... But the first thing each engine is doing is invoke the same stored procedure -- which updates the same table -- and I'm wondering, if I can alter it to perform the staggering: cause each engine to _sleep_ for random number of milliseconds. What would the SQL snippet for this look like? Our servers run Oracle-19...