Rod Flohr on the web

One of the nice features of Zend Server for IBM i is that it will start up automatically whenever the IBM i is restarted.  To be more specific, it starts whenever subsystem QSYSWRK is started.  Zend DBi does not provide for automatic start up of the Zend DBi MySQL server daemon.  In a production environment using MySQL, it would be beneficial to have MySQL start up after IPL.  This article suggests a couple of ways to make that happen.

Prerequisites:  Before you set up automatic start up for Zend DBi, you must first install Zend DBi and verify that you can start the MySQL server daemon.  These articles can help with that:

Install MySQL using the Zend DBi installer with Zend Server

Start the Zend DBi MySQL Daemon job and verify it is running

The command to start up the ZENDDBI subsystem and Zend DBI MySQL server daemon is simply this:

strsbs zmysql/zenddbi

It seems from my own testing that any user with *JOBCTL special authority can run this command successfully. Keep in mind this will only work if subsystem ZENDDBI is not already active.  If the subsystem is active but the daemon is not, then a different command is needed:

CALL ZMYSQL/ZMYSQLSTRT

At start up, subsystem ZENDDBI is probably not active, so the strsbs command is the one to use.

Method 1

One way to run the strsbs command at start up is to simply add the ‘strsbs zmysql/zenddbi’ command to the start up program defined by the QSTRUPPGM system value. If this program is QSYS/QSTRUP, you should create a new start up program by retrieving the CL source for QSYS/QSTRUP into another library, adding your commands to it, and then changing the QSTRUPPGM system value to use your new QSTRUP program. You can see documentation for this process here:

Changing the IPL startup program

Method 2

Another way to do it is to add an autostart job entry to a subsystem that already starts automatically at IPL. In the following example, we will use the QSYSWRK subsystem, which is also used by Zend Server for IBM i.

First, create a job description to be used by the autostart job entry:

CRTDUPOBJ OBJ(ZMYSQLJBDA) FROMLIB(ZMYSQL) OBJTYPE(*JOBD) NEWOBJ(ZMYSQLJBDX)

Next, change the request data for the new job description to start the ZENDDBI subsystem, and assign a user profile with *JOBCTL special authority (ZENDADMIN in this example):

CHGJOBD JOBD(ZMYSQL/ZMYSQLJBDX) TEXT('ZendDBi MySQL autostart JOBD')
USER(ZENDADMIN) RQSDTA('QSYS/STRSBS SBSD(ZMYSQL/ZENDDBI)')

Finally, add the autostart job entry to the QSYSWRK subsystem:

ADDAJE SBSD(QSYS/QSYSWRK) JOB(ZDBI_STR) JOBD(ZMYSQL/ZMYSQLJBDX)

Verify the automatic start up

After setting up either method, check after the next few IPLs to make sure the ZENDDBI subsystem and ZENDDBID server daemon are starting up as expected.  From the command line:

wrkactjob sbs(zenddbi)

The output should be this:

               Current                            
Subsystem/Job  User        Type  CPU %  Function  
ZENDDBI        QSYS        SBS      .0            
  ZENDDBID     MYSQL       BCI      .0  PGM-mysqld

The MySQL server is a daemon, meaning it is a constantly running process that communicates via TCP/IP.  In order to use Zend DBi MySQL, the Zend DBi MySQL server daemon must be running.

Prerequisite:  Zend DBi must be installed.  The following article can help with that:

Install MySQL using the Zend DBi installer with Zend Server

To start the Zend DBi MySQL daemon job, please sign on to a 5250 session as QSECOFR or as a *SECOFR class user.  Enter this command:

go zendsvr6/zsmenu

This should bring you to the Zend Server 6 for IBM i Setup Menu.  Please use option “6. ZendDBi Management menu”.  If Zend DBi is installed, this should bring you to the ZendDBi management menu.  (If Zend DBi is not installed, you will be offered the opportunity to install it).  These are the options displayed:

1. Start ZendDBi subsystem 
2. Work with ZendDBi susbsystem

4. Stop ZendDBi subsystem 

6. Start ZendDBi daemon 
7. Stop ZendDBi daemon 

9. Upgrade to ZendDBi

Note:  The menus shown in this article were installed with Zend Server for IBM i version 6.3.0.  There is an earlier version of this menu that does not display all of the same options.  This is what the earlier menu options look like:

1. Start ZendDBi subsystem
2. Stop ZendDBi subsystem 

