Archive for the ‘LAMP’ Category
Toad for MySQL Freeware
While SQL Developer is a nice tool and free, Toad is still an awesome development platform, albeit for Windows. In fact, it was annoying to have to install the Microsoft .NET Framework before installing it. It is free for MySQL!
Since my students have to do all their work in Oracle and then port it to MySQL, I demonstrate Quest’s Toad for MySQL’s at the end of the term. I don’t want them to leverage the automatic ERD diagramming while they’re learning how to do it.
There’s only one real trick to making automatic ERD diagramming work. That trick requires that you write your loading scripts for the Inno DB and use referential integrity constraints. My sample Video Store scripts for my database class are updated for MySQL referential integrity.
Unlike the friendly CASCADE CONSTRAINTS
clause you can use in Oracle, MySQL won’t let you create a re-runnable script with only DDL statements. Actually, the constraint comes from the InnoDB engine. You must issue a specialized InnoDB command before running your script:
11 12 13 | -- This enables dropping tables with foreign key dependencies. -- It is specific to the InnoDB Engine. SET FOREIGN_KEY_CHECKS = 0; |
Primary keys are a bit different from Oracle and it appears you can’t name them, at least I couldn’t see how to do it. Here’s an example of primary and foreign key constraints in MySQL. The primary key is inline and the foreign key constraints are out of line. This example from the downloadable scripts uses self referencing foreign key constraints.
24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 | CREATE TABLE system_user ( system_user_id INT UNSIGNED PRIMARY KEY AUTO_INCREMENT , system_user_name CHAR(20) NOT NULL , system_user_group_id INT UNSIGNED NOT NULL , system_user_type INT UNSIGNED NOT NULL , first_name CHAR(20) , middle_name CHAR(20) , last_name CHAR(20) , created_by INT UNSIGNED NOT NULL , creation_date DATE NOT NULL , last_updated_by INT UNSIGNED NOT NULL , last_update_date DATE NOT NULL , KEY system_user_fk1 (created_by) , CONSTRAINT system_user_fk1 FOREIGN KEY (created_by) REFERENCES system_user (system_user_id) , KEY system_user_fk2 (last_updated_by) , CONSTRAINT system_user_fk2 FOREIGN KEY (last_updated_by) REFERENCES system_user (system_user_id) ) ENGINE=InnoDB DEFAULT CHARSET=latin1; |
Once installed (instructions are here) and connected to the MySQL database, you simply click the ERD icon in the top panel and drag the tables onto the canvas. You’ll see something like this (by the way click on the image to see its full size):
Have fun with it. It’ll be interesting to see how Oracle positions MySQL when they own it. My hunch is that they’ll continue to sell it and provide it as an open source product.
PHP for loading a BLOB
Sometimes you chalk something up as straightforward because you’ve done it a while. I did that in a lab assignment recently. It asked my students to upload a large text file and image to the MySQL database, store them in a TEXT
and MEDIUMBLOB
column, and read them back out of the database, like this:
The trick was that I wanted them to read the file into a string and then load the string. There wasn’t a single code example to do this out there, except some that might exist behind an account and credit card payment. I put together a complete example like the Oracle LOB processing page. You can find it here in the MySQL LOB processing blog page. More or less, it shows you how to stream an image into a MySQL database in chunks (I chose 8 K chunks).
The general tricks to upload a string require you enclose to enclose them with the addslashes()
function before assigning a binary stream to a variable, then stripslashes()
function by segment before you load it to the database. You really don’t need to do that. It’s a myth. The binary stream doesn’t require that extra handling. In fact, you can corrupt certain images when you use the addslashes()
and stripslashes()
functions; they should be avoided in this context.
You should do it in streams (at least when they’re larger than 1 MB), I chose the procedural mysqli
to demonstrate it because there wasn’t an example that I or my students could find on the web. Just for information, some laptops don’t have the resources to accommodate LARGEBLOB
datatypes on the Windows OS. The BLOB
or MEDIUMBLOB
should work fine, especially for this little example.
46 47 48 49 50 51 52 53 54 55 56 57 58 | // Declare a PL/SQL execution command. $sql = "UPDATE item SET item_blob = ? WHERE item_id = ?"; // Prepare statement and link it to a connection. if (mysqli_stmt_prepare($stmt,$sql)) { mysqli_stmt_bind_param($stmt,"bi",$item_blob,$id); $start = 0; $chunk = 8192; while ($start < strlen($item_blob)) { mysqli_send_long_data($stmt,0,substr($item_blob,$start,$chunk)); $start += $chunk; } |
You can find the code in that blog page referenced. Hope it helps some folks.
Zend Core Server
The Zend Core Server replaces the deprecated Zend Core for Oracle. I’ve put a quick installation guide here. It’s much nicer, and the licensed server is now the recommended direction from Oracle.
The community edition also installs MySQL, phpMySQLAdmin, and a brand new console. You should try it out.
PHP, LOBs, and Oracle
I finally got around to summarizing how to use PHP to store, retrieve, and display CLOBs and BLOBs from an Oracle database. I think too often we default to BFILEs. I put all the code in zip files with instructions and suggestions for locations. This is really the second entry that goes with configuring Zend Server Community Edition or the DEPRECATED Zend Core for Oracle.
If you’re new to PHP, check out the Underground PHP and Oracle book from Christopher Jones and Alison Holloway. It’s FREE!
The Oracle LOB Processing entry is in this blog page. I know it means another click, but I’ll probably add and modify it over time. If you’ve got time and interest, take a look and let me know what you think and what improvements you’d like to see. Thanks.
Oracle PHP Configuration
I finally got around to creating a blog page that shows you how to install Zend Core for Oracle, verify the installation of PHP, your connection to an Oracle XE database with PHP, and your connection to a MySQL database with PHP.
It’s using the folding concepts I’ve adopted in the blog. One section unfolds to display screen shots for the installation of Zend Core for Oracle. Another section shows you how to confirm your PHP and Apache installation. Two other sections show you how to confirm your connection to an Oracle or MySQL database.
I apologize to those who don’t like to click through to another page, but it was a long page with 20+ images. Hope it helps a few folks, I know it’ll help my students. 😉
MySQL security risk?
Somebody asked me why you must provide the IDENTIFIED BY password
clause on a grant after you’ve already created the user. This is a very good question, I wondered it myself a while back. This blog provides the what, why, and how of it.
The reason you typically must provide the IDENTIFIED BY password
clause is that typically you’ve enabled the NO_AUTO_CREATE_USER
value in your sql_mode
. That parameter is there by default from MySQL 5.0.2 forward. It’s what requires you to use the IDENTIFIED BY password
clause.
The parameter enforces a match against user
, host
, and password
columns in the user
table of the mysql
database. If you disable it, the match is on user
and host
columns in the user
table. If you make a grant to a user without specifying a host and a wildcard (%) host doesn’t exist for that user, the grant will insert a duplicate user. The same user name with a wildcard host trumps the one with a localhost
host value for access privileges, which means that user no longer needs a password to gain entry to the database.
The following steps will show and explain what happens in MySQL’s security model.
1. Sign on as the root
user, then create a new myuser
user:
mysql> CREATE USER 'myuser'@'localhost' IDENTIFIED BY 'myuser'@'localhost'; |
If you attempt to grant permissions to the myuser
without a password value, like this
mysql> GRANT ALL ON sampledb.* TO 'myuser'@'localhost'; |
You should see an outcome like the following. At least, you will when the NO_AUTO_CREATE_USER
value is set in your sql_mode
variable.
ERROR 1133 (42000): Can't find any matching row in the user table |
If it let you set it, then the NO_AUTO_CREATE_USER
value isn’t set. You can check what’s set with this query:
mysql> SELECT @@sql_mode; |
You should see this:
+----------------------------------------------------------------+ | @@sql_mode | +----------------------------------------------------------------+ | STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION | +----------------------------------------------------------------+ |
2. Reset the sql_mode
to disable the NO_AUTO_CREATE_USER
value, like this (presuming you’ve got the generic MySQL 5.1 settings):
mysql> SET sql_mode='STRICT_TRANS_TABLES,NO_ENGINE_SUBSTITUTION'; |
3. After disabling the restriction, you can grant privileges on a sampledb
to the myuser
user without the IDENTIFIED BY password
clause. You’d use the following syntax:
mysql> GRANT ALL ON sampledb.* TO 'myuser'@'localhost'; |
Before you sign off and sign on to test your new user, you should now query the USER
table. Here’s the query to focus on the three important columns:
mysql> SELECT USER, host, password -> FROM USER -> WHERE USER = 'myuser'; |
If you return more than one row, you made an error and most likely left off the @'localhost'
or used @'%'
. If you made that mistake, you’ll find that disabling the NO_AUTO_CREATE_USER
value, can yield multiple entries in the user
table for the same user. Worse yet, the user no longer can sign in with their password.
+--------+-----------+-------------------------------------------+ | USER | host | password | +--------+-----------+-------------------------------------------+ | myuser | % | | | myuser | localhost | *CBA73BBE5D9AF59311C3F4D7E8C20AA847F7B188 | +--------+-----------+-------------------------------------------+ |
If you were to sign off and attempt to sign on with the myuser
account password, you’d get the following error message:
ERROR 1045 (28000): Access denied FOR USER 'myuser'@'localhost' (USING password: YES) |
You can now sign on as the myuser
user only without a password. Fixing it is simple, just drop the user for any host:
mysql> DROP USER 'myuser'@'%'; |
While disabling the sql_mode
parameters makes your job of granting privileges easier, it risks compromising your database. Therefore, avoid it or be careful. If I missed something or got it wrong, let me know.
MySQL Upload from CSV
You have one approach to uploading CSV file data into a MySQL database. You use the LOAD
statement. There two caveats about using the LOAD
command, and they are:
- You must convert dates to the default MySQL format – YYYYMMDD, or the four digit year, two digit month, and two digit day.
- You can’t use
AUTO_INCREMENT
from the source to the loading table.
It’s untrue that you must input dates as strings and convert them in the database. However, you can do that if you like. I’d suggest you open the file in Microsoft Excel, then create a column with the correct data format. You can do that by creating a custom format, or with a formula. I figure the custom format is easiest but sometimes a formula is necessary for applications.
Excel Formula to Convert a Date to MySQL Format ↓
This is an example formula that’ll convert any supported Excel date format into a CSV-compliant MySQL date string.
The formula is large because MySQL can’t process a date when a one character month or day occurs in the string. You can find more on the logic of the Excel mega formula in this blog post.
=CONCATENATE(CONCATENATE("",YEAR(A1)),IF(LEN(MONTH(A1)),CONCATENATE("0",MONTH(A1))),IF(LEN(DAY(A1)) = 1,CONCATENATE("0",DAY(A1)),CONCATENATE("",DAY(A1)))) |
You should note that loading a date at the end of a line has problems in MySQL. The easiest fix that I’ve found is to place a comma at the end of each line. In a Microsoft world, that eliminates the need for the \r
from the LINES TERMINATED BY
clause.
While this shows a trivial amount of data, here’s a CSV file to use in the test case. Create a directory like Data
off your base logical drive or mount point. Then create a file named transaction_upload.csv and put the following data set in it.
20090102,5 20091231,5 20091128,5 20090616,5 |
Create an importing.sql
file in the C:\Data
folder with the contents below. It’s only a recommendation but generally import tables aren’t useful in between uploads, and that’s why I create the table as an in-memory table. In-memory tables are dropped when you recycle (boot) the MySQL instance.
-- Conditionally drop the table. DROP TABLE IF EXISTS transaction_upload; -- Create the new upload target table. CREATE TABLE transaction_upload ( transaction_date DATE , transaction_amount FLOAT ) ENGINE=MEMORY; -- Load the data from a file, don't forget the \n after the \r on Windows or it won't work. LOAD DATA INFILE 'c:/Data/mysql/transaction_upload.csv' INTO TABLE transaction_upload FIELDS TERMINATED BY ',' ENCLOSED BY '"' ESCAPED BY '\\' LINES TERMINATED BY '\r\n'; -- Select the uploaded records. SELECT * FROM transaction_upload; |
Before you connect as anything other than the root account, you’ll need to grant the global FILE
privilege to the targeted user. This is true because you’ve not qualified a LOCAL
file in the import statement. Users that have all privileges on a given database also have read-write access to LOCAL
tables, which is read write to client-side files. When the LOCAL
key word is omitted, you’re instructing a read from the server by the client tool. You must therefore grant a global permission to enable a connected user can call a server-side file.
If you want to import without granting any additional global permissions, you can rewrite the statement like this:
LOAD DATA LOCAL INFILE 'c:/Data/mysql/transaction_upload.csv' INTO TABLE transaction_upload FIELDS TERMINATED BY ',' ENCLOSED BY '"' ESCAPED BY '\\' LINES TERMINATED BY '\r\n'; |
If you don’t want to rewrite the statement, you’ll get the following error:
ERROR 1045 (28000): Access denied FOR USER 'student'@'localhost' (USING password: YES) Empty SET (0.01 sec) |
Grant Global File Permission ↓
This shows you how to grant a global file permissions to a restricted user. It is only necessary when you want the user to read or write server-side files.
C:\Data> mysql -uroot -pcangetin -P3306 |
Then, grant the global privilege to the user:
mysql> GRANT FILE ON *.* TO 'student'@'localhost' IDENTIFIED BY 'student'; |
Now, you can connect to the data base as the restricted user and read an external server-side file:
C:\Data> mysql -ustudent -pstudent -P3306 |
Once you’ve granted these permissions, the user is no longer truly a restricted user. I’d strongly discourage doing this if the user is accessed via web applications.
Run the script:
mysql> \. importing.sql |
Then, you should see the following:
+------------------+--------------------+ | transaction_date | transaction_amount | +------------------+--------------------+ | 2009-01-02 | 5 | | 2009-12-31 | 5 | | 2009-11-28 | 5 | | 2009-06-16 | 5 | +------------------+--------------------+ 4 rows in set (0.00 sec) |
Another quick tidbit, dates can be tricky when they’re the last column in the file. The best solution is to put a comma at the end of each string. If you put the comma at the end of each line, you can also dispense with the \r
on the Windows platform.
MySQL Stored Procedure
A friend, who’s a MySQL-only developer, asked me a question that I couldn’t resist sharing. He wanted to know how to simplify his PHP code by making a single call to the database for a set of inserts to multiple tables.
You can imagine my delight when I got to say a stored procedure. He said, “It’s not Oracle; that won’t work.” I said, “MySQL supports stored procedures too.” Then, he asked how do you do that. Since the reference manual is a bit sparse on that, here’s a quick example.
Here are the detailed steps, even though there are some other blog entries with more information on these topics.
1. Sign on as the root user, if you need more on that go here. Create a database – storeddb
:
CREATE DATABASE storeddb; |
2. Create a user and grant them permissions:
mysql> CREATE USER 'student' IDENTIFIED BY 'student'; mysql> GRANT ALL ON storeddb.* TO 'student'@'localhost' IDENTIFIED BY 'student'; |
3. Create a create_mysql_procedure.sql
file with the following contents:
-- Select the database. USE storeddb; -- Conditionally drop the objects to make this rerunnable. DROP TABLE IF EXISTS a; DROP TABLE IF EXISTS b; DROP PROCEDURE IF EXISTS double_insert; -- Create the tables. CREATE TABLE a ( a_id INT UNSIGNED PRIMARY KEY AUTO_INCREMENT , a_text CHAR(12)); CREATE TABLE b ( b_id INT UNSIGNED PRIMARY KEY AUTO_INCREMENT , a_id INT UNSIGNED , b_text CHAR(12)); -- Reset the execution delimiter to create a stored program. DELIMITER $$ -- Create a stored procedure. CREATE PROCEDURE double_insert ( input_a CHAR(12), input_b CHAR(12)) BEGIN START TRANSACTION; INSERT INTO a VALUES (NULL, input_a); INSERT INTO b VALUES (NULL, last_insert_id(), input_b); COMMIT; END; $$ -- Reset the delimiter to the default. DELIMITER ; -- Declare a couple local session variables. SET @text1 = 'This is one.'; SET @text2 = 'This is two.'; -- Call the local procedure. CALL double_insert(@text1,@text2); SELECT * FROM a; SELECT * FROM b; |
4. Quit the session as the root user.
mysql> QUIT; |
5. Sign on as the student
user.
C:\> mysql -ustudent -pstudent |
6. As the student
user, source the file. You have two ways to do that. One leverage csh/Tcsh
shell syntax and the other uses Bourne, Korn, or BASH
shell syntax.
6(a). The csh/Tcsh
syntax:
mysql> SOURCE create_mysql_procedure.sql |
6(b). The Bourne, Korn, or BASH syntax:
mysql> \. create_mysql_procedure.sql |
When you source it, you should see the following. Don’t worry if you see the three warnings because when you rerun the script they won’t be there. There telling you that the tables didn’t exist to be dropped the first time.
DATABASE changed Query OK, 0 ROWS affected, 1 warning (0.00 sec) Query OK, 0 ROWS affected, 1 warning (0.00 sec) Query OK, 0 ROWS affected, 1 warning (0.00 sec) Query OK, 0 ROWS affected (0.14 sec) Query OK, 0 ROWS affected (0.00 sec) Query OK, 0 ROWS affected (0.08 sec) Query OK, 0 ROWS affected (0.00 sec) Query OK, 0 ROWS affected (0.00 sec) Query OK, 0 ROWS affected (0.09 sec) +------+--------------+ | a_id | a_text | +------+--------------+ | 1 | This IS one. | +------+--------------+ 1 ROW IN SET (0.00 sec) +------+------+--------------+ | b_id | a_id | b_text | +------+------+--------------+ | 1 | 1 | This IS two. | +------+------+--------------+ 1 ROW IN SET (0.00 sec) |
A quick note to those new to automatic numbering in MySQL. You use a null
when you don’t want to write an override signature for the INSERT
statement. You can find more on SQL Automated Numbering for Oracle, MySQL, and SQL Server in this blog post.
7. Write the following PHP program, and name it call_mysql_procedure.php
. We’re going to call it from the command line, but you shouldn’t have to modify it when you call it from a browser.
<?php // Attempt to connect to your database. $c = @mysqli_connect("localhost", "student", "student", "storeddb"); if (!$c) { print "Sorry! The connection to the database failed. Please try again later."; die(); } else { // Initialize a statement in the scope of the connection. $stmt = mysqli_stmt_init($c); // Declare two variables for the test procedure call. $val1 = "Hello Hal!"; $val2 = "Hello Dave!"; // Set the call statement, like a SQL statement. $sql = "CALL double_insert(?,?)"; // Prepare the statement and bind the two strings. if (mysqli_stmt_prepare($stmt, $sql)) { mysqli_stmt_bind_param($stmt, "ss", $val1, $val2); // Execute it and print success or failure message. $success = mysqli_stmt_execute($stmt); if ($success) { print "Congrats! You've executed a MySQL stored procedure from PHP!"; } else { print "Sorry, I can't do that Dave..."; } } } ?> |
8. Run the call_mysql_procedure.php
from the command line, like this:
php call_mysql_procedure.php |
You should see the following message:
Congrats! You've executed a MySQL stored procedure from PHP! |
9. You can now connect to the MySQL database storeddb
and re-query the tables a
and b
. You should see the following, which tells you that your PHP code worked.
mysql> SELECT * FROM a; +------+--------------+ | a_id | a_text | +------+--------------+ | 1 | This IS one. | | 2 | Hello Hal! | +------+--------------+ 2 ROWS IN SET (0.00 sec) mysql> SELECT * FROM b; +------+------+--------------+ | b_id | a_id | b_text | +------+------+--------------+ | 1 | 1 | This IS two. | | 2 | 2 | Hello Dave! | +------+------+--------------+ 2 ROWS IN SET (0.00 sec) |
I guess Alice that writing PL/SQL may actually be portable to other databases? Actually, there are some severe limits on loops in MySQL’s implementation but you can move basic stored procedures as of MySQL 5.1. Officially, MySQL 5.0 supports them.
As always, I hope this helps some folks.
LAMPs for the Mac
LAMP the ubiquitous acronym for Linux, Apache, MySQL, and PHP, Python, or Perl has a couple cousins. They are OPAL (Oracle’s stack on Linux, and MAMP (Mac OS X, Apache, MySQL, and PHP et cetera). Perhaps another acronym on the horizon is: OPAM (Oracle, PHP, Apache, and Mac OS X). OPAM is a guess on my part. Nobody knows what Oracle’s marketing department may choose. Regardless of the acronym for it, Oracle has published instructions for configuring an Oracle/PHP stack on Mac OS X.
I generally configure the OPAL stack with Zend Core for Oracle and the Oracle database on a virtual machine running Windows XP, Windows Vista, Ubuntu, or Red Hat Linux. If you follow my posts I prefer VMWare Fusion over Parallels. The MAMP stack I use is open source and provided by Living E. It follows the pattern of Mac OS X installations, which differs from the recent posting from Oracle. It’s easy to install, as you tell from the documentation. MAMP installs PHP 5.2.6 as the current release.
It’s a great choice when you incorporate the open source Sequel Pro tool. Isn’t it ashame that Sequel Pro doesn’t work natively with Oracle. If I find some time this summer, it might make a great project to extend it to Oracle. The interface to Sequel Pro looks like this:
When you create a connection, you should know the typical values. The database value can be left blank when connecting as the superuser root
:
Host: localhost User: root Password: root Database: Socket: /Applications/MAMP/tmp/mysql/mysql.sock Port: 3306 |
Here’s the connection dialog where you’ll enter the values:
Have fun playing with the MAMP stack.