Blog Archives

I deal with all shipes and sizes, and distributions, of Linux. Each Linux distribution has quirks. Particularly with package management. There are some people who are religious about RPM (Redhat, Centos, Fedora, Suse, etc.) or APT (Debian, Ubuntu, etc). I see both good and bad points of each. In my daily tasks, I just want “Things to Work (TM)”. I had an interesting problem the other day that I found a solution for that I thought I would share.

The problem was on a server that I was trying to install Percona 5.5 on that already had a stock Centos MySQL 5.1 install. I need to uninstall the existing packages:

[root@host]# rpm -e mysql-5.0.77-4.el5_6.6
error: "mysql-5.0.77-4.el5_6.6" specifies multiple packages

Hmm, this is very odd. I’ve never seen this. There is a way to reveal exactly what the problem is:

[root@host]# rpm -q --queryformat "%{name}.%{arch}\n" mysql
mysql.x86_64
mysql.i386

Interesting! So, somehow, someone installed both the 64-bit and 32-bit versions of MySQL. Now that I know the exact error, I was able to remove each separately:

[root@host]# rpm -e mysql.x86_64

[root@host]# rpm -e mysql.i386

[root@host]# rpm -qa|grep mysql

Good, the problem is solved!

So, if ever you find yourself in this predicament, you know what to do. This also gives reason to perhaps to refer back to the RPM manual to find other useful tips.

I know most articles are more about DBA issues, however, being a MySQL DBA means that you have several hats that you can where– data architect, application developer, MySQL source developer, and often good old fashioned DBA. I recently had an interesting problem with a particular project I was working on that required the MySQL source code developer hat that I used to wear much more often during the black vodka era of working for MySQL AB.

This was case of a custom storage engine a client has, and whenever I would try to use statement-based replication, I would encounter an error upon running DML statements on the master:

ERROR 1598 (HY000): Binary logging not possible.
Message: Statement-based format required for this statement,
but not allowed by this combination of engines

Despite having worked on several storage engines, written Federated/FederatedX, I couldn’t remember what about a storage engine pertains to replication, nor did it make sense to me that the storage engine would have anything to do with it since binary logging occurs at a higher level than at the storage engine.

I was talking to Monty about it, and he pointed me to look at ha_myisam.cc:

ha_myisam::ha_myisam(handlerton *hton, TABLE_SHARE *table_arg)
  :handler(hton, table_arg), file(0),
  int_table_flags(HA_NULL_IN_KEY | HA_CAN_FULLTEXT | HA_CAN_SQL_HANDLER |
                  HA_BINLOG_ROW_CAPABLE | HA_BINLOG_STMT_CAPABLE |
                  HA_DUPLICATE_POS | HA_CAN_INDEX_BLOBS | HA_AUTO_PART_KEY |
                  HA_FILE_BASED | HA_CAN_GEOMETRY | HA_NO_TRANSACTIONS |
                  HA_CAN_INSERT_DELAYED | HA_CAN_BIT_FIELD | HA_CAN_RTREEKEYS |
                  HA_HAS_RECORDS | HA_STATS_RECORDS_IS_EXACT),
   can_enable_indexes(1)
{}

How one forgets simple but important things over time, that being me! These flags are very important and determine the capabilities of a storage engine. I will post about what each is at a later time. Suffice to say, I had searched Google to no avail, and by posting this article, if you are trying to get replication working for a given storage engine, this will come up! The important flags you see here are HA_BINLOG_ROW_CAPABLE and HA_BINLOG_STMT_CAPABLE. I never used to pay as much attention to these particular flags when developing Federated because I would often use flags from another storage engine and was focused on flags that pertained more to what Federated could do and could not do. However, these flags are required if you want replication to work.

The flags in the client storage engine did have HA_BINLOG_ROW_CAPABLE but not HA_BINLOG_STMT_CAPABLE. Additionally, the flag HA_HAS_OWN_BINLOGGING was present. This is a show-stopper. The only engine that has this is NDB cluster.

No harm done, with the correct flags added, the custom storage engine now replicates!

Happy storage engine hacking!

I’m pleased to announce the release of DBD::mysql, the MySQL Perl client, 4.020. The release has several great fixes provided by the community, and in particular from Masahiro Chiba, who fixed several issues pertaining to prepared statement support as well as UTF8 support, something that I myself have had a difficult time fixing and testing.

