Errata for the MySQL 5.0 Certification Study Guide

Exam Updates after the publication of the Study Guide

The MySQL 5.0 Certification Study Guide was written before the completion of the certification exams. At that time, it was expected that questions on MySQL Administrator and MySQL Query Browser would be part of the exam. For a number of technical reasons, questions on these two products do not appear on the final version of the exams.

Errata

• Contents at a Glance: The chapter "The mysql Client Program" starts on page 35, not 25.
• Acknowledgments: Delete one instance of "and initiate the".
• Page 8+9: The distribution of questions within the exams have changed since the publication of the book. Replace the two tables with the following:
  
  

  Table IN.1: Division of Questions on Exam Sections for the Developer Exams
  MySQL Developer I Exam		MySQL Developer II Exam
  Client/Server Concepts	5%	Joins	10%
  The mysql Client Program	10%	Subqueries	10%
  Connectors	5%	Views	15%
  Data Types	15%	Importing and Exporting Data	5%
  Identifiers	5%	User Variables	5%
  Databases	5%	Prepared Statements	5%
  Tables and Indexes	15%	Stored Routines	20%
  Querying for Data	15%	Triggers	5%
  SQL expressions	15%	Obtaining Database Metadata	10%
  Updating Data	10%	Debugging MySQL Applications	5%
  		Basic Optimizations	10%
  Table IN.2: Division of Questions on Exam Sections for the DBA Exams
  MySQL DBA I Exam		MySQL DBA II Exam
  MySQL Architecture	10%	Stored Procedures	5%
  Starting, Stopping and Configuring MySQL	20%	User Management	20%
  Client Programs for DBA Work	5%	Securing the Server	10%
  Character Set Support	5%	Upgrade-Related Security Issues	5%
  Locking	10%	Optimizing Queries	15%
  Storage Engines	20%	Optimizing Schemas	15%
  Table Maintenance	10%	Optimizing the Server	10%
  The INFORMATION_SCHEMA Database	5%	Interpreting Error Messages	5%
  Data Backup and Recovery Methods	15%	Optimizing the Environment	5%
  		Scaling MySQL	10%

