Difference in result between Mysql 5.5 and Mariadb 5.5

Question

I have a query running on my site that is installed the Mysql 5.5.43, but when I went up on the server (which has the MariaDB 5.5.38 and I only came to know now) the query does not return the same result, and so I wonder if someone could tell me why this wrong result and the differences between these two databases, that although they are very similar, there are their differences, or I am wrong?

Here is an example of the query I’m trying to run, remembering that in Mysql returns the correct results and in Mariadb that is not on the server:

SELECT DISTINCT umr.tema_id,

    (
        SELECT count(vw1.tema_id)
        FROM vw_unidades_monitoramentos_respostas vw1
        WHERE ((vw1.tema_id = umr.tema_id)
        AND (vw1.unidade_id = 68)
        AND (vw1.opt_aplicavel = 1)
        AND (vw1.opt_conforme = 1)
        AND (vw1.dt_validate >= '2015-07-22'))
    ) as conforme,

    (
        SELECT count(vw2.tema_id)
        FROM vw_unidades_monitoramentos_respostas vw2
        WHERE ((vw2.tema_id = umr.tema_id)
        AND (vw2.unidade_id = 68)
        AND (vw2.opt_aplicavel IS NULL))
    ) as naoAvaliado,

    (
        SELECT count(vw3.tema_id)
        FROM vw_unidades_monitoramentos_respostas vw3
        WHERE ((vw3.tema_id = umr.tema_id)
        AND (vw3.unidade_id = 68)
        AND (vw3.opt_aplicavel = 1 OR vw3.opt_aplicavel IS NULL))
    ) as totalAtribuido

FROM vw_unidades_monitoramentos_respostas umr
WHERE (umr.unidade_id = 68 AND (umr.opt_aplicavel = 1 OR umr.opt_aplicavel IS NULL))

Here is a print I took of the results running this query in both databases and see the difference between the two databases, remembering that the data are the same.

Follow link with a database dump for a test. https://gist.github.com/marcelod/91836c31cf4bdce5eadc

Answer 1

Incompatibilities between Mariadb 5.1 and Mysql 5.1

In a few cases Mariadb must be incompatible to allow Mariadb to provide more and better information than Mysql.

Here is the list of all known user level incompatibilities you may have while using Mariadb 5.1 instead of the Mysql 5.1.

1. Installation package names start with Mariadb instead of the Mysql.

2. Times can be different like Mariadb is in many cases faster than the Mysql.

3. Mysqld in Mariadb also reads the sections [Mariadb] of your files my.cnf.

4. You cannot use a single binary storage mechanism library with Mariadb if it is not compiled for exactly the same version Mariadb. (This is because the internal server structure THD is different between Mysql and Mariadb. This is also common among the different versions of Mysql). This shouldn’t be a problem as most people don’t carry new storage mechanisms and Mariadb comes with more storage mechanisms than the Mysql.

5. CHECKSUM TABLE may give different result as Mariadb does not ignore NULL in the columns of how Mysql 5.1 makes (future versions of Mysql shall calculate the verification sums in the same way as Mariadb). You can get the checksum "old style" in Mariadb initiating the Mysqld with the option --. Note however that what Myisam and storage mechanisms in Mariadb is using the new verification internally, so if you are using --old, the command CHECKSUM will be slower as it needs to calculate the checksum of line by line.

6. The slow query log has more information about the query, which can be a problem if you have a script that analyzes the slow query log.

7. Mariadb, by default takes a little more memory than the Mysql because we have enabled by default the Aria storage mechanism for handling internal temporary tables. If you need to Mariadb take very little memory (at the cost of performance), you can set the value of aria_pagecache_buffer_size to 1M (default is 128M).

8. If you are using new command options, new features of Mariadb or new storage mechanisms, you cannot move easily and back between Mysql and Mariadb.

Source: https://mariadb.com/kb/en/mariadb/mariadb-vs-mysql-compatibility/

Man, it looks like the differences are starting to show now, but the older ones don’t make much of a difference.

Answer 2

According to tests obtained with the same records and tables show that the results are the same:

In Mariadb:

     MariaDB [test]> SELECT DISTINCT umr.tema_id,    (        SELECT count(vw1.tema_id)        FROM vw_unidades_monitoramentos_respostas vw1        WHERE ((vw1.tema_id = umr.tema_id)        AND (vw1.u
nidade_id = 68)        AND (vw1.opt_aplicavel = 1)        AND (vw1.opt_conforme = 1)        AND (vw1.dt_validate >= '2015-07-22'))    ) as conforme,    (        SELECT count(vw2.tema_id)        F
ROM vw_unidades_monitoramentos_respostas vw2        WHERE ((vw2.tema_id = umr.tema_id)        AND (vw2.unidade_id = 68)        AND (vw2.opt_aplicavel IS NULL))    ) as naoAvaliado,    (        SE
LECT count(vw3.tema_id)        FROM vw_unidades_monitoramentos_respostas vw3        WHERE ((vw3.tema_id = umr.tema_id)        AND (vw3.unidade_id = 68)        AND (vw3.opt_aplicavel = 1 OR vw3.op
t_aplicavel IS NULL))    ) as totalAtribuido FROM vw_unidades_monitoramentos_respostas umr WHERE (umr.unidade_id = 68 AND (umr.opt_aplicavel = 1 OR umr.opt_aplicavel IS NULL));
+---------+----------+-------------+----------------+
| tema_id | conforme | naoAvaliado | totalAtribuido |
+---------+----------+-------------+----------------+
|      20 |        2 |           0 |              2 |
|      21 |        3 |          18 |             24 |
|      11 |        8 |          19 |             29 |
|      24 |        1 |           0 |              1 |
|      18 |        1 |           0 |              1 |
|      23 |        0 |           2 |              2 |
|       7 |        0 |           0 |              2 |
|       4 |        0 |           2 |              4 |
|       2 |        1 |           0 |              1 |
+---------+----------+-------------+----------------+
9 rows in set (0.04 sec)

MariaDB [test]>

IN Mysql:

    mysql> SELECT DISTINCT umr.tema_id,    (        SELECT count(vw1.tema_id)        FROM vw_unidades_monitoramentos_respostas vw1        WHERE ((vw1.tema_id = umr.tema_id)        AND (vw1.unidade_id
 = 68)        AND (vw1.opt_aplicavel = 1)        AND (vw1.opt_conforme = 1)        AND (vw1.dt_validate >= '2015-07-22'))    ) as conforme,    (        SELECT count(vw2.tema_id)        FROM vw_un
idades_monitoramentos_respostas vw2        WHERE ((vw2.tema_id = umr.tema_id)        AND (vw2.unidade_id = 68)        AND (vw2.opt_aplicavel IS NULL))    ) as naoAvaliado,    (        SELECT coun
t(vw3.tema_id)        FROM vw_unidades_monitoramentos_respostas vw3        WHERE ((vw3.tema_id = umr.tema_id)        AND (vw3.unidade_id = 68)        AND (vw3.opt_aplicavel = 1 OR vw3.opt_aplicav
el IS NULL))    ) as totalAtribuido FROM vw_unidades_monitoramentos_respostas umr WHERE (umr.unidade_id = 68 AND (umr.opt_aplicavel = 1 OR umr.opt_aplicavel IS NULL));
+---------+----------+-------------+----------------+
| tema_id | conforme | naoAvaliado | totalAtribuido |
+---------+----------+-------------+----------------+
|      20 |        2 |           0 |              2 |
|      21 |        3 |          18 |             24 |
|      11 |        8 |          19 |             29 |
|      24 |        1 |           0 |              1 |
|      18 |        1 |           0 |              1 |
|      23 |        0 |           2 |              2 |
|       7 |        0 |           0 |              2 |
|       4 |        0 |           2 |              4 |
|       2 |        1 |           0 |              1 |
+---------+----------+-------------+----------------+
9 rows in set (0.15 sec)

mysql>

NOTE: Data obtained through the link https://gist.github.com/marcelod/91836c31cf4bdce5eadc#file-database-sql

As far as we talk about differences between these databases, it is simple to remember the following rule, Mysql has an obligation to deliver everything that is implemented for Mariadb copy (in its free version), Mariadb will maintain compatibility, but you can do better or implement something new, which in turn may not work in Mysql in these cases.