MySQL Asynchronous vs. Semi-Synchronous Replication

January 25th, 2012
No comments

When it comes to choose your MySQL replication setup you have the choice between Asynchronous replication or Semi-Synchronous replication. At the time of writing there is no fully-synchronous solution for MySQL replications.

The way these two differ is interesting and would be very useful when you are choosing your architecture.

In MySQL Manual for semi-synchronous replication it is said very well :

  • With asynchronous replication, the master writes events to its binary log and slaves request them when they are ready. There is no guarantee that any event will ever reach any slave.

     

  • With fully synchronous replication, when a master commits a transaction, all slaves also will have committed the transaction before the master returns to the session that performed the transaction. The drawback of this is that there might be a lot of delay to complete a transaction.

     

  • Semisynchronous replication falls between asynchronous and fully synchronous replication. The master waits after commit only until at least one slave has received and logged the events. It does not wait for all slaves to acknowledge receipt, and it requires only receipt, not that the events have been fully executed and committed on the slave side.

So as you can see the ideal situation in terms of data consistency and no data-loss would be the fully-synchronous solution. Which on the other hand may result in a lot of delay in the performance of the system and will make the responses slower, as you are dealing with (at least) two nested levels of transactions. (Although fully-synchronous is not available)

On a Asynchronous solution, Master writes the events in the binary log and it may happen that no Slave would pick it up after Master has crashed or any other reason.

Semi-Synchronous seems to be good and practical solution for many cases where High Availability and No Data-loss is important, but you should consider that semi-synchronous “does not provide strong guarantees against data loss“. [article]

You may ask why ? It is as simple as this, imagine Master has sent out the event and one slave has received it, then Master will commit. But on the other hand the slave could have possibly crashed or timed-out or an error happens. In this way you have received the commit on the client, while in reality data has not been committed on the Slave. [article]

Just wanted to point out the differences and ask you to be careful when you are choosing you solution for replication. Semi-Synchronous Does NOT guarantee no data-loss.

 

High Availability, MySQL, Replication, SQL , , , , ,

Written by: MajiD Fatemian  

Python itertools; Cyclling through a Python list

October 18th, 2011
No comments

 

If you want to loop through a list in Python, and everytime you hit the last one you loop back and start from the beginning without throwing an exception you can use the Cycle module:

>>> from itertools import cycle
>>> my_list = [1,2,3]
>>> my_cycle = cycle(my_list)
>>> my_cycle.next()
1
>>> my_cycle.next()
2
>>> my_cycle.next()
3
>>> my_cycle.next()
1
>>> my_cycle.next()
2
>>> my_cycle.next()
3
>>> my_cycle.next()
1

Python, Tips , , ,

Written by: MajiD Fatemian  

MySQL, Select Random string values from a list of strings

October 13th, 2011
No comments

If you have ever wondered how to select a random string value from a list of given string in MySQL the following might be helpful :

UPDATE profile SET `GEO`= ELT(FLOOR(RAND()*8)+1, ‘US’, ‘CA’, ‘FR’, ‘DE’ , ‘UK’ , ‘IR’ , ‘RU’ , ‘GR’)

The code above sets the GEO field of the profile table to random values selected from a list. That would help when you’re populating dummy information in to your tables for testing.

ELT Command will return the string of a given index, and by doing a FLOOR(RAND()) , you can easily randomize the given index.

Hope it helps.

General

Written by: MajiD Fatemian  

Determine MySQL Table’s Primary Key Dynamically

October 6th, 2011
No comments

 

 

You may have been in a situation which you may need to figure out what is the PRIMARY key field of a given table. Or even if it is a compound key what are the columns building the PRIMARY key.
This usually helps a lot when you’re making some generic tools to do some jobs on a given table.

The Script is fairly easy, the following SQL command returns back all the INDEXes of a given Table :

SHOW INDEX FROM `tbl_name`

