MacLochlainns Weblog

While I posted how to install Toad for MySQL Freeware five years ago, I’m always surprised how few people know about it there and consistently updated and improved. You can download Toad for MySQL Freeware or Toad Freeware for Oracle, SQL Server, Sybase, or IBM DB2 at this web site.

You can also download Toad Data Modeler Freeware Edition. Just two notes, while Toad for Oracle Freeware is an MSI file, Toad for MySQL Freeware is a zip file and limited to only a Windows install.

It does remind me that I need to pay close attention to the deprecation of mysqli functions with each release. These are actually the largest change since PHP 5.0.

Shantanu asked a follow-up question on my Cleanup a MySQL Schema post from last month. He wanted to know if there was a way to capture foreign keys before removing them. The answer is yes, but how you do it depends on whether the primary key is based on a surrogate key using an auto incrementing sequence of a natural key using descriptive columns.

You can capture foreign keys with a simple query when they’re determined by a single column value. However, this script creates ALTER statements that will fail when a table holds a multiple column foreign key value. The SELECT statement would look like this when capturing all foreign key values in a MySQL Server:

1
2
3
4
5
6
7
8
9
10
11

SELECT   CONCAT('ALTER TABLE',' ',tc.table_schema,'.',tc.table_name,' '
               ,'ADD CONSTRAINT',' fk_',tc.constraint_name,' '
               ,'FOREIGN KEY (',kcu.column_name,')',' '
               ,'REFERENCES',' ',kcu.referenced_table_schema,'.',kcu.referenced_table_name,' ' 
               ,'(',kcu.referenced_column_name,');') AS script
FROM     information_schema.table_constraints tc JOIN information_schema.key_column_usage kcu
ON       tc.constraint_name = kcu.constraint_name
AND      tc.constraint_schema = kcu.constraint_schema
WHERE    tc.constraint_type = 'foreign key'
ORDER BY tc.TABLE_NAME
,        kcu.column_name;

In MySQL 8 forward, you must make a case sensitive comparison, like:

9

WHERE    tc.constraint_type = 'FOREIGN KEY'

You would add a line in the WHERE clause to restrict it to a schema and a second line to restrict it to a table within a schema, like this:

AND      tc.table_schema = 'your_mysql_database'
AND      tc.table_name = 'your_table_name'

Unfortunately, when the primary and foreign keys involve two or more columns you require a procedure and function. The function because you need to read two cursors, and the NOT FOUND can’t be nested in the current deployment of MySQL’s SQL/PSM stored programs. In this example the storedForeignKeys procedure finds the table’s foreign key constraints, and the columnList function adds the column detail. The command_list table stores the commands to restore foreign key constraints.

The command_list table that stores the values is:

CREATE TABLE command_list
( command_list_id  INT UNSIGNED PRIMARY KEY AUTO_INCREMENT
, sql_command      VARCHAR(6)    NOT NULL
, sql_object       VARCHAR(10)   NOT NULL
, sql_constraint   VARCHAR(11)
, sql_statement    VARCHAR(768)  NOT NULL);

This is the storedForeignKeys procedure:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75

