Learning MySQL - Updates and Errata

Here's a list of corrections and improvements that we and some of our readers have suggested after the book went into its first print run:

You can also visit the O'Reilly errata page for Learning MySQL.

PageLocation (third of page)Current textCorrected text
xi Lower the staff is the staff are
xv Upper http://www.learningmysql.com http://tahaghoghi.com/LearningMySQL
xv Middle Ttahaghoghi Tahaghoghi
xvi Upper http://www.learningmysql.com/feedback http://tahaghoghi.com/LearningMySQL/feedback
8 Upper won't spent won't spend
8 Upper http://www.learningmysql.com http://tahaghoghi.com/LearningMySQL
10 Middle how to separately install and configure Apache, PHP, and Perl on your system. how to separately install and configure Apache, PHP, and Perl to work together on your system
16 Lower Our previous two examples would be written as:
$ chmod 644 myfile.txt
and:
$ chmod 777 mydir
 Using this notation our previous three examples would be written as:
$ chmod 644 myfile.txt
$ chmod 600 myfile.txt
$ chmod 777 mydir
19 Middle $ pico filename $ pico myfile.txt
19 Lower $ open -a TextEdit filename $ open -a TextEdit myfile.txt
23 Lower this program's dialog looks like. this program's dialog box looks like.
26 Lower be called something like MySQL-server-5.0.22-0.i386.rpm and MySQL-client-5.0.22-0.i386.rpm be called something like MySQL-server-5.0.22-0.i386.rpm and MySQL-client-5.0.22-0.i386.rpm. Packages with higher CPU numbers, such as i586 or i686, are better tuned for newer machines, but won't work on older machines.
58 Upper (Figure 2-14) C:\mysql-4.1.14-win32> C:\mysql-5.0.22-win32>
61 Upper The XAMPP installation has PHP configured, with The XAMPP installation may have PHP configured, with
61 Middle Local, using an noninstallation gzipped Local, using a non-installation gzipped
67 Lower 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. 		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).
69 Lower you may wonder whether you it's you may wonder whether it's
71 Middle MySQL server newer than 4.1.0 MySQL server from 4.1.0 onwards
73 Middle Under Linux, change to your MySQL base directory and type:
$scripts/mysql_fix_privilege_tables \
--user=root \
--password=the_mysql_root_password 		Under Linux, change to your MySQL base directory and type:
$scripts/mysql_fix_privilege_tables \
--user=root \
--password=the_mysql_root_password

On some Linux installations, mysql_fix_privilege_tables is in the MySQL bin directory; if the above command doesn't work, try this:
$bin/mysql_fix_privilege_tables \
--user=root \
--password=the_mysql_root_password
74 Lower The ellipsis (...) symbols indicate where we've left out some of the program output. 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
75 Upper For example, you might type:
# export http_proxy=http://adam:[email protected]:8080
# export ftp_proxy=http://adam:[email protected]:8080 		For example, you might type:
# export http_proxy=http://adam:[email protected]:8080
# export ftp_proxy=http://adam:[email protected]:8080
79 Middle Hold down Ctrl-C to exit the more program. Press Ctrl-C to exit the more program.
80 Lower You can do this by specifying the username from the command line:
$ bin/mysqladmin --user=root shutdown 		You can do this by specifying the root username and password from the command line:
$ bin/mysqladmin --user=root --password=the-root-password shutdown
83 Lower or the apachectl command described later or the apachectl or apache2ctl commands described later
84 Lower Windows
/mnt/c/Program Files/xampp/apache/conf/httpd.conf for an XAMPP installation 		Windows
C:\Program Files\xampp\apache\conf\httpd.conf for an XAMPP installation
85 Upper On Linux systems, it's increasingly common to find servers configured in a modular way, with a main configuration file that reads in other files, typically under the directory /etc/httpd/modules.d. It's increasingly common to find servers configured in a modular way, with a main configuration file that reads in other files, for example under the directory /etc/httpd/modules.d on a Linux system, or in the apache\conf\extra directory under the XAMPP install directory.
85 Middle You can generally start an installed Apache server by using the command:

# apachectl start

If this fails because the command isn't found, use the find command to try and locate the apachectl script file:

