Actually, the default return type of IFNULL(expression1, expression2) is more general of the two expressions, in the order STRING, REAL or INTEGER. It can be understood from the following example −Examplemysql> Create table testing Select IFNULL(100, 'testing123'); Query OK, 1 row affected (0.18 sec) Records: 1 Duplicates: 0 Warnings: 0 mysql> Select * from testing568; +-----------------------+ | IFNULL(100, 'testing') | +-----------------------+ | 100                   | +-----------------------+ 1 row in set (0.00 sec) mysql> Describe testing568; +-----------------------+------------+------+-----+---------+-------+ | Field                 | Type     ... Read More

Actually, they both are assignment operator and used to assign values but the significant difference between them is as follows −= operator assigns a value either as a part of the SET statement or as a part of the SET clause in an UPDATE statement, in any other case = operator is interpreted as a comparison operator. On the other hand, := operator assigns a value and it is never interpreted as a comparison operator.mysql> Update estimated_cost1 SET Tender_value = '8570.000' where id = 2; Query OK, 1 row affected (0.06 sec) Rows matched: 1 Changed: 1 Warnings: 0 ... Read More

It can be created with the help of the following query −mysql> Delimiter // mysql> CREATE PROCEDURE fact(IN x INT)     -> BEGIN     -> DECLARE result INT;     -> DECLARE i INT;     -> SET result = 1;     -> SET i = 1;     -> WHILE i SET result = result * i;     -> SET i = i + 1;     -> END WHILE;     -> SELECT x AS Number, result as Factorial;     -> END// Query OK, 0 rows affected (0.17 sec)Now when invoking this ... Read More

Followings are the limitations for replicating stored procedure and functions −Type of Action − Actually the replication of stored procedure and functions depends upon the type of action. If the action, embedded in stored procedures, is nondeterministic (random) or time-based then it may not replicate properly. By their very nature, randomly produced results are not predictable and cannot be exactly reproduced, and therefore, random actions replicated to a slave will not mirror those performed on a master.Type of transaction − non-transactional tables for which errors occur during large DML actions (such as bulk inserts) may experience replication issues in that ... Read More

Actually, a MySQL slave server has the authority to execute any statement read from a master's MySQL server binary log, hence some special security constraints exist for using stored functions with replication. If replication or binary logging in general (for the purpose of point-in-time recovery) is active, then MySQL DBAs have two security options open to them −Option of SUPER privilegeAny user wishing to create stored functions must be granted the SUPER privilege by DBA.log_bin_trust_function_creators modeActually, log_bin_trust_function_creators enables anyone with the standard CREATE ROUTINE privilege to create stored functions hence a DBA can set the log_bin_trust_function_creators system variable to 1.Read More

Actually standard actions carried out in stored procedures and functions are replicated from a master MySQL server to a slave MySQL server. Even the creation of stored procedures and functions carried out through normal DDL statements on a master MySQL server are replicated to a slave MySQL server. In this way, objects will exist on both the servers.The actions that take place inside the stored procedure and functions are replicated because MySQL records each DDL event that occurs inside stored procedures and functions. After recording the events it is replicated to the slave MySQL server. But the actual calls made ... Read More

As we know that ROLLBACK will revert any changes made to the database after the transaction has been started. To perform the ROLLBACK in MySQL stored procedure we must have to declare EXIT handler. We can use a handler for either sqlexception or SQL warnings. It can be understood with the help of an example in which stored procedure having ROLLBACK created for the table having the following details −mysql> SHOW CREATE table gg\G *************************** 1. row ***************************        Table: gg Create Table: CREATE TABLE `gg` (    `Id` int(11) NOT NULL AUTO_INCREMENT,    `Name` varchar(30) NOT NULL,    PRIMARY KEY ... Read More

Suppose one of the queries fails or generates errors and another query (s) properly executed the MySQL still commit the changes of the properly executed query(s). It can be understood from the following example in which we are using the table ‘employee.tbl’ having the following data −Examplemysql> Select * from employee.tbl; +----+---------+ | Id | Name    | +----+---------+ | 1  | Mohan   | | 2  | Gaurav  | | 3  | Sohan   | | 4  | Saurabh | | 5  | Yash    | +----+---------+ 5 rows in set (0.00 sec) mysql> Delimiter // mysql> ... Read More

As we know the START transaction will start the transaction and COMMIT is used to made any changes made after starting the transaction. In the following example, we have created a stored procedure with COMMIT along with START transaction which will insert a new record and commit changes in table ‘employee.tbl’ having the following data −mysql> Select * from employee.tbl; +----+---------+ | Id | Name    | +----+---------+ |  1 | Mohan   | |  2 | Gaurav  | |  3 | Rahul   | |  4 | Saurabh | +----+---------+ 4 rows in set (0.00 sec)Examplemysql> Delimiter // mysql> ... Read More

As we know the START transaction will start the transaction and set the auto-commit mode to off. In the following example, we have created a stored procedure with a START transaction which will insert a new record in table employee.tbl having the following data −mysql> Select * from employee.tbl; +----+---------+ | Id | Name    | +----+---------+ | 1  | Mohan   | | 2  | Gaurav  | | 3  | Rahul   | +----+---------+ 3 rows in set (0.00 sec)Examplemysql> Delimiter // mysql> Create Procedure st_transaction()    -> BEGIN    -> START TRANSACTION;    -> INSERT INTO employee.tbl(name) values ... Read More