Wiki ⇒ Tips and Tricks ⇒ MySQL ⇒ CPG Dragonfly™ CMS
WikiTips and Tricks ⇒ MySQL

10. 2: MySQL Parent


There are a few things in your Dragonfly Administration which give you some nice details about your database. See admin.php?op=info&mysql

Slow Queries

When started with the --log-slow-queries[=file_name] option, mysqld writes a log file containing all SQL statements that took more than long_query_time seconds to execute. A statement is logged to the slow query log after it has been executed and after all locks have been released. Log order may be different from execution order.
By default a server doesn't log the queries so you have to modify the mysqld command OR your my.cnf, and this last noted file is what we gonna change IF needed for debugging, so let's start and locate the my.cnf on your system.

On Windows, MySQL programs read startup options from the following files:

FilenamePurpose
WINDIR\my.iniGlobal options
C:\my.cnfGlobal options
INSTALLDIR\my.iniGlobal Options
defaults-extra-fileThe file specified with --defaults-extra-file=path, if any

On Unix, MySQL programs read startup options from the following files:

FilenamePurpose
/etc/my.cnfGlobal options
$MYSQL_HOME/my.cnfServer-specific options
defaults-extra-fileThe file specified with --defaults-extra-file=path, if any
~/.my.cnfUser-specific options

More details can be found at MySQL Reference Manual :: 4.3.2. Using Option Files

When you have the my.cnf open add the following lines in the [mysqld] section of the file.

log-slow-queries
log-long-format

Save the file and restart mysql (on linux, $ service mysql restart or $ /etc/init.d/mysql restart) and then it should work.

The slow query log can be used to find queries that take a long time to execute and are therefore candidates for optimization. However, examining a long slow query log can become a difficult task. To make this easier, you can process the slow query log using the mysqldumpslow command to get a summary of the queries that appear in the log.

More details about slow query logging can be found at MySQL Reference Manual :: 5.10.5. The Slow Query Log

CHARACTER SETS and COLLATIONS

Dragonfly CMS is an UTF-8 based system and therefore all data inside the database is UTF-8 encoded.

If you didn't always use Dragonfly and upgraded from PHP-Nuke or CPG-nuke then you may have mixed data where the "old" data is proberly latin1 and the new data UTF-8. Therefore you should have converted the old data first, before continuing with Dragonfly CMS as reported many times inside the forums.

If you see strange characters in your current Dragonfly >= 9.1.x data then the issue relies in the database collation and charset which are probably latin1_swedish_ci or similar, so the only thing you have to do is change the: default charset and collation of the database, tables and columns WITHOUT converting the data.

NOTE: Never convert the data, only fix the database settings.

Code:
ALTER DATABASE mydatabase CHARSET=utf8 COLLATE utf8_general_ci;

ALTER TABLE mytable CHARSET=utf8 COLLATE utf8_general_ci;

# the following is mostly NOT needed
ALTER TABLE mytable ALTER COLUMN mycol CHARSET=utf8 COLLATE utf8_general_ci;

MySQL Reference Manual :: Chapter 10. Character Set Support

Created: Saturday, September 03, 2005 (02:14:56) by DJMaze
Updated: Tuesday, January 09, 2007 (14:19:35) by DJ Maze