Unknown database error when trying to activate Booking Calendar Extension

[hahnn]

I tried to activate Booking Calendar Extension after installing it.

The extension claimed it cannot be enabled because of a FATAl ERROR.

Unfortunately, there is absolutely nothing about that in pg4wp_errors.log file.

Then i took a look at my Apache HTTPD error log file and here is what I found about this issue:

[Sun Mar 10 16:36:16.018762 2024] [php:error] [pid 19935] [client XX.XX.XX.XX:50944] PHP Fatal error:  Uncaught Exception: Invalid or unsupported SQL statement. in wp-content/pg4wp/driver_pgsql_rewrite.php:26\n
Stack trace:\n
#0 wp-content/pg4wp/driver_pgsql_rewrite.php(40): createSQLRewriter()\n
#1 wp-content/pg4wp/driver_pgsql.php(523): pg4wp_rewrite()\n
#2 wp-content/pg4wp/core.php(34) : eval()'d code(2349): wpsqli_query()\n
#3 wp-content/pg4wp/core.php(34) : eval()'d code(2263): wpdb2->_do_query()\n
#4 wp-content/pg4wp/core.php(34) : eval()'d code(3146): wpdb2->query()\n
#5 wp-content/plugins/booking/core/wpbc_functions.php(2960): wpdb2->get_results()\n
#6 wp-content/plugins/booking/core/wpbc-activation.php(660): wpbc_is_field_in_table_exists()\n
#7 wp-content/plugins/booking/core/wpbc-core.php(97): wpbc_booking_activate()\n
#8 wp-content/plugins/booking/core/any/activation.php(263): make_bk_action()\n
#9 wp-content/plugins/booking/core/any/activation.php(236): WPBC_Install->wpbc_activate()\n
#10 wp-includes/class-wp-hook.php(324): WPBC_Install->wpbc_activate_initial()\n
#11 wp-includes/class-wp-hook.php(348): WP_Hook->apply_filters()\n
#12 wp-includes/plugin.php(517): WP_Hook->do_action()\n
#13 wp-admin/includes/plugin.php(693): do_action()\n
#14 wp-admin/plugins.php(58): activate_plugin()\n
#15 {main}\n  thrown in wp-content/pg4wp/driver_pgsql_rewrite.php on line 26, referer: https://www.XXXXXXXXXXXX.XX/wp-admin/plugins.php?plugin=booking%2Fwpdev-booking.php

[mattbucci]

We should update https://github.com/PostgreSQL-For-Wordpress/postgresql-for-wordpress/blob/c7e3fbd50bb5363c84b39a611f65b9c699c7f63f/pg4wp/driver_pgsql_rewrite.php#L26 to include the SQL statement in the exception

if you could add var_dump($sql); die(); before the exception you can see the query. It looks like the query should be contained in the function wpbc_is_field_in_table_exists

This error basically means the query isn't one of the following SELECT|INSERT|REPLACE INTO|UPDATE|DELETE|DESCRIBE|ALTER TABLE|CREATE TABLE|DROP TABLE|SHOW INDEX|SHOW VARIABLES|SHOW TABLES|OPTIMIZE TABLE|SET NAMES|SHOW FULL COLUMNS|SHOW TABLE STATUS

[mattbucci]

Here's the function in question from this plugin. I think the issue is we have not implemented a rewriter for SHOW COLUMNS FROM $table

This does not exist in Postgres

Expected format for return is

Field
Type
Null (is nullable)
Key (for some reason only PRI for Primary)
Default
Extra

        /**
         * Check if table exist
         *
         * @global  $wpdb
         * @param string $tablename
         * @param  $fieldname
         * @return 0|1
         */
        function wpbc_is_field_in_table_exists( $tablename , $fieldname) {

            global $wpdb;

            if (
                   ( ( ! empty( $wpdb->prefix ) ) && ( strpos( $tablename, $wpdb->prefix ) === false ) )
                || ( '_' == $wpdb->prefix )                                                                                 //FixIn: 8.7.3.16
            ) {
                $tablename = $wpdb->prefix . $tablename;
            }

            if ( 0 ) {

                $sql_check_table = "SELECT * FROM INFORMATION_SCHEMA.COLUMNS where TABLE_NAME='{$tablename}' AND TABLE_SCHEMA='{$wpdb->dbname}' ";

                $res = $wpdb->get_results( $sql_check_table );

                foreach ( $res as $fld ) {
                    if ( $fieldname === $fld->COLUMN_NAME ) {
                        return 1;
                    }
                }

            } else {

                $sql_check_table = "SHOW COLUMNS FROM {$tablename}";

                $res = $wpdb->get_results( $sql_check_table );

                foreach ( $res as $fld ) {
                    if ( $fld->Field == $fieldname ) {
                        return 1;
                    }
                }
            }

            return 0;
        }

[mattbucci]

This is nearly the same as https://github.com/PostgreSQL-For-Wordpress/postgresql-for-wordpress/blob/v3/pg4wp/rewriters/ShowFullColumnsSQLRewriter.php

Per https://dev.mysql.com/doc/refman/8.0/en/show-columns.html

The optional EXTENDED keyword causes the output to include information about hidden columns that MySQL uses internally and are not accessible by users.

The optional FULL keyword causes the output to include the column collation and comments, as well as the privileges you have for each column.