[code]
[25-Sep-2018 14:18:52 UTC] WordPress database error Table 'yotlix.mw_625_posts' doesn't exist for query SELECT DISTINCT mw_users.ID FROM mw_users LEFT JOIN mw_usermeta ON ( mw_users.ID = mw_usermeta.user_id ) LEFT JOIN mw_usermeta AS mt1 ON ( mw_users.ID = mt1.user_id ) LEFT JOIN mw_usermeta AS mt2 ON ( mw_users.ID = mt2.user_id ) LEFT JOIN mw_usermeta AS mt3 ON (mw_users.ID = mt3.user_id AND mt3.meta_key = 'wpseo_noindex_author' ) LEFT JOIN mw_usermeta AS mt4 ON ( mw_users.ID = mt4.user_id ) WHERE 1=1 AND mw_users.ID<>1 AND mw_users.ID IN ( SELECT DISTINCT mw_625_posts.post_author FROM mw_625_posts WHERE mw_625_posts.post_status = 'publish' AND mw_625_posts.post_type IN ( 'post', 'page', 'attachment', 'mw-portfolio' ) ) AND (
(
(
mw_usermeta.meta_key = '_yoast_wpseo_profile_updated'
AND
(
( mt1.meta_key = 'mw_625_user_level' AND mt1.meta_value != '0' )
AND
(
( mt2.meta_key = 'wpseo_noindex_author' AND mt2.meta_value != 'on' )
OR
mt3.user_id IS NULL
)
)
)
AND
(
mt4.meta_key = 'mw_625_capabilities'
)
)
) ORDER BY mw_usermeta.meta_value+0 DESC
[/code]
I have the agreement of 2nd tier to continue with this ticket here.
With regards to the translator not being able to see the jobs assigned to them:
1.Is this for new products? or only for old products?
2.Which user?
3.Can you provide the user credentials to be able to see their translation dashboard? (I am opening the private area for this)
4. Please provide steps to replicate this on your system (be detailed).
Hello, I am George from second tier support.
I am investigating the problem with our developers.
They have asked me if you can provide the whole stack trace of that database error.
I reproduced the error on your site, and I see that the problem is that it seeks for the users table in the current database, however, because your database is fragmented between multisites, the users table is actually in your main database, and thus an error happens.
So we need one of the two options here:
- Full stack trace dump of the database errror - I tried to get that, but nothing was logged in the debug log of your site, after I enabled debugging - so perhaps you can help me there?
OR
- A full copy of your site OR a staging environment where we can do all sort of tests and modifications freely - that might be destructive to data.
Currently, based on the data that we have of the error and of the structure of your site, this looks to us like a conflict with your custom code - that fragments the multisite database into multiple databases.
Hello,
thanks for debugging this. Due to the large size of the multisite install there sadly is no easy way to gice you the full copy or create a one-to-one staging environment.
Please, provide me your IP address, I'll add it to the list of allowed IP's to access the log and will provide you the information how to get to the error log.
I use HyperDB in a pretty simple scenario, just to split the site's dbs after each 100 sites. Here's the function that takes care of that:
// Multisite blog tables are "{$base_prefix}{$blog_id}_*"
if ( preg_match("/^{$wpdb->base_prefix}(\d+)_/i", $wpdb->table,$matches) && strpos($wpdb->table, '_slim_') === false ) {
if (isset($matches[1])) {
$db_id = intval($matches[1] / 100);
if ($db_id) {
return 'yotlix_' . $db_id;
}
}
}
Let me know if you need anything else, I'll be happy to provide you more details.
Yes, I noticed that code, and that is exactly the source of the problem.
Your main database is X, but the subsite database is X_Number_
So we search because it is a multisite in the current database, but in the current database, the table "users" is not present - and because of that - there is no results to be displayed in the translation queue.
This is not a standard setup and not according to the WordPress and HyperDB usage standards.
My IP address is dynamic, so perhaps it would be better if you can just provide the logs after reproducing the error - e.g. visiting the translation queue with the translation user.
Another suggestion for your code, perhaps you can detect when the users tables are being accessed, and rewrite the query to suit your needs as you already do for other requests - as I told you in my previous reply - this is a custom coding scenario that we cannot support.
My current IP address is: 188.254.152.213, but this can change as I have no static address.
Hello George,
thanks. To make this more clear:
- the HyperDB setup is actually a standard one and this is exactly what HyperDB is used for: the code I'm using is similar to the one in the example db-config http://plugins.svn.wordpress.org/hyperdb/trunk/db-config.php
- HyperDB adjusts the $wpdb object - as long as you use the WP API, everything should work just fine - $wpd->prefix, $wpdb->users etc., all of this should work just fine, and there's no reason to be guessing what the DB should be, it's being adjusted automatically
The error is this one:
[27-Sep-2018 11:39:19 UTC] WordPress database error Table 'yotlix_6.mw_users' doesn't exist for query SELECT DISTINCT
SUBSTRING_INDEX(ito.element_type, '_', 1) AS element_type_prefix,
ito.element_type AS original_post_type
FROM mw_625_icl_translate_job j
JOIN mw_625_icl_translation_status s
ON j.rid = s.rid
JOIN mw_625_icl_translations t
ON s.translation_id = t.translation_id
JOIN mw_625_icl_translate iclt
ON iclt.job_id = j.job_id
JOIN mw_625_icl_translations ito
ON ito.element_id = iclt.field_data
AND ito.trid = t.trid
JOIN (SELECT rid, MAX(job_id) job_id FROM mw_625_icl_translate_job GROUP BY rid ) jobmax
ON ( j.revision IS NULL
AND j.rid = jobmax.rid)
OR ( j.job_id = jobmax.job_id
AND j.translated = 1)
LEFT JOIN mw_625_posts p
ON t.element_id = p.ID
LEFT JOIN mw_users u
ON s.translator_id = u.ID
WHERE s.status > 0 AND s.status <> 9 AND (j.translator_id = 98 OR j.translator_id = 0 OR j.translator_id IS NULL ) AND s.translation_service='local' AND ( (t.source_language_code = 'cs' AND t.language_code IN ('en')) OR (t.source_language_code = 'en' AND t.language_code IN ('cs')) )
AND iclt.field_type = 'original_id'
made by WPML_Translation_Job_Factory::get_translation_job_types_filter
Thank you for your feedback.
I will forward the error message and your response to our developers and they will take another look at the code that is responsible for this.
As soon as we have something, either me or my colleague Yvette will bring you up to speed with the progress.
Ok, I debugged this more and can see what the issue is, it's this code, where the joined users table obviously doesn't exist.
$job_types = $wpdb->get_results( "SELECT DISTINCT
SUBSTRING_INDEX(ito.element_type, '_', 1) AS element_type_prefix,
ito.element_type AS original_post_type
FROM " . $this->get_table_join() . "
LEFT JOIN {$wpdb->prefix}posts p
ON t.element_id = p.ID
LEFT JOIN {$wpdb->users} u
ON s.translator_id = u.ID
WHERE {$where}
AND iclt.field_type = 'original_id'
" );
Any chance to rewrite this to get the user details in another query?
Yes, that is what I was trying to explain.
I talked with our developers this morning and they were concerned, as the users table is outside in another table and potentially re-writing the query could cause performance penalties - as it is another request that will run.
I have provided to them the full error message that you provided and I hope that they can help.
I will keep you posted about their decision.
As I suspected, that information was not enough for our developers to diagnose the issue and to suggest a proper resolution.
As HyperDB modifies the query, it is hard to tell with such limited data, if there is a problem with our code, or something else is running not the appropriate time or filtering incorrectly.
They are asking if it will be possible to have a staging machine where the issue is reproduced - for testing, or if it is fine to test on that machine where you have already provided access?
They need full access - even best if SSH access is included.
Please let me know if you can provide a staging environment (I am enabling the private reply) or if you agree for us to use the current environment for tests.
Please do note that using the current environment for tests is a dangerous operation and may lead to corruption or loss of data..
Hello!
I wanted to share something that I got from our developers.
They think that they found the issue in your custom code that you have used for fragmenting the subsites in a separate prefix.
They suggest two changes:
- Ignoring the users table in your code
- Adding prefix for the database in our code.
So let me here provide you the exact changes that you can test.
Your code change in db-config.php:
From
And code change in our plugin code (might not be necessary):
Change from:
LEFT JOIN {$wpdb->users} u
to
LEFT JOIN {$wpdb->prefix}users u
I see 3 instances in the WPML Translation Management plugin in the files:
2 in \wpml-translation-management\classes\class-wpml-translation-job-factory.php
1 in \wpml-translation-management\inc\translation-jobs\jobs\wpml-string-translation-job.class.php
Please let me know how it goes.
Please remember to create backups before you do anything.
