Errata for Learning MySQL

Change: Our previous two examples would be written as: $ chmod 644 myfile.txt and: $ chmod 777 mydir to: Using this notation our previous three examples would be written as: $ chmod 644 myfile.txt $ chmod 600 myfile.txt $ chmod 777 mydir

"filename" should be "myfile.txt" in examples.

Version is now 5.0.26

Version is now 5.0.26

The installer package is in the StuffIt Expander (.sitx) format. If you get a screen of garbled text in your browser when trying to download it, press the "back" button to see the download link - for example, http://easynews.dl.sourceforge.net/sourceforge/xampp/xampp-macosx-0.3.sitx. Hold down the Ctrl key and click on the link. From the menu that appears, select the entry that says Download Linked File (for Safari), Save Link As (for Firefox), or Download Link to Disk (for Internet Explorer). Once the StuffIt archive is downloaded, double-click on it to extract the installation package, and then double-click on the installation package to start the XAMPP installation program. When the decompression program finishes, you should find the installation program saved in the same directory as the downloaded file, or on your Desktop. This installation program has a name like xampp-macosx-0. 5.pkg. Running this and accepting the default settings will install XAMPP to the /Applications/xampp/ directory, with the MySQL datafiles located in the /Applications/xampp/xamppfiles/var/mysql directory. Should be: This installer package is in the form of a disk image file (.dmg). The Safari web browser will automatically mount this disk image and start the installer. If you're using a different browser, you'll need to double-click on the downloaded file to mount it; you can then drag the XAMPP folder in this disk image to your Applications folder. This install XAMPP to the /Applications/XAMPP/ directory, with the MySQL datafiles located in the /Applications/XAMPP/xamppfiles/var/mysql directory. The XAMPP on Mac OS X examples in this book were written for older versions of XAMPP that installed to the /Applications/xampp/ directory; please be prepared to replace references to the old (lowercase xampp) path with the new one (uppercase XAMPP).

The ellipsis (...) symbols indicate where we've left out some of the program output. Should be: The ellipsis (...) symbols indicate where we've left out some of the program output. You may need to run mysql_secure_installation as the system root user (see the instructions earlier in this Chapter in "Following the Instructions in This Book"): # bin/mysql_secure_installation On Mac OS X, you may also need to create a symbolic link to the mysql command-line client program by typing: # ln -s /usr/local/mysql/bin/mysql /opt/local/bin/mysql

In the third line of code there shouldn't be quotes in the user input.

Windows /mnt/c/Program Files/xampp/apache/conf/httpd.conf for an XAMPP installation Should be: Windows C:\Program Files\xampp\apache\conf\httpd.conf for an XAMPP installation

You can download and install the DBI module, the MySQL driver, and the CGI module for the XAMPP Perl installation by typing these commands in turn: $ sudo /Applications/xampp/xamppfiles/bin/perl -MCPAN -e 'install DBI;' $ sudo /Applications/xampp/xamppfiles/bin/perl -MCPAN -e 'install DBD::mysql;' $ sudo /Applications/xampp/xamppfiles/bin/perl -MCPAN -e 'install CGI;' Should be: You'll can get various required configuration and header files by downloading the XAMPP "Developer package". The current version is xampp-macosx-0.7.4-dev.tar.gz; open this archive and copy the contents of the xamppfiles directory to /Applications/xampp/xamppfiles. One way to do this is: $ cd /Applications/xampp $ sudo tar zxvf ~my_user_name/xampp-macosx-0.7.4-dev.tar.gz You can download and install the CGI and DBI modules by typing: $ sudo /Applications/xampp/xamppfiles/bin/perl -MCPAN -e 'install DBI;' $ sudo /Applications/xampp/xamppfiles/bin/perl -MCPAN -e 'install CGI;' To install the MySQL driver (DBD::mysql), follow these steps: $ sudo /Applications/xampp/xamppfiles/bin/cpan cpan> o conf makepl_arg --mysql_config=/Applications/xampp/xamppfiles/bin/mysql_config cpan> install DBD::mysql cpan> quit

