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;

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

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.

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%';

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;

Best Related Posts are Following:

1. MySQL Interview Questions and Answers for 3 year experience.
2. MySQL Interview questions and answers for 2 year experience.
3. Advanced MySQL Interview Questions and Answers for Experienced.
4. How can I make SQL Query case sensitive string comparison on MySQL?.
5. PHP Mysql Interview Questions and Answers for fresher and experienced.
6. Differences between Stored Procedures and Functions in MYSQL.
7. PHP MySQL interview questions and answers for experienced.
8. MySQL Interview Questions and Answers for Experienced.
9. SQLSTATE[23000]: Integrity constraint violation: 1062 Duplicate entry 2269548 for key PRIMARY.
10. Mysql Privileges - Types of privileages in MySql - How do I grant privileges in MySQL.
11. Calculate a Sum of Time using MySQL.