External Databases seem an issue

Heyo!

I’m migrating to external databases for all my sites, including the site host MainWP on. For some reason, MainWP cannot see the external database. I’m thinking it may be the checks used. When I look at the Info > Server section, it detects the mod, and the client encoding, but it cannot check the version. Nothing is being saved. I can’t import anything. It’s just a bit… confused I think. :slight_smile:

I’ve tested on a new install. I’ve added new sites and received positive confirmation of the site existing. And yet there are zero sites to display. I’ve tried importing sites and it has failed.

I’m thinking there is something about the way MainWP uses the database or cli that may be an issue.

Hoping someone here may have a suggestion, or an idea for me to do further testing.

Hi @LoveSudo

Can you please post the community system report from your MainWP Dashboard and post your Error Logs if any are present?

The Error Logs are located in your Dashboard under Info → Error Logs.

And the report is located in your Dashboard under Info → Server, on the top right of the page.

Be sure to use the button like the one below; this button hides all your private information:

image

Pressing the button auto-copies the report to your clipboard then just paste it in a reply here.

Heyo!

I created a fresh install, did not add a child yet, as the wizard could not see any information about the database and had a warning so just let it walk through without adding any children, and then I got the same results. Here is the info requested.

Community System Report


### WordPress Check                Required                                               Detected                                     Status   ###

FileSystem Method                  = direct                                               direct                                       Pass        
MultiSite Disabled                 =true                                                  true                                         Pass        
WordPress Memory Limit             >=64M                                                  40M                                          Warning     
WordPress Version                  >=3.6                                                  6.0                                          Pass        

### PHP                            Required                                               Detected                                     Status   ###

cURL Extension Enabled             =true                                                  true                                         Pass        
cURL SSL Version                   >=OpenSSL/1.1.0                                        OpenSSL/1.1.1f                               Pass        
cURL Timeout                       >=300 seconds                                          60                                           Warning     
cURL Version                       >=7.29.0                                               7.68.0                                       Pass        
PCRE Backtracking Limit            >=10000                                                1000000                                      Pass        
PHP Allow URL fopen                N/A                                                    YES                                                      
PHP Disabled Functions             N/A                                                    disk_free_space, disk_total_space, diskfreespace, dl, exec, opcache_get_configuration, opcache_get_status, passthru, pclose, pcntl_alarm, pcntl_exec, pcntl_fork, pcntl_get_last_error, pcntl_getpriority, pcntl_setpriority, pcntl_signal, pcntl_signal_dispatch, pcntl_sigprocmask, pcntl_sigtimedwait, pcntl_sigwaitinfo, pcntl_strerror, pcntl_wait, pcntl_waitpid, pcntl_wexitstatus, pcntl_wifcontinued, pcntl_wifexited, pcntl_wifsignaled, pcntl_wifstopped, pcntl_wstopsig, pcntl_wtermsig, popen, posix_getpwuid, posix_kill, posix_mkfifo, posix_setpgid, posix_setsid, posix_setuid, posix_uname, proc_close, proc_get_status, proc_nice, proc_open, proc_terminate, shell_exec, show_source, system,            
PHP Exif Support                   N/A                                                    YES ( V8.0.)                                             
PHP IPTC Support                   N/A                                                    YES                                                      
PHP Loaded Extensions              N/A                                                    Core, FFI, PDO, Phar, Reflection, SPL, SimpleXML, Zend OPcache, bcmath, calendar, cgi-fcgi, ctype, curl, date, dom, exif, fileinfo, filter, ftp, gd, gettext, hash, iconv, igbinary, imagick, intl, json, libxml, mbstring, mysqli, mysqlnd, openssl, pcre, pdo_mysql, posix, readline, redis, session, shmop, soap, sockets, sodium, standard, sysvmsg, sysvsem, sysvshm, tokenizer, xml, xmlreader, xmlwriter, xsl, zip, zlib            
PHP Max Execution Time             >=30 seconds                                           30                                           Pass        
PHP Max Input Time                 >=30 seconds                                           60                                           Pass        
PHP Memory Limit                   >=128M                                                 256M                                         Pass        
PHP Post Max Size                  >=2M                                                   64M                                          Pass        
PHP Safe Mode Disabled             =true                                                  true                                         Pass        
PHP Upload Max Filesize            >=2M                                                   64M                                          Pass        
PHP Version                        >=7.0                                                  8.0.19                                       Pass        
PHP XML Support                    N/A                                                    YES                                                      
SSL Extension Enabled              =true                                                  true                                         Pass        
SSL Warnings                       = empty                                                                                             Pass        

