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."

Additional Errata

• Not a 100% mistake but something worth to point out i think: The introduction of the book deals with things you should know before taking an exam and dedicates a special paragraph - on page 12-13 - about interpreting the DESCRIBE output, since it is essential for the exam.
  On page 13 it is stated that:
  

  If the KEY value is the keyword UNI, this indicates that the column is the first column of a unique-valued index that cannot contain NULL values.

  However it fails to mention that (and i quote the manual here)
  

  Before MySQL 5.0.11, if the column allows NULL values, the Key value can be MUL even when a single-column UNIQUE index is used. The rationale was that multiple rows in a UNIQUE index can hold a NULL value if the column is not declared NOT NULL. As of MySQL 5.0.11, the display is UNI rather than MUL regardless of whether the column allows NULL; you can see from the Null field whether or not the column can contain NULL.

  Since this section is so important it is already addressed in the Introduction i think this important information should not be left out. For the exam i think you must assume you're dealing with mysql version >= 5.0.11.
• Page 78 (2nd paragraph) it says : 'Define the column that should hold the updated time with DEFAULT CURRENT TIMESTAMP'.
  I think DEFAULT should be replaced by ON UPDATE (which is how the query below is defined)
• Page 88, 2nd bullet: 'A positive value can be inserted explicitly into an AUTO_INCREMENT column if the value isn't already present in the column.' And also on Page 89, 2nd bullet: 'When you reach the upper limit of an AUTO_INCREMENT column, an attempt to generate the next sequence value results in a duplicate-key error.'
  Both statements are wrong, because it's possible (even if it is uncommon) to define an AUTO_INCREMENT column with a normal INDEX (so not PRIMARY or UNIQUE) and in this case you can insert duplicate values and the duplicate error won't happen. So, the behaviour mentioned in the book isn't behaviour that can be attributed to the fact that a column is defined as AUTO_INCREMENT but it is behaviour caused when an column has a PRIMARY or UNIQUE index.
• Page 186, 1st bullet. (Not so much a mistake but certainly something worth to point out): 'For positive exact values, ROUND() rounds up the next integer if the fractional part is .5 or greater, and down to the next integer otherwise.
  The book claims to be a certification study guide for mysql 5.0 and in mysql 5.0 this isn't entirely true. I quote the manual here: "Before MySQL 5.0.3, the behavior of ROUND() when the argument is halfway between two integers depends on the C library implementation. Different implementations round to the nearest even number, always up, always down, or always toward zero."
  So, in mysql 5.0.1 for example, the example with ROUND(28.5) on a linux machine would result in 28 and not 29.
• Page 233, As the official errata list (above) mentions: the meaning attributed to the example is wrong.
  A quick check will show why it is false: Just delete all the entries of countries outside the european continent where spanish is spoken (from the CountryLanguage table) - thereby making the European continent the only one where Spanish is spoken - and the query will still return an empty result.
  
  Still on Page 233, another false conclusion is drawn however, which the errata list fails to mention.
  

  "In other words, the statement is really saying 'return all the countries where there are _some_ people that do not speak Spanish'"

  This is also not true, which can be quickly verified by executing this query:

  
  DELETE FROM CountryLanguage
  WHERE CountryCode IN ('AND','FRA','ESP','SWE')
  AND Language != 'Spanish'

  and then running the SELECT query from the book again. Andorra, France, Spain and Sweden now only have Spanish speakers (so, there not _some_ people that do not speak Spanish - all of them speak spanish now), but Andorra, France, Spain and Sweden will still show up in the result.
• Page 351, last bullet. The first sentence states that Client Programs communicate with the server by sending requests to it over a network connection.
  While this is true most of the time, there are other methods too (for local connections), like shared-memory
• Page 418, 1st bullet. Add CREATE TABLE to the list of statements that cause an implicit commit. I've tested this, so i'm quite sure it should've been in the list.
• Page 439. This page lists some statements for table maintenance and indicates for which tables it works. The list of supported tables is incomplete in all cases though.

  Statement	Missing Table	Version	Possible message types	Reference
  CHECK TABLE	ARCHIVE	As of MySQL 5.0.16	status, error, info, or warning	mysql manual
  	CSV	As of MySQL 5.1.9		mysql manual
  OPTIMIZE TABLE	ARCHIVE	As of MySQL 5.0.16	status, error, info, note, or warning	mysql manual
  ANALYZE TABLE	BDB	Supported until mysql 5.1. After that support for the whole engine was discontinued	status, error, info, note, or warning	mysql manual
  REPAIR TABLE	ARCHIVE	Since always	status, error, info, note, or warning	mysql manual
  	CSV	As of MySQL 5.1.9		mysql manual

• Page 457, 32.4.1. Add "Output files are written in text format and are portable".
  In think this should be added, since it is mentioned explicitly about mysqldump (32.4.2, page 458) and the same is true for SELECT INTO OUTFILE
• Page 457, 32.4.2. This chapter fails to mention that mysqldump doesn't backup your stored routines in version prior to 5.1.2. After reading 32.5 (page 461) it seems that when you perform these actions together you will have a complete backup, but - as i said - this is not true in versions < 5.1.2; here you should manually back up your stored routines as well. A possibility is to make a backup of the mysql.proc table.