4. Start ZendDBi daemon 
5. Stop ZendDBi daemon

It’s OK to have the earlier menu.  Please just be aware of the differences as you read through this article.

To start the Zend DBi MySQL daemon when the subsystem is not active, please use option “1. Start ZendDBi subsystem”.  If the subsystem is active but the daemon is not, please use option “6. Start ZendDBi daemon” (option 4 if you have the old menu).  If you are not sure how to tell if the subsystem is active, please keep reading.

To verify that the Zend DBi MySQL daemon job is running, please use option “2. Work with Zend DBi subsystem”.  If you have the old menu, or if you just prefer to use a command:

wrkactjob sbs(zenddbi)

If the job is active, you should see this:

               Current                            
Subsystem/Job  User        Type  CPU %  Function  
ZENDDBI        QSYS        SBS      .0            
  ZENDDBID     MYSQL       BCI      .0  PGM-mysqld

If you see more than one ZENDDBID job, the daemon is still starting up.  Hit F5 a few times to make sure that there is just the one job displayed, and that the job has not disappeared (which would indicate a problem with the start up).  If the display shows the ZENDDBID job, and it does not disappear after hitting F5 a few times, then the daemon is started.  Congratulations!  If the ZENDDBID job does not appear, please continue reading.

If the subsystem is not active, you will see this:

(No active jobs to display)

This is highly unlikely.  If the subsystem does not start, change your job for maximum logging:

CHGJOB LOG(4 00 *SECLVL) LOGCLPGM(*YES)

Then try to start the subsystem from the command line:

strsbs zmysql/zenddbi

If the subsystem fails to start, check your job log for messages, to determine what the problem is, and possibly see some suggested solutions.

If the subsystem is active, but the ZENDDBID job is not active, the ZENDDBI active jobs display will show this:

               Current                            
Subsystem/Job  User        Type  CPU %  Function  
ZENDDBI        QSYS        SBS      .0

If the ZENDDBID job is missing, there is some start up problem.  Logs often show an error that says “mysql.sock not found”.  This is a file that only exists when the ZendDBi MySQL daemon is running.  If it is not found, then that just means the daemon did not start.   To find out why, you will need to:

Troubleshoot Zend DBi MySQL daemon start up failures

To determine if you need to troubleshoot the Zend DBi MySQL start up, you should first try to:

Start the Zend DBi MySQL Daemon job and verify it is running

If the ZENDDBID job will not start, you will often see some message (after a bit of digging around) that states “Object not found. Object is /tmp/mysql.sock.” It seems natural to assume that this file is needed for the start up, and it might be missing because of some problem with the installation, or maybe it got deleted.  It is a natural assumption, but it is wrong.  The mysql.sock file is created whenever the daemon starts, and is deleted whenever the daemon ends.  The start up program checks to see if mysql.sock exists.  If it does, that indicates a successful start up.  If it does not exist, that indicates the start up failed.  If mysql.sock is missing, it just tells us the daemon failed to start.  It does not really tell us why.

So, with that little misunderstanding out of the way, how do we find out what the problem is.  We have ways to get some better error reporting, but before we go through all that, I have found it worthwhile to go through some of the more common issues first.  These issues are very easy to check.  It only takes a few minutes to go through and check them all, and I can honestly not think of one single Zend DBi MySQL start up failure that was not due to one of these, so it is well worth the time to check them out.

My troubleshooting procedure is to go through each of the common problems one at a time, quickly eliminating them until I find the one that is preventing the start up.  Then I fix that problem.  If none of the common problems should turn out to be the issue, I have a couple of steps that can help provide more error information.  None of the following steps is dependent on any other, so they can be done in any order.  This is just the order in which I do them, believing this to be the fastest way, on average, to find and fix the problem.  These issues are also not mutually exclusive.  You can have more than one.  If you fix one problem, but the daemon still will not start, leave the fix in place and continue researching the remaining steps.

1. QGPL in system library list prevents Zend DBi MySQL daemon from starting

2. Port 3306 in use prevents Zend DBi MySQL daemon from starting

3. Missing job description ZMYSQLJBDA prevents Zend DBi MySQL daemon from starting

4. Some problem with mysql.sock prevents Zend DBi MySQL daemon from starting

5. Start Zend DBi MySQL daemon in PASE to review start up errors

6. Maximize job logging for the Zend DBi MySQL daemon start up job

