Undefined offset problem
-
We have a lot of notices like this:
Undefined offset: 3 in […]wordpress/wp-content/plugins/wordpress-seo/inc/sitemaps/class-post-type-sitemap-provider.php on line 143
It seems that the problem is related to MySQL 8 and this query:
$sql = " SELECT post_modified_gmt FROM ( SELECT @rownum:=0 ) init JOIN {$wpdb->posts} USE INDEX( type_status_date ) WHERE post_status IN ('" . implode( "','", $post_statuses ) . "') AND post_type = %s AND ( @rownum:=@rownum+1 ) %% %d = 0 ORDER BY post_modified_gmt ASC ";MySQL is dropping a warning about it:
Warning: #1287 Setting user variables within expressions is deprecated and will be removed in a future release. Consider alternatives: ‘SET variable=expression, …’, or ‘SELECT expression(s) INTO variables(s)’.This query does not work on MySQL 8 and should be probably be rewritten to something like this:
SET @row_number = 0; SELECT post_modified_gmt FROM wp_2_posts WHERE post_status IN ('publish') AND post_type = 'page' AND ( @row_number:=@row_number+1) % 1000 = 0 ORDER BY post_modified_gmt ASCOr consider rewriting it to using ROW_NUMBER() function on MySQL 8.
The result of this error is lack of last modified date next to the sitemap file, which negatively affects SEO, so this bug should be considered important. See screenshot here:
https://ibb.co/VSTybRL
-
Here is what happens when running the query in database:
mysql> SELECT post_modified_gmt FROM ( SELECT @rownum:=0 ) init JOIN wp_2_posts USE INDEX( type_status_date ) WHERE post_status IN ('publish') AND post_type = 'page' AND ( @rownum:=@rownum+1 ) % 1000 = 0 ORDER BY post_modified_gmt ASC; Empty set, 2 warnings (0.00 sec) mysql> show warnings; +---------+------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | Level | Code | Message | +---------+------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | Warning | 1287 | Setting user variables within expressions is deprecated and will be removed in a future release. Consider alternatives: 'SET variable=expression, ...', or 'SELECT expression(s) INTO variables(s)'. | | Warning | 1287 | Setting user variables within expressions is deprecated and will be removed in a future release. Consider alternatives: 'SET variable=expression, ...', or 'SELECT expression(s) INTO variables(s)'. | +---------+------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ 2 rows in set (0.00 sec)And here is the result of modified query with deleted @rownum variables:
SELECT post_modified_gmt FROM wp_2_posts WHERE post_status IN ('publish') AND post_type = 'page' ORDER BY post_modified_gmt ASC;The data seems to be ok.
Unfortunately, that one website uses the Google XML Sitemap plugin for XML Sitemaps.
Anyway, the problem is present on multiple of our network websites and probably shows up when there are more than 1000 (or 2000) pages defined in WordPress. I also see the code in YoastSEO Pro for the sitemap generation is the same.
Regarding the missing dates – we do not have missing dates in particular sitemaps – we have missing dates in the sitemap index (list of sitemaps). And this is not related to custom types.
- The topic ‘Undefined offset problem’ is closed to new replies.