Updated image for Figure 4-4. http://tahaghoghi.com/LearningMySQL/Downloads/Files/UpdatedFigures/Figure.4-4.png

Updated image for Figure 4-5: http://tahaghoghi.com/LearningMySQL/Downloads/Files/UpdatedFigures/Figure.4-5.png

Updated image for Figure 4-13: http://tahaghoghi.com/LearningMySQL/Downloads/Files/UpdatedFigures/Figure.4-13.png

Updated image for Figure 4-15: http://tahaghoghi.com/LearningMySQL/Downloads/Files/UpdatedFigures/Figure.4-15.png

mysql> SELECT * FROM track WHERE -> track_name LIKE "R__ %"; Should be: mysql> SELECT * FROM track WHERE track_name LIKE "R__ %";

mysql> SELECT * FROM album WHERE album_id > 2 -> AND (NOT album_id) = 4 OR album_id = 6; ... mysql> SELECT * FROM album WHERE (NOT album_id) = 4; ... mysql> SELECT * FROM album WHERE (NOT album_id) != 4;

mysql> SELECT * FROM album WHERE album_id > 2 -> AND (NOT album_id) = 4 OR album_id = 6; ... mysql> SELECT * FROM album WHERE (NOT album_id) = 4; ... mysql> SELECT * FROM album WHERE (NOT album_id) != 4;

In the last sentence before "Joining Two Tables" section, reverse 10 and 5.

Note from the Author or Editor:
instead of writing LIMIT 5,10, you can write LIMIT 5 OFFSET 10. Should be: instead of writing LIMIT 5,10, you can write LIMIT 10 OFFSET 5.

| time | decimal(3,2) | YES | | NULL | | +----------------------------------------------------------+ Should be: | time | decimal(5,2) | YES | | NULL | | +----------------------------------------------------------+

| time | decimal(3,2) | YES | | NULL | | +----------------------------------------------------------+ Should be: | time | decimal(5,2) | YES | | NULL | | +----------------------------------------------------------+

| | `time` decimal(3,2) default NULL, | Should be: | | `time` decimal(5,2) default NULL, |

It should be clarified that the table that is created at the top of page 206 does not have any values stored in it, so the forthcoming queries of pages 206-207 will not work. This may puzzle beginners.

Note from the Author or Editor:
You can use the names index for fast searching Should be: First, let's insert data for three fictional customers: mysql> INSERT INTO customer VALUES -> (1,'Lucy','Elizabeth','Williams'), -> (2,'Rose','Isabella','Williams'), -> (3,'Sanaa','Sadat','Tahaghoghi'); Query OK, 3 rows affected (0.00 sec) Records: 3 Duplicates: 0 Warnings: 0 You can use the names index for fast searching

This error includes book's accompanying code. The book code excerpt has "time TIME" and says that this is preferred to the alternate DECIMAL type. However, the code has "time DECIMAL(5,2)" in file "data/music.sql". Elsewhere in the book it's clear from table outputs that the decimal type was used; errata on book website has changes this to DECIMAL(3,2). However, page 214 describes TIME as the preferred type. There should be consistency between the type used in the code, the table outputs, and the discussion on this page.

Note from the Author or Editor:
DECIMAL(5,2) is the choice used for the book; the errata actually correct to use this, not DECIMAL(3,2).

mysql> SELECT CONCAT(artist_name, " recorded ", album_name) AS recording -> FROM artist INNER JOIN album USING (artist_id) -> ORDER BY CONCAT(artist_name, " recorded ", album_name); Should be: mysql> SELECT CONCAT(artist_name, " recorded ", album_name) -> FROM artist INNER JOIN album USING (artist_id) -> ORDER BY CONCAT(artist_name, " recorded ", album_name);