As you will see in the result, all the indexes are returned including the PRIMARY ones. The only concern is if the PRIMARY KEY is a compound key it has one entry in the list for each column.

Table           Non_unique  Key_name      Seq_in_index  Column_name 
---------------------------------------------------------------------
test_table      0           PRIMARY       1             Config_ID   
test_table      0           PRIMARY       2             User_ID     
test_table      1           idxSettingID  1             User_ID

 

So you can easily loop through the results and find the compound keys.

Here is a sample Python script which does the same thing, using the  List Comprehension :

cursor.execute(‘SHOW INDEX FROM `test_table`’)
if cursor.rowcount < 1 :
return []

return [d[4] for d in localCursor.fetchall() if d[2].upper() == ‘PRIMARY’] [row[4] for row in cursor.fetchall() if row[2].upper() == ‘PRIMARY’]

Update – 18.Oct.2011 :

Also you can use the following SQL to get information about the table and loop through it in Python :

DESCRIBE `tbl_name`

General

Written by: MajiD Fatemian  

Python’s MySQLdb Parameter Placeholders

July 14th, 2011
No comments

Using “MySQLdb” API for Python is very handy and also recommended to use String Formatted Parameterized placeholders, because when you pass them as the “execute” ‘s function operands, the library does the string formatting for you and also does the escaping to prevent [Basic] SQL injections.
But there are two notes to consider when you’re using them :

1 – pass the params as a tuple

You have to always pass the params as a tuple to the execute function. So the following is not valid :

max_value = 5
sql = ‘SELECT * FROM my_table WHERE Score = %s ‘
my_cusror.execute(sql , max_value)

Beacuse the max_value is not being passed as tuple. So you have to pass it like this :

my_cusror.execute(sql , (max_value,) )

You have to enclose it with Parenthesis and more important than that you have to have a “,” at the end. Because only having the Parenthesis doesn’t enforce it to a tuple, while the last “,” does.
Obviously if you have more than one parameter to send, you won’t need it as you already have some “,” in place.

my_cusror.execute(sql , (max_value,minvalue) )

 

2 – Placeholders can only be used to insert column values

According to the documents :
[quote]
Parameter placeholders can only be used to insert column values. They can not be used for other parts of SQL, such as table names, statements, etc.
[/quote]
Actually I figured this out the hard way, I was trying to join a series of IDs and pass it as one Parameter to the SQL :

sql = ‘SELECT * FROM my_table WHERE ID IN (%s)’
my_cusror.execute(sql , ‘,’.join(map(str,IDS)))

But I’m only getting the result for the first ID. Even this didn’t work :

sql = ‘SELECT * FROM my_table WHERE ID IN (%s)’
ids_str = ‘,’.join(map(str,IDS))
my_cusror.execute(sql , ids_str)

But this works like a charm :

sql = ‘SELECT * FROM my_table WHERE ID IN (%s)’ % ‘,’.join(map(str,IDS))

So I eventually learned that :
[quote]
Parameter placeholders can only be used to insert column values. They can not be used for other parts of SQL, such as table names, statements, etc.
[/quote]

General

Written by: MajiD Fatemian  

MySQL Triggers ; Error 1442

July 5th, 2011
No comments


MySQL Trigger is a powerful tool to provide the data consistency and accuracy among your tables or Database in general.
The only disadvantage among Triggers is that they’re not able to update the same table they’re based on.
If you have ever hit the following error on your SQL updates, well, you pretty much have no luck.

Error Code : 1442
Can’t update table ‘my_table’ in stored function/trigger because it is already used by statement which invoked this stored function/trigger.

It might seem logical to be working for a “AFTER UPDATE” Trigger. On a “BEFORE UPDATE” Trigger specially in a MyISAM table engine, the whole table is locked and the UPDATE on the same table would fail. But that’s exactly the case for “AFTER UPDATE” as well. I’m not sure it is because of locking too, but it will fail.