# find / -type f -name apachectl

If it's reported as being in, say 		Apache web server installations usually include a control script called apachectl that you can use to start or stop the server.  On newer installations this is sometimes called apache2ctl; if the examples below don't work for you, try replacing apachectl with apache2ctl or rcapache2.

You can generally start an installed Apache server by using the command:

# apachectl start

If you get an error message saying the apachectl file can't be found, try locating it yourself.  On a Linux or Mac OS X system use:

# find / -type f -name apachectl

while on a Windows system, use the built-in search.

If the apachectl file is reported as being in, say
87 Lower If you're not planning to write nontrivial Perl scripts If you're not planning to write complex Perl scripts
91 Upper 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;' 		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
97 Upper There are two databases here; You may see different results on your MySQL server. There are two databases here;
99 Lower by typing rebuild by typing rehash
100 Upper You can get information on individual topics You may see more or less help content depending on the help files that have been installed with your server. You can get information on individual topics
104 Upper from having to type use the_database_name from having to type USE the_database_name
109 Lower for each of his courses: for each of their courses:
110 Middle John Paul Bloggs has failed the Computing Mathematics course once with 45 percent, John Paul Bloggs has failed the Computing Mathematics course once with 43 percent,
116 Upper (Figure 4-4)

Please use this updated image for Figure 4-4.
118 Upper (Figure 4-5)

Please use this updated image for Figure 4-5.
122 Lower The only strong entity in the database is artist, which has an artist_id attribute that uniquely identifies it. Each album entity is uniquely identified by its album_id combined with the artist_id of the corresponding artist entity. A track entity is similarly uniquely identified by its track_id combined with the related album_id and artist_id attributes. The played entity is uniquely identified by a combination of its played time, and the related track_id, album_id, and artist_id attributes. The only strong entity in the database is Artist, which has an artist_id attribute that uniquely identifies it. Each Album entity is uniquely identified by its album_id combined with the artist_id of the corresponding Artist entity. A Track entity is similarly uniquely identified by its track_id combined with the related album_id and artist_id attributes. The Played entity is uniquely identified by a combination of its played time, and the related track_id, album_id, and artist_id attributes.
126 Upper (Figure 4-13)

Please use this updated image for Figure 4-13.
127 Lower and Melbourne-Christchurch trips. and Melbourne-Christchurch legs.
128 Middle because it's the not of this table, because it's a key not of this table,
131 Upper

Please use this updated image for Figure 4-15.
145 Middle Most strings used with LIKE contain a percentage character (%) Most strings used with LIKE contain the percentage character (%)
145 Lower mysql> SELECT * FROM track WHERE -> track_name LIKE "R__ %"; mysql> SELECT * FROM track WHERE track_name LIKE "R__ %";
149 Upper mysql> SELECT * FROM album WHERE album_id > 2
    -> AND NOT album_id = 4 OR album_id = 6; 		mysql> SELECT * FROM album WHERE album_id > 2
    -> AND (NOT album_id) = 4 OR album_id = 6;
149 Middle mysql> SELECT * FROM album WHERE NOT album_id = 4; mysql> SELECT * FROM album WHERE (NOT album_id) = 4;
149 Middle mysql> SELECT * FROM album WHERE NOT album_id != 4; mysql> SELECT * FROM album WHERE (NOT album_id) != 4;
154 Middle mysql> SELECT track_name FROM track WHERE track_name < binary 'b'; mysql> SELECT track_name FROM track WHERE track_name < BINARY 'b';
156 Lower instead of writing LIMIT 5,10, you can write LIMIT 5 OFFSET 10. instead of writing LIMIT 5,10, you can write LIMIT 10 OFFSET 5.
163 Lower You'll learn more about the auto_increment shortcut You'll learn more about the AUTO_INCREMENT shortcut
164 Lower 
| time       | decimal(3,2) | YES  |     | NULL    |       |
+----------------------------------------------------------+
 
