MacLochlainns Weblog

Michael McLaughlin's Technical Blog

Site Admin

Archive for the ‘MySQL’ Category

MySQL 5.0 migration bug

without comments

At present, you can’t use the MySQL Workbench migration tool to migrate MySQL 5.0 to MySQL 5.5, as documented in Bug 66861. The only documentation reference that I could find that references the mysql.proc table. Since the physical definition of the mysql.proc table changes across the MySQL 5.0, 5.1, and 5.6 releases, I modified my documentation Bug 66886 to suggest providing online documentation (as a feature request) for the mysql, information_schema, and performance_schema tables across all releases.

The actual definition of the mysql.proc table for MySQL 5.0.91 holds 16 columns not 20 columns as presently expected by the MySQL Workbench migration tool, and is summarized below:

Field Type Null Key
db char(64) NO PRI
name char(64) NO PRI
type enum(‘FUNCTION’,’PROCEDURE’) NO PRI
specific_name char(64) NO  
language enum(‘SQL’) NO SQL
sql_data_access enum(‘CONTAINS_SQL’,…) NO CONTAINS_SQL
is_deterministic enum(‘YES’,’NO’) NO NO
security_type enum(‘INVOKER’,’DEFINER’) NO DEFINER
param_list blob NO  
returns char(64) NO  
body longblob NO  
definer char(77) NO  
created timestamp NO CURRENT_TIMESTAMP
modified timestamp NO 0000-00-00 00:00:00
sql_mode set(‘REAL_AS_FLOAT’,…) NO  
comment char(64) NO  

I found out about the issue through a comment on my blog from Marc, who was trying to migrate his production instance. I hope this provides a heads-up to anybody else attempting to migrate a MySQL 5.0 database to a MySQL 5.5. The good news is that the MySQL Workbench team appears to be actively working the issue.

Written by maclochlainn

September 20th, 2012 at 12:47 am

Bulk Transfer Works

with 9 comments

As many already know, I’ve been trying to get the MySQL Workbench migration feature working between Microsoft SQL Server 2012 and MySQL 5.5. There are a number of features added to the 5.2.43 point release, and one led me to believe that the Migration tool expects to find the data in a schema of its own, as opposed to the dbo schema. Having made that change in Microsoft SQL Server, it did appear to have a positive impact on the migration and when I corrected a character set mismatch it worked perfectly!

MySQL Workbench successfully migrated the schema and table but failed to migrate the data because of a character set mismatch. I updated Bug 66516 the log file from the character set mismatch before I retyped all 9 test rows to make sure they were in a latin1 character set.

I shortened the original log file because the actual log had over 2,000 blanks line :-(. That’s probably something that should be fixed in the code too.

Starting...
Prepare information for data copy...
Prepare information for data copy done
Create shell script for data copy...
Table copy script written to C:\Users\McLaughlinM\Desktop\copy_migrated_tables.cmd
Create shell script for data copy done
Determine number of rows to copy....
Counting number of rows in tables...
 
wbcopytables.exe --count-only --passwords-from-stdin --odbc-source=DSN=SQL Server ODBC;DATABASE=;UID=sa --table [studentdb] [studentdb].[conquistador]
18:29:13 [INF][      copytable]: Opening ODBC connection to 'DSN=SQL Server ODBC;DATABASE=;UID=sa;PWD=XXX'
18:29:14 [INF][      copytable]: ODBC connection to 'DSN=SQL Server ODBC;DATABASE=;UID=sa;PWD=' opened
 
9 total rows in 1 tables need to be copied:
- [studentdb].[studentdb].[conquistador]: 9
Determine number of rows to copy finished
 
Copy data to target RDBMS....
 
Migrating data...
 
wbcopytables.exe --odbc-source=DSN=SQL Server ODBC;DATABASE=;UID=sa --target=student@mclaughlinsql:3306 --progress --passwords-from-stdin --thread-count=1 --table [studentdb] [studentdb].[conquistador] `studentdb` `conquistador` [conquistador_id], [conquistador], [actual_name], [nationality]
`studentdb`.`conquistador`:Copying 4 columns of 9 rows from table [studentdb].[studentdb].[conquistador]
 
ERROR: `studentdb`.`conquistador`:Inserting Batch: Incorrect string value: '\x9Acak' for column 'actual_name' at row 7
`studentdb`.`conquistador`:Finished copying 0 rows in 0m00s
 
29:15 [INF][      copytable]: Opening ODBC connection to 'DSN=SQL Server ODBC;DATABASE=;UID=sa;PWD=XXX'
29:16 [INF][      copytable]: ODBC connection to 'DSN=SQL Server ODBC;DATABASE=;UID=sa;PWD=' opened
29:16 [INF][      copytable]: Connecting to MySQL server at mclaughlinsql:3306 with user student
29:16 [INF][      copytable]: Connection to MySQL opened
 
Copy helper has finished
 
Data copy results:
- `studentdb`.`conquistador` has FAILED (0 of 9 rows copied)
0 tables of 1 were fully copied
 
Copy data to target RDBMS finished
Tasks finished with warnings and/or errors, view the logs for details
Finished performing tasks.

Originally, I thought the failure was due to the extended ASCII characters in the Microsoft SQL Server table. It still failed when I took all of the extended characters out. However, Alfredo suggested it was a character set issue, which is obvious when I looked more closely at the log – '\x9Acak' is clearly an incorrect string. I retyped the INSERT statement for the nine rows and it worked perfectly. Naturally, I’ve updated open Bug 66516 with the log file.

If you’re curious about the Microsoft SQL Server configuration check this post.

Written by maclochlainn

September 16th, 2012 at 6:55 pm

UTOUG Fall Symposium

without comments

Heading out to speak on Comparative SQL between Oracle and MySQL at the Utah Oracle User’s Group Fall Symposium. I’ll look forward to meeting old and new friends. Also, I’ve got a couple promotional copies of my Oracle Database 11g and MySQL 5.6 Developer Handbook to give out at the end of the session.

I’m also presenting Monday evening at Oracle Develop during Oracle Open World 2012. I’ll also have copies of the Oracle Database 11g PL/SQL Programming and Oracle Database 11g PL/SQL Programming Workbook books to give away at the end of the session. Naturally, McGraw-Hill wants me to stop by the bookstore for a signing. If you have time and are in Moscone West, stop by and visit.

Naturally, I’m also attending MySQL Connect to see the brilliant sessions being presented. Hope to see you in San Francisco! :-)

