Re: [Maria-discuss] [Dbmail] MariaDB and dbmail
![](https://secure.gravatar.com/avatar/359652f5d5394fdc76fea714c0e40c97.jpg?s=120&d=mm&r=g)
Am 03.04.2013 11:09, schrieb Sergej Pupykin:
At Tue, 02 Apr 2013 19:47:15 +0400, Sergej Pupykin
wrote: Not sure if it caused by migration to mariadb, but problem appeared near same time.
Downgrading back from mariadb 5.5.30 to mysql 5.5.30 solves the problem. It looks like mariadb has ordering problems or it is not drop-in replacement of mysql
i am CC'ing this to the MariaDB list..........
but "however when I run it from command line it works well" speaks
against it - this is highly interesting since more and more
distributions are switching to MariaDB including Fedora and i am
at preparing the switch too with no problems until now
_____________________________
zamri
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 = l.part_id JOIN dbmail_physmessage ph ON 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.
![](https://secure.gravatar.com/avatar/bccaf81a23eecaecd9feaf01c7c42ff4.jpg?s=120&d=mm&r=g)
Hello, On Wed, Apr 03, 2013 at 12:15:53PM +0200, Reindl Harald wrote:
Am 03.04.2013 11:09, schrieb Sergej Pupykin:
At Tue, 02 Apr 2013 19:47:15 +0400, Sergej Pupykin
wrote: Not sure if it caused by migration to mariadb, but problem appeared near same time.
Downgrading back from mariadb 5.5.30 to mysql 5.5.30 solves the problem. It looks like mariadb has ordering problems or it is not drop-in replacement of mysql
i am CC'ing this to the MariaDB list..........
but "however when I run it from command line it works well" speaks against it - this is highly interesting since more and more distributions are switching to MariaDB including Fedora and i am at preparing the switch too with no problems until now _____________________________
zamri
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 = l.part_id JOIN dbmail_physmessage ph ON 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.
If something isn't working in MariaDB, we would like to fix it. I am not aware of any intended changes that would cause MariaDB to handle ORDER BY differently than MySQL does. Could you please tell, what exactly is wrong with the results you're getting? Is it the right data, but in the wrong order, or the data itself is also wrong? You mention
however when I run it from command line it works well.
How is it run not from commandline? (sorry if this question is trivial, but I'm not familiar with the dbmail codebase). What version of dbmail are you using? Is your table definition for table dbmail_partlists the same as in dbmail-3.0.2, file sql/mysql/create_tables.mysql ? Getting to know the above could help us to resolve the problem. BR Sergei -- Sergei Petrunia, Software Developer Monty Program AB, http://askmonty.org Blog: http://s.petrunia.net/blog
![](https://secure.gravatar.com/avatar/359652f5d5394fdc76fea714c0e40c97.jpg?s=120&d=mm&r=g)
Hi Am 03.04.2013 15:35, schrieb Sergei Petrunia:
Hello,
On Wed, Apr 03, 2013 at 12:15:53PM +0200, Reindl Harald wrote:
Am 03.04.2013 11:09, schrieb Sergej Pupykin:
At Tue, 02 Apr 2013 19:47:15 +0400, Sergej Pupykin
wrote: Not sure if it caused by migration to mariadb, but problem appeared near same time.
Downgrading back from mariadb 5.5.30 to mysql 5.5.30 solves the problem. It looks like mariadb has ordering problems or it is not drop-in replacement of mysql
i am CC'ing this to the MariaDB list..........
but "however when I run it from command line it works well" speaks against it - this is highly interesting since more and more distributions are switching to MariaDB including Fedora and i am at preparing the switch too with no problems until now _____________________________
zamri
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 = l.part_id JOIN dbmail_physmessage ph ON 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.
If something isn't working in MariaDB, we would like to fix it. I am not aware of any intended changes that would cause MariaDB to handle ORDER BY differently than MySQL does.
Could you please tell, what exactly is wrong with the results you're getting? Is it the right data, but in the wrong order, or the data itself is also wrong?
You mention
however when I run it from command line it works well.
How is it run not from commandline? (sorry if this question is trivial, but I'm not familiar with the dbmail codebase).
What version of dbmail are you using? Is your table definition for table dbmail_partlists the same as in dbmail-3.0.2, file sql/mysql/create_tables.mysql ?
Getting to know the above could help us to resolve the problem
thank you for your feedback i am not the original poster and because "suppress_duplicates" not sure if you are subscribed to the dbmail-list now and the message reached the list i am not the orginal poster but because i plan to migrate to MariaDB in a short and we are a heavy user of the last recent dbmail i liked to bring both communities together we personally use this dbmail snapshot which has some improvements comapred with the official 3.0.2 build dbmail-3.0.2-7.fc18.20130321.rh.22b964b653ba149b63b9b6787aeac7803c2121e5.x86_64 http://git.dbmail.eu/paul/dbmail hopefully if there are issues with dbmail and MariaDB they get caught thank you!
![](https://secure.gravatar.com/avatar/307208007599c3b8c18867fd8be632ac.jpg?s=120&d=mm&r=g)
At Wed, 3 Apr 2013 17:35:46 +0400,
Sergei Petrunia
Could you please tell, what exactly is wrong with the results you're getting? Is it the right data, but in the wrong order, or the data itself is also wrong?
Data are correct, but order is wrong. If I print key variable (see link to dbmail-message.c below) it prints 1,1,2,3,2,3,3 however from commandline order is good: SELECT l.part_key,l.part_depth,l.part_order,l.is_header,DATE_FORMAT(ph.internal_date, GET_FORMAT(DATETIME,'ISO')) FROM dbmail_mimeparts p JOIN dbmail_partlists l ON p.id = l.part_id JOIN dbmail_physmessage ph ON ph.id = l.physmessage_id WHERE l.physmessage_id = 4748475 ORDER BY l.part_key,l.part_order ASC; +----------+------------+------------+-----------+-----------------------------------------------------------+ | part_key | part_depth | part_order | is_header | DATE_FORMAT(ph.internal_date, GET_FORMAT(DATETIME,'ISO')) | +----------+------------+------------+-----------+-----------------------------------------------------------+ | 1 | 0 | 0 | 1 | 2013-04-02 12:49:41 | | 1 | 0 | 1 | 0 | 2013-04-02 12:49:41 | | 2 | 1 | 0 | 1 | 2013-04-02 12:49:41 | | 2 | 1 | 1 | 0 | 2013-04-02 12:49:41 | | 3 | 1 | 0 | 1 | 2013-04-02 12:49:41 | | 3 | 1 | 1 | 0 | 2013-04-02 12:49:41 | | 3 | 0 | 3 | 0 | 2013-04-02 12:49:41 | +----------+------------+------------+-----------+-----------------------------------------------------------+
You mention
however when I run it from command line it works well.
How is it run not from commandline? (sorry if this question is trivial, but I'm not familiar with the dbmail codebase).
See file: http://git.dbmail.eu/paul/dbmail/tree/src/dbmail-message.c?id=4333e2d94dc03b... function: static DbmailMessage * _mime_retrieve(DbmailMessage *self) You may also want to look into libzdb sources, but they look like just wrappers to provide unified API. http://www.tildeslash.com/libzdb/
What version of dbmail are you using? Is your table definition for table dbmail_partlists the same as in dbmail-3.0.2, file sql/mysql/create_tables.mysql ?
I use dbmail-3.0.2. Database was created by older version many years ago, but mysqldump shows current table description: CREATE TABLE `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; /*!40101 SET character_set_client = @saved_cs_client */;
participants (3)
-
Reindl Harald
-
Sergei Petrunia
-
Sergej Pupykin