• Page 10+11: In both figure captions, replace 'status bar says "select' with 'status bar indicates "Select'. Also for both captions, add a closing '"' last in the paragraph. In Figure IN,3, the last word should be "responses", not "answers".
• Page 10, last paragraph: Replace 'status line says' with 'status line indicates'.
• Page 15, last line: replace 'Country' with 'CountryCode'.
• Page 32, line 11: mysql> SET sql_mode='TRADITIONAL"; should read: mysql> SET sql_mode='TRADITIONAL'; (i.e. change double-quote to single-quote).
• Page 48: Last word on the page should be "them", not "then".
• Page 61: In the table at the bottom of the page, the lowest value for a signed INT is stated as "-2,147,683,648". It should be: "-2,147,483,648".
• Page 69, second paragraph: Padding is done with null bytes, not spaces. But BINARY columns are not stripped of null bytes. The length of a retrieved column BINARY(M) is always M.
• Page 72, third paragraph of section 5.5: Replace "and '00:00:00' for TIME) values." with "and '00:00:00' for TIME values)."
• Page 73: 6th paragraph (the first of the two starting with "MySQL represents time values...") should be deleted.
• Page 73, last paragraph: The last two instances of "TIME" should be replaced with "time", so the end of the paragraph reads: "...It's similar to a combination of DATE and TIME values, but the time part represents time of day rather than elapsed time and has a range limited to '00:00:00' to '23:59:59'. The date part of DATETIME columns has the same range as DATE columns; combined with the time part, this results in a DATETIME range from '1000-01-01 00:00:00' to '9999-12-31 23:59:59'.
• Page 88: The CREATE TABLE statement of the 2nd bulletpoint (line 13) should include the NOT NULL attribute: CREATE TABLE t (id INT NOT NULL AUTO_INCREMENT, PRIMARY KEY(id))". (MySQL will add this implicitly for any PRIMARY KEY or AUTO_INCREMENT declaration in the field specification).
• Page 89, last bullet point: Add "If the server is restarted, InnoDB will start incrementing from the high end of the sequence, ignoring AUTO_INCREMENT values that have been created and then deleted from the high end."
• Page 98, section 6.2: Table aliases are governed by the same rules as table identifiers.
• Page 114, first line: replace "you convert" with "you to convert".
• Page 122, section 8.6.1, first bullet point: add "A table may contain only one primary key."
• Page 217, 2nd bullet point of section 12.2.2: Add: "With USING(), only the column of the table mentioned first in the join will be part of the result set. The (redundant) column of the second table mentioned will not be included in the result."
• Page 232, Last paragraph and accompanying example ("Compare that query to the following one...". The meaning attributed to the SQL statement in this example is incorrect. The example should be ignored.
• Page 233. The discussion indicating the SOME is a synonym for ANY includes an example SQL statement. The meaning attributed to this statement is incorrect. The example should be ignored. The modified discussion after removing the example should read:
  The word SOME is an alias for ANY, and may be used anywhere that ANY is used. The SQL standard defines these two words with the same meaning to overcome a limitation in the English language, particularly for inequality comparisons. In the English language, we expect "not any" to mean "none at all." However, in SQL, <> ANY means "one or more do not match." To alleviate the confusion that might arise from the use of <> ANY, the SQL standard includes the SOME keyword as a synonym for ANY. Using the <> SOME construct makes it easier to understand the expected outcome of such SQL statements.
• Page 308: First paragraphs of section 19.2: Replace the text starting from "Each trigger for a given table must ..." to the end of the paragraph with "Each trigger within a database must have a different name."
• Page 311: Delete first bullet point ("You cannot use the CALL statement").
• Page 311, 2nd paragraph from bottom: Delete from "In MySQL, the syntax requires... " until the end of the paragraph.
• Page 328, third paragraph: Replace "For the DELETE statement" with "For the DROP statement".
• Page 337 and 338: All instances of "YEAR" should read "YEAR(d)"
• Page 340: Replace the first 3 paragraphs (up to and including "doesn't retrieve as many rows in the first place") with the following:

  In general, you should try to use a WHERE clause to restrict results so the server only retrieves the number of rows actually needed. Some uses of LIMIT, such as SELECT * FROM t LIMIT 10; will be fast. However, for result sets with millions of rows a statement such as SELECT * FROM ... LIMIT 2000000, 10; is not very efficient.

• Page 352, 6th line of 3rd paragraph: Replace "OBDC" with "ODBC".
• Page 368, last bullet point before section 24.4: Replace "the Perl DBI module" with "Perl".
• Page 404: replace "byacquiring" with "by acquiring".
• Page 411, first bullet point: Replace "begin" with "begun".
• Page 430: SHOW ENGINEINNODB STATUS should read SHOW ENGINE INNODB STATUS
• Page 432, last paragraph: Replace "stored" with "storage".
• Page 453, First paragraph of section 32.3.1: Replace ".MYD and .MYI files" with ".MYD, .MYI and .TRG files".
• Page 454, 5th paragraph (section "3"): Add new bulletpoint: "Any .TRG file(s) associated with the tables being backed up.
• Page 461, section 32.5: Delete first bullet point.
• Page 490, 6th line from the bottom: replace "FLUSH STATEMENT" with "FLUSH PRIVILEGES statement".
• Page 500, section 35.6: In the last line of the CREATE TABLE statement, replace COMMENT= with CONNECTION=. (Changed in MySQL 5.0.13).
• Page 543, third bullet point: delete ", which allows their contents to be read in parallel".
• Exercises, Client/Server Concepts: For the exercise that starts with "Suppose that you invoke mysql with the -h . option.", part of the answer is wrong. It states wrongly: "mysql -h . tries to establish a local connection using shared memory, or, in case this fails, a named pipe." It should say: "mysql -h . tries to establish a local connection using named pipes on Windows, if the server has named-pipe connections enabled. If named-pipe connections are not enabled, an error occurs."

If you believe you have found further errors in the MySQL Certification Study Guide, please send an e-mail to certification@mysql.com or use our Contact Form.