Written by maclochlainn

September 5th, 2012 at 1:56 pm

Posted in MySQL,Oracle

Hostname Change Error

without comments

While staging to rebuild the Oracle DB Console (Oracle Enterprise Manager – OEM), I needed to check something in my MySQL instance and ran into the following error after changing the machine’s hostname for that OEM test. The message basically says that MySQL Workbench can’t resolve the connection.

The dialog error provides an excellent note, which lists the actual error as the first thing to check. The dialog follows:

This lists the text of the error dialog:

Your connection attempt failed for user '<user_name>' from your host to server at <server_name>:3306:
  Unknown MySQL server host '<server_name>' (0)
 
Please:
1 Check that mysql is running on server <server_name>
2 Check that mysql is running on port 3306 (note: 3306 is the default, but this can be changed)
3 Check the student has rights to connect to <server_name> from your address (mysql rights define what clients can connect to the server and from which machines)
4 Make sure you are both providing a password if needed and using the correct password for <server_name> connecting from the host address you're connecting from

Navigate to Database -> Manage Connections… in the menu. This opens a dialog where you can manage the details of a connection.

It opens the following dialog page:

The Test button lets you retest whether the connection works. The error message raised by the test, noted below, is not nearly as detailed as the previous error message.

Inspecting the Parameters tab below, you see the hostname value McLaughlinMySQL. The actual hostname is mclaughlinsql. You need to replace the displayed value with the correct value.

After making the change, click the Test Connection button. A correct change prompts you for the user’s password and when successfully entered yields the following dialog.

Hope this helps a few folks resolve the problem when the machine hostname changes.

Written by maclochlainn

September 2nd, 2012 at 10:23 pm

MySQL Workbench Limit

with 6 comments

Working with MySQL Workbench, I was always curious why you couldn’t run a script with a sourcing command, like source or \. command. It raises a 1064 error code, like the one shown in the illustration.

It turned out that there’s a pending feature request to add the ability to run a sourcing command like the following:

SOURCE c:\DATA\some_script.SQL

or,

\. c:\DATA\some_script.SQL

I added my business reason to the bug. Let’s hope the product managers add it quickly.

Written by maclochlainn

September 1st, 2012 at 11:59 pm

Posted in MySQL,MySQL Workbench

Tagged with ,

MySQL Workbench Scripts

with 2 comments

It’s always interesting when somebody asks why they got an error message, and especially sweet when you’re working on something related that lets you answer the question. They were using MySQL Workbench and wanted to know why they couldn’t open a SQL script file by clicking on the Scripting menu option.

As I explained to the individual who asked, you should always click the Edit SQL Script link in the SQL Development section of the MySQL Workbench home page to work on SQL scripts. The Scripting menu option supports Python and Lua plug-ins development and scripts.

