mysqldump: Couldn't execute 'SET OPTION SQL_QUOTE_SHOW_CREATE=1':

After upgraded MySQL db to MySQL 5.6.16, mysqldump resulting the following error.

shell> mysqldump -h sandbox -P9216 -u root -p dumptest > dumptest.sql
Enter password:
mysqldump: Couldn't execute 'SET OPTION SQL_QUOTE_SHOW_CREATE=1': 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 'OPTION SQL_QUOTE_SHOW_CREATE=1' at line 1 (1064)

The reason for this is that, before starting to take backup of given database or tables mysqldump first sets an option SQL_QUOTE_SHOW_CREATE by running  "SET OPTION SQL_QUOTE_SHOW_CREATE=1" statement in the server, earlier versions of MySQL i.e MySQL < 5.6 there is no problem with those servers because of those servers support "SET OPTION" statements. Since MySQL 5.6.5 the "SET OPTION" is deprecated in favor of SET without OPTION. Thats the reason why mysqldump fails with the above mentioned error.

In the newer versions of MySQL client libraries(mysqldump) the "SET OPTION SQL_QUOTE_SHOW_CREATE=1" is replace by "SET SQL_QUOTE_SHOW_CREATE=1".

NOTE : The statement "SET SQL_QUOTE_SHOW_CREATE=1" will execute on all MySQL versions i.e (MySQL 5.1, 5.5 and 5.6).

The solution to this problem is, upgrade your MySQL client binaries.

Let see, what makes difference the use of "SET SQL_QUOTE_SHOW_CREATE=1".

mysql> show variables like 'SQL_QUOTE_SHOW_CREATE';
+-----------------------+-------+
| Variable_name         | Value |
+-----------------------+-------+
| sql_quote_show_create | OFF   |
+-----------------------+-------+
1 row in set (0.00 sec)

mysql> show create table rpl3\G
*************************** 1. row ***************************
       Table: rpl3
