Continuing on from preparation in our MySQL latin1 to utf8 migration let us first understand where MySQL uses character sets. MySQL defines the character set at 4 different levels for the structure of data.

• Instance
• Schema
• Table
• Column

In MySQL 5.1, the default character set is latin1. If not specified, this is what you will get. For example.

mysql> create table test1(c1 varchar(10) not null);
mysql> show create table test1\G
Create Table: CREATE TABLE `test1` (
  `c1` varchar(10) NOT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1

If you want all tables in your instance to always be a default of utf8, you can changed the server variable character_set_server. This can be set dynamically.

mysql> set global …

March 1st Meeting
Workbench and Future

Workbench suddenly has a new bunch of features and that will be the main subject of Monday's meeting. This visual design, documentation, and all around database tool is quickly evolving into a 'must have' tool in your arsenal.

Plus, the North Texas MySQL Users Group Will be losing our home in a few months. We need to discuss if we will seek another, join the Dallas Oracle Users Group as a Special Interest Group or ???


Seven PM Sharp
Suite 700
16000 Dallas Tollway
Dallas Texas

The source version for MySQL Cluster 6.3.31b has now been made available at ftp://ftp.mysql.com/pub/mysql/download/cluster_telco/mysql-5.1.41-ndb-6.3.31b/

This replaces MySQL Cluster 6.3.30 and 6.3.31.

You can either wait for the binaries to be released or if you’re in a rush then you can find instructions on building the binaries for yourself in the earlier article: “MySQL Cluster 7.0.7 source released“.

A description of all of the changes (fixes) that have gone into MySQL Cluster 6.3.31b (compared to 6.3.30) can be found in the MySQL Cluster 6.3.31b Change Log

The source version for MySQL Cluster 7.0.11b has now been made available at ftp://ftp.mysql.com/pub/mysql/download/cluster_telco/mysql-5.1.41-ndb-7.0.11b/

This replaces MySQL Cluster 7.0.10 and 7.0.11.

You can either wait for the binaries to be released or if you’re in a rush then you can find instructions on building the binaries for yourself in the earlier article: “MySQL Cluster 7.0.7 source released“.

A description of all of the changes (fixes) that have gone into MySQL Cluster 7.0.11b (compared to 7.0.10) can be found in the MySQL Cluster 7.0.11b Change Log

I recently had the pleasure of logging into an AIX 5.3 machine for the first time ever, to debug a corefile.
Firstly, having the mysqld binary and core is not enough, unless you have an identical machine on which to study the corefile. Library mismatches can be a problem.. IBM was kind enough to provide the snapcore utility to solve this easily.
Snapcore will gather all the libraries and create a single archive contain libs, binary, core.
So we now have a file called something like: snapcore_555060.paxOn our dev box, extract the pax archive:
gunzip snapcore_555060.pax.Zpax -r -f snapcore_555060.pax
On your dev AIX box, make sure you have DBX installed!!
bash-3.00# lslpp -l | grep …

I recently presented a webcast hosted by O’Reilly and Webex.  In it I take you on a step-by-step installation of DRBD and MySQL.  I start by using Sun’s Virtualbox to create to virtual machines running CentOS.  I then explain how to configure them with virtual external drives to use for DRBD.  I next configure the network interfaces to support routed packets into and out of the boxes.  Then I install various packages with yum, configure drbd and finally install MySQL as the last step.  You can follow along at the command line and do it yourself on a Windows, Mac or Linux box.

In the past I came across a scheduled cronjob to dump and restore a subset of a table that was changed to a MySQL view into the authoritative data. The idea is , though the initial implementation sucked due to a single query taking 300% longer than before. Here is why.

SELECT MAX(Entered) FROM Table1;

So the above query is looking at a timestamp column within a table. It basically was finding the newest entry in the table.


So as a background, the existing dump/restore would dump out the contents of table with SQL:

SELECT * FROM Table1 WHERE (Entered >= unix_timetamp(now() - 32*24*3600) );

The new view that was created was:

CREATE VIEW Table2 SELECT * FROM Table1 WHERE (Entered >= unix_timetamp(now() - 32*24*3600) );


So the issue was that

SELECT MAX(Entered) FROM Table1; took 0.3 seconds though
…

I came across the following issue not too long ago


Here is the error:
Last_Errno: 1206
Last_Error: Error 'The total number of locks exceeds the lock table size' on query. Default database: 'Foobar'. Query: 'REPLACE LOW_PRIOR ITY INTO blah (ID,Modified,Feature,Style, ....


That begs the question - where and what is the 'lock table', how can I look into it to see the space constraints and why does this occur.

A quick google led me to the following post http://mrothouse.wordpress.com/2006/10/20/mysql-error-1206/

“in Innodb row level locks are implemented by having special lock table, located in the buffer pool where small record allocated for each hash and for each row locked on that page bit can be set.”


Thus Innodb records row level locking in the buffer pool. So what appears to have happened is that the active set in the …

One aspect of my positions with Calpont, MySQL, and other companies I’ve worked for has been to interact with the various analysts who cover the database scene. It’s definitely an interesting part of the job, especially when you get to query some of the experts who have been around a long time and are good at making solid technology calls on where things are headed.

IDC is one such group of experts and they recently released an interesting set of predictions on where databaRead More...

The headline is flame-bait, don’t take it. I just wanted to point something out about character sets and collations in MySQL. To the uninitiated, it may seem overwhelming. Everything has a character set! Everything has a collation! And they act weirdly! The server has one. The database has one (oh, and it changes magically as I USE different databases.) Every table has one, and columns too. Is that all? NO! My connection has one!