They did the following initially, which led down the rabbit warren and left them stumped because they don’t know anything about Python or Lua. This is provided to those who choose to experiment with this advanced feature of MySQL Workbench.

That presents you with a chooser dialog and it lets you pick any type of file. (You may wonder, as I did, why they didn’t restrict it to .py and .lua file extensions, which would preclude opening a .sql file. I actually logged an enhancement request to see if the development team may agree with me.) You get the following message when you choose something other than a Python or Lua script. You can click on any of the reduced size screen shots to enlarge them and make them readable.

As you may note, the dialog says the activity is unsupported by provides no cancellation button. Click the OK button and the unsupported file is loaded into a tab that is useless. All you can do is click to close the tab and dismiss the window.

After you dismiss (by clicking the x) the non-editable .sql file, you need to click on the Open Script file icon shown below.

This chooser really should open where the default is for the MySQL Workbench application script files but it doesn’t. It opens in the last accessed directory. You need to navigate to where your Python or Lua scripts are stored, which is the following directory on Windows:

C:\Users\<user_name>\AppData\Roaming\MySQL\Workbench\scripts

Please note that on a Windows system you can’t chose this directory option because it’s protected. You must enter the navigation bar and type it. Then, you should see any scripts that you saved from within MySQL Workbench.

The ReadFile.py below contains a rather simplistic and static program that reads a file and prints it to console (it’s small and fits in the screen). Obviously, it dispenses with a bunch to keep it small but check a Python website or book for the right way to manage a try block and handle exceptions.

Here’s the ReadFile.py file shown in the preceding and next screen shots. For those new to Python, watch out because tabs aren’t equivalent to spaces. I made a change in the script below to display the trailing semicolon because one of my students asked about it.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
# -*- coding: utf-8 -*-
# MySQL Workbench Python script
# ReadFile.py
# Written in MySQL Workbench 5.2.41
 
import os
f = open("c:\\Data\\MySQL\\query.sql",'rU')
while True:
    line = f.readline()
    # Parse string to avoid reading line return.
    if not line[len(line) - 1:len(line)] == ";":
      print(line[0:len(line) - 1])
    else:
      print(line)
    if not line:
        break

Life’s funny, and you can never please everyone. The latest question, “Why did I choose to use substrings when suppressing line returns from the print() function is easier?” Simple answer because the approach differs between Python 2.7 and 3.0 and I didn’t want this post to have a lot of Python nuance.

Python 2.7 (compatible with MySQL Workbench 5.2):

1
2
3
4
5
6
7
8
9
import os
f = open("c:\\Data\\MySQL\\query.sql",'rU')
while True:
    line = f.readline()
    # Suppress line return.
    print(line),
    if not line:
        print
        break

Python 3.0 (not-compatible with MySQL Workbench 5.2)

You should take note that both version require a print statement on line #8. Line #6 above shows that Python 2.7 uses a comma to suppress the line return, and below line #6 shows Python 3 requires you set end equal to an empty string. Line #8 below also has a set of empty parentheses, which works in Python 3.x but not in Python 2.7. Python 2.7 would print the parentheses unless you put an empty string inside of them, like a print('') statement.

1
2
3
4
5
6
7
8
9
import os
f = open("c:\\Data\\MySQL\\query.sql",'rU')
while True:
    line = f.readline()
    # Suppress line return.
    print(line, end = '')
    if not line:
        print()
        break

Hopefully, everyone concurs the parsing was simpler than explaining all these Python nuances. Although, it’s nice somebody was so curious.

If your script complies with the Python 2.7 rules (that’s what is deployed in MySQL Workbench), click the lighting bolt and your code will run and display the results. That’s shown in the last screen shot.

If you’re interesting in developing plug-ins, check this summary page or this nice example of executing a query to text. Although, rumor has it that certain features may mature over the next year …

Naturally, I hope this helps those experimenting but personally it’s a cool advanced feature of the MySQL Workbench.

Written by maclochlainn

August 18th, 2012 at 4:57 pm

Oracle and Java Tutorial

without comments

I’m posting this because of a question raised against this older post on how to configure the %CLASSPATH% to find the ojdbc6.jar file. This is the lab file I use in my Database 1 class to expose students to the moving parts of writing Java programs against the Oracle database. That’s why I choose to use a CLOB data type, which requires Oracle’s DBMS_LOB package and wrapping stored procedures.

If you want the same content for MySQL, here’s the link. The full program in either blog entry is available by clicking on the fold/unfold Java Source Code Program widget at the bottom of the respective posts.

