MariaDB - CONNECT ENGINE - ORDER BY error

Question

I'm trying to get Asterisk CDR records from MySQL table (5.5.45) by CONNECT engine on other server running MariaDB (10.0.29).

I can create the connection between table easily:

CREATE TABLE `calls` engine=CONNECT table_type=MYSQL
CONNECTION='mysql://user@IP/asteriskcdrdb/calls';

When I run simple SELECT * FROM calls, everything works good, when I add some WHERE conditions, still everything okay.

But the problem start when I add ORDER BY column parameter, then I got this error from MariaDB:

#1032 - Can't find record in 'calls'

I checked MySQL log, MariaDB log - there are no errors at all.

Did I miss something?

Thank you!

Update: The whole query is simple:

SELECT * FROM `calls` ORDER BY `calldate`

The table structure:

CREATE TABLE `calls` (
  `calldate` datetime NOT NULL default '0000-00-00 00:00:00',
  `clid` varchar(80) NOT NULL default '',
  `src` varchar(80) NOT NULL default '',
  `dst` varchar(80) NOT NULL default '',
  `dcontext` varchar(80) NOT NULL default '',
  `channel` varchar(80) NOT NULL default '',
  `dstchannel` varchar(80) NOT NULL default '',
  `lastapp` varchar(80) NOT NULL default '',
  `lastdata` varchar(80) NOT NULL default '',
  `duration` int(11) NOT NULL default '0',
  `billsec` int(11) NOT NULL default '0',
  `disposition` varchar(45) NOT NULL default '',
  `amaflags` int(11) NOT NULL default '0',
  `accountcode` varchar(20) NOT NULL default '',
  `uniqueid` varchar(32) NOT NULL default '',
  `userfield` varchar(255) NOT NULL default '',
  `recordingfile` varchar(255) NOT NULL default '',
  `cnum` varchar(40) NOT NULL default '',
  `cnam` varchar(40) NOT NULL default '',
  `outbound_cnum` varchar(40) NOT NULL default '',
  `outbound_cnam` varchar(40) NOT NULL default '',
  `dst_cnam` varchar(40) NOT NULL default '',
  `call_charge` float NOT NULL default '0',
  `from_did` varchar(30) NOT NULL,
  `did` varchar(50) NOT NULL default '',
  `user_id` int(8) unsigned default NULL,
  `client_id` int(8) unsigned default NULL,
  KEY `IDX_UNIQUEID` (`uniqueid`),
  KEY `src` (`src`),
  KEY `dst` (`dst`),
  KEY `calldate` (`calldate`),
  KEY `uniqueid` (`uniqueid`),
  KEY `userfield` (`userfield`),
  KEY `from_did` (`from_did`),
  KEY `user_id` (`user_id`),
  KEY `client_id` (`client_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

Update #2: Update the table names, to don't confuse, but it's not the issue. The CONNECTION table is created okay.

Query works:

SELECT * FROM `calls`

Query works:

SELECT * FROM `calls` WHERE `user_id`=X

Query return error:

SELECT * FROM `calls` ORDER BY `calldate`

Update #3: The MySQL was updated to veriosn 5.5.45, the type was changed to InnoDB and the charset was converted to UTF8. But no success.

PROBLEM SOLVED Well, it's MariaDB bug, when I changed to FederatedX engine (which is basically little bit limited version of CONNECT), everything works as expected.

Have you tried with mariadb-mariadb(same version)? can be version issue on index search. — arheops, Feb 17 '17 at 20:00
@arheops No, I can't install MariaDB on the Asterisk server. At least the MySQL was updated to 5.5, but still no luck. — Jan Pavlik, Feb 17 '17 at 22:54
install mariadb in chroot and test. at least you will know if you have issue or not. — arheops, Feb 18 '17 at 03:13

score 1 · Answer 1 · answered Feb 17 '17 at 18:20

1

In your query you do SELECT * FROM calls

but in your table structure you have CREATE TABLE cdr

and both have calldate column. Check if you querying the right table.

answered Feb 17 '17 at 18:20

Joseph

789
1
9
23

It's the table on Asterisk server, but when I'm using CONNECT, in the definitions is `CREATE TABLE calls`... from connection string `mysql://user@IP/asteriskcdrdb/cdr` – Jan Pavlik Feb 17 '17 at 18:22
If cdr is a database, do SELECT * FROM cdr.calls ORDER BY `calldate` – Joseph Feb 17 '17 at 18:26
cdr is table on other server. CONNECT engine is like remote view. So you create table `calls` which works, until ORDER BY is added. – Jan Pavlik Feb 17 '17 at 18:30
Try this SELECT * FROM calls ORDER BY calldate without the quotes in calls and calldate. – Joseph Feb 17 '17 at 19:15
The other thing you could do is issue this SQL command flush privileges; then restart your MySQL service and try that query again. It should work. – Joseph Feb 17 '17 at 19:19
There are no issues with privileges, anyway I did restart MySQL couple times. The problem persist. – Jan Pavlik Feb 17 '17 at 19:26
How about you try this SELECT * FROM `calls` ORDER BY `calldate` LIMIT 18446744073709551615 – Joseph Feb 17 '17 at 19:34

MariaDB - CONNECT ENGINE - ORDER BY error

1 Answers1