Design Problems of PostGres- Part II

The problems in PostGres DB are solved in MySQL to an extent. The design merits of MySQL are the following:

  • The primary index has a mapping of key to an offset in the disk. But all secondary index tables have a mapping of key to the primary index’s key.
Primary index  
--------------------  
| key | Disk offset|  
--------------------  
Secondary index  
---------------------  
| key | primary key |  
---------------------  
  • The drawback is that a lookup of the secondary table needs two hops.
  • On the other hand, a change in a row only needs modification of the primary index. It avoids changing the secondary index for every change in row’s data
  • MySQL replication uses logical information instead of data with physical information used in PostGres.
  • Logs are compact compared to PG.
  • MySQL manages its own cache, unlike PG that uses buffer cache.
  • PG buffer cache use is inefficient since a read operation would have to land in the buffer cache and hence a context switch.
  • PG uses a process per connection. It is expensive. MySQL uses threads for a connection and scales without too much resource consumption.
Advertisements

MySQL & Python- Error: 2006 mysql has gone away

MySQL & Python: Error: 2006 mysql has gone away

This problem occurs for multiple reasons such as DB connection problem. In our code, we hit this issue due to a subtle problem with DB cursor.

The code was as following:

with conn as cur:
    try:
        print "hello"
    except:
        print "sorry"
    finally: 
        conn.close()

The above code would throw the error 2016 mysql has gone away exception. The problem lies in with conn as cur. This statement creates a cursor on the DB and the cursor object autmatically gets destroyed.
Here, we are closing the DB connection before the automatic destruction happened.

So since connection was invalid(closed), cursor deletion hit an exception.

The solution is to close the connection after cursor object deletion.

try:
    with conn as cur:
        print "hello"
except:
    print "sorry"
finally: 
    conn.close()

Written with StackEdit.

Part I MySQL for beginners: Frequent commands

I am dabbling my feet in SQL after a long time. I studied RDBMS in academics and worked briefly on SQLite.

Following is a list of commands, errors and suggestion, I have come across. This information is collection from various independent sources.

  • SQL Server Table Column Types:
bigint    Integer value (-9,223,372,036,854,775,808 - 9,223,372,036,854,775,807)    2^63
int    smaller Integer value (-2,147,483,648) - (2,147,483,647)    2^31
smallint    smaller Integer value (-32,768) - (32,767)    2^15
tinyint    smaller Integer values 0 - 255    2^8
bit    Integer data value (either 1 or 0 value)    1 or 0
decimal    Decimal values from -10^38 - 10^38    10^38
numeric    Decimal values from -10^38 - 10^38    10^38
money    Money values (-922,337,203,685,477.5808) - (922,337,203,685,477.5807)    2^63
smallmoney    Smaller Money Values (-214,748.3648) - (214,748.3647)    2^31
datetime    Date value (January 1, 1753) - (December 31, 9999)    
smalldatetime    Smaller Date Value (January 1, 1900) - (June 6, 2079)    
timestamp    Unique Number Value (updates when row is updated)    
char    Character String Value (max 8,000 characters)    
varchar    Character String Value maximum of 8,000 characters, unless otherwise noted)    
nchar    Character String Value (max 4,000 characters)    
nvarchar    Character String Value (max 4,000 characters)    
text    Character String Value (max 2,147,483,647 characters)    2^31
ntext    Character String Value (max 1,073,741,823 characters)    2^30
binary    Binary Value (max 8,000 bytes)    
varbinary    Binary Value (max 8,000 bytes)    
image    Binary Value (max 2,147,483,647 bytes)    2^31
uniqueidentifier    Global Unique ID (GUID)
  • Deleting a foreign key from a table

mysql> alter table address drop foreign KEY id;

  • View a table in detail

mysql> show create table address;

  • Adding primary key to a table

mysql> alter table address add primary KEY(addr_id);

  • adding foreign key to a table

mysql> alter table address ADD FOREIGN KEY(id) REFERENCES entity(id) ;

  • Change a column name

mysql> alter table entity change name fname varchar(128);

  • Adding auto-increment

mysql> alter table meta_entity modify id int not null AUTO_INCREMENT;