Archive for the ‘MySQL’ Category
Fedora Install LAMP
My students wanted an extra credit assignment, so I thought a LAMP configuration and test would be appropriate. The only problem was I hadn’t added it to their course VMware instance. So, here are the instructions to install Apache2, PHP, and MySQLi for a complete LAMP stack when MySQL is already installed.
The post builds on my Fedora Install of MySQL and MySQL Workbench on Fedora posts from last year. It also presumes that you’ve installed a studentdb database but you need to know how to do that let me know (but it hasn’t changed much from the example at the bottom of this old MySQL 5.1 blog post).
You install Apache2 with the following command as the root user, or with the sudo command as a sudoer-list user:
yum install httpd |
The following displays the results of starting the yum utility to install httpd, and you need to reply with a y to complete the installation:
Loaded plugins: langpacks, refresh-packagekit mysql-connectors-community | 2.5 kB 00:00 mysql-tools-community | 2.5 kB 00:00 mysql56-community | 2.5 kB 00:00 pgdg93 | 3.6 kB 00:00 updates/20/x86_64/metalink | 16 kB 00:00 updates | 4.9 kB 00:00 updates/20/x86_64/primary_db | 13 MB 00:04 (1/2): updates/20/x86_64/updateinfo | 1.9 MB 00:02 (2/2): updates/20/x86_64/pkgtags | 1.4 MB 00:01 Resolving Dependencies --> Running transaction check ---> Package httpd.x86_64 0:2.4.10-2.fc20 will be installed --> Processing Dependency: httpd-tools = 2.4.10-2.fc20 for package: httpd-2.4.10-2.fc20.x86_64 --> Processing Dependency: system-logos-httpd for package: httpd-2.4.10-2.fc20.x86_64 --> Running transaction check ---> Package fedora-logos-httpd.noarch 0:21.0.1-1.fc20 will be installed ---> Package httpd-tools.x86_64 0:2.4.10-2.fc20 will be installed --> Finished Dependency Resolution Dependencies Resolved ================================================================================ Package Arch Version Repository Size ================================================================================ Installing: httpd x86_64 2.4.10-2.fc20 updates 1.2 M Installing for dependencies: fedora-logos-httpd noarch 21.0.1-1.fc20 fedora 28 k httpd-tools x86_64 2.4.10-2.fc20 updates 79 k Transaction Summary ================================================================================ Install 1 Package (+2 Dependent packages) Total download size: 1.3 M Installed size: 4.0 M Is this ok [y/d/N]: y Downloading packages: (1/3): fedora-logos-httpd-21.0.1-1.fc20.noarch.rpm | 28 kB 00:00 (2/3): httpd-2.4.10-2.fc20.x86_64.rpm | 1.2 MB 00:01 (3/3): httpd-tools-2.4.10-2.fc20.x86_64.rpm | 79 kB 00:00 -------------------------------------------------------------------------------- Total 815 kB/s | 1.3 MB 00:01 Running transaction check Running transaction test Transaction test succeeded Running transaction (shutdown inhibited) Installing : httpd-tools-2.4.10-2.fc20.x86_64 1/3 Installing : fedora-logos-httpd-21.0.1-1.fc20.noarch 2/3 Installing : httpd-2.4.10-2.fc20.x86_64 3/3 Verifying : httpd-2.4.10-2.fc20.x86_64 1/3 Verifying : fedora-logos-httpd-21.0.1-1.fc20.noarch 2/3 Verifying : httpd-tools-2.4.10-2.fc20.x86_64 3/3 Installed: httpd.x86_64 0:2.4.10-2.fc20 Dependency Installed: fedora-logos-httpd.noarch 0:21.0.1-1.fc20 httpd-tools.x86_64 0:2.4.10-2.fc20 Complete! |
Next, you install php as the root user with the following command:
yum install php |
The following displays when you install php, and you need to reply with a y to complete the installation:
Loaded plugins: langpacks, refresh-packagekit Resolving Dependencies --> Running transaction check ---> Package php.x86_64 0:5.5.22-1.fc20 will be installed --> Processing Dependency: php-common(x86-64) = 5.5.22-1.fc20 for package: php-5.5.22-1.fc20.x86_64 --> Processing Dependency: php-cli(x86-64) = 5.5.22-1.fc20 for package: php-5.5.22-1.fc20.x86_64 --> Running transaction check ---> Package php-cli.x86_64 0:5.5.22-1.fc20 will be installed ---> Package php-common.x86_64 0:5.5.22-1.fc20 will be installed --> Processing Dependency: php-pecl-jsonc(x86-64) for package: php-common-5.5.22-1.fc20.x86_64 --> Running transaction check ---> Package php-pecl-jsonc.x86_64 0:1.3.6-1.fc20 will be installed --> Processing Dependency: /usr/bin/pecl for package: php-pecl-jsonc-1.3.6-1.fc20.x86_64 --> Processing Dependency: /usr/bin/pecl for package: php-pecl-jsonc-1.3.6-1.fc20.x86_64 --> Running transaction check ---> Package php-pear.noarch 1:1.9.5-6.fc20 will be installed --> Processing Dependency: php-xml for package: 1:php-pear-1.9.5-6.fc20.noarch --> Processing Dependency: php-posix for package: 1:php-pear-1.9.5-6.fc20.noarch --> Running transaction check ---> Package php-process.x86_64 0:5.5.22-1.fc20 will be installed ---> Package php-xml.x86_64 0:5.5.22-1.fc20 will be installed --> Finished Dependency Resolution Dependencies Resolved ================================================================================ Package Arch Version Repository Size ================================================================================ Installing: php x86_64 5.5.22-1.fc20 updates 2.6 M Installing for dependencies: php-cli x86_64 5.5.22-1.fc20 updates 3.9 M php-common x86_64 5.5.22-1.fc20 updates 1.0 M php-pear noarch 1:1.9.5-6.fc20 updates 343 k php-pecl-jsonc x86_64 1.3.6-1.fc20 updates 34 k php-process x86_64 5.5.22-1.fc20 updates 77 k php-xml x86_64 5.5.22-1.fc20 updates 247 k Transaction Summary ================================================================================ Install 1 Package (+6 Dependent packages) Total download size: 8.2 M Installed size: 32 M Is this ok [y/d/N]: y Downloading packages: (1/7): php-5.5.22-1.fc20.x86_64.rpm | 2.6 MB 00:03 (2/7): php-cli-5.5.22-1.fc20.x86_64.rpm | 3.9 MB 00:03 (3/7): php-common-5.5.22-1.fc20.x86_64.rpm | 1.0 MB 00:00 (4/7): php-pear-1.9.5-6.fc20.noarch.rpm | 343 kB 00:00 (5/7): php-pecl-jsonc-1.3.6-1.fc20.x86_64.rpm | 34 kB 00:00 (6/7): php-process-5.5.22-1.fc20.x86_64.rpm | 77 kB 00:00 (7/7): php-xml-5.5.22-1.fc20.x86_64.rpm | 247 kB 00:00 -------------------------------------------------------------------------------- Total 1.1 MB/s | 8.2 MB 00:07 Running transaction check Running transaction test Transaction test succeeded Running transaction (shutdown inhibited) Installing : php-cli-5.5.22-1.fc20.x86_64 1/7 Installing : php-process-5.5.22-1.fc20.x86_64 2/7 Installing : php-xml-5.5.22-1.fc20.x86_64 3/7 Installing : 1:php-pear-1.9.5-6.fc20.noarch 4/7 Installing : php-common-5.5.22-1.fc20.x86_64 5/7 Installing : php-pecl-jsonc-1.3.6-1.fc20.x86_64 6/7 Installing : php-5.5.22-1.fc20.x86_64 7/7 Verifying : php-5.5.22-1.fc20.x86_64 1/7 Verifying : php-common-5.5.22-1.fc20.x86_64 2/7 Verifying : php-cli-5.5.22-1.fc20.x86_64 3/7 Verifying : 1:php-pear-1.9.5-6.fc20.noarch 4/7 Verifying : php-process-5.5.22-1.fc20.x86_64 5/7 Verifying : php-xml-5.5.22-1.fc20.x86_64 6/7 Verifying : php-pecl-jsonc-1.3.6-1.fc20.x86_64 7/7 Installed: php.x86_64 0:5.5.22-1.fc20 Dependency Installed: php-cli.x86_64 0:5.5.22-1.fc20 php-common.x86_64 0:5.5.22-1.fc20 php-pear.noarch 1:1.9.5-6.fc20 php-pecl-jsonc.x86_64 0:1.3.6-1.fc20 php-process.x86_64 0:5.5.22-1.fc20 php-xml.x86_64 0:5.5.22-1.fc20 Complete! |
After installing the software, you can set the Apache server to start automatically with the following command:
chkconfig httpd on |
However, that command only starts the Apache server the next time you boot the server. You use the following command as the root user to start the Apache server:
apachectl start |
You can verify the installation with the following command as the root user:
ps -ef | grep httpd | grep -v grep |
It should return:
root 5433 1 0 17:03 ? 00:00:00 /usr/sbin/httpd -DFOREGROUND apache 5434 5433 0 17:03 ? 00:00:00 /usr/sbin/httpd -DFOREGROUND apache 5435 5433 0 17:03 ? 00:00:00 /usr/sbin/httpd -DFOREGROUND apache 5436 5433 0 17:03 ? 00:00:00 /usr/sbin/httpd -DFOREGROUND apache 5437 5433 0 17:03 ? 00:00:00 /usr/sbin/httpd -DFOREGROUND apache 5438 5433 0 17:03 ? 00:00:00 /usr/sbin/httpd -DFOREGROUND apache 5442 5433 0 17:03 ? 00:00:00 /usr/sbin/httpd -DFOREGROUND |
and, then verify the listening port with the following command as the root user:
netstat -tulpn | grep :80 |
It should return the following when both the Apache server is listening on port 80 and the Oracle multi-protocol server is listening on port 8080:
tcp6 0 0 :::80 :::* LISTEN 5433/httpd tcp6 0 0 :::8080 :::* LISTEN 1505/tnslsnr |
After verifying the connection, you can test it by creating the traditional info.php program file in the /var/www/http directory. The file should contain the following:
1 2 3 | <?php phpinfo(); ?> |
You can test it by opening the Firefox browser and entering the following URL from the Fedora Linux image:
http://localhost/info.php |
It should display the typical diagnostic page. This verifies the configuration of the Apache and PHP servers. The next step verifies whether you have the mysqli library to connect to the MySQL database.
You create a mysqli_check.php script, like this:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 | <html>
<header>
<title>Static Query Object Sample</title>
<style type="text/css">
/* HTML element styles. */
table {background:white;border-style:solid;border-width:3px;border-color:black;border-collapse:collapse;}
th {text-align:center;font-style:bold;background:lightgray;border:solid 1px gray;}
td {border:solid 1px gray;}
/* Class tag element styles. */
.ID {min-width:50px;text-align:right;}
.Label {min-width:200px;text-align:left;}
</style>
</header>
<body>
<?php
if (!function_exists('mysqli_init') && !extension_loaded('mysqli')) {
print 'mysqli not installed.'; }
else {
print 'mysqli installed.'; }
?>
</script>
</body>
</html> |
You can test it with the following URL from the local browser:
http://localhost/mysqli_check.php |
If it’s installed you can skip the next step, but if not you need to run yum in expert mode as follows (the check for php-mysql isn’t really necessary because it’s too old a version but good practice):
[root@localhost etc]# yum shell Loaded plugins: langpacks, refresh-packagekit > remove php-mysql No Match for argument: php-mysql > install php-mysqlnd > run --> Running transaction check ---> Package php-mysqlnd.x86_64 0:5.5.22-1.fc20 will be installed --> Processing Dependency: php-pdo(x86-64) = 5.5.22-1.fc20 for package: php-mysqlnd-5.5.22-1.fc20.x86_64 --> Running transaction check ---> Package php-pdo.x86_64 0:5.5.22-1.fc20 will be installed --> Finished Dependency Resolution ================================================================================ Package Arch Version Repository Size ================================================================================ Installing: php-mysqlnd x86_64 5.5.22-1.fc20 updates 293 k Installing for dependencies: php-pdo x86_64 5.5.22-1.fc20 updates 141 k Transaction Summary ================================================================================ Install 1 Package (+1 Dependent package) Total download size: 433 k Installed size: 1.4 M Is this ok [y/d/N]: y Downloading packages: (1/2): php-mysqlnd-5.5.22-1.fc20.x86_64.rpm | 293 kB 00:00 (2/2): php-pdo-5.5.22-1.fc20.x86_64.rpm | 141 kB 00:00 -------------------------------------------------------------------------------- Total 427 kB/s | 433 kB 00:01 Running transaction check Running transaction test Transaction test succeeded Running transaction (shutdown inhibited) Installing : php-pdo-5.5.22-1.fc20.x86_64 1/2 Installing : php-mysqlnd-5.5.22-1.fc20.x86_64 2/2 Verifying : php-pdo-5.5.22-1.fc20.x86_64 1/2 Verifying : php-mysqlnd-5.5.22-1.fc20.x86_64 2/2 Installed: php-mysqlnd.x86_64 0:5.5.22-1.fc20 Dependency Installed: php-pdo.x86_64 0:5.5.22-1.fc20 Finished Transaction > quit |
You should note that this also installed PDO. One caveat, before you rerun the mysqli_check.php script from a browser, you need to restart the Apache server. You can do that as the root user with the following syntax:
apachectl restart |
You can retest it with the following URL from the local browser:
http://localhost/mysqli_check.php |
At this point you should have everything installed to test your connection the MySQL database. As mentioned, this example extends my instructions for installing MySQL on the Fedora instance.
The following query.php file tests your ability to connect to the MySQL database with the mysqli driver, and it uses the studentdb and video store example from my Oracle Database 11g and MySQL 5.6 Developer Handbook:
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 | <html>
<header>
<title>Static Query Object Sample</title>
<style type="text/css">
/* HTML element styles. */
table {background:white;border-style:solid;border-width:3px;border-color:black;border-collapse:collapse;}
th {text-align:center;font-style:bold;background:lightgray;border:solid 1px gray;}
td {border:solid 1px gray;}
/* Class tag element styles. */
.ID {min-width:50px;text-align:right;}
.Label {min-width:200px;text-align:left;}
</style>
</header>
<body>
<?php
// Assign credentials to connection.
$mysqli = new mysqli("localhost", "student", "student", "studentdb");
// Check for connection error and print message.
if ($mysqli->connect_errno) {
print $mysqli->connect_error."<br />";
print "Connection not established ...<br />";
}
else {
// Declare a static query.
$query = "SELECT au.system_user_id, au.system_user_name FROM system_user au" ;
// Loop through a result set until completed.
do {
// Attempt query and exit with failure before processing.
if (!$stmt = $mysqli->query($query)) {
// Print failure to resolve query message.
print $mysqli->error."<br />";
print "Failed to resolve query ...<br />";
}
else {
// Print the opening HTML table tag.
print '<table><tr><th class="ID">ID</th><th class="Label">User Role Name</th></tr>';
// Fetch a row for processing.
while( $row = $stmt->fetch_row() ) {
// Print the opening HTML row tag.
print "<tr>";
// Loop through the row's columns.
for ($i = 0;$i < $mysqli->field_count;$i++) {
// Handle column one differently.
if ($i == 0)
print '<td class="ID">'.$row[$i]."</td>";
else
print '<td class="Label">'.$row[$i]."</td>";
}
// Print the closing HTML row tag.
print "</tr>";
}
}
} while( $mysqli->next_result());
// Print the closing HTML table tag.
print "</table>";
// Release connection resource.
$mysqli->close(); }
?>
</script>
</body>
</html> |
This should display the following in the browser:
You can see how to open port 80 for the Apache server in this blog post. If you want to work with blob data types, you’ll also need to use yum to install the php-gd library. You can read my LAMP php-gd library blog post to learn how to install the libraries. As always, I hope a step-by-step approach without assumptions helps those learning MySQL.
Lowercase Table Names
A student posed the question about why table names are case sensitive. That’s because case sensitive table names are the default installation, as qualified in the MySQL documentation. You can verify that with the following query:
SELECT CASE WHEN @@lower_case_table_names = 1 THEN 'Case insensitive tables' ELSE 'Case sensitive tables.' END AS "Table Name Status"; |
The default value returned on Linux is:
+------------------------+ | Table Name Status | +------------------------+ | Case sensitive tables. | +------------------------+ 1 row in set (0.00 sec) |
The default value for the lower_case_table_names value on the Windows OS is 1 not 0 because you can inadvertently create a lowercase and case sensitive table when you write an INSERT statement and use a lowercase table name. I’ve provided that detail in a reply comment to this blog post.
You can change the default by adding the following parameter in the my.cnf file on Linux or the my.ini file on Windows:
# Make all tables case insensitive. lower_case_table_names=1 |
This lets you enter tables in upper or mixed case, and stores them in the data catalog as lowercase table names.
Querying InnoDB Tables
Somebody ran into the following error message trying to query the innodb_sys_foreign and innodb_sys_foreign_cols tables from the information_schema database:
ERROR 1227 (42000): Access denied; you need (at least one of) the PROCESS privilege(s) for this operation |
It’s easy to fix the error, except you must grant the PROCESS privilege. It’s a global privilege and it should only be granted to super users. You grant the privilege global PROCESS privilege to the student user with the following command:
GRANT PROCESS ON *.* TO student; |
Then, you can run this query to resolve foreign keys to their referenced primary key column values:
SELECT SUBSTRING_INDEX(f.id,'/',-1) AS constraint_name , CONCAT(SUBSTRING_INDEX(for_name,'/',-1),'.',SUBSTRING_INDEX(for_col_name,'/',-1)) AS foreign_key_column , CONCAT(SUBSTRING_INDEX(ref_name,'/',-1),'.',SUBSTRING_INDEX(ref_col_name,'/',-1)) AS primary_key_column FROM innodb_sys_foreign f INNER JOIN innodb_sys_foreign_cols fc ON f.id = fc.id WHERE SUBSTRING_INDEX(f.for_name,'/',-1) = 'system_user_lab' ORDER BY CONCAT(SUBSTRING_INDEX(for_name,'/',-1),'.',SUBSTRING_INDEX(for_col_name,'/',-1)) , CONCAT(SUBSTRING_INDEX(ref_name,'/',-1),'.',SUBSTRING_INDEX(ref_col_name,'/',-1)); |
It returns the following:
+---------------------+--------------------------------------+------------------------------------+ | constraint_name | foreign_key_column | primary_key_column | +---------------------+--------------------------------------+------------------------------------+ | system_user_lab_fk1 | system_user_lab.created_by | system_user_lab.system_user_id | | system_user_lab_fk2 | system_user_lab.last_updated_by | system_user_lab.system_user_id | | system_user_lab_fk3 | system_user_lab.system_user_group_id | common_lookup_lab.common_lookup_id | | system_user_lab_fk4 | system_user_lab.system_user_type | common_lookup_lab.common_lookup_id | +---------------------+--------------------------------------+------------------------------------+ 4 rows in set (0.00 sec) |
However, you can get the same information without granting the global PROCESS privilege. You simply use the table_constraints and key_column_usage tables, like this:
SELECT tc.constraint_name , CONCAT(kcu.table_name,'.',kcu.column_name) AS foreign_key_column , CONCAT(kcu.referenced_table_name,'.',kcu.referenced_column_name) AS primary_key_column 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' AND tc.table_name = 'system_user_lab' ORDER BY tc.table_name , kcu.column_name; |
It prints the same output:
+---------------------+--------------------------------------+------------------------------------+ | constraint_name | foreign_key_column | primary_key_column | +---------------------+--------------------------------------+------------------------------------+ | system_user_lab_fk1 | system_user_lab.created_by | system_user_lab.system_user_id | | system_user_lab_fk2 | system_user_lab.last_updated_by | system_user_lab.system_user_id | | system_user_lab_fk3 | system_user_lab.system_user_group_id | common_lookup_lab.common_lookup_id | | system_user_lab_fk4 | system_user_lab.system_user_type | common_lookup_lab.common_lookup_id | +---------------------+--------------------------------------+------------------------------------+ 4 rows in set (0.00 sec) |
Hope this helps.
MySQL Non-unique Indexes
Somebody wanted to know how to find any non-unique indexes in information_schema of the MySQL. The query takes a session variable with the table name and returns the non-unique indexes by column names. It uses a correlated subquery to exclude the table constraints. A similar query lets you find unique indexes in MySQL. Both queries are in this post.
You set the session variable like this:
SET @sv_table_name := 'member_lab'; |
You can query the indexes result with the following query:
SELECT s.table_name , s.index_name , s.seq_in_index , s.column_name FROM information_schema.statistics s WHERE s.table_name = @sv_table_name AND s.non_unique = TRUE AND NOT EXISTS (SELECT null FROM information_schema.table_constraints tc WHERE s.table_name = tc.table_name AND s.index_name = tc.constraint_name) ORDER BY s.table_name , s.seq_in_index; |
You can also reverse the logic and exclude implicit unique indexes on auto incrementing columns, like
SELECT s.table_name , s.index_name , s.seq_in_index , s.column_name FROM information_schema.statistics s WHERE s.table_name = @sv_table_name AND s.non_unique = FALSE AND NOT s.index_name = 'primary' AND EXISTS (SELECT null FROM information_schema.table_constraints tc WHERE s.table_name = tc.table_name AND s.index_name = tc.constraint_name) ORDER BY s.index_name , s.seq_in_index; |
Hope this helps those trying to find non-unique indexes for a table in MySQL.
Popular PHP Frameworks
My students often ask me about popular PHP frameworks and MySQL approaches. I wish a PHP framework choice was as clear as a JavaScript framework, like AngularJS. Unfortunately, PHP frameworks aren’t that clear cut.
It seems that the most popular PHP frameworks are: Laravel (1st), Phalcon (2nd), and Symphony2 (3rd). I found the following graphic (from December 2013) that highlights popularity by percentage of the market (though I can’t guarantee its accuracy). As far as jobs go, on Dice.com only Laravel had more than 3 positions referring to the framework by name. There were actually 42 PHP developer positions that mention Laravel out of 2,115 PHP developer positions. So, it seems learning a specific framework for PHP doesn’t yet have much market appeal.
While learning the basics of PHP are generic, frameworks expedite process and control pattern implementation. My suggestion to students is to target three to five employers that use a specific framework and start learning how to use the framework. I’d like to get opinions from those in the field whether this is the best advice.
As to MySQL, I suggest they learn native Mysqli and MySQL PDO. Any other suggestions on that from readers? Thanks as always.
Using MySQL Workbench
I’ve been setting up a simplified lab environment to let my students learn use in class. This added content will show them how to do reverse engineering with MySQL Workbench.
It’s a complete Fedora image with MySQL and Oracle Database 11g for the course. The uncompressed image is 14GB and the compressed image is 5.3GB. I chose Fedora because it’s the smallest open source image that supports both environments, and Fedora is the closest to Red Hat and Oracle Unbreakable Linux. I’m inclined to make the instance available generally but haven’t figured out the best way to do that.
Here are the new instructions I’m adding and if you have any input leave it as a comment. 😉
You connect as the student user, which puts you in the /home/student directory. Once connected to the Fedora OS, you open a Terminal session by clicking on Activities in the upper right hand corner, and then you type terminal in the search box. When you’re in the Terminal session, use the following command to verify that the apply_mysql_lab1.sql file is correct:
cat Data/cit225/mysql/lab1/apply_mysql_lab1.sql |
It should display the following commands:
\. /home/student/Data/cit225/mysql/lib/cleanup.sql \. /home/student/Data/cit225/mysql/lib/create_mysql_store_ri.sql \. /home/student/Data/cit225/mysql/lib/seed_mysql_store_ri.sql |
You can run the apply_mysql_lab1.sql to create the tables in the studentdb database, and seed them with data. Assuming you’re in the same /home/student directory, you connect to the studentdb database with the following syntax:
mysql -ustudent -pstudent studentdb |
or, more securely:
mysql -ustudent studentdb -p |
Having connected to the studentdb database, you can run the following command:
\. /home/student/Data/cit225/mysql/lab1/apply_mysql_lab1.sql |
It creates a Video store image and seeds it with some basic data. You can use the show command to see the tables you’ve created, like:
show tables; |
It displays:
+---------------------+ | Tables_in_studentdb | +---------------------+ | address | | common_lookup | | contact | | contacts | | current_rental | | item | | member | | rental | | rental_item | | street_address | | system_user | | telephone | +---------------------+ 12 rows in set (0.00 sec) |
After successfully creating and seeding the studentdb database, you can run MySQL Workbench by launching it from the search field (recommended). Alternatively, you can open it from a terminal session with the following command. Unfortunately, a command-line launch links the terminal and the MySQL Workbench processes and closing the terminal will close the MySQL Workbench.
mysql-workbench |
Here are the instructions for the lab with MySQL Workbench:
- The first displayed page of MySQL Workbench is the home page (click on it or any of the others to see the full size image). Click the ⊕ symbol to the right of the MySQL Workbench title.
- After clicking the the ⊕ symbol, it launches the Startup New Connection dialog. Enter a name for your new connection. I recommend you enter MySQLConnection.
- Click the Test Connection button to see if it works.
- When you click the Test Connection button, MySQL Workbench prompts you for a password. After entering a password and verifying the connection, click the OK button to test the connection.
- Click the OK button to continue.
- Click the gray highlighted MySQLConnection connection icon below the MySQL Connection title to launch the MySQL Workbench application.
- When you click the gray highlighted MySQLConnection connection icon, MySQL Workbench prompts you for a password. After entering a password and verifying the connection, click the OK button to connect to the MySQL Workbench application.
- The MySQL Workbench launches in the default view.
- Click on the Database menu option and then the Reverse Engineering… option, as shown in the illustration.
- This displays the Set Parameters for Connecting to a DBMS dialog. Click the Stored Connection list of values. Choose the MySQLConnection (if you used my suggestion) or the one you created from the list of values of the Stored Connection element. Click the Next button to continue.
- Enter the password and click the OK button to connect to the MySQL database.
- This is an in-progress display, it runs waiting for the password and until the step of the wizard completes.
- This dialog displays when the MySQL Workbench application connects to the database, retrieves a schema (database) list from the database management system, and checks the common server configuration issues. Click the Next button to move to the next step of the wizard.
- This Select Schemas to Reverse Engineer dialog displays any available schemas. You check the schemas that you want. Click the Next button to move to the next step of the wizard.
- Enter the password and click the OK button to connect to the MySQL database to retrieve objects from the database management system.
- This dialog displays when the MySQL Workbench application retrieves objects from the database. Click the Next button to move to the next step of the wizard.
- This dialog displays when the MySQL Workbench application retrieves objects from the schemata and checks the result. Click the Next button to move to the next step of the wizard.
- This dialog displays the tables, views, and routines to import. Click the Execute button to move to the next step of the wizard.
- This dialog displays the tables, views, and routines to import. Click the Execute button to move to the next step of the wizard.
- This dialog displays shows the reverse engineering objects and puts them in the object image. Click the Next button to move to the next step of the wizard.
- This dialog displays a summary of reverse engineering objects. Click the Close button to move to the next step of the wizard.
- The MySQL Model dialog displays after you complete the reverse engineering process. Click the EER Diagram icon or EER Diagram tab to see visual depiction of the database objects.
- The EER Diagram is equivalent to the EER Diagram tab. The Navigator displays the tables as blue rectangles. You can scroll through the Canvas Panel to work with the display of tables and views.
- The next image shows EER Diagram with the tables moved for display purposes. Sometimes there are too many relationship lines, MySQL Workbench lets you split the lines so they don’t clutter the diagram.
- The next image shows you the Property tab of EER Diagram. You should see that the drawSplit checkbox is checked, which suppresses the bottom-most relationship line from displaying in the EER from display on the Canvas Panel.
You can now save the MySQL Workbench file in the Lab 3 directory.
MySQL Workbench on Fedora
The early release of Fedora 20 disallowed installation of MySQL Workbench but the current version allows it. Almost like Tom Cruise’s Edge of Tomorrow without the drama. All you need to do is follow my earlier instructions for installing MySQL on Fedora 20. I’d check your kernel to know whether it’s supported. You can check that with this command:
<shell> uname -r |
My Fedora is at the following version:
3.14.8-200.fc20.x86_64 |
Then, you can install MySQL Workbench with yum, like this:
<shell> sudo yum install mysql-workbench |
It generates the following log file, and if you have Oracle 11g XE installed you can ignore the mime-type error:
Loaded plugins: langpacks, refresh-packagekit
Resolving Dependencies
--> Running transaction check
---> Package mysql-workbench-community.x86_64 0:6.1.7-1.fc20 will be installed
--> Processing Dependency: libzip.so.2()(64bit) for package: mysql-workbench-community-6.1.7-1.fc20.x86_64
--> Processing Dependency: libvsqlitepp.so.3()(64bit) for package: mysql-workbench-community-6.1.7-1.fc20.x86_64
--> Processing Dependency: libtinyxml.so.0()(64bit) for package: mysql-workbench-community-6.1.7-1.fc20.x86_64
--> Processing Dependency: liblua-5.1.so()(64bit) for package: mysql-workbench-community-6.1.7-1.fc20.x86_64
--> Processing Dependency: libgtkmm-2.4.so.1()(64bit) for package: mysql-workbench-community-6.1.7-1.fc20.x86_64
--> Processing Dependency: libgdkmm-2.4.so.1()(64bit) for package: mysql-workbench-community-6.1.7-1.fc20.x86_64
--> Processing Dependency: libctemplate.so.2()(64bit) for package: mysql-workbench-community-6.1.7-1.fc20.x86_64
--> Running transaction check
---> Package compat-lua-libs.x86_64 0:5.1.5-1.fc20 will be installed
---> Package ctemplate.x86_64 0:2.2-5.fc20 will be installed
---> Package gtkmm24.x86_64 0:2.24.4-2.fc20 will be installed
---> Package libzip.x86_64 0:0.11.2-1.fc20 will be installed
---> Package tinyxml.x86_64 0:2.6.2-4.fc20 will be installed
---> Package vsqlite++.x86_64 0:0.3.13-3.fc20 will be installed
--> Finished Dependency Resolution
Dependencies Resolved
================================================================================
Package Arch Version Repository Size
================================================================================
Installing:
mysql-workbench-community x86_64 6.1.7-1.fc20 mysql-tools-community 24 M
Installing for dependencies:
compat-lua-libs x86_64 5.1.5-1.fc20 updates 158 k
ctemplate x86_64 2.2-5.fc20 fedora 174 k
gtkmm24 x86_64 2.24.4-2.fc20 fedora 748 k
libzip x86_64 0.11.2-1.fc20 updates 59 k
tinyxml x86_64 2.6.2-4.fc20 updates 49 k
vsqlite++ x86_64 0.3.13-3.fc20 updates 58 k
Transaction Summary
================================================================================
Install 1 Package (+6 Dependent packages)
Total download size: 26 M
Installed size: 119 M
Is this ok [y/d/N]: y
Downloading packages:
(1/7): compat-lua-libs-5.1.5-1.fc20.x86_64.rpm | 158 kB 00:01
(2/7): ctemplate-2.2-5.fc20.x86_64.rpm | 174 kB 00:01
(3/7): tinyxml-2.6.2-4.fc20.x86_64.rpm | 49 kB 00:00
(4/7): gtkmm24-2.24.4-2.fc20.x86_64.rpm | 748 kB 00:01
(5/7): vsqlite++-0.3.13-3.fc20.x86_64.rpm | 58 kB 00:00
(6/7): libzip-0.11.2-1.fc20.x86_64.rpm | 59 kB 00:02
(7/7): mysql-workbench-community-6.1.7-1.fc20.x86_64.rpm | 24 MB 00:08
--------------------------------------------------------------------------------
Total 2.9 MB/s | 26 MB 00:08
Running transaction check
Running transaction test
Transaction test succeeded
Running transaction
Installing : gtkmm24-2.24.4-2.fc20.x86_64 1/7
Installing : libzip-0.11.2-1.fc20.x86_64 2/7
Installing : vsqlite++-0.3.13-3.fc20.x86_64 3/7
Installing : ctemplate-2.2-5.fc20.x86_64 4/7
Installing : compat-lua-libs-5.1.5-1.fc20.x86_64 5/7
Installing : tinyxml-2.6.2-4.fc20.x86_64 6/7
Installing : mysql-workbench-community-6.1.7-1.fc20.x86_64 7/7
Error in file "/usr/share/applications/oraclexe-startdb.desktop": "Application/database" is an invalid MIME type ("Application" is an unregistered media type)
Verifying : tinyxml-2.6.2-4.fc20.x86_64 1/7
Verifying : compat-lua-libs-5.1.5-1.fc20.x86_64 2/7
Verifying : ctemplate-2.2-5.fc20.x86_64 3/7
Verifying : vsqlite++-0.3.13-3.fc20.x86_64 4/7
Verifying : mysql-workbench-community-6.1.7-1.fc20.x86_64 5/7
Verifying : libzip-0.11.2-1.fc20.x86_64 6/7
Verifying : gtkmm24-2.24.4-2.fc20.x86_64 7/7
Installed:
mysql-workbench-community.x86_64 0:6.1.7-1.fc20
Dependency Installed:
compat-lua-libs.x86_64 0:5.1.5-1.fc20 ctemplate.x86_64 0:2.2-5.fc20
gtkmm24.x86_64 0:2.24.4-2.fc20 libzip.x86_64 0:0.11.2-1.fc20
tinyxml.x86_64 0:2.6.2-4.fc20 vsqlite++.x86_64 0:0.3.13-3.fc20
Complete! |
After successfully installing MySQL Workbench, you can launch it with the following command:
<shell> mysql-workbench |
It should launch the following MySQL Workbench home page (click on it to see the full size image):
MySQL Insert from Query
While working with an error that my students surfaced in the Oracle Database 12c, I blogged about the limit of using a subquery in an Oracle INSERT statement, and I discovered something when retesting it in MySQL. It was a different limitation. I was also surprised when I didn’t find any mention of it through a Google search, but then I may just not have the right keywords.
The original test case uses a subquery to create a multiple row result set for an INSERT statement. Unfortunately, I discovered that you can’t always embed a UNION or UNION ALL inside a subquery. At least, you can’t when you call the subquery inside an INSERT statement. For example, let’s create a DESTINATION table and then we’ll try to insert records with a query that fabricates a result set.
-- Drop the destination table. DROP TABLE IF EXISTS destination; -- Create the destination table. CREATE TABLE destination ( destination_id int unsigned , destination_name varchar(30)); |
Now let’s try the subquery:
1 2 3 4 | INSERT INTO destination ( SELECT 1,'Sample1' UNION ALL SELECT 2,'Sample2' ); |
It raises the following error message:
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 'UNION ALL SELECT 2,'Sample2' )' at line 3 |
I wasn’t sure if I missed something. It turned out that I did. While you can put a subquery inside an INSERT statement, you can’t embed it inside a set of parentheses when it contains a UNION ALL set operator. Other statements work with or without parentheses.
1 2 3 4 | INSERT INTO destination SELECT 1,'Sample1' UNION ALL SELECT 2,'Sample2'; |
If somebody has any more to add, I look forward to reading it.
Toad Freeware Page
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.
A/UX, NeXTSTEP, & OS X
One thing that gets tedious in the IT community and Oracle community is the penchant for Windows only solutions. While Microsoft does an excellent job in certain domains, I remain a loyal Apple customer. By the way, you can install Oracle Client software on Mac OS X and run SQL Developer against any Oracle Database server. You can even run MySQL Workbench and MySQL server natively on the Mac OS X platform, which creates a robust development platform and gives you more testing options with the MySQL monitor (the client software).
Notwithstanding, some Windows users appear to malign Apple and the Mac OS X on compatibility, but they don’t understand that it’s a derivative of the Research Unix, through BSD (Berkeley Software Distribution). This Unix lineage chart illustrates it well:
I’m probably loyal to Apple because in the early 1990’s I worked on Mac OS 6, Mac OS 7, A/UX, NeXTSTEP, and AIX/6000 (Version 3) while working at APL (American President Lines) in Oakland, California. Back then, my desktop was a pricey Macintosh Quadra 950 and today I work on a pricey Mac Pro desktop. The Mac Pro lets me use VMware virtualize development environments for Oracle Linux, Red Hat Enterprise Linux, Fedora, and as you might guess Windows 7/8. My question to those dyed in the wool Microsoft users is simple, why would you choose a single user OS like Windows over a multi-user OS like Mac OS X?
Subscribe via RSS