This article presents one of the troubleshooting steps for Zend DBi MySQL daemon start ups, found in this article:

Troubleshoot Zend DBi MySQL daemon start up failures (mysql.sock)

To check this issue, please log on to a 5250 session and enter this command:

dspsysval qsyslibl

The output will be similar to this:

Sequence         
 number   Library
     0           
    10    QSYS   
    20    QSYS2  
    30    QHLPSYS
    40    QUSRSYS
    50    QGPL

If QGPL appears on this list, as in the above example, it’s a problem.  Another indicator you may see for this problem is this message appears when starting the Zend DBi MySQL daemon job from the menu:

CPF1338 received by procedure ZENDDBID. (C D I R)

If QGPL is not in the default system library list and the CPF 1338 message does not appear, this is probably not the problem.  Please return to the troubleshooter to try another step.

If QGPL in the system library list is the problem, here is a way to work around it and get the Zend DBi MySQL daemon running.  To do it, rename the existing start program, then put in a new start program that changes the system library list and calls the old renamed program.  Please sign on to a 5250 session as QSECOFR or a *SECOFR class user.  Then do the following steps.

1. Rename the existing MySQL start up program:

RNMOBJ OBJ(ZMYSQL/ZMYSQLSTRT) OBJTYPE(*PGM) NEWOBJ(ZMYSQLSTRX)

2. Create a new CLP source member for the new start up program. In this example, the source member will be SOMELIB/ZMYSQLSTRT.

3. Put this source in your new source member:

/* This program will fix a problem starting MySQL when QGPL is in the system */
/* library list. */

/*****************************************************************************/
/* IMPORTANT: */
/* First, rename the existing ZMYSQLSTRT program to ZMYSQLSTRX: */
/* */
/* RNMOBJ OBJ(ZMYSQL/ZMYSQLSTRT) OBJTYPE(*PGM) NEWOBJ(ZMYSQLSTRX) */
/* */
/* Only then should you compile this program as ZMYSQL/ZMYSQLSTRT. This */
/* program calls the renamed program. */
/*****************************************************************************/

 PGM 
 CHGSYSLIBL LIB(QGPL) OPTION(*REMOVE) 
 CALL ZMYSQL/ZMYSQLSTRX 
 ENDPGM

4. Create the new start up program (remember to put your library in place of SOMELIB in the following command):

CRTCLPGM PGM(ZMYSQL/ZMYSQLSTRT) SRCFILE(SOMELIB/QCLSRC)

5. To start up successfully with the subsystem, user MYSQL needs authority to use the CHGSYSLIBL command:

GRTOBJAUT OBJ(CHGSYSLIBL) OBJTYPE(*CMD) USER(MYSQL) AUT(*USE)

To start up successfully using option 6 from the ZendDBi management menu, the user signed on to the 5250 session will need use authority to the CHGSYSLIBL command.

6. Because ZMYSQL/ZMYSQLSTRT is a program distributed with Zend DBi, it might be replaced any time an upgrade is done, and will surely be replaced if a reinstall is done. Please make it a policy to test this issue after any update or reinstall, and repeat the above steps if needed.

This article presents one of the troubleshooting steps for Zend DBi MySQL daemon start ups, found in this article:

Troubleshoot Zend DBi MySQL daemon start up failures (mysql.sock)

One reason MySQL may fail to start is if the default MySQL port 3306 is in use.   One way to check if port 3306 is in use is to use the netstat command.  A limitation of netstat is that it can only show jobs using a port, but not tasks.  If port 3306 is in use, we will want to know which job or task is using it.  Navigator can show both jobs and tasks, so this is a better way to check the port.

In Navigator, go to Network -> TCP/IP Configuration -> IPv4 -> Connections. Click the local port column title to sort by local port.  Look for local port 3306 on the list. Right click the line that shows local port 3306. The menu will show Stop, Jobs, Tasks, and Properties. Use the Tasks menu option to display any tasks using the port. Use the Jobs menu option to display any jobs using the port.

In the following example animation, Zend Server 5 has been set up to use port 3306.  This is not something we would normally do.  I just did it to show what it looks like when some job or task uses port 3306:

If port 3306 does not appear on the list of local ports, this is probably not the problem. Please return to the troubleshooter to try another step.

