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`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1

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 citysightsnysucks.com, 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 citysightsny.com. 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 citysightsny.com

Whois Server Version 2.0

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

   Domain Name: CITYSIGHTSNY.COM
   Registrar: INTERCOSMOS MEDIA GROUP, INC. D/B/A DIRECTNIC.COM
   Whois Server: whois.directnic.com
   Referral URL: http://www.directnic.com
   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

Registrant:
 CitySights New York LLC
 15 Second Ave
 Brooklyn, NY 11215
 US
 718-875-8200x103
Fax:718-875-7056


Domain Name: CITYSIGHTSNY.COM


$ whois citysightsnysucks.com

Whois Server Version 2.0

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

   Domain Name: CITYSIGHTSNYSUCKS.COM
   Registrar: INTERCOSMOS MEDIA GROUP, INC. D/B/A DIRECTNIC.COM
   Whois Server: whois.directnic.com
   Referral URL: http://www.directnic.com
   Name Server: NS.PUSHONLINE.NET
   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-

Registrant:
 CitySights New York LLC
 15 Second Ave
 Brooklyn, NY 11215
 US
 718-875-8200x103
Fax:718-875-7056


Domain Name: CITYSIGHTSNYSUCKS.COM

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:

myisam_recovery=FORCE,BACKUP

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='';
FLUSH PRIVILEGES;
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 www.example.com, written also with the protocol prefix http://www.example.com, but in reality www. is optional, only example.com 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

  • www.google.com
  • www.facebook.com
  • www.cnn.com
  • www.yahoo.com
  • www.myspace.com
  • www.ebay.com
  • www.plurk.com
  • www.amazon.com
  • www.fotolog.com
  • www.linkedin.com

Do not use www

  • digg.com
  • wordpress.com
  • identi.ca

Show duplicate content

  • flickr.com
  • chi.mp
  • corkd.com
  • vimeo.com
  • garysguide.org
  • engineyard.com

Curiously youtube.com uses a 303 redirect, microsoft.com, stumbleupon.com and craigslist.org a 302 redirect.

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

$ wget www.google.com
--2008-09-22 19:56:48--  http://www.google.com/
Resolving www.google.com... 72.14.205.99, 72.14.205.103, 72.14.205.104, ...
Connecting to www.google.com|72.14.205.99|:80... connected.
HTTP request sent, awaiting response... 200 OK

$ wget google.com
--2008-09-22 19:57:56--  http://google.com/
Resolving google.com... 64.233.167.99, 64.233.187.99, 72.14.207.99
Connecting to google.com|64.233.167.99|:80... connected.
HTTP request sent, awaiting response... 301 Moved Permanently
Location: http://www.google.com/ [following]

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

$ wget facebook.com
--2008-09-22 19:59:43--  http://facebook.com/
Resolving facebook.com... 69.63.176.140, 69.63.178.11
Connecting to facebook.com|69.63.176.140|:80... connected.
HTTP request sent, awaiting response... 301 Moved Permanently
Location: http://www.facebook.com/ [following]


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


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


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

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

Professionally, I prefer shorter and simpler without www.

References:

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 www.etsy.com 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 Amazon.com, 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, agency.com, 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 – del.icio.us was launched from a computer in an apartment
  • 2004 – NY Tech Meetup
  • 2004 – Union Square Ventures $120million raised
  • 2005 – about.com 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';
  DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET done=TRUE;

  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;
  END LOOP;

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
WHERE table_schema = 'INFORMATION_SCHEMA';

A simple and trivial solution.

DISCLAIMER:
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 del.icio.us/ (which now ironically redirects to http://delicious.com), 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 flickr.com 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 http://identi.ca, http://chi.mp and http://cyclo.ps.

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

Issues
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

Issues
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

Issues
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

Issues
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,

Issues
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.