The maximum length of a VARCHAR is only restricted by the maximum row length. In most storage engines, the maximum row length is the maximum allowed by MySQL, which is 65,535 bytes. Only the NDB storage engine has a different maximum value.
I was doing some research over the weekend on how transactions work “under the hood” in MySQL. I thought it might be enlightening if I wrote about what I found. Have a look.
I had so much to say in response to a recent post asking about virtualization from Jennifer Glore that I realized it was long enough to be a blog post.
Every so often we have a client worrying about unauthenticated users. Who are these unauthenticated users, how do they get there, and why aren’t they authenticated? The client-server handshake in MySQL is a 4-step process. Those familiar with mysql-proxy already know these steps, as there are four functions that a Lua script in mysql-proxy can override. The process is useful to know for figuring out exactly where a problem is when something breaks.
Get it while it’s hot! The Fall 2008 issue of the MySQL Magazine is now available at http://www.mysqlzine.net. Issue 6 is chock full of 16 pages good stuff. Download the PDF directly or go to the MySQL Magazine page to download any and all of the 6 issues.
Recently I was working on a problem for a customer. They are converting a server with two InnoDB data files to innodb_file_per_table: I honestly don’t recall ever seeing (or hearing about) so large a data file. The method chosen for conversion boils down to this: stop and start the server to enable innodb_file_per_table, alter all tables to myisam, stop server and delete ibdata file, restart server, convert tables back to InnoDB, add foreign keys. This post isn’t about how we did it, or about whether or not it was the best way, or anything like that. This post is really about the the last two steps.
The other day we began to encounter weird and random errors on small and innocent queries that shouldn’t give any errors at all. It all lead to one of our most basic health checks failing for no apparent reason. The first clue that popped into our minds was related to case-sensitivity, since the failing check was looking for the column names of the only table in the schema with UPPER CASE name. This symptom was especially weird since all of this was happening on MySQL setups running on Windows, and so we’re not sure if this was being caused by some internal code library, MySQL, or Windows itself. Here’s how to fix it.
Recently I had an interesting issue crop up. Due to an unfortunate migration incident in which involved master/master replication and not checking to see if replication was caught up, we ended up with an infinite replication loop of a number of SQL statements. awk helped immensely in the aftermath cleanup. The basics of the replication infinite loop were…
In many parts of the world times are uncertain. I live in the United States and we are in the middle of a financial meltdown that many fear may be as bad as the Great Depression. If you are involved with MySQL as a database administrator, or if you work directly with MySQL in some other aspect, you can probably breathe a little easier. Why is this? MySQL Server has grown in market penetration for a long time. It is now a significant section of the RDBMS pie.
This is a post about SYSDATE() and NOW() and CURRENT_TIMESTAMP() functions in MySQL. Firstly, note is that of these three, only CURRENT_TIMESTAMP() is part of the SQL Standard. NOW() happens to be an alias for CURRENT_TIMESTAMP() in MySQL. Secondly, note that replication does not work well with non-deterministic functions. You can start to see the problem here….but there’s more….