Also if you want to do a trick and CALL a STORED PROCEDURE from the trigger to do the job for you, MySQL won’t let you do this. So as I said earlier when you hit the error just gave up the Trigger, or SP solution and think of another way with a Control Script, Cron Job or the same logic being implemented in your application layer.

This error which I would call it a Bug has been reported for years, but only has been considered as a feature request !

You can read more about almost the same approach here.

General, MySQL , ,

Written by: MajiD Fatemian  

MySQL Federated Tables

May 17th, 2011
No comments

I’ve been hearing for MySQL FEDERATED Tables for a while, today went through them and figure out how amazing they are.
In simple terms a MySQL FEDERATED Table is a proxy to a regular table located in another DB server. You will see the table as a local table while it’s being located in another MySQL Database Server.

The remote table could be defined in as any Storage-Engine as you want, you just need to map the columns and set the connection. So in your local DB server you can easily query your remote table as if it’s a local table located in your Database.

If you have a remote table like this :

CREATE TABLE test_table (
    id INT(20) NOT NULL AUTO_INCREMENT,
    name VARCHAR(32) NOT NULL DEFAULT ”,
    other INT(20) NOT NULL DEFAULT ’0′,
    PRIMARY KEY (id),
    INDEX name (name),
    INDEX other_key (other)
)
ENGINE=MyISAM
DEFAULT CHARSET=latin1;

You can set your Federated table like this :

CREATE TABLE federated_table (
    id INT(20) NOT NULL AUTO_INCREMENT,
    name VARCHAR(32) NOT NULL DEFAULT ”,
    other INT(20) NOT NULL DEFAULT ’0′,
    PRIMARY KEY (id),
    INDEX name (name),
    INDEX other_key (other)
)
ENGINE=FEDERATED
DEFAULT CHARSET=latin1
CONNECTION=’mysql://fed_user@remote_host:9306/federated/test_table’;

where the connection is :

scheme://user_name[:password]@host_name[:port_num]/db_name/tbl_name

And according to MySQL Documents here is the procedure that MySQL follows for a query from a Federated table [1]:

  1. The storage engine looks through each column that the FEDERATED table has and constructs an appropriate SQL statement that refers to the remote table.

    2.  

  2. The statement is sent to the remote server using the MySQL client API.

    3.  

  3. The remote server processes the statement and the local server retrieves any result that the statement produces (an affected-rows count or a result set).
     

  4. If the statement produces a result set, each column is converted to internal storage engine format that the FEDERATED engine expects and can use to display the result to the client that issued the original statement.

    5.  

You have to keep in mind that indexes should be created in the remote server, also you can not alter the table. Also Federated tables can not support transaction. And as another disadvantage of Federated tables, you have to be careful that “the size of the insert cannot exceed the maximum packet size between servers. If the insert exceeds this size, it is broken into multiple packets and the rollback problem can occur” [2].

Here you can find very useful information about the MySQL Federated Tables.

MySQL FEDERATED Storage-Engine may not be something you deal with every day and for every project. But for distributed projects it makes a lot of sense to use and consider them. Just knowing the concept opens your eyes to a new way of solving the distributed architectures problem.

References :

[1] : Description of the FEDERATED Storage Engine
[2] : Limitations of the FEDERATED Storage Engine

General

Written by: MajiD Fatemian  

Dynamic / Static Typing vs. Strong / Weak Typing

May 6th, 2011
No comments

Here is a great article about Static vs. Dynamic typing and Strong vs. Weak typing.

But as to summarize it ; Dynamic typing languages are which variables’ type declarations are not mandatory and they will be generated on the fly, by their first use [PHP, Python]. While Static typing languages are the ones which variable declarations are mandatory before usage [Java, C].

/* C code */
static int num, sum;
num = 5;
sum = 10;
sum = sum + num;

/* PHP Code */
$sum = 10;
$sum++;
echo $sum;