If port 3306 does appear on the list, take a look at the job or task using the port.  Port 3306 is usually reserved for MySQL, so other products using this port were likely set up custom to use this port.  It may be possible to change the other product to use some other port. This is the preferable solution.  Unfortunately, it is often not the most practical solution. The other option is to change the port used by the Zend DBi MySQL daemon.

To change the MySQL daemon port, you will first need to locate an unused port.  The following article can help with this task:

Find an Available Port

Now you need to update the start up shell script, /usr/local/mysql/bin/mysql.sh .

IMPORTANT:  

Before editing this file, or any configuration file, it is always important to make a back up copy.

We do not recommend using the 5250 EDTF command to edit this file, because the first line is longer than can be displayed on a 5250 terminal emulator. The EDTF command can truncate characters at the end of the displayed part of the line, causing them to be lost from the middle of the complete line, if insert is used. If WRKLNK is used to display the file, and then option 2 is used to edit, this is the same as using the EDTF command, so this is also not recommended.

We do not recommend using Notepad or other basic Windows editors to edit this file. The FastCGI configuration uses the Unix/Linux standard of using only the new line character (\n) at the end of each line, while Windows uses a carriage return and new line at the end of each line (\r\n). FastCGI expects the ‘\n’, but Notepad will insert the ‘\r\n’ at the end of each line. As a result, your configuration file will appear to be correct when you view it in the editor, but FastCGI will not start.

Editors that we know of that properly handle the end of line include Zend Studio and Notepad ++ .  In Studio, right click the file name and choose Open With -> Text Editor.  Otherwise, the file may open for editing in a Notepad window, and the end of line will be corrupted when saving the file.

The start up shell script used by Zend DBi is:

/usr/local/mysql/bin/mysql.sh

Please make a back up copy of this file before editing.

In the script, find the mysqld command. It will look something like this:

$BASEDIR/bin/mysqld --defaults-file=${DEFS_FILE} --basedir=${BASEDIR} --datadir=${DATADIR} \
 --port=3306 --user=${USER} --socket=${SOCKET_PATH} \
 --pid-file=${PID_PATH} --tmpdir=${TEMP} 2> /dev/null 1> /dev/null &

In the above example, note the port=3306. Change the port number to the new port.  Don’t change anything else.  In the following example we changed the port to 3307:

$BASEDIR/bin/mysqld --defaults-file=${DEFS_FILE} --basedir=${BASEDIR} --datadir=${DATADIR} \
 --port=3307 --user=${USER} --socket=${SOCKET_PATH} \
 --pid-file=${PID_PATH} --tmpdir=${TEMP} 2> /dev/null 1> /dev/null &

Save your changes. The next time the Zend DBi MySQL daemon is started, it will use the new port.  This should allow the daemon to start successfully.

Things to remember when you change the MySQL port:

When you deploy phpMyAdmin, you will need to know the port that is used for MySQL.

If you are using a third party PHP application that uses MySQL, please check with the vendor to learn how to update the MySQL port.

If you are writing or updating your own application, please remember to specifiy the report in the connection. In the following example, they specify port 3307:

// we connect to example.com and port 3307
$link = mysql_connect(‘example.com:3307’, ‘mysql_user’, ‘mysql_password’);

This example is from Example #2 on the mysql_connect() function documentation page:

http://www.php.net/manual/en/function.mysql-connect.php

This article presents one of the troubleshooting steps for Zend DBi MySQL daemon start ups, found in this article:

Troubleshoot Zend DBi MySQL daemon start up failures (mysql.sock)

To check this issue, please log on to a 5250 session as QSECOFR or a *SECOFR class user and enter this command:

DSPOBJD OBJ(ZMYSQL/ZMYSQLJBDA) OBJTYPE(*JOBD)

If the job description is displayed, this is probably not the problem.  Please return to the troubleshooter to try another step.

If this message is displayed:

Object ZMYSQLJBDA in ZMYSQL type *JOBD not found.

