do you have some data to test? 2013/9/1 Roberto Spadim <roberto@spadim.com.br>:

well.. provide create tables like: SHOW CREATE TABLE dbmail_mimeparts; SHOW CREATE TABLE dbmail_partlists; SHOW CREATE TABLE dbmail_physmessage;

2013/9/1 Reindl Harald <h.reindl@thelounge.net>

...

if it would be that easy

i am only a guy try to help dbmail and not that good in data details and how to reproduce a valid test set, al i can say is that it wasted a lot of hours to figure out why the machines of the dbmail-developer are beahve differently in case of nsetd mime-messages and that it is caused by MariaDB afetr that i searched my archive for the mail below

AFAIK i even tried to bring MariaDB/DBMail community together months ago

*please* some MariaDB developers could you connect to dbmail/libzdb to sort out this problem because from the original post below and the feedback of dbmail-developer the query is correct with the terminal client

so something goes wrong in combination with libzdb IMHO this *must not* happen if MariaDB claims to be a drop-in-replacemnet

Am 02.09.2013 00:44, schrieb Roberto Spadim:

...

Could you send some data to test?

Em 01/09/2013 18:37, "Reindl Harald" <h.reindl@thelounge.net <mailto:h.reindl@thelounge.net>> escreveu:

please can someone from the MariaDB developers MariaDB 5.5.32 is running here on test-machines

this is todays feedback of the dbmail core-developer and beause more and more distributions including Fedora/OpenSuSE and AFAIK even RHEL7 switch to MariaDB this is a *serious* problem which is not what a "drop-in replacement" should do and a show-stopper rollout Fedora 19 at all in the near future

> Anyway, MariaDB doesn't even pass the basic unit-tests > > Looks like this maybe libzdb related. The ordering of the query > retrieving mime parts is ok on the console, but *not* when looping > over the result in libzdb. > > MariaDB is *no-go* at this moment. Just what I experienced a year > ago or so. I'm sorry, but I don't think I'll hold 3.1.4 for that > not a regression at all.

-------- Original-Nachricht -------- Betreff: Re: [Dbmail] MariaDB and dbmail Datum: Tue, 02 Apr 2013 19:47:15 +0400 Von: Sergej Pupykin <ml@sergej.pp.ru <mailto:ml@sergej.pp.ru>> An: DBMail mailinglist <dbmail@dbmail.org <mailto:dbmail@dbmail.org>>

At Thu, 20 Sep 2012 10:46:52 +0800, zamri <myzamri@gmail.com <mailto:myzamri@gmail.com>> wrote: > MariaDB is fully compatible with MySQL. Migration is done. So far so good. Phewww!... > > Now I need to do some research on tuning mariadb if needed.

I found that after migration following SQL returns wrong sorted data:

SELECT l.part_key, l.part_depth, l.part_order, l.is_header, DATE_FORMAT(ph.internal_date,GET_FORMAT(DATETIME,'ISO')), data FROM dbmail_mimeparts p JOIN dbmail_partlists l ON p.id <http://p.id> = l.part_id JOIN dbmail_physmessage ph ON ph.id <http://ph.id> = l.physmessage_id WHERE l.physmessage_id = <ID> ORDER BY l.part_key,l.part_order ASC;

however when I run it from command line it works well.

Probably it does not fail always because of not all emails damaged.

Not sure if it caused by migration to mariadb, but problem appeared near same time.

-- Roberto Spadim SPAEmpresarial

-- Roberto Spadim SPAEmpresarial

please send the mariadb version too 2013/9/2 Roberto Spadim <roberto@spadim.com.br>:

please Reindl provide some data to test, i found some information, we can add a bug at jira (bug tracker) of mariadb and solve this problem... (if it exists...)

here one report from archlinux guys similar to your report, maybe we (mariadb) have a problem with order by (?) https://mailman.archlinux.org/pipermail/arch-general/2013-April/033282.html

