The stupid, it burns...

[adric]

Herein An unredacted SQL schema I just encountered on a customer's site. His complaint: "I added a record about one hour agao, but it seems I am not able to add in new enteries."

mysql> describe rs_sites;
+-----------------+--------------+------+-----+---------+----------------+
| Field           | Type         | Null | Key | Default | Extra          |
+-----------------+--------------+------+-----+---------+----------------+
| siteid          | tinyint(9)   | NO   | PRI | NULL    | auto_increment | 
| siteName        | varchar(255) | NO   |     |         |                | 
| siteSlug        | varchar(255) | NO   |     |         |                | 
| siteKeyword     | varchar(255) | NO   |     |         |                | 
| siteDescription | longtext     | NO   |     |         |                | 
| siteThumb       | varchar(255) | NO   |     |         |                | 
| siteAffUrl      | varchar(255) | NO   |     |         |                | 
+-----------------+--------------+------+-----+---------+----------------+
7 rows in set (0.00 sec)

And my fix, after I explained the problem to the customer:

mysql> alter table rs_sites modify siteid smallint(9) auto_increment; 

ERROR 2006 (HY000): MySQL server has gone away
No connection. Trying to reconnect...

Tinyint is one bye, a maximum of 250 or so somethings. Smallint is 2 bytes, so 64,000 more somethings. And, according to the manual the argument (9) only affects the number of characters displayed, which is useful for zeropadding and pretty printing. Is is exceedingly unlikely there will ever be 9 digits to display in a three digit number made up of eight bits. It's not much more likely that the 2 byte (16bit) number is ever going to need nine digits displayed either, but I was being conservative in the face of someone else's terrible code. And varchar[255] ? That allocates enough space for a 255 letter sentence no matter how long the entries are. This is really inefficient and silly.

Ref: http://dev.mysql.com/doc/refman/4.1/en/numeric-types.html