Dependency error installing mylvmbackup on Ubuntu 8.04

I’ve started an investigation of MySQL Backups using LVM. I’m working with Lenz’s mylvmbackup but I found it both used Perl and needed a number of dependencies installed.

Installing dependencies failed on my test system, yet I found it actually worked when I went back to my dev system (but it is not configured with LVM for full testing).

$ sudo cpan Config::IniFiles Sys::Syslog Date::Format Getopt::Long  DBI

Details of error:

.... Going to build S/SA/SAPER/Sys-Syslog-0.27.tar.gz

WARNING: LICENSE is not a known parameter.
Checking if your kit is complete...
Looks good
'LICENSE' is not a known MakeMaker parameter name.
Writing Makefile for Sys::Syslog
cp blib/lib/Sys/
/usr/bin/perl /usr/share/perl/5.8/ExtUtils/xsubpp -noprototypes -typemap /usr/share/perl/5.8/ExtUtils/typemap  Syslog.xs > Syslog.xsc && mv Syslog.xsc Syslog.c
cc -c   -D_REENTRANT -D_GNU_SOURCE -DTHREADS_HAVE_PIDS -DDEBIAN -fno-strict-aliasing -pipe -I/usr/local/include -D_LARGEFILE_SOURCE -D_FILE_OFFSET_BITS=64 -O2   -DVERSION="0.27" -DXS_VERSION="0.27" -fPIC "-I/usr/lib/perl/5.8/CORE"  -DUSE_PPPORT_H Syslog.c
In file included from Syslog.xs:6:
/usr/lib/perl/5.8/CORE/perl.h:420:24: error: sys/types.h: No such file or directory
/usr/lib/perl/5.8/CORE/perl.h:451:19: error: ctype.h: No such file or directory
/usr/lib/perl/5.8/CORE/perl.h:463:23: error: locale.h: No such file or directory
/usr/lib/perl/5.8/CORE/perl.h:480:20: error: setjmp.h: No such file or directory

Some searching was necessary to find this thread and confirm that my prod server did not have a correct dev package.

apt-get install libc6-dev

NOTE: While the doc refers to the module File::Basename, trying to install this throws an error which when you investigate further is a false positive. The README does refer to this being normally part of the default perl installation.

The size of memory tables

I was doing some database sizing in MySQL 5.1.30 GA for memory tables. Generally I have used INFORMATION_SCHEMA.TABLES data_length,index_length as a reasonable guide.

However working with a MEMORY table, after deleting rows, the size did not decrease as expected. I deleted 10% of rows, and saw 0% reduction. This was confirmed by doing a subsequent ALTER where I saw the 10% reduction in memory size.

It requires more investigation, however I found these results unexpected and worthy of publishing.

mysql> select version();
| version() |
| 5.1.30    |

| table_name      | engine | row_format | table_rows | avg_row_length | total_mb    | data_mb     | index_mb   |
| location_ex4    | MEMORY | Fixed      |    1111000 |             45 | 59.68744659 | 51.16348267 | 8.52396393 |

mysql> delete from location_ex4 limit 111000;
Query OK, 111000 rows affected (0.16 sec)

| table_name      | engine | row_format | table_rows | avg_row_length | total_mb    | data_mb     | index_mb   |
| location_ex4    | MEMORY | Fixed      |    1000000 |             45 | 59.68744659 | 51.16348267 | 8.52396393 |

mysql> alter table location_ex4 engine=memory;
Query OK, 1000000 rows affected (2.95 sec)
Records: 1000000  Duplicates: 0  Warnings: 0

| table_name      | engine | row_format | table_rows | avg_row_length | total_mb    | data_mb     | index_mb   |
| location_ex4    | MEMORY | Fixed      |    1000000 |             45 | 53.75530243 | 45.97259521 | 7.78270721

Using Flipper to manage MySQL Pairs

As discussed previously in Options using MySQL pairs I have started evaluating the strengths and weaknesses of various open source options. This is an evaluation of Flipper, a product from Proven Scaling a MySQL consulting organization.


  • Pros When correctly configured and with a working installation it just works, simple and functional, which is good design.
  • Cons The functionality is incomplete especially when it comes to edge cases, additional manual scripting especially for MySQL specifics is necessary and could have be easily added.

The Flipper documentation is detailed, but I found the implementation could have been easier without reading most of the documentation first. The software comes in RPM packages, but as I’m using Ubuntu, installation is via source.

The documentation however assumes your Master/Fail Over master MySQL environment is already correctly configured, and running with Virtual IP’s and the correct read_only status. There is no information for configuration here, so you need to be comfortable with MySQL Replication before starting.

The default notification of IP addresses is managed by arping. Under Ubuntu 8.04 this actually throws an error for a virtual IP on the same host and then Flipper fails to operate as designed. I spent some time to diagnose the problem first before submitting to the flipper-devel list. The response was prompt, the recommendation was to actually use Linux Heatbeat for the purposes of the address notification. The installation of this was easy, via apt-get and the configuration change to Flipper a single row meta data change in one table, which showed good design in this flexibility.

Overall however, Flipper is only a partial solution. It lacked some functionality I just expected would be included in the initial version. The ability to set read_only on a server, Flipper handles this for a controlled failover, but not for just setting against the read only host. There is no means of starting a MySQL slave using the Flipper CLI, you need to do this again manually with additional scripts.

Overall, while a level of information feedback is available, and controlled failover of a correctly working and configured environment works great, manual steps are necessary in the “not ideal” case, when the tool could offer more.

Some points in addition to the supplied documentation.

  • The ‘flipper’ user may only need SELECT privileges to the necessary meta data tables, but it requires ‘SUPER’ privilege for SLAVE management.
  • Installation of arping necessary with ‘sudo apt-get install arping’
  • The arping command syntax needs to be updated for Ubunutu to ”/usr/sbin/arping -I $sendarp_interface -c 5 $sendarp_ip’ ‘. The path and options change. See 2.5.2 ARP sending command. You also need to adjust the Sudo Privileges for the command

Using Heatbeat

The solution to the ‘arping’ problem was to actually use a different command, send_arp which is part of Heatbeat. It’s ironic that Heatbeat is an entire product that could be used for managing pairs. However the following did work.

