Tips_sql   >   Mysql   >   MySQL (All Contents)


This section covers various tips related to setting up and working with MySQL from the perspective of an Omnis Studio programmer. I started using MySQL in early 2006 when writing a ticket tracking system for a European Appliance warranty provider.

The reason for using MySQL was that it was free and Omnis Studio provided a DAM to connect to MySQL. I verified with MySQL to make sure that it was legal to use the free version for business use. I only required a commericial version if I was shipping MySQL on a CD or in some other form of an auto installer. As long as MySQL was being installed manually on the client's server it was 100% okay to use the free version. (Good to know)

At the time of doing this work I was developing and testing on a MacBook Pro running Mac OS 10.4.5. The customer was using the Debian flavour of Linux for a server and with WindowXP/98 clients.

My ISP installed MySQL 5.0, PHP 5.1, and phpMyAdmin 2.7 on the Linux box so I can't give any advice on how that was done. He said Debian makes it really easy.

I installed MySQL 5.0, PHP 4.4, and phpMyAdmin 2.7 on my MacBook Pro laptop which came with Apache 1.3 preinstalled. After many hours of struggling and searching for answers on Google I managed to get everything working satisfactory on my laptop. If you are attempting the same feat, see the topic on Installing MySQL on MacOS X.

Installing MySQL on Mac OS X

Installing MySQL 5.0 on my MacBook Pro (intel) running Mac OS 10.4.5 was fairly simple.

I downloaded MySQL 5.0 from the website.

Once downloaded I ran their Mac friendly package installer. You should also install the MySQLStartupitem package.

The mysql files are installed at /usr/local/mysql/

The readme.txt file talks about setting up aliases to the mysql program or a path in the $HOME/.bashrc file. The aliases only work until you restart, so that is a waste of time. The path instructions didn't work. I finally got the path to work in a .profile file. You can execute the following from the Terminal command line to create a .profile file in your home directory.

  1. Go to your home directory.

    cd ~

  2. Use pico to create a dot profile file.

    pico -w .profile

  3. Enter the following text in the pico editor.

    export PATH=$PATH:/usr/local/mysql/bin

  4. Ctrl+X to exit pico, y to confirm save changes, return to save the file.
  5. From the command prompt you should be able to type mysql to get to the mysql> prompt.

If you use TextEdit or some other word processing program to create or edit any configuration files, like the .profile file you must set and save the file as plain text. If you allow the text editing program to save the file as rich text or some other non-plain format the configuration file will not work.

Next you will need to set up users and privileges.

If you need to change the default database startup settings you will need to go to the /usr/loca/mysql/support-files/ directory and read the my-*.cnf files to see which one you should use. Follow the directions at the top of the file.

Installing MySQL on WinXP

Installing MySQL 5.0 on a WinXP computer is very easy.

Downloaded MySQL for WinXP from the website and run their installer.

The installer asks you for a password for the root user. Go ahead and enter a password that you won't forget. This saves you from having to set the root user password manually.

One of the installer screens has a checkbox for adding the path to the Program Files\mysql\bin file, be sure to set to check the option.

To get to the mysql> command line prompt:

After you finish with the installer you should may want to add a database administrator user other than root.

  1. Select the Run... menu from the Start menu.
  2. Enter cmd to get to the DOS command prompt.
  3. From the command line open the mysql program as the user root:

    mysql -u root -p

    You will be prompted for the password which you entered during the installation. After entering the correct password and pressing return you should be at the mysql> prompt.

Setting Users and Privileges