Then this is a problem.  You will need to create the job description.  To assist with this task, here are the attributes for the job description:

 Job description:   ZMYSQLJBDA     Library:   ZMYSQL
 User profile . . . . . . . . . . . . . . . . . . :   MYSQL
 CL syntax check  . . . . . . . . . . . . . . . . :   *NOCHK
 Hold on job queue  . . . . . . . . . . . . . . . :   *NO
 End severity . . . . . . . . . . . . . . . . . . :   30
 Job date . . . . . . . . . . . . . . . . . . . . :   *SYSVAL
 Job switches . . . . . . . . . . . . . . . . . . :   00000000
 Inquiry message reply  . . . . . . . . . . . . . :   *RQD
 Job priority (on job queue)  . . . . . . . . . . :   5
 Job queue  . . . . . . . . . . . . . . . . . . . :   ZMYSQLJBQ
   Library  . . . . . . . . . . . . . . . . . . . :     ZMYSQL
 Output priority (on output queue)  . . . . . . . :   5
 Printer device . . . . . . . . . . . . . . . . . :   *USRPRF
 Output queue . . . . . . . . . . . . . . . . . . :   *USRPRF
   Library  . . . . . . . . . . . . . . . . . . . :
 Message logging:
   Level  . . . . . . . . . . . . . . . . . . . . :   4
   Severity . . . . . . . . . . . . . . . . . . . :   0
   Text . . . . . . . . . . . . . . . . . . . . . :   *NOLIST
 Log CL program commands  . . . . . . . . . . . . :   *YES
 Job log output . . . . . . . . . . . . . . . . . :   *SYSVAL
 Accounting code  . . . . . . . . . . . . . . . . :   *USRPRF
 Print text . . . . . . . . . . . . . . . . . . . :   *SYSVAL
 Routing data . . . . . . . . . . . . . . . . . . :   ZMYSQL
 Request data . . . . . . . . . . . . . . . . . . :   QSYS/CALL ZMYSQL/ZMYSQLSTRT
 DDM conversation . . . . . . . . . . . . . . . . :   *KEEP
 Device recovery action . . . . . . . . . . . . . :   *SYSVAL
 Time slice end pool  . . . . . . . . . . . . . . :   *SYSVAL
 Job message queue maximum size . . . . . . . . . :   *SYSVAL
 Job message queue full action  . . . . . . . . . :   *SYSVAL
 Allow multiple threads . . . . . . . . . . . . . :   *NO
 Initial ASP group  . . . . . . . . . . . . . . . :   *NONE
 Spooled file action  . . . . . . . . . . . . . . :   *SYSVAL
 Text . . . . . . . . . . . . . . . . . . . . . . :   ZendDBi JOBD
                                                       Initial library list:
 Sequence                                    Sequence                                    Sequence
  number   Library                            number   Library                            number   Library
     10    ZMYSQL                                30    QTEMP
     20    QGPL

Here are the object authorities:

                         Object  
User        Group       Authority
*PUBLIC                 *CHANGE  
QSECOFR                 *ALL

This article presents one of the troubleshooting steps for Zend DBi MySQL daemon start ups, found in this article:

Troubleshoot Zend DBi MySQL daemon start up failures (mysql.sock)

When the Zend DBi MySQL daemon job, ZENDDBID, will not start, the job log will often show something like this:

RNM OBJ('/tmp/mysql.sock') NEWOBJ('mysql.sock')
Object not found. Object is /tmp/mysql.sock.

This is how the start up program checks for a successful start.  File /tmp/mysql.sock is a socket file created by the MySQL daemon when it starts, and is removed when the daemon stops.  The socket file should only exist when the daemon is running.  The start up program performs the rename operation, using the same file name so the file will not actually be renamed.  If the file is not found, the rename fails, and the start up program knows the daemon is not running.

While the missing socket file can be because of any issue that prevents the Zend DBi MySQL daemon from starting, one of those issues can be that the daemon cannot write the socket file.  A related issue is one where the /tmp/mysql.sock already exists at start up, preventing the daemon from writing a new one.  In these cases, the “Object not found” message does not appear.

To check for these issues, start by using Navigator, the wrklnk command, a mapped network drive, or the RSE perspective in Zend Studio to look at the /tmp directory to see if the mysql.sock file exists.  Then, depending on whether it exists, go to the appropriate section below. My preference is to use Navigator for this task, because Navigator makes it easy to set permissions, if needed.

1. If the /tmp/mysql.sock file does not exist

In the picture above, file mysql.sock does not exist in the /tmp directory (the file list is sorted by name).  If the file /tmp/mysql.sock does not exist, then there may be a permissions issue.  (Or, some other problem is preventing the start up.  Remember, a missing mysql.sock can indicate simply that the daemon did not start.)  User MYSQL needs to have permissions to write to this directory.  Permissions for the /tmp directory typically look something like this:

Notice that user (Public) has all of the permissions for this folder.  Sometimes permissions for (Public) are set to Exclude, as some feel this is more secure.  In that case, user MYSQL should be assigned all permissions for this folder:

This will allow the ZendDBi MySQL daemon to create the mysql.sock socket file.

If the permissions were OK but the file mysql.sock does not exist, this is probably not the problem.  Please return to the troubleshooter to try another step.

2. If the /tmp/mysql.sock file does exist

In this example, file mysql.sock already exists in the /tmp directory.  This is a problem. There are two possible reasons for this. One is that there is some other installation of MySQL on the IBM i that is currently running. The other is that the ZendDBi MySQL daemon had been started previously, but ended abnormally for some reason.

2.a. Some other MySQL daemon process is running

To determine if some other MySQL daemon is running, please sign on to a 5250 session as QSECOFR or a *SECOFR class user, and run this command:

call qp2term

This brings up the PASE shell.  In the shell, enter this command:

ps ax

This will list all the active PASE processes. They will typically fill more than one screen. You need to page up to scroll back through them all. Look at them all, searching for any occurrence of the mysqld or mysqld_safe command. Here is an example showing the ZendDBi MySQL daemon running:

11242 - A 0:00 /usr/local/ZendSvr/bin/php-cgi.bin 
11243 - A 0:00 /usr/local/ZendSvr/bin/php-cgi.bin 
11281 - A 0:00 /usr/local/mysql/bin/mysqld --defaults-file=/usr/local 
11284 - A 0:00 /QOpenSys/usr/bin/-sh -i

These are just a few of the lines displayed.  Notice process number 11281 is running command /usr/local/mysql/bin/mysqld .  This is the mysqld from ZendDBi.

If mysqld is running from /usr/local/mysql, but the ZENDDBID job is not running, it is possible someone ran mysqld directly from inside the PASE shell, rather than letting the job run it.  They may have done this for diagnostic purposes.  Or, an older version of MySQL distributed by Zend might be running in the ZMYSQL subsystem.  It is less likely that some other version of MySQL is running out of this directory, since that would have given some trouble during the install.  You can stop the process using the kill command in PASE to end the process by number.  In this example the process number is 11281:

kill 11281

Run ps ax again to verify that the process is no longer present.  If it is still there, wait a few minutes and check again.  If it will not die in a reasonable amount of time, please try this:

kill -9 11281

The ‘-9’ sends the SIGKILL signal, “which usually cannot be ignored or overridden“.  It is sort of like killing a job with the ENDJOBABN command.

If you kill the process, you may need to delete the mysql.sock socket file manually (see 2.b. below).  This should allow the Zend DBi MySQL daemon to start.

If mysqld or mysqld_safe is running under some other directory (not /usr/local/mysql), there is another version of MySQL on your IBM i that was not distributed by Zend.  It would need to be stopped before you can start up ZendDBi.  If you got this far and are just now realizing there is another version of MySQL on your system (I’ve seen it happen), then it is time to stop and find out where it came from and whether anybody is using it for anything.   You should probably not kill the process until you know more about how it got there.

2.b. No other MySQL daemon process is running

If there were no other mysqld or mysqld_safe processes running when you ran the ps ax command, then the /tmp/mysql.sock file was likely left behind when the daemon ended abnormally.  If this is the case, it is safe to delete it.  In Navigator, you can right click the file and select Delete from the context menu that appears.  You will be prompted to confirm the deletion. This should allow the Zend DBi MySQL daemon to start.

Most Zend DBi MySQL daemon start up issues can be resolved by reviewing the known issues covered in the troubleshooting article:

Troubleshoot Zend DBi MySQL daemon start up failures (mysql.sock)

However, there may be a situation where none of the known issues seem to be the problem.  Even if one of them is, checking for that problem may not show it, for some reason.  As it happens, I have never seen a start up issue that could not be resolved from the known issues, but did once have an occasion where the customer misunderstood the instructions to check for a port conflict.  We did not know that at the time, of course.  But I came up with this procedure to check for PASE messages, and it did show us the port conflict.  So, if nothing else, this makes a good safety net, in case something is missed in researching the known issues.

This technique is also useful in researching errors for other PASE processes run by the various Zend Server components.  This article is specific to the Zend DBi MySQL daemon, but other problems can be researched by using the correct job queues and PASE programs.

For this example, I have set up a port conflict at port 3306 that will prevent the daemon from starting.  This is a known issue, but the hope is that the same procedure could reveal a helpful message that could be used to analyze and resolve a previously unknown issue.

