Documentation Troubleshooting Resolving WP Statistics SQL Errors

Resolving WP Statistics SQL Errors

Some WP Statistics users face SQL errors such as:

WordPress database error Unknown column 'page_id' in 'field list' for query SELECT `page_id` FROM `wp_statistics_pages` WHERE `date` = '2021-06-30' AND `type` = 'loginpage' AND `id` = 0 made by require('wp-load.php'), require_once('wp-config.php'), require_once('wp-settings.php'), do_action('init'), WP_Hook->do_action, WP_Hook->apply_filters, WP_STATISTICS\Hits::record_login_page_hits, WP_STATISTICS\Hits::record, WP_STATISTICS\Pages::record
WordPress database error Unknown column 'type' in 'field list' for query INSERT IGNORE INTO `wp_statistics_pages` (`uri`, `date`, `count`, `id`, `type`) VALUES ('', '2021-06-30', 1, '0', 'loginpage') made by require('wp-load.php'), require_once('wp-config.php'), require_once('wp-settings.php'), do_action('init'), WP_Hook->do_action, WP_Hook->apply_filters, WP_STATISTICS\Hits::record_login_page_hits, WP_STATISTICS\Hits::record, WP_STATISTICS\Pages::record, WP_STATISTICS\Pages::save_page
WordPress database error Unknown column 'user_id' in 'field list' for query INSERT INTO `wp_statistics_useronline` (`ip`, `timestamp`, `created`, `date`, `referred`, `agent`, `platform`, `version`, `location`, `user_id`, `page_id`, `type`) VALUES ('202.172.26.22', '1625056348', '1625056348', '2021-06-30 12:32:28', 'https://www.YOURWEBSITE.com', 'Chrome', 'Windows', '10.0', 'JP', 0, '0', 'loginpage') made by require('wp-load.php'), require_once('wp-config.php'), require_once('wp-settings.php'), do_action('init'), WP_Hook->do_action, WP_Hook->apply_filters, WP_STATISTICS\Hits::record_login_page_hits, WP_STATISTICS\Hits::record, WP_STATISTICS\UserOnline::record, WP_STATISTICS\UserOnline::add_user_online

Solution

You need to make sure the pages table has the below columns:

+---------+--------------+------+-----+---------+----------------+
| Field   | Type         | Null | Key | Default | Extra          |
+---------+--------------+------+-----+---------+----------------+
| page_id | bigint(20)   | NO   | PRI | NULL    | auto_increment |
| uri     | varchar(255) | NO   | MUL | NULL    |                |
| type    | varchar(255) | NO   |     | NULL    |                |
| date    | date         | NO   | MUL | NULL    |                |
| count   | int(11)      | NO   |     | NULL    |                |
| id      | int(11)      | NO   | MUL | NULL    |                |
+---------+--------------+------+-----+---------+----------------+