Archive for the ‘MySQL’ Category
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
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
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
USE studentdb; SELECT DATABASE();
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
fc20-5 changes with point releases, but assuming that you’re installing the
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:
- Set the MySQL Service to start with the Fedora operating system with the following command (not
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:
port lines below after you know the actual IP address of the server to the
my.cnf file in the
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:
Then, add these two lines to the my.cnf file.
It’s actually easier to use localhost.localdomain than an IP address when you use DHCP, like:
If you plan to connect from a host system, like Windows or Mac OS X, to a virtual Linux environment using DHCP, change
- 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
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.
I’ve worked with every release of Microsoft Excel, and I know it takes effort to keep up to date with certain releases. Clearly, the Data Analysis eXpression (DAX) Language introduced in Excel 2010 went unnoticed by many, which was sad. DAX is truly a powerful extension to the analytical and modeling approaches in Microsoft Excel.
I’d like to recommend Microsoft Excel 2013 Building Data Models with PowerPivot to those who haven’t learned how to use DAX in Excel 2010, 2011, or 2013. DAX works with tables but if you don’t use tables, I guess you can skip DAX because you must have infinite time to produce marginal analytical outcomes (tongue in cheek humor). However, if you’re like most folks, you want a book to get you up-to-speed quickly, and that’s what this book will do for you.
Just one caveat if you’re using an Oracle or MySQL database, use the prepackaged analytic functions before you download the data set. You should always pre-select data before applying analytics in Excel. Remember the more refined the data model you start with the easier it is to structure analytical tools to leverage the data model. While DAX is powerful, it doesn’t replace the speed and query optimized behaviors of effective Oracle or MySQL queries.
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
/* 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
/* 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
/* 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
An acquaintance sent me this image from a cyber cafe or hotel in Russia. It says that my blog site is prohibited and violates Russian law, and that they’re blocking my site in accordance with the Russian Federal Law of 27.07.2006 No. 149-FZ.
All I can say is, “Wow!” I didn’t know that stuff about writing programs, web pages, and solving generic database and operating system problems was so sensitive. For that matter, I didn’t know what I post would interest any government. I half wonder whether my friend’s pulling my leg.
As I reflect on it, could it be that Oracle post on how to write an encrypted object type? or, how with proper OS credentials how you reset MySQL’s root password? or, how to verify a socket in Perl? Nah, it’s probably the one on how to set a static IP in Windows – that’s truly sensitive stuff. 😉
Does anybody who reads the blog have any clue about what would drive the Russian government to block a technology blog site like mine? My serious guess is that there is somebody who hosts with the same provider who’s done something tacky.
SQL tidbits are always valuable and highly searched for by newbies (as opposed to reading the SQL documentation). Sometimes we seasoned SQL developers take for granted little things like when a single- or multiple-character wildcard comparison works. It seems we know what newbies don’t. That you need a wildcard comparison operator not simply and equality comparison operator.
The question posed to me was, “Why doesn’t my wildcard comparison work?” Here’s a simplified example of their question.
SELECT 'Valid' AS "Test" FROM dual WHERE 'Treat' = 'Tre_t' OR 'Treet' = 'Tre_t';
Naturally, the answer is that the equality operator compares the strings based on their exact match (character sensitively in Oracle and character insensitively in MySQL). It needs to be rewritten by replacing the equals (
=) comparison operator with the
LIKE comparison operator. The following query does that:
SELECT 'Valid' AS "Test" FROM dual WHERE 'Treat' LIKE 'Tre_t' OR 'Treet' LIKE 'Tre_t'
The same behavior exists for the multiple-character wildcard (
%). I hope this helps those looking for this answer.
I registered yesterday for Oracle OpenWorld 2013, and I’ll look forward to seeing friends there. Having worked in the Oracle 12c beta for a year, I’ll be interested in the presentations. Also, hearing more about Java 7 at JavaOne. On the downside, I’m missing MySQL Connect this year.
Cloud computing offers many possibilities, and container and pluggable databases are a great solution. We’ve two new acronyms with the Oracle 12c release. A containerized database is a CDB, and a pluggable database is a PDB. I’m looking forward to seeing more about the provisioning of PDBs during the conference. If you’re new to the changes, check out CDBs and PDBs in Chapter 17 in the Oracle 12c Concepts manual.
A couple of my favorite new features are Identity and Invisible Columns. If you’re unfamiliar with the new features for application development, let me recommend this Oracle White Paper. Also, for reference I’ve covered identity and invisible columns thoroughly in the Oracle Database 12c PL/SQL Programming book, which will be available in December.
Missing the MySQL Connect 2013 Bus
Unfortunately, travel budgets preclude me attending MySQL Connect 2013 this year (alas, I’ll miss the bus). It was hard because I’d like to see what’s up with MySQL (since I was a closet MySQL user at Oracle before they acquired it). Anyway, if you’re there, make sure you check out MySQL Workbench 6 for me. Also, I’d like to thank Dave Stokes for the AWESOME review he wrote on Amazon.com for my MySQL Workbench: Data Modeling & Development book. Maybe, I’ll get to go to MySQL Connect 2014 next year.
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.
Just went through all my PHP testing against a fresh instance of Oracle with Zend Server Community Edition 6, and found these warnings, guess that’s pretty clean for the Oracle part of the installation. I didn’t notice it before because generally I do most of my PHP development against a MySQL database. I should have been configuring the
php.ini file routinely, as qualified in this PHP forum discussion.
Warning: oci_set_client_info(): It is not safe to rely on the system's timezone settings. You are *required* to use the date.timezone setting or the date_default_timezone_set() function. In case you used any of those methods and you are still getting this warning, you most likely misspelled the timezone identifier. We selected the timezone 'UTC' for now, but please set date.timezone to select your timezone. in C:\Program Files (x86)\Zend\Apache2\htdocs\Oracle\Db.php on line 47 Warning: oci_set_module_name(): It is not safe to rely on the system's timezone settings. You are *required* to use the date.timezone setting or the date_default_timezone_set() function. In case you used any of those methods and you are still getting this warning, you most likely misspelled the timezone identifier. We selected the timezone 'UTC' for now, but please set date.timezone to select your timezone. in C:\Program Files (x86)\Zend\Apache2\htdocs\Oracle\Db.php on line 48 Warning: oci_set_action(): It is not safe to rely on the system's timezone settings. You are *required* to use the date.timezone setting or the date_default_timezone_set() function. In case you used any of those methods and you are still getting this warning, you most likely misspelled the timezone identifier. We selected the timezone 'UTC' for now, but please set date.timezone to select your timezone. in C:\Program Files (x86)\Zend\Apache2\htdocs\Oracle\Db.php on line 69
Turns out Zend 6 doesn’t automatically set the
[Date] elements in the
php.ini file, which is required for the
oci_set_action() functions of the OCI. You can find the
php.ini file in the
C:\Program Files (x86)\Zend\ZendServer\etc folder on Windows:
[Date] ; Defines the default timezone used by the date functions ; http://php.net/date.timezone ;date.timezone = ; http://php.net/date.default-latitude ;date.default_latitude = 31.7667 ; http://php.net/date.default-longitude ;date.default_longitude = 35.2333 ; http://php.net/date.sunrise-zenith ;date.sunrise_zenith = 90.583333 ; http://php.net/date.sunset-zenith ;date.sunset_zenith = 90.583333
You can find the values for
date.timezone here. Update the
date.timezone as follows:
date.timezone = America/Denver
Then, reboot the Zend Server, and it fixes the warning messages.
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.
- 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
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;
- 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; $$
- 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; $$
- Next, you create a test case with an
UPDATEstatement 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';
- 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.