To begin, please sign on to a 5250 session as QSECOFR or as a *SECOFR class user.

Please enter this command:

call qp2term

This will bring you into the PASE shell.

The first thing to do in the PASE shell is to check for any active MySQL processes. Please enter this command:

ps ax

This brings up a list of processes. Search the list for a process running /usr/local/mysql/bin/mysqld, or any other file named mysqld. There may be many pages, so page up and down as needed to see them all. Here is a partial example output, including a process running mysqld:

11242 - A 0:00 /usr/local/ZendSvr/bin/php-cgi.bin 
11243 - A 0:00 /usr/local/ZendSvr/bin/php-cgi.bin 
11281 - A 0:00 /usr/local/mysql/bin/mysqld --defaults-file=/usr/local 
11284 - A 0:00 /QOpenSys/usr/bin/-sh -i

In this example, we see the process number 11281 is running MySQL. If there is a MySQL process running, and you did not start it from the menu, check the following article for more information on how to deal with it:

Some problem with mysql.sock prevents Zend DBi MySQL daemon from starting

If there was no mysqld process running, please stay in the PASE shell and continue with the next steps.

Attempt to start MySQL in the PASE shell using this command:

/usr/local/mysql/bin/mysql.sh start

If all is working OK, you would expect to see this message:

-n Starting MySQL ...

And after a small wait, the PASE shell prompt (usually a dollar sign or pound sign) should reappear. The normal message can appear even if the daemon does not start.  However, we want to try this step to see if there is any error in the shell script mysql.sh.

If the normal message appears, please run the ps ax command again to see if mysqld is running.  If it is, then the MySQL daemon is running successfully, and there is no PASE problem.  Kill the process using the PASE kill command and the process number.  In this example we are killing process 11281:

kill 11281

If the daemon started, there is no need to continue checking for PASE messages.  Continue to the final debugging step:

Maximize job logging for the Zend DBi MySQL daemon start up job

In our example, the normal message did appear, but the daemon did not start.  So we will continue looking for a PASE message. This next command will run the mysqld program directly, without any shell script.   This command is long, so please try to copy and paste from here into your PASE shell.

/usr/local/mysql/bin/mysqld --defaults-file=/usr/local/mysql/bin/my.cnf --basedir=/usr/local/mysql --datadir=/usr/local/mysqldata --port=3306 --user=mysql --socket=/tmp/mysql.sock --pid-file=/usr/local/mysqldata/zmysql.pid --tmpdir=/tmp

If there is a problem in PASE, you should see an error when running this command.  Here is our example result:

/usr/local/mysql/bin/mysqld --defaults-file=/usr/local/mysql/bin/my.cnf --bas
edir=/usr/local/mysql --datadir=/usr/local/mysqldata --port=3306 --user=mysql
 --socket=/tmp/mysql.sock --pid-file=/usr/local/mysqldata/zmysql.pid --tmpdir
=/tmp                                                                        
Warning: World-writable config file '/usr/local/mysql/bin/my.cnf' is ignored 
140314  0:17:09 [Warning] Setting lower_case_table_names=2 because file syste
m for /usr/local/mysqldata/ is case insensitive                              
140314  0:17:09 [Warning] One can only use the --user switch if running as ro
ot                                                                           

140314  0:17:09 [Note] Plugin 'FEDERATED' is disabled.
140314  0:17:09  InnoDB: Initializing buffer pool, size = 8.0M               
140314  0:17:09  InnoDB: Completed initialization of buffer pool             
140314  0:17:09  InnoDB: Started; log sequence number 0 44233                
140314  0:17:09 [ERROR] Can't start server: Bind on TCP/IP port: Address alre
ady in use                                                                   
140314  0:17:09 [ERROR] Do you already have another mysqld server running on 
port: 3306 ?                                                                 
140314  0:17:09 [ERROR] Aborting                                             

140314  0:17:09  InnoDB: Starting shutdown...                                
140314  0:17:15  InnoDB: Shutdown completed; log sequence number 0 44233     
140314  0:17:15 [Note] /usr/local/mysql/bin/mysqld: Shutdown complete        

$

Notice the errors highlighted in red.  They pretty much tell us exactly what the problem is, and even suggest we may have another server running on port 3306.  Remember that for this example I deliberately set up a port conflict at 3306.  If you are working with Zend Support on this issue, you can copy and paste your error messages into your case email, for Zend Support to review.

