Result difference between MySql 5.5 and MariaDB 5.5

Question

Result difference between MySql 5.5 and MariaDB 5.5

Navigation

#1 by (13 votes)
#2 by (4 votes)

16

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

Here is an example of the query I am trying to run, remembering that in Mysql it returns the correct results and in MariaDB it is on the server that does not:

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 that I took out of the results by running this query on both bases and see the difference between the two banks, remembering that the data is the same.

Linktoadumpinthedatabasetobetested. link

mysql mariadb

asked by anonymous 01.07.2015 / 05:49

2 answers

How to remove auto complete from google Chrome input? Where to create your own class, and how to instantiate it later?

score 13 · Answer 1

Incompatibilities between MariaDB 5.1 and MySQL 5.1

In a few cases MariaDB has to be incompatible to allow MariaDB to provide more and better information than MySQL .

Here is the list of all known level-level incompatibilities you may have when using MariaDB 5.1 instead of MySQL

The names of the installation packages begin with MariaDB instead of MySQL .

MySQLd in MariaDB also reads the sections [ MariaDB ] from your my.cnf files.

li>

You can not use a single binary storage engine library with MariaDB if it is not compiled to exactly the same version MariaDB . (This is because the built-in THD server is different between MySQL and MariaDB.) This is also common among the different versions of MySQL. This should not be a problem as most people do not load new storage mechanisms and MariaDB comes with more storage mechanisms than MySQL .

/ li>

MySQL 5.1 does (future versions of MySQL > must calculate checksums in the same way as MariaDB ). You can get the "old style" checksum in MariaDB by starting MySQLd with the CHECKSUM TABLE option. Note however that what MyISAM and Aria storage mechanisms in MariaDB are using the new check internally, so if you are using -- , the --old command % will be slower since you need to calculate the line-by-line checksum.

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

MariaDB , by default, takes a little more memory than MySQL because we have by default enabled the Aria storage engine for manipulation of temporary tables internal controls. If you need MariaDB to take very little memory (at the expense of performance), you can set the value of CHECKSUM to 1M (default is 128M).

If you are using new command options, new MariaDB features, or new storage engines, you can not easily move back and forth between MySQL > and MariaDB .

Source: link

Man, it looks like the differences will start to appear now, but the older ones do not make much difference.

score 4 · Answer 2

According to tests obtained with the same records and the same tables, 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]>

EM 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>

OBS: Data obtained through the link link

As far as the differences between these databases are mentioned, it is simple to remember the following rule, Mysql is obliged to deliver everything that is implemented for the MariaDB copy (in its free version), MariaDB will maintain the compatibility , but you can do better or implement something new, which in turn may be that in MySql do not run in these cases.