15.8 Exercises
| Question 1: | Is the following statement about the InnoDB storage engine true or false?
The InnoDB storage engine keeps table data and indexes in a tablespace. | | Question 2: | Is the following statement about the InnoDB storage engine true or false?
The InnoDB storage engine keeps table definitions in .frm files. | | Question 3: | Is the following statement about the InnoDB storage engine true or false?
The InnoDB storage engine can use compressed tables. | | Question 4: | Is the following statement about the InnoDB storage engine true or false?
The InnoDB storage engine has its own error log. | | Question 5: | Is the following statement about the InnoDB storage engine true or false?
The InnoDB storage engine uses both its own log files and the MySQL binary log (if enabled). | | Question 6: | Is the following statement about the InnoDB storage engine true or false?
The InnoDB storage engine is multi-versioned, which means that different transactions use different versions of the data. | | Question 7: | Is the following statement about the InnoDB storage engine true or false?
The InnoDB storage engine is multi-versioned, which means that all versions of InnoDB can use the same datafiles. | | Question 8: | InnoDB tablespace files are stored in a format that is machine independent and can be copied from one machine to another as long as certain conditions are met for both machines. Which of the following conditions must be true for the InnoDB tablespace to be machine independent?
Both machines must run under the same operating system. You should create databases and tables using lowercase names. The operating systems on both machines must use the same line-ending sequence. This is why you can't copy an InnoDB tablespace from a Windows machine (where lines end with \r\n) to a Mac OS X machine (where lines end with \r). Both machines must use two's-complement integer arithmetic. Both machines must have processors from the same family, such as Intel 586. Both machines must use IEEE floating-point format, or else none of the InnoDB tables in the tablespace must contain any floating-point columns (FLOAT or DOUBLE).
| | Question 9: | Is the following statement about the InnoDB storage engine true or false?
Deadlock can occur with the InnoDB storage engine, unlike with the MyISAM storage engine. | | Question 10: | Is the following statement about the InnoDB storage engine true or false?
The InnoDB storage engine uses row-level locking, which provides better query concurrency than page-level or table-level locking and gives better performance in environments where there are many reads and writes at the same time. | | Question 11: | Is the following statement about the InnoDB storage engine true or false?
The SELECT ... FOR UPDATE SQL statement makes sense for a transactional storage engine like InnoDB, but not for a non-transactional storage engine like MyISAM. | | Question 12: | Is the following statement about the InnoDB storage engine true or false?
SELECT ... LIMIT is a MySQL extension to standard SQL that cannot be used with the InnoDB storage engine. | | Question 13: | What is the effect of setting the innodb_flush_log_at_trx_commit option to 1 or 0? | | Question 14: | Where would you set the innodb_flush_log_at_trx_commit option? What will you have to do after setting it to cause it to take effect? | | Question 15: | By default, an InnoDB tablespace is stored in a single regular file named ibdata1 in the MySQL data directory, but you can configure the tablespace to have more than one component by setting the innodb_data_file_path option in the [mysqld] section of a MySQL option file. What other kind of file can you use in a tablespace, and what would be the advantage of not using a regular file? | | Question 16: | When creating an InnoDB table, how do you control which tablespace file InnoDB will use for storing that table's contents? | | Question 17: | When specifying a tablespace file in a MySQL option file, you normally indicate a fixed size for it (for example, ibdata2:100M). What happens when all tablespace files are full? What could you do to make tablespace files dynamic? Give an example how you would do that. | | Question 18: | For an auto-extending tablespace file, how can you prevent it from expanding to use all available space on the filesystem where it is located? | | Question 19: | With InnoDB, you can create tables that exceed filesystem limits on maximum file size. Why is that so? Give an example. | | Question 20: | Can an InnoDB tablespace be distributed across different filesystems? | | Question 21: | Can you have a tablespace that uses regular files and raw partitions (device files) at the same time? | | Question 22: | How can you view status information about InnoDB? | | Question 23: | How can you check the amount of free space available in the InnoDB tablespace? | | Question 24: | Is the following statement about the InnoDB rollback mechanism true or false?
InnoDB uses information in its log files to perform rollbacks. | | Question 25: | Is the following comment about the InnoDB rollback mechanism true or false?
InnoDB uses a data structure called the rollback segment in the InnoDB tablespace to store transaction undo information. If your operations will require large transactions, you must ensure that the tablespace is large enough to store that information. | | Question 26: | Is the following statement about the InnoDB rollback mechanism true or false?
InnoDB uses the MySQL binary log in case it has to perform a rollback. | | Question 27: | Provided that the conditions for InnoDB binary portability are met, you can make a binary copy of the InnoDB tables stored on one machine and copy them to another machine. To do so, what files do you need to copy? | | Question 28: | Can you make a binary copy of the InnoDB tables stored on a machine while the server is running? | | Question 29: | What methods can you use to copy InnoDB tables besides making a binary copy? | | Question 30: | When the MySQL server or the server host crashes, what can you do to recover your InnoDB tables, other than restoring them from backups? | | Question 31: | Which programs or SQL statements can you use to check InnoDB tables? | | Question 32: | Which programs or SQL statements can you use to repair InnoDB tables? | | Question 33: | How would you define "multi-versioning" as used by the InnoDB storage engine? | | Question 34: | After the following transaction has been ended with ROLLBACK, what will the contents of the table t be? Why?
mysql> CREATE TABLE t (i INT) TYPE = InnoDB;
mysql> CREATE TABLE t2 (i INT) TYPE = InnoDB;
mysql> BEGIN WORK;
mysql> INSERT INTO t SET i = 1;
mysql> DROP TABLE t2;
mysql> ROLLBACK;
| | Question 35: | Consider the following session listing for one client:
mysql> BEGIN WORK;
mysql> SELECT * FROM trans;
Empty set
mysql> INSERT INTO trans VALUES (1),(2),(3);
Query OK, 3 rows affected
Records: 3 Duplicates: 0 Warnings: 0
Now, a second client issues the following statements:
mysql> SELECT * FROM trans;
How many rows will the second client see if both clients are running with an InnoDB isolation level of REPEATABLE READ? | | Question 36: | Suppose that CountryList is an InnoDB table. Consider the following session listing:
mysql> SELECT COUNT(*) FROM CountryList;
+----------+
| COUNT(*) |
+----------+
| 192 |
+----------+
mysql> BEGIN WORK;
mysql> DELETE FROM CountryList;
mysql> SELECT COUNT(*) FROM CountryList;
+----------+
| COUNT(*) |
+----------+
| 0 |
+----------+
mysql> ROLLBACK;
How many rows will the table have after the ROLLBACK statement has been issued? | | Question 37: | Consider the following InnoDB table and the session listing:
mysql> DESCRIBE CountryList;
+-----------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-----------+-------------+------+-----+---------+-------+
| Code | char(3) | | | | |
| Name | char(52) | | | | |
| IndepYear | smallint(6) | YES | | NULL | |
+-----------+-------------+------+-----+---------+-------+
mysql> SELECT * FROM CountryList;
Empty set (0.00 sec)
mysql> SET AUTOCOMMIT=0;
mysql> INSERT INTO CountryList VALUES('XXX','XLand',2003);
Query OK, 1 row affected (0.00 sec)
mysql> ROLLBACK;
What are the contents of CountryList after the ROLLBACK statement has been issued? | | Question 38: | Consider the following InnoDB table and the session listing:
mysql> DESCRIBE CountryList;
+-----------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-----------+-------------+------+-----+---------+-------+
| Code | char(3) | | | | |
| Name | char(52) | | | | |
| IndepYear | smallint(6) | YES | | NULL | |
+-----------+-------------+------+-----+---------+-------+
mysql> SELECT * FROM CountryList;
Empty set (0.01 sec)
mysql> SET AUTOCOMMIT=0;
mysql> INSERT INTO CountryList VALUES('XXX','XLand',2003);
Query OK, 1 row affected (0.00 sec)
mysql> BEGIN WORK;
mysql> INSERT INTO CountryList VALUES('YYY','YLand',2004);
Query OK, 1 row affected (0.00 sec)
mysql> ROLLBACK;
What are the contents of CountryList after the ROLLBACK statement has been issued? | | Question 39: | Consider the following InnoDB table and the session listing:
mysql> DESCRIBE CountryList;
+-----------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-----------+-------------+------+-----+---------+-------+
| Code | char(3) | | | | |
| Name | char(52) | | | | |
| IndepYear | smallint(6) | YES | | NULL | |
+-----------+-------------+------+-----+---------+-------+
mysql> SELECT * FROM CountryList;
Empty set (0.01 sec)
mysql> SET AUTOCOMMIT=0;
mysql> BEGIN WORK;
mysql> BEGIN WORK;
mysql> INSERT INTO CountryList VALUES('XXX','XLand',2003);
Query OK, 1 row affected (0.00 sec)
mysql> COMMIT;
mysql> INSERT INTO CountryList VALUES('YYY','YLand',2004);
Query OK, 1 row affected (0.00 sec)
mysql> ROLLBACK;
What are the contents of CountryList after the ROLLBACK statement has been issued? | | Question 40: | Consider the following InnoDB table and the session listing:
mysql> DESCRIBE CountryList;
+-----------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-----------+-------------+------+-----+---------+-------+
| Code | char(3) | | | | |
| Name | char(52) | | | | |
| IndepYear | smallint(6) | YES | | NULL | |
+-----------+-------------+------+-----+---------+-------+
mysql> SELECT * FROM CountryList;
Empty set (0.01 sec)
mysql> SET AUTOCOMMIT=0;
mysql> INSERT INTO CountryList VALUES('XXX','XLand',2003);
Query OK, 1 row affected (0.00 sec)
mysql> INSERT INTO CountryList VALUES('YYY','YLand',2004);
Query OK, 1 row affected (0.00 sec)
mysql> ROLLBACK;
What are the contents of CountryList after the ROLLBACK statement has been issued? | | Question 41: | Can you undo a ROLLBACK or a COMMIT? If so, how would you do that? | | Question 42: | Suppose that a client is in the middle of performing a transaction. How does the server handle the transaction under the following conditions:
The client loses the connection before ending the transaction. The client itself closes the connection before ending the transaction.
|
Answers to Exercises | | | Answer 1: | The statement is true. | | | | Answer 2: | The statement is true. | | | | Answer 3: | False. Compressed tables are a feature of the MyISAM storage engine, not of InnoDB. | | | | Answer 4: | False. The MySQL error log is used to store errors, but it is not InnoDB-specific. | | | | Answer 5: | True. InnoDB records transaction activity in the InnoDB log, and MySQL records statements for all table types in the binary log if they modify data. | | | | Answer 6: | The statement is true. | | | | Answer 7: | False. Multi-versioning means that different transactions use different versions of the data. | | | | Answer 8: | The conditions for a machine-independent InnoDB tablespace are similar to those the MyISAM storage engine:
Both machines must use two's-complement integer arithmetic. Both machines must use IEEE floating-point format, or else none of the InnoDB tables in the tablespace must contain any floating-point columns (FLOAT or DOUBLE).
In addition, you should create databases and tables using lowercase names.
The other conditions stated are not required for machine independence of the tablespace. | | | | Answer 9: | True. Deadlock can occur with InnoDB because it uses row-level locking and it might determine that additional locks are necessary during the course of query processing. The MyISAM storage engine uses table-level locking; this cannot lead to deadlock because the server can determine all necessary locks before executing a query. | | | | Answer 10: | True. When there are many reads and writes at the same time, row-level locking provides superior performance over table-level locking. | | | | Answer 11: | True. SELECT ... FOR UPDATE is used to select a set of rows that you also intend to update. It makes sense for InnoDB, which allows individual rows to be locked. It does not make sense for the MyISAM storage engine, which locks the entire table even if only some of its rows will be updated.
See section A.1.29, "SELECT." | | | | Answer 12: | False. SELECT ... LIMIT is a MySQL extension to standard SQL, but it works with all MySQL storage engines.
See section A.1.29, "SELECT." | | | | Answer 13: | With an innodb_flush_log_at_trx_commit setting of 1, InnoDB writes and flushes the log buffer to the log files after each commit, making the transaction changes permanent in the database. With a setting of 0, InnoDB writes and flushes the log buffer about once a second, but not after each commit. It is possible with a setting of 0 for about a second's worth of committed transactions to be lost if a crash occurs.
See section A.4, "Server System Variables." | | | | Answer 14: | The innodb_flush_log_at_trx_commit setting should be given in the [mysqld] section of the MySQL option file, or on the command line. Putting it in the option file is advisable because you don't have to remember it each time you start the server.
To cause the change to take effect, restart the server.
See section A.4, "Server System Variables." | | | | Answer 15: | You can specify a raw partition (a device file) for an InnoDB tablespace. This will avoid a level of overhead normally incurred when using regular files in a filesystem. Raw partitions also are not bound by file size limits; InnoDB can use the entire partition.
See section A.4, "Server System Variables." | | | | Answer 16: | You cannot control that. InnoDB is free to store table contents anywhere in the tablespace that it finds available space. It will use any or all files in the tablespace, if necessary. | | | | Answer 17: | When there is no more free space in a fixed size tablespace, InnoDB rolls back the next statement that tries to add data. (The application is expected to detect this error and perform a ROLLBACK operation to roll back the entire transaction.) To avoid running out of space, you can add the autoextend attribute to the specification of the last file in the tablespace. For example, to create a tablespace from two 100MB files in the data directory and make the second one auto-extending, you could put something like this in your C:\my.cnf file:
[mysqld]
innodb_data_file_path = ibdata1:100M;ibdata2:100M:autoextend
| | | | Answer 18: | To limit the size to which InnoDB allows an auto-extending file to grow, add a max specifier after autoextend. To allow ibdata2 to grow to a maximum of 500MB, configure the tablespace like this:
[mysqld]
innodb_data_file_path = ibdata1:100M;ibdata2:100M:autoextend:max:500M
You can also limit the maximum size of a tablespace file indirectly if your operating system provides a quota system. This involves procedures that are not covered in this study guide. | | | | Answer 19: | You can create an InnoDB tablespace from multiple files if you want. Any regular file that is part of the tablespace is subject to the size limitations of the filesystem, but InnoDB will store tables using more than one file if necessary. For example, if your filesystem imposes a limit of 2GB as the maximum size of a file, you can create the InnoDB tablespace from multiple files that are 2GB in size. To store a table that has 5GB of data, InnoDB could then use three such files, thus exceeding the filesystem's limitation. Another way to overcome file size limits is to use raw partitions that InnoDB can access directly (that is, not through the filesystem). The size of a raw partition that InnoDB can handle in a tablespace is constrained only by InnoDB's internal size limit (four billion database pages, where each page is 16KB by default). | | | | Answer 20: | Yes. For example, you could have one tablespace file on a ReiserFS filesystem partition, and another tablespace file on an ext3 filesystem partition. | | | | Answer 21: | Yes. You can use a mix of regular files and raw partitions in the same tablespace. | | | | Answer 22: | The SHOW INNODB STATUS statement displays information about the status of InnoDB. For example, you can issue the statement using the mysql client program:
mysql> SHOW INNODB STATUS\G
*************************** 1. row ***************************
Status:
=====================================
030322 20:54:14 INNODB MONITOR OUTPUT
=====================================
Per second averages calculated from the last 7 seconds
----------
SEMAPHORES
----------
OS WAIT ARRAY INFO: reservation count 11, signal count 11
Mutex spin waits 9, rounds 160, OS waits 1
RW-shared spins 18, OS waits 9; RW-excl spins 1, OS waits 1
------------
TRANSACTIONS
------------
Trx id counter 0 244754
Purge done for trx's n:o < 0 244747 undo n:o < 0 0
Total number of lock structs in row lock hash table 0
LIST OF TRANSACTIONS FOR EACH SESSION:
---TRANSACTION 0 244753, not started, OS thread id 1500
MySQL thread id 2, query id 881 localhost 127.0.0.1 superuser
SHOW INNODB STATUS
--------
FILE I/O
--------
I/O thread 0 state: wait Windows aio
I/O thread 1 state: wait Windows aio
I/O thread 2 state: wait Windows aio
I/O thread 3 state: wait Windows aio
Pending normal aio reads: 0, aio writes: 0,
...
(The output has been shortened.)
See section A.1.40, "SHOW INNODB STATUS." | | | | Answer 23: | Issue a SHOW TABLE STATUS statement that includes output for at least one InnoDB table. The approximate amount of available space in the tablespace is displayed in the Comment field for every InnoDB table shown in the output. For example:
mysql> SHOW TABLE STATUS LIKE 't%';
+----------+--------+------------+- -+------------------------+
| Name | Type | Row_format | ... | Comment |
+----------+--------+------------+- -+------------------------+
| t | InnoDB | Fixed | ... | InnoDB free: 179200 kB |
| test | InnoDB | Fixed | ... | InnoDB free: 179200 kB |
+----------+--------+------------+- -+------------------------+
See section A.1.43, "SHOW TABLE STATUS." | | | | Answer 24: | False. InnoDB uses the rollback segment of the tablespace to perform rollbacks. | | | | Answer 25: | True. The rollback segment contains undo information that is used to roll back transactions. | | | | Answer 26: | False. With respect to InnoDB, the binary log is used to record only committed transactions. Transactions that roll back never appear in the binary log. | | | | Answer 27: | To make a binary copy of the InnoDB tablespace, you need to copy:
All tablespace files All InnoDB log files The .frm file for each of your InnoDB tables The InnoDB tablespace and log configuration information stored in your MySQL option file (that is, the settings for innodb_data_home_dir, innodb_data_file_path, and perhaps other InnoDB options)
| | | | Answer 28: | You cannot make a binary copy of InnoDB tables while the server is running. Unlike the options available when using MyISAM tables (lock the tables, flush them to disk, and then copy them), you must tell the server to stop before making a copy of InnoDB files. This is necessary to ensure that InnoDB has completed any pending transactions before the copy is made. | | | | Answer 29: | You can also copy InnoDB tables with the following programs and SQL statements:
See section A.1.29, "SELECT." | | | | Answer 30: | Normally, you don't have to do anything after a crash except restart the server. InnoDB will recognize that it was not shut down correctly and perform an automatic recovery. | | | | Answer 31: | To check an InnoDB table, you can use either of the following:
See section A.1.5, "CHECK TABLE." | | | | Answer 32: | To repair an InnoDB table, you cannot use REPAIR TABLE or the myisamchk program (which, as its name indicates, is for MyISAM tables only). The method to use is to dump the InnoDB table and then reload it.
See section A.1.24, "REPAIR TABLE." | | | | Answer 33: | In InnoDB, multi-versioning means that as transactions modify rows, InnoDB maintains isolation between them by maintaining multiple versions of the rows, and makes available to each transaction the appropriate version of the rows that it should see. | | | | Answer 34: | The table t will contain the row added by the INSERT statement:
mysql> SELECT i FROM t;
+------+
| i |
+------+
| 1 |
+------+
The reason for this is that DROP TABLE is one of the statements that causes an implicit commit of preceding uncommitted statements in the transaction.
See section A.1.28, "ROLLBACK." | | | | Answer 35: | The second client will see no rows because the first client has not issued a COMMIT. If the first client does issue a COMMIT, the second client then will see the three newly inserted rows.
See sections A.1.4, "BEGIN," A.1.6, "COMMIT," and A.1.29, "SELECT." | | | | Answer 36: | The table will have its original 192 rows. ROLLBACK rolls back the DELETE statement. | | | | Answer 37: | CountryList will contain no rows. Setting AUTOCOMMIT to 0 causes MySQL to treat the following SQL statements as a transaction. That transaction can be (and was) rolled back. To commit it instead of rolling it back, you would either have to issue COMMIT explicitly or issue another SQL statement that would commit the transaction implicitly.
See sections A.1.4, "BEGIN," A.1.6, "COMMIT," A.1.28, "ROLLBACK," and A.1.29, "SELECT." | | | | Answer 38: | The table CountryList will contain the following row:
mysql> SELECT * FROM CountryList;
+------+-------+-----------+
| Code | Name | IndepYear |
+------+-------+-----------+
| XXX | XLand | 2003 |
+------+-------+-----------+
The BEGIN WORK statement causes an implicit commit of the preceding uncommitted INSERT statement, thus the first row is inserted. The second INSERT, however, is within a transaction that is rolled back.
See sections A.1.4, "BEGIN," and A.1.29, "SELECT." | | | | Answer 39: | The table CountryList will contain the following row:
mysql> SELECT * FROM CountryList;
+------+-------+-----------+
| Code | Name | IndepYear |
+------+-------+-----------+
| XXX | XLand | 2003 |
+------+-------+-----------+
The explanation is not that the session has "nested" transactions. The second BEGIN WORK statement actually commits the first transaction implicitly and starts another transaction. COMMIT ends that transaction. Because the session is not running in autocommit mode, however, the next statement also begins a new transaction and it is unnecessary to issue an explicit BEGIN WORK. As a result, ROLLBACK rolls back the second INSERT statement.
See sections A.1.4, "BEGIN," A.1.6, "COMMIT," A.1.28, "ROLLBACK," and A.1.29, "SELECT." | | | | Answer 40: | The table CountryList will contain no rows. In non-autocommit mode, everything is regarded as a transaction that is committed only when a COMMIT statement is issued. Thus, all statements are rolled back with ROLLBACK.
See sections A.1.4, "BEGIN," A.1.28, "ROLLBACK," and A.1.29, "SELECT." | | | | Answer 41: | A ROLLBACK cannot be undone. You must repeat your transaction. A COMMIT also cannot be undone.
See sections A.1.6, "COMMIT," and A.1.28, "ROLLBACK." | | | | Answer 42: | In either case (whether the connection closes abnormally or normally), the server treats connection termination as if the client had issued a ROLLBACK statement, so the transaction is rolled back. |
|