This demonstrates how to create an Java infrastructure for reading and writing large text files to an Oracle database. The example provides:

  • A FileIO.jar library that lets you enter Oracle connection parameters through a JOptionPane, and a customized JFileChooser to filter and read source files from the file system.
  • A ojdbc6.jar file, which is Oracle’s library for JDBC communication with the Oracle Databases.

The steps to compiling and testing this code are qualified below:

  1. Download and install the Java Software Development Kit (JSDK) for Java 6.
  2. Create a C:\JavaTest folder on Windows, or a /JavaTest directory from some mount point of your choice.
  3. Download and position the ojdbc6.jar and FileIO.jar files in the JavaTest directory.
  4. Create a batch file to source your environment path (%PATH% on Windows and $PATH on Linux or Mac OS X) and the two Java Archive (JAR) files. A sample batch file is noted below:
set PATH=C:\Program Files\Java\jdk1.6.0_07\bin;%PATH%
set CLASSPATH=C:\JavaDev\Java6\ojdbc6.jar;C:\JavaDev\Java6\FileIO.jar;.

You can run this file by simply typing the files first name. On Linux or Mac OS X, you first need to grant it privileges with the chmod command as 755.

  1. Copy the WriteReadCLOB.java code from the bottom of this posting and also put it into the JavaTest directory.
  2. Compile the WriteReadCLOB.java source code with the javac utility, as shown below:
javac WriteReadCLOB.java

After you compile it, you should run it as follows:

java WriteReadCLOB
  1. Before running the code, you’ll need to seed (INSERT) a row that meets the desired hard coded criteria. It requires an ITEM_TITLE value of 'The Lord of the Rings - Fellowship of the Ring' and an ITEM_SUBTITLE of 'Widescreen Edition' in the ITEM table.
  2. When it runs, you’ll see the following tabbed JOptionPane.

You need to enter the following values before clicking the OK button:

  • Host: The hostname of your machine.
  • Port: The port that the Oracle Listener is running on (the default value is 1521).
  • Database: The Oracle TNS Alias, which is orcl for the full database sample database or xe for the Oracle Database 10g Express Edition.
  • UserID: The user (schema) name where you’ve created an ITEM table.
  • Password: The password for the user’s account.

In the JFileChooser, select a file to upload to the database.

You should see what you uploaded displayed in a JFrame.

Written by maclochlainn

August 1st, 2012 at 12:25 pm

Delay or synchronize it?

with one comment

A couple students in one of my classes ran into a problem when competing Java threads tried to insert new rows in a table. They raised an error when they tried the DELAY keyword to avoid the race (collision) condition in an INSERT statement. It was simple to explain to them that the DELAY keyword doesn’t work with an InnoDB table. Any attempt throws the following error:

ERROR 1616 (HY000): DELAYED OPTION NOT supported FOR TABLE 'message'

Important Update: INSERT DELAYED is gone in MySQL 5.6.6 (announcement) and the whole issue comes down to synchronizing threads (some dislike the solution) or using the ON DUPLICATE KEY clause.