CREATE PROCEDURE storeForeignKeys
( pv_schema_name  VARCHAR(64)
, pv_table_name   VARCHAR(64))
BEGIN
 
  /* Declare local variables. */
  DECLARE lv_schema_name              VARCHAR(64);
  DECLARE lv_table_name               VARCHAR(64);
  DECLARE lv_constraint_name          VARCHAR(64);
  DECLARE sql_stmt                    VARCHAR(1024);
 
  /* Declare control variable for handler. */
  DECLARE fetched    INT DEFAULT 0;
 
  /* Declare local cursor for foreign key table, it uses null replacement
     because the procedure supports null parameters. When you use null 
     parameters, you get all foreign key values. */
  DECLARE foreign_key_table CURSOR FOR
    SELECT   tc.table_schema
    ,        tc.table_name
    ,        tc.constraint_name
    FROM     information_schema.table_constraints tc
    WHERE    tc.table_schema = IFNULL(lv_schema_name, tc.table_schema)
    AND      tc.table_name = IFNULL(lv_table_name, tc.table_name)
    AND      tc.constraint_type = 'FOREIGN KEY'
    ORDER BY tc.table_name;
 
  /* Declare a not found record handler to close a cursor loop. */
  DECLARE CONTINUE HANDLER FOR NOT FOUND SET fetched = 1;
 
  /* Assign parameter values to local variables. */
  SET lv_schema_name := pv_schema_name;
  SET lv_table_name := pv_table_name;
 
  /* Open a local cursor. */  
  OPEN foreign_key_table;
  cursor_foreign_key_table: LOOP
 
    /* Fetch a row into the local variables. */
    FETCH foreign_key_table
    INTO  lv_schema_name
    ,     lv_table_name
    ,     lv_constraint_name;
 
    /* Catch handler for no more rows found from the fetch operation. */
    IF fetched = 1 THEN LEAVE cursor_foreign_key_table; END IF;
 
    /* The nested calls to the columnList function returns the list of columns
       in the foreign key. Surrogate primary to foreign keys can be resolved 
       with a simply query but natural primary to foreign key relationships
       require the list of columns involved in the primary and foreign key.
       The columnList function returns the list of foreign key columns in 
       the dependent table and the list of referenced columns (or the primary
       key columns) in the independent table. */
    SET sql_stmt := CONCAT('ALTER TABLE ',' ',lv_schema_name,'.',lv_table_name,' '
                          ,'ADD CONSTRAINT ',lv_constraint_name,' '
                          ,'FOREIGN KEY (',columnList(lv_schema_name,lv_table_name,lv_constraint_name));
 
    /* Record the SQL statements. */
    INSERT INTO command_list
    ( sql_command
    , sql_object
    , sql_constraint
    , sql_statement )
    VALUES
    ('ALTER'
    ,'TABLE'
    ,'FOREIGN KEY'
    , sql_stmt );
 
  END LOOP cursor_foreign_key_table;
  CLOSE foreign_key_table;  
 
END;
$$

This is the columnList function:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83

CREATE FUNCTION columnList
( pv_schema_name      VARCHAR(64)
, pv_table_name       VARCHAR(64)
, pv_constraint_name  VARCHAR(64)) RETURNS VARCHAR(512)
BEGIN
 
  /* Declare local variables. */
  DECLARE lv_schema_name              VARCHAR(64);
  DECLARE lv_table_name               VARCHAR(64);
  DECLARE lv_constraint_name          VARCHAR(64);
  DECLARE lv_column_count             INT UNSIGNED;
  DECLARE lv_column_name              VARCHAR(64);
  DECLARE lv_column_list              VARCHAR(512);
  DECLARE lv_column_ref_list          VARCHAR(64);
  DECLARE lv_referenced_table_schema  VARCHAR(64);
  DECLARE lv_referenced_table_name    VARCHAR(64);
  DECLARE lv_referenced_column_name   VARCHAR(64);
  DECLARE lv_return_string            VARCHAR(768);
 
  /* Declare control variable for handler. */
  DECLARE fetched    INT DEFAULT 0;
 
  /* Declare local cursor for foreign key column. */
  DECLARE foreign_key_column CURSOR FOR
    SELECT   kcu.column_name
    ,        kcu.referenced_table_schema
    ,        kcu.referenced_table_name
    ,        kcu.referenced_column_name
    FROM     information_schema.key_column_usage kcu
    WHERE    kcu.referenced_table_schema = lv_schema_name
    AND      kcu.table_name = lv_table_name
    AND      kcu.constraint_name = lv_constraint_name
    ORDER BY kcu.column_name;
 
  /* Declare a not found record handler to close a cursor loop. */
  DECLARE CONTINUE HANDLER FOR NOT FOUND SET fetched = 1;
 
  /* Assign parameter values to local variables. */
  SET lv_schema_name := pv_schema_name;
  SET lv_table_name := pv_table_name;
  SET lv_constraint_name := pv_constraint_name;
 
  /* Set the first column value. */
  SET lv_column_count := 1;
 
  /* Open the nested cursor. */
  OPEN  foreign_key_column;
  cursor_foreign_key_column: LOOP
 
    /* Fetch a row into the local variables. */    
    FETCH foreign_key_column
    INTO  lv_column_name
    ,     lv_referenced_table_schema
    ,     lv_referenced_table_name
    ,     lv_referenced_column_name;
 
    /* Catch handler for no more rows found from the fetch operation. */
    IF fetched = 1 THEN LEAVE cursor_foreign_key_column; END IF;
 
    /* Initialize the column list or add to it. */
    IF lv_column_count = 1 THEN
      SET lv_column_list := lv_column_name;
      SET lv_column_ref_list := lv_referenced_column_name;
 
      /* Increment the counter value. */
      SET lv_column_count := lv_column_count + 1;
    ELSE
      SET lv_column_list := CONCAT(lv_column_list,',',lv_column_name);
      SET lv_column_ref_list := CONCAT(lv_column_ref_list,',',lv_referenced_column_name);
    END IF;
 
  END LOOP cursor_foreign_key_column;
  CLOSE foreign_key_column;
 
  /* Set the return string to a list of columns. */
  SET lv_return_string :=
        CONCAT(lv_column_list,')',' '
              ,'REFERENCES',' ',lv_referenced_table_schema,'.',lv_referenced_table_name,' ' 
              ,'(',lv_column_ref_list,');');
 
  RETURN lv_return_string;
