MySQL

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.

Installing MySQL on Mac OS X

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

Installing MySQL on WinXP

Installing MySQL 5.0 on a WinXP computer is very easy.

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.
   
   

   GRANT ALL PRIVILEGES ON *.* TO 'DBADMIN'@'localhost' IDENTIFIED BY 'MyPassword' WITH GRANT OPTION;

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

   GRANT ALL PRIVILEGES ON *.* TO 'DBADMIN'@'%' IDENTIFIED BY 'MyPassword' WITH GRANT OPTION;

   
   
   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.
   
   

   exit
   mysql -u root -p
   
   exit
   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.

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

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
   [mysqld]

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

   lower_case_table_names=1

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

That's all there is to it!

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:

NOW()

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:

CURRENT_TIMESTAMP or CURRENT_TIMESTAMP()
CURDATE() or CURRENT_DATE or CURRENT_DATE()
CURTIME() or CURRENT_TIME or CURRENT_TIME()

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

tx_isolation='READ-COMMITTED'

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.
transaction_isolation='READ-COMMITTED'
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.
tx_isolation='READ-COMMITTED'
wait_timeout=432000 # 5 days
# End Admin Configuration Settings

mysqldump

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

Example:

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;

   
   Example:
   
   

   create database LibraryData2;

2. Import the mysqldump file from the shell prompt:
   
   

   mysql -u UserName -p DBName < TheFullTargetFilePath

   
   
   Example:
   
   

   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

phpMyAdmin

phpMyAdmin

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

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.

Installing phpMyAdmin on Mac OS X

phpMyAdmin

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.