Hi all,I was asked this question in an e-mail -- feel free to ask your questions in the comments, as I will point the original author to this post to answer those questions. There is not a lot of data here, so instead of me asking questions in an e-mail I figured I would open it up to the (MySQL) world. Without further ado, here's the question:Basically when we open more than one connection- we're opening 5, and we do processing in mysql (innodb tables) the server cpu and m

I was asked this question recently, and I thought it was a great little tidbit of knowledge to pass along. The short answer is "no". The slightly longer answer was written up by Jan Kneschke when dealing with a forum post about proxy + connection pooling.From http://forums.mysql.com/read.php?146,169265,169700The clear-text password is _never_ transfered in the authentication phase.On the network we have:

The answers to the last pop quiz are up: http://www.pythian.com/blogs/868/pop-quiz-mysql-clusterSo here's another pop quiz. Given the following:Welcome to the MySQL monitor. Commands end with ; or \\g.Your MySQL connection id is 16450949 to server version: 4.1.14-standard-logType 'help;' or '\\h' for help. Type '\\c' to clear the buffer.mysql> select count(*),length(password) from mysql.user group by l

While doing a standard audit for a new client, I recommended a few changes to get better performance. Because I had several changes, I used the documentation at http://dev.mysql.com/doc/refman/5.0/en/server-system-variables.htmland found that "innodb_flush_log_at_trx_commit" is a dynamic variable. I was surprised, because most operations dealing with file sizes and operations are not dynamic.

Recently while we were building a slave with a newer version of MySQL 5.1 from an InnoDB Hot backup, the following error occurred when we ran "mysql_upgrade":mysql.time_zone OKmysql.time_zone_leap_second OKmysql.time_zone_name OKmysql.time_zone_transition OKmysql.time_zone_transition_type OKmysql.user OKRunning 'mysql_fix_privilege_tables'...ERROR 13 (HY000) at line 311: Can't get stat of './mysql/general_log.CSV' (Errcode: 2)ERROR 13 (HY000) at line 316: Can't get stat of './mysql/slow_log.CSV' (Errcode: 2)FATAL ERROR: Upgrade failedThe problem is that in MySQL 5.1, it is possible to log the slow query log and general log to tables in the "mysql" schema (source: Selecting General Query and Slow Query Log Output Destinations). These tables are  []

MySQL Full Text Search LimitationsSuppose you have a MyISAM table containing a column with a full text index. This table starts to grow to a significant size (millions of rows) and gets updated fairly frequently. Chances are that you'll start to see some bottlenecks when accessing this table, since without row level locking, the reading and writing operations will be blocking each other.A solution that many people would suggest right away is to use the master for writes and a slave for reads, but this only masks the problem, and it won't take long before enough read traffic on the slave starts causing slave lags.Why Sphinx?

A broken VIEW was caused by the view's definer being non-existent. In this particular system, backups are done by replicating all the machines (production, development, test, etc) to one server and doing cold physical backups off that server, which currently has 12 instances running.So in order to find on what machine the user might still be defined, I went to the backup server. All the data directories are in one path, ie:instance 1 has a datadir of /data/mysql/instance1instance 2 has a datadir of /data/mysql/instance2Now, the unix tool "strings" can be used against many types of files. In particular, though, you can use "strings" on the mysql/user.MYD file to see the username, host, and password hash.

I was writing up some notes about a client's status variables, and I'd had to remind myself of Handler_read_first. Handler_read_first is a counter that increments when the first entry in the index is read, which may indicate that a full index scan is being used. With this particular client, I ran a SHOW STATUS, waited 100 seconds, and ran it again. Handler_read_first jumped by about 53. So I wrote down in my notes:first in the index...every other second. Lots of table scans?As s

I just filed a very annoying bug when trying to compile with plugin engines using the 5.1.xx source tarball.DescriptionI am trying to test SphinxSE as a plugin instead of getting it statically linked and came across an annoying bug. When using the configure "--with-plugins" option only once, the engine is statically linked. When using it twice, the first engine is created as a plugin, and the 2nd one is linked statically. Here are a couple of examples:

This tip is to overcome a type of expensive query that I see all the time. In doing this, I uncovered a nasty bug in MySQL v5.1.ScenarioI've seen queries like these several times:SELECT COUNT(*) FROM sample WHERE status = n;Normally, this is OK, but if you have hundreds of millions of rows, only a handful of possible statuses and execute this query dozens of times per minute on an active InnoDB table, it quickly becomes a bottleneck.One SuggestionEstablis

Today's trivial MySQL system variable:old_alter_tableThe interesting bit is that this is a system variable, and shows up in SHOW GLOBAL VARIABLES, but is not documented on the Server System Variables manual page.Instead, it is documented on the manual page for Server options.Unfortunately, that documentation is v