Create Table: CREATE TABLE rpl3 (
  id int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(15) DEFAULT NULL,
  sid varchar(100) DEFAULT NULL,
  PRIMARY KEY (id)
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8
1 row in set (0.00 sec)

mysql> SET SQL_QUOTE_SHOW_CREATE=1;
Query OK, 0 rows affected (0.00 sec)

mysql> show variables like 'SQL_QUOTE_SHOW_CREATE';
+-----------------------+-------+
| Variable_name         | Value |
+-----------------------+-------+
| sql_quote_show_create | ON    |
+-----------------------+-------+
1 row in set (0.00 sec)

mysql> show create table rpl3\G
*************************** 1. row ***************************
       Table: rpl3
Create Table: CREATE TABLE `rpl3` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(15) DEFAULT NULL,
  `sid` varchar(100) DEFAULT NULL,
  PRIMARY KEY (`id`).
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8
1 row in set (0.00 sec)

If we observe the output of above 2 show create statements, the result is different based on sql_quote_show_create. if enable "sql_quote_show_create" it will quote the table name and column names, which won't cause any reserved words usage errors.

For more information about this bug, go through MySQL Bug Report.




MySQL Internals, MySQL HighAvailability, MySQL Performance Tuning, MySQL Query Optimization, MySQL performance, MySQL Fabric, MySQL HA, MySQL Installation, MySQL Upgrade, InnoDB Performance Tuning, InnoDB Buffer Pool Size, MySQL Performance Tuning, MySQL ClusterMySQL Latest News, News and Events, MySQL Customers 

MySQL Threads

The MySQL server creates the following threads:

Connection manager threads handle client connection requests on the network interfaces that the server listens to. On all platforms, one manager thread handles TCP/IP connection requests. On Unix, this manager thread also handles Unix socket file connection requests.

A signal thread handles all signals. This thread also normally handles alarms and calls process_alarm() to force timeouts on connections that have been idle too long.

InnoDB Main Thread/Background Thread, The main InnoDB thread is a special thread that just runs in a loop to do various kinds of work.  In older versions of innodb it did just about everything, and that was often a source of problems.  It was once responsible for keeping the transaction logs flushed as well as handling asynchronous io requests and much more.  A stall in any number of places would cause the others to get far behind.

Since then many tasks handled by the main thread have been split out into separate threads, which means even if there's a stall in one, others can continue to function and help make sure the server keeps responding.

For InnoDB there will be additional read and write threads by default. The number of these are controlled by the innodb_read_io_threads and innodb_write_io_threads parameters.

On a master replication server, connections from slave servers are handled like client connections: There is one thread per connected slave.

On a slave replication server, an I/O thread is started to connect to the master server and read updates from it. An SQL thread is started to apply updates read from the master. These two threads run independently and can be started and stopped independently.

If the server is started with the --flush_time=val option, a dedicated thread is created to flush all tables every val seconds.

If the event scheduler is active, there is one thread for the scheduler, and a thread for each event currently running.

For more information go through MySQL Threads.



MySQL Internals, MySQL HighAvailability, MySQL Performance Tuning, MySQL Query Optimization, MySQL performance, MySQL Fabric, MySQL HA, MySQL Installation, MySQL Upgrade, InnoDB Performance Tuning, InnoDB Buffer Pool Size, MySQL Performance Tuning, MySQL ClusterMySQL Latest News, News and Events, MySQL Customers 

MySQL InnoDB Row Level Locking Mechanism

We know that MySql InnoDB engine provides row level locking, while MyISAM can only do table level lock. But, InnoDB's row level locking mechanism may not be as what you expect. InnoDB doesn't really lock rows of data, instead, it set locks on every index record that is scanned in the processing of the SQL statement. This means, only when your SQL statement is using index to query data, InnoDB will use row level lock. Otherwise, InnoDB will use table level lock.

Let's do some simple tests.

create table called innodb_lock_test as below.  alter table innodb_lock_test add index id(id);

mysql> create table innodb_lock_test(id int,name varchar(10)) engine=innodb;
Query OK, 0 rows affected (0.15 sec)
mysql> insert into innodb_lock_test values(1,'Trimurthy'),(2,'Anand'),(3,'Krishna'),(4,'Prasad');
Query OK, 4 rows affected (0.00 sec)
Records: 4 Duplicates: 0 Warnings: 0


Now Lets open 2 sessions.

SESSION #1 :

mysql> set autocommit=0;
Query OK, 0 rows affected (0.00 sec)

mysql> select * from innodb_lock_test where id =1;
+------+-----------+
| id   | name      |
+------+-----------+
|    1 | Trimurthy |
+------+-----------+
1 row in set (0.00 sec)

SESSION #2 :

mysql> set autocommit=0;
Query OK, 0 rows affected (0.00 sec)

mysql> select * from innodb_lock_test where id =2;
+------+-------+
| id   | name  |
+------+-------+
|    2 | Anand |
+------+-------+
1 row in set (0.00 sec)


SESSION #1 :

mysql> select * from innodb_lock_test where id =1 for update;
+------+-----------+
| id   | name      |
+------+-----------+
|    1 | Trimurthy |
+------+-----------+
1 row in set (0.00 sec)


SESSION #2 :
mysql> select * from innodb_lock_test where id =2 for update;
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction

This example show us InnoDB can only use table level locking if no index is available. In session 1, it looks like we only set a lock on one row (where id=1 for update). But we don't have index on id column, InnoDB is actually locking the whole table, so in session two, when we try to set a lock to a different row(where id=2 for update), we have to wait until the lock in session 1 is released.

Now, let's add index to our id column and repeat the same test.

mysql> alter table innodb_lock_test add index id(id);
Query OK, 0 rows affected (0.24 sec)
Records: 0  Duplicates: 0  Warnings: 0

SESSION #1 :

mysql> set autocommit=0;
Query OK, 0 rows affected (0.00 sec)

mysql> select * from innodb_lock_test where id =1;
+------+-----------+
| id   | name      |
+------+-----------+
|    1 | Trimurthy |
+------+-----------+
1 row in set (0.00 sec)

SESSION #2 :

mysql> set autocommit=0;
Query OK, 0 rows affected (0.00 sec)

mysql> select * from innodb_lock_test where id =2;
+------+-------+
| id   | name  |
+------+-------+
|    2 | Anand |
+------+-------+
1 row in set (0.00 sec)


SESSION #1 :

mysql> select * from innodb_lock_test where id =1 for update;
+------+-----------+
| id   | name      |
+------+-----------+
|    1 | Trimurthy |
+------+-----------+
1 row in set (0.00 sec)


SESSION #2 :

mysql> select * from innodb_lock_test where id =2 for update;
+------+-------+
| id   | name  |
+------+-------+
|    2 | Anand |
+------+-------+
1 row in set (0.00 sec)

If we observer session1 and session2 both the queries got executed(means both the queries got locks on respective rows) because of index.

CONCLUSION : InnoDB row level locks works only when your SQL statement is using index to query data.


MySQL Internals, MySQL HighAvailability, MySQL Performance Tuning, MySQL Query Optimization, MySQL performance, MySQL Fabric, MySQL HA, MySQL Installation, MySQL Upgrade, InnoDB Performance Tuning, InnoDB Buffer Pool Size, MySQL Performance Tuning, MySQL ClusterMySQL Latest News, News and Events, MySQL Customers

How to calculate InnoDB log files usage.

In this example, I would demonstrate how to check the amount of InnoDB log file space in use,
follow these steps ( checking current usage at peak times):
Examine INNODB MONITOR output (i.e. SHOW ENGINE INNODB STATUS\G)
and look at LOG section.

---
LOG
---
Log sequence number 5428 3708369329
Log flushed up to   5428 3708369329
Last checkpoint at  5428 834766592
0 pending log writes, 0 pending chkp writes


The innodb log file usage can be calculated by the following formula:

Last sequence number is ((5428 * 4 * 1024*1024*1024)+3708369329) = 23316790852017
Last checkpoint at is ((5428 * 4 * 1024*1024*1024)+834766592) = 23313917249280

So the actual innodb log file usuage is = 23316790852017 - 23313917249280 = 2873602737 = which is about 2740 MB.

innodb_log_file_size = 1048576000
innodb_log_files_in_group = 4

Total log files size = 1048576000 * 4 = 4000MB

The above calculations applicable only for MySQL versions < 5.5 version
and for MySQL version > 5.5 use the following calculations.


---
LOG
---
Log sequence number 2735016708
Log flushed up to   2734016690
Last checkpoint at  2380597012

Perfrom following calulation (formula) to know log space used. The values to use in calculation are "Log sequence number" and "Last checkpoint at".

mysql> SELECT (2735016708 - 2380597012)/1024/1024 "Space used in MB";
+------------------+
| Space used in MB |
+------------------+
|     338.00096130 |
+------------------+
1 row in set (0.01 sec)



MySQL Internals, MySQL HighAvailability, MySQL Performance Tuning, MySQL Query Optimization, MySQL performance, MySQL Fabric, MySQL HA, MySQL Installation, MySQL Upgrade, InnoDB Performance Tuning, InnoDB Buffer Pool Size, MySQL Performance Tuning, MySQL ClusterMySQL Latest News, News and Events, MySQL Customers 

InnoDB Transaction Isolation Levels and Concurrency

When multiple clients run transactions concurrently, three problems that may result are dirty reads, non-repeatable reads, and phantoms. These occur under the following circumstances:

A dirty read is a read by one transaction of uncommitted changes made by another. Suppose that transaction T1 modifies a row. If transaction T2 reads the row and sees the modification even though T1 has not committed it, that is a dirty read. One reason this is a problem is that if T1 rolls back, the change is undone but T2 does not know that.

A non-repeatable read occurs when a transaction performs the same retrieval twice but gets a different result each time. Suppose that T1 reads some rows and that T2 then changes some of those rows and commits the changes. If T1 sees the changes when it reads the rows again, it gets a different result; the initial read is non-repeatable. This is a problem because T1 does not get a consistent result from the same query.

A phantom is a row that appears where it was not visible before. Suppose that T1 and T2 begin, and T1 reads some rows. If T2 inserts a new row and T1 sees that row when it reads again, the row is a phantom.

InnoDB implements four isolation levels that control the visibility of changes made by one transaction to other concurrently executing transactions:

READ UNCOMMITTED : allows a transaction to see uncommitted changes made by other transactions. This isolation level allows dirty reads, non-repeatable reads, and phantoms to occur.

READ COMMITTED : allows a transaction to see changes made by other transactions only if they’ve been committed. Uncommitted changes remain invisible. This isolation level allows non-repeatable reads and phantoms to occur.

REPEATABLE READ : ensures that if a transaction issues the same SELECT twice, it gets the same result both times, regardless of committed or uncommitted changes made by other transactions. In other words, it gets a consistent result from different executions of the same query. In some database systems,  isolation level allows phantoms, such that if another transaction inserts new rows in the interval between the SELECT statements, the second SELECT will see them. This is not true for InnoDB, phantoms do not occur for the REPEATABLE READ level.

SERIALIZABLE : completely isolates the effects of one transaction from others. It is similar to REPEATABLE READ with the additional restriction that rows selected by one transaction cannot be changed by another until the first transaction finishes.

The essential difference between REPEATABLE READ and SERIALIZABLE is that with REPEATABLEREAD, one transaction cannot modify rows another has modified, whereas with SERIALIZABLE,one transaction cannot modify rows if another has merely even read them.



MySQL Internals, MySQL HighAvailability, MySQL Performance Tuning, MySQL Query Optimization, MySQL performance, MySQL Fabric, MySQL HA, MySQL Installation, MySQL Upgrade, InnoDB Performance Tuning, InnoDB Buffer Pool Size, MySQL Performance Tuning, MySQL ClusterMySQL Latest News, News and Events, MySQL Customers 

MySQL upgrade issues with SQL statements

The following errors encountered duting the validation of new MySQL versions including MySQL 5.5 and 5.6.

#1) As of MySQL5.1 there is no problem with "TRUNCATE TABLE" statement while truncating a parent table if its child table doesn't have any matching rows or if the child table is created using constraints with cascade delete or cascade update.

Since MySQL 5.5 "TRUNCATE TABLE" statement results with the following error while truncating a table, even though its child table doesn't have any matching rows or if the child table is created using constraints with cascade delete or cascade update.

ERROR 1451 (23000): Cannot delete or update a parent row: a foreign key constraint fails (`test`.`child`, CONSTRAINT `child_ibfk_1` FOREIGN KEY (`parent_id`) REFERENCES `parent` (`id`)).


Although TRUNCATE TABLE is similar to DELETE, it is classified as a DDL statement rather than a DML statement. It differs from DELETE in the following ways since MySQL 5.5 onwards :

Truncate operations drop and re-create the table, which is much faster than deleting rows one by one.

TRUNCATE TABLE does not cause ON DELETE triggers to fire, and it cannot be performed for InnoDB tables with parent-child foreign key relationships.

Any AUTO_INCREMENT value is reset to its start value. This is true even for MyISAM and InnoDB, which normally do not reuse sequence values.

SUMMARY : Please make sure that you are not using anywhere TRUNCATE TABLE as part of your code, and going forward use DELETE statement instead of TRUNCATE TABLE.

For more information please go through http://dev.mysql.com/doc/refman/5.6/en/truncate-table.html



#2) MySQL 5.1 is not strict to the data types and the associated values.

The following statement works fine in MySQL 5.1.

insert into testdatatype2 select * from testdatatype where status1=1;

The same statement results in the following error in MySQL 5.5 and 5.6.
ERROR 1292 (22007): Truncated incorrect DOUBLE value: 'OFF'

Before start our discussion first lets go through tables structures used.

mysql> show create table testdatatype\G
*************************** 1. row ***************************
       Table: testdatatype
Create Table: CREATE TABLE `testdatatype` (
  `id` int(11) DEFAULT NULL,
  `name` varchar(20) DEFAULT NULL,
  `status1` varchar(5) DEFAULT NULL,
  `status2` int(5) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8
1 row in set (0.00 sec)

mysql> show create table testdatatype2\G
*************************** 1. row ***************************
       Table: testdatatype2
Create Table: CREATE TABLE `testdatatype2` (
  `id` int(11) DEFAULT NULL,
  `name` varchar(20) DEFAULT NULL,
  `status1` varchar(5) DEFAULT NULL,
  `status2` int(5) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8
1 row in set (0.00 sec)

mysql> select * from testdatatype;
+------+-----------+---------+---------+
| id   | name      | status1 | status2 |
+------+-----------+---------+---------+
|    1 | trimurthy | OFF     |       2 |
|    2 | anandh    | 1       |       2 |
+------+-----------+---------+---------+
2 rows in set (0.00 sec)

mysql> select * from testdatatype2;
Empty set (0.00 sec)

Both tables structure is same, only table names are different, if we observe the table structure status1 column has been defined as varchar(5).
And the table is having both string and number data in status1 column.

In MySQL 5.1 version the following statement worked with out any errors and warnings.

mysql> insert into testdatatype2 select * from testdatatype where status1=1;
Query OK, 2 rows affected (0.00 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql> select * from testdatatype2;
+------+-----------+---------+---------+
| id   | name      | status1 | status2 |
+------+-----------+---------+---------+
|    1 | trimurthy | OFF     |       2 |
|    2 | anandh    | 1       |       2 |
+------+-----------+---------+---------+
2 rows in set (0.00 sec)

But when it comes to MySQL 5.5 and 5.6 the same insert statement results in the following error.

mysql> insert into testdatatype2 select * from testdatatype where status1=1;
ERROR 1292 (22007): Truncated incorrect DOUBLE value: 'OFF'

This is because since MySQL 5.5 onwards server is strict to the data types.

To resolve the above mentioned error use quotations in the where clause as follows, which is a SQL standard.

insert into testdatatype2 select * from testdatatype where status1='1';


#3 As of MySQL 5.1 and 5.5 there is no problem with index creation on large varchar data type columns, incase of 5.1 and 5.5 it will create index only on first 255 characters of a given column and it will result in warning stating that "Specified key was too long; max key length is 767 bytes", when it comes to MySQL 5.6 it will result with an error "ERROR 1071 (42000): Specified key was too long; max key length is 767 bytes".

The following is an example :

MySQL 5.1 :

mysql> create table compatibility_check ( id int, name varchar(50), description varchar(500), key key_desc (description));

Query OK, 0 rows affected, 1 warning (0.02 sec)

mysql> show warnings;

+---------+------+---------------------------------------------------------+
| Level   | Code | Message                                                 |
+---------+------+---------------------------------------------------------+
| Warning | 1071 | Specified key was too long; max key length is 767 bytes |
+---------+------+---------------------------------------------------------+
1 row in set (0.00 sec)

mysql> show create table compatibility_check;

CREATE TABLE `compatibility_check` (
  `id` int(11) DEFAULT NULL,
  `name` varchar(50) DEFAULT NULL,
  `description` varchar(500) DEFAULT NULL,
  KEY `key_desc` (`description`(255))
) ENGINE=InnoDB DEFAULT CHARSET=utf8;


MySQL 5.5 :

mysql> create table compatibility_check ( id int, name varchar(50), description varchar(500), key key_desc (description));

Query OK, 0 rows affected, 1 warning (0.03 sec)

mysql> show warnings;

+---------+------+---------------------------------------------------------+
| Level   | Code | Message                                                 |
+---------+------+---------------------------------------------------------+
| Warning | 1071 | Specified key was too long; max key length is 767 bytes |
+---------+------+---------------------------------------------------------+
1 row in set (0.00 sec)

mysql> show create table compatibility_check;

CREATE TABLE `compatibility_check` (
  `id` int(11) DEFAULT NULL,
  `name` varchar(50) DEFAULT NULL,
  `description` varchar(500) DEFAULT NULL,
  KEY `key_desc` (`description`(255))
) ENGINE=InnoDB DEFAULT CHARSET=utf8;


MySQL 5.6 :

mysql> create table compatibility_check ( id int, name varchar(50), description varchar(500), key key_desc (description));

ERROR 1071 (42000): Specified key was too long; max key length is 767 bytes

mysql> create table compatibility_check ( id int, name varchar(50), description varchar(500), key key_desc (description(100)));

Query OK, 0 rows affected (0.03 sec)


SUMMARY : Make sure that you are not using anywhere creation of index on big varchar columns as part of your code, and going forward use "KEY key_desc (description(255)" instead of "key key_desc (description)", if you have to create and index on varchar columns larger than 255 characters.



MySQL Internals, MySQL HighAvailability, MySQL Performance Tuning, MySQL Query Optimization, MySQL performance, MySQL Fabric, MySQL HA, MySQL Installation, MySQL Upgrade, InnoDB Performance Tuning, InnoDB Buffer Pool Size, MySQL Performance Tuning, MySQL ClusterMySQL Latest News, News and Events, MySQL Customers