| time       | decimal(5,2) | YES  |     | NULL    |       |
+----------------------------------------------------------+
165 Lower one whose primary key matches that of for which the primary key matches that of
168 Middle single-table deletes here, and discuss multitable deletes single-table deletes here, and discuss multi-table deletes
170 Middle We discuss indexes in detail in “Keys and Indexes” in Chapter 4. We discuss indexes in detail in “Keys and Indexes” in Chapter 6.
175 Lower 
| time       | decimal(3,2) | YES  |     | NULL    |       |
+----------------------------------------------------------+
 
| time       | decimal(5,2) | YES  |     | NULL    |       |
+----------------------------------------------------------+
176 Upper 
|       |   `time` decimal(3,2) default NULL,                 |
 
|       |   `time` decimal(5,2) default NULL,                 |
182 Upper Here's how you drop it temporarily: Here's how you drop it:
182 Lower we've added two: artist_id SMALLINT(5) NOT NULL default 0 and artist_name CHAR(128) default NULL. we've added two: artist_id SMALLINT(5) NOT NULL DEFAULT 0 and artist_name CHAR(128) DEFAULT NULL.
183 Upper The name field is the table name, and it has the same limitations The name field is the column name, and it has the same limitations
183 Lower avoid characters than require you to remember avoid characters that require you to remember
187 Middle it'll instead be removed (dropped) it'll be removed (dropped)
191 Lower command to reports the details of the warning command to report the details of the warning
197 Upper mysql> SELECT * from show_padding; mysql> SELECT * FROM show_padding;
197 Upper mysql> SELECT * from show_padding WHERE mystring LIKE '% '; mysql> SELECT * FROM show_padding WHERE mystring LIKE '% ';
203 Middle SET('value1'[,'value2'[, ...]] SET('value1'[,'value2'[, ...]])
205 Lower -> );


Query OK, 0 rows affected (0.06 sec) 		-> );
Query OK, 0 rows affected (0.06 sec)
206 Middle You can use the names index for fast searching 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
213 Middle despite the file being typically larger despite the files being typically larger
213 Middle We haven't added a NOT NULL clause to the artist_name. Instead we'll assume that whatever application we build will do the checking for us. The fewer the constraints and conditions that are built into the database, the faster it is for MySQL to work with. We haven't added a NOT NULL clause to the artist_name, and have instead assumed that whatever application we build will do the checking for us. In general, the fewer the constraints and conditions that are built into the database, the faster it is for MySQL to work with. However, MySQL now optimizes for NOT NULL columns, so it is better to declare NOT NULL where the data will never be NULL. See the “Data Size” section of the MySQL manual for details.
214 Upper CREATE TABLE track (
track_id SMALLINT(3) NOT NULL DEFAULT 0,
track_name CHAR(128) DEFAULT NULL,
artist_id SMALLINT(5) NOT NULL DEFAULT 0,
album_id SMALLINT(4) NOT NULL DEFAULT 0,
time TIME DEFAULT NULL,
PRIMARY KEY (artist_id,album_id,track_id)
);

The reasoning behind the choices for the first four columns is the same as for the other tables. The time column stores the duration of each track, and we’ve chosen to use the TIME type to store this. Using the TIME typein preference to a numeric type such as DECIMALmakes it easy to do math such as summing values to find the running time for an album. It also gives you flexibility in formats for the time data, as discussed previously. Despite this, you’ll see that in music. sql we use the format HH:MM:SS because we prefer to keep SQL queries readable and unambiguous.

CREATE TABLE track (
track_id SMALLINT(3) NOT NULL DEFAULT 0,
track_name CHAR(128) DEFAULT NULL,
artist_id SMALLINT(5) NOT NULL DEFAULT 0,
album_id SMALLINT(4) NOT NULL DEFAULT 0,
time DECIMAL(5,2) DEFAULT NULL,
PRIMARY KEY (artist_id,album_id,track_id)
);
225 Upper 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); 		mysql> SELECT CONCAT(artist_name, " recorded ", album_name)
    -> FROM artist INNER JOIN album USING (artist_id)
    -> ORDER BY CONCAT(artist_name, " recorded ", album_name);
233 Middle 
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)

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)
234 Lower to use it find to use it to find
248 Upper 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;
 		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;
