How to Change/Convert your MySQL Database Collation to utf8 (a verbose sample)

Scenario: You have a very big database(schema)(more than 500 mb), and you wish to convert it say from latin1 to utf8(for reasons you can keep :) probably because you want your DB to be able to accommodate an app that can be fully localized (like Mambo or Vbulletin for Example), then this article might help.

You might find many conversion scripts around, but ever wondered what happens in the background? then read on.

Tools:
1.) mysqldump and mysql -or- phpmyadmin
2.) a text editor (vi perhaps?)

Steps:

Step 1: Create a new Database
This will be the placeholder for the database you are about to convert:

[SQL Query]

CREATE DATABASE `new_database` DEFAULT CHARSET=utf8


Step 2: Generate a "Structure-only sql dump"
Export an SQL file that should contain only the table structure:

[SHELL COMMAND]

mysqldump -u {USER} -p{PASSWORD} --no-data --no-create-db --opt --quote-names old_db > old_db_structure_only.sql

where old_db is the name of the database you wish to convert. Replace {USER} and {PASSWORD} with appropriate values


Step 3. Modify the "Structure-only sql dump"
Open the "old_db_structure_only.sql" file, use a text editor-> "Replace All" feature to change all instance of "DEFAULT CHARSET={CURRENT_CHARSET}" to "DEFAULT CHARSET=utf8". If you are using vi, you can probably get away with it by using the following command

[vi's non-visual mode command (press caps lock, then escape, then 'q' to switch to this mode)]

:%s/CHARSET=latin1/CHARSET=utf8/

assuming you are converting a latin1 collated DB


Step 4. Generate a Data-only sql dump
Next is generate a data-only dump, if your DB is more than 50mb, i advise that you use mysqldump command to execute this. Generating a big data-only dump from a big DB might lead to problems if done over http (timeouts, corrupted output etc)

[SHELL COMMAND]

mysqldump -u {USER} -p{PASSWORD} --no-create-db --no-create-info --opt --quote-names old_db > old_db_data_only.sql


Step 5. Restore the Structure-only sql dump (.sql) into the new Database

[SHELL COMMAND]

mysql -u {USER} -p{PASSWORD} new_db < old_db_structure_only.sql


Step 6. Restore the Data-only sql dump (.sql) into the new Database

[SHELL COMMAND]

mysql -u {USER} -p{PASSWORD} new_db <>Step 7. Drop the old Database (Make sure you are keeping your backups)

[SQL Query]

DROP DATABASE `old_db`

Step 8. Point your app from `old_db` to `new_db`
This would depend with your php application. There is a file usually named config.php or configuration.php which you will have to change the db_name values.

your app nows use utf8.. done

How to Make 'Read More' Links (A very simple sample implementation for PHP/MySQL)

Scenario: You have a php/mysql dynamically driven pages, you want a 'Read More' link displayed with your articles. This sample implementation might just give you the very basic analogy.

Method 1: For Articles stored on a single DB Field

A.) The DB Structure: (Use MySQL CLI or PHPMyAdmin to execute query)

CREATE DATABASE `test`;
CREATE TABLE  `test`.`article_table` (
`id` int(11) unsigned NOT NULL auto_increment,
`title` varchar(100) NOT NULL default '',
`article` mediumtext NOT NULL,
`created` datetime NOT NULL default '0000-00-00 00:00:00',
PRIMARY KEY  (`id`)
)

B.) The DB Sample Content: (Use MySQL CLI or PHPMyAdmin to execute query)

