Multiple database issues

Hi,

we just tried to install and use QSM and we started with the free version.

There are probably multiple issues to be addressed regarding the database handling. Looking at this support forum, I see that database issues pop up frequently.

These are my findings while dealing with a couple of error messages:

• Trying to do DB schema changes on EVERY (admin) request is inefficient

I stumbled upon hundreds of error messages like the following:

[Wed Oct 25 14:45:34.496429 2023] [php:notice] [pid 6363] [client .....]  WordPress database error Table 'xxx-wp-prod.wp_mlw_results' doesn't exist for query ALTER TABLE wp_mlw_results ADD page_name varchar(255) NOT NULL made by do_action('admin_init'), WP_Hook->do_action, WP_Hook->apply_filters, qsm_add_author_column_in_db, referer: https://www....

So here we have a ALTER TABLE query being executed on an admin_init hook ( = on every request of any admin page), instead of upon install or when changing settings. Are you using more admin action of this type? If so, I would review that …
But wait – why is the query failing? Obviously a table is missing, the error message reports …

• Not catching/reporting install errors

When installing via GUI/admin pages, installation and activation does not report an error. If installing with wpcli, however, we see the reason for the failing queries / missing table:

wp plugin activate quiz-master-next
[25-Oct-2023 13:13:24 UTC] WordPress database error Specified key was too long; max key length is 1000 bytes for query CREATE TABLE wp_mlw_results (
                        result_id mediumint(9) NOT NULL AUTO_INCREMENT,
                        quiz_id INT NOT NULL,
                        quiz_name TEXT NOT NULL,
                        quiz_system INT NOT NULL,
                        point_score FLOAT NOT NULL,
                        correct_score INT NOT NULL,
                        correct INT NOT NULL,
                        total INT NOT NULL,
                        name TEXT NOT NULL,
                        business TEXT NOT NULL,
                        email TEXT NOT NULL,
                        phone TEXT NOT NULL,
                        user INT NOT NULL,
                        user_ip TEXT NOT NULL,
                        time_taken TEXT NOT NULL,
                        time_taken_real DATETIME NOT NULL,
                        quiz_results MEDIUMTEXT NOT NULL,
                        deleted INT NOT NULL,
            unique_id varchar(255) NOT NULL,
            form_type INT NOT NULL,
                        UNIQUE (unique_id),
                        PRIMARY KEY  (result_id)
                ) DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_520_ci made by include('phar:///usr/local/bin/wp/php/boot-phar.php'), include('phar:///usr/local/bin/wp/vendor/wp-cli/wp-cli/php/wp-cli.php'), WP_CLI\bootstrap, WP_CLI\Bootstrap\LaunchRunner->process, WP_CLI\Runner->start, WP_CLI\Runner->run_command_and_exit, WP_CLI\Runner->run_command, WP_CLI\Dispatcher\Subcommand->invoke, call_user_func, WP_CLI\Dispatcher\CommandFactory::WP_CLI\Dispatcher\{closure}, call_user_func, Plugin_Command->activate, activate_plugin, do_action('activate_quiz-master-next/mlw_quizmaster2.php'), WP_Hook->do_action, WP_Hook->apply_filters, QSM_Install::install, dbDelta
Plugin 'quiz-master-next' activated.

This is due to the uniqe_id being 255 Chars long, which is (because of unicode) leading to large index key size. So first of all, this error (actually all errors) should be reported to the site owner if using the GUI for installation, and also the DB usage should be a bit more portable. There might be many ways to do that, using a field length under 190 for the uniqe key would be one of it and avoids the key error.

For our site, I was forced to manually add the missing table to the database with a shorter key ( unique_id being set to 150 chars ) – will that work?

However, I would still suggest to review and improve the DB handling to avoid unnecessary resource consumption.

REgards,

Ulrich

• This topic was modified 1 year ago by ulicgn.