Showing posts with label MYSQL Interview Questions. Show all posts
Showing posts with label MYSQL Interview Questions. Show all posts

Thursday 18 June 2020

Get Mysql Row size too large error when change engine from MyISAM to Innodb

Get Mysql Row size too large error when change engine from MyISAM to Innodb
ALTER TABLE table_name ENGINE=InnoDB

When i try to change the engine From MyISAM to InnoDB, getting following error.

row size too large (> 8126). changing some columns to text or blob or using row_format=dynamic or row_format=compressed may help.

Solutions:
  1. Add the following to the my.cnf file under [mysqld] section.
            innodb_file_per_table=1;
            innodb_file_format = Barracuda;
            innodb_log_file_size = 256M    
    
         
  2. ALTER TABLE table_name ENGINE=InnoDB ROW_FORMAT=COMPRESSED KEY_BLOCK_SIZE=8;




Sunday 7 June 2020

MySQL database Administrator interview question and Answer for 6 year experienced

MySQL database Administrator interview question and Answer for 6 year experienced

Question: How to reset AUTO_INCREMENT in MySQL?>
ALTER TABLE tablename AUTO_INCREMENT = 1

Question: How to concatenate multiple rows into one field?>
SELECT GROUP_CONCAT(id SEPARATOR ', ')   FROM users   WHERE city = 'india'  GROUP BY city;

(It will concatinate all the users)


Question: Which MySQL data type to use for storing boolean values?>
BIT

Question: What are best character-set for multi-language data?>
utf8mb4

Question: What is the difference between utf8mb4_unicode_ci and utf8mb4_general_ci?
utf8mb4_unicode_ci is based on the official Unicode rules for universal sorting and comparison, which sorts accurately in a wide range of languages.

utf8mb4_general_ciis a simplified set of sorting rules which aims to do as well as it can while taking many short-cuts designed to improve speed. It does not follow the Unicode rules and will result in undesirable sorting or comparison in some situations


Question: How do you set a default value for a MySQL Datetime column?>
Add the default CURRENT_TIMESTAMP
For example:
CREATE TABLE foo (
    `modification_time` DATETIME ON UPDATE CURRENT_TIMESTAMP
)



Question: What is the difference between physical and logical backup?>
physical backup is to copy for backing up all the physical files that belongs to database.(like data files,control files,log files, executables etc), we need to stop the mysql service temporary.>
In logical backup, you don't take the copies of any physical things,you only extract the data from the data files into dump files.(ex : using export )


Question: What are World's most popular Databases?>
  1. Oracle
  2. MySQL
  3. Microsoft SQL Server
  4. PostgreSQL
  5. MongoDB



Question: Explain ACID?
Atomicity. All completed OR Nothing.
Consistency. Database protected from actions that may corrupt data.
Isolation. multiple actions occuring in parallel will not impact each other.
Durability. All commited statements data are safe.


Question: What does FLUSH TABLES WITH READ LOCK do?
1 set the global read lock - after this step, insert/update/delete/replace/alter statements cannot run.
2 close open tables - this step will block until all statements started previously have stopped.
3 set a flag to block commits.
FLUSH TABLES


Monday 1 June 2020

MySQL Stored procedure interview Questions and Answer

MySQL Stored procedure interview Questions and Answer

Question: What is stored procedure?
A stored procedure is Structured Query Language (SQL) statements which has a name, a parameter list, and SQL statement(s) stored in MySQL Server.



Question: Give example of stored procedure?
DELIMITER $$
CREATE PROCEDURE getUsersAscending()
BEGIN
	SELECT 
		first_name, 
		last_name, 
		email
	FROM
		users
	ORDER BY first_name;    
END$$
DELIMITER ;




Question: Give example of stored procedure with parameter details?
DELIMITER $$
CREATE   PROCEDURE `getUsersDetails`(IdSearch INT)    
    BEGIN
	SELECT 
		first_name, 
		last_name, 
		email
	FROM
		users
	WHERE id=IdSearch;      

    END$$
DELIMITER ;



Question: To show all stored procedures:
SHOW PROCEDURE STATUS;



Question: How to show stored procedures in a specific database
SHOW PROCEDURE STATUS WHERE Db = 'db_name';



Question: MySQL stored procedures advantages?
  1. Reduce network traffic
  2. Make database more secure



Question: MySQL stored procedures dis-advantages?
  1. Resource usages: If you use many stored procedures, the memory usage of every connection will increase substantially.
  2. Troubleshooting:MySQL does not provide any facilities to debug stored procedures like other enterprise database products such as Oracle and SQL Server
  3. Maintenances Developing and maintaining stored procedures often requires a specialized skill set that not all application developers possess.



Question: How to call stored procedure with OR without arguments?
Call without argument
call getUsersAscending();
Call With argument
call getUsersDetails(10);



Question: How to delete the procedure?
DROP PROCEDURE IF EXISTS getUsersAscending;



Question: What if i am trying to delete the stored procedure for non-exist.?
It will give warning only, if you have used "IF EXISTS"
It will give Error, if you have used not used "IF EXISTS"


Question: How to Alter a stored procedure in mysql?
1: Delete the Stored procedure. 
2: Re-Create the Stored procedure.



Tuesday 7 April 2020

How to check active connections in MySQL?

How to check active connections in MySQL?

Question: How to check active connections in mysql?
show status where `variable_name` = 'Threads_connected';



Question: How to check the limit of MySQL active connections?
SHOW VARIABLES LIKE "max_connections";



Question: Display the list of active MySQL Process list?
show processlist;



Question: How to check the max no of connections used?
SHOW STATUS WHERE `variable_name` = 'Max_used_connections';



Question: MySQL set the max no of connections used (Temporarily/Quick)?
SET GLOBAL max_connections = 512;



Question: MySQL set the max no of connections used (Temporarily/Quick)?
Open my.cnf or my.ini in MySQL
max_connections = 512

service mysqld restart


Question: MySQL check number of connections throughout history?
show status like 'Connections%';

Thursday 12 October 2017

What is Mysql


What is Mysql?
MySQL is an open-source relational database management system (RDBMS). Its name is a combination of "My", the name of co-founder Michael Widenius's daughter, and "SQL", the abbreviation for Structured Query Language.
Today, Its most widely used in Web Development.




What are Client programs?
Communicating with the server to manipulate information.The following list of client programs:
  • MySQL Query Browser and MySQL Administrator.
  • All mysql is a command-line program.


What are three layer of Mysql Architecture?
Following are 3 layer of Mysql Architecture
  1. Application Layer: It represents the interface for all type of users, The user who is interacting with databases.
    • Sophisticated User
    • Specialized User
    • Naive User
    • Database Administrators
  2. Logical Layer: Core functionality of RDBMS is represented in the logical layer.
  3. Physical Layer: It is responsible for the storage of variety of information and that are following.
    • Data Files: It store user data
    • Data dictionaryr: store metadata about the structure of the database.
    • Statistical Data: Statistical information about the data
    • Log Information: Keep the track of query information.



Question: In which language it was written?
C/C++


Question: What is current Stable Version ?
5.7.21 / 15 January 2018;


Question: How to change the collation of column, table and database?
ALTER TABLE `hotels` CHANGE `Name` `Name` VARCHAR(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL;