Scorpio Framework for PHP

• Resources

  • Categories
  • News
  • Documentation
  • Tutorials
  • Roadmap

• Links

  • Madagasgar.com
  • SourceForge Project Page
  • Download Releases
  • Bug Tracker
  • Feature Requests
  • API Docs

Creating a Scorpio development environment

• You are in »
• Categories /
• Tutorials /

Configuring MySQL

With Apache now largely taken care of, we can look at setting up MySQL. By default on Fedora systems it comes with no root password and only a very basic configuration file. Before actually creating any databases though, it is usually best to first set a root password, but even before that it is much better to change a couple of settings in the my.cnf file. The settings to play with are the collation and character set and to remove the old_passwords directive. Why?

Well, MySQL is developed (mostly) by a set of Swedish developers which in turn has lead to some, shall we say, dubious choices in the default configuration. This is typified by the choice of latin_swedish_ci as the default collation and Swedish as the default character set. Not particularly useful. Why they have not changed this to UTF-8 is anybodies guess - though I am sure there is a long discussion on the pros and cons. Either way; we should (actually need to) use UTF-8.

Why remove old_passwords? Because there is little to no point in using them.

So, we need to edit the main my.cnf file:

nano /etc/my.cnf

Remove the line about old_passwords and then within the mysqld section and the following two lines:

default-character-set=utf8

default-collation = utf8_general_ci

Note: for MySQL 5.2+ the above directives have been removed. Instead you should use the following to set UTF-8 as the default:

character-set-server=utf8

collation-server=utf8_general_ci

Save the changes and exit nano. Now restart mysqld:

service mysqld restart

MySQL should restart without issue. With that done we can create a password for the root account and start setting up the various scorpio databases.

To create a mysql password where one has not been set before simply issue:

mysqladmin -u root password

And specify the password you want to use.

Change directory to the scorpio SQL data folder,:

cd /home/sites/scorpio/data/sql

Connect to mysql as root:

mysql -u root -p

And create a new user. As this is a development machine I am not being too careful with permissions. In a full deployment you would need to be restrictive. Either way, there must be at least one user that has ALTER and CREATE privileges as these are needed by the dbUpdate utility.

grant all on *.* to 'scorpio'@'localhost' identified by 'PASSWORD';

Next we need to create the three basic databases for the scorpio system which are: logging, system and wurfl. Logging can be skipped, however you will not be able to use the database system log writer if you do. The command can be issued on one line or individually:

create database scorpio_logging; create database scorpio_system; create database scorpio_wurfl;

Now it is time to setup the various tables. Scorpio comes bundle with a set of SQL scripts for MySQL for these components. All the initialisation files are located in the /data/sql folder and are named COMPONENTInit.sql.

First we will start with logging. Please note that you need the full path to the SQL files, or you must cd to the /data/sql folder first before continuing.

cd /data/sql

use scorpio_logging;

\. loggingInit.sql;

\. is a short-cut for importing SQL files. For all of these commands you must ensure there is a trailing ; (semi-colon) to tell the client to execute the SQL statement. You should see a result showing the number of queries executed and if there were any errors.

Next we will add the system tables. This is slightly more involved as there are additional update SQL files that need to be applied - but only after the Init.sql file has been loaded. They MUST be executed in order of Init, then update_X where X is a number from 1 to n+1.

use scorpio_system;

\. systemInit.sql;

\. system_update_1.sql;

\. system_update_2.sql;

Finally we can add the wurfl tables for the wurfl layer.

use scorpio_wurfl;

\. wurflInit.sql

With that done, we can exit the mysql client.

exit

That is MySQL sorted. If you wish to add any other specific configuration directives feel free.

The final step to take is to ensure that MySQL and Apache will start up with the OS. This is handled via the chkconfig command:

chkconfig --level 2345 mysqld on

chkconfig --level 2345 httpd on

With that all done, we can return to the Scorpio folder and finish the configuration there.