You will likely need to use the command line to get MySQL setup with the intials users and a database. I tried using GUI tools but ran into buggy behavior here and there. phpMyAdmin is the best GUI tool I found, but you are best to get the initial users set up using the command line. MySQL installs with a user named root with no password. The first thing you want to do is add a password to root as follows:

  1. From the command line open the mysql program as the user root:

    mysql -u root

    WinXP users who specified a password during the installation will need to append -p (prompt for password) to the command. This will cause mysql to prompt you for a password, which you then enter.

    mysql -u root -p

    You should be at the mysql> prompt.
  2. From the mysql> prompt set a password using SQL

    SET PASSWORD FOR 'root'@'localhost' = PASSWORD('MyNewPassword');

  3. Add a user for localhost use. I am in the habit of creating a user called DBADMIN. MySQL lets you create a user and grant privileges in the same SQL statement.


  4. Add a second user which can access MySQL from all other IP addresses:


    Depending on security you may want to replace '%' with something more limiting like '192.168.1.%'. You can read all the options in the MySQL online documentation.

    From what I can tell you need to add the 'localhost' user if you want to login on the local host. The '%' user doesn't appear to work when you are on the localhost.
  5. Reload the user privileges.

    flush privileges;

  6. Test your changes by exiting mysql and then entering again specifying the user and password option. In each case your will be prompted for the password.

    mysql -u root -p

    mysql -u DBADMIN -p

All going well you will be able to login to mysql with your specified password and user. If not, you'll have to use Google and the MySQL online documentation to find answers.

With MySQL up and running you can move on to installing phpMyAdmin which is a great GUI tool to use for administering MySQL. It is browser based so you don't need any client side software to use it.

Installing PHP and phpMyAdmin on my MacBook Pro is where I spent many hours. Hopefully the tips in the phpMyAdmin section will save you some grief. Once it is set up and running phpMyAdmin works great!

Session Settings

When you open a session using the MYSQLDAM you may want to issue the following initial statement for setting various defaults.

SET @@global.innodb_flush_log_at_trx_commit=1, @@session.max_allowed_packet=16*1024*1024, @@session.autocommit=1, @@session.tx_isolation='READ-COMMITTED';

@@global.innodb_flush_log_at_trx_commit=1 means that the logs are written when a transaction is committed instead of the default which is every minute or so. This is slower, but more dependable.

@@session.max_allowed_packet=16*1024*1024 means to set the max transaction size to 16MB.

@@session.autocommit=1 means that the transaction is automatically commited when it is finished.

@@session.tx_isolation='READ-COMMITTED' means that reads don't put locks on the records. They are committed immediately after reading them.

Create my.cnf File

You should create a my.cnf file on your MySQL server so that you can set various things on the server side.

The my.cfg file needs should be located in the etc directory. If it doesn't exist you will need to copy it from the /usr/local/mysql/support-files directory as follows:

  1. At the terminal enter: cd /usr/local/mysql/support-files - to go to the directory
  2. Enter ls - to list all of the files. You should see a file named my-huge.cnf
  3. Copy the file to the etc directory as follows: cp my-huge.cnf /etc/my.cnf
  4. Go to the etc directory: cd /etc

You can now edit the my.cnf file with you favorite text editor. e.g. pico my.cnf