sudo apt-get install heartbeat
# Install fails consistently on 8.04, following needed
sudo apt-get update
sudo apt-get install heatbeat
# Weird but necessary
INSERT INTO masterpair (masterpair, name, value) VALUES
  ('pairname', 'broadcast', '');
UPDATE masterpair SET value="/usr/lib/heartbeat/send_arp -p /tmp/ -i 100 -r 5 $sendarp_interface $sendarp_ip auto $sendarp_broadcast $sendarp_netmask" WHERE masterpair="pairname" AND name="send_arp_command";


As a result of this, I found at least one bug. With the send_arp_command you can specifiy $sendarp_broadcast as an argument in the value, however when you do, if the variable is not set, there should be a configuration error in Flipper, rather then it attempting to execute a remote SSH command with the variable undefined which causes an error, but could if not support the write variable protection cause other issues depending on syntax used.


1. One annoying thing was unnecessary stderr for SSH connections under Ubuntu, you can fix by doing a 2>/dev/null to address it. It was however useful in debugging to see the number of SSH connections, and then it help find the ‘arping’ issue, but in general it’s annoying, for example.

./flipper developer swap
Connection to closed.
Connection to closed.
Connection to closed.
Connection to closed.
Connection to closed.
Connection to closed.
Connection to closed.
Connection to closed.
Connection to closed.
Connection to closed.
Connection to closed.
Connection to closed.

2. The slave is listed first, you just automatically think master/slave, the output should be formatted in this though.

./flipper developer status 2>/dev/null
MASTERPAIR: developer
NODE: beta181 has read IP, is read-only, replication running, 0s delay
NODE: alpha187 has write IP, is writable, replication running, 0s delay

3. No information after swap. When you do a swap, it would be good for the status to be shown. You are only going to run this command anyway to confirm.


I started working on modifying the ‘flipper’ script to support a read_only command, but I only had 1 day and ran out of time to finish.

Some MySQL pairs terminology

In response to a number of comments, I thought I would clarify the scope of my discussion regarding Options using MySQL pairs before I begin. As mentioned their is no one way or type of configuration for MySQL in a HA solution, however the simplest progression from a single Master/Slave environment is the concept of a pair of servers, configured to support a fail over and fail back via MySQL Replication.

The concept of a MySQL Pair in this context is to have a “hot” MySQL standby ready for controlled and hopefully! automated fail over. I say hopefully because with MySQL Replication as an asynchronous solution there is no guarantee for no loss of data.

I consider DRBD/Heatbeat for example a “cold” standby, as MySQL on the slave server is not actually running. DRBD does provide a guarantee of consistency in data (a synchronous solution) that is written at a disk level, which is a significant advantage over asynchronous replication. I consider Red Hat Cluster suite, simply a management process, and definitely “cold”.

A Shared disk solution, for example a SAN, and a failover server that uses the shared storage, is also a “cold” standby.

There are advantages and disadvantages to each option. These relative merits of the strengths and weaknesses should be considered carefully when you are making a design decision.

Options using MySQL Pairs

Configuring a production environment using a pair of MySQL servers in a Master/Fail Over Master situation is a common process to provide many benefits including supporting failover, backup/recovery, higher availability for software & database upgrades. This is also a common method for database shards. One of the key hidden benefits is by performing regular controlled failovers for example with software upgrades you are actively testing your disaster recovery procedures. Most organizations have a partial plan, some don’t have any, but rarely do people test their disaster recovery.

There is no one way to configure and manage such an environment. There are a number of options including:

  • Develop your own home grown scripts
  • Flipper by Proven Scaling
  • MMM by Percona
  • Heatbeat by the Linux High Availability Project

I have started a detailed review of a number of these technologies and will be providing my findings for review.

This is not the only way to solve the problems of course. Google for example have provided MySQL Patches that include features such as semi-sync replication and mirrored binary logs. Red Hat Cluster suite, and MySQL/DRBD are other technologies but less idea for various reasons specifically the “cold” nature of the failover environment.

Where is the innovation?

The 2009 MySQL Conference has closed it’s submissions for papers. This year the motto is “Innovation Everywhere”.

Last weekend’s Open SQL Camp in Charlottesville, Virginia, we had the chance to talk about the movements in the MySQL ecosystem. I was impressed to get the details of the Percona MySQL Patches, but focus is still in 5.0. (Welcome to the Percona team Tom Basil) Our Delta is attempting now to integrate patches into various MySQL branches. There was an opening keynote by Brian Aker from Drizzle, and Drizzle team Jay Pipes and Stewart Smith on hand. It was also announced that MySQL 5.1.30 will be GA, available in early December.

But these are not innovations that are ground breaking. Last year, it was the announcement of KickFire that I found most intriguing regarding innovation.

What is there this year?. The most interesting thing I read last week was Memcached as a L2 Cache for Innodb – The Waffle Grid Project. This is my kind of innovation. It’s sufficiently MySQL, but just adds another dimension with another companion technology. The patch seems relatively simple in concept and code size, and I’m almost prepared to fire up a few EC2’s to take this one for a spin. I’m doubly impressed because the creators are two friends and colleagues that are not hard core kernel hackers, but professionals on the front line dealing with clients daily. Will it be successful, or viable? That is the question about innovation.

Unfortunately I spend more time these days not seeing innovation in MySQL, but in other alternative database solutions in general. Projects like Clustrix, Inc., LucidDB, and Mongo in the 10gen stack.

When mysqldump –no-set-names matters

I had this perplexing problem yesterday where a mysql dump and restore was producing different results when using MaatKit mk-table-checksum.

mk-table-checksum --algorithm=BIT_XOR h=192.168.X.XX,u=user,p=password --databases=db1 --tables=c
db1      c           0 192.168.X.XX InnoDB     215169         d1d52a31    2    0 NULL NULL
mk-table-checksum --algorithm=BIT_XOR h=localhost,u=user,p=password --databases=db1 --tables=c
db1      c           0 localhost InnoDB     215169         91e7f182    0    0 NULL NULL

It was rather crazy until I reviewed the mysqldump settings I was using, and I realized I was using –no-set-names.

