Lynch Consulting Blog - Database

MySQL 5.1 logging changes - Log to DB and runtime config

Fri, 29 Jan 2010 08:15:00 --1000

While browsing around the MySQL site last night I discovered a number of nice new features of mysql 5.1 that relate to logging. These are:

Logging to DB instead of log files
Runtime configuration of logging.

Logging to DB instead of log files

Coming from a web development background rather than a sysadmin background I'm far more comfortable manipulating and analysing data using SQL. So to be able to log all the queries or just the slow queries for an application to the db during application development or load testing is a huge benefit. To enable logging to DB you can add the following to your my.cnf


log_output = TABLE

The logs will be written to the 'slow_log' and 'general_log' tables in the mysql database. Note - logging to tables has more overhead than logging to file, so would suggest using it primarily for development purposes. Full details of the options are on the mysql manual on log tables

Runtime configuration of logging.

This allows you to turn on and off logging without restarting MySQL - which just saves a little bit of time and makes it much nicer for debugging problems. To turn on the logging of all queries run:


SET GLOBAL general_log = 'ON';

And for just the slow query log:


SET GLOBAL slow_query_log = 'ON';

And to turn them both off use:


SET GLOBAL general_log = 'OFF';
SET GLOBAL slow_query_log = 'OFF';

If you also want to see queries not using indexes in the slow query log you can set the following variable:


SET GLOBAL log_queries_not_using_indexes = 'ON';

Hope it helps, Mark

Identifying which queries to tune with MSSQL

Wed, 04 Feb 2009 11:29:00 --1000

I've been doing some more work tuning and CF/MSSQL application and during figuring out which parts of the system is doing the most work (and hence a good candidate for tuning) I came across this gem which will show stats on the most run queries in the cache.


SELECT TOP 20 SUBSTRING(qt.text, (qs.statement_start_offset/2)+1, 
        ((CASE qs.statement_end_offset
          WHEN -1 THEN DATALENGTH(qt.text)
         ELSE qs.statement_end_offset
         END - qs.statement_start_offset)/2)+1), 
qs.execution_count, 
qs.total_logical_reads, qs.last_logical_reads,
qs.min_logical_reads, qs.max_logical_reads,
qs.total_elapsed_time, qs.last_elapsed_time,
qs.min_elapsed_time, qs.max_elapsed_time,
qs.last_execution_time,
qp.query_plan
FROM sys.dm_exec_query_stats qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) qt
CROSS APPLY sys.dm_exec_query_plan(qs.plan_handle) qp
WHERE qt.encrypted=0
ORDER BY qs.total_logical_reads DESC

I found it in this excellent article on optimising MSSQL The times that are returned from the queries are in micro seconds as documented on msdn. Cheers, Mark

Clear MSSQL Query Caches

Tue, 03 Feb 2009 22:12:00 --1000

Found this useful little snippet for when you are load testing MSSQL Servers. You can use it to clear the query cache - to ensure you are starting from a level playing field. Or if you are tuning queries in a application and want to ensure they are not cached run it before each run:


dbcc freeproccache
go
dbcc dropcleanbuffers
go

Cheers, Mark

Loading MySQL timezone info on Linux and Macs

Mon, 08 Sep 2008 13:30:00 --1000

MySQL has some really useful functions that make timezone support a bit easier as described in a previous blog post.. However, you need to do a tiny bit of setup work before you can use the functions. You need to load the timezone info from your system into the mysql db. Thankfully, the MySQL Developers have included a very handy script that will do just that from a zoneinfo files which are used on Linux and on Mac's.


mysql_tzinfo_to_sql /usr/share/zoneinfo | mysql -u root mysql

Obviously change the mysql params or add a -p if you have a password or are connecting to a remote server. Check out the MySQL developer site for full details on MySQL timezone support Cheers, Mark

MySQL and Temporary tables and CASE syntax

Sun, 31 Aug 2008 20:47:00 --1000

