Tips_sql   >   Mysql   >   MySQL

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.

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 mysql.com 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.
Warning

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

    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.

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

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:

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