Article Categories
- All Categories
-
Data Structure
-
Networking
-
RDBMS
-
Operating System
-
Java
-
MS Excel
-
iOS
-
HTML
-
CSS
-
Android
-
Python
-
C Programming
-
C++
-
C#
-
MongoDB
-
MySQL
-
Javascript
-
PHP
Database Articles
Page 155 of 546
What are the limitations for replicating stored procedure and functions?
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 MoreWhat are the special security requirements for using stored procedures and functions together with replication?
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 MoreWhile linking two strings, if I will add a NULL value then what would be the output of a CONCAT() function?
MySQL CONCAT() function will return a NULL if you will add a NULL value while linking two strings. Following example will demonstrate it −Examplemysql> Select CONCAT('Tutorials',NULL,'Point'); +----------------------------------+ | CONCAT('Tutorials',NULL,'Point') | +----------------------------------+ | NULL | +----------------------------------+ 1 row in set (0.06 sec) mysql> Select CONCAT('TutorialsPoint','.com',NULL); +--------------------------------------+ | CONCAT('TutorialsPoint','.com',NULL) | +--------------------------------------+ | NULL | +--------------------------------------+ 1 row in set (0.00 sec)
Read MoreCreate a procedure to find out the factorial of a number?
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 MoreHow can we create MySQL stored procedures without ‘BEGIN’ and ‘END’?
We can create MySQL stored procedures without ‘BEGIN’ and ‘END’ just in the same way created with both of them only thing is to omit to BEGIN and END. In the following example, we are creating a stored procedure without ‘BEGIN’ and ‘END’ to get all the rows from a table −Examplemysql> Delimiter // mysql> CREATE PROCEDURE Hello() -> SELECT * from Student_info; // Query OK, 0 rows affected (0.08 sec)We can see MySQL created stored procedures without BEGIN and END. Now invoke this by CALL statement −mysql> Delimiter ; mysql> CALL Hello(); +-----+---------+------------+------------+ | id | ...
Read MoreHow can we access tables through MySQL stored procedures?
We can access one or all the tables from the MySQL stored procedure. Following is an example in which we created a stored procedure that will accept the name of the table as a parameter and after invoking it, will produce the result set with all the details from the table.Examplemysql> Delimiter // mysql> Create procedure access(tablename varchar(30)) -> BEGIN -> SET @X := CONCAT('Select * from', ' ', tablename); -> Prepare statement from @X; -> Execute statement; -> END// Query OK, 0 rows affected (0.16 sec)Now invoke the procedure with the table name, we ...
Read MoreHow can we perform COMMIT transactions inside MySQL stored procedure?
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 MoreHow can REPLACE() be used with UPDATE clause to make permanent changes to a table?
As we know that REPLACE () function is used to replace the occurrences of a substring with another substring within a string. We can also use the REPLACE function with the UPDATE statement to update the table. Following example will demonstrate it −Examplemysql> Update Student set Father_Name = REPLACE(Father_Name, 'Mr.', 'Shri '); Query OK, 5 rows affected (0.06 sec) Rows matched: 5 Changed: 5 Warnings: 0 mysql> Select Name, Father_Name from Student; +---------+-----------------+ | Name | Father_Name | +---------+-----------------+ | Gaurav | Shri Ramesh | | Aarav | Shri Sanjay | ...
Read MoreWhat would be the default return type of MySQL IFNULL() control flow operator?
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 MoreWhat is the use of MySQL IFNULL() control flow function?
MySQL IFNULL() control flow function will return the first argument if it is not NULL otherwise it returns the second argument.SyntaxIFNULL(expression1, expression2)Here if expression1 is not NULL then IFNULL() will return expression1 otherwise expression2. It will return NULL if both of the arguments are NULL. Following example will exhibit this −mysql> Select IFNULL(NULL, 'Ram'); +--------------------+ | IFNULL(NULL, 'Ram') | +--------------------+ | Ram | +--------------------+ 1 row in set (0.00 sec) mysql> Select IFNULL('Shyam', 'Ram'); +-----------------------+ | IFNULL('Shyam', 'Ram') | +-----------------------+ | Shyam | +-----------------------+ ...
Read More