If the command runs OK and MySQL starts up, you may not see the PASE prompt return. Just use F3 to return to command entry, then use the call qp2term command again to get back in to the PASE shell. You can use the ps ax and kill commands as shown above to verify mysqld is running, and to end it.  If this is the case, that mysqld started OK using the PASE command, then there is not a PASE issue.  Please continue to the final debugging step:

Maximize job logging for the Zend DBi MySQL daemon start up job

Most Zend DBi MySQL daemon start up issues can be resolved by reviewing the known issues covered in the troubleshooting article:

Troubleshoot Zend DBi MySQL daemon start up failures (mysql.sock)

However, there may be a situation where none of the known issues seem to be the problem.  In that situation, the first thing to do is try to find a PASE error by starting the mysqld program in the PASE shell.  If the mysqld program starts successfully, but the ZENDDBID job does not start from the menu, then the technique presented here can be used to determine any problems in the submission of the job.  To find out if the mysqld PASE program starts successfully, please go to the following article (if you have not done so already):

Start Zend DBi MySQL daemon in PASE to review start up errors

If a PASE error was not found, continue with this step.  This process will create a detailed job log of the start up program that should contain any errors preventing the job from starting or submitting. To begin, please log on to a 5250 session as QSECOFR or a *SECOFR class user and enter this command:

go zendsvr6/zsmenu

This will bring you to the Zend Server 6 for IBM i Setup Menu. Use option “6. ZendDBi Management menu”. This will bring you to the ZendDBi management menu.

Verify that subsystem ZENDDBI is active, and job ZENDDBID is not active.  Use option “2. Work with ZendDBi susbsystem”.  If the subsystem is active, the display should show this:

               Current                            
Subsystem/Job  User        Type  CPU %  Function  
ZENDDBI        QSYS        SBS      .0

If it is not active, use option “1. Start ZendDBi subsystem”.  Then, once again, use option “2. Work with ZendDBi susbsystem”.  The display should now look like the example just above.  If there is also one or more ZENDDBID jobs in there, hit F5 a few times, until they go away.  If one ZENDDBID remains and does not end, that would mean start up is working, and we would not be here doing this.  If it looks like this, we are done:

               Current                            
Subsystem/Job  User        Type  CPU %  Function  
ZENDDBI        QSYS        SBS      .0            
  ZENDDBID     MYSQL       BCI      .0  PGM-mysqld

That is very unlikely to be the case.

The next step is to put the job queue on hold:

HLDJOBQ JOBQ(ZMYSQL/ZMYSQLJBQ)

Now, please go to the menu and use option “6. Start ZendDBi daemon”. (This might be option 4 if you have the older version of the menu.) It will take a while, then it may give a message that it could not be started. If the message appears, this is OK, because the start up job is held in the job queue. Display the job queue:

WRKJOBQ JOBQ(ZMYSQL/ZMYSQLJBQ)

You should see one job in there (if there are more, the job queue may have been held all along, which may have been the problem). It will look something like this, with a different job number:

Job            User           Number     Priority     Status
ZENDDBID       MYSQL          034213        5          RLS

Use option 2 to change the job and put these parameters on the command line:

Opt     Job            User           Number   
 2      ZENDDBID       MYSQL          034213   

Parameters for options 2, 3 or command         
===> LOG(4 00 *SECLVL) LOGCLPGM(*YES)

Press enter to record the changes. Make a note of the job name and number, or just stay on this screen while you release the job queue, so you can display the job after it finishes. Release the job queue:

RLSJOBQ JOBQ(ZMYSQL/ZMYSQLJBQ)

Display the job that was in the queue when it was released.

DSPJOB 034213/MYSQL/ZENDDBID

On the Display Job menu, use option “4. Display spooled files”. There should be a job log. There may also be a QPRINT spooled file, as seen in this example:

            Device or                       Total  
File        Queue       User Data   Status  Pages  
QPRINT      QPRINT                   RDY        1  
QPJOBLOG    QEZJOBLOG   ZENDDBID     RDY        1

Please examine the job log for any messages. If you are working with Zend Support on your issue, you can also copy the job log to a text file, zip the text file, and attach the zipped file to your next email response. If there is a QPRINT spool file, examine it, and if you have a Support case, copy to text, zip it and send it, as well.