Eric Bergen

Copy and paste is normally a great idea. It saves time. Who wants to retype things over and over again? Not me. There are times when copy and paste can get you in trouble. This has bit me a few times in the past as well as other people. When this happens it usually causes and “oh shit! CTRL+c” to kill the query. The query gets cancelled on the master but still propagates to the slave. The master will happily continue executing while replication silently stays stopped on the slave.

The solution? Stop writes on the master. It really depends on the situation whether you want reads to continue or not. If not shutdown mysql (this will stop your clients from hanging) If reads can continue a simple flush tables with read lock; will be fine. Once the slave has caught up flush tables on the slave and copy the table to the master. Once the table is in place on the master start mysql or flush tables and unlock tables if you left it running. The table on the master is now how it would be had the bad query never been ran. Magic! Replication should continue and everything will be happy (including whom ever ran the query ). If the slave was caught up when the table was copied to the master no queries or data will be lost.

Those steps again are.
* Stop writes on the master.
* Let the slave catch up.
* Copy table from slave to master.
* Continue writes on the master.

Now for some notes on how to prevent this from happening at all. In the case above an update query had been copied without the where clause which caused MySQL to update the entire table instead of just one row. Instead of using copy/paste to edit queries then pasting them into the mysql client write them in a .sql text file and then use the mysql cli client to execute them. I realize one should never execute queries by hand on a live system but sometimes it has to be done. The mysql client has two options for executing sql files. First being to use the shell to pipe the file in to the client such as mysql < file.sql or cat file.sql | mysql. The other is to us \. (backslash dot) file.sql from the mysql prompt.

As I’m writing this my treo is mixing quite a beat. It’s not coming from it though, it’s coming through my stereo. Normally this happens only when I’m about to get a message. Tonight though it has gone on for more than a minute. The same beat over and over again.

This now on top of several other problems I have had. It has locked up twice. Also about half of the incoming calls fail. When someone calls I hear the call, I can answer it but I can’t hear the caller. When this happens I say “sorry I’ll call you back” and hang up. Then I call them back. Most of the people that call me have been “trained” to deal with this. I recently tried the firmware upgrade. No help. Next step call cingular.

I have been putting off calling cingular because every time I call a cell phone company (both cingular and verizon people have done this) I end up getting some goofy person that decides to unload their life story on me. The last time it was some bubbly chick asking me how it is to live in Northern California and telling me a very long winded story about some uncle with a house somewhere that wants her to move out. Meanwhile I take the phone away from my ear, stare at it awkwardly for 10 seconds then start listening again.

Goofy people. Anyway I can’t bash on the treo all that much. The touch screen, QWERTY keyboard and the ability to easily read sports news (wap.yahoo.com) anywhere I go is very handy.

A few days ago I was contacted by a user that was getting table full errors. I explained that by default MySQL uses 4 byte pointers for row data and if the data file grows larger than 4 bytes MySQL will run out of space for pointers. I also explained about max_rows and avg_row_length and how they can be used with alter or create table to give himself larger row pointers. He took this information and happily altered his table expecting all of his data to be there and MySQL to begin adding new rows. What he got was a new table with only a few hundred rows of data. He pinged me in a panick asking what he did wrong. Having never seen such a thing myself I logged into the box. I checked mysqld.err and found that for the previous 10 days or so mysqld had been writing corrupt index warnings to the log. Fortunately he had a slave that we could recover the data from.

After some research the best explanation I can offer as to what went wrong is that when MySQL went to alter the table it did a read on the bad index and thought there was only 100 or so rows in the table. After copying those rows out it overwrote the good table with the temp table and return success thinking it had done a fine job of increasing the row pointer size. Which it had. The new table had 5 byte pointers.. just no data.

As a warning to everyone out there. Keep an eye (or script) on your err file because it can warn you about problems long before they bite you. It’s also a good practice to check your tables before altering them.

I heard this over the wall of my cube today:

Guy A: Computer or Paperweight?
Guy B: Huh?
Guy A: I’m going to the hardware giveaway. Am I going to get a computer or paperweight?
Guy B: *Mumbles something*
Guy A: It’s a heavy object used to keep papers from blowing away.

After the initial shock of learning someone didn’t know what a paperweight was my brain started to drift away.
*wavy flashback lines lines*
Imagine working in an office long ago before air conditioning. In a time where stuffy wool suits were the only fashion and air moved by the grace of god or by your hand powered fan. Your sitting at your desk writing numbers on a ledger with a quill wet with fresh ink from the well on your desk. It’s 105F outside but you can’t go down to the creek for a swim because your boss demands the accounts be balanced by quitting time. The only water comes from a wooden bucket that was hauled from the well hours before so it’s almost as hot as you are. Sweat pours out of you where it feeds a thriving culture of bacteria in your suit creating a pungent oder that drifts throughout the room. The two small windows in your office are open wide in hopes of a blessing. Then it comes. A small gust of wind. Just enough to give you a second of relief from the heat and rustle a few papers on your desk. Previous experience tells you what to do next. You look for and adjust your paperweight.