MariaDB VIEW

[ArturFormella]

Hello! I have the following problem with mysql fdw and views.

MariaDB server:

CREATE TABLE `admin_alerts` (
  `id` int(11) NOT NULL
) ENGINE=MyISAM;
create view test_fdw_view as SELECT id FROM `admin_alerts` 

Postgresql: IMPORT FOREIGN SCHEMA main LIMIT TO (admin_alerts,test_fdw_view) FROM SERVER mysql_db4_server INTO mysql_realtime;

And then: SELECT * FROM test_fdw_view returns:

SQL error: ERROR: 8 failed to prepare the MySQL query: Remote Error: Prepared statement needs to be re-prepared Query: SELECT NULL FROM main.test_fdw_view In statement: SELECT COUNT() AS total FROM (SELECT FROM "mysql_realtime"."test_fdw_view") AS sub

but SELECT * FROM admin_alerts works as expected.

The message comes from here (mysqlBeginForeignScan):

            case CR_UNKNOWN_ERROR:
            default:
            {
                char *err = pstrdup(_mysql_error(festate->conn));
                ereport(ERROR,
                            (errcode(ERRCODE_FDW_UNABLE_TO_CREATE_EXECUTION),
                            errmsg(" 8 failed to prepare the MySQL query:\nRemote Error: %s\nQuery: %s", err, festate->query)));
            }
            break;

This issue is also complicated by the fact that it sometimes works as expected. I don't know how to fix it. Could someone help?

[ArturFormella]

This seems to be connected: https://laracasts.com/discuss/channels/laravel/general-error-1615-prepared-statement-needs-to-be-re-prepared-selecting-mysql-view?page=1 https://laracasts.com/discuss/channels/general-discussion/mysql-general-error-1615-prepared-statement-needs-to-be-re-prepared?page=1

so, how to disable prepared statements in mysql fdw?

[johnramsden]

@ArturFormella did you ever find a solution to this issue? It is also occurring for me using MariaDB. Interestingly it wasn't occurring with MariaDB 5.5, but it is with MariaDB 10.3.

[ArturFormella]

Yes, I had to change default settings to fix it:

table_definition_cache 16384 table_open_cache 16384