For some reason I've always avoided the use of temporary tables in SQL (MySQL or otherwise) but today I came across a difficult problem that temporary tables made very simple. I needed to use a case function on a subquery value to return a tri-state value. I.e. yes, no or partial. The cut down query below demonstrates how it works.

First we drop the temporary table in case one has been left lying around.
Then we select a query into the temporary table which include some complex subqueries.
We can then query the temporary table to do further processing on the result, which in this case depending on the values of the class_count and class_count_assigned allows us to set a field in three states.
- 'yes' if all users for a class are selected
- 'partial' if some users are selected
- 'no' if none are selected.



	DROP TEMPORARY TABLE  IF EXISTS tmp_exams;
	/* Create temporary table query which includes student counts */
	CREATE TEMPORARY TABLE tmp_exams
	SELECT	e.id
			, e.name
			, (SELECT count(1) FROM tbl_user_classes uXc
                WHERE uXc.school_class_id = ) AS class_count
            , (SELECT count(1) FROM tbl_user_classes uXc
                INNER JOIN  tbl_users_exams uXe ON uXe.user_id = uXc.user_id
                WHERE uXc.school_class_id = 
                AND exam_id = e.id) AS class_count_assigned
	FROM	tbl_exams e;
	/* Select out the relevant info */
	SELECT e.id
			, e.name
			, e.class_count
			, e.class_count_assigned
		    ,(CASE WHEN class_count_assigned = 0 THEN 'no'
		    	WHEN class_count = class_count_assigned THEN 'yes'
		    	ELSE 'partial' END) AS active
	FROM tmp_exams e
	WHERE	1=1
	ORDER BY e.name

Mostly posted for my future reference but hope it helps out. Cheers, Mark

PHP and MySQL 5 bit fields

Mon, 25 Aug 2008 12:21:00 --1000

While debugging some PHP code the other night I came across a particularly strange problem with MySQL Bit fields. I was returning a query with some bit fields into my class but the it was not returning true or false when I switched the data. A bit of hunting and it turned out the that it was being returned as a binary type and after a few failed attempts to convert it to an integer or boolean on the PHP side I found this bug report on it. So the moral of the story is if you are selecting BIT types from a mysql DB in PHP don't do this:


 SELECT myBit
 FROM tbl_example
 WHERE id = 1

Instead cast the bit to an integer in MySQL like so:


 SELECT CAST(myBit AS unsigned integer) AS myBit, 
 FROM tbl_example
 WHERE id = 1

Voila, it works like expected. Cheers, Mark Lynch

"Show Full Columns" problem with CFMX and MySQL solved

Sun, 01 Jun 2008 21:01:00 --1000

We've been performing some load testing on a new website we've developed and our helpful sysadmin noticed lots of queries happening on the DB that looked like this:


SHOW FULL COLUMNS FROM `dbname`.`tablename`

We weren't intentionally doing these queries but they were coming from somewhere. A bit of detective work via google found a very enlightening article about this problem. A quick read of this explained:

It turns out ColdFusion was asking Connector/J for the metadata on every field, which in turn triggered a SHOW FULL COLUMNS query for every varchar and text column returned.

It also went on to say that is had been fixed as of Connector/J 5.07. On reading the release notes it mentions that the

Driver detects when it is running in a ColdFusion MX server (tested with version 7), and uses the configuration bundle coldFusion, which sets useDynamicCharsetInfo to false (see previous entry), and sets useLocalSessionState and autoReconnect to true.

However, from my testing it wasn't doing it as we were still seeing the problem. However, we are running the MultiServer version of ColdFusion which sits atop Jrun4. So I added the parameter directly to the querystring:


&useDynamicCharsetInfo=false

So it now looks like:


jdbc:mysql://127.0.0.1:3306/mydbname?allowMultiQueries=true&useDynamicCharsetInfo=false

This has stopped the problem, and given a significant performance gain. On one of the sites I was load testing it gave a 12% throughput increase and on another one which had less queries it gave a 4% increase. Hope it helps. Cheers, Mark

CF 7.0.2 Cumulative Hotfix 2 breaks MySQL Multiple Queries