mysql> SELECT * FROM track GROUP BY artist_id; +----------+----------------------+-----------+----------+----------+ | track_id | track_name | artist_id | album_id | time | +----------+----------------------+-----------+----------+----------+ | 0 | Elegia | 1 | 1 | 00:04:56 | | 0 | Do You Love Me? | 2 | 1 | 00:05:57 | | 0 | In A Silent Way | 3 | 1 | 00:01:49 | | 0 | Rocks Off | 4 | 1 | 00:04:32 | | 0 | Breaking Into Heaven | 5 | 1 | 00:11:22 | | 0 | Spinning Around | 6 | 1 | 00:03:28 | +----------+----------------------+-----------+----------+----------+ 6 rows in set (0.12 sec) Only the artist_id here is meaningful--the rest just contain the first-listed entry from each group. To illustrate this point, "Elegia" is the first track that would be listed for artist_id 1 if we hadn't performed any grouping: mysql> SELECT * FROM track WHERE artist_id=1; +----------+----------------------+-----------+----------+----------+ | track_id | track_name | artist_id | album_id | time | +----------+----------------------+-----------+----------+----------+ | 0 | Elegia | 1 | 1 | 00:04:56 | | 1 | In A Lonely Place | 1 | 1 | 00:06:16 | | 2 | Procession | 1 | 1 | 00:04:28 | ... +----------+----------------------+-----------+----------+----------+ 86 rows in set (0.01 sec) Should be: mysql> SELECT * FROM track GROUP BY artist_id; +----------+----------------------+-----------+----------+-------+ | track_id | track_name | artist_id | album_id | time | +----------+----------------------+-----------+----------+-------+ | 0 | Elegia | 1 | 1 | 4.93 | | 0 | Do You Love Me? | 2 | 1 | 5.95 | | 0 | In A Silent Way | 3 | 1 | 1.81 | | 0 | Rocks Off | 4 | 1 | 4.54 | | 0 | Breaking Into Heaven | 5 | 1 | 11.37 | | 0 | Spinning Around | 6 | 1 | 3.46 | +----------+----------------------+-----------+----------+-------+ 6 rows in set (0.00 sec) Only the artist_id here is meaningful--the rest just contain the first-listed entry from each group. To illustrate this point, "Elegia" is the first track that would be listed for artist_id 1 if we hadn't performed any grouping: mysql> SELECT * FROM track WHERE artist_id=1; +----------+-----------------------+-----------+----------+------+ | track_id | track_name | artist_id | album_id | time | +----------+-----------------------+-----------+----------+------+ | 0 | Elegia | 1 | 1 | 4.93 | | 1 | In A Lonely Place | 1 | 1 | 6.26 | | 2 | Procession | 1 | 1 | 4.47 | ... +----------+-----------------------+-----------+----------+------+ 86 rows in set (0.00 sec)

COUNT(*) should be COUNT(played) mysql> SELECT artist_name, album_name, COUNT(*) FROM -> artist INNER JOIN album USING (artist_id) -> INNER JOIN track USING (artist_id, album_id) -> LEFT JOIN played USING (artist_id, album_id, track_id) -> GROUP BY album.artist_id, album.album_id; Should be: mysql> SELECT artist_name, album_name, COUNT(played) FROM -> artist INNER JOIN album USING (artist_id) -> INNER JOIN track USING (artist_id, album_id) -> LEFT JOIN played USING (artist_id, album_id, track_id) -> GROUP BY album.artist_id, album.album_id;

(Boolean OR), the ANY keyword returns values Should be: (Boolean OR), the ALL keyword returns values

The SQL query example at top of page should be better chosen. While technically correct the chosen example is redundant and should be replaced with something more meaningful. In this case it makes no sense to use the FROM clause, since the parenthetical statement gives the same result. In other words, running: mysql> SELECT producer_name, months FROM -> (SELECT producer_name, years*12 AS months FROM producer) AS prod; is equivalent to running: SELECT producer_name, years*12 AS months FROM producer

yazd.invy.com Should be: yazd.invyhome.com

...of access the user partmusic has for the database ResearchPeople. should be: ...of access the user partmusic has for the database music.

...of access the user partmusic has for the database ResearchPeople. should be: ...of access the user partmusic has for the database music.

