There are those that are very adamant about letting people know that using INFORMATION_SCHEMA can crash your database. For example, in making changes to many tables at once Baron writes: “querying the INFORMATION_SCHEMA database on MySQL can completely lock a busy server for a long time. It can even crash it. It is very dangerous.” Though Baron is telling the truth here, he left out one extremely important piece of information: you can actually figure out how dangerous your INFORMATION_SCHEMA query will be, ahead of time, using EXPLAIN.

On first glance, it looks like TEXT and VARCHAR can store the same information. However, there are fundamental differences between the way TEXT fields and VARCHAR fields work, which are important to take into consideration.

Video: Building a MySQL Slave and Keeping it in Sync At the Boston MySQL User Group Sheeri Cabral presented on how to get a consistent snapshot to build a slave, how to use mk-table-checksum to check for differences between masters and slaves on an ongoing basis, and how to use tools such as mk-table-sync and mysqldump to sync the data if there are any discrepancies. This link contains the slides and the videos

Author's announcement that the MySQL Bible, for beginner and intermediate MySQL DBA's, is available. The book is suitable for those completely new to databases, or those that are familiar with another RDBMS (such as Oracle or Microsoft SQL Server) and want to learn how MySQL works.

sar-sql name change, state of the software and road map.

The Boston MySQL User Group got Keith Murphy to speak at the June User Group meeting, about backups. Direct play the video at:http://technocation.org/node/559/playDirect download the video (351 MB) at:http://technocation.org/node/559/downloadLinks referred to in the presentation:MyLVMBackup by

Giuseppe Maxia and Sheeri K. Cabral give an introduction to what MySQL is. A PDF of the slides can be downloaded at http://technocation.org/files/doc/2009_04_Tour.pdf (21 Mb). Links referred to in the presentation, or related to the presentation: The MySQL forge can be found at http://forge.mysql.com, which contains the Wiki (http://forge.mysql.com/wiki) as well as the worklog, code snippets and tools for use with MySQL. Planet MySQL is an aggregate of MySQL-related blogs, including Giuseppe's and mine -- http://planet.mysql.com The website for the 2009 MySQL User Conference and Expo is at http://mysqlconf.com. MySQL Camp is a free conference at the same time as the User Conference. The details, including the schedule, are at http://forge.mysql.com/wiki/MySQLCamp2009Sessions. A collection of User Group videos is at http://technocation.org/category/areas/user-group. All the videos, including User Group ones, are  []

Part 2 of "Understanding How MySQL Works by Understanding Metadata", presented by Sheeri K. Cabral (The Pythian Group) and Patrick Galbraith (Lycos Inc.). This was a 3-hour tutorial. Part 2 is at http://www.youtube.com/watch?v=C_Uv_4I9gus The PDF of the slides can be found at http://technocation.org/files/doc/2009_04_Understanding.pdf. From the official abstract at http://www.mysqlconf.com/mysql2009/public/schedule/detail/5682: We have spent countless hours researching over 1,000 pieces of metadata. In the process, we have learned a lot about how MySQL works, and realized that it was a pretty good learning method. Examples: Understanding the query_cache% system variables and Qcache% status variables helps us learn about the query cache—what it is, when it is used, how to examine query cache efficiency, how to tune the query cache. This relates to the GLOBAL_VARIABLES and GLOBAL_STATUS system  []

Part 1 of "Understanding How MySQL Works by Understanding Metadata", presented by Sheeri K. Cabral (The Pythian Group) and Patrick Galbraith (Lycos Inc.). This was a 3-hour tutorial. Part 2 is at http://www.youtube.com/watch?v=V3IBVsYGdtA The PDF of the slides can be found at http://technocation.org/files/doc/2009_04_Understanding.pdf. From the official abstract at http://www.mysqlconf.com/mysql2009/public/schedule/detail/5682: We have spent countless hours researching over 1,000 pieces of metadata. In the process, we have learned a lot about how MySQL works, and realized that it was a pretty good learning method. Examples: Understanding the query_cache% system variables and Qcache% status variables helps us learn about the query cache—what it is, when it is used, how to examine query cache efficiency, how to tune the query cache. This relates to the GLOBAL_VARIABLES and GLOBAL_STATUS system  []

What's new, in a nutshell: http://dev.mysql.com/doc/refman/5.1/en/mysql-nutshell.html Release notes: http://dev.mysql.com/doc/refman/5.1/en/news-5-1-x.html (In the video, it's the page entitled "Changes in release 5.1.x"). And yes, very early on (at about 2 minutes in) I talk about my take on Monty's controversial post at http://monty-says.blogspot.com/2008/11/oops-we-did-it-again-mysql-51-released.html The slides can be downloaded as a PDF at http://technocation.org/files/doc/2008_12_New51.pdf or in Open Office presentation format at http://technocation.org/files/doc/2008_12_New51.odp

Sheeri Cabral speaks about the changes in MySQL 5.1 and everything you need to know about upgrading. Get the slides at http://technocation.org/files/doc/2008_12_New51.pdf See the accompanying blog post with more information at http://www.pythian.com/news/1414/new-in-mysql-51-sheeris-presentation

