nil.enroll(aetheric_username, quantum_class_id) (adric) wrote,
nil.enroll(aetheric_username, quantum_class_id)
adric

  • Location:

The stupid, it burns...

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

Tags: bug, work
Subscribe

  • Post a new comment

    Error

    Anonymous comments are disabled in this journal

    default userpic

    Your reply will be screened

    Your IP address will be recorded 

  • 0 comments