search

EnterpriseDB / mysql_fdw

PostgreSQL foreign data wrapper for MySQL
Other
531 stars 162 forks source link

Postgres-9.4 mysql_fdw write issues to MariaDB-5.5 #68

Open VKen opened 8 years ago

VKen commented 8 years ago

Hi all,

I chanced upon this great extension and it is excellent for reading mysql and mariadb database.

However, there seems to be an issue with the mysql database write functions, even though the current master branch (d9836a345d820ce419063bf46c0ed744e5e3ba87) says it has included write capability.

SELECT statements are fine.

INSERT statement has weird behaviour:

DELETE statement returns error from postgres:

ERROR:  cache lookup failed for type 0
********** Error **********
ERROR: cache lookup failed for type 0
SQL state: XX000

My system is running ubuntu 14.04 and the db servers are as below.

Connection to foreign mysql server is root with superuser privileges so there should not be privilege issues and user mapping issues.

I'm not too sure how to debug this for the fdw writing to MariaDB database. (Encoding? Server Version?)

Any direction or help is much appreciated.

-- MariaDB database
CREATE DATABASE `test` /*!40100 DEFAULT CHARACTER SET utf8 COLLATE utf8_unicode_ci */

-- MariaDB table
CREATE TABLE `test_table` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `col1` varchar(45) COLLATE utf8_unicode_ci DEFAULT NULL COMMENT 'test',
  `bool` tinyint(1) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=16 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

-- postgres db
CREATE DATABASE testdb
WITH OWNER = postgres
ENCODING = 'UTF8'
TABLESPACE = pg_default
LC_COLLATE = 'en_US.UTF-8'
LC_CTYPE = 'en_US.UTF-8'
CONNECTION LIMIT = -1;

-- postgres foreign table
CREATE FOREIGN TABLE remote_mysql_test.test_table
(
    id integer NOT NULL,
    col1 character varying(45),
    bool boolean
)
SERVER mysqlserver
OPTIONS (dbname 'test', table_name 'test_table');
ALTER FOREIGN TABLE remote_mysql_test.test_table
OWNER TO postgres;
ibrarahmad commented 8 years ago

Everything works fine on my computer with MySQL server. Ahsan can you please try that with MariaDB.

testdb=# \d test_table Foreign table "public.test_table" Column | Type | Modifiers | FDW Options --------+-----------------------+-----------+------------- id | integer | not null | col1 | character varying(45) | | bool | boolean | | Server: mysql_svr FDW Options: (dbname 'test', table_name 'test_table')

testdb=# insert into test_table values(1, 'Ibrar', false); INSERT 0 1 testdb=# select * from test_table; id | col1 | bool ----+-------+------ 1 | Ibrar | f (1 row)

testdb=# insert into test_table values(2, 'Ahsan', true); INSERT 0 1 testdb=# select * from test_table; id | col1 | bool ----+-------+------ 1 | Ibrar | f 2 | Ahsan | t (2 rows)

On Thu, Oct 8, 2015 at 3:21 PM, VKen notifications@github.com wrote:

Hi all,

I chanced upon this great extension and it is excellent for reading mysql and mariadb database.

However, there seems to be an issue with the mysql database write functions, even though the current master branch (d9836a3 https://github.com/EnterpriseDB/mysql_fdw/commit/d9836a345d820ce419063bf46c0ed744e5e3ba87) says it has included write capability.

SELECT statements are fine.

INSERT statement has weird behaviour:

  • the autoincrement id gets updated, while all other columns becomes '0' or 0 Example test schema data is below.

DELETE statement returns error from postgres:

ERROR: cache lookup failed for type 0 \ Error ** ERROR: cache lookup failed for type 0 SQL state: XX000

My system is running ubuntu 14.04 and the db servers are as below.

  • postgresql-9.4/trusty-pgdg,now 9.4.4-1.pgdg14.04+1 amd64
  • 5.5.44-MariaDB-1ubuntu0.14.04.1 (Ubuntu)

Connection to foreign mysql server is root with superuser privileges so there should not be privilege issues and user mapping issues.

I'm not too sure how to debug this for the fdw writing to MariaDB database. (Encoding? Server Version?)

Any direction or help is much appreciated.

-- MariaDB databaseCREATE DATABASE test /!40100 DEFAULT CHARACTER SET utf8 COLLATE utf8_unicode_ci / -- MariaDB tableCREATE TABLE test_table ( id int(11) NOT NULL AUTO_INCREMENT, col1 varchar(45) COLLATE utf8_unicode_ci DEFAULT NULL COMMENT 'test', bool tinyint(1) DEFAULT NULL, PRIMARY KEY (id) ) ENGINE=InnoDB AUTO_INCREMENT=16 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci; -- postgres dbCREATE DATABASE testdb WITH OWNER = postgres ENCODING = 'UTF8' TABLESPACE = pg_default LC_COLLATE = 'en_US.UTF-8' LC_CTYPE = 'en_US.UTF-8' CONNECTION LIMIT = -1; -- postgres foreign table CREATE FOREIGN TABLE remote_mysql_test.test_table ( id integer NOT NULL, col1 character varying(45), bool boolean ) SERVER mysqlserver OPTIONS (dbname 'test', table_name 'test_table'); ALTER FOREIGN TABLE remote_mysql_test.test_table OWNER TO postgres;

— Reply to this email directly or view it on GitHub https://github.com/EnterpriseDB/mysql_fdw/issues/68.

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