(one note, I have updated a previous blog post by adding the video for the LISA presentation I gave entitled "How to Stop Hating MySQL")OpenSQL Camp is in full swing! Baron Schwartz has done an amazing job organizing this free unconference.We are well into the 2nd session of the day, and the quality of the presentations is excellent (though I expected that!) and it is always great to see people.Some pictures....

I was surprised to find out that Innobase's Innobackup script did not read the socket file location from the "my.cnf" file.� So I added in as a feature.A few notes:\tThese modifications are against version 1.4.0 of the script.� A quick search for the "socket" keyword in the 1.5.0 version of the script did not reveal any simillar functionality to what I'm proposing here.\tThese modifications have not actually bee

So you want to store URLs in MySQL, and the URLs have those annoying "%20%27%7C%26%5E%2B%2D%25" symbols? And you want to be able to show your users some kind of human-readable information. You might want to consider using this trick. Take this list of commonly escaped characters as an example:%20 - space%27 - '%7C - |%26 - &%5E - ^%2B - +%2D - -%25 - %So, how about we do some search'n'replace on that?mysql> SET @url:='%20%27%7C%26%5E%2B%2D%25';Query OK, 0 rows affected (0.00 sec)mysql> SELECT @url as original, -> REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE( -> REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE( -> @test,'%20',' '), -> '%27','\\''), -> '%7C','|'), -- REPLACE() is case sensitive -> '%7c','|'), -- so we have -> '%26','&'), -> '%5E','^'),  []

Yesterday evening, a friend of mine had some issues with installing DBD::mysql, and asked if I had encountered the same issue. The problem, as the output from "make test" showed, was that certain symbols was missing:# Tried to use 'DBD::mysql'.# Error: Can't load '/Users/westerlund/src/perl/DBD-mysql-4.008/blib/arch/auto/DBD/mysql/mysql.bundle' for module DBD::mysql: dlopen(/Users/westerlund/src/perl/DBD-mysql-4.008/blib/arch/auto/DBD/mysql/mysql.bundle, 2): Symbol not found: _is_prefixFair enough, this is related to a 64-bit issue with MySQL---at least with my Perl version, which is now: Summary of my perl5 (revision 5 version 10 subversion 0) configuration: Platform: osname=darwin, osvers=9.5.0, archname=darwin-thread-multi-64int-2levelIf you try to link to a x86_64 version of MySQL, then you get the above mentioned error. So, I downloaded an x86 version of MySQL and tried again. The output from  []

The unsung heroes of InnoDB are the logfiles. They are what makes InnoDB automatic crash recovery possible.Database administrators of other DBMS may be familiar with the concept of a "redo" log. When data is changed, affected data pages are changed in the innodb_buffer_pool. Then, the change is written to the redo log, which in MySQL is the InnoDB logfile (ib_logfile0 and ib_logfile1). The pages are marked as "dirty", and eventually get flushed and written to disk.If MySQL crashes, t

Over the weekend, I worked on a client's two computers, trying to get a slave in sync with the master. It was during this time that I began thinking about:a) how this never should have happened in the first place.b) how "slave drift" could be kept from happening.c) how this is probably keeping some businesses from using MySQL.d) how MySQL DBAs must spend thousands of hours a year wasting time fixing replication issues.I'll be the first person to tell you that the replication under MySQL is pretty much dead-simple to set up. My only complaint is that it is annoying to type in the two-line "CHANGE MASTER" command to set up a new slave. Even so, it makes sense.It is also very easy, however, for a slave to end up with different data than the master server has. This can be caused by replication bugs, hardware problems, or by using non-deterministic functions. Without proper permissions, a user/developer/DBA can log  []

As I putter around the MySQL "INFORMATION_SCHEMA", I am finding lots of undocumented behavior for fields that should be straightforward. For example, the "VIEWS" table holds information about views, and the "VIEW_DEFINITION" field contains the view definition, right?Well, when I was looking at the "VIEW_DEFINITION" today, I noticed an odd thing. Even though I had permissions to see the view definition (as proven by the "SHOW CREATE VIEW

A few days ago I had a new idea for a blog post. A post about what it really takes to be a good database administrator. I began by researching what others had done on the topic. At the end of this post you will find links to six of the posts I found that provided some insight into this question. Even after uncovering this information, I thought I could add something to the mix from my own experiences. So here we go!

Simple auditing, i.e., knowing what changed recently, can save you tons of time while troubleshooting.I know that, in the ideal world:\tEverything is supposed to be done through configuration management.\tEverything is documented and all changes are tracked through a VCS.\tEvery DDL or set global is trapped via MySQL Proxy and logged.But there are always ways to bypass the gatekeepers. Changes can go in unnoticed. An hour or so later, your database performance suddenly changes for the worse, and you get that phone call.First you check if anything caused an actual error. You look around at a few log files and nothing shows up. The next thing you ask yourself is, did someone change anything in the last little while. Of course, everybody says no. After a few hours of digging, comparing schemas, diff-ing old and current config files, you actually find what has changed, put it back the way it was and everything is back to normal.  []