The following are some settings which I have added/changed in the my.cnf file.

  1. lower_case_table_names = 1 (Add to the [mysqld] section - see tip in this section for more details)
  2. max_allowed_packet = 10M (increased from 1M after importing a large file and getting a bigger than max_allowed_packet error message.

Create Database

From the mysql prompt in the terminal:

create database newdatabasename;

Lower Case Table Names

If you want to avoid headaches with moving your data around or restoring from backups, set MySQL to use lower case table names.

  1. Open the my.cnf file (see separate tip in this section)
  2. Look for the following text in the file.

    # The MySQL server

  3. In the MySQL server section add the following line to the file:


  4. Save the file.
  5. Stop and restart MySQL

That's all there is to it!

When you create new tables, the table names will be in lower case. Trust me this will make dumping and restoring databases a lot more reliable.

Problems with Blobs

I ran into problems with binary columns and the MYSQLDAM

The error I hit was:

SQL Error Type: 2
SQL Error Code: -107
SQL Error Text: The data buffers could not be allocated

MySQL does not support the chuncking of binary data, so in order to successfully fetch the LOB, the buffer size must be greater than or equal to the column size.
kStatementBufferAllocationFailed occurs when the MYSQLDAM fails to allocate a buffer needed to store the data for one of the result columns.
This usually occurs with binary data where the column size is very large, or where the user is trying to fetch multiple LOB columns.

To solve the problem I did the following after opening a session with the MySQL database:

; Set session properties to avoid errors with binary columns.
Do sessobj.$connectoption(kMySqlOptDataTruncation,kTrue)
Calculate Size as 17000000
Do irSessionObj.$blobsize.$assign(Size)
Do irSessionObj.$lobthreshold.$assign(Size)
Do irSessionObj.$lobchunksize.$assign(Size)

Stop/Start MySQL server

To stop the MySQL server from command line enter:

mysqladmin -u root -p shutdown

To start the MySQL server from command line enter:

mysqld_safe &

System Date and Time

The MySQL syntax for setting a timestamp column's date to the current date/time is as follows:


Calculate SQLText as "UPDATE TableName SET ModDateColName=NOW(),ColName1=[$cinst.ColName1], etc."
Do StmntObj.$execdirect(SQLText) Returns FlagOK

Other MySQL date/time functions are as follows:


Transactions Settings

The default transaction setting for MySQL is REPEATABLE READ. The problem with this settings is that it can lock a chunk of records. To avoid this I prefer to use READ COMMITTED.

You can change the transaction_isolation setting on your server by adding the following to your my.cnf file (See the tip on Creating my.cnf if you don't have one set up.)


This should go somewhere just before [mysqldump] in the my.cnf file.

I recommend that you set up an Admin Configuration Settings block just before [mysqldump] for this and other custom settings.

# Admin Configuration Settings - 2007-09-12 Doug K.
wait_timeout=432000 # 5 days
# End Admin Configuration Settings

Wait Timeout

If your clients leave your Omnis Studio application open for more than eight hours without any activity the MySQL server will automatically time out and close their connection. In this case, you normally get one of the following error codes (which one you get is operating system-dependent):

  1. CR_SERVER_GONE_ERROR - The client couldn't send a question to the server.
  2. CR_SERVER_LOST - The client didn't get an error when writing to the server, but it didn't get a full answer (or any answer) to the question.

By default, the server closes the connection after eight hours if nothing has happened. You can change the time limit by setting the wait_timeout variable when you start mysqld.

If you have a script, you just have to issue the query again for the client to do an automatic reconnection. This assumes that you have automatic reconnection in the client enabled (which is the default for the mysql command-line client).

You can change the wait_timeout setting on your server by adding the following to your my.cfg file (See the tip on Creating my.cfg if you don't have one set up.)

wait_timeout=432000 # 5 days

This should go somewhere just before [mysqldump] in the my.cfg file.

I recommend that you set up an Admin Configuration Settings block just before [mysqldump] for this and other custom settings.

# Admin Configuration Settings - 2007-09-12 Doug K.
wait_timeout=432000 # 5 days
# End Admin Configuration Settings


You can use mysqldump to create a backup of a MySQL database, or to send the database to someone else, or move it to another server. It is easy to do, and blazing fast.

The syntax from the shell is:

mysqldump TheDatabaseName -p > TheFullTargetFilePath


mysqldump LibraryData -p > /Users/doug/Desktop/LibraryDataDump.sql

To import the data into a new database:

  1. Create the database. For example from the mysql prompt:

    create database NewDatabase;


    create database LibraryData2;

  2. Import the mysqldump file from the shell prompt:

    mysql -u UserName -p DBName < TheFullTargetFilePath


    mysql -u root -p LibraryData2 < /Users/doug/Desktop/LibraryDataDump.sql

mysqldump --no-create-info

To do a mysqldump of just the data, not create table statements use the following syntax:

mysqldump --no-create-info -u MySQLUserName DatabaseName > TargetFilePath

An example:

mysqldump --no-create-info -u root MyData > /Users/doug/Desktop/MyData_20081128.sql


is a widely used GUI administration tool for MySQL. Being browser based you don't need any client side software to use it and depending on how you set it up you could also access if from anywhere on the internet.

Installing PHP and phpMyAdmin on my MacBook Pro is where I spent many hours. Hopefully the tips in this section will save you some grief. Once phpMyAdmin is set up and running it works great.

Installing PHP on Mac OS X

Before you do anything, check to see if PHP is installed and enabled on your Mac as follows:

  1. Turn on Personal Web Sharing in the System Preferences under Sharing.
  2. Go to Library/WebServer/Documents and create a file called phpinfo.php using a text editor.
  3. Enter the following text in the file and save the file.

    <?php phpinfo() ?>

  4. From your web browser enter the URL localhost/phpinfo.php
  5. If PHP is enabled, you will get a page with all kinds of info about PHP displayed in your browser. If PHP not enabled you will just get the text of your file displayed in the browser.

If you need to enable PHP do the following from the command line:

  1. Go to the httpd directory

    cd /etc/httpd
  2. Open the httpd configuration file as super user using pico or the text editor of your choice. (My preference is BBedit). You will need to enter the root password for your computer.

    sudo pico -w httpd.conf
  3. In the config file you will need to find and uncomment the following lines by deleting the # character at the beginning of the line.

    For Mac OS X 10.5

    #LoadModule php5_module

    For Mac OS X 10.4

    #LoadModule php4_module
    #AddModule mod_php4.c

  4. Ctrl+X to exit pico.
  5. Stop and restart Personal Web Sharing in the System Preferences.
  6. Try the localhost/phpinfo.php in your web browser again. Be sure to reload the page.
  7. If that doesn't work Google around for tips from other developers.
Once you have PHP working you are ready to install phpMyAdmin.

Installing PHP on WinXP

On WinXP you will need to install/enable web server software on their computer. You can only run your computer as a web server if you have WinXP Pro. Afer installing and setting up your computer as a web server type in http://localhost/ in your browser to test whether or not it is working.

Once you have your WinXP computer working as a web server you are ready to install PHP.

Google for Download PHP for WinXP and then download and install the latest version of PHP following the directions provided.

Installing phpMyAdmin on Mac OS X

is easy to install, but I had a tough time getting the config file sorted out so that it would work on my MacBook Pro.

  1. Download phpMyAdmin from At the time of writing this I could not get the 2.8 version to work property. There isn't a package installer for Mac OS X so I just download the zip version. The download is small.
  2. Drag a copy of the downloaded and unzipped folder into your /Library/WebServer/Documents folder.
  3. Rename the folder to pma to save you some typing in your web browser.
  4. The documentation talks about a file which you are supposed to modify rather than the config.default.php file. (There is a warning at the top of the config.default.php file.) In my case no such file existed. Eventually I created the file myself directly inside the pma folder and found that the following text in the file did the trick.

    $cfg['blowfish_secret'] = 'ae85mh04wq23'; // Enter a random string to be used for blowfish encryption

    // Server(s) configuration
    $i = 0;
    $cfg['Servers'][$i]['host'] = ''; // 'localhost' did NOT work!
    $cfg['Servers'][$i]['connect_type'] = 'socket'; // 'tcp' did NOT work!

    $cfg['Servers'][$i]['auth_type'] = 'cookie'; // Using cookies is easiest and safest setup.
    $cfg['Servers'][$i]['user'] = ''; // MySQL user - empty for cookies
    $cfg['Servers'][$i]['password'] = ''; // MySQL password - empty for cookies


  5. Go to localhost/pma in your web browser. All going well you will be prompted to enter a user name and password. You can either enter the root user name and password or the DBADMIN user name and password if you created one.
  6. Click the Logon button. All going well you will be presented with a Welcome to phpMyAdmin page.