On the other hand the difference between Strong and Weak typing is different; Strong typing is when a variable is declared as a specific type (no matter by Dynamic or Static typing) it is necessarily bound to that particular data type [Python, Java]. While in Weak typing variables are not of a specific data type ;  it does mean that variables are not “bound” to a specific data type [C,PHP] [1].

/* Python code */
>>> foo = "x"
>>> foo = foo + 2
>>>>>
Traceback (most recent call last):
File "", line 1, in ?
foo = foo + 2
TypeError: cannot concatenate 'str' and 'int' objects
>>>

/* PHP code */
$foo = "x";
$foo = $foo + 2; // not an error
echo $foo;

 

[1] Introduction to Static and Dynamic Typing

 

General

Written by: MajiD Fatemian  

Lazy PHP

May 6th, 2011
No comments

I would say I love this site. It’s a very simple to use and handy but on the other hand, knowledgeable and informative website containing tons of interesting articles, tutorials and forum discussions.

When I was about to start learning PHP and MySQL I was introduced to an article by the name of “Build you own Database Driven Website Using PHP and MySQL”. Now this is the closes thing I can find to that article, which is sample PDF of a book. I believe even this sample is enough to get you started.

All I wanted to say is this site has it all, various resources for all levels.

Today I came across another interesting article about PHP, how it works and how to make its performance better. The article is quite old but still applicable and useful.

It is very useful to know behind the scenes of the technology you’re using. The low-level knowledge of “How things work” will give you better understanding of how to improve you application and how to increase the performance. And the more you know the tools provided, the better and faster you can develop.

The way PHP scripts are being executed and interpreted, gives you an idea of how you can improve the performance. In this article it speaks about the 4 stage execution of each request by WebServer/PHP :

  1. Incoming Request : When WebServer receives the Request for the script
  2. Prepare OPCODEs : When the PHP script is being parsed and OPCODES are being generated.
  3. Execution of script and responding back to user
  4. Trash the lot including all the OPCODEs, variables and etc

We all know that now a days there are several different tools to improve the stages 2 and 4 which are the most resource consuming and duplicated tasks of the PHP Interpreter. PHP Accelerators Like APC , eAccelerator,XCache and so on help a lot to eliminate the phases #2 and #4.

Most PHP accelerators work by caching the compiled bytecode of PHP scripts to avoid the overhead of parsing and compiling source code on each request[1].

Also in this great series of articles “Anonymous functions” known as “Closures” and “Variable Functions” are being discussed and there are some simple but very helpful samples of how they work and how they can help.

Also techniques like ‘Lazy includes’ which seems to be pretty obvious but many of us forget it, have been discussed.

I will let you read the articles by yourself ; Part-1 , Part-2, Part-3 and enjoy it :-)

[1] : http://en.wikipedia.org/wiki/PHP_accelerator

1. Incoming Request:Incoming Request

PHP , , ,

Written by: MajiD Fatemian  

Exposé Application windows + Spaces

August 2nd, 2010
3 comments

Spaces is an amazing feature since OS X Leopard.
I’ve been using it since I switched to Mac and enjoying it, although it still has some flaws.
The main issue which at least I haven’t figured it out yet is how to navigate between different windows of an application while they are in different Spaces. Mac Command Icon + ~ Doesn’t help. Although I have most of my main applications assigned to specific Spaces, there are some applications that are being used regularly and for different purposes, like TextEdit which I want to paste a temporary value or write down something, or open a ReadMe file or etc.
This could happen in different Spaces and I don’t have the option to navigate through different windows while they’re are in different spaces.

Exposé for Application windows

This can be activated using F10 in old Macs or Control+F3 in new Macs, and it shows all the windows of the front most application, no matter in which Space they are.

I Know! it’s not a great discovery but it’s a time saver feature ;)

General, Mac, osx , ,

Written by: MajiD Fatemian