END;
$$

You can call the procedure with a schema and table name, and you’ll get the foreign keys from just that table. You can create the following parent and child tables to test how multiple column foreign keys work in the script (provided because most folks use surrogate keys):

CREATE TABLE parent
( first_name  VARCHAR(20)  NOT NULL DEFAULT ''
, last_name   VARCHAR(20)  NOT NULL DEFAULT ''
, PRIMARY KEY (first_name, last_name)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
 
CREATE TABLE child
( child_name  VARCHAR(20)  NOT NULL
, first_name  VARCHAR(20)  DEFAULT NULL
, last_name   VARCHAR(20)  DEFAULT NULL
, PRIMARY KEY (child_name)
, KEY fk_parent(first_name, last_name)
, CONSTRAINT fk_parent FOREIGN KEY (first_name, last_name)
  REFERENCES parent (first_name, last_name)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

You call the storeForeignKeys procedure for the child table with this syntax:

CALL storeForeignKeys('studentdb', 'child');

You call the storeForeignKeys procedure for all tables in a schema with this syntax:

CALL storeForeignKeys('studentdb', null);

While unlikely you’ll need this, the following calls the storeForeignKeys procedure for all tables in all schemas:

CALL storeForeignKeys(null, null);

You can export the command sequence with the following command to a script file:

SELECT sql_statement
INTO OUTFILE 'c:/Data/MySQL/apply_foreign_keys.sql'
FIELDS TERMINATED BY ','
OPTIONALLY ENCLOSED BY '"'
LINES TERMINATED BY '\r\n'
FROM command_list;

While preservation of tables and foreign keys is best managed by using a tool, like MySQL Workbench, it’s always handy to have scripts to do specific tasks. I hope this helps those looking for how to preserve foreign keys. You also can find a comprehensive treatment on how to write SQL/PSM code in Chapter 14 of my Oracle Database 11g and MySQL 5.6 Developer Handbook.

The LinkedIn MySQL DB Development group posed a questions on how to handle images. Naturally, the argument always goes: Should images be deployed in the database or the file system? I believe they should be stored in the database because the cost and time associated is too high with regard to managing files, a file naming schema, and backing up the file system discretely from the database.

Since there’s a significant difference between the backup of transactional data and image data, they should be placed in different databases. The imagedb database is where you would place the images and large text descriptions, as shown in the MySQL Workbench ERD:

The imagedb ERD splits the foreign key references back to the system_user table, which contains the individual user credentials. The system_user table serves as the Access Control List (ACL) for the application.

Until I get a chance to write the code for this model, you can refer to the generic PHP/MySQL solution from several years back (its code source was last tested with PHP 5.3). As always, I hope this helps.

I built a new image on VMWare Fusion for my class, which required installing MySQL 5.6 on Fedora, Version 20. If you don’t know how to add your user to the sudoers list, you should check this older and recently updated blog post.

1. Download the MySQL Yum Repository and launch the downloaded RPM.

2. Install MySQL on Fedora, Version 20, which you can find with the following command:

shell> rpm -qa | grep mysql
mysql-community-release-fc20-5.noarch

shell> sudo yum localinstall mysql-community-release-fc20-5.noarch.rpm

3. Install MySQL on Fedora with the following command:

shell> sudo yum install mysql-server

4. Start the MySQL service on Fedora with the following command:

shell> sudo service mysqld start

5. Secure the MySQL installation with the following command:

shell> mysql_secure_installation

6. Set the MySQL Service to start with the Fedora operating system with the following command (not chkconfig):

shell> sudo systemctl enable mysqld.service

ln -s '/usr/lib/systemd/system/mysqld.service' '/etc/systemd/system/mysql.service'
ln -s '/usr/lib/systemd/system/mysqld.service' '/etc/systemd/system/multi-user.target.wants/mysqld.service'

7. Reset the MySQL configuration file to enable external connections through Port 3306 with the following changes to the my:

#socket=/var/lib/mysql/mysql.sock

shell> ifconfig

bind-address=nnn.nnn.nnn.nnn
port=3306

bind-address=localhost.localdomain
port=3306

bind-address=0.0.0.0
port=3306

8. Restart the mysqld service with the following syntax:

shell> sudo service mysqld restart

shell> sudo netstat –anp | grep 3306

tcp        0      0 127.0.0.1:3306          0.0.0.0:*               LISTEN      1311/mysqld

Go to this page if you want to install MySQL Workbench.

Somebody asked how to create a SYNONYM in MySQL, which is interesting because MySQL doesn’t support synonyms. I thought the prior entry explained how to do it, but here’s a new post. However, you can create a view in one database that relies on a table in another database.

The following SQL statements create two databases and grant appropriate privileges to the student as the root superuser:

/* Create two databases. */
CREATE DATABASE seussdb;
CREATE DATABASE appdb;
 
/* Grant privileges to a student user. */
GRANT ALL ON seussdb.* TO student;
GRANT ALL ON appdb.* TO student;

Log out from the root superuser and reconnect as the student user. Then, the following code connects to the seuss database and create a hat table; and inserts two rows into the hat table:

/* Use the appdb database. */
USE seussdb;
 
/* Create a hat table. */
CREATE TABLE hat
( hat_id    INT UNSIGNED PRIMARY KEY AUTO_INCREMENT
, hat_text  VARCHAR(20));
 
/* Insert two rows into the hat table. */
INSERT INTO hat (hat_text) VALUES ('Thing 1');
INSERT INTO hat (hat_text) VALUES ('Thing 2');

The following code connects to the application database and creates a hat view; and then the code inserts one additional row into the hat table:

/* Connect to the application database. */
USE appdb;
 
/* Create a hat view. */
CREATE VIEW hat AS
  SELECT * FROM seussdb.hat;
 
/* Query the contents of the view, or seuss.hat table. */
SELECT * FROM hat;
 
/* Insert a new row into the hat table. */
INSERT INTO hat (hat_text) VALUES ('Thing 3');
 
/* Query the contents of the view, after insert to the view. */
SELECT * FROM hat;

The results will be the following:

+--------+----------+
| hat_id | hat_text |
+--------+----------+
|      1 | Thing 1  |
|      2 | Thing 2  |
|      3 | Thing 3  |
+--------+----------+

I hope this answers the question on how to mimic the Oracle database’s synonyms. The appdb.hat view acts as a synonym to the seuss.hat table.

One of my old students and lab assistants stopped by to show his fiancée the BYU-Idaho campus. It was a long trip since he lives in Macao, China.

He kindly brought me a copy of my Oracle Database 11g and MySQL 5.6 Developer Handbook in simplified Chinese. He’s holding it in the photo.

That makes three books translated into Chinese, which made my day. It’ll be interesting to see if the new MySQL Workbench: Data Modeling & Development book gets translated into Chinese too. Oddly, I never hear about this from the publisher first.

The cover emphasized only the Dolphin, not the Oracle logo material. It made me wonder, how many MySQL users there might be in China. If anybody from China catches the post, it would be great to hear about the MySQL Community in China.

Likewise, if anybody in China catches the post and reads the book, please let me know if you liked it. 😉 Naturally, let me know if you found any problems with it too. By the way, I keep an errata for the book here.

Somebody asked why you can’t implement MySQL triggers that write information when you want to stop the DML statement, like autonomous procedures in Oracle. The question was a surprise but I didn’t find anything on it, so here’s how you can do it. This is more or less like an autonomous process by leveraging both the InnoDB and MyISAM engine’s behaviors. This post leverages an earlier explanation of MySQL Triggers.

1. First you create a MyISAM table, which is a persistent store that auto commits when you’re other InnoDB tables can be transactionally dependent. Here’s a simple MyISAM logger table.

CREATE TABLE logger
( logger_id         INT UNSIGNED AUTO_INCREMENT PRIMARY KEY
, logger_event      VARCHAR(50)
, logger_table      VARCHAR(50)
, logger_instring   VARCHAR(100)
, logger_outstring  VARCHAR(100)
, created_by        INT UNSIGNED
, creation_date     DATE
, last_updated_by   INT UNSIGNED
, last_update_date  DATE) ENGINE=MyISAM;

2. Next, you create an on-insert trigger that changes an input but doesn’t stop the transaction. It also writes to the logger MyISAM table in the scope of the transaction.

CREATE TRIGGER contact_insert
BEFORE INSERT ON contact
FOR EACH ROW
BEGIN
 
  /* Check if last name contains a white space. */
  IF new.last_name REGEXP '^.* .*$' THEN
 
    /* Insert into an MyISAM table, which auto commits in the scope
       of a transaction. */
    INSERT INTO logger
    VALUES ( null
           ,'insert'
           ,'contact'
           , new.last_name
           , REPLACE(new.last_name,' ','-')
           , new.created_by
           , new.creation_date
           , new.last_updated_by
           , new.last_update_date );
 
    /* Replace the name for the INSERT INTO the CONTACT table. */
    SET new.last_name := REPLACE(new.last_name,' ','-');
  END IF;
END;
$$

3. Next, you create an on-update trigger that changes an update while aborting the transaction. It also writes to the logger MyISAM table because its outside the InnoDB scope of a transaction and auto committed on insert.

CREATE TRIGGER contact_update
BEFORE UPDATE ON contact
FOR EACH ROW
BEGIN
 
  /* Check if last name contains a white space. */
  IF new.last_name REGEXP '^.* .*$' THEN
 
    /* Insert into an MyISAM table, which auto commits in the scope
       of a transaction. */
    INSERT INTO logger
    VALUES ( null
           ,'update'
           ,'contact'
           , new.last_name
           , null           
           , old.created_by
           , old.creation_date
           , new.last_updated_by
           , new.last_update_date );
 
    /* Throw an exception to force the business user to see they
       can't update a last name with a white space. */
    SIGNAL SQLSTATE '42000';
  END IF;
END;
$$

4. Next, you create a test case with an INSERT and UPDATE statement that meets the condition of the triggers.

/* Insert a row meeting the trigger condition. */
INSERT INTO contact VALUES
( null, 1001, 1003,'Catherine', null,'Zeta Jones', 1001, UTC_DATE(), 1001, UTC_DATE());
 
/* Update a row meeting the trigger condition. */
UPDATE contact
SET    last_name = 'Zeta Jones'
,      last_updated_by = 1003
,      last_update_date = UTC_DATE()
WHERE  last_name = 'Zeta-Jones';

5. Last, query the logger table. You have a record inserted for both the allowed behavior and the aborted behavior. This means you have the ability to capture material that should never be inserted or updated into a table and who did it by leveraging the who-audit columns of the table.

SELECT * FROM logger;

+-----------+--------------+--------------+-----------------+------------------+------------+---------------+-----------------+------------------+
| logger_id | logger_event | logger_table | logger_instring | logger_outstring | created_by | creation_date | last_updated_by | last_update_date |
+-----------+--------------+--------------+-----------------+------------------+------------+---------------+-----------------+------------------+
|         1 | insert       | contact      | Zeta Jones      | Zeta-Jones       |       1001 | 2013-04-26    |            1001 | 2013-04-26       |
|         2 | update       | contact      | Zeta Jones      | NULL             |       1001 | 2013-04-26    |            1003 | 2013-04-26       |
+-----------+--------------+--------------+-----------------+------------------+------------+---------------+-----------------+------------------+
2 rows in set (0.00 sec)

This effectively delivers in MySQL the equivalent of an autonomous transaction in Oracle. The result from the non-critical trigger records the before and after value, while the results from the critical update trigger only record the before values because the event is aborted by raising an error in the trigger. As always, I hope this helps somebody looking for a solution.

My install instructions on the web site were old, somebody wanted me to publish another set of screen capture for the MySQL 5.6 install and configuration. This is it for Windows 7 using the downloadable MSI file.

Installation Steps

The installation from MySQL’s perspective is actually the installation and configuration of MySQL. For your convenience and reference, I’ve already installed the pre-requisites for MySQL. They’re:

• Visual Studio Tools for Office 20120 Runtime
• Microsoft .NET Framework 4 Client Profile
• Microsoft Excel 2007 or greater
• Microsoft .NET Framework 4 Client Profile
• Microsoft Visual C++ 2010 32-bit runtime
• Microsoft .NET Framework 4 Client Profile

Below are the installation steps after you download the current release .msi file. The icon should look like the one to the right. For this example, I”m using the mysql-installer-community-5.6.11.0.msi. Double-click the icon on your desktop or from your C:\Users\username\Downloads folder. While working through the steps, you can launch any of the small images to the left if you’d like to see what your screen should look like (generally with a right click to open in a new window).

1. The first screen is a Windows 7 dialog box. Click the Run button to install launch the MySQL 5.6 Installer.

2. The second screen is a Windows 7 dialog box. It advises you that the MySQL Installer is working and lets you cancel that operation. Don’t click the Cancel button unless you want to stop the MySQL 5.6 Installer.

3. The third screen is a MySQL Installer message box. It closes when ready to proceed. Ignore it, unless it’s there for more than a couple minutes. If that occurs you’ve got something wrong with your Windows installation or a very slow computer. If the former, kill the installation process; if the latter, wait patiently.

4. The fourth screen in the process is the first MySQL Installer screen. Here you choose what you want to do. You can install MySQL products, inquire about MySQL, or check physical resource components. Provided you installed the prerequisites listed above, you should be prepared to install MySQL Products. Click the Install MySQL Products link to proceed or one of the others to explore.

5. The second MySQL Installer screen is the license form. You must check the I accept the license terms checkbox to enable the Next button. Once the Next button is enabled, click it to proceed.

6. The third MySQL Installer screen connects to the Internet and finds the latest product update. You can check the Skip the check for updates (not recommended) checkbox to skip this but for the example we’ll check anyway. Click the Execute button to proceed.

7. The fourth MySQL Installer screen connects to the Internet and finds the latest product update. You can check the Skip the check for updates (not recommended) checkbox to skip this but for the example we’ll check anyway. Click the Execute button to proceed.

8. The fifth MySQL Installer screen acknowledges the latest update is what you’re installing. Click the Next button to proceed.

9. The sixth MySQL Installer screen gives you five choices for the installation, which are listed below. You most likely want to install the Developer Default, so click the Developer Default radio button and then, click the Next button to proceed.

   MySQL Server

   Both the client and server software for the MySQL Server

   MySQL Workbench

   The GUI application to develop for and manage the server.

   MySQL Visual Studio Plugin

   To work with the MySQL Server from VS.

   MySQL Connectors

   Connector/Net, Java, C/C++, OBDC and others.

   Examples and tutorials

   To help you get started with your development.

   Documentation

   Allows you to read the documentation offline.

10. The seventh MySQL Installer screen performs a system check for the pre-requisites, which I listed before the installation. Assuming you installed them, you should see a screen that confirms your system configuration is ready for installation. Click the Next button to proceed.

11. The eighth MySQL Installer screen performs displays the products that it’ll install, which are listed below and available in the full image to the left. Click the Execute button to install the products.

    • MySQL Server 5.6.11
    • MySQL Workbench CE 5.2.47
    • MySQL Notifier 1.0.3
    • MySQL for Excel 1.1.1
    • Connector/ODBC 5.2.4
    • Connector/C++ 1.1.2
    • Connector/C++ 1.1.2
    • Connector/J 5.1.24
    • Connector/NET 6.6.5
    • MySQL Documentation 5.6.11
    • Samples and Examples 5.6.11

12. The nineth MySQL Installer screen shows the installation by product and it can take a couple minutes. The screen to the left displays progress more than halfway complete. You don’t need to do anything in this step until all products are complete.

13. The tenth MySQL Installer screen shows the completed installation. Everything should install successfully, as shown in the image. Click the Next button to proceed.

This concludes the installation of the MySQL products. The next section shows you how to configure MySQL.

Configuration Steps

You have two basic options, the simple one and the advanced one. These steps will show you how to perform an advanced configuration. I’ve opted to maintain the step numbering from the beginning of the installation. Here are the steps:

14. The eleventh MySQL Installer screen is the first MySQL Configuration screen. You can click the Show Details button or begin the configuration. Click the Next button to proceed.

15. The second MySQL Configuration screen sets the server configuration type, enables TCP/IP networking (as opposed to a socket model), and lets you enable Advanced Configuration. For this installation, we enable the Show Advanced Options checkbox before you click the Next button.

16. The third MySQL Configuration screen sets the password and lets you create MySQL User Accounts. It’s much easier to let the install proceed and use MySQL Workbench to create databases, users, and roles; plus grant permissions through the GUI environment. Enter the root password twice, a trivial and unsecure password cangetin is what I recommend to my students who won’t have any meaningful information in the database. Make sure you can remember the password you enter. Clearly, a better password is required for real environments. After entering the password twice, click the Next button to proceed.

17. The fourth MySQL Configuration screen sets the Windows Service Name, and you should probably make sure there isn’t another MySQL56 service on the machine before you proceed. You have the choice of running the Windows Service using the Standard System Account or a Customer User account. Unless you’re an expert at Windows 7 administration, you should probably choose the Standard System Account as the one running the Windows 7 service. Click the Next button.

18. The fifth MySQL Configuration screen sets the logging options. You only need Show Query Log typically, but the Error Log is helpful. Make the choices and click the Next button.

19. The sixth MySQL Configuration screen explains the next step. It installs the sample files and example databases. You can see what you’ve installed when you click the Show Details button, which is what I did to get the image at the left. The default choice installs the samples and example databases, which can’t hurt. You’ll need the test database if you install DBD::mysql for Perl. If you don’t want them, you can drop them from the database.

20. The seventh MySQL Configuration screen explains you’ve completed configuring the MySQL Server. Click the Next button to proceed.

21. The eighth MySQL Configuration screen explains you’ve finished everything. You can copy the log file to clipboard, which allows you to see everything that was done. Click the Finish button to complete the installation and configuration.

Just one caveat (that’s a warning), this installation doesn’t put the MySQL executable into your System %PATH% variable. You’ll need to do that, and if I get a chance I’ll put a post together for that. I know one or two of my students may need it later.

I hope this helps those you are using the new installer for the first time. It’s a superior tool to the old one, which was also a good tool.

Somebody ran into a problem after reading about the MySQL CREATE statement and the AUTO_INCREMENT option. They couldn’t get a CREATE statement to work with an AUTO_INCREMENT value other than the default of 1. The problem was they were using this incorrect syntax:

CREATE TABLE elvira
( elvira_id    int unsigned PRIMARY KEY AUTO_INCREMENT=1001
, movie_title  varchar(60))
  ENGINE=InnoDB
  CHARSET=utf8;

It raises this error:

ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '=1001
, movie_title  varchar(60))
  ENGINE=InnoDB
  CHARSET=utf8' at line 2

They concluded that MySQL requires you to assign a default value of 1 as the initial automatic numbering value; then you use the ALTER statement to change the initial sequence value. That assumption is incorrect. The problem was with their assignment of an overriding AUTO_INCREMENT value inside the parenthetical list of columns. That assignment needs to occur after the list of columns and constraints, like

CREATE TABLE elvira
( elvira_id    int unsigned PRIMARY KEY AUTO_INCREMENT
, movie_title  varchar(60))
  ENGINE=InnoDB
  AUTO_INCREMENT=1001
  CHARSET=utf8;

It’s not an unlikely mistake since there’s no clear example on either of the referenced web pages (at the time of writing). It would be nice if they were added but I’m of the opinion some of the reference manual pages are too sparse.

After creating the table, you have the generic fix that seems to appear most often as an answer to setting or re-setting the auto numbering sequence of a MySQL table:

ALTER TABLE elvira AUTO_INCREMENT=1001;

Why would you set the auto numbering sequence to something other than 1? Some designers consider it a best practice to increment from a set point like 101 or 1,001 for your Application Programming Interface (API) and they leave a readily identifiable sequence number set for pre- or post-seeded data in tables. The region of preallocated numbers are typically only used in a few of the tables, in any application, but consistently managing sequences across all tables does sometimes simplifies data diagnostics.

Hope this helps somebody looking for a syntax fix. By the way, you can find it on Page 162 of the Oracle Database 11g & MySQL 5.6 Developer Handbook.