Study Guide Errors Not Reported

[bruno rossi]

1) Chapter 1 - 1.2.3 - Using Option Files

"On Windows, programs look for option files in the following order: my.ini and my.cnf
in the Windows directory (for example, the C:\Windows or C:\WinNT directory), and then
C:\my.ini and C:\my.cnf"

The order in which the client programs options files are read is not correct. On windows the programs look for option files in the following order:
C:\my.ini, C:\my.cnf, C:\Windows\my.ini, C:\Windows\my.cnf, C:\Programs\MYSQL Server 5.0\my.ini, C:\Programs\MYSQL Server 5.0\my.cnf
as mentioned by mysql client program typing the the following command:
shell> mysql --help;

2) Preface - Interpreting DESCRIBE Output

"The Key indicator may be empty or contain one of three non-empty values:

An empty Key value indicates that the column in question either isn't indexed or is
indexed only as a secondary column in a multiple-column, non-unique index. For
purposes of the exam, you should assume that if Key is empty, it's because the column
is not indexed at all.

If the Key value is the keyword PRI (as in the output shown for the ID column), this
indicates that the column is a PRIMARY KEY or is one of the columns in a multiplecolumn
PRIMARY KEY.

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.

If the Key value is the keyword MUL, this indicates that the column is the first column
of a non-unique index or a unique-valued index that can contain NULL values."

The Reference Manual for MYSQL5.0 refers the following:

"Before MySQL 5.0.11, if the column allows NULL values, the Key value can be MUL even when a 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."

For the purpose of the exam i have to consider describe outputs in the manner of pre 5.0.11 release or as of 5.0.11 release?
Look at the fact that if i have to consider outputs in the manner of pre 5.0.11 release i have to refer to a not production release (mysql 5.0 was delivered for production in 5.0.15 version, if i'm not wrong)...

Besides the Reference Manual for MYSQL5.0 isn't complete in the definition of 5.0.11 and later versions.
Running mysql on windows xp the behavior, previosly mentioned for mysql 5.0.11 and later, works only if i have a unique column index.
if i have a double column index defined as UNIQUE and one of the column can take NULL values the DESCRIBE outputs show me the first column of the index as MUL (this behavior is in fact equal to pre 5.0.11 versions).

For Example:

Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 8
Server version: 5.0.45-community-nt MySQL Community Edition (GPL)

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql (root@localhost) [DB:prova] > create table metro(
id int(10),
name VARCHAR(255) NOT NULL,
surname VARCHAR(255) NULL,
fax VARCHAR(255) NULL,
phone VARCHAR(255) NOT NULL,
constraint a unique index bigname(name, surname),
constraint b unique index ringrong(fax),
constraint c unique key ringring(phone));
Query OK, 0 rows affected (0.07 sec)

mysql (root@localhost) [DB:prova] > describe metro;
*************************** 1. row ***************************
Field: id
Type: int(10)
Null: YES
Key:
Default: NULL
Extra:
*************************** 2. row ***************************
Field: name
Type: varchar(255)
Null: NO
Key: MUL
Default:
Extra:
*************************** 3. row ***************************
Field: surname
Type: varchar(255)
Null: YES
Key:
Default: NULL
Extra:
*************************** 4. row ***************************
Field: fax
Type: varchar(255)
Null: YES
Key: UNI
Default: NULL
Extra:
*************************** 5. row ***************************
Field: phone
Type: varchar(255)
Null: NO
Key: PRI
Default:
Extra:
5 rows in set (0.00 sec)

3) Data Type - 5.7 Using the AUTO_INCREMENT Column Attribute

"There may be only one AUTO_INCREMENT column per table, the column must be indexed, and the column must be defined as NOT NULL."

I propose to change "must be defined as NOT NULL" in "should be defined as NOT NULL" because the server doesn't return any error if
I define an AUTO_INCREMENT column as NULL (if I define more than an AUTO_INCREMENT column per table or the column isn't indexed the server returns an error).
Defining a column as NOT NULL, the server seems doing a silent conversion and it changes the column in NOT NULL.

Check it out the following rows:

Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 5.0.45-community-nt MySQL Community Edition (GPL)

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> set session sql_mode = traditional;
Query OK, 0 rows affected (0.02 sec)

mysql> select @@session.sql_mode;
+-------------------------------------------------------------------------------------------------------------------------------+
| @@session.sql_mode |
+-------------------------------------------------------------------------------------------------------------------------------+
| STRICT_TRANS_TABLES,STRICT_ALL_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,TRADITIONAL,NO_AUTO_CREATE_USER |
+-------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

mysql> use test;
Database changed
mysql> CREATE TABLE nonull (a INT NULL AUTO_INCREMENT, INDEX myi(a));
Query OK, 0 rows affected (0.06 sec)

mysql> describe nonull;
+-------+---------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-------+---------+------+-----+---------+----------------+
| a | int(11) | NO | MUL | NULL | auto_increment |
+-------+---------+------+-----+---------+----------------+
1 row in set (0.00 sec)

mysql> set session sql_mode = '';
Query OK, 0 rows affected (0.00 sec)

mysql> select @@session.sql_mode;
+--------------------+
| @@session.sql_mode |
+--------------------+
| |
+--------------------+
1 row in set (0.01 sec)

mysql> DROP TABLE nonull;
Query OK, 0 rows affected (0.05 sec)

mysql> CREATE TABLE nonull (a INT NULL AUTO_INCREMENT, INDEX myi(a));
Query OK, 0 rows affected (0.06 sec)

mysql> describe nonull;
+-------+---------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-------+---------+------+-----+---------+----------------+
| a | int(11) | NO | MUL | NULL | auto_increment |
+-------+---------+------+-----+---------+----------------+
1 row in set (0.00 sec)



Edited 4 time(s). Last edit at 09/06/2007 07:16AM by bruno rossi.