Archive for January, 2014
Open a port on Fedora
Since MySQL Workbench 6.0 isn’t available for Fedora, Version 20, I’m having my students install it on their local Windows and Mac OS X operating systems. You can configure the /etc/sysconfig/iptables file to enable port 3306 after installing MySQL on Fedora.
You can open a port by using the firewall-config utility (easy way) or by adding the following line to the /etc/sysconfig/iptables file (Fedora’s instructions on editing iptables [hard way]). The file won’t exist initially, but you can create it by running the following command as the root superuser or sudoer:
shell> service iptables save |
You you can run the following commands as the root superuser, which saves the line in the iptables file:
shell> iptables -A INPUT -m conntrack --ctstate NEW -m tcp -p tcp --dport 3306 -j ACCEPT shell> iptables-save |
After making the change to the /etc/iptables file you can change the firewall by running the following command as the root superuser:
shell> service iptables restart |
Just make sure you don’t inadvertently start both iptables and ip6tables as services. You can check that only one is running by using the following commands:
shell> service iptables status shell> service ip6tables status |
MySQL Workbench Configuration
- The first thing you need to do is click on the
+symbol in the circle to the right of the MySQL Connections text label. It launches the Setup New Connection dialog.
- The second thing you need to do is enter a Connection Name, Hostname, Port, and Username. Then, click the Test Connection button.
- The Test Connection button launches the Connect to MySQL Server dialog. Enter the password for the
studentuser (or whatever user you’re interested in), and then click the OK button.
- When the credentials in the Connect to MySQL Server dialog work, you see the following confirmation dialog message. Click the OK button to continue and you’ll see a new VMWare Fedora Instance connection icon.
- Click the VMWare Fedora Instance connection to start a new connection.
- The VMWare Fedora Instance button launches the Connect to MySQL Server dialog. Like you did when configuring the connection, enter the password for the
studentuser (or whatever user you’re interested in), and then click the OK button. It launches an interactive panel that lets you run, edit, or save the SQL script file.
- Type the following two lines in the Query1 panel (at least if you have a
studentdbdatabase:USE studentdb; SELECT DATABASE();
Cleaning up a Schema
My students wanted a simple way to cleanup a development schema. So I wrote the following anonymous block PL/SQL program, which also manages the fact that Oracle Database 12c doesn’t completely drop system-generated sequences in active sessions.
The new identity columns in Oracle Database 12c create system-generated sequences, which you must purge from the recycle bin. If you don’t a generic script, like the following raises:
ORA-32794: cannot DROP a system-generated SEQUENCE |
Here’s the script that cleans up an Oracle schema:
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 | BEGIN FOR i IN (SELECT object_name , object_type FROM user_objects ORDER BY object_type DESC) LOOP /* Drop types in descending order. */ IF i.object_type = 'TYPE' THEN /* Drop type and force operation because dependencies may exist. Oracle 12c also fails to remove object types with dependents in pluggable databases (at least in release 12.1). Type evolution works in container database schemas. */ EXECUTE IMMEDIATE 'DROP '||i.object_type||' '||i.object_name||' FORCE'; /* Drop table tables in descending order. */ ELSIF i.object_type = 'TABLE' THEN /* Drop table with cascading constraints to ensure foreign key constraints don't prevent the action. */ EXECUTE IMMEDIATE 'DROP '||i.object_type||' '||i.object_name||' CASCADE CONSTRAINTS'; /* Oracle 12c ONLY: Purge the recyclebin to dispose of system-generated sequence values because dropping the table doesn't automatically remove them from the active session. CRITICAL: Remark out the following when working in Oracle Database 11g. */ EXECUTE IMMEDIATE 'PURGE RECYCLEBIN'; ELSIF i.object_type = 'LOB' OR i.object_type = 'INDEX' THEN /* A system generated LOB column or INDEX will cause a failure in a generic drop of a table because it is listed in the cursor but removed by the drop of its table. This NULL block ensures there is no attempt to drop an implicit LOB data type or index because the dropping the table takes care of it. */ NULL; ELSE /* Drop any other objects, like sequences, functions, procedures, and packages. */ EXECUTE IMMEDIATE 'DROP '||i.object_type||' '||i.object_name; END IF; END LOOP; END; / |
As noted by Marat, you can simplify the drop of the tables by simply appending a PURGE clause to the DROP TABLE statement.
16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 | /* Drop table tables in descending order. */ ELSIF i.object_type = 'TABLE' THEN /* Drop table with cascading constraints to ensure foreign key constraints but you need to purge system-generated constraints. */ EXECUTE IMMEDIATE 'DROP '||i.object_type||' '||i.object_name||' CASCADE CONSTRAINTS PURGE'; ELSE /* Drop any other objects, like sequences, functions, procedures, and packages. */ EXECUTE IMMEDIATE 'DROP '||i.object_type||' '||i.object_name; END IF; END LOOP; END; / |
Don’t run this version if you’ve provisioned an APEX Workspace in the Oracle Schema. I’ve got an updated version of the script for APEX 4.0. As always, I hope this helps a few people.
Fedora Install of MySQL
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.
- Download the MySQL Yum Repository and launch the downloaded RPM.
- 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 |
The fc20-5 changes with point releases, but assuming that you’re installing the fc20-5 release:
shell> sudo yum localinstall mysql-community-release-fc20-5.noarch.rpm |
- Install MySQL on Fedora with the following command:
shell> sudo yum install mysql-server |
- Start the MySQL service on Fedora with the following command:
shell> sudo service mysqld start |
- Secure the MySQL installation with the following command:
shell> mysql_secure_installation |
- Set the MySQL Service to start with the Fedora operating system with the following command (not
chkconfig):
shell> sudo systemctl enable mysqld.service |
It sets the following two links:
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' |
Restart the Fedora operating system to effect the changes.
- Reset the MySQL configuration file to enable external connections through Port 3306 with the following changes to the my:
Remark out the socket line, like this:
#socket=/var/lib/mysql/mysql.sock |
Add the bind-address and port lines below after you know the actual IP address of the server to the my.cnf file in the /etc directory.
You substitute the actual IP address for the nnn.nnn.nnn.nnn on the bind_address line with the actual IP address returned by the ifconfig command, like this:
shell> ifconfig |
Then, add these two lines to the my.cnf file.
bind-address=nnn.nnn.nnn.nnn port=3306 |
It’s actually easier to use localhost.localdomain than an IP address when you use DHCP, like:
bind-address=localhost.localdomain port=3306 |
If you plan to connect from a host system, like Windows or Mac OS X, to a virtual Linux environment using DHCP, change localhost.localdomain to 0.0.0.0:
bind-address=0.0.0.0 port=3306 |
- Restart the mysqld service with the following syntax:
shell> sudo service mysqld restart |
You can check whether MySQL is listening on Port 3306 with this syntax:
shell> sudo netstat –anp | grep 3306 |
It displays:
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.
Oracle 12c Offset & Rows
Oracle Database 12c provides a limit syntax in SQL for a query with the following clause:
[OFFSET n ROWS] FETCH FIRST m ROWS ONLY |
Unfortunately, it can’t be used dynamically like this in a stored function or procedure:
CURSOR dynamic_cursor ( cv_offset NUMBER , cv_rows NUMBER ) IS SELECT i.item_title FROM item i OFFSET cv_offset ROWS FETCH FIRST cv_rows ROWS ONLY; |
If you attempt it, you would raise the following error:
CREATE OR REPLACE FUNCTION dynamic_range * ERROR at line 1: ORA-03113: end-of-file ON communication channel Process ID: 4516 SESSION ID: 78 Serial NUMBER: 4467 |
The easy solution is to simply write it as a function returning an ADT (Attribute Data Type) collection like:
1 2 3 | CREATE OR REPLACE TYPE item_title_table AS TABLE OF VARCHAR2(60); / |
The following dynamic_range function returns a collection with a dynamic range bound to the limiting clause:
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 | CREATE OR REPLACE FUNCTION dynamic_range ( pv_offset NUMBER , pv_rows NUMBER ) RETURN item_title_table IS /* Declare a collection type. */ lv_item_title_table ITEM_TITLE_TABLE := item_title_table(); /* Local variable length string. */ lv_item_title VARCHAR2(60); /* Declare a local counter. */ lv_counter NUMBER := 1; /* Local NDS statement and cursor variables. */ lv_stmt VARCHAR2(2000); lv_cursor SYS_REFCURSOR; BEGIN /* Assigned a dynamic SQL statement to local variable. */ lv_stmt := 'SELECT i.item_title'||CHR(10) || 'FROM item i'||CHR(10) || 'OFFSET :bv_offset ROWS FETCH FIRST :bv_rows ROWS ONLY'; /* Open cursor for dynamic DNS statement. */ OPEN lv_cursor FOR lv_stmt USING pv_offset, pv_rows; LOOP /* Fetch element from cursor and assign to local variable. */ FETCH lv_cursor INTO lv_item_title; /* Exit when no more record found. */ EXIT WHEN lv_cursor%NOTFOUND; /* Extend space, assign a value, and increment counter. */ lv_item_title_table.EXTEND; lv_item_title_table(lv_counter) := lv_item_title; lv_counter := lv_counter + 1; END LOOP; /* Close cursor. */ CLOSE lv_cursor; /* Return collection. */ RETURN lv_item_title_table; END; / |
By using, the following query:
1 2 | SELECT COLUMN_VALUE AS item_title FROM TABLE(dynamic_range(2,5)); |
Hope this helps anybody who wants to make the limiting clause dynamic. You can find out how to embed it in PHP in Chapter 2 of the Oracle Database 12c PL/SQL Programming.
Subscribe via RSS