248 Lower you can use use rollRIGHT JOIN you can use RIGHT JOIN
252 Upper (In “User Variables,” later in this chapter, we'll show how you can use variables to avoid having to type in the value in the second query.) In “User Variables,” later in this chapter, we'll show how you can use variables to avoid having to type in the value in the second query.
254 Lower Similarly, <> ANY or != ANY has the alias NOT IN. Using IN, the previous example can be rewritten: Using IN, the previous example can be rewritten:
255 Upper Let's try an example using NOT IN. We'll find all the engineers who aren't producers: mysql> SELECT engineer_name FROM engineer WHERE
    -> engineer_name NOT IN
    -> (SELECT producer_name FROM producer);
+---------------+
| engineer_name |
+---------------+
| Eddie Kramer  |
| Jeff Jarratt  |
| Ed Stasium    |
+---------------+
3 rows in set (0.25 sec)
255 Lower The ALL keyword has a few tricks and traps: We can use the alias NOT IN in place of <> ANY or != ANY. Let’s find all the engineers who aren't producers:

mysql> SELECT engineer_name FROM engineer WHERE
    -> engineer_name NOT IN
    -> (SELECT producer_name FROM producer);
+---------------+
| engineer_name |
+---------------+
| Eddie Kramer  |
| Jeff Jarratt  |
| Ed Stasium    |
+---------------+
3 rows in set (0.25 sec)

The ALL keyword has a few tricks and traps:
255 Lower (Boolean OR), the ANY keyword returns values (Boolean OR), the ALL keyword returns values
262 Middle The outer query is straightforward: it just returns the producer_name and the month value created through the subquery. The outer query here is very simple and just picks up the producer_name and the month value created through the subquery.
284 Lower (last line) c:\artists_and_albums C:\artists_and_albums.csv
311 Middle yazd.invy.com yazd.invyhome.com
320 Upper, line 3 the database ResearchPeople. the database music.
349 Middle mysql> set password for 'root'@'localhost'=password('the_new_mysql_root_password'); mysql> SET PASSWORD FOR 'root'@'localhost'=PASSWORD('the_new_mysql_root_password');
356 Lower 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; 		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;
358 Lower (penultimate line) set-charset set-charset
363 typing crontab -l typing crontab -l (the “l” stands for “list”)
363 In the Run tab, type in the full command above. In the Run tab, type in the full command below.
390 Upper $ mysqladmin --user=root --password=the_mysql_root_password status $ mysqladmin --user=root --password=the_mysql_root_password status
391 Upper connection (see how the new process number (26535) is different?): connection (see how the new process number — 26535 — is different?):
395 Upper (first line) MySQL's most common use is in wePHPMyb database applications. MySQL's most common use is in web database applications.
409 Upper while($row = @ mysql_fetch_array($result)) while($row = @ mysql_fetch_array($result, MYSQL_ASSOC))
436 Middle for ($x=0;$x&lt;$count;$x++) for ($x=0;$x<$count;$x++)
456 Lower for($x=0; $x&<$len; $x++) for($x=0; $x<$len; $x++)
463 Upper // Connect to the MySQL DBMS and use the wedding database - credentials are
// in the file db.php 		// Connect to the MySQL DBMS and use the music database - credentials are
// in the file db.php
502 Lower 
	{
		// The user wants to add a new item to their shopping list.
		...

		}
	}
				
	{
		// The user wants to add a new item to their shopping list.
		...
	}
506 Lower (last line) // 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']}"; 		// 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']}'";
514 Middle print "\nThe temperature difference is: ".
    $TemperatureToday-$TemperatureYesterday. "\n"; 		print "\nThe temperature difference is: ".
    ($TemperatureToday-$TemperatureYesterday). "\n";
542 Upper while(my $val=$sth->fetchrow_hashref())
{
    printf ("%-5s %-128sn", $ref->{artist_id}, $ref->{artist_name});
}
 		while(my $ref=$sth->fetchrow_hashref())
{
    printf ("%-5s %-128sn", $ref->{artist_id}, $ref->{artist_name});
}
549 Middle (Example 17.3) $Total+=$ref->[0]; $Total+=$ref->[1];
579 Upper for($matchedrows=0;($row = @ mysqli_fetch_array($result));$matchedrows++); for($matchedrows=0;($tmprow = @ mysqli_fetch_array($result));$matchedrows++) $row=$tmprow;