A short time ago I posted how I was Using XtraDB Backup to backup InnoDB. Overall, the blog post was positive, but experiences that others have had (and commented to on that blog post) have made me want to put out another short article about using XtraDB backup.The first few points remain the same -- the backup process is stable, we were able to use the binaries without compiling, and using Innobackupex as the wrapper script, analogous to Innobackup.pl. However, we did figure out why Xtrabackup had to be run as the mysql user:Xtrabackup writes to the data dictionary file (ibdata1, for example). We have not examined if it also writes to the data and index files (still ibdata1 by default, or the .ibd files when using innodb_file_per_table). [EDIT: The authors of Xtrabackup have commented below as to why the write occurs:xtrabackup is kind of very small and restricted instance of InnoDB, and reuses a lot of InnoDB code.InnoDB by  []

At the November MySQL User Group, Patrick Galbraith ran into a problem where "binlog-do-db" was duplicated. It manifests itself like this:(copied from http://lists.mysql.com/replication/607) master my.cnf:binlog-do-db=db1slave my.cnf:replicate-do-db=db1;Relevant show slave status outputReplicate_Do_DB: db1;,db1;When db1 is modified on master, Read_Master_Log_Pos and Relay_Log_Pos dochanges, also

A friend asked for this, so I thought it'd be helpful:All tables with no primary key:use INFORMATION_SCHEMA;select CONCAT(t.table_name,".",t.table_schema) as tbl,from TABLES AS t LEFT JOIN KEY_COLUMN_USAGE AS c ON (t.TABLE_NAME=c.TABLE_NAME AND c.CONSTRAINT_SCHEMA=t.TABLE_SCHEMA AND constraint_name='PRIMARY') WHERE t.table_schema!="information_schema" AND constraint_name IS NULL;All tables and their primary keys, if exist:use INFORMATION_SCHEMA

In Spotting the Wolf in Sheep's Clothing, Frank Mash writes about a specific person who is spreading fear, uncertainty and doubt about MySQL.Now, this always gets me, especially with MySQL. For how long will MySQL be the bastard stepchild of the database world? Because really, it's been a full-fledged DBMS for at least 5 years. 10 years ago there were no transactions, but....that was 10 years ago! 10 years ago everyone made $100,000 per year, took 3 hour lunch breaks, played foosball in the office, coveting their IT stock and sat in massage chairs with Apple Cinema displays.How much have *you* changed in 10 years? Your digital world? 10 years ago we paid for text messages by the character, which is why we came up with rlly abbr wrds like kthxbye! and OMG, LOL, ROFL and my favorite, ROFLPMP -- because it makes me giggle whenever I meet a professional Project Manager who has PMP as a suffix.Whoops, mini-rant aside....don't hate  []

MySQL's manual page for InnoDB's adaptive hash states: If a table fits almost entirely in main memory, the fastest way to perform queries on it is to use hash indexes. InnoDB has a mechanism that monitors index searches made to the indexes defined for a table. If InnoDB notices that queries could benefit building a hash index, it does so automatically.Note that the hash index is always built base

A friend of mine asked me:I'm hoping you can help me out with something -- I'm trying to optimize a search feature. Since it uses a MySQL database, the search already uses the "LIKE" statement to get matches for a search query, we might be needing something more flexible. I found mention on MySQL's website about something called the "SOUNDS LIKE" expression that can be more flexible than "LIKE". Do you know anything about this? If you do, can you point me a direction where I might be able to learn more about it? Thanks in advance for your help!My response:

The listing of Dynamic System Variables on the MySQL Reference Manual's page is supposed to show those variables you can change on-the-fly."innodb_data_home_dir" is listed there as a dynamic variable, but it is not one, and trying to change it on-the-fly doesn't work: mysql> set global innodb_data_home_dir="/data/mysql/data";ERROR 1193 (HY000): Unknown system variable 'innodb_data_home_

It felt like the right time for us to look back at some useful commands for table maintenance that some of us may not have mastered as much as we might like to think.In my post about gathering index statistics, I referred to "OPTIMIZE TABLE", "ANALYZE TABLE", and "REPAIR TABLE" -- but I never explained in depth what the different commands do, and what the differences between them are. That is what I thought I would do with this post, focusing on InnoDB and MyISAM, and the differences in how they treat those commands. I will also look at different cases and see which one is right for in each case.

I recently needed to set up multiple MySQL servers on a test computer to simulate a master-slave setup. I had never done this before, so I think it might be useful for others if I documented what occurred. This setup was done on a Linux server, however other platforms should operate similarly. I choose to use the "mysqld_multi" script to manage these instances. There is a way that you can compile the MySQL server with different network interfaces, but in my opinion, using "mysqld_multi" to manage activities is much easier.So shall we begin?