So just what does this option remove. Here is a diff of mysqldump with and without.

> /*!40101 SET NAMES utf8 */;

As you can see it executes a SET NAMES utf8. The problem here is I’m exporting a table, and it is DEFAULT CHARSET=latin1, and no columns are defined as utf8.

I’m not expert in character sets, but this strikes me as strange, and a problem that remains unresolved to my satisfaction, resolved, but not to my comfort level.

ORDER BY (the lesser known way)

We all know with MySQL you can use ORDER BY with a list of columns to return an ordered set, e.g. ORDER BY name, type, state;
I often use the syntax ORDER BY 1,2; which I’m surprised that some people do not know about.

However I needed to do some selective ordering of a type field, I didn’t want to for example have a lookup table just to join for ordering. While contemplating a means of achieving this, I asked a work colleague, who I figured may have just experienced this problem before. Lone behold I became the student as I discovered there is a third syntax with ORDER BY, using expressions.

mysql> create table test(name varchar(10) not null, type varchar(10) not null);
Query OK, 0 rows affected (0.06 sec)

mysql> insert into test(name,type) values
Query OK, 7 rows affected (0.00 sec)
Records: 7  Duplicates: 0  Warnings: 0

mysql> select name from test
order by type='Veg' DESC,
         type='Fruit' DESC,
         type='Food' DESC,
         type='Liquid' DESC;
| name     |
| Carrots  |
| Onions   |
| Apples   |
| Bananas  |
| Crackers |
| Beer     |
| Water    |
7 rows in set (0.00 sec)

Of course, reading the MySQL Manual confirms this on the SELECT command.
I’ve not read the MySQL manual from cover to cover, since 4.x days. Perhaps it’s time.

Thanks to Nick Pisarro of Blog Revolution for this most valuable tip.

Improving your web site compatibility with browsers

Every website page content uses two basic elements, HyperText Markup Language (HTML) and Cascading Style Sheets (CSS). Each of these has various standards, HTML has versions such as 3.2, 4.0, 4.01, and the new XHTML 1.0,1.1, 2.0 along with various version flavors know as strict, transitional & frameset. CSS also has various versions including 1, 2 and 3.

Each browser renders your combined HTML & CSS differently. The look and feel can vary between FireFox, Safari, Chrome, Internet Explorer and the more less common browsers. Indeed each version of a product also renders different. With IE 8 just being released, it’s common versions now are 5.0, 5.5, 6.0 and 7.0. This product alone now has 5 versions that UI designers must test and verify.

To minimize presentation and rendering problems, adhering to the standards can only assist, and greatly benefit the majority of entrepreneurs, designers and developers that are not dedicated resources. There are two excellent online tools from the standards body, the World Wide Web Consortium (W3C) that an easily assist you.

You can also link directly to these sites, so it’s easier to validate your HTML and CSS directly from your relevant webpage. For example, HTML Validation and CSS.

It’s not always possible to meet the standards, and when you are not the full-time developer of your site, it can be time consuming if you don’t check early and regularly.

Selecting wise indexes

Indexes are a great way to improve performed in a MySQL database, when used appropriately.
When used in-appropriately the impact can be a degradation of performance.

The following example from Movable Type shows how when reviewing the slow query log I found numerous occurrences of Inserts take 3 or more seconds, with no reported lock contention time for this insert.

# Query_time: 3  Lock_time: 0  Rows_sent: 0  Rows_examined: 0
SET insert_id=6281;
INSERT INTO mt_comment
(comment_author, comment_blog_id, comment_commenter_id, comment_created_by,
 comment_created_on, comment_email, comment_entry_id, comment_ip, comment_junk_log,
comment_junk_score, comment_junk_status, comment_last_moved_on, comment_modified_by,
comment_modified_on, comment_parent_id, comment_text, comment_url, comment_visible)
VALUES (...)

The impact here, is that SELECT statements to the mt_comment table are also blocked because this table is in MyISAM. It was reviewing slow running SELECT statements that the cause of the slow inserts was easily determined.

mysql> explain SELECT comment_id
    -> FROM mt_comment
    -> WHERE (comment_visible = '1') AND (comment_blog_id = '3') AND (comment_entry_id = '276')
    -> ORDER BY comment_created_on DESC;

*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: mt_comment
         type: ref
possible_keys: mt_comment_visible,mt_comment_entry_id,mt_comment_blog_id,mt_comment_blog_stat,mt_comment_visible_date,mt_comment_entry_visible,mt_comment_blog_visible,mt_comment_blog_ip_date,mt_comment_blog_url
          key: mt_comment_entry_visible
      key_len: 6
          ref: const,const
         rows: 99
        Extra: Using where
1 row in set (0.00 sec)

