MySQL Asynchronous vs. Semi-Synchronous Replication

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.

MySQL Triggers ; Error 1442


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.

get DB handler in CodeIgniter for stand-alone Libraries

We were working on a project where some developers were working with CI and were developing libraries there, and some developers were developing stand-alone libraries. But both groups were working on the same project.

At some point we needed all those stand-alone libraries to be  used in CI as well.
For common code it’s not a big deal, the only problem which took a little bit of time to figure out was using the DataBase. We didin’t want to make new DB connections, so how could we use the current connection, inside an stand-alone code.

The answer is pretty easy, CI’s DB class has a non-documented attribute which returns back the DB handler.

$this->db->conn_id

or if you have multiple DB cnnections that would be :

$this->dbr->conn_id;
$this->dbw->conn_id;

and then you can easily pass the DB connection to regular mysql_query function like this :

mysql_query( string $query[,resource $link_identifier] );

and as an example :

mysql_query( 'SELECT * FROM `table1`', $this->dbr->conn_id );

who is “Definer” in terms of MySQL

Today I was playing with our database settings and trying to grant/ cut permissions of users from the development DB and production DB, changing passwords and so on.
Using our primitive hosting’s control panel I wanted to remove the main production DB user from Development DB, which I ended up loosing the user completely.
I created a new user/pass and in few seconds I changed the configurations. Evertyhing looked fine till I found that something is missing and not working properly.

The problem was on one of our scripts and the error was :

The user specified as a definer : (‘no_such_user’@’no_such_host’) is invalid or unregistered

And thank to our primitive control panel, I was not able to recreate the user as it contains “_” [underscore] in the username.

All the settings were fine and I was wondering what’s causing the problem. At first rush I thought that it could be a cache, as I had requested recently to have xcache on our server.

But it wasn’t the case. The error was much more stupid than even one can imagine. That specific script uses a stored procedure to insert / fetch data to/from MySQL. The user who had created the sp was the one who was deleted. And that was the problem. The term “Definer” in terms of MySQL is the one who creates the stored procedure and for the stored procedure to be executed that user must exists.

A quick solution for that was dropping the old stored procedure and re-create it while logged in to MySQL with the new user.

Voila, it solved our issue. Not the best solution but at least I figured out what “Definer” means and it must exists when a sp is being called.

WAMP server: right-click and select your version

Have you ever been in a situation that you need to install PHP5 to have one application which is spcifically written in PHP5 and you have also some legacy applications which are written in PHP4 and are not compatible with PHP5 but you want to have them both.

I used to hate all those software bundles installing Apache/MySQL/PHP for you easily. I prefered to have them separately installed by myself to have more control over it. But considering WAMP server with an amazing feature of having many versions of Apache/MySQL/PHP and just switch from one to another by right-clicking and selecting it, is awesome.

I’ll definitely consider it for my local machine. [First need to be done with my exam on Wednesday]

MySQL – group results by second/minute/hour

This is a simple MySQL tip, but it helped me a lot.

We have a traffic table which monitors every single coming traffic to our applciation. I wanted to get the real values for how many requests I we have per Second / Minute / Hour and also what times of the the day are high traffic and what times are low traffic, to do some load-balancing on the application and server.

The query is very simple, just COUNT the number and do a GROUP BY and extract the minute/hour/…

Like :

SELECT tr_date_time,count(*) AS NUM FROM `traffic`
    WHERE DATE(`tr_date_time`) = '2009-04-25'
    GROUP BY EXTRACT(HOUR_MINUTE FROM tr_date_time);

The script above extracts the number of hits per minute during the day.
Don’t forget that if you want to have the result by minutes, you have to add the hour as well to make it unique.

p.s: I hope your Date/Time/DateTime field is human friendly not Unix_timestamp, which will add some more extra calcuations on the result set.