Errata

MySQL Cookbook

Errata for MySQL Cookbook, Third Edition

Submit your own errata for this product.

The errata list is a list of errors and their corrections that were found after the product was released.

The following errata were submitted by our customers and have not yet been approved or disproved by the author or editor. They solely represent the opinion of the customer.

Color Key: Serious technical mistake Minor technical mistake Language or formatting error Typo Question Note Update

Version Location Description Submitted by Date submitted
Safari
Safari

I am running the
<code>
SET @dt = '2014-11-23 09:00:00';
SELECT @dt AS Chicago,
CONVERT_TZ(@dt,'US/Central','Europe/Berlin') AS Berlin,
CONVERT_TZ(@dt,'US/Central','Europe/London') AS London,
CONVERT_TZ(@dt,'US/Central','America/Edmonton') AS Edmonton,
CONVERT_TZ(@dt,'US/Central','Australia/Brisbane') AS Brisbane;
</code>

but when I do I keep getting NULL for the 4 Values. I only get the date for Chicago.

James  Sep 15, 2015 
ePub Page tsdemo
final comment in tsdemo.sql (recipes/table directory)

# An update that changes no rows (doesn't change any TIMESTAMP columns)
=======================================================
The single quote (apostrophe) starts a quoted string, so the SQL commands below this comment will not be executed. The problem can be fixed by adding another apostrophe between n and t.

Gregory Sherman  Apr 18, 2018 
ePub Page 101
2nd and 3rd blocks of Perl

In both calls to map, the use of the defined or operator // would make the code more concise and, in my opinion, easier to read.
The statements could be revised as follows:

map { $ref->{$_} // "NULL";} keys (%{$ref});

@val = map { $_ // "NULL";} @val;


Gregory Sherman  Apr 11, 2018 
ePub Page 124
end of "Selecting Rows from the Beginning, End, or Middle of Query Results" section

SELECT SQL_CALC_FOUND_ROWS * FROM profile ORDER BY name LIMIT 4;
==========================================================
To agree with the text in the preceding and succeeding pargraphs, the command should end .with "LIMIT 3"

Gregory Sherman  Apr 16, 2018 
ePub Page 187
second code block of "Converting between date-and-time values and seconds"

mysql> set time_zone = '+00:00';
===========================
ERROR
command should be: set session time_zone = '+00:00';

Gregory Sherman  Apr 18, 2018 
ePub Page 243,246
SET sql_mode command and Grouping by Expression Results

When I ran the first mysql command under Grouping by Expression Results, it produced a "Non grouping field" error because I had earlier issued "SET sql_mode = 'ONLY_FULL_GROUP_BY';" which appears a few pages back. There's nothing in the text that tells the reader how to undo this, so I had to restart mysql to get the command to run properly.

Gregory Sherman  Apr 22, 2018 
ePub Page 275
exec_stmt procedure, calls and text

CREATE PROCEDURE exec_stmt(stmt_str TEXT)
BEGIN
SET @_stmt_str = stmt_str;
PREPARE stmt FROM @_stmt_str;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;
END;

The exec_stmt() routine enables the same statements to be executed much more simply

:CALL exec_stmt(CONCAT('CREATE TABLE ',@tbl_name,' (i INT)'));
CALL exec_stmt(CONCAT('INSERT INTO ',@tbl_name,' (i) VALUES(',@val,')'));

exec_stmt() uses an intermediary user-defined variable, @_exec_stmt,
=======================================================
The variable is called @_stmt_str in the procedure, conflicting with the explanation below.
Regardless of the variable's name, both CALLs fail when I run them under Maria DB 10.2:
ERROR 1064 (42000):
... near 'NULL' at line 1

Gregory Sherman  Apr 23, 2018 
ePub Page 277
drop_user procedure

[may be related to the problem reported on page 275]

I ran exec_stmt.sql from the recipes/routines directory, and it completed without errors, producing:
1
1
DATABASE()
cookbook

Running drop_user.sql, however, failed:
ERROR 1396 (HY000) ... Operation DROP USER failed ...


Gregory Sherman  Apr 23, 2018 
ePub Page 364
sub check_enum_value() and description above

The routine takes four arguments: a database handle, the table name and column name for the ENUM column, and the value to check. It returns true or false to indicate whether the value is legal:sub check_enum_value
{
my ($dbh, $db_name, $tbl_name, $col_name, $val) = @_;
==============================================
It takes five arguments and the second one is a database name.

Gregory Sherman  Apr 28, 2018 
ePub Page 460, 461
table definition and INSERT INTO calls

CREATE TABLE poll_vote
(
poll_id INT UNSIGNED NOT NULL AUTO_INCREMENT,
candidate_id INT UNSIGNED,
vote_count INT UNSIGNED,
PRIMARY KEY (poll_id, candidate_id)
);

For the first vote received for a given poll candidate, insert a new row with a vote count of 1.
* NOTE: false - see below *

For subsequent votes for that candidate, increment the vote count of the existing record.


INSERT INTO poll_vote (poll_id,candidate_id,vote_count) VALUES(14,3,7)
ON DUPLICATE KEY UPDATE vote_count = vote_count +1;
* NOTE: vote_count beoomes 7, not 1 *


INSERT INTO poll_vote (poll_id,candidate_id,vote_count) VALUES(14,3,5)
ON DUPLICATE KEY UPDATE vote_count = vote_count +1;
* NOTE: vote_count beoomes 8, so value in INSERT INTO oommand is meaningless in this case *

Gregory Sherman  May 02, 2018 
PDF Page 660
1st (continued) paragraph

The full context reads:

Many people answer this question by saying, “Don’t do it!” and some of the reasons are discussed in the following sidebar. Because it’s important to know how to work with binary data, this section does show how to store images in MySQL. Nevertheless, in recognition __that that__ may not always be the best thing to do, the section also shows how to store images in the file‐ system.

I would simply suggest reworking to continue referring to the act of storing images in MySQL as "it". While I do believe "that that may not always..." is technically correct English, I believe "that it may not always..." would be clearer to read as the act of storing was first introduced by the “Don’t do it!” remark.

x0xMaximus  Jan 19, 2017