CREATE TABLE `mt_comment` (
  `comment_id` int(11) NOT NULL auto_increment,
  `comment_author` varchar(100) default NULL,
  `comment_blog_id` int(11) NOT NULL default '0',
  `comment_commenter_id` int(11) default NULL,
  `comment_created_by` int(11) default NULL,
  `comment_created_on` datetime default NULL,
  `comment_email` varchar(75) default NULL,
  `comment_entry_id` int(11) NOT NULL default '0',
  `comment_ip` varchar(16) default NULL,
  `comment_junk_log` mediumtext,
  `comment_junk_score` float default NULL,
  `comment_junk_status` smallint(6) default '0',
  `comment_last_moved_on` datetime NOT NULL default '2000-01-01 00:00:00',
  `comment_modified_by` int(11) default NULL,
  `comment_modified_on` datetime default NULL,
  `comment_parent_id` int(11) default NULL,
  `comment_text` mediumtext,
  `comment_url` varchar(255) default NULL,
  `comment_visible` tinyint(4) default NULL,
  PRIMARY KEY  (`comment_id`),
  KEY `mt_comment_commenter_id` (`comment_commenter_id`),
  KEY `mt_comment_visible` (`comment_visible`),
  KEY `mt_comment_junk_score` (`comment_junk_score`),
  KEY `mt_comment_ip` (`comment_ip`),
  KEY `mt_comment_parent_id` (`comment_parent_id`),
  KEY `mt_comment_entry_id` (`comment_entry_id`),
  KEY `mt_comment_email` (`comment_email`),
  KEY `mt_comment_last_moved_on` (`comment_last_moved_on`),
  KEY `mt_comment_created_on` (`comment_created_on`),
  KEY `mt_comment_junk_status` (`comment_junk_status`),
  KEY `mt_comment_blog_id` (`comment_blog_id`),
  KEY `mt_comment_blog_stat` (`comment_blog_id`,`comment_junk_status`,`comment_created_on`),
  KEY `mt_comment_visible_date` (`comment_visible`,`comment_created_on`),
  KEY `mt_comment_entry_visible` (`comment_entry_id`,`comment_visible`,`comment_created_on`),
  KEY `mt_comment_blog_visible` (`comment_blog_id`,`comment_visible`,`comment_created_on`,`comment_id`),
  KEY `mt_comment_blog_ip_date` (`comment_blog_id`,`comment_ip`,`comment_created_on`),
  KEY `mt_comment_junk_date` (`comment_junk_status`,`comment_created_on`),
  KEY `mt_comment_blog_url` (`comment_blog_id`,`comment_visible`,`comment_url`)

As you can see, the table has 18 indexes. This means that for every row inserted, 18 separate index inserts are required.

When adding an Index to a table, first determine the usage patterns that will use the index, consolidating indexes when possible and removing obvious duplicates (in the above example, the single column comment_blog_id is a classic duplicate index).

Adding an index will generally help SELECT performance, depending on cardinality, but will always impact INSERT,UPDATE and DELETE performance.
Another down side of too many indexes is the MySQL optimizer has much more work to do to eliminate beneficial indexes for every Query Execution Plan (QEP) that is undertaken.

Indeed I have seen worse, in one case a table with ~120 columns, move then 20 single column indexes AND a 3 part primary key summing 40 bytes in InnoDB. The impact was terrible for performance, with the Index size being 3x times the data size.

About the Author

Ronald Bradford, Principal of 42SQL provides Consulting and Advisory Services in Data Architecture, Performance and Scalability for MySQL Solutions. An IT industry professional for two decades with extensive database experience in MySQL, Oracle and Ingres his expertise covers data architecture, software development, migration, performance analysis and production system implementations. His knowledge from 10 years of specialized consulting across many industry sectors, technologies and countries has provided unique insight into being able to provide solutions to problems. For more information Contact Ronald.

Brand identity with undesirable domain names

Choosing a domain name for your brand identity is the start. Protecting your domain name by registering for example .net, .org, and the many more extensions is one step in brand identity.

However a recent very unpleasant experience in New York, resulted in realizing some companies also register undesirable domain names. I was one of many unhappy people, mainly tourists as I was showing an Australian friend the sights of New York. We had chosen to use the City Sights NY bus line, but we caught with some 100+ people in a clear “screw the paying customers” experience.

I was really annoyed that my friend, only in New York for 2 days both had to experience this, and missed out on a night tour. I commented, I’m going to register, and share the full story of our experience, directing people to use Gray Line New York, which clearly by observation were providing the service we clearly did not get.

To my surprise, the domain name was already taken. To my utter surprise, the owner of the domain is the same as Did they do this by choice, or did another unhappy person (at least in 2006) register this, only to be perhaps threatened legally to give up the domain.

I would generally recommend in brand identity this approach, especially when select common misspellings and hyphenated versions if applicable can easily lead to a lot of domain names for your brand identity.

$ whois

Whois Server Version 2.0

Domain names in the .com and .net domains can now be registered
with many different competing registrars. Go to
for detailed information.

   Whois Server:
   Referral URL:
   Name Server: NS0.DIRECTNIC.COM
   Name Server: NS1.DIRECTNIC.COM
   Status: clientDeleteProhibited
   Status: clientTransferProhibited
   Status: clientUpdateProhibited
   Updated Date: 31-dec-2006
   Creation Date: 28-nov-2004
   Expiration Date: 28-nov-2011

 CitySights New York LLC
 15 Second Ave
 Brooklyn, NY 11215


$ whois

Whois Server Version 2.0

Domain names in the .com and .net domains can now be registered
with many different competing registrars. Go to
for detailed information.

   Whois Server:
   Referral URL:
   Name Server: NS2.PUSHONLINE.NET
   Status: clientDeleteProhibited
   Status: clientTransferProhibited
   Status: clientUpdateProhibited
   Updated Date: 26-jun-2007
   Creation Date: 11-aug-2006
   Expiration Date: 11-aug-

 CitySights New York LLC
 15 Second Ave
 Brooklyn, NY 11215


Why you do not use GRANT ALL ON *.*?

Why you do not use GRANT ALL ON *.*?

I was with a client today, and after rebooting a MySQL 5.0.22 instance cleanly with /etc/init.d/mysqld service, I observed the following error, because you always check the log file after starting MySQL.

080923 16:16:24  InnoDB: Started; log sequence number 0 406173600
080923 16:16:24 [Note] /usr/libexec/mysqld: ready for connections.
Version: '5.0.22-log'  socket: '/var/lib/mysql/mysql.sock'  port: 3306  Source distribution
080923 16:16:24 [ERROR] /usr/libexec/mysqld: Table './schema_name/table_name' is marked as crashed and should be repaired
080923 16:16:24 [Warning] Checking table:   './schema_name/table_name'

Now, I’d just added to the /etc/my.cnf a number of settings including:


which explains the last line of the log file. When attempting to connect to the server via the mysql client I got the error:

“To many connections”

So now, I’m in a world of hurt, I can’t connect to the database as the ‘root’ user to observe what’s going on. I know that table it’s decided to repair is 1.4G in size, and the server is madly reading from disk. Shutting down the apache server that was connecting to the database is not expected to solve the problem, and does not, because connections must wait to timeout.

MySQL reserves a single super privileged connection, i.e. ‘root’ to the mysql server specifically for this reason, unless all the connections have this privilege. The problem, as often experienced with clients, is the permissions of the application user is simply unwarranted.

mysql> select host,user,password from mysql.user;
| host      | user        | password         |
| localhost | root        | 76bec9cc7dd32bc0 |
| xxxxxx    | root        |                  |
| xxxxxx    |             |                  |
| localhost |             |                  |
| %         | xxxxxxxxxxx | 0716d6776318d605 |
| localhost | xxxxxxxxxxx | 0716d6776318d605 |
| localhost | xxxxxxx     | 6885269c4a550a03 |
7 rows in set (0.00 sec)

mysql> show grants for xxxxxxx@localhost;
| Grants for xxxxxxx@localhost                                                          |
| GRANT USAGE ON *.* TO xxxxxxx'@'localhost' IDENTIFIED BY PASSWORD '6885269c4a550a03'  |
| GRANT ALL PRIVILEGES ON `xxxxxxx`.* TO 'xxxxxxx'@'localhost' WITH GRANT OPTION        |
2 rows in set (0.00 sec)

So the problem is ALL PRIVILEGES is granted to an application user. Never do this!

The solution is to remove all unused users, anonymous users, and create the application user with just the privileges needed.

DROP USER xxxxxxxxxxx@localhost;
DROP USER xxxxxxxxxxx@'%';

DELETE FROM mysql.user WHERE user='';
DROP USER xxxxxxx@localhost;
CREATE USER xxxxxxx@localhost IDENTIFIED BY 'xxxxxxx';

GRANT SELECT,INSERT,UPDATE,DELETE ON xxxxxxx.* TOxxxxxxx@localhost;

To www or not www

Domain names historically have been, written also with the protocol prefix, but in reality www. is optional, only is actually needed.

www. is technically a sub-domain and sub-domains incur a small penalty in search engine optimization.

There is no right or wrong. What is important is that you choose one, and the other needs to be a 301 Permanent Redirect to the one you have chosen.

You also need to know that creating a server alias in your web server configuration, for example Apache or Tomcat is not a permanent redirect, in-fact it is technically duplicate content, with two web sites the same also incurring a penalty for search engine rating.

So what do the big players do. Here are a few.

Use www


Do not use www


Show duplicate content


Curiously uses a 303 redirect,, and a 302 redirect.

How do you check? Use a CLI tool such as wget.

$ wget
--2008-09-22 19:56:48--
Resolving,,, ...
Connecting to||:80... connected.
HTTP request sent, awaiting response... 200 OK

$ wget
--2008-09-22 19:57:56--
Connecting to||:80... connected.
HTTP request sent, awaiting response... 301 Moved Permanently
Location: [following]

$ wget
--2008-09-22 20:07:59--
Connecting to||:80... connected.
HTTP request sent, awaiting response... 200 OK
Length: unspecified [text/html]

$ wget
--2008-09-22 19:59:43--
Connecting to||:80... connected.
HTTP request sent, awaiting response... 301 Moved Permanently
Location: [following]

$ wget
--2008-09-22 20:10:47--
Connecting to||:80... connected.
HTTP request sent, awaiting response... 200 OK
Length: 15322 (15K) [text/html]

$ wget
--2008-09-22 20:14:06--
Connecting to||:80... connected.
HTTP request sent, awaiting response... 301 Moved Permanently
Location: [following]

$ wget
--2008-09-22 20:26:18--
Connecting to||:80... connected.
HTTP request sent, awaiting response... 200 OK
Length: 2655 (2.6K) [text/html]

$ wget
--2008-09-22 20:26:41--
Connecting to||:80... connected.
HTTP request sent, awaiting response... 301 Moved Permanently
Location: [following]

Professionally, I prefer shorter and simpler without www.


Patience and Passion at Web 2.0 NY

Gary Vaynerchuk spoke next at Web 2.0 NY on Building Personal Brand Within the Social Media Landscape.

He was hilarious. His video presentation is available online, to share with others. He is inspirational for new young entrepreneur and I’d love to see him talk at Ultra Light Startups

His talk was simply “Patience and Passion”.

Words of wisdom included.

  • There is no reason to do stuff you hate.
  • You can loose just as much money doing stuff you love.
  • What do you want to do for the rest of my life, do it.
  • Hussle is the most important word. We are building businesses here.
  • You can monetize anything, you need to work hard, be patient and passionate about your business.
  • You have to have a business model, that makes some cash along the way – Freemium.
  • Your goal should be to leave a legacy.
  • You need to build brand equity, in yourself. There is never a bad time when you believe, when you work hard, when you know what you are doing.
  • People are the people that are going to help you, get out there and network, be transparent, be exposed.
  • Previously you had to work hard to build a brand, now you can use any network to become known and more successful.
  • 9-5 is for your job, a few hours with your family, then 7-2am is plenty of time to focus on your dreams.

In closing, you have to do what you love.

Integrity, clarity and responsibility – Web 2.0 NY Keynote

Next on the Web 2.0 keynote speaker list was Maria Thomas of with her talk The DIY Guide to Growing a Company.

If never heard of Etsy before – Your place to buy & sell all things handmade, interesting site. Most companies start small, and stay small. Only 0.1 of 1% grow to any size (e.g. > $250 Million) Esty, this year has 100M in revenue, Amazon is about $2 billion.

The opening lines included the message “Don’t loose the essence of who you are, and what you want to achieve.” and the term Filotimo.

Filotimo (Greek)

  • Operating with integrity
  • a clarity of purpose
  • a sense of social responsibility

It was an interesting point about qualifications “I got my Internet Degree at, then building digital media business at NPR – National Public Radio.”

Some more quotes from this discussion.

  • Listen to end user, but be clear about when you want to go.
  • Set you goals, communicate them, measure them.
  • Practice Filimato – Keep it human.
  • Go behind the resume, talk to people, be direct, be honest. Believe in employees.
  • Understand that small decisions and impact bigger decisions.
  • Just because your a DIY company doesn’t mean you have build everything.
  • Get products out the door, get them out fast.
  • It takes an effort of will, and a very good process to do this well.
  • The perfect is enemy of good enough.
  • Launch products off at platform and build more quickly.
  • The marketplace has social, it’s personal, it’s playful.
  • Visits to etsy become habit forming, it makes connections to real people with unique products.

What if software was a physical object – NY Web 2.0 Third Keynote

Some points of reference from the next Web 2.0 keynote by Jason Fried of 37 Signals

  • Software business is a great place to be.
  • You can build anything you want. All you really have to it type, it’s not easy to do, it’s just not that hard to do.
  • Change is easy, cost is cheap in relation to physical objects.
  • You can build it anywhere.
  • Software doesn’t have the same kind of feedback as physical objects.
  • Visually we can determine good verses poor design (e.g. a bottle of water or a remote control).
    It doesn’t have edges, size or weight. It just expands, continues to expand and this is bad.
  • What would your software be like if it was physical?
  • When you say yes to too many features you end up with Homer’s car.
  • The goal should be simple, clean, elegant and streamlined.
  • Once you hit bloat, it’s too hard to go back.
  • Listen to customers, but don’t do everything as they say. Think of yourself as a curator.
  • Make your software a collection, not a warehouse.
  • You don’t need to have everything in the world.
  • You need a few solid features.
  • Real work is hard, imaginary work is easy.
  • Tell the people that want to have new features, to build them. Attach real costs to any requests.

The DNA of your company has to able to say no.

Technology changes, humans don't. – Web 2.0 NY Second keynote

I needed a rest from my opening keynote review NY Tech 1995-2008. Opening Web 2.0 Expo NY Keynote but a few siginificant points from The Death of the Grand Gesture by Deb Schultz.

  • An interesting site is Visual Complexity showing graphical representations of many social networks.
  • All the binary communication becomes white noise — Information Overload.
  • “Technology changes, humans don’t” – Deb Schultz

NY Tech 1995-2008. Opening Web 2.0 Expo NY Keynote

Web 2.0 Expo NY keynotes are happening today. Technology in use included CrowdVine which I’d not heard of, and plenty of Twitter feeds such as w2e_NY08.

The opening keynote was Fred Wilson from Union Square Ventures with his presentation New York’s Web Industry From 1995 to 2008: From Nascent to Ascendent .

Some stats, Seed and early stage deals.

  • 1995 230 SF Bay area, 30 in NY
  • 2008 360 SF Bay area, 116 in NY

Fred first asked “New York is not an alley. Call it Broadway, or just New York.”

Here is a summary of his history of New York Web Industry.

  • 1991 – ZDNet
  • 1993 – New York Online Dialup services
  • 1993 – Jupiter Communications online conference
  • 1993 – Prodigy
  • 1994 – Startups such as Pseudo, Total New york, Razorfish.
  • 1994 – Time Warner Pathfinder
  • 1995 -NYIC 55 Broad St. – Technology oriented building
  • 1995 – Seth Godin – Yoyodyne – Permission Marketing
  • 1995 – itraffic,, NY Times online
  • 1995 – Softbank, Double Click, 24×7, Real Media
  • 1996 – Silicon Alley Reporter
  • 1996 – ivillage, the knot
  • 1996 – Flatiron Partners – good sued for that
  • 1997 – The Silicon Alley Report Radio Show
  • 1997 – mining co.
  • 1997 – Total NY sold to AOL
  • 1997 – Agency rollups razorfish buying 4 companies
  • 1997 – DoubleClick IPO
  • 1998 – Seth Godin moves to Yahoo
  • 1998 – Burn Rate
  • 1998 – Kozmo – We’ll be right over
  • 1998 – was the last year of sanity in the Internet wave
  • 1999 – The start of the boom
  • 1999 the big players came online , all hell breaks loose. 200 startups were funded in 1999, 300 in 2000.
  • 2000 – The Crash & Burn
  • 2000 – f**kedcompany
  • 2000 – Google came to New York. – 86th St Starbucks
  • 2001 – Layoffs, Landlords and bankruptcies
  • 2002 – Rock bottom
  • 2003 – Renewal
  • 2003 – Blogging started gizmodo
  • 2003 – Web 2.0 coined
  • 2003 – was launched from a computer in an apartment
  • 2004 – NY Tech Meetup
  • 2004 – Union Square Ventures $120million raised
  • 2005 – acquired by NY Times
  • 2005 – Etsy
  • 2006 – Google took over port authority building, now with 750 engineers in NY
  • 2008 – Web 2.0 comes to New York City

New York is now 1/3 of Silicon valley, compared to 1/8 of funded Internet companies.

One thing mentioned is a documentary called “We live in Public”. Some of the footage from 1999, is so early Big Brother.

Web 2.0 in NY

I will be attending next week’s Web 2.0 Expo 2008 in New York.

Garys Guide has a schedule of the key events and off site associated event parties.

It will be a bit of a change from the typical MySQL Conferences and recent OSCON Conference I have attended this year.

The Keynote titles gives you an indication of the variety of talks expected.

  • Organizing Chaos: The Growth of Collaborative Filters
  • (Re)making the Internet: Accounting for the Future of Information, Communication and Entertainment Technologies
  • Next Generation of Video Games
  • 10 Things We’ve Learned at 37signals
  • High Order Bit
  • What ManyEyes Knows
  • Arianna Huffington in Conversation with Tim O’Reilly
  • Because We Make You Happy
  • The Real Future of Technology
  • Enterprise Radar
  • The Death of the Grand Gesture
  • It’s Not Information Overload. It’s Filter Failure.
  • Building Personal Brand Within the Social Media Landscape

A neat trick for a row number in a MySQL recordset

While working for a client, I had need to produce canned results of certain different criteria, recording the result in a table for later usage, and keep the position within each result.

Knowing no way to do this via a single INSERT INTO … SELECT statement, I reverted to using a MySQL Stored Procedure. For example, using a sample I_S query and the following snippet:

  DECLARE list CURSOR FOR SELECT select table_name from information_schema.tables where table_schema='INFORMATION_SCHEMA';

  OPEN list;
  SET result_position = 1;
  SET done = FALSE;
  lab: LOOP
    FETCH list INTO table_name;
    IF done THEN
      CLOSE list;
      LEAVE lab;
    END IF;
    INSERT INTO  summary_table(val,pos) VALUES (table_name,result_position);
    SET result_position = result_position + 1;

However, in reviewing with another colleague after writing some 10+ different queries and SP loops, I realized that it is possible to record the position of each row in a result set using session variables, negating the need for all that code.

SET @rowcount = 0;
SELECT table_name, @rowcount := @rowcount + 1 FROM information_schema.tables WHERE table_schema = 'INFORMATION_SCHEMA';
| table_name                            | @rowcount := @rowcount + 1 |
| CHARACTER_SETS                        |                          1 |
| COLLATIONS                            |                          2 |
| COLLATION_CHARACTER_SET_APPLICABILITY |                          3 |
| COLUMNS                               |                          4 |
| COLUMN_PRIVILEGES                     |                          5 |
| ENGINES                               |                          6 |
| EVENTS                                |                          7 |
| FILES                                 |                          8 |
| GLOBAL_STATUS                         |                          9 |
| GLOBAL_VARIABLES                      |                         10 |
| KEY_COLUMN_USAGE                      |                         11 |
| PARTITIONS                            |                         12 |
| PLUGINS                               |                         13 |
| PROCESSLIST                           |                         14 |
| PROFILING                             |                         15 |
| REFERENTIAL_CONSTRAINTS               |                         16 |
| ROUTINES                              |                         17 |
| SCHEMATA                              |                         18 |
| SCHEMA_PRIVILEGES                     |                         19 |
| SESSION_STATUS                        |                         20 |
| SESSION_VARIABLES                     |                         21 |
| STATISTICS                            |                         22 |
| TABLES                                |                         23 |
| TABLE_CONSTRAINTS                     |                         24 |
| TABLE_PRIVILEGES                      |                         25 |
| TRIGGERS                              |                         26 |
| USER_PRIVILEGES                       |                         27 |
| VIEWS                                 |                         28 |
28 rows in set (0.01 sec)

Of course you need the all important SET before each query, if not specified however, the subsequent query does not result in an error, just NULL.

So all I needed was:

INSERT INTO summary_table(val,pos)
SELECT table_name, @rowcount := @rowcount + 1
FROM information_schema.tables

A simple and trivial solution.

How this performs under load, and how it is supported in different and future versions of MySQL is not determined.

Securing your OS for MySQL with JeOS

Do you have a full time System Administrator? Do you have only a part-time SA, or none at all?

Packet General’s Data Security and PCI Compliance solutions run on a dedicated appliance, based on a “Just Enough Operating System” (JeOS) to minimize exposure.

This appliance actually improves not just the security of your data, but ensures your Operating System is secure and up to date. With only 4 services and a footprint < 600MB this is an ideal solution for running even a normal MySQL installation. Security upgrades can also be provided as an automated feature, eliminating the need for this management internally.

Tomorrow in the MySQL Webinar How to secure MySQL data and achieve PCI compliance which is being held Thursday, September 11, 2008, 10:00 am PST, 1:00 pm EST, 18:00 GMT we will be discussing this in more detail.

Domain name trends

It started with (which now ironically redirects to, and now it’s becoming more the trend to create a domain name with the extension included for effect.

With unique .com domains harder to come by, and dropping vowels like so last generation, some countries must be trying to cash in on the success such as Tuvalu which has something like 10% of GDP from domain name sales of .tv.

Some recent names I’ve noticed are, and

I have even considered some recent projects using this new trend, but the combination of either the 2 letter extension not existing (For example .ld) or it’s not possible to get domains from a registrar (For example .er) it will take some time.

How to secure MySQL data and achieve PCI compliance

This week I will be the moderator for a MySQL Webinar How to secure MySQL data and achieve PCI compliance being held Thursday, September 11, 2008, 10:00 am PST, 1:00 pm EST, 18:00 GMT.

Recently I wrote about Do you store credit cards in your MySQL Database?. If you do, then PCI Compliance is not something you can ignore.

This webinar will not only be discussing PCI Compliance, but also MySQL data security. Our panel includes Didier Godart from MasterCard Worldwide, one of three members who drafted the Payment Card Industry Data Security Standard 1.0.

For more information on the various PCI Compliance and Encryption options for MySQL , check out the Packet General website.

What is Google's direction?

Tonight over discussion was Android and what is Google’s ultimate direction. Have they lost their way, or are they just planning to explode with so many new things that will revolutionize what and how we do things. With $475,000 first price for Android, they certainly have the money available to invest in new directions.

I arrive home, and find email discussion on The Google Browser – Chrome.

Inquisitive, I take a look, to find the great teaser, nothing by a comic, come back tomorrow for the download link. Is that clever to leak information, have everybody write about it and check back tomorrow?

Naming standards? Singular or Plural

It’s important that for any software application good standards exist. Standards ensure a number of key considerations. Standards are necessary to enforce and provide reproducible software and to provide a level of quality in a team environment, ease of readability and consistency.

If you were going to create a MySQL Naming Standard you have to make a number of key decisions. Generally there is no true right or wrong, however my goals tend towards readability and simplicity. In 2 decades of database design I’ve actually changed my preference between some of these points.

1. Pluralism

Option 1
All database objects are defined in the logical form, that being singular.

For example: box, customer, person, category, user, order, order_line product, post, post_category

Option 2

For database tables & views, objects are defined in plural. For columns, objects are defined singular.

For example: boxes, customers, people, categories, users, orders, order_lines, products, posts, post_categories

Inconsistency between table name and column name, when using plural. Column names simply are not plural.
When the plural of the name is a completely different spelled word. For example a table of People, and a primary key of PersonId.
When the plural rule is not adding ‘s’, for example replacing ‘y’ with ‘ies’ as with Category.
Strict rule necessary for relationship and intersection tables. Generally, only the last portion is plural.
What about other objects, such as stored procedures for example.

2. Case Sensitivity

Option 1
All database objects should be specified as lowercase only. Words are separated with ‘underscores’.

For example: customer, customer_history, order, order_line, product, product_price, product_price_history

Option 2
All database objects use CamelCase. Words are separated via Case.

For example: Customer, CustomerHistory, Order, OrderLine, Product, ProductPrice, ProductPriceHistory

Some database products have restrictions here, Oracle for example, UPPERCASES all objects. MySQL allows for both, except for some Operating Systems that does not support Mixed Case properly (e.g. Microsoft)

3.Key names

In a related post I will be discussing natural and surrogate keys. For this purpose, we will assume you are using surrogate keys.

All keys will have a standard name. For Example: id, key, sgn (system global number)

When referencing primary keys and foreign keys, what standard is used?

Option 1
The primary key is the same name across all tables, making it easy to know the primary key of a table.
For foreign keys, the name is prefixed with the table name or appropriate table alias.

For Example: id. The foreign key would be customer_id, order_id, product_id

Option 2
The primary key is defined as unique across the system, and as such the foreign key is the same as the primary key.

For Example: customer_id, order_id, product_id

When self referencing columns to a table, having a standard is also appropriate, for example parent_product_id.

4. Specific Object Names

Option 1
For the given type of object, have a standard prefix or suffix.

For Example, all tables are prefixed with tbl, all views are suffixed with _view, all columns for a given table are prefixed with table alias.

Option 2
Don’t prefix or suffix an object with it’s object type.

5. Reserved Words

Option 1
Don’t use them. When a word is reserved, find a more description name.

E.g. system_user, order_date,

Option 2
Allow them.

For Example: user, date, group, order

A number of database systems do not allow the use of reserved words, or historically have not. Sum such as MySQL for example, allow reserved words, but only when additional quoting is used.

6. Abbreviations

This indeed could be a entire topic it’s self. In simplicity, do you use abbreviations other then the most common and everybody knows abbreviations (to the point you don’t know what the abbreviation actually is in real life type abbreviations), or do you not.

I use the example of ‘url’. How many people actually know what url stands for. This is a common abbreviation.

Option 1
For objects, use abbreviations when possible. Don’t use it for key tables, but for child and intersection tables.
For Example: invoice, inv_detail, inv_id,

Option 2
Avoid abbreviations at all costs.
invoice, invoice_detail, invoice_id,

Unless you have a large schema (e.g. > 500 tables) the use of abbreviations should not be needed given the relative sizes of objects in modern databases.

My Recommendations

There are a lot more considerations then these few examples for naming standards, however as with every design, it’s important to make a start and work towards continual improvement.

  1. All objects are singular (very adamant, people/person, category/categories, sheep/sheep for tables, but not columns – simplicity wins as English is complex for plurals).
  2. All objects are lowercase and use underscore ‘_’ (I really like CamelCase for readability, but for consistency and simplicity, unfortunately lowercase is easier).
  3. All primary key’s are defined as unique across the system.
  4. Don’t use prefixes/suffixes to identity object types.
  5. Never use reserved words.
  6. Don’t use abbreviations except for the most obvious.

At the end of the day, I will work with what standard is in place. What I won’t work with is, when there is no documented, accountable standard.

Websites in review – Week 1

I often come across new websites, quite often by accident, or by indirection in links from looking at other details. The Internet is an amazing place, and one could spend all day reading such a variety information and only touch on just a few specific topics.

I think it’s important to share interesting and new sites, often it’s a referral from others that provide for enjoyable and useful reading. Here are mine in return. Sites which I bookmarked and intended to review again.

99 Designs —

Need something designed? 99designs connects clients needing design work such as logo designs, business cards or web sites to a thriving community of 17,781 talented designers.

Wufoo —

Wufoo strives to be the easiest way to collect information over the Internet.

Our HTML form builder helps you create contact forms, online surveys, and invitations so you can collect the data, registrations and online payments you need without writing a single line of code.

Mako Templates —

Mako is a template library written in Python. It provides a familiar, non-XML syntax which compiles into Python modules for maximum performance. Mako’s syntax and API borrows from the best ideas of many others, including Django templates, Cheetah, Myghty, and Genshi. Conceptually, Mako is an embedded Python (i.e. Python Server Page) language, which refines the familiar ideas of componentized layout and inheritance to produce one of the most straightforward and flexible models available, while also maintaining close ties to Python calling and scoping semantics.

Venture Beat —

I came across this site when reading Developer Analytics: Facebook game Mob Wars making $22,000 a day.

VentureBeat’s mission is to provide news and information about private companies and the venture capital that fuels them. Founder Matt Marshall covered venture capital for the San Jose Mercury News until he left in Sept. 2006 to launch VentureBeat as an independent company. VentureBeat will focus initially on Silicon Valley, and gradually expand to cover innovation hubs around the globe. Its mission in each region will be the same: to provide insider news and data about the entrepreneurial and venture community that is useful to decision makers.

Developer Analytics —

The previous article referenced this site, with the tagline “the world’s first social media ratings and reporting services.” I like the categories used in reports.

  • Reach – Unique traffic.
  • Audience Profile – Demographics and sociographics breakdown. Interest clouds.
  • Engagement -Impressions. Average page views per user. Return users.
  • Growth -New Users. Churn Rates. Viral Factors.
  • Monetization – CPM and CPA revenue potential.

SXSW Interactive —

I’ve heard of South By South West Conference before, and this is it. SXSW Interactive: March 13-17, 2009 -The Brightest Minds in Emerging Technology

SXSW Interactive The SXSW Interactive Festival features five days of exciting panel content and amazing parties. Attracting digital creatives as well as visionary technology entrepreneurs, the event celebrates the best minds and the brightest personalities of emerging technology. Whether you are a hard-core geek, a dedicated content creator, a new media entrepreneur, or just someone who likes being around an extremely creative community, SXSW Interactive is for you!

Actually sounds rather interesting, and different.

Pulse 2.0 —

Pulse 2.0 is a company that is driven by our passion of technology and entrepreneurship. We use Pulse 2.0 to share our thoughts on Web 2.0.

Monetization through Online Advertising

Next week I will be the panel moderator for September 2008 Entrepreneurs Forum on “Monetization through Online Advertising” organized by Ultra Light Startups™. A slightly different approach to my regular speaking schedule, it will be good to observe and interact with our speakers.

The companies represented by the panelists for the evening include:

  • PerformLine – Alex Baydin, Founder and CEO
  • SocialDough – Derek Lee, Founder and CEO
  • Zanox Inc – Rani Nagpal, Vice President of Affiliate Management
  • Blinkx – Max Ramirez, Head of online ad sales

Event Details

Date: Thursday, September 4, 2008
Time: 7:00pm – 9:00pm
Location: Rose Technology Ventures, LLC
Street: 30 East 23rd Street
City/Town: New York, NY