btrim does not exist

[studdugie]

I have a MySQL/PHP app that I want to port to PostgreSQL so I just installed the mysql_fdw from https://github.com/EnterpriseDB/mysql_fdw because I'd like to do the data migration in SQL if possible.

Installation and set up worked flawlessly but when I run the following query

SELECT
  title,
  description,
  '[' || starts || ', ' || COALESCE(ends, 'infinity') || ']'
FROM
  _filler
WHERE
  starts IS NOT NULL
  AND description IS NOT NULL
  AND LENGTH(TRIM(title)) > 0
  AND LENGTH(TRIM(description)) > 0;

​I get the following error:​
​ ​

​ERROR: failed to prepare the MySQL query:
FUNCTION latest.btrim does not exist

FYI: Tables names that start w/ _ are the MySQL versions of PostgreSQL tables.​

My PostgreSQL version is 9.5beta1 on x86_64-pc-linux-gnu, compiled by gcc (Ubuntu 4.9.2-10ubuntu13) 4.9.2, 64-bit.

My MySQL version is 5.6.27-0ubuntu1.

[ahsanhadi]

Can you please share the table ddl to reproduce the issue.

On Thu, Oct 29, 2015 at 7:31 PM, Dane notifications@github.com wrote:

I have a MySQL/PHP app that I want to port to PostgreSQL so I just
installed the mysql_fdw from https://github.com/EnterpriseDB/mysql_fdw
because I'd like to do the data migration in SQL if possible.

Installation and set up worked flawlessly but when I run the following
query
SELECT
title,
description,
'[' || starts || ', ' || COALESCE(ends, 'infinity') || ']'
FROM
_filler
WHERE
starts IS NOT NULL
AND description IS NOT NULL
AND LENGTH(TRIM(title)) > 0
AND LENGTH(TRIM(description)) > 0;

​I get the following error:​
​ ​

​ERROR: failed to prepare the MySQL query:
FUNCTION latest.btrim does not exist

FYI: Tables names that start w/ _ are the MySQL versions of PostgreSQL
tables.​

—
Reply to this email directly or view it on GitHub
#70.

Ahsan Hadi
Snr Director Product Development
EnterpriseDB Corporation
The Enterprise Postgres Company

Phone: +92-51-8358874
Mobile: +92-333-5162114

Website: www.enterprisedb.com
EnterpriseDB Blog: http://blogs.enterprisedb.com/
Follow us on Twitter: http://www.twitter.com/enterprisedb

This e-mail message (and any attachment) is intended for the use of the
individual or entity to whom it is addressed. This message contains
information from EnterpriseDB Corporation that may be privileged,
confidential, or exempt from disclosure under applicable law. If you are
not the intended recipient or authorized to receive this for the intended
recipient, any use, dissemination, distribution, retention, archiving, or
copying of this communication is strictly prohibited. If you have received
this e-mail in error, please notify the sender immediately by reply e-mail
and delete this message.

[studdugie]

I don't know if this makes a difference because I expect you to test/verify this on MySQL 5.6.x but the storage engine is INNODB.

CREATE TABLE filler(
  id INT UNSIGNED NOT NULL PRIMARY KEY AUTO_INCREMENT,
  title VARCHAR(140) NOT NULL,
  description TEXT,
  starts DATETIME,
  ends DATETIME,
  image VARCHAR(255),
  INDEX (starts)
)DEFAULT CHARACTER SET UTF8;

The DDL for the foreign table defined on the PostgreSQL side is:

CREATE FOREIGN TABLE _filler(
  id INT,
  title VARCHAR(140),
  description TEXT,
  starts TIMESTAMPTZ,
  ends TIMESTAMPTZ,
  image VARCHAR(255)
) SERVER mysql_server OPTIONS (dbname '{$dbname}', table_name 'filler');

[studdugie]

Hi,
Just wondering if anyone is working on this. I'm not asking for an ETA or anything like that. Just to know if it's on anyone's todo. If so I'll continue w/ the expectation that it will be fixed otherwise I'll need to work around the issue by copying all the tables as temporary tables to PostgreSQL. So if someone could let me know which way to go I would appreciate it.

[ibrarahmad]

I will look at this issue this week.

On Mon, Nov 2, 2015 at 9:00 AM, Dane notifications@github.com wrote:

Hi,
Just wondering if anyone is working on this. If so I'll continue w/ the
expectation that it will be fixed otherwise I'll need to work around the
issue by copying all the tables as temporary tables to PostgreSQL. So if
someone could let me know which way to go I would appreciate it.

—
Reply to this email directly or view it on GitHub
#70 (comment)
.

Ibrar Ahmed
EnterpriseDB http://www.enterprisedb.com

[studdugie]

Thanks!

[studdugie]

Thanks for fixing this ibrarahmad! You've added a notch to my happy meter.