From the Change log:

2011-08-15 Patrick Galbraith (4.020)
* Numerous (!! Thank you!!) fixes for prepared statements: Masahiro Chiba
- Chop blanks fixed
- UTF8 improvements
- fixed memory allocation for BLOBs
- auto-reconnect
* Fix in leak test, which failed sometime due to first assignment $prev_size over
paging (Masahiro Chiba)
* Catalog test allows use of schemas other than ‘test’ (Masahiro Chiba)
* Documentation fix for auto_reconnect (Karen Etheridge )
* Win32 and general installation fixes (Alexandr Ciornii, http://chorny.net)

I want to thank Masahiro, Karen and Alexandr for their contributions, which made this release possible. I want to again thank everyone who submits bugs as well as simply using this driver!

The release can be found at:

http://search.cpan.org/~capttofu/DBD-mysql-4.020/

As well as simply installing DBD::mysql from CPAN.

So often, you have a task where you need to perform an alteration of a huge table while not wanting to affect the operation of your website. With an alter table, MySQL will internally lock the table in question, create a temporary table with the new table definition, copy the date, then drop the current table, then rename the temporary table to the current table, then unlock tables– not acceptable for a running website!

Read the full article…

Dear Perl and MySQL enthusiasts,

I’m pleased to announce the release of DBD::mysql 4.019. I’m especially pleased because there are some new enhancements and features that have been provided by contributions from the community:

* Asynchronous support, added by Rob Hoelz. This is a new feature to DBD::mysql that takes advantage of libmysql’s asynchronous functions (see Jan’s article from 2008 http://jan.kneschke.de/2008/9/9/async-mysql-queries-with-c-api/) . From the DBD::mysql documentation: Read the full article…

I’ve had a week to think about and listen to the varying feedback around the O’Reilly MySQL and Collaborate conferences. There are many good points in the conversation I’ve been listening to. In particular, Baron Schwarz reminds everyone that the O’Reilly Conference brings together a unique cohort once a year that everyone looks forward to. He’s right. We have a fantastic community and an incredible density at that conference. But, the event doesn’t make the community.

Read the full article…

This morning at the MySQL User Conference and Expo, we’re excited to be included in Brian Aker’s keynote “The State of Drizzle” as one of the first organization to offer support for Drizzle now that it has reached GA with the release of Drizzle 7.  Brian Aker is the CTO of Data Differential and is the lead developer for the Drizzle Project.
Read the full article…

Hello to those of you who are in Santa Clara!  Patrick Galbraith and I will be here this week, speaking and glad handing.  You can find us both in the Attendee Directory and identify either or both of us as someone you’d like to meet.

Patrick’s Sessions are:

5:15pm Wednesday – Perl programming with MySQL and Drizzle
11:55am Thursday – Narada: using Gearman, Sphinx, Memcached, Drizzle and PHP to build a search application

I’ll be speaking in Wednesday night’s Ignite MySQLconf and at:

10:50am Thursday – MySQL replication – all data everywhere, is it a good idea?

And, I believe we’ll both be at the MySQL Community Dinner tonight.  I’m looking forward to seeing many familiar and lots of new faces.  I’ll even have my recorder with me to capture sound bites for the OurSQL podcast.  See you at Pedro’s!

I’m pleased to announce the release of the Narada Search Engine Application, version 0.202, as well as a ready-to-use AMI, ami-140df07d.

The source release can be found at Launchpad – lp:narada and and a packaged version at http://patg.net/downloads/narada-0.202.tar.gz

What is Narada? Narada is a search engine application. It does have a web application component, but it also has a very novel approach on the back-end in how it implements various functionalities by using Gearman, memcached, Sphinx and either MySQL or Drizzle as a data store. It is somewhat of a proof-of-concept that was borne from an idea I had one late night while working on my first book, Developing Web Applications with Apache, MySQL, memcached, and Perl. I wanted to show how useful using all these great new tools– Gearman, memcached and Sphinx — for scaling out functionality and data, along with MySQL, and later Drizzle.

Read the full article…

In the land of the MySQL council,  the IOUG has created a survey asking for community feedback on the future of the council and the IOUG’s programming for MySQL.  This is an excellent opportunity for us to shape how the largest independent user group supporting Oracle technologies helps our community grow and work with Oracle going forward.

Read the full article…