here the create tables: -------------------------------------------------------- CREATE TABLE IF NOT EXISTS `dbmail_mimeparts` ( `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT, `hash` char(128) NOT NULL, `data` longblob NOT NULL, `size` bigint(20) NOT NULL DEFAULT '0', PRIMARY KEY (`id`), KEY `hash` (`hash`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8; --- CREATE TABLE IF NOT EXISTS `dbmail_partlists` ( `physmessage_id` bigint(20) unsigned NOT NULL DEFAULT '0', `is_header` tinyint(1) NOT NULL DEFAULT '0', `part_key` smallint(6) NOT NULL DEFAULT '0', `part_depth` smallint(6) NOT NULL DEFAULT '0', `part_order` smallint(6) NOT NULL DEFAULT '0', `part_id` bigint(20) unsigned NOT NULL DEFAULT '0', UNIQUE KEY `message_parts` (`physmessage_id`,`part_key`,`part_depth`,`part_order`), KEY `physmessage_id` (`physmessage_id`), KEY `part_id` (`part_id`), CONSTRAINT `dbmail_partlists_ibfk_1` FOREIGN KEY (`physmessage_id`) REFERENCES `dbmail_physmessage` (`id`) ON DELETE CASCADE ON UPDATE CASCADE, CONSTRAINT `dbmail_partlists_ibfk_2` FOREIGN KEY (`part_id`) REFERENCES `dbmail_mimeparts` (`id`) ON DELETE CASCADE ON UPDATE CASCADE ) ENGINE=InnoDB DEFAULT CHARSET=utf8; --- CREATE TABLE IF NOT EXISTS `dbmail_physmessage` ( `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT, `messagesize` bigint(20) unsigned NOT NULL DEFAULT '0', `rfcsize` bigint(20) unsigned NOT NULL DEFAULT '0', `internal_date` datetime NOT NULL DEFAULT '0000-00-00 00:00:00', PRIMARY KEY (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8; ---

-- Roberto Spadim SPAEmpresarial

hi Reindl I'm an user like you and i don't use dbmail, next time, try to provide a test case, this make things faster than just report something without usefull information, at least the create table and the select query that have problems others guys can help you better here, when reporting a bug, report at mariadb bug tracker: http://mariadb.atlassian.net/ good lucky 2013/9/2 Reindl Harald (mobile) <h.reindl@thelounge.net>:

As said in my initial post 5.5.32 and if you read the whole post you see also that this exists very long

*please* read my whole initial post - I *can not* send test data because it is *not* reproduceable in a terminal so how should I as ordinary user verify that whatever I send is qualified to prove the problem - so please do not insist that I should provide the test data because with the queries from my initial post and some lines code using libzdb this should be easy to verify - as php developer I can not provide it

mail's with multiple mime parts are broken and reconstructed in the wrong order and so destroyed - so install dbmail and send such test mail's - you not "maybe" have a order by problem you *have* have one which wasted a lot of hours to debug a mailserver because a software claiming to be a drop-in replacement is broken - and I would not be that angry if I had not tried to bring MariaDB upstream to the problem a other user reported months ago and nobody cared

-------- Ursprüngliche Nachricht -------- Von: Roberto Spadim <roberto@spadim.com.br> Gesendet: Mon Sep 02 06:28:48 MESZ 2013 An: Reindl Harald <h.reindl@thelounge.net> CC: Mailing-List mariadb <maria-discuss@lists.launchpad.net>, Mailing-List dbmail <dbmail@dbmail.org> Betreff: Re: [Maria-discuss] Fwd: [Dbmail] MariaDB and dbmail

please send the mariadb version too

2013/9/2 Roberto Spadim <roberto@spadim.com.br>:

...

please Reindl provide some data to test, i found some information, we can add a bug at jira (bug tracker) of mariadb and solve this problem... (if it exists...)

here one report from archlinux guys similar to your report, maybe we (mariadb) have a problem with order by (?) https://mailman.archlinux.org/pipermail/arch-general/2013-April/033282.html

here the create tables: -------------------------------------------------------- CREATE TABLE IF NOT EXISTS `dbmail_mimeparts` ( `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT, `hash` char(128) NOT NULL, `data` longblob NOT NULL, `size` bigint(20) NOT NULL DEFAULT '0', PRIMARY KEY (`id`), KEY `hash` (`hash`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8; --- CREATE TABLE IF NOT EXISTS `dbmail_partlists` ( `physmessage_id` bigint(20) unsigned NOT NULL DEFAULT '0', `is_header` tinyint(1) NOT NULL DEFAULT '0', `part_key` smallint(6) NOT NULL DEFAULT '0', `part_depth` smallint(6) NOT NULL DEFAULT '0', `part_order` smallint(6) NOT NULL DEFAULT '0', `part_id` bigint(20) unsigned NOT NULL DEFAULT '0', UNIQUE KEY `message_parts` (`physmessage_id`,`part_key`,`part_depth`,`part_order`), KEY `physmessage_id` (`physmessage_id`), KEY `part_id` (`part_id`), CONSTRAINT `dbmail_partlists_ibfk_1` FOREIGN KEY (`physmessage_id`) REFERENCES `dbmail_physmessage` (`id`) ON DELETE CASCADE ON UPDATE CASCADE, CONSTRAINT `dbmail_partlists_ibfk_2` FOREIGN KEY (`part_id`) REFERENCES `dbmail_mimeparts` (`id`) ON DELETE CASCADE ON UPDATE CASCADE ) ENGINE=InnoDB DEFAULT CHARSET=utf8; --- CREATE TABLE IF NOT EXISTS `dbmail_physmessage` ( `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT, `messagesize` bigint(20) unsigned NOT NULL DEFAULT '0', `rfcsize` bigint(20) unsigned NOT NULL DEFAULT '0', `internal_date` datetime NOT NULL DEFAULT '0000-00-00 00:00:00', PRIMARY KEY (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8; ---

--

Reindl Harald (mobile) the lounge interactive design GmbH A-1060 Vienna, Hofmühlgasse 17 CTO / CISO / Software-Development +43 (676) 40 221 40 http://www.thelounge.net

-- Roberto Spadim SPAEmpresarial

Ciao, I wanted to add some info in the CREATE PROCEDURE page in the KB, but I think that some info about CREATE PROCEDURE clauses miss both in the kb and in the MySQL manual. * [NOT] DETERMINISTIC. From MySQL docs: "A routine is considered “deterministic” if it always produces the same result for the same input parameters, and “not deterministic” otherwise." What does this exactly mean for procedures? OUT and INOUT parameters? Resultsets returned by the procedure? Both? * NO SQL: As far as I understand, even SET is a SQL statement. I guess that a "no sql" function is something like CREATE FUNCTION x(n INT) RETURNS INT BEGIN RETURN n*2; END; But what is a "no sql" procedure (if such a procedure even exists)? I'm sorry if this info is already written somewhere, but I really wasn't able to find it - and I feel that the KB should specify things like these. Greetings Federico

just an idea, but i'm not sure about it... - NO SQL indicates that the routine contains no SQL statements. maybe it's something about what function/procedure do (maybe in future with another language/script? python?) a function that don't use SQL, probably will not use sql parser... i'm not sure about this, but it's a point to consider, since if you parse the function/procedure using SQL parser, you will lost time/ram with it, and if function don't have SQL (python?) you shouldn't lost this resources... i'm not sure, but maybe it's something about it reading source code.... i think it's dead code... at least i don't found something interesting using it: in sql_yacc.yy: | NO_SYM SQL_SYM { Lex->sp_chistics.daccess= SP_NO_SQL; } from command line: C:\tmp\mysql-gdb\mariadb-10.0.3\sql>grep "SP_NO_SQL" * grep: CMakeFiles: Invalid request code grep: examples: Invalid request code grep: share: Invalid request code sp.cc: chistics.daccess= SP_NO_SQL; sp.cc: case SP_NO_SQL: sql_lex.h: SP_NO_SQL, sql_yacc.yy: { Lex->sp_chistics.daccess= SP_NO_SQL; } 2013/9/2 Roberto Spadim <roberto@spadim.com.br>

Hi Federico!

...

Ciao,

I wanted to add some info in the CREATE PROCEDURE page in the KB, but I

2013/9/2 Federico Razzoli <federico_raz@yahoo.it>: think that some info about CREATE PROCEDURE clauses miss both in the kb and in the MySQL manual.

...

* [NOT] DETERMINISTIC. From MySQL docs: "A routine is considered “deterministic” if it always produces the same

result for the same input parameters, and “not deterministic” otherwise."

...

What does this exactly mean for procedures? OUT and INOUT parameters? Resultsets returned by the procedure? Both?

i think it's something like: RANDON() = not deterministic, ROUND(1.5) = deterministic maybe it's important for replication (must be done via binary, instead SQL), and it's important for query cache (not deterministic can't be cached)

...

* NO SQL: As far as I understand, even SET is a SQL statement. I guess that a "no sql" function is something like CREATE FUNCTION x(n INT) RETURNS INT BEGIN RETURN n*2; END;

But what is a "no sql" procedure (if such a procedure even exists)?

i don't know, but reading mysql protocol, there's some functions for show fields, process info, process kill, http://dev.mysql.com/doc/internals/en/text-protocol.html

...

I'm sorry if this info is already written somewhere, but I really wasn't able to find it - and I feel that the KB should specify things like these.

Greetings Federico

_______________________________________________ Mailing list: https://launchpad.net/~maria-discuss Post to : maria-discuss@lists.launchpad.net Unsubscribe : https://launchpad.net/~maria-discuss More help : https://help.launchpad.net/ListHelp

-- Roberto Spadim SPAEmpresarial

-- Roberto Spadim SPAEmpresarial

Hi, Federico! On Sep 02, Federico Razzoli wrote:

Ciao,

I wanted to add some info in the CREATE PROCEDURE page in the KB, but I think that some info about CREATE PROCEDURE clauses miss both in the kb and in the MySQL manual.

* [NOT] DETERMINISTIC. From MySQL docs: "A routine is considered “deterministic” if it always produces the same result for the same input parameters, and “not deterministic” otherwise." What does this exactly mean for procedures? OUT and INOUT parameters? Resultsets returned by the procedure? Both?

It doesn't really matter. For stored functions, optimizer uses the value of [NOT] DETERMINISTIC to optimize function calls. But for procedures it's not used for anything, as far as I know.

* NO SQL: As far as I understand, even SET is a SQL statement. I guess that a "no sql" function is something like CREATE FUNCTION x(n INT) RETURNS INT BEGIN RETURN n*2; END;

Not quite. As far as I understand, "NO SQL" means that there's no SQL in the routine *at all*. Like,it might be written on C or ADA or whatever sql standard specifies: <language clause> ::= LANGUAGE <language name> <language name> ::= ADA | C | COBOL | FORTRAN | M | MUMPS | PASCAL | PLI | SQL It's particularly important if a routine has the EXTERNAL clause, instead of the BEGIN ... END body. But MariaDB does not support EXTERNAL or any other LANGUAGE besides SQL.

I'm sorry if this info is already written somewhere, but I really wasn't able to find it - and I feel that the KB should specify things like these.

It's only on the SQL-99 Complete, Really pages: https://mariadb.com/kb/en/create-procedurefunctionmethod-statement/#language... https://mariadb.com/kb/en/create-procedurefunctionmethod-statement/#sql-data... Regards, Sergei

Thank you! I wrote these info in the KB https://mariadb.com/kb/en/create-procedure/ https://mariadb.com/kb/en/create-function/ Federico -------------------------------------------- Mar 3/9/13, Sergei Golubchik <serg@mariadb.org> ha scritto: Oggetto: Re: [Maria-discuss] CREATE PROCEDURE clauses A: "Federico Razzoli" <federico_raz@yahoo.it> Cc: "Mailing-List mariadb" <maria-discuss@lists.launchpad.net> Data: Martedì 3 settembre 2013, 09:13 Hi, Federico! On Sep 02, Federico Razzoli wrote:

Ciao,

I wanted to add some info in the CREATE PROCEDURE page in the KB, but I think that some info about CREATE PROCEDURE clauses miss both in the kb and in the MySQL manual.

* [NOT] DETERMINISTIC. From MySQL docs: "A routine is considered “deterministic” if it always produces the same result for the same input parameters, and “not deterministic” otherwise." What does this exactly mean for procedures? OUT and INOUT parameters? Resultsets returned by the procedure? Both?

It doesn't really matter. For stored functions, optimizer uses the value of [NOT] DETERMINISTIC to optimize function calls. But for procedures it's not used for anything, as far as I know.

* NO SQL: As far as I understand, even SET is a SQL statement. I guess that a "no sql" function is something like CREATE FUNCTION x(n INT) RETURNS INT BEGIN RETURN n*2; END;

Not quite. As far as I understand, "NO SQL" means that there's no SQL in the routine *at all*. Like,it might be written on C or ADA or whatever sql standard specifies: <language clause> ::= LANGUAGE <language name> <language name> ::=     ADA   | C   | COBOL   | FORTRAN   | M | MUMPS   | PASCAL   | PLI   | SQL It's particularly important if a routine has the EXTERNAL clause, instead of the BEGIN ... END body. But MariaDB does not support EXTERNAL or any other LANGUAGE besides SQL.

I'm sorry if this info is already written somewhere, but I really wasn't able to find it - and I feel that the KB should specify things like these.

It's only on the SQL-99 Complete, Really pages: https://mariadb.com/kb/en/create-procedurefunctionmethod-statement/#language... https://mariadb.com/kb/en/create-procedurefunctionmethod-statement/#sql-data... Regards, Sergei