They retested their Java application after redefining the target table using the MyISAM engine. They found it worked but that’s a bad fix in Java (a brief Java/MySQL tutorial post). They really needed to synchronize the Java thread (line #22), leave out the DELAY keyword, and manage the table with the InnoDB engine. Here’s the modified Java code (by the way, they named their project VulcanTech if you’re wondering about the packages in the import statement):

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
package vulcantech.vth.server.commands;
 
import java.io.IOException;
import java.net.Socket;
 
import vulcantech.vth.server.combeans.MessageBean;
 
public class MessageHandler implements Handler {
 
  @Override
  public void handleIt(Object... args) {
 
    MessageBean message = (MessageBean) args[0];
    Socket sock = (Socket) args[1];
 
    DatabaseConnection dbconnection = new DatabaseConnection();
 
    String update = new String("INSERT INTO message(message_timestamp, sender, recipient, message, checked) VALUES(\'" 
                  + message.getTimeStamp() + "\', \'" + message.getSender() + "\', \'" 
                  + message.getRecipient() + "\', \'" + message.getMessage() + "\', b\'0\')");
 
    synchronized (this) {
      dbconnection.executeUpdate(update);
    }
 
    try {
      sock.getOutputStream().write(1);
    } catch (IOException e) {
      e.printStackTrace();
    }
    dbconnection.close();
  }
}

Hope this helps those who encounter race conditions against MySQL when you’re writing Enterprise Java Beans (EJBs).

Written by maclochlainn

July 22nd, 2012 at 3:53 pm

PHP for MySQL Striped View

with 5 comments

Back in May I explained how to create MySQL striped views with session variables. A couple folks wanted to know how to implement them through PHP programs. The trick is sharing the same connection between a call to the function before a query against the striped view.

I’ve updated the MySQL example beyond the Hobbit analogy from the prior post. It now uses the following database components:

  • An APPLICATION_USER table
  • A striped AUTHORIZED_USER view
  • A FUNCTION_QUERY view to optimize function calls
  • A SET_LOGIN function
  • A GET_LOGIN_ID function
  • A GET_USER_ID function

The definition of the APPLICATION_USER table is:

CREATE TABLE application_user
( user_id  int(10) unsigned PRIMARY KEY AUTO_INCREMENT
, user_name  varchar(20) NOT NULL
, user_role  varchar(20) NOT NULL
, user_group_id  int(10) unsigned NOT NULL
, user_type  int(10) unsigned NOT NULL
, first_name  varchar(20)
, middle_name  varchar(20)
, last_name  varchar(20)
, created_by  int(10) unsigned NOT NULL
, creation_date  datetime NOT NULL
, last_updated_by  int(10) unsigned NOT NULL
, last_update_date  datetime NOT NULL
, CONSTRAINT natural_key UNIQUE (user_name)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=latin1;

You should note that the natural key is a user-defined user name (mind you in reality it is often set by the application administrator). This guarantees that the authorize_cursor in the set_login function below always returns only one row.

The following seeds five rows in the APPLICATION_USER table:

INSERT INTO application_user VALUES
 ( null, 'potterhj', 'System Admin', 2, 1, 'Harry', 'James', 'Potter', 1, NOW(), 1, NOW())
,( null, 'weasilyr', 'Guest', 1, 1, 'Ronald', null, 'Weasily', 1, NOW(), 1, NOW())
,( null, 'longbottomn', 'Guest', 1, 1, 'Neville', null, 'Longbottom', 1, NOW(), 1, NOW())
,( null, 'holmess', 'DBA', 3, 1, 'Sherlock', null, 'Holmes', 1, NOW(), 1, NOW())
,( null, 'watsonj', 'DBA', 3, 1, 'John', 'H', 'Watson', 1, NOW(), 1, NOW());

Before creating the striped view, you should create the functions that set the session variables and query them. The set_login function sets two session variables when successful, which requires a user name that matches a valid value in the user_name column of the application_user table. The function returns an integer of 1 on success and 0 on failure. The set_login function code follows.

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
CREATE FUNCTION set_login(pv_login_name VARCHAR(20)) RETURNS INT UNSIGNED
BEGIN
 
  /* Declare a local variable to verify completion of the task:
  || ==========================================================
  ||   a. Default value is zero, which means false.
  ||   b. Non-default value is one, which means true.
  || ==========================================================
  */
  DECLARE  lv_success_flag  INT UNSIGNED  DEFAULT 0;
 
  /* Declare local variables to hold the return values from the cursor. */
  DECLARE  lv_login_id  INT UNSIGNED;
  DECLARE  lv_group_id  INT UNSIGNED;
 
  /* Declare a condition variable for zero rows fetched, selected, or processed. */
  DECLARE  no_rows_fetched  CONDITION FOR 1329;
 
  /* Declare a cursor to return an authorized user id. */
  DECLARE authorize_cursor CURSOR FOR
    SELECT   a.user_id
    ,        a.user_group_id
    FROM     application_user a
    WHERE    a.user_name = pv_login_name;
 
  /* Declare a handler for the cursor when it fails to return a row. */   
  DECLARE EXIT HANDLER FOR no_rows_fetched
    BEGIN
      /* The return statement when the function is aborted through an error. */
      RETURN lv_success_flag;
    END;
 
  /* Check whether the input value is something other than a null value. */
  IF pv_login_name IS NOT NULL THEN
 
    OPEN  authorize_cursor;
    FETCH authorize_cursor INTO lv_login_id, lv_group_id;
    CLOSE authorize_cursor;
 
    /* Set the success flag. */
    SET @sv_login_id := lv_login_id;
    SET @sv_group_id := lv_group_id;
 
    /* Check whether the session variables are set. */
    IF NOT ISNULL(@sv_login_id) AND @sv_login_id > 0 AND
       NOT ISNULL(@sv_group_id) AND @sv_group_id > 0 THEN
      SET lv_success_flag := 1;
    END IF;
 
  END IF;
 
  /* Return the success flag. */
  RETURN lv_success_flag;
END;
$$

The following GET_LOGIN_ID function returns the value from the @sv_login_id variable.

1
2
3
4
5
6
CREATE FUNCTION get_login_id() RETURNS INT UNSIGNED
BEGIN
  /* Return the success flag. */
  RETURN @sv_login_id;
END;
$$

The following GET_GROUP_ID function returns the value from the @sv_group_id variable.

1
2
3
4
5
6
CREATE FUNCTION get_group_id() RETURNS INT UNSIGNED
BEGIN
  /* Return the success flag. */
  RETURN @sv_group_id;
END;
$$

Lastly, you create the MySQL striped AUTHORIZED_USER like this one. It looks ineffective because it includes four function calls to the get_group_id() and one to the get_login_id().

CREATE VIEW authorized_user AS
SELECT   au.user_id
,        au.user_name
,        au.user_role
,        CONCAT(au.last_name,", ",au.first_name," ",IFNULL(au.middle_name,"")) AS full_name
FROM     application_user au
WHERE   (au.user_group_id = 1
AND      au.user_group_id = get_group_id()
AND      au.user_id = get_login_id())
OR       get_group_id() = 2
OR      (get_group_id() > 2
AND      au.user_group_id = get_group_id());

The prior view’s query lets you see the logic for the three types of access. You can eliminate the multiple function calls by using an inline view, like the following in a SQL statement:

CREATE VIEW authorized_user AS
SELECT   au.user_id
,        au.user_name
,        au.user_role
,        CONCAT(au.last_name,", ",au.first_name," ",IFNULL(au.middle_name,"")) AS full_name
FROM     application_user au CROSS JOIN
        (SELECT   get_login_id() AS login_id
         ,        get_group_id() AS group_id) fq
WHERE   (au.user_group_id = 1
AND      au.user_group_id = fq.group_id
AND      au.user_id = fq.login_id)
OR       fq.group_id = 2
OR      (fq.group_id > 2
AND      au.user_group_id = fq.group_id);

Unfortunately, the preceding query raises the following exception if you attempt to put it in a view:

ERROR 1349 (HY000): View's SELECT contains a subquery in the FROM clause

MySQL raises the error because a SELECT statement can’t contain a subquery in the FROM clause, according to the Create View MySQL Reference material.

The solution to the limitation of the CREATE VIEW syntax requires that you breakup the SQL statement into queries, and put them into separate views. The following example shows the function_query view holding the function calls and the authorized_user view cross joining the function_query view.

CREATE VIEW function_query AS
SELECT   get_login_id() AS login_id
,        get_group_id() AS group_id;
 
CREATE VIEW authorized_user AS
SELECT   au.user_id
,        au.user_name
,        au.user_role
,        CONCAT(au.last_name,", ",au.first_name," ",IFNULL(au.middle_name,"")) AS full_name
FROM     application_user au CROSS JOIN function_query fq
WHERE   (au.user_group_id = 1
AND      au.user_group_id = fq.group_id
AND      au.user_id = fq.login_id)
OR       fq.group_id = 2
OR      (fq.group_id > 2
AND      au.user_group_id = fq.group_id);

The following PHP program calls the SET_LOGIN function before querying the AUTHORIZED_USER view. It uses a $_GET global parameter to simplify testing the concept but you should always run parameters through the $_POST global parameter. The $_GET and $_REQUEST global parameters are security risks.

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
84
85
86
87
88
89
90
91
92
93
94
95
<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
 
  // Process the input parameter, which should be through a $_POST argument.
  (isset($_GET['user_name'])) ? $input = $_GET['user_name'] : $input = '';
 
  // 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 dynamic function call.
    $query = "SELECT set_login(?)";
 
    // Attempt preparing statement.
    if (!$stmt = $mysqli->prepare($query)) {
 
      // Print failure to resolve query message.
      print $mysqli->error."<br />";
      print "Failed to resolve query ...<br />";
    }     
    else {
 
      // Bind variable to SQL statement and execute it.
      $stmt->bind_param("s", $input);
      $stmt->execute();
      $stmt->close(); 
    }
 
    // Declare a static query.
    $query = "SELECT au.user_id, au.user_name, au.user_role, au.full_name FROM authorized_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 Name</th><th class="Label">User Role</th><th class="Label">Full 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>

You can call this through a browser with the following type of URL. You have two possible striped values, and they are any user’s unique user name.

http://localhost/stripedquery1.php?user_name=potterhj

It returns one row when the user isn’t in a privileged group, all rows when the user is the root privileged group and all rows for a privilege group when not in the root privileged group. Naturally, you can extend this level of individual and group membership.

You can test this in the web page or directly in MySQL. The MySQL test doesn’t require image files and thereby loads faster, which is why I’ve opted to show it to you that way.

  1. Test for the root privilege group:
SELECT set_login('potterhj');

You get the full five rows:

+---------+-------------+--------------+----------------------+
| user_id | user_name   | user_role    | full_name            |
+---------+-------------+--------------+----------------------+
|       1 | potterhj    | System Admin | Potter, Harry James  |
|       2 | weasilyr    | Guest        | Weasily, Ronald      |
|       3 | longbottomn | Guest        | Longbottom, Neville  |
|       4 | holmess     | DBA          | Sherlock, Holmes     |
|       5 | watsonj     | DBA          | John, Watson H       |
+---------+-------------+--------------+----------------------+
  1. Test for an individual in a non-privileged group:
SELECT set_login('weasilyr');

You get the single user’s row:

+---------+-----------+-----------+------------------+
| user_id | user_name | user_role | full_name        |
+---------+-----------+-----------+------------------+
|       2 | weasilyr  | Guest     | Weasily, Ronald  |
+---------+-----------+-----------+------------------+
  1. Test for a non-root privileged group:
SELECT set_login('holmess');

You get the two rows that belong to the non-root privileged group:

+---------+-----------+-----------+-------------------+
| user_id | user_name | user_role | full_name         |
+---------+-----------+-----------+-------------------+
|       4 | holmess   | DBA       | Holmes, Sherlock  |
|       5 | watsonj   | DBA       | Watson, John H    |
+---------+-----------+-----------+-------------------+

Naturally, it’s more effective to put these components into a function library. The trick to making this work in a session is to share the connection. The object approach to the MySQL Improved (mysqli) object holds the connection, and that’s whats passed in the following example files.

This is an optimistic setter function. It forks (starts) the MySQL SQL/PSM set_login function but it doesn’t wait see if it ran successfully. That’s the nature of optimistic programming solutions, and the principal problem with them.

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
function set_login($mysqli, $user_name) {
 
  // Define return string.
  $return = false;
 
  // Declare a dynamic function call.
  $query = "SELECT set_login(?)";
 
  // Attempt preparing statement.
  if (!$stmt = $mysqli->prepare($query)) {
 
    // Print failure to resolve query message.
    print $mysqli->error."<br />";
    print "Failed to resolve query ...<br />";
  }     
  else {
 
    // Bind variable to SQL statement and execute it.
    $stmt->bind_param("s", $user_name);
    $stmt->execute();
    $stmt->close();
    $return = true;
  }
 
  // Return the string.
  return $return;
}

An optimistic setter function fails to synchronize behaviors between the PHP and MySQL coding levels. It should be rewritten to fork the MySQL SQL/PSM set_login function and evaluate it’s successful or unsuccessful completion, which makes it a pessimistic function.

There are two ways to solve this problem. One can write a wrapper that accesses the get_login_id stored function to confirm the session variable is set, and the other handles the return value from the native set_login_id stored function. The former requires knowledge of the internal workings of the database model, while the latter does not. That means the first is more tightly coupled than the latter.

The following set_login PHP function is rewritten to be pessimistic but dependent on a supplemental call to another get_login PHP function, which calls the get_login_id stored function in the MySQL Server:

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
function set_login($mysqli, $user_name) {
 
  // Define return string.
  $return = false;
 
  // Declare a dynamic function call.
  $query = "SELECT set_login(?)";
 
  // Attempt preparing statement.
  if (!$stmt = $mysqli->prepare($query)) {
 
    // Print failure to resolve query message.
    print $mysqli->error."<br />";
    print "Failed to resolve query ...<br />";
  }     
  else {
 
    // Bind variable to SQL statement and execute it.
    $stmt->bind_param("s", $user_name);
    $stmt->execute();
    $stmt->close();
 
    // True only when query returns a row.
    if (get_login($mysqli)) {
      $return = true; }
  }
 
  // Return the string.
  return $return;
}

A more effective solution reads the return value from the set_login function, 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
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
function set_login($mysqli, $user_name) {
 
  // Define return string.
  $return = false;
 
  // Declare a dynamic function call.
  $query = "SELECT set_login(?)";
 
  // Attempt preparing statement.
  if (!$stmt = $mysqli->prepare($query)) {
 
    // Print failure to resolve query message.
    print $mysqli->error."<br />";
    print "Failed to resolve query ...<br />";
  }     
  else {
 
    // Bind variable to SQL statement and execute it.
    $stmt->bind_param("s", $user_name);
 
    // Attempt query and exit with failure before processing.
    if (!$stmt->execute()) {
 
      // Print failure to resolve query message.
      print $mysqli->error."<br />";
      print "Failed to resolve query ...<br />";
    }     
    else {
 
      // Fetch a row for processing.
      $result = $stmt->get_result();
      $row = $result->fetch_array(MYSQLI_NUM);
    }
 
    // Close the statement cursor.
    $stmt->close();
 
    // True only when query returns a row.
    if (!is_null($row[0]) && ($row[0] > 0)) {
      $return = true; }
  }
 
  // Return the string.
  return $return;
}

This is the getter 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
function get_login($mysqli) {
 
  // Define return string.
  $return = false;
 
  // Declare a dynamic function call.
  $query = "SELECT get_login_id()";
 
  // Attempt preparing statement.
  if (!$stmt = $mysqli->query($query)) {
 
    // Print failure to resolve query message.
    print $mysqli->error."<br />";
    print "Failed to resolve query ...<br />";
  }     
  else {
 
    // Fetch a SQL statement.
    $row = $stmt->fetch_row();
 
    // Close the statement.    
    $stmt->close();
 
    // True only when query returns a row.
    if (!is_null($row[0]) && ($row[0] > 0)) {
      $return = true; }
  }
 
  // Return the string.
  return $return;
}

The get_authorized_user PHP function gets and displays the table result from the authorized_user striped view:

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
function get_authorized_user($mysqli) {
 
  // Define return string.
  $out = '';
 
  // Declare a static query.
  $query = "SELECT au.user_id, au.user_name, au.user_role, au.full_name FROM authorized_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.
      $out .= '<table><tr><th class="ID">ID</th><th class="Label">User Name</th>'
      .       '<th class="Label">User Role</th><th class="Label">Full Name</th></tr>';
 
      // Fetch a row for processing.
      while( $row = $stmt->fetch_row() ) {
 
        // Print the opening HTML row tag.
        $out .= "<tr>";
 
        // Loop through the row's columns.
        for ($i = 0;$i < $mysqli->field_count;$i++) {
 
          // Handle column one differently.
          if ($i == 0)
            $out .= '<td class="ID">'.$row[$i]."</td>";
          else
            $out .= '<td class="Label">'.$row[$i]."</td>";
        }
 
        // Print the closing HTML row tag.
        $out .= "</tr>"; 
      }
    }
  } while( $mysqli->next_result());
 
  // Print the closing HTML table tag.
  $out .= "</table>"; 
 
  // Return an HTML table of the results.
  return $out;
}