INSERT INTO `test`.`article_table` VALUES
(1, 'Article 1', 'Its been a while since i posted an article, busy months have passed and
im still within timeframes and deadlines.. well most of the time real life intervenes eh?
anyways heres my article about Sharing Hashes between Mambo 4.x and SugarCRM
4.5.1 Using MySQL Views', '2008-01-01 12:00:00'),
(2,'Article 2', 'The "foo: bar: not found" error message does not indicate that bar could
not be found, but rather bar exists but is calling something that could not be found.
This is the case with Perl scripts when the script cannot find where Perl is.
I have noticed that most of out "out-of-the-box" Perl scripts point to "/usr/local/bin/perl"
whereas some of the OS pre-bundled Perl is preinstalled at "/bin/perl", hence, when you
run a Perl script, the "foo: bar: not found" pops out the terminal.', '2008-01-01 12:00:00')

C.) The Script to Display the Articles in Summary and Full View Depending on the Task. Save as readmore.php


Heres whats happening above:

• All Articles Are displayed as Summary
• We used Substring to show only first 60 characters, of course you can adjust this to any value, just hack the code.
• Read More Link Per article will point to "readmore.php?task=Full&id={ID OF ARTICLE}"
• Full View is Displayed
  

Important Hints:

• The Full Article View needs the ID of the Article to Display
• This makes it necessary for ReadMore link to specify the ID of the Article it is a part of

Basic: Preparing Solaris 10 to run Mambo CMS

Brief Backgrounder on Solaris 10:

Solaris 10 is the latest OS offered by Sun Microsystems. Unlike its predecessors – Solaris 9 and previous releases, this version of Solaris is free for use with no restriction of any kind. It also has a twin named OpenSolaris which is essentially an exact replica except that it is available in source and as the named implies, yes OpenSolaris is open source. More info here and here.

Preparing Solaris 10:

Preparing Solaris 10 environment to run Mambo is basically having to set up Apache, MySQL and PHP environment and nothing else, as you might know the system requirements of Mambo is any system that can run certain versions of Apache, MySQL and PHP – click here for details. Solaris 10 is bundled with Apache 2 and MySQL 5 already, its just a matter of configuring these which we will discuss in detail later. PHP 5 is a little different story though as we will need a source distribution to compile this ourselves. PHP 5 needs to be a module for Apache, it has to support MySQL so bear with as we dig deeper.

Configuring Apache:

The configuration file for Apache should be located in “/etc/apache2/”. From a fresh installation it is named as httpd.conf-example under the same directory. You have to rename it to httpd.conf so Apache will recognize it.

# mv /etc/apache2/httpd.conf-example /etc/apache2/httpd.conf

Using your text editor (gedit or vi), edit httpd.conf to your liking, take note of the following directives as you may need to set this as neccessary:

ServerName – The IP address or hostname of your machine (ex. 127.0.0.1 or example.com)

Listen – the port where Apache will listen to (ex. 80)

Next, is invoke the terminal and enable the service using the following command:

# svcadm enable apache2

Test Apache by opening the browser and pointing it to http://localhost.

Note that depending with your Desktop GUI, invoking the Terminal is done differently. Under the Java Desktop System 3, you do this by right clicking the desktop and selecting Open Terminal. Under CDE, you do this by clicking the Tools menu (the arrow on top of Performance Meter) and clicking console.

Configuring MySQL:

The MySQL database tables need to be created 1^st. It will be the physical representation of your would-be MySQL tables as you create them. Moreover, the physical storage for your MySQL databases. Invoke the terminal and do the following command:

# /usr/sfw/bin/mysql_install_db

You should see several messages. MySQL Daemon by default does not allow root to run it. In such case a user needs to be set up specifically to run the MySQL server. Proper permissions are also mandatory. Invoke the terminal and issue the following commands:

# groupadd mysql
# useradd –g mysql mysql
# chown root /var/mysql
# chgrp –R mysql /var/mysql
# chmod –R 770 /var/mysql

What the above commands do are:

1. Creates a group called MySQL,


2. Creates a User named MySQL and makes it a member for the MySQL Group,


3. Sets the ownership of the MySQL data directory to root,


4. Sets the group of the MySQL data directory to MySQL group and


5. Sets Read write access for the Group and Owner designated to the MySQL data directory while leaving others with no access.

Next is to set the MySQL Configuration File: Using the terminal, create a copy of a sample MySQL configuration file to the /etc directory as follows:

# cp /usr/sfw/share/mysql/my-medium.cnf /etc/my.cnf

Now to start the MySQL Daemon, use the following command:

# cd /usr/sfw/sbin/
# ./mysqld_safe --user=mysql &

To set the MySQL Password, the following commands need to be executed:

# cd /usr/sfw/bin
#./mysqladmin –u root password new-password
#./mysqladmin –u root –h localhost password new-password

To start the MySQL Daemon automically when Solaris starts, you need to bootstrap the startup files to rcs.d, rc1.d, rc2.d and rc3.d under the /etc directory. Issue the following commands using the terminal:

# ln /etc/sfw/mysql/mysql.server /etc/rcS.d/k00mysql
# ln /etc/sfw/mysql/mysql.server /etc/rc0.d/K00mysql
# ln /etc/sfw/mysql/mysql.server /etc/rc1.d/K00mysql
# ln /etc/sfw/mysql/mysql.server /etc/rc2.d/K00mysql
# ln /etc/sfw/mysql/mysql.server /etc/rc3.d/S99mysql

Compiling PHP 5

If you have gone this far without errors - congratulations, but we are still at the half. Since we will need to compile PHP from source, we will need to set Solaris for compiling environment and will install a bunch of applications.

Note that the variable $PATH which is an array of location where Solaris searches for applications is not set properly on a fresh installation. Consider setting up your path as follows
On TCSH:
# setenv PATH {$PATH}: /opt/csw/bin:/usr/sfw/bin:/usr/dt/bin:/usr/css/bin
On SH:
# set PATH=$PATH:/opt/csw/bin:/usr/sfw/bin:/usr/dt/bin:/usr/css/bin
# export PATH

A default install of PHP requires the following:

• gcc
• make
• flex
• m4
• autoconf
• automake
• perl
• gzip
• GNU tar
• GNU sed

However a standard install of Solaris is already bundled with most of the listed above, what you'll need is only autoconf, automake and GNU sed, you can download them directly by invoking the following commands from the terminal (you should be in a directory where you intend to store the packages). The full is at http://www.sunfreeware.com/programlistintel10.html

# wget ftp://ftp.sunfreeware.com/pub/freeware/intel/10/autoconf-2.60-sol-10-x86-local.gz
# wget ftp://ftp.sunfreeware.com/pub/freeware/intel/8/automake-1.5-sol8-intel-local.gz
# wget ftp://ftp.sunfreeware.com/pub/freeware/intel/10/sed-4.1.5-sol10-x86-local.gz

Note that the Automake version is for Solaris 8 but should also work, links to the Solaris 9 and 10 versions are dead on the time of this writing as reffered here.

Once you have downloaded the above packages, uncompress them using gunzip

# gunzip *.gz

Then install them using pkgadd -d

# pkgadd -d autoconf-2.60-sol-10-x86-local.gz
# pkgadd -d automake-1.5-sol8-intel-local.gz
# pkgadd -d sed-4.1.5-sol10-x86-local.gz

After successfully doing the steps above which have set up a compiling environment for PHP, there are still a few applications that needs to be installed similar to the procedure above. The following applications are needed for a fully functional and optimal Mambo installation later: Download the following files from http://www.sunfreeware.com/programlistintel10.html or use wget for direct download

# wget ftp://ftp.sunfreeware.com/pub/freeware/intel/10/zlib-1.2.3-sol10-x86-local.gz
# wget ftp://ftp.sunfreeware.com/pub/freeware/intel/10/xpm-3.4k-sol10-intel-local.gz
# wget ftp://ftp.sunfreeware.com/pub/freeware/intel/10/freetype-2.2.1-sol10-x86-local.gz
# wget ftp://ftp.sunfreeware.com/pub/freeware/intel/10/freetype-2.2.1-sol10-x86-local.gz
# wget ftp://ftp.sunfreeware.com/pub/freeware/intel/10/fontconfig-2.2.98-sol10-intel-local.gz
# wget ftp://ftp.sunfreeware.com/pub/freeware/intel/10/expat-1.95.5-sol10-intel-local.gz
# wget ftp://ftp.sunfreeware.com/pub/freeware/intel/10/libiconv-1.11-sol10-x86-local.gz
# wget ftp://ftp.sunfreeware.com/pub/freeware/intel/10/libpng-1.2.12-sol10-x86-local.gz
# wget ftp://ftp.sunfreeware.com/pub/freeware/intel/10/jpeg-6b-sol10-intel-local.gz
# wget ftp://ftp.sunfreeware.com/pub/freeware/intel/10/openssl-0.9.8d-sol10-x86-local.gz
# wget ftp://ftp.sunfreeware.com/pub/freeware/intel/10/curl-7.15.4-sol10-x86-local.gz
#wget ftp://ftp.sunfreeware.com/pub/freeware/intel/10/ncurses-5.5-sol10-x86-local.gz

Install the above application in exact order as downloaded to stay safe. Certain dependencies might not be fullfilled if installed in random..

..TO BE CONTINUED