INSERT INTO `track` VALUES (0,'Do You Love Me?',2,1,'00:05:57'), (1,'Nobody\'s Baby Now',2,1,'00:03:52'),(2,'Loverman',2,1,'00:06:22'), (3,'Jangling Jack',2,1,'00:02:47'),(4,'Red Right Hand',2,1,'00:06:11'), (5,'I Let Love In',2,1,'00:04:15'),(6,'Thirsty Dog',2,1,'00:03:49'), ... UNLOCK TABLES; Should be: INSERT INTO `track` VALUES (0,'Do You Love Me?',2,1,'5.95'), (1,'Nobody\'s Baby Now',2,1,'3.87'),(2,'Loverman',2,1,'6.37'), (3,'Jangling Jack',2,1,'2.78'),(4,'Red Right Hand',2,1,'6.18'), (5,'I Let Love In',2,1,'4.25'),(6,'Thirsty Dog',2,1,'3.81'), ... UNLOCK TABLES;

Doesn't work on Linux Ubuntu: `date +"%Y.%m.%d.%H.%M"`.MySQL_Backup.sql.gz Needs to be: `/bin/date +\%Y.\%m.\%d.\%H.\%M`.MySQL_Backup.sql.gz

Note from the Author or Editor:
45 4 * * sun /usr/local/mysql/bin/mysqldump \ --user=root \ --password=the_mysql_root_password \ database_to_dump \ | gzip --best --to-stdout \ > dump_directory/`date +"%Y.%m.%d.%H.%M"`.MySQL_Backup.sql.gz Should be changed to: 45 4 * * sun /usr/local/mysql/bin/mysqldump user=root --password=the_mysql_root_password database_to_dump | gzip --best --to-stdout > /tmp/`/bin/date +"\%Y.\%m.\%d.\%H.\%M"`.MySQL_Backup.sql.gz

In the Run tab, type in the full command above. Should be: In the Run tab, type in the full command below.

change: while($row = @ mysql_fetch_array($result)) to: while($row = @ mysql_fetch_array($result, MYSQL_ASSOC)) (add the missing MYSQL_ASSOC) as is shown in the full code on p. 406

There's an unencoded < in this line (&lt; is showing, when it should be <). for ($x=0;$x&lt;$count;$x++) Should be: for ($x=0;$x<$count;$x++)

There's an extra ampersand in this line. for($x=0; $x&<$len; $x++) Should be: for($x=0; $x<$len; $x++)

“About the Authors”: Change “England” to “Britain” and “between civilizations” to “among civilizations.”

"About the Authors": Change "England" to "Britain" and "between civilizations" to "among civilizations."

There's an extra closing bracket, which should be removed. { // The user wants to add a new item to their shopping list. ... } } Should be: { // The user wants to add a new item to their shopping list. ... }

// Try to unreserve the gift with the matching username and gift ID $query = "UPDATE gifts SET username = NULL WHERE gift_id = {$gift_id}". "AND username={$_SESSION['username']}"; Should be: // Try to unreserve the gift with the matching username and gift ID $query = "UPDATE gifts SET username = NULL WHERE gift_id = {$gift_id}". " AND username='{$_SESSION['username']}'";

The line: while(my $val=$sth->fetchrow_hashref()) Should read: while(my $val=$ref->fetchrow_hashref()) ^^^^

Note from the Author or Editor:
while(my $val=$sth->fetchrow_hashref()) { printf ("%-5s %-128s\n", $ref->{artist_id}, $ref->{artist_name}); } near the top of the page should be corrected to: while(my $ref=$sth->fetchrow_hashref()) { printf ("%-5s %-128s\n", $ref->{artist_id}, $ref->{artist_name}); }

In the while statement in Example 17-4, it should be $Total += $ref -> [1]

Note from the Author or Editor:
page 549, Example 17.3; the line: $Total+=$ref->[0]; should be: $Total+=$ref->[1];

for($matchedrows=0;($row = @ mysqli_fetch_array($result));$matchedrows++); Should be: for($matchedrows=0;($tmprow = @ mysqli_fetch_array($result));$matchedrows++) $row=$tmprow;