Browse Tag: mysql

Add fields to a MySQL table without doing an ALTER TABLE

I have a database table that was created about 2 years ago and has been filling up quite quickly over the years. These days, it’s massive. Our database dumps are 68gb uncompressed, and 60gb of that is this table. It’s used quite regularly, as it contains all of the error reports we receive, but to call it “unwieldy” is an understatement.

I was content to just let sleeping dogs lie, but alas — one of my devs needs a couple extra fields added to the table for more data and sorting and whatnot. If this wasn’t a 60gb table in our production database, I’d happily run an ALTER TABLE and call it a day. (In fact, I attempted to do this — and then the site went down because the whole db was locked. oops)

Instead, I discovered a better way to add fields while retaining both uptime and data (!). MySQL’s CREATE TABLE command actually has a lot of interesting functionality that allows me to do this:

CREATE TABLE errors2 (
  keywords VARCHAR(255), 
  errorid VARCHAR(64), 
  stacktrace TEXT, 
  is_silent BOOL, 
  id INT(10) AUTO_INCREMENT, 
  PRIMARY KEY (id), 
  KEY playerid (playerid,datecreate), 
  KEY datecreate (datecreate), 
  KEY hidden (hidden,datecreate), 
  KEY hidden_debug (hidden,is_debug,datecreate)
) 
ENGINE=InnoDB 
AUTO_INCREMENT=2417067 
DEFAULT CHARSET=utf8 
SELECT * from errors; 

What this CREATE TABLE statement does is create a new table with 5 explicitly-specified fields (keywords, errorid, stacktrace, is_silent, and id). Four of these are what I wanted to add; ‘id’ exists in the original table, but I specify it here because I need to make it AUTO_INCREMENT (as this is a table setting, not a bit of data or schema that can be copied). Additional keys are specified verbatim from a SHOW CREATE TABLE errors (the original table), as is the AUTO_INCREMENT value.

After specifying my table creation variables, I perform a SELECT on the original table. MySQL is smart enough to know that if I’m SELECTing during a CREATE TABLE, I probably want any applicable table schema copied as well, so it does exactly that — copies over any columns missing from the schema I specified in my CREATE statement. Even better, because the various keys were specified, the indexes get copied over as well.

The result? An exact copy of the original table — with four additional fields added. All that’s left is to clean up:

DROP TABLE errors;
RENAME TABLE errors2 TO errors;

And that, as they say, is that.

Easier-to-read MySQL “show table status”

[code lang=”bash”]mysqlshow –status db_name |sort -n -k10 |awk -F\| ‘($6 !~ /0/)’ |awk -F\| ‘{print $2 ” ” $6 ” ” $7 ” ” $14}’ |egrep -v “^ “[/code]

Creates a much easier-to-read view of the output of “show table status”:

Name                    Rows   Avg_row_length   Update_time         
 wp_users                1      140              2009-08-08 04:13:07 
 wp_links                9      106              2009-10-16 12:57:32 
 wp_comments             14     464              2009-11-28 16:09:43 
 wp_usermeta             15     166              2009-11-29 06:41:19 
 wp_term_taxonomy        53     40               2009-11-20 14:06:21 
 wp_postmeta             141    46               2009-11-29 06:44:05 
 wp_options              172    4624             2009-11-29 06:40:59 
 wp_term_relationships   357    21               2009-11-21 02:35:42 

mod_auth_mysql and segfaults

Symptom: seemingly random PHP scripts are causing Apache to segfault.

Looking deeper: all the PHP scripts that are causing segfaults make database queries (specifically, MySQL).

Look even closer: the following line is in your Apache configuration:

[code]LoadModule auth_mod_mysql modules/mod_auth_mysql.so[/code]

Solution: comment that line out of your Apache configuration and restart Apache.

Why: If the PHP code is run through Apache, you’ve essentially got one process making the SQL queries (if your PHP code makes it so). However, while your code made the connection and is expecting responses and whatnot, Apache, with mod_auth_mysql loaded, is ready and willing to make and take database connections. When a connection that returns a response is made from your PHP code, Apache will attempt to accept the response and handle it itself, instead of passing it to PHP. Since Apache is not expecting the data it’s getting, it has no error handling code for this situation and simply segfaults.

Disable mod_auth_mysql by commenting it out and everything will work without issue.

Extract a single table from a sql dump

The only caveat is that you have to know the table that comes after the one you’re trying to extract. ┬áIt’s alphabetical, if you can get a list of tables, otherwise a quick search of the SQL file will get that info for you.
[code lang=”bash”]awk ‘/Table structure for table .table1./,/Table structure for table .table2./{print}’ bigassdatabase.sql > table1.sql[/code]