### MySQL                          Required                                               Detected                                     Status   ###

MySQL Client Encoding              N/A                                                    utf8mb4                                                  
MySQL Mode                         N/A                                                    REAL_AS_FLOAT,PIPES_AS_CONCAT,ANSI_QUOTES,IGNORE_SPACE,NO_ZERO_IN_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION            
MySQL Version                      >=5.0                                                                                               Fail        

### Server Configuration           Detected Value                                      ###

Accept Content                     text/html,application/xhtml+xml,application/xml;q=0.9,image/avif,image/webp,image/apng,*/*;q=0.8,application/signed-exchange;v=b3;q=0.9
Accept-Charset Content             N/A                                                    
Architecture                       64		 bit                                               
Gateway Interface                  CGI/1.1                                                
HTTPS                              ON                                                     
Memory Usage                       12.83 MB                                               
Operating System                   Linux                                                  
Request Time                       1654112215                                             
Server Protocol                    HTTP/2.0                                               
Server self connect                Not expected HTTP response body:                       
Server Software                    nginx/1.20.2                                           
User Agent                         Mozilla/5.0 (Windows NT 10.0; Win64; x64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/102.0.5005.61 Safari/537.36

### MainWP Dashboard Settings      Detected Value                                      ###

Abandoned plugins/themes tolerance                                                        
Basic uptime monitoring enabled    No                                                     
Cache control enabled              No                                                     
MainWP Dashboard Version           Latest: 4.2.4.1 | Detected: 4.2.4.1 Pass               
MainWP legacy backups enabled      No                                                     
Maximum number of pages to return                                                         
Maximum number of posts to return                                                         
Maximum simultaneous install and update requests                                                       
Maximum simultaneous requests      4                                                      
Maximum simultaneous requests per ip1                                                      
Maximum simultaneous sync requests                                                        
Minimum delay between requests     200                                                    
Minimum delay between requests to the same ip1000                                                   
Number of child sites              0                                                      
Optimize for shared hosting or big networksNo                                                     
Plugin advanced automatic updates enabledNo                                                     
Primary backup system              MainWP Legacy Backups                                  
REST API enabled                   No                                                     
Site health monitoring enabled     No                                                     
Theme advanced automatic updates enabledNo                                                     
Use WP Cron                        No                                                     
WP Core advanced automatic updates enabledNo                                                     

### Extensions                     Version                                                License                                      Status   ###

No installed extensions            

### Plugin                         Version                                                Status                                    ###

Limit Login Attempts Reloaded      2.25.3                                                 Active                                       
MainWP Dashboard                   4.2.4.1                                                Active                                       
SpinupWP                           1.4.1                                                  Active                                       

Error Logs
downloadedFULL text NOT NULL DEFAULT ‘’,
downloadedFULLComplete tinyint(1) NOT NULL DEFAULT 0,
downloadedDBComplete tinyint(1) NOT NULL DEFAULT 0,
attempts int(11) NOT NULL DEFAULT 0,
removedFiles tinyint(1) NOT NULL DEFAULT 0,
(SELECT phpversion.value FROM 68m_mainwp_wp_options phpversion WHERE phpversion.wpid = intwp.id AND phpversion.name = “phpversion” LIMIT 1) AS phpversion,
last_error text NOT NULL DEFAULT ‘’,
task_id int(11) NOT NULL,
ON wp.id = wp_sync.wpid
JOIN 68m_mainwp_wp_sync wp_sync
dtsFetched int(11) NOT NULL DEFAULT 0,
FROM 68m_mainwp_wp wp
(SELECT recent_posts.value FROM 68m_mainwp_wp_options recent_posts WHERE recent_posts.wpid = intwp.id AND recent_posts.name = “recent_posts” LIMIT 1) AS recent_posts,
(SELECT recent_pages.value FROM 68m_mainwp_wp_options recent_pages WHERE recent_pages.wpid = intwp.id AND recent_pages.name = “recent_pages” LIMIT 1) AS recent_pages,
downloadedDB text NOT NULL DEFAULT ‘’,
pid int(11) NOT NULL DEFAULT 0,
fetchResult text NOT NULL DEFAULT ‘’,
(SELECT wp_upgrades.value FROM 68m_mainwp_wp_options wp_upgrades WHERE wp_upgrades.wpid = intwp.id AND wp_upgrades.name = “wp_upgrades” LIMIT 1) AS wp_upgrades FROM 68m_mainwp_wp intwp) wp_optionview ON wp.id = wp_optionview.wpid WHERE ( wp_sync.dtsAutomaticSync = 0 OR wp_sync.dtsAutomaticSyncStart = 0 OR wp_sync.dtsAutomaticSyncStart < 0 ) AND wp.is_staging = 0 ORDER BY wp_sync.dtsAutomaticSyncStart ASC LIMIT 4 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, Cron_Event_Command->run, Cron_Event_Command::run_event, do_action_ref_array(‘mainwp_cronupdatescheck_action’), WP_Hook->do_action, WP_Hook->apply_filters, MainWP\Dashboard\MainWP_System_Cron_Jobs->cron_updates_check, MainWP\Dashboard\MainWP_DB->get_websites_check_updates
wp_id int(11) NOT NULL,
UNIQUE (task_id)) DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_520_ci made by activate_plugin, plugin_sandbox_scrape, include_once(’/plugins/mainwp/mainwp.php’), MainWP\Dashboard\MainWP_System->__construct, MainWP\Dashboard\MainWP_System->update, MainWP\Dashboard\MainWP_Install->install, dbDelta
KEY idx_task_id (task_id),
WHERE (wp_sync.sync_errors IS NOT NULL) AND (wp_sync.sync_errors <> “”) AND wp.is_staging = 0 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, Cron_Event_Command->run, Cron_Event_Command::run_event, do_action_ref_array(‘mainwp_cronupdatescheck_action’), WP_Hook->do_action, WP_Hook->apply_filters, MainWP\Dashboard\MainWP_System_Cron_Jobs->cron_updates_check, MainWP\Dashboard\MainWP_System_Cron_Jobs->start_notification_daily_digest, MainWP\Dashboard\MainWP_DB->get_disconnected_websites
01-Jun-2022 19:36:16 UTC WordPress database error Unable to create or change a table without a primary key, when the system variable ‘sql_require_primary_key’ is set. Add a primary key to the table or unset this variable to avoid this message. Note that tables without a primary key can cause performance problems in row-based replication, so please consult your DBA before changing this setting. for query CREATE TABLE 68m_mainwp_wp_backup_progress (
01-Jun-2022 19:36:24 UTC WordPress database error You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ‘“version”’ at line 1 for query SHOW VARIABLES LIKE “version” made by do_action(‘admin_init’), WP_Hook->do_action, WP_Hook->apply_filters, MainWP\Dashboard\MainWP_Setup_Wizard->admin_init, MainWP\Dashboard\MainWP_Setup_Wizard->setup_wizard_content, call_user_func, MainWP\Dashboard\MainWP_Setup_Wizard->mwp_setup_system_requirements, MainWP\Dashboard\MainWP_Server_Information::render_quick_setup_system_check, MainWP\Dashboard\MainWP_Server_Information::render_row_with_description, call_user_func, MainWP\Dashboard\MainWP_Server_Information_Handler::get_mysql_version, MainWP\Dashboard\MainWP_DB_Base->get_my_sql_version
01-Jun-2022 19:36:55 UTC WordPress database error You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ‘“version”’ at line 1 for query SHOW VARIABLES LIKE “version” made by do_action(‘mainwp_page_ServerInformation’), WP_Hook->do_action, WP_Hook->apply_filters, MainWP\Dashboard\MainWP_Server_Information::render, MainWP\Dashboard\MainWP_Server_Information::render_mysql_check_tbody, MainWP\Dashboard\MainWP_Server_Information::render_row, call_user_func, MainWP\Dashboard\MainWP_Server_Information_Handler::get_mysql_version, MainWP\Dashboard\MainWP_DB_Base->get_my_sql_version
01-Jun-2022 19:37:04 UTC WordPress database error Reference ‘recent_comments’ not supported (forward reference in item list) for query SELECT wp.,wp_sync.,wp_optionview.* FROM 68m_mainwp_wp wp JOIN 68m_mainwp_wp_sync wp_sync ON wp.id = wp_sync.wpid JOIN (SELECT intwp.id AS wpid, (SELECT recent_comments.value FROM 68m_mainwp_wp_options recent_comments WHERE recent_comments.wpid = intwp.id AND recent_comments.name = “recent_comments” LIMIT 1) AS recent_comments,
01-Jun-2022 19:38:04 UTC WordPress database error Unknown column ‘’ in ‘where clause’ for query SELECT wp.,wp_sync.

@LoveSudo

Thanks for the system report.

Would you mind opening a private Help Desk ticket so we can collect some additional information & investigate further?

I can do that for you. I’ll go ahead and set that up right now.

The problem has been resolved after disabling the ANSI_QUOTES MySQL mode.

3 Likes

This topic was automatically closed 24 hours after the last reply. New replies are no longer allowed.