This is the modified program using the functions:

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
<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
 
  // Include the credentials file if omitted.
  include_once("striping.inc");
 
  // Process the input parameter, which should be through a $_POST argument.
  (isset($_GET['user_name'])) ? $input = $_GET['user_name'] : $input = '';
 
  // 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 {
 
    // After setting the session variable, get the striped view result. 
    if (set_login($mysqli,$input)) {
 
      // Print the authorized list.
      print get_authorized_user($mysqli);
 
    }
 
    // Release connection resource.
    $mysqli->close();
  }
?>
</script>
</body>
</html>

Hope this helps those implementing MySQL Striped tables.

Written by maclochlainn

July 16th, 2012 at 1:47 am

Posted in MySQL,PHP,Stored Procedures

Tagged with ,

PHP/MySQL Query

without comments

Somebody wanted an example of how to handle column values using PHP to query a MySQL database. While I thought there were enough examples out there, they couldn’t find one that was code complete.

Well, here’s one that works using a static query. If you want to use a prepared statement, check this earlier post.

<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>

It prints the following image:

While you shouldn’t embed CSS, I’ve done it to keep this as simple as possible. You can also use the procedural approach to the MySQLi library, like this:

<html>
<header>
<title>Static Query Procedural 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 = mysqli_connect("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 {
 
    // Initialize a statement in the scope of the connection.
    $stmt = mysqli_stmt_init($mysqli);
 
    // 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($mysqli,$query)) {
 
        // Print failure to resolve query message.
        print mysqli_error($stmt)."<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 = mysqli_fetch_row($stmt) ) {
 
          // Print the opening HTML row tag.
          print "<tr>";
 
          // Loop through the row's columns.
          for ($i = 0;$i < mysqli_field_count($mysqli);$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($mysqli));
 
    // Print the closing HTML table tag.
    print "</table>"; 
 
    // Free system resources.
    mysqli_stmt_free_result($stmt);
 
    // Release connection resource.
    mysqli_close($mysqli); 
  }
?>
</script>
</body>
</html>

It produces the same output as the object oriented approach with one exception the title of the web page.

Hope this helps some folks.

Written by maclochlainn

July 14th, 2012 at 11:59 pm

Posted in LAMP,MAMP,MySQL,PHP

Tagged with , ,