Wed, 23 Apr 2008 13:40:00 --1000

I've just spent a while figuring out a painful bug in CF and am posting here so that I can reference it from the Adobe bug tracker as I can't explain the problem in 2000 characters.

Summary of Problem

CF 7.0.2 Cumulative Hotfix 2 and 3 breaks the use of Multiple SQL statements with MySQL connectorJ connector. This is a regression as it works perfectly in CF 7.0.2 and CF 7.0.2 with Cumulative hotfix 1. I have tested the following configurations:

CF7.0.2 MySQL connectorJ 5.0.8 - works
CF7.0.2 hotfix 1 MySQL connectorJ 5.0.8 - works
CF7.0.2 hotfix 2 MySQL connectorJ 5.0.8 - broken
CF7.0.2 hotfix 3 MySQL connectorJ 5.0.8 - broken
CF7.0.2 MySQL connectorJ 3.1.10 - works
CF7.0.2 hotfix 1 MySQL connectorJ 3.1.10 - works
CF7.0.2 hotfix 2 MySQL connectorJ 3.1.10 - broken
CF7.0.2 hotfix 3 MySQL connectorJ 3.1.10 - broken

Steps to reproduce bug:

1. Set up a CF 7.0.2 Multiserver install with built in webserver 2. Download and install ConnectorJ JDBC driver for MySQL and install as per Adobe Technote http://www.adobe.com/go/6ef0253 3. Create a mysql database named "test" the and create a table with the following script:


CREATE TABLE `tbl_test` ( 
	`id`  	int(11) AUTO_INCREMENT NOT NULL,
	`data`	varchar(50) NULL,
	PRIMARY KEY(`id`)
)

4. In CF Admin create a DSN named dsn_test with following settings

JDBC Url: jdbc:mysql://127.0.0.1:3306/test?allowMultiQueries=true
Driver Class: com.mysql.jdbc.Driver
Driver name: (leave blank)
username: (valid user for your db - default: root)
password: (valid password for your db - default: 'blank')
Place the following file in the /opt/jrun4/servers/cfusion/cfusion-ear/cfusion-war directory


MySQL driver version:


 #oDriver.getMajorVersion()#.#oDriver.getMinorVersion()#

	Unknown




	INSERT INTO tbl_test (data)
	VALUES ('Test String');
	SELECT LAST_INSERT_ID() as lastInsertID;




	No Query set returned from query.

This code should function correctly and return a cfdump of the auto generated primary key as per expected screenshot. Next apply Coldfusion Cumulative hotfix 2 and restart coldfusion as per Adobe Technote http://www.adobe.com/go/kb400996 Re run the test and no query set will be returned from the driver. The qTest variable does not get set as per the broken screenshot.

Related Resources

Technote re upgrading MySQL JDBC Drivers http://www.adobe.com/go/6ef0253 CF Updates page http://www.adobe.com/support/coldfusion/downloads_updates.html#mx7 CF 7.0.2 Cumulative hot 3 http://www.adobe.com/go/kb402465 CF 7.0.2 Cumulative hot 2 http://www.adobe.com/go/kb400996 CF 7.0.2 Cumulative hot 1 http://www.adobe.com/go/kb400074 MySQL ConnectorJ http://www.mysql.com/products/connector/j/ MySQL ConnectorJ 3.1.10 Download: http://dev.mysql.com/get/Downloads/Connector-J/mysql-connector-java-3.1.10.tar.gz/from/pick#mirrors

Multiple queries with CFMX-MySQL JDBC connector

Sun, 10 Feb 2008 21:32:00 --1000

I found a little snippet the other day and included it in the tail end of a previous post - however, on reflection I thought it deserved a post of it's own. If you want to utilise multiple queries per sql statement and you are using the MYSQL JDBC connector you need to add the following to your jdbc connector querystring.


allowMultiQueries=true

So you querystring would look like:


jdbc:mysql://127.0.0.1:3306/mydbname?allowMultiQueries=true

This will allow you to do queries like the following to return autoincrement values:




 INSERT INTO tbl_demo (name)
 VALUES ( );
 SELECT last_insert_id() as newID

ID of value is: #qInsert.newID#

Please note however that this functionality is disabled as it can leave the door open for SQL Injection attacks. However, as long as you always use cfqueryparam for all the dynamic parts of your query you will be fine.

CFMX Mysql Query String for UTF8 and Multiple Queries

Thu, 07 Feb 2008 12:56:00 --1000

While testing some of the code we've written for UTF8 support for double byte characters (Chinese, Japanese, Malaysian) I discovered that there are some voodoo JDBC connection string parameters that have to be set to make it work. Firstly, CFMX used UTF8 by default so it's already configured. You need to make sure that your MySQL DB table has a Charset of utf8 as per example below:


CREATE TABLE `utf8test` ( 
	`id`     	int(11) NOT NULL,
	`data`    	varchar(25) NULL,
	PRIMARY KEY(`id`)
)CHARACTER SET utf8 COLLATE utf8_general_ci;

Then you need to add the following params to the connection string:

characterEncoding=UTF8
characterSetResults=UTF8

I also add the "allowMultiQueries=true" string which allows you to execute multiple queries in the one statement. It is disabled by default as it can open up a window for SQL Injection attacks, but this can be mitigated by always using CFQUERYPARAM's. So the final connection string looks like this:


jdbc:mysql://127.0.0.1:3306/utf8demo?allowMultiQueries=true&characterEncoding=UTF8&characterSetResults=UTF8

Hope it helps, Cheers, Mark

Cross platform Database IDE

Sat, 15 Sep 2007 05:29:00 --1000

Ever since I've moved to using Linux full time I've been looking for a solid Database IDE. I've tried and tested quite a few.

Aqua Data Studio

This is my current DB IDE of choice and has some significant features in the new version 6.5 that means I'll be staying with it for a while, particularly as it runs on Linux, Macs and Windows identically.

Schema Comparison - very handy for keeping development and production db's schemas in sync.
Charts & Pivot Tables - makes it easy to create adhoc graphs withouth having to resort to Excel/OpenOffice Calc
Morph to Delimited List - A simple but powerful function that saves a lot of time. Allows you to take the results of a sql query and convert to comma seperated list. I've done dozens of adhoc CF scripts to handle this before :-)
Subversion Support - As we use subversion for our code it's handy to have it built in.

MySQL GUI tools (Query Browser & Admin)

These have some very nice functionality but have always found them to be pretty unstable - they just crash a lot with no explanation.

Navicat

This is pretty good option, but the Linux version is the poor cousin to the Windows & Mac versions.

Love to hear any comments feedback or suggestions.

Compare db's with DB Diff tool for CFMX

Thu, 19 Jul 2007 10:30:00 --1000

As a cf developer I have lots of tools for managing source code with excellent version control systems, cfeclipse, and the luxury of dev, staging and production environments for code. Despite all these tools, one area that can be difficult to manage is changes to database schema's as applications evolve. For example - as there are seperate dev, staging and production db's you need to be meticulous in scripting all changes and remembering to apply all changes across all db's at appropriate times. I've been reading about how Ruby on Rails manages db changes with "Migrations" which seem like a pretty good solution but they have significant limits and are not perfect solution. In my ongoing quest to make my life easier I wanted a tool which could tell me the difference between two databases - eg dev and staging db's - and save me having to check each table and row. Initially I used mysqldump and a diff tool to see the differences - which worked a treat but was a lot of repetitive steps to get the report I needed. Perfect for some automation. After a bit of searching I found a CFMX diff cfc by Rick Osborne which was allowed me to very simply create a super simple Datasource compare tool as shown in the screenshot. I can now quickly compare the different db's by setting up datasources on my machine for each one, and ensure that they are always in a consistent state. Download it, give it a spin and let me know if it's any use. Note that it only works in MySQL 5 at the moment, but would likely be simple to port to other dbs. Also very happy to hear if anyone has